Mysql选错索引案例分析分析：从图可以看出，Q1扫描了10万行，是全表扫描，执行时间是40毫秒；Q2扫描了10001行

1.准备工作：

#创建一张表

create TABLE test_table2( id int(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键', a int(11) NOT NULL COMMENT '测试字段a', b int(11) NOT NULL COMMENT '测试字段b' , primary key (id), key a (a), key b (b) )ENGINE=InnoDB DEFAULT CHARSET=utf8m64 COMMIT='测试表';

#开启慢查询日志

set global long_query_time = 0;

set global slow_query_log='ON';

set global slow_query_log_file='/data/instance-1-slow.log';

#验证

select sleep(2);

#使用存储过程生成100000条数据

delimiter ;;

create procedure idata1()

begin

declare i int; 
set i=1; 
while(i<=100000)do 
	insert into `test_table2`(`a`,`b`) values(i, i); 
	set i=i+1; 
	end while;
end;;
delimiter ;
call idata1();

通过两个sessionA，sessionB实现如下操作

select @@global.tx_isolation,@@tx_isolation,version(),"session A";

start TRANSACTION with consistent snapshot;

select @@global.tx_isolation,@@tx_isolation,version(),"session B";

delete from test_table2;

call idata1();

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

#Q1

select * from test_table2 where a between 10000 and 20000;

#Q2

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

commit;

2.运行结果分析：从图可以看出，Q1扫描了10万行，是全表扫描，执行时间是40毫秒；Q2扫描了10001行，执行了18毫秒，也就是优化器选错了索引，导致更长的执行时间。这个例子对应的是平常不断删除历史数据和新增数据的场景。

3.优化器逻辑作用之一：选择索引，即找到一个最优的执行方案，并用最小的代价去执行语句。判断扫描行数方法：Mysql在真正开始执行语句之前，并不能准确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录书，这个统计信息就是索引的区分度。

基数：一个索引上不同的值的个数。基数越大，索引的区分度越好。使用show index命令可查看索引基数 4.Mysql得到索引基数的方法是采样统计（整张表一行行统计虽准确但代价太高）

过程：采样统计的时候，InnoDB默认选择N个数据页，统计这些页面上不同的值，得到一个平均值，乘以这个索引的页面数，就得到了该索引的基数。数据表会持续更新，索引统计信息也不会固定不变。当变更数据超过1/M时，会自动触发重新做一次索引统计。

5.Mysql中有两种存储索引统计的方式，通过innodb_stats_persistent的值来选择：

设置为on时，表示统计信息会持久化存储，默认N是20，M是10 设置为off时，表示统计信息只存储于内存中，默认N是8，M是16 之前问题的主要原因是Mysql没能准确判断出扫描行数解决方法：使用analyze table命令重新统计索引信息来修正 6.索引选择异常和处理

采用force index强行选择一个索引修改语句引导Mysql使用期望的索引有些场景下，可新建一个更合适的索引，提供优化器做选择，或者删除误用的索引