MySQL 索引 - 选取索引[选错索引]

1.应用场景

弄清楚索引的本质， 以及如何使用索引，能帮助我们解决一些性能问题

2.学习/操作

1. 前言 大家都知道，MySQL 一个表中可以创建多个索引，但是在执行一条查询语句的时候，MySQL 只能选一个索引，如果我们没有指定 MySQL 使用某个索引，那么就是由 MySQL 的优化器来决定要使用哪个索引了，然而，MySQL 也是会有选错的时候。 前面的文章，我们有介绍过执行一条查询 SQL 语句分别会经历那些过程，执行一条SQL语句都经历了什么？ 存在多个索引的情况下，优化器一般会通过比较扫描行数、是否需要临时表以及是否需要排序等，来作为选择索引的判断依据。 2. 实践验证 这里GUI 工具是使用Navicat 2.1 新建表 我们先来新建一个表，创建两个普通索引。 CREATE TABLE `t` (   `id` int(11) NOT NULL,   `a` int(11) DEFAULT NULL,   `b` int(11) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `a` (`a`),   KEY `b` (`b`) ) ENGINE=InnoDB; 2.2 插入数据 这里我们使用存储过程往表里插入 10w 测试数据。 如果对 MySQL 的存储过程不熟悉，请看我在代码中的注释，应该能看得懂得。 #定义分割符号，MySQL 默认分割符为分号;，这里定义为 // #分隔符的作用主要是告诉MySQL遇到下一个 // 符号即执行上面这一整段SQL语句 delimiter // #创建一个存储过程，并命名为 testData create procedure testData()  #下面这段就是表示循环往表里插入10w条数据 begin   declare i int;   set i=1;   while(i<=100000)do     insert into t values(i, i, i);     set i=i+1;   end while; end //  #这里遇到//符号，即执行上面一整段SQL语句 delimiter ; #恢复mysql分隔符为; 执行结果：  开始插入数据 call testData(); #调用存储过程 执行时间大概要几分钟 某一个时刻  另外某一个时刻 执行完毕： 可以看到有十万条记录 2.3 执行一条查询语句 数据插入完成后，我们来看下面这条 SQL 语句。 SELECT * FROM t WHERE (a BETWEEN 1 AND 1000) AND (b BETWEEN 50000 AND 100000) ORDER BY b LIMIT 1; 由于主键id、a、b 三个字段的值其实都是一样的，所以其实这条 SQL 语句的结果集为空，没有符合条件的记录。 2.4 分析上述SQL语句，如何选择索引的？ 我们来看看 MySQL 该是怎么选择索引的，这里有三个索引可用，分别是主键索引、索引a、索引b。 如果选择主键索引虽然可以减少回表过程，但是只能走全表扫描，需要扫描 10w 条记录。 如果选择索引 a，则只需在 a 索引上扫描 1k 条记录，然后回到主键索引上过滤掉不满足 b 条件的记录，最后再按 b 排序即可。 如果选择索引 b，则需要在 b 索引上扫描 5w 条记录，然后同样回到主键索引上过滤掉不满足 a 条件的记录，因为索引有序，所以使用 b 索引不需要额外排序。 我们来使用执行计划看下 MySQL 究竟会选择哪个索引。 mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1; +----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+ | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows  | Extra                              | +----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+ |  1 | SIMPLE      | t     | range | a,b           | b    | 5       | NULL | 50128 | Using index condition; Using where | +----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+ 1 row in set (0.12 sec) 截图 可以看出 MySQL 是选择使用索引 b，虽然扫描行数要多一些，但因为索引本身是有序的，使用索引 b 可以避免排序，MySQL 认为这个排序的代价高于扫描行数。 上面这个选择是 MySQL 优化器内部的分析，那么实际情况又如何呢？ 我们可以分别执行一下 SQL 语句，使用 force index(a) 强制使用索引 a 来对比下， 看下两者具体花费的时间。 mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1; Empty set (0.65 sec) mysql> select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1; Empty set (0.05 sec) 截图： 从结果可以看到其实使用索引 a 显然速度会更快，所以这就是属于 MySQL 选错了索引的情况。 那我们怎么避免这种情况呢？ 我们可以把 SQL 语句改成下面这样的，即把 order by b 改成 order by b, a select * from t where a between 1 and 1000 and b between 50000 and 100000 order by b,a limit 1; 截图： 这样的话，在 MySQL 看来无论是使用索引 a 还是索引 b 都需要排序了，那就只能选择扫描行更少的索引了，所以 MySQL 会选择索引 a，从而达到避免 MySQL 选错索引的目的，我们可以看下优化后的这条 SQL 的执行计划。 mysql> explain select * from t where a between 1 and 1000 and b between 50000 and 100000 order by b,a limit 1; +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                              | +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+ |  1 | SIMPLE      | t     | range | a,b           | a    | 5       | NULL |  999 | Using index condition; Using where; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+ 1 row in set (0.00 sec) 截图： 大多数情况下，MySQL 都会选择正确的索引，选错索引算是比较少见的特殊情况了。 文中的例子也是个特例，仅是给大家提供一个分析思路，当你遇到一些已经使用了索引但依然比较慢的 SQL 语句的时候，可以尝试分析是否是 MySQL 选错了索引的原因。 其实还有一些情况，会导致 mysql 选错索引，就是 MySQL 预估扫描行的数据不够准确，而这个不准确通常是数据表有频繁的删除或更新操作导致的数据空洞造成的，关于这个原因，会在后面再补充。

3.问题/补充

TBD

4.参考

https://mp.weixin.qq.com/s/zNZczvY9Jzo99wMTl2Kkmg

后续补充

...