一、索引设计原则1、代码先行索引后上主体业务功能开发完毕把涉及到该表相关sql都要拿出来分析之后再建立索引。2、联合索引尽量覆盖条件比如可以设计一个或者两三个联合索引(尽量少建单值索引)让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。3、不要在小基数字段上建立索引索引基数是指这个字段在表里总共有多少个不同的值比如一张表总共100万行记录其中有个性别字段其值不是男就是女那么该字段的基数就是2。如果对这种小基数字段建立索引的话还不如全表扫描了因为你的索引树里就包含男和女两种值根本没法进行快速的二分查找那用索引就没有太大的意义了。一般建立索引尽量使用那些基数比较大的字段就是值比较多的字段那么才能发挥出B树快速二分查找的优势来。4、长字符串我们可以采用前缀索引尽量对字段类型较小的列设计索引比如tinyint之类的占用磁盘空间小搜索性能好。对于varchar(255)的大字段会比较占用磁盘空间可优化成对这个字段的前20个字符建索引把这个字段里的每个值的前20个字符放在索引树里比如 KEY index(name(20),age,position)。如果需要order by name因为name在索引树里仅仅包含了前20个字符所以这个排序无法用上索引的 group by也是同理。5、where与order by冲突时优先where在where和order by出现索引设计冲突时一般都是让where条件去使用索引来快速筛选出来一部分指定的数据接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据然后做排序的成本可能会小很多。6、基于慢sql查询做优化可以根据监控后台的一些慢sql针对这些慢sql查询做特定的索引优化。关于慢sql查询工具参考这篇文章http://share.note.youdao.com/noteshare?idc71f1e66b7f91dab989a9d3a7c8ceb8esub0B91DF863FB846AA9A1CDDF431402C7B二、常规索引优化示例代码CREATE TABLE employees ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(24) NOT NULL DEFAULT COMMENT 姓名, age int(11) NOT NULL DEFAULT 0 COMMENT 年龄, position varchar(20) NOT NULL DEFAULT COMMENT 职位, hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入职时间, PRIMARY KEY (id), KEY idx_name_age_position (name,age,position) USING BTREE ) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8 COMMENT员工记录表; INSERT INTO employees(name,age,position,hire_time) VALUES(LiLei,22,manager,NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES(HanMeimei, 23,dev,NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES(Lucy,23,dev,NOW()); -- 插入一些示例数据 drop procedure if exists insert_emp; delimiter ;; create procedure insert_emp() begin declare i int; set i1; while(i100000)do insert into employees(name,age,position) values(CONCAT(zhuge,i),i,dev); set ii1; end while; end;; delimiter ; call insert_emp();1、常见案例、原因及优化方案a、联合索引第一个字段用范围不会走索引EXPLAIN SELECT * FROM employees WHERE name LiLei AND age 22 AND position manager;结论联合索引第一个字段就用范围查找不会走索引mysql内部可能觉得第一个字段就用范围结果集应该很大回表效率不高还不如就全表扫描b、强制走索引EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name LiLei AND age 22 AND position manager;结论虽然使用了强制走索引让联合索引第一个字段范围查找也走索引扫描的行rows看上去也少了点但是最终查找效率不一定比全表扫描高因为回表效率不高TEST-- 关闭查询缓存 set global query_cache_size0; set global query_cache_type0; -- 执行时间0.333s SELECT * FROM employees WHERE name LiLei; -- 执行时间0.444s SELECT * FROM employees force index(idx_name_age_position) WHERE name LiLei;c、覆盖索引优化EXPLAIN SELECT name,age,position FROM employees WHERE name LiLei AND age 22 AND position manager;d、in和or在表数据量比较大的情况会走索引在表记录不多的情况下会选择全表扫描EXPLAIN SELECT * FROM employees WHERE name in (LiLei,HanMeimei,Lucy) AND age 22 AND position manager;EXPLAIN SELECT * FROM employees WHERE (name LiLei or name HanMeimei) AND age 22 AND position manager;TEST将employees 表复制一张employees_copy的表里面保留两三条记录EXPLAIN SELECT * FROM employees_copy WHERE name in (LiLei,HanMeimei,Lucy) AND age 22 AND position manager;EXPLAIN SELECT * FROM employees_copy WHERE (name LiLei or name HanMeimei) AND age 22 AND position manager;e、like KK% 一般情况都会走索引EXPLAIN SELECT * FROM employees WHERE name like LiLei% AND age 22 AND position manager;EXPLAIN SELECT * FROM employees_copy WHERE name like LiLei% AND age 22 AND position manager;f、分页查询优化1、根据自增且连续的主键排序的分页查询示例mysql select * from employees limit 90000,5; mysql select * from employees where id 90000 limit 5;注意如果主键不连续不能使用上面描述的优化方法。另外如果原 SQL 是 order by 非主键的字段按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件主键自增且连续结果是按照主键排序的2、根据非主键字段排序的分页查询示例mysql select * from employees ORDER BY name limit 90000,5; mysql select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id ed.id;让排序时返回的字段尽可能少所以可以让排序和分页操作先查出主键然后根据主键查到对应的记录g、join关联查询优化1、关联字段加索引让mysql做join操作时尽量选择NLJ算法驱动表因为需要全部查询出来所以过滤的条件也尽量要走索引避免全表扫描总之能走索引的过滤条件尽量都走索引。2、小表驱动大表写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式省去mysql优化器自己判断的时间straight_join解释straight_join功能同join类似但能让左边的表来驱动右边的表能改表优化器对于联表查询的执行顺序。比如select * from t2 straight_join t1 on t2.a t1.a;代表指定mysql选着 t2 表作为驱动表。straight_join只适用于inner join并不适用于left joinright join。因为left joinright join已经代表指定了表的执行顺序尽可能让优化器去判断因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。h、in和exsits优化原则小表驱动大表即小的数据集驱动大的数据集1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比3、EXISTS子查询往往也可以用JOIN来代替何种最优需要具体问题具体分析i、count(*)查询优化字段有索引count(*)≈count(1)count(字段)count(主键 id) //字段有索引count(字段)统计走二级索引二级索引存储数据比主键索引少所以count(字段)count(主键 id)字段无索引count(*)≈count(1)count(主键 id)count(字段) //字段没有索引count(字段)统计走不了索引count(主键 id)还可以走主键索引所以count(主键 id)count(字段)count(1)跟count(字段)执行过程类似不过count(1)不需要取出字段统计就用常量1做统计count(字段)还需要取出字段所以理论上count(1)比count(字段)会快一点。count(*) 是例外mysql并不会把全部字段取出来而是专门做了优化不取值按行累加效率很高所以不需要用count(列名)或count(常量)来替代 count(*)。为什么对于count(id)mysql最终选择辅助索引而不是主键聚集索引因为二级索引相对主键索引存储数据更少检索性能应该更高mysql内部做了点优化(应该是在5.7版本才优化)。1、查询mysql自己维护的总行数myisam存储引擎的表有innodb没有2、show table status3、将总数维护到Redis里插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)但是这种方式可能不准很难保证表操作和redis操作的事务一致性4、增加数据库计数表插入或删除表数据行的时候同时维护计数表让他们在同一个事务里操作2、常见SQL深入优化1.Order by与Group by优化Case 1:分析利用最左前缀法则中间字段不能断因此查询用到了name索引从key_len74也能看出age索引列用在排序过程中因为Extra字段里没有using filesortCase 2分析从explain的执行结果来看key_len74查询使用了name索引由于用了position进行排序跳过了age出现了Using filesort。Case 3分析查找只用到索引nameage和position用于排序无Using filesort。Case 4分析和Case 3中explain的执行结果一样但是出现了Using filesort因为索引的创建顺序为name,age,position但是排序的时候age和position颠倒位置了。Case 5分析与Case 4对比在Extra中并未出现Using filesort因为age为常量在排序中被优化所以索引未颠倒不会出现Using filesort。Case 6分析虽然排序的字段列与索引顺序一样且order by默认升序这里position desc变成了降序导致与索引的排序方式不同从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。Case 7分析对于排序来说多个相等条件也是范围查询Case 8可以用覆盖索引优化3、优化总结1、MySQL支持两种方式的排序filesort和indexUsing index是指MySQL扫描索引本身完成排序。index效率高filesort效率低。2、order by满足两种情况会使用Using index。1) order by语句使用索引最左前列。2) 使用where子句与order by子句条件列组合满足索引最左前列。3、尽量在索引列上完成排序遵循索引建立索引创建的顺序时的最左前缀法则。4、如果order by的条件不在索引列上就会产生Using filesort。5、能用覆盖索引尽量用覆盖索引6、group by与order by很类似其实质是先排序后分组遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意where高于having能写在where中的限定条件就不要去having限定了。三、Using filesort文件排序原理详解filesort文件排序方式单路排序是一次性取出满足条件行的所有字段然后在sort buffer中进行排序用trace工具可以看到sort_mode信息里显示 sort_key, additional_fields 或者 sort_key, packed_additional_fields 双路排序又叫回表排序模式是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID然后在 sort buffer 中进行排序排序完后需要再次取回其它需要的字段用trace工具可以看到sort_mode信息里显示 sort_key, rowid MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。如果 字段的总长度小于max_length_for_sort_data 那么使用 单路排序模式如果 字段的总长度大于max_length_for_sort_data 那么使用 双路排序模·式。示例验证下各种排序方式查看下这条sql对应trace结果如下(只展示排序部分)mysql set session optimizer_traceenabledon,end_markers_in_jsonon; --开启trace mysql select * from employees where name zhuge order by position; mysql select * from information_schema.OPTIMIZER_TRACE; trace排序部分结果 join_execution: { --Sql执行阶段 select#: 1, steps: [ { filesort_information: [ { direction: asc, table: employees, field: position } ] /* filesort_information */, filesort_priority_queue_optimization: { usable: false, cause: not applicable (no LIMIT) } /* filesort_priority_queue_optimization */, filesort_execution: [ ] /* filesort_execution */, filesort_summary: { --文件排序信息 rows: 10000, --预计扫描行数 examined_rows: 10000, --参与排序的行 number_of_tmp_files: 3, --使用临时文件的个数这个值如果为0代表全部使用的sort_buffer内存排序否则使用的磁盘文件排序 sort_buffer_size: 262056, --排序缓存的大小单位Byte sort_mode: sort_key, packed_additional_fields --排序方式这里用的单路排序 } /* filesort_summary */ } ] /* steps */ } /* join_execution */ mysql set max_length_for_sort_data 10; --employees表所有字段长度总和肯定大于10字节 mysql select * from employees where name zhuge order by position; mysql select * from information_schema.OPTIMIZER_TRACE; trace排序部分结果 join_execution: { select#: 1, steps: [ { filesort_information: [ { direction: asc, table: employees, field: position } ] /* filesort_information */, filesort_priority_queue_optimization: { usable: false, cause: not applicable (no LIMIT) } /* filesort_priority_queue_optimization */, filesort_execution: [ ] /* filesort_execution */, filesort_summary: { rows: 10000, examined_rows: 10000, number_of_tmp_files: 2, sort_buffer_size: 262136, sort_mode: sort_key, rowid --排序方式这里用的双路排序 } /* filesort_summary */ } ] /* steps */ } /* join_execution */ mysql set session optimizer_traceenabledoff; --关闭trace我们先看单路排序的详细过程从索引name找到第一个满足 name ‘zhuge’ 条件的主键 id根据主键 id 取出整行取出所有字段的值存入 sort_buffer 中从索引name找到下一个满足 name ‘zhuge’ 条件的主键 id重复步骤 2、3 直到不满足 name ‘zhuge’对 sort_buffer 中的数据按照字段 position 进行排序返回结果给客户端我们再看下双路排序的详细过程从索引 name 找到第一个满足 name ‘zhuge’ 的主键id根据主键 id 取出整行把排序字段 position 和主键 id 这两个字段放到 sort buffer 中从索引 name 取下一个满足 name ‘zhuge’ 记录的主键 id重复 3、4 直到不满足 name ‘zhuge’对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序遍历排序好的 id 和字段 position按照 id 的值回到原表中取出 所有字段的值返回给客户端其实对比两个排序模式单路排序会把所有需要查询的字段都放到 sort buffer 中而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序然后再通过主键回到原表查询需要的字段。如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了可以适当把 max_length_for_sort_data 配置小点让优化器选择使用双路排序算法可以在sort_buffer 中一次排序更多的行只是需要再根据主键回到原表取数据。如果 MySQL 排序内存有条件可以配置比较大可以适当增大 max_length_for_sort_data 的值让优化器优先选择全字段排序(单路排序)把需要的字段放到 sort_buffer 中这样排序后就会直接从内存里返回查询结果了。所以MySQL通过 max_length_for_sort_data 这个参数来控制排序在不同场景使用不同的排序模式从而提升排序效率。注意如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序但不能因为这个就随便增大sort_buffer(默认1M)mysql很多参数设置都是做过优化的不要轻易调整。四、MySQL的表关联常见有两种算法Nested-Loop Join 算法Block Nested-Loop Join 算法-- 示例表 CREATE TABLE t1 ( id int(11) NOT NULL AUTO_INCREMENT, a int(11) DEFAULT NULL, b int(11) DEFAULT NULL, PRIMARY KEY (id), KEY idx_a (a) ) ENGINEInnoDB DEFAULT CHARSETutf8; create table t2 like t1; -- 插入一些示例数据 -- 往t1表插入1万行记录 drop procedure if exists insert_t1; delimiter ;; create procedure insert_t1() begin declare i int; set i1; while(i10000)do insert into t1(a,b) values(i,i); set ii1; end while; end;; delimiter ; call insert_t1(); -- 往t2表插入100行记录 drop procedure if exists insert_t2; delimiter ;; create procedure insert_t2() begin declare i int; set i1; while(i100)do insert into t2(a,b) values(i,i); set ii1; end while; end;; delimiter ; call insert_t2();1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法一次一行循环地从第一张表称为驱动表中读取行在这行数据中取到关联字段根据关联字段在另一张表被驱动表里取出满足条件的行然后取出两张表的结果合集。mysql EXPLAIN select * from t1 inner join t2 on t1.a t2.a;从执行计划中可以看到这些信息驱动表是 t2被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)优化器一般会优先选择小表做驱动表用where条件过滤完驱动表然后再跟被驱动表做关联查询。所以使用 inner join 时排在前面的表并不一定就是驱动表。当使用left join时左表是驱动表右表是被驱动表当使用right join时右表时驱动表左表是被驱动表当使用join时mysql会选择数据量比较小的表作为驱动表大表作为被驱动表。使用了 NLJ算法。一般 join 语句中如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。上面sql的大致流程如下从表 t2 中读取一行数据如果t2表有查询过滤条件的用先用条件过滤完再从过滤结果里取出一行数据从第 1 步的数据中取出关联字段 a到表 t1 中查找取出表 t1 中满足条件的行跟 t2 中获取到的结果合并作为结果返回给客户端重复上面 3 步。整个过程会读取 t2 表的所有数据(扫描100行)然后遍历这每行数据中字段 a 的值根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引1次扫描可以认为最终只扫描 t1 表一行完整数据也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行。如果被驱动表的关联字段没索引使用NLJ算法性能会比较低(下面有详细解释)mysql会选择Block Nested-Loop Join算法。2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法把驱动表的数据读入到 join_buffer 中然后扫描被驱动表把被驱动表每一行取出来跟 join_buffer 中的数据做对比。mysqlEXPLAIN select * from t1 inner join t2 on t1.b t2.b;Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。上面sql的大致流程如下把 t2 的所有数据放入到 join_buffer 中把表 t1 中每一行取出来跟 join_buffer 中的数据做对比返回满足 join 条件的数据整个过程对表 t1 和 t2 都做了一次全表扫描因此扫描的总行数为10000(表 t1 的数据总量) 100(表 t2 的数据总量) 10100。并且 join_buffer 里的数据是无序的因此对表 t1 中的每一行都要做 100 次判断所以内存中的判断次数是 100 * 10000 100 万次。这个例子里表 t2 才 100 行要是表 t2 是一个大表join_buffer 放不下怎么办呢·join_buffer 的大小是由参数 join_buffer_size 设定的默认值是 256k。如果放不下表 t2 的所有数据话策略很简单就是分段放。比如 t2 表有1000行记录 join_buffer 一次只能放800行数据那么执行过程就是先往 join_buffer 里放800行记录然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果然后清空 join_buffer 再放入 t2 表剩余200行记录再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢如果上面第二条sql使用 Nested-Loop Join那么扫描行数为 100 * 10000 100万次这个是磁盘扫描。很显然用BNL磁盘扫描次数少很多相比于磁盘扫描BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法有索引的情况下 NLJ 算法比 BNL算法性能更高五、索引下推Index Condition PushdownICP对于辅助的联合索引(name,age,position)正常情况按照最左前缀原则SELECT * FROM employees WHERE name like LiLei% AND age 22 AND position manager 这种情况只会走name字段索引因为根据name字段过滤完得到的索引行里的age和position是无序的无法很好的利用索引。在MySQL5.6之前的版本这个查询只能在联合索引里匹配到名字是 LiLei 开头的索引然后拿这些索引对应的主键逐个回表到主键索引上找出相应的记录再比对age和position这两个字段的值是否符合。MySQL 5.6引入了索引下推优化可以在索引遍历过程中对索引中包含的所有字段先做判断过滤掉不符合条件的记录之后再回表可以有效的减少回表次数。使用了索引下推优化后上面那个查询在联合索引里匹配到名字是 LiLei 开头的索引之后同时还会在索引里过滤age和position这两个字段拿着过滤完剩下的索引对应的主键id再回表查整行数据。索引下推会减少回表次数对于innodb引擎的表索引下推只能用于二级索引innodb的主键索引聚簇索引树叶子节点上保存的是全行数据所以这个时候索引下推并不会起到减少查询全行数据的效果。为什么范围查找Mysql没有用索引下推优化估计应该是Mysql认为范围查找过滤的结果集过大like KK% 在绝大多数情况来看过滤后的结果集比较小所以这里Mysql选择给 like KK% 用了索引下推优化当然这也不是绝对的有时like KK% 也不一定就会走索引下推。六、Mysql如何选择合适的索引mysql EXPLAIN select * from employees where name a;如果用name索引需要遍历name字段联合索引树然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据成本比全表扫描还高可以用覆盖索引优化这样只需要遍历name字段的联合索引树就能拿到所有结果如下mysql EXPLAIN select name,age,position from employees where name a ;mysql EXPLAIN select * from employees where name zzz ;对于上面这两种 namea 和 namezzz 的执行结果mysql最终是否选择走索引或者一张表涉及多个索引mysql最终如何选择索引我们可以用trace工具来一查究竟开启trace工具会影响mysql性能所以只能临时分析sql使用用完之后立即关闭七、trace工具用法mysql set session optimizer_traceenabledon,end_markers_in_jsonon; --开启trace mysql select * from employees where name a order by position; mysql SELECT * FROM information_schema.OPTIMIZER_TRACE; 查看trace字段 { steps: [ { join_preparation: { --第一阶段SQL准备阶段格式化sql select#: 1, steps: [ { expanded_query: /* select#1 */ select employees.id AS id,employees.name AS name,employees.age AS age,employees.position AS position,employees.hire_time AS hire_time from employees where (employees.name a) order by employees.position } ] /* steps */ } /* join_preparation */ }, { join_optimization: { --第二阶段SQL优化阶段 select#: 1, steps: [ { condition_processing: { --条件处理 condition: WHERE, original_condition: (employees.name a), steps: [ { transformation: equality_propagation, resulting_condition: (employees.name a) }, { transformation: constant_propagation, resulting_condition: (employees.name a) }, { transformation: trivial_condition_removal, resulting_condition: (employees.name a) } ] /* steps */ } /* condition_processing */ }, { substitute_generated_columns: { } /* substitute_generated_columns */ }, { table_dependencies: [ --表依赖详情 { table: employees, row_may_be_null: false, map_bit: 0, depends_on_map_bits: [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { ref_optimizer_key_uses: [ ] /* ref_optimizer_key_uses */ }, { rows_estimation: [ --预估表的访问成本 { table: employees, range_analysis: { table_scan: { --全表扫描情况 rows: 10123, --扫描行数 cost: 2054.7 --查询成本 } /* table_scan */, potential_range_indexes: [ --查询可能使用的索引 { index: PRIMARY, --主键索引 usable: false, cause: not_applicable }, { index: idx_name_age_position, --辅助索引 usable: true, key_parts: [ name, age, position, id ] /* key_parts */ } ] /* potential_range_indexes */, setup_range_conditions: [ ] /* setup_range_conditions */, group_index_range: { chosen: false, cause: not_group_by_or_distinct } /* group_index_range */, analyzing_range_alternatives: { --分析各个索引使用成本 range_scan_alternatives: [ { index: idx_name_age_position, ranges: [ a name --索引使用范围 ] /* ranges */, index_dives_for_eq_ranges: true, rowid_ordered: false, --使用该索引获取的记录是否按照主键排序 using_mrr: false, index_only: false, --是否使用覆盖索引 rows: 5061, --索引扫描行数 cost: 6074.2, --索引使用成本 chosen: false, --是否选择该索引 cause: cost } ] /* range_scan_alternatives */, analyzing_roworder_intersect: { usable: false, cause: too_few_roworder_scans } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { considered_execution_plans: [ { plan_prefix: [ ] /* plan_prefix */, table: employees, best_access_path: { --最优访问路径 considered_access_paths: [ --最终选择的访问路径 { rows_to_scan: 10123, access_type: scan, --访问类型为scan全表扫描 resulting_rows: 10123, cost: 2052.6, chosen: true, --确定选择 use_tmp_table: true } ] /* considered_access_paths */ } /* best_access_path */, condition_filtering_pct: 100, rows_for_plan: 10123, cost_for_plan: 2052.6, sort_cost: 10123, new_cost_for_plan: 12176, chosen: true } ] /* considered_execution_plans */ }, { attaching_conditions_to_tables: { original_condition: (employees.name a), attached_conditions_computation: [ ] /* attached_conditions_computation */, attached_conditions_summary: [ { table: employees, attached: (employees.name a) } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { clause_processing: { clause: ORDER BY, original_clause: employees.position, items: [ { item: employees.position } ] /* items */, resulting_clause_is_simple: true, resulting_clause: employees.position } /* clause_processing */ }, { reconsidering_access_paths_for_index_ordering: { clause: ORDER BY, steps: [ ] /* steps */, index_order_summary: { table: employees, index_provides_order: false, order_direction: undefined, index: unknown, plan_changed: false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { refine_plan: [ { table: employees } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { join_execution: { --第三阶段SQL执行阶段 select#: 1, steps: [ ] /* steps */ } /* join_execution */ } ] /* steps */ } 结论全表扫描的成本低于索引扫描所以mysql最终选择全表扫描 mysql select * from employees where name zzz order by position; mysql SELECT * FROM information_schema.OPTIMIZER_TRACE; 查看trace字段可知索引扫描的成本低于全表扫描所以mysql最终选择索引扫描 mysql set session optimizer_traceenabledoff; --关闭trace