MySQL选错索引的原因以及解决方案

MySQL中,可以为某张表指定多个索引,但在语句具体执行时,选用哪个索引是由MySQL中执行器确定的。那么执行器选择索引的原则是什么,以及会不会出现选错索引的情况呢?

先看这样一个例子:

创建表Y,设置两个,创建一个存储过程用于插入数据。

　　

MySQL: 5.7.27,隔离级别:RR

　　 　　 　　 　　创建表“Y”( 　　“id”int(11)不是零AUTO_INCREMENT, 　　' a ' int(11)默认为空, 　　' b ' int(11)默认为空, 　　主键(“id”), 　　关键' a ' (' a '), 　　关键“b”(b) 　　)引擎=InnoDB; 　　 　　 　　 　　分隔符;; 　　创建过程idata () 　　开始 　　声明我int; 　　组i=1; 　　而(i<=100000) 　　插入Y (a, b)值(我); 　　我=+ 1; 　　结束时; 　　结束;; 　　分隔符; 　　调用idata (); 　　 　　

查看如下事务:

　　

　　 　　 　　 　　 　　 　　 　　 　　会话的 　　会话B 　　 　　 　　 　　 　　开始与一致的快照事务; 　　 　　 　　 　　 　　删除从t; 　　 　　 　　 　　调用idata (); 　　 　　 　　 　　解释select * Y, 10000年和20000年之间的; 　　 　　 　　 　　解释select *从Y部队指数(a)在10000年和20000年之间; 　　 　　 　　提交; 　　 　　 　　 　　

　　

如果单独执行会话B中select * Y, 10000年和20000年之间的;,毫无疑问会选择了这个索引。

　　

但如果安装会话,会话B的顺序执行,发现索引的选择如下:

　　

　　

可以发现,在会话B的场景下,执行器却没有选择一所在的索引,而是选择基于主键索引的全表扫描。

　　 　　 　　设置long_query_time=0; 　　——将慢查询日志打开,并将阙值设为0。在记录的日志中,可以发现MySQL并没有选择一所在的索引,同时花费了更长的时间。 　　 　　

这样看,MySQL的优化器不一定每次都能选择合适的索引。想要理解出现该现象的原因,就要从优化器的选择逻辑说起。

　　

<>强优化器
　　

　　

MySQL中优化器的目的就是找到一个,从而用最小的代价去执行语句。

　　

优化器在选择索引时,主要会考虑如下的因素:

　　

　　<李>扫描的行数:扫描的行数越少,就证明访问磁盘数据的次数越少,消耗的CPU资源就越少。 　　<李>有没有涉及到临时表 　　<李>排序李 　　

　　

<>强关于扫描行数的确定

　　

　　

MySQL在执行语句前,其实并不能准确的计算出扫描的行数,而是通过数学统计信息来估算记录数。这个统计信息被称为索引的“区分度”,在索引上不同的值越多,区分度就越高。在一个索引上不同值的个数,称为“基数”。基数越大,索引的区分度越好。

　　

　　

这里的基数就是索引的基数,但基数并不是完全准确的.MySQL是在获取基数时,实际上是采用的方式。

　　

　　

计算时,会选择N个数据页,并统计这些页面上的不同值,得到一个平均值,然后乘以该索引的页面数,然后得到的就是索引的基数。

　　 　　

在MySQL中,有两种存储索引的方式,可通过设置innodb_stats_persistent来切换:

　　

　　<李>时:表示统计信息会持久化存储,默认N为20 M为10。 　　<李>时,统计信息仅会存储在内存中,默认N为8,M为16。
　　李 　　

　　

由于表中数据是不断变化的,所以当更新的值超过1/M时,会自动触发索引统计。

　　

但需要注意的是,

。 　　

　　

之前看的到,执行<代码> Select * Y, 10000年和20000年之间的代码> 　　

之后执行<代码> select *从Y部队指数(a)在10000年和20000年之间> 　　

而且更奇怪的是,虽然37116行的预估行数不太合理,但也远小于全表扫描的100015年,为什么优化器还是选择全表扫描呢?

　　

首先先看第二个问题,选择100015的原因是因为如果使用索引的一话,除了需要在一个索引扫描外,还需要回表,主键索引上的查询代价,优化器也需要算进去,所以选择了全表扫描。

MySQL选错索引的原因以及解决方案