in查询的底层原理

·  阅读 182

成本分析

方便理解,我先把id IN (6,8,2,5)这条查询转化为下面这条完整的SQL:

SELECT * FROM user WHERE id IN (6, 8, 2, 5)
复制代码

在MySQL中有一个配置参数eq_range_index_dive_limit,它的作用是一个等值查询(比如:in查询),其等值条件数小于该配置参数,则查询成本分析使用扫描索引树的方式分析,如果大于等于该配置参数,则使用索引统计的方式分析。使用扫描索引树的方式分析在MySQL内部叫做index dives,使用索引统计的方式分析在MySQL内部叫做index statistics

结合上面这条SQL,就是如果SQL中IN查询字段id的值出现的数量小于eq_range_index_dive_limit,则走索引树扫描分析查询成本,大于等于eq_range_index_dive_limit,则走索引统计的方式分析查询成本。

所以,本章标题的那个问题,即IN字段查询多少个值最合适?结合上面的分析,我的回答是IN查询的字段,该字段的值不要超过eq_range_index_dive_limit这个参数,让MySQL能够正确选择执行计划,保证SQL查询的性能。eq_range_index_dive_limit参数的默认值在5.7版本更新为200。

参数配置

关于eq_range_index_dive_limit这个参数如何查看,我们可以使用下面这条SQL查看:

SHOW VARIABLES LIKE '%dive%';
复制代码

MRR

聪明的MySQL在处理上面这个问题时,想到了一个把随机IO转换为顺序IO的办法,去提升辅助索引主键到聚簇索引查询的性能,而这个办法就是MRR。

相比原先按照主键6,8,2,5的顺序搜索聚簇索引,MRR的做法要聪明多了,因为按照6,8,2,5这个顺序搜索聚簇索引,这是一个随机查找,而转换为2,5,6,8后,搜索聚簇索引就变为顺序查找,这个性能相比前者一定会有很大提升的。

分类:
后端
标签:
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改