案例场景

假设现在我们有一张人物表，建表语句如下：

 create TABLE `person` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `name` varchar(255) NOT NULL,
   `score` int(11) NOT NULL,
   `create_time` timestamp NOT NULL,
   PRIMARY KEY (`id`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

并创建两个索引：

 KEY `name_score` (`name`,`score`) USING BTREE,
 KEY `create_time` (`create_time`) USING BTREE

然后插入 10 万条数据：

 create DEFINER=`root`@`%` PROCEDURE `insert_person`()
 begin
     declare c_id integer default 1;
     while c_id<=100000 do
     insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
     -- 需要注意，因为使用的是now()，所以对于后续的例子，使用文中的SQL你需要自己调整条件，否则可能看不到文中的效果
     set c_id=c_id+1;
     end while;
 end

数据插入后，我们用下面的 SQL 进行查询：

 explain select * from person where NAME>'name84059' and create_time>'2020-01-24 05:00:00'

通过上面的执行计划可以看到：type=All，说明是全表扫描。

接着我们把 create_time 条件中的 5 点改为 6 点：

 explain select * from person where NAME>'name84059' and create_time>'2020-01-24 06:00:00'

执行计划显示：type=range，key=create_time，走了 create_time 索引，而不是 name_score 联合索引。

看到这里，你是不是很诧异？接下来，我们就一起来分析一下这背后的原因。

原因分析

MySQL 在查询数据之前，会先对可能的方案做执行计划，然后依据成本决定走哪个执行计划。这里的成本，包括 IO 成本和 CPU 成本：

IO 成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的 IO 成本常数是 1（也就是读取 1 个页成本是 1）。
CPU 成本，是检测数据是否满足条件和排序等 CPU 操作的成本。默认情况下，检测记录的成本是 0.2。

MySQL 维护了表的统计信息，可以使用下面的命令查看：

 SHOW TABLE STATUS LIKE 'person'

从图中可以看到，总行数是 100086 行，由于 MySQL 的统计信息是一个估算，这里多了 86 行是正常的。CPU 成本是 100086*0.2=20017 左右。

数据长度是 4734976 字节。对于 InnoDB 来说，4734976 就是聚簇索引占用的空间，等于聚簇索引的页数量 * 每个页面的大小。InnoDB 每个页面的大小是 16KB，大概计算出页的数量是 289，因此 IO 成本是 289 左右。

所以，全表扫描的总成本是 20306 左右。

在 MySQL 5.6 及之后的版本中，我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程。

 SET optimizer_trace="enabled=on";
 explain select * from person where NAME >'name84059' and create_time>'2020-01-24 05:00:00';
 select * from information_schema.OPTIMIZER_TRACE;
 SET optimizer_trace="enabled=off";

对于按照 create_time>'2020-01-24 05:00:00’ 条件走全表扫描的 SQL，我从 OPTIMIZER_TRACE 的执行结果中，摘出了几个重要片段来重点分析：

使用 name_score 对 name84059<name 条件进行索引扫描需要扫描 25362 行，成本是 30435。

 {
   "index": "name_score",
   "ranges": [
     "name84059 < name"
   ],
   "rows": 25362,
   "cost": 30435,
   "chosen": false,
   "cause": "cost"
 }

30435 是查询二级索引的 IO 成本和 CPU 成本之和，再加上回表查询聚簇索引的 IO 成本和 CPU 成本之和。

使用 create_time 进行索引扫描需要扫描 23758 行，成本是 28511。

 {
   "index": "create_time",
   "ranges": [
     "0x5e2a79d0 < create_time"
   ],
   "rows": 23758,
   "cost": 28511,
   "chosen": false,
   "cause": "cost"
 }

3. 全表扫描 100086 条记录的成本是 20306。（和上面计算的一致）

 {
   "considered_execution_plans": [{
     "table": "`person`",
     "best_access_path": {
       "considered_access_paths": [{
         "rows_to_scan": 100086,
         "access_type": "scan",
         "resulting_rows": 100086,
         "cost": 20306,
         "chosen": true
       }]
     },
     "rows_for_plan": 100086,
     "cost_for_plan": 20306,
     "chosen": true
   }]
 }

所以 MySQL 最终选择了全表扫描方式作为执行计划。

把 SQL 中的 create_time 条件从 05:00 改为 06:00，再次分析 OPTIMIZER_TRACE 可以看到：

 {
   "index": "create_time",
   "ranges": [
     "0x5e2a87e0 < create_time"
   ],
   "rows": 16588,
   "cost": 19907,
   "chosen": true
 }

因为是查询更晚时间的数据，走 create_time 索引需要扫描的行数从 23758 减少到了 16588。这次走这个索引的成本 19907 小于全表扫描的 20306，更小于走 name_score 索引的 30435。

所以这次执行计划选择的是走 create_time 索引。

解决方案

有时会因为统计信息的不准确或成本估算的问题，实际开销会和 MySQL 统计出来的差距较大，导致 MySQL 选择错误的索引或是直接选择走全表扫描，这个时候就需要人工干预，使用强制索引了。

比如，像这样强制走 name_score 索引：

 explain select * from person FORCE INDEX(name_score) where NAME >'name84059' and create_time>'2020-01-24 00:00:00'

小结

本文通过一个例子，谈到了 MySQL 还有另外一个索引失效的场景，即分析器成本分析。

对于是否走索引，我们要学会使用 explain 进行分析。另外，在 MySQL 5.6 及之后的版本中，我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程。

本文正在参加「技术专题19期漫谈数据库技术」活动

MySQL是如何判断要不要走索引的？

案例场景

原因分析

解决方案

小结