谈到索引失效，大家可能都能列举出几个场景，比如：后模糊查询、条件中带函数、索引中断等等。今天我想和你分享另一个场景：索引成本分析。

我先用一个具体的例子来描述一下这个场景。

案例场景

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

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