本文为《MySQL归纳学习》专栏的第十二篇文章，同时也是关于《MySQL索引》知识点的第四篇文章。
相关文章：
MySQL索引大揭秘：优化查询的秘籍（上）
MySQL索引大揭秘：优化查询的秘籍（下）
技巧解锁：应对优化器选错索引的智慧之道
欢迎阅读本篇文章，《解析MySQL：SQL索引的检查与失效情景解析》。在日常的数据库操作中，我们经常会使用SQL索引来提高查询效率，但是如何查看SQL是否真正命中了索引呢？本文将详细介绍使用explain命令查看索引是否生效，并解读其执行结果。同时，我们也将深入讨论索引失效的四种场景，并提供实用的操作建议，帮助读者避免陷入这些常见的错误。无论你是数据库的新手还是老手，这篇文章都将为你提供一些有价值的洞见。让我们开始探索MySQL的神秘世界吧！
如何查看 SQL 语句是否能够命中索引？
执行 expalin 命令，此命令能够打印出 SQL 语句的执行计划，从而判断要执行的 SQL 语句是否能够命中索引，并做进一步调整。 在优化 SQL 查询时，最好的方式就是使用此命令来判断执行计划是否合理。 如下：
explain select * from test_user where id=1;
重点关注三个字段：type，key 和 Extra。
- type 表示 MySQL 在表中找到所需行的方式, 又称"访问类型"。 ALL, index, range, ref, eq_ref, const, system, NULL (从左到右, 性能从差到好)
- ALL：全表扫描；index：全索引扫描；range：范围扫描；ref：非唯一性索引或唯一性索引的前缀索引；eq_ref：唯一索引扫描；const：主键索引扫描；system，要求数据库表中只有一条数据。
- key： 此列显示 MySQL 实际决定使用的键（索引）。 如果没有选择索引，键是 NULL。 要想强制 MySQL 使用或忽视 possible_keys 列中的索引，则在查询中使用 FORCE INDEX、 USEINDEX 或 者IGNOREINDEX。 如果这里为 NULL，则说明没有命中索引。
- Extra：此列如果出现 Using filesort （需要额外的步骤来发现如何对返回的行排序，系统会分配一块内存空间用于排序，如果需要排序的字段数据大小超过了，还需要临时文件来辅助排序）或者Using temporary （需要创建一个临时表来存储结果），说明查询需要优化。Using index：使用覆盖索引；Using Where：使用where条件Using index condition，使用普通索引，还需要回表查询。
其中 Extra 中的各种返回结果会在其他文章中结合具体案例进行分析，这里先做了解即可。
索引失效场景
条件字段函数操作
假设有如下表结构：
mysql> CREATE TABLE tradelog (
id int(11) NOT NULL,
tradeid varchar(32) DEFAULT NULL,
operator int(11) DEFAULT NULL,
t_modified datetime DEFAULT NULL,
PRIMARY KEY (id),
KEY tradeid (tradeid),
KEY t_modified (t_modified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
假设，现在已经记录了从 2016 年初到 2018 年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中 7 月份的交易记录总数。
mysql> select count(*) from tradelog where month(t_modified)=7;
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。 虽然优化器权衡后依然选择了走索引，但是这个索引没有办法通过树搜索功能快速定位，只能全索引扫描(遍历索引树)。
where 条件左侧即使是最简单的加1操作也算是函数操作，会放弃走树搜索功能。
隐式类型转换，本质上也是函数操作
mysql> select * from tradelog where tradeid=110717;
在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。
因为 tradeid 是字符串类型，对于优化器来说，这个语句相当于：
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
总结来说： 对索引字段做函数操作，优化器会放弃走树搜索功能，最终导致全表扫描。
再看一个正例：
select * from tradelog where id="83126";
where 条件右侧的字符串会转换成数字，正常使用索引，不会全表查询。
隐式字符编码转换
假设有一张表
mysql> CREATE TABLE trade_detail (
id int(11) NOT NULL,
tradeid varchar(32) DEFAULT NULL,
trade_step int(11) DEFAULT NULL, /*操作步骤*/
step_info varchar(32) DEFAULT NULL, /*步骤信息*/
PRIMARY KEY (id),
KEY tradeid (tradeid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());
insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');
如果要查询 id=2 的交易的所有操作步骤信息，SQL 语句可以这么写：
mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/
对应执行计划为：
在这个执行计划里，是从 tradelog 表中取 tradeid 字段，再去 trade_detail 表里查询匹配字段。因此，我们把 tradelog 称为驱动表，把 trade_detail 称为被驱动表，把 tradeid 称为关联字段。
我们看下这个 explain 结果表示的执行流程：
在执行步骤的第 3 步，如果单独把这一步改成 SQL 语句的话，那就是：
mysql> select * from trade_detail where tradeid=$L2.tradeid.value;
其中，$L2.tradeid.value 的字符集是 utf8mb4。
字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。
上述语句等同于下面这个语句：
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
CONVERT() 函数，在这里的意思是把输入的字符串转成 utf8mb4 字符集。
作为对比，我们换个写法：
mysql>select l.operator from tradelog l ,trade_detail d where d.tradeid=l.tradeid and d.id=4;
对应执行计划为：
可以看出，此时的驱动表变为了trade_detail，被驱动表变成了tradelog。
因为 tradelog 字符集为 utf8mb4，所以 where 条件左侧 l.tradeid 是不需要通过函数来转换字符集。
select operator from tradelog where traideid =$R4.tradeid.value;
那么如何优化呢？有两种做法：
- 一般来说，把两个表的字符编码统一即可，比如说把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4。
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
- 如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大， 或者业务上暂时不能做这个 DDL 的话，那就只能采用修改 SQL 语句的方法了。
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
查询条件长度超长处理
表结构如下：
mysql> CREATE TABLE table_a (
id int(11) NOT NULL,
b varchar(10) DEFAULT NULL,
PRIMARY KEY (id),
KEY b (b)
) ENGINE=InnoDB;
假设现在表里面，有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’， 假设现在执行语句是这么写的:
mysql> select * from table_a where b='1234567890abcd';
最理想的情况是，MySQL 看到字段 b 定义的是 varchar(10)，那肯定返回空呀。可惜，MySQL 并没有这么做。
这条 SQL 语句的执行很慢，流程是这样的：
索引最左匹配失效
关于这点在《MySQL索引大揭秘：优化查询的秘籍（下）》一文中讲解最左前缀原则时详细讲过，这里就不做重复介绍。
上述四种索引失效的情况，设计表和书写SQL时注意避免类似情况的发生。
