Mysql索引选择逻辑

原创
2020/02/02 14:01
阅读数 770

有时候我们会发现mysql可能出现选错索引的情况,要了解这个问题我们得先看看sql优化器是怎么选择索引的

索引选择逻辑

优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少

扫描行数是怎么判断的?

MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数

这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”。也就是说,这个基数越大,索引的区分度越好

MySQL 是怎样得到索引的基数的呢

采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数

而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计

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

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

其实索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行

如果使用索引,每次从索引上拿到一个值,都要回到主键索引上查出整行数据,这个代价优化器也要算进去的

MySQL 选错索引,主要还是没能准确地判断出扫描行数

既然是统计信息不对,那就修正。analyze table t 命令,可以用来重新统计索引信息

索引选择异常和处理

当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断 即使统计信息没错依然可能出现选错的情况,那我们应该如何处理呐?

第一种方法是,采用 force index 强行选择一个索引

第二种方法就是,我们可以考虑修改语句,引导 MySQL 使用我们期望的索引

第三种方法是,在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选 择,或删掉误用的索引

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部