要不要走索引？MySQL 的成本分析
谈到索引失效，大家可能都能列举出几个场景，比如：后模糊查询、条件中带函数、索引中断等等。今天我想和你分享另一个场景：索引成本分析。
我先用一个具体的例子来描述一下这个场景。
案例场景
假设现在我们有一张人物表，建表语句如下：
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 的执行结果中，摘出了几个重要片段来重点分析：
1、使用 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 成本之和。
2、使用 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 功能查看优化器生成执行计划的整个过程。
整理自极客时间《JAVA开发常见错误》学习笔记
公众号：杨同学technotes
