10mysql为什么有时候选错索引

MySQL为什么有时候会选错索引

创建时间：2021年11月15日01:50:14
编辑时间：2021年11月16日10:04:14、2021年11月17日10:14:58、2021年11月22日11:07:46

——————————————————————————————

问：MySQL优化器选择索引的依据是什么？

答：扫描行数，是否使用临时表、排序等。

扫描行数

问：MySQL怎么判断扫描行数的？

答：索引的区分度是一个重要的行数估算参数。其参数的依据是采样统计——选择N个数据页，统计上面不同值的平均值，乘以索引页数。

-- 查询命令
show index from t;

其中，cardinality的解释在官方文档中如下：

*An estimate of the number of unique values in the index. To update this number, run ANALYZE TABLE or (for MyISAM tables) myisamchk -a.

Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

（个人翻译：是唯一的索引个数的估算，要更新这个值，可以运行ANALIZY TABLE或者仅针对MyINSAM表执行myisamchk -a，

索引区分度建立在基于整数的表统计，所以这个值即使对那些很小的表来说，也并非一定准确。索引区分度越高，则被选中的概率越高。）

索引区分度在变更数据量达到1/M时进行刷新。

其计算不会将被标记删除的数据纳入其中。

索引区分度只是优化器选择索引的一个重要估算参数，最终还是根据优化器的估算结果（explain中的row）作为判定。

问：怎么获得正确的扫描行数？

答：

先执行

analyze table table_name 

问：MySQL的索引统计信息的存储设置是什么？

答：有两种方式

set innodb_stats_persistent = 'ON' -- 持久化存储，N-20 M-10

set innodb_stats_persistent = 'Off' -- 内存存储，N-8 M16

其中，innodb_stats_persistent的官方解释如下：

Specifies whether InnoDB index statistics are persisted to disk. Otherwise, statistics may be recalculated frequently which can lead to variations in query execution plans. This setting is stored with each table when the table is created. You can set innodb_stats_persistent at the global level before creating a table, or use the STATS_PERSISTENT clause of the CREATE TABLE and ALTER TABLE statements to override the system-wide setting and configure persistent statistics for individual tables.

（个人翻译：指定Innodb索引统计信息是否持久化到磁盘。如果否，则这个影响表查询执行计划的统计信息可能发生频繁刷新。在创建表的时候，在每张表中进行该设置，你可以在创建表之前执行全局级别的innodb_stats_persistent设置，或者在CREATE_TABLE的列STATS_PERSISTENT利用ALTER_TABLE语句进行系统级的覆盖修改，并为单个的表进行配置。 ）

PS：（暂定在将来做一篇博客介绍索引区分度）

实战

问：复现一个MySQL选错索引的场景，并解释原因。

答：建表和初始数据脚本如下

CREATE TABLE `t` (
  `id` int(11) 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 t (`a`,`b`) values(i, i);
    set i=i+1;
  end while;
end;;
delimiter ;

切记，表必须是自增主键，并且脚本赋值时，必须由数据库自己生成主键，而不是脚本赋值主键

执行脚本初始化数据

call idata();

之后，

事务A启动事务

start transaction with consistent snapshot;

事务B

delete from t;
call idata();
set global slow_query_log='ON';
set long_query_time = 0;
select * from t where a between 10000 and 20000;
select * from t force index(a)  where a between 10000 and 20000;

查询慢日志，得到：

使用explain命令可发现，除非强制指定使用索引a，否则默认全表扫描。

宁愿全表索引也不愿索引a，是优化器认为37000的扫描次数中的回表会开销更高的资源，所以选择全表主键扫描，不需要额外开销。

事实证明优化器分析错误。

PS：常用技巧

show VARIABLES like '%aa%'; -- 查看指定的参数配置

cat /dev/null > /var/lib/mysql/localhost-slow.log
-- 清空日志文件内容

问：为什么索引a的扫描次数有误？

答：

事务1启动一致性读视图后，事务2的删除对数据进行了标记删除，而此前有一个事务可重复读时，新的数据插入不会占用被删除数据的空间（遵循MVCC），所以另起炉灶后，优化器计算要扫描的普通索引数量翻几倍了。

问：为什么主键索引树预估扫描行数没有翻倍？

答：因为优化器估算主键是直接按照表的行数进行的（取的并非就是主键索引区分度）。

show table status

问：为什么扫描行数是37000多行，不是20000多行？

答：不知道。

排序

问：复现一个因为使用了排序而导致索引失效的场景。

答：上图的表结构和初始化数据情况下，执行

select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;

该语句走a索引查询效果更好，但优化器最终选择走b索引。

因为

优化器认为使用索引b可以避免排序上的性能开销，并且使用了limit 1后，优化器认为如果符合b在50000到100000的数据只有少得可怜的几条的话，扫描得到的行数很少，而如果limit的是一个比较大的值的话，优化器可能会认为扫描的数据量会过大，还是老老实实走a索引更好。

解决途径：

打破其认为的能利用上的索引排序（order by b，a），或者更改limit值，或者删除b索引等等。