为什么MySQL会选错索引？

MySQL 中，可以为某张表指定多个索引，但在语句具体执行时，选用哪个索引是由 MySQL 中执行器确定的。那么执行器选择索引的原则是什么，以及会不会出现选错索引的情况呢？

示例：创建表 Y，设置两个普通索引, 创建一个存储过程用于插入数据。

-- MySQL版本：
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.28-log |
+------------+
1 row in set (0.00 sec)

-- 隔离级别: RR
mysql> set global transaction_isolation='REPEATABLE-READ' ;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)


CREATE TABLE `Y` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
     insert into Y (`a`,`b`) values(i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

执行如下事务：
Session A：start transaction with consistent snapshot;
Session B：delete from t;
Session B：call idata();
Session B：explain select * from Y where a between 10000 and 20000;
Session B：explain select * from Y force index(a) where a between 10000 and 20000;
Session A：commit;

按照上面的顺序执行事务 Session A，Session B ，发现索引的选择如下：

table         ：此次查询访问的表（单表无所谓，多表连接查询需要关注）
type          ：索引查询的类型（ALL、index、range范围、ref等值、eq_ref、const(system)、NULL）
possible_keys ：可能会应用的索引
key           : 最终选择的索引
key_len       ：索引覆盖长度，主要是用来判断联合索引应用长度。
rows          ：此次查询需要扫描的行数（预估值）
Extra         ：额外信息

单独执行 Session B 中执行毫无疑问会选择 a 这个索引。

explain select * from Y where a between 10000 and 20000;

可以发现，在 Session B 的场景下，执行器却没有选择 a 所在的索引，而是选择基于主键索引的全表扫描。

set long_query_time=0;
将慢查询日志打开，并将阙值设为 0. 在记录的日志中，
可以发现 MySQL 并没有选择 a 所在的索引，同时花费了更长的时间。

这样看，MySQL 的优化器不一定每次都能选择合适的索引。想要理解出现该现象的原因，就要从优化器的选择逻辑说起。MySQL 中优化器的目的就是找到一个最优的执行方案，从而用最小的代价去执行语句。

优化器在选择索引时，主要会考虑如下的因素：

• List item
• 扫描的行数
• 有没有涉及到临时表
• 排序

MySQL 在执行语句前，其实并不能准确的计算出扫描的行数，而是通过数学统计信息来估算记录数。这个统计信息被称为索引的“区分度”，在索引上不同的值越多，区分度就越高。在一个索引上不同值的个数，称为“基数”。基数越大，索引的区分度越好。

这里的 Cardinality 就是索引的基数，但基数并不是完全准确的。MySQL 是在获取基数时，实际上是采用采样统计的方式。

计算时，会选择 N 个数据页，并统计这些页面上的不同值，得到一个平均值，然后乘以该索引的页面数，然后得到的就是索引的基数。

在 MySQL 中，有两种存储索引的方式，可通过设置 innodb_stats_persistent 来切换：
on 时：表示统计信息会持久化存储，默认 N 为 20，M 为 10
off 时，统计信息仅会存储在内存中，默认 N 为 8，M 为 16

由于表中数据是不断变化的，所以当更新的值超过 1/M 时，会自动触发索引统计。

但需要注意的是，由于是采样统计，所以基数的值不是准确的。

预估扫描行数的错误

Select * from Y where a between 10000 and 20000 ;

之前看到，执行预估的行数是 200110，这个是能理解的，因为走的是全表扫描。

select * from Y force index(a) where a between 10000 and 20000 ;

之后执行 预估的行数是 37116，这个就不能理解了，理想的情况下应该是 10001 行 (需要遍历到 20001)。

而且更奇怪的是，虽然 37116 行的预估行数不太合理，但也远小于全表扫描的 200110，为什么优化器还是选择全表扫描呢？

首先先看第二个问题，选择 200110 的原因是因为如果使用索引 a 的话，除了需要在 a 索引扫描外，还需要回表，主键索引上的查询代价，优化器也需要算进去，所以选择了全表扫描。

这时再看第一个问题，为什么没有得到正确的行数。这个就和一致性视图有关了，首先 Session A 中，开启了一致性视图，并没有提交。之后的 Session 清空了 Y 表后，又重新创建了相同的数据，这时每行数据都有两个版本，旧版本是 delete 前的数据，新版本是标记为删除的数据。所以索引 a 上的数据其实有两份。也就造成了行数的预估错误。

mysql 是通过标记删除的方法来删除记录的，并不是在索引和数据文件中真正的删除。而且由于一致性读的保证，不能删除 delete 的空间，再加上 insert 的空间。导致统计信息有误。

选用错误索引的解决办法对于行数预估错误的情况， 可采用如下的方法：

• 如果遇到 EXPLAIN 和预估的行数，数值相差较大时，可以通过analyze table 来重新统计索引信息。
  
• 直接通过 force index 强制指定需要使用的索引，不让优化器进行判断。但使用 force 也可能带来一些问题：

1. 迁移数据库时，语法不支持

2. 不容易变更并且不太方便，因为选错索引的情况一般不会经常发生，在生产环境出现问题后，才需要改代码，但还需要重新进行上线测试，部署。

那知道的选错索引的情况，再总结一下MySQL在不走索引的情况：

1 没有查询条件，或者查询条件没有建立索引

全表扫描 ：select * from city; 
SQL注入：select * from city where id=1001 or  1=1;

2 查询结果集是原表中的大部分数据，15-25%以上不走。

Mysql表t100w  num列有索引   
select * from t100w where num > 1000；    ----> 全表扫描

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。 MySQL的预读功能有关。

可以通过精确查找范围或者有大于配合小于一起使用，达到优化的效果。

where id = 1000000
where id > 500000 and id < 600000
limit 100

3 统计信息不真实（过旧）不走索引

对于表内容变化比较频繁的情况下，本身是有自我维护的能力，但是也有可能会出现索引失效。

help--->help contents--->help Table Maintenance
方法一：ANALYZE TABLE world.city;
方法二：OPTIMIZE TABLE  world.city;
方法三：删除索引重建

4 查询条件出现计算类
使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)，很产生严重的性能影响，

