10 | MySQL为什么有时候会选错索引？
使用哪个索引是由 MySQL 来确定的
可能遇到的情况：一条本来可以执行得很快的语句，却由于 MySQL 选错了索引，而导致执行速度变得很慢
先建一个简单的表，表里有 a、b 两个字段，并分别建上索引：
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；
然后，我们往表 t 中插入 10 万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)。
分析一条 SQL 语句：
select * from t where a between 10000 and 20000;
使用explain查看命令执行情况：
这条查询语句的执行也确实符合预期，key 这个字段值是’a’，表示优化器选择了索引 a。
接着做如下操作：
session A 开启了一个事务。随后，session B 把数据都删除后，又调用了 idata 这个存储过程，插入了 10 万行数据
这时候，session B 的查询语句 select * from t where a between 10000 and 20000 就 不会再选择索引 a 了
使用对照 force index(a) 证明优化器真的选错了索引
set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
- 第一句话是将慢查询日志的阈值设置为0。表示该线程的下一条语句将记录在慢查询日志中
[En]
the first sentence is to set the threshold of the slow query log to 0. * indicates that the next statements of this thread will be recorded in the slow query log * *
- 第二句，Q1 是 session B 原来的查询；
- 第三句，Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比。
如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。
而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。
优化器估计这两种选择的成本，因此，优化器认为直接扫描主键索引更快。然后就错了。
[En]
The optimizer estimates the cost of these two choices, and as a result, the optimizer thinks it is faster to scan the primary key index directly. And then it was wrong.
例子对应的是我们 平常不断地删除历史数据和新增数据的场景
优化器的逻辑
优化器选择索引的目的是找到最佳执行方案，并以最低成本执行语句。
[En]
The purpose of the optimizer in selecting the index is to find an optimal execution scheme and to execute the statement at the minimum cost.
在数据库里面， 扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
扫描的行数不是唯一的标准。优化器还会根据是否使用临时表、是否排序等因素进行综合判断。
[En]
The number of rows scanned is not the only criterion. The optimizer will also make a comprehensive judgment based on factors such as * whether to use temporary tables * and * whether to sort * .
扫描行数是怎么判断的？
MySQL 在真正开始执行语句之前， 并不能精确地知道满足这个条件的记录有多少条，而只能 根据统计信息来估算记录数。
这个统计信息就是 索引的”区分度”。一个索引上 不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为” 基数“（cardinality）。也就是说， 这个基数越大，索引的区分度越好。
下图为表 t 的 show index 的结果 。虽然这个表的每一行的 三个字段值都是一样的，但是在统计信息中，这三个索引的 基数值并不同，而且其实都不准确。
MySQL 是怎样得到索引的基数
MySQL 使用采样统计的方法，因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是 代价太高了，所以只能选择”采样统计”。
采样统计的时候，InnoDB 默认会 选择 N 个数据页， 统计这些页面上的不同值， 得到一个平均值，然后 乘以这个索引的页面数，就得到了这个索引的基数S。
数据表是会持续更新的，索引统计信息也不会固定不变。所以， 当变更的数据行数X，X/S超过 1/M 的时候，会自动触发重新做一次索引统计。
- 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
- 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。
索引统计信息只是一个输入，对于特定语句，优化器还确定要扫描多少行来执行该语句本身。考虑回到谈判桌前。
[En]
Index statistics are just an input, and for a specific statement, the optimizer also determines how many lines to scan to execute the statement itself. Consider going back to the table.
如果发现 explain 的结果预估的 rows 值跟实际情况差距比较大，使用analyze table t来 重新统计索引信息
不是由于索引统计信息不准确导致索引选错的情况：
依然是基于表 t，另外一个语句：
select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
有条件地，该查询没有限定的记录，因此返回一个空集合。
[En]
Conditionally, this query does not have a qualified record, so an empty collection is returned.
人工判断会选择哪一项指标？
[En]
Which index will be chosen by manual judgment?
如果使用索引 a 进行查询，那么就是扫描索引 a 的 前 1000 个值，然后 取到对应的 id， 再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行。
如果使用索引 b 进行查询，那么就是扫描索引 b 的 最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。
explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
key 字段显示，这次优化器选择了索引 b，而 rows 字段显示需要扫描的行数是 50198。
修改语句，引导 MySQL 使用我们期望的索引。
法一：
把”order by b limit 1″ 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
之前优化器选择使用索引 b，是 因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历）， 所以即使扫描行数多，也判定为代价更小。
现在 order by b,a 这种写法，要求按照 b,a 排序， 就意味着使用这两个索引都需要排序。 因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引 a。
！注意：
这种修改并不是通用的优化手段，只是刚好在这个语句里面有 limit 1，因此如果有满足条件的记录， order by b limit 1 和 order by b,a limit 1 都会返回 b 是最小的那一行，逻辑上一致，才可以这么做。
法二：
select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
在这个例子里， 我们用 limit 100 让优化器意识到，使用 b 索引代价是很高的。其实是我们根据数据特征诱导了一下优化器，也不具备通用性。
法三：
在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
总结：
由于索引统计信息不准确导致行数判断失误的问题，你可以用 analyze table 来解决。
对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。
Q:在构造第一个例子的过程中，通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。
而如果没有 session A 的配合，只是单独执行 delete from t 、call idata()、explain 这三句话，会看到 rows 字段其实还是 10000 左右。这是什么原因呢？
A:
delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是覆盖了原来的 10 万行。
但是，session A 开启了事务 并没有提交，所以之前插入的 10 万行数据是不能删除的。 这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。这样，索引 a 上的数据其实就有两份。
但是主键索引不是通过采样生成的，是直接按照表的行数来估计的。而表的行数，优化器直接用的是show table status 的值。
Original: https://www.cnblogs.com/ydssx7/p/16512420.html
Author: ydssx
Title: MySQL实战45讲 10
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505162/
转载文章受原作者版权保护。转载请注明原作者出处！