前言
本文简要介绍了优化器选择索引的依据，以及如何人为地引导优化器选择较好的执行方案。
[En]
This paper briefly introduces the basis for the optimizer to select the index and the ways to artificially guide the optimizer to choose a better execution scheme.
为什么会出现选错索引
可能是统计索引基数信息错误，导致优化器错选索引，也可能是 MySQL的 bug。
选择依据
优化器根据扫描的行记录的数量、返回表的次数、是否创建临时表以及是否对其进行排序来选择索引。
[En]
The optimizer selects the index based on the number of scanned row records, the number of times the table is returned, whether to create a temporary table, and whether to sort it.
索引扫描行数。通过取样的方式统计索引列上不同的值数量，取 N张数据页，统计页面上不同值的数量，然后估算总的不同数量（基数 cardinality），这也称之为索引的”区分度”。可以通过 SHOW INDEX FROM table_name来查看每个索引的基数。
对于这条执行语句 SELECT * FROM t WHERE a BETWEEN (1, 10000) AND b BETWEEN (50000, 100000) ORDER BY b LIMIT 1，从扫描行数上考虑，应该选择 a上的索引，但通过 MySQL执行计划发现，实际选择了 b索引。因为优化器考虑了需要根据 b排序，选择 b索引扫描获取记录可以避免再排序。但是，使用 a索引的查询耗时远比使用 b索引耗时低。
如何避免选错索引
1、强制使用索引
使用 force Index(a)强制 SQL执行时采用某个索引，比如 SELECT * FROM t force Index(a) WHERE a BETWEEN (1, 10000) AND b BETWEEN (50000, 100000) ORDER BY b LIMIT 1。
2、重新计算基数
因为 MySQL取样估计基数可能存在比较大的误差，导致优化器选择低效的执行方案。为了避免这个统计信息的问题，可以使用 Analyze TABLE t来重新统计信息。
3、选择更合适的索引或者删除误用的索引
提问
表结构
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;
创建过程，插入 100000 条数据
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i
如果在执行事务 B之前不开启事务 A，那么语句 explain select * from t where a between 10000 and 20000;扫描行数约为 10000行；当开启事务 A，则扫描行数约为 37000行，这是为什么呢？
解释：
不开启事务 A扫描行数为 10000行，因为 MySQL采用的是标记删除的方法，在 purge线程还未执行之前，索引树和表数据并没有清除。当新插入 100000行数据时，因为主键和已经删除的相同，所以会直接复用之前删除的空间，所以优化器抽样判断扫描的行数是 10000行。
开启事务 A扫描行数为 37000行，因为事务 A开启了一致性读，于是新插入数据时，不能复用已经删除的空间，必须开辟新的空间存储，使得索引数据页的数据更加密集，从而优化器抽样判断扫描行数是 37000行。
Original: https://www.cnblogs.com/flowers-bloom/p/select-error-index.html
Author: flowers-bloom
Title: MySQL45讲之优化器选错索引
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/508167/
转载文章受原作者版权保护。转载请注明原作者出处！