错误的例子：select * from test where id-1=9; 
正确的例子：select * from test where id=10;

5 隐式转换导致索引失效
这一点应当引起重视.也是开发中经常会犯的错误

错误的例子：
create table tab (id int,name char(10),telnum varchar(11));
insert into tab values (1,"a","110"),(2,"b","120"),(3,"c","119");
select * from tab;

alter table tab add index inx_tel(telnum);
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | char(10)    | YES  |     | NULL    |       |
| telnum | varchar(11) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc select * from tab where telnum="110";
mysql> desc select * from tab where telnum=110;

6 <> ，not in 不走（辅助）索引，走聚簇索引（range）

DESC select * from city where countrycode <> "CHN";
DESC select * from city where id <> 1;

单独的>,<,in 有可能走，也有可能不走，和结果集有关15%-25%，尽量结合业务添加limit
唯一值比较多的时候，or或in 可以修改成union all

EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');

改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119';

7 like “%_” 百分号在最前面不走

range索引扫描: EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  
不走索引:      EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  

%linux%类的搜索需求，可以使用elasticsearch 或者 mongodb 专门做搜索服务的数据库产品。

调整索引的思路

1 (必须的) 建表时一定要有主键,一般是个无关自增列数字列。
a 降低索引树高度。
b 如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。
A 使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；
B 该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；除此以外，如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时
2 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分 (2) 可以将此列和其他的查询类,做联合索引

select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

3 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。
4 限制索引的数目

索引的数目不是越多越好。可能会产生的问题: 1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。3) 优化器的负担会很重,有可能会影响到优化器的选择。
5 大表加索引,要在业务不繁忙期间操作 推荐使用PT-OSC。
6 尽量少在经常更新值的列上建索引

更多精彩内容欢迎关注微信公众号。