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

/*
mysql扫描行数是如何判断的?
mysql在真正的执行语句之前是不知道扫描行数的,只能根据统计信息来估算记录数
这个统计信息就是 区分度 ,显然一个索引上不同的值越多,这个索引的区分度就越好,而一个索引上不同值的个数,我们称之为 基数 ,也就是说这个基数越大,索引的区分度越好

我们可以使用 show index 方法,查看一个索引的基数,如: show index from t 查看t表的索引基数

那么mysql是怎么获取基数的呢?(采样统计)
为什么要采样统计呢?因为一张表一行一行查出来精确统计,成本太高
采样统计的时候,innodb是选择n个数据页,统计这些页面上的不同值,取得一个平均数,然后乘以这个索引的页数,就会的得到这个索引的基数
数据表中的数据是随时发生改变的所以这个基数也会改变所以,当变更的数据行数大于 1/M的时候会自动触发重新做一次索引统计

在mysql中有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent的值来选择
设置为on的时候,表示统计信息会持久化存储,这时N默认是20,M是10
设置为OFF的时候,统计信息只存储在内存中,这时N是8,M是16

由于是采样统计,所以N是 20 还是8 ,这个基数都是很容易不准的

除了扫描行数的不准之外,还有优化器会选择成本低的执行方式,成本低不只是扫描行数少。
如:一张表有10万条数据,id索引和a索引,使用id索引需要扫描10row  而通过a索引需要15000 row 但是,使用a索引这里需要进行再一次的回表,优化器将这个成本也算进去了,而id是直接在id主键上进行的扫描,没有额外的代价


统计信息不对 修正
analyze table 表名
如果通过explain 这个命令查看的row 和实际相差比较大,就可以使用这个命令修正(重新采样统计)


强制使用指定索引(  force index(索引名) )
select * from t force index(a) where a between 10000 and 20000;


索引选择异常和处理
1.通过force index 强行使用指定索引
2.修改表结构,引导优化器使用我们想要的索引
3,在合适的产景下可以考虑添加一个合适的索引来使用

*/
原文地址:https://www.cnblogs.com/LF-place/p/11537555.html