MySQL SQL优化教程之in和range查询

2023-07-08 22:30:08 72

首先我们来说下in()这种方式的查询。在《高性能MySQL》里面提及用in这种方式可以有效的替代一定的range查询，提升查询效率，因为在一条索引里面，range字段后面的部分是不生效的。使用in这种方式其实MySQL优化器是转化成了n*m种组合方式来进行查询，最终将返回值合并，有点类似union但是更高效。同时它存在这一些问题：

老版本的MySQL在IN()组合条件过多的时候会发生很多问题。查询优化可能需要花很多时间，并消耗大量内存。新版本MySQL在组合数超过一定的数量就不进行计划评估了，这可能导致MySQL不能很好的利用索引。

这里的“一定数量”在MySQL5.6.5以及以后的版本中是由eq_range_index_dive_limit这个参数控制（感谢@叶金荣同学的指点）。默认设置是10，一直到5.7以后的版本默认会修改成200，当然我们是可以手动设置的。我们看下5.6手册中的说明：

Theeq_range_index_dive_limitsystemvariableenablesyoutoconfigurethenumberofvaluesatwhichtheoptimizerswitchesfromonerowestimationstrategytotheother.Todisableuseofstatisticsandalwaysuseindexdives,seteq_range_index_dive_limitto0.TopermituseofindexdivesforcomparisonsofuptoNequalityranges,seteq_range_index_dive_limittoN+1.
eq_range_index_dive_limitisavailableasofMySQL5.6.5.Before5.6.5,theoptimizerusesindexdives,whichisequivalenttoeq_range_index_dive_limit=0.

也就是说：

1.eq_range_index_dive_limit=0只能使用indexdive
2.0 3.eq_range_index_dive_limit>N只能使用indexdive

indexdive与indexstatistics是MySQL优化器对开销代价的估算方法，前者统计速度慢但是能得到精准的值，后者统计速度快但是数据未必精准。

theoptimizercanestimatetherowcountforeachrangeusingdivesintotheindexorindexstatistics.

在MySQL5.7版本中将默认值从10修改成200目的是为了尽可能的保证范围等值运算（IN()）执行计划尽量精准，因为IN()list的数量很多时候都是超过10的。

说在前面

今天文章的主题有两个：

range查询与索引使用
eq_range_index_dive_limit的说明

range查询与索引使用

SQL如下：

SELECT*FROMpre_forum_postWHEREtid=7932552AND`invisible`IN('0','-2')
ORDERBYdatelineDESCLIMIT10;

索引如下：

+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|pre_forum_post|0|PRIMARY|1|tid|A|NULL|NULL|NULL||BTREE|||
|pre_forum_post|0|PRIMARY|2|position|A|25521392|NULL|NULL||BTREE|||
|pre_forum_post|0|pid|1|pid|A|25521392|NULL|NULL||BTREE|||
|pre_forum_post|1|fid|1|fid|A|1490|NULL|NULL||BTREE|||
|pre_forum_post|1|displayorder|1|tid|A|880048|NULL|NULL||BTREE|||
|pre_forum_post|1|displayorder|2|invisible|A|945236|NULL|NULL||BTREE|||
|pre_forum_post|1|displayorder|3|dateline|A|25521392|NULL|NULL||BTREE|||
|pre_forum_post|1|first|1|tid|A|880048|NULL|NULL||BTREE|||
|pre_forum_post|1|first|2|first|A|1215304|NULL|NULL||BTREE|||
|pre_forum_post|1|new_auth|1|authorid|A|1963184|NULL|NULL||BTREE|||
|pre_forum_post|1|new_auth|2|invisible|A|1963184|NULL|NULL||BTREE|||
|pre_forum_post|1|new_auth|3|tid|A|12760696|NULL|NULL||BTREE|||
|pre_forum_post|1|idx_dt|1|dateline|A|25521392|NULL|NULL||BTREE|||
|pre_forum_post|1|mul_test|1|tid|A|880048|NULL|NULL||BTREE|||
|pre_forum_post|1|mul_test|2|invisible|A|945236|NULL|NULL||BTREE|||
|pre_forum_post|1|mul_test|3|dateline|A|25521392|NULL|NULL||BTREE|||
|pre_forum_post|1|mul_test|4|pid|A|25521392|NULL|NULL||BTREE|||
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

看下执行计划：

root@localhost16:08:27[ultrax]>explainSELECT*FROMpre_forum_postWHEREtid=7932552AND`invisible`IN('0','-2')
->ORDERBYdatelineDESCLIMIT10;
+----+-------------+----------------+-------+-------------------------------------------+--------------+---------+------+------+---------------------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+----------------+-------+-------------------------------------------+--------------+---------+------+------+---------------------------------------+
|1|SIMPLE|pre_forum_post|range|PRIMARY,displayorder,first,mul_test,idx_1|displayorder|4|NULL|54|Usingindexcondition;Usingfilesort|
+----+-------------+----------------+-------+-------------------------------------------+--------------+---------+------+------+---------------------------------------+
1rowinset(0.00sec)

MySQL优化器认为这是一个range查询，那么(tid,invisible,dateline)这条索引中，dateline字段肯定用不上了，也就是说这个SQL最后的排序肯定会生成一个临时结果集，然后再结果集里面完成排序，而不是直接在索引中直接完成排序动作，于是我们尝试增加了一条索引。

root@localhost16:09:06[ultrax]>altertablepre_forum_postaddindexidx_1(tid,dateline);
QueryOK,20374596rowsaffected,0warning(600.23sec)
Records:0Duplicates:0Warnings:0
root@localhost16:20:22[ultrax]>explainSELECT*FROMpre_forum_postforceindex(idx_1)WHEREtid=7932552AND`invisible`IN('0','-2')ORDERBYdatelineDESCLIMIT10;
+----+-------------+----------------+------+---------------+-------+---------+-------+--------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+----------------+------+---------------+-------+---------+-------+--------+-------------+
|1|SIMPLE|pre_forum_post|ref|idx_1|idx_1|3|const|120646|Usingwhere|
+----+-------------+----------------+------+---------------+-------+---------+-------+--------+-------------+
1rowinset(0.00sec)
root@localhost16:22:06[ultrax]>SELECTsql_no_cache*FROMpre_forum_postWHEREtid=7932552AND`invisible`IN('0','-2')ORDERBYdatelineDESCLIMIT10;
...
10rowsinset(0.40sec)
root@localhost16:23:55[ultrax]>SELECTsql_no_cache*FROMpre_forum_postforceindex(idx_1)WHEREtid=7932552AND`invisible`IN('0','-2')ORDERBYdatelineDESCLIMIT10;
...
10rowsinset(0.00sec)

实验证明效果是极好的，其实不难理解，上面我们就说了in()在MySQL优化器里面是以多种组合方式来检索数据的，如果加了一个排序或者分组那势必只能在临时结果集上操作，也就是说索引里面即使包含了排序或者分组的字段依然是没用的。唯一不满的是MySQL优化器的选择依然不够靠谱。

总结下：在MySQL查询里面使用in()，除了要注意in()list的数量以及eq_range_index_dive_limit的值以外（具体见下），还要注意如果SQL包含排序/分组/去重等等就需要注意索引的使用。

eq_range_index_dive_limit的说明

还是上面的案例，为什么idx_1无法直接使用？需要使用hint强制只用这个索引呢？这里我们首先看下eq_range_index_dive_limit的值。

root@localhost22:38:05[ultrax]>showvariableslike'eq_range_index_dive_limit';
+---------------------------+-------+
|Variable_name|Value|
+---------------------------+-------+
|eq_range_index_dive_limit|2|
+---------------------------+-------+
1rowinset(0.00sec)

根据我们上面说的这种情况0

{
"index":"displayorder",
"ranges":[
"7932552<=tid<=7932552AND-2<=invisible<=-2",
"7932552<=tid<=7932552AND0<=invisible<=0"
],
"index_dives_for_eq_ranges":false,
"rowid_ordered":false,
"using_mrr":false,
"index_only":false,
"rows":54,
"cost":66.81,
"chosen":true
}
//indexdive为false，最终chosen是true
...
{
"index":"idx_1",
"ranges":[
"7932552<=tid<=7932552"
],
"index_dives_for_eq_ranges":true,
"rowid_ordered":false,
"using_mrr":false,
"index_only":false,
"rows":120646,
"cost":144776,
"chosen":false,
"cause":"cost"
}

我们可以看到displayorder索引的cost是66.81，而idx_1的cost是120646，而最终MySQL优化器选择了displayorder这条索引。那么如果我们把eq_range_index_dive_limit设置>N是不是应该就会使用indexdive计算方式，得到更准确的执行计划呢？

root@localhost22:52:52[ultrax]>seteq_range_index_dive_limit=3;
QueryOK,0rowsaffected(0.00sec)
root@localhost22:55:38[ultrax]>explainSELECT*FROMpre_forum_postWHEREtid=7932552AND`invisible`IN('0','-2')ORDERBYdatelineDESCLIMIT10;
+----+-------------+----------------+------+-------------------------------------------+-------+---------+-------+--------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+----------------+------+-------------------------------------------+-------+---------+-------+--------+-------------+
|1|SIMPLE|pre_forum_post|ref|PRIMARY,displayorder,first,mul_test,idx_1|idx_1|3|const|120646|Usingwhere|
+----+-------------+----------------+------+-------------------------------------------+-------+---------+-------+--------+-------------+
1rowinset(0.00sec)

optimize_trace结果如下

{
"index":"displayorder",
"ranges":[
"7932552<=tid<=7932552AND-2<=invisible<=-2",
"7932552<=tid<=7932552AND0<=invisible<=0"
],
"index_dives_for_eq_ranges":true,
"rowid_ordered":false,
"using_mrr":false,
"index_only":false,
"rows":188193,
"cost":225834,
"chosen":true
}
...
{
"index":"idx_1",
"ranges":[
"7932552<=tid<=7932552"
],
"index_dives_for_eq_ranges":true,
"rowid_ordered":false,
"using_mrr":false,
"index_only":false,
"rows":120646,
"cost":144776,
"chosen":true
}
...
"cost_for_plan":144775,
"rows_for_plan":120646,
"chosen":true
//在备选索引选择中两条索引都被选择，在最后的逻辑优化中选在了代价最小的索引也就是idx_1

以上就是在等值范围查询中eq_range_index_dive_limit的值怎么影响MySQL优化器计算开销，从而影响索引的选择。另外我们可以通过profiling来看看优化器的统计耗时：

indexdive

+----------------------+----------+
|Status|Duration|
+----------------------+----------+
|starting|0.000048|
|checkingpermissions|0.000004|
|Openingtables|0.000015|
|init|0.000044|
|Systemlock|0.000009|
|optimizing|0.000014|
|statistics|0.032089|
|preparing|0.000022|
|Sortingresult|0.000003|
|executing|0.000003|
|Sendingdata|0.000101|
|end|0.000004|
|queryend|0.000002|
|closingtables|0.000009|
|freeingitems|0.000013|
|cleaningup|0.000012|
+----------------------+----------+

indexstatistics

+----------------------+----------+
|Status|Duration|
+----------------------+----------+
|starting|0.000045|
|checkingpermissions|0.000003|
|Openingtables|0.000014|
|init|0.000040|
|Systemlock|0.000008|
|optimizing|0.000014|
|statistics|0.000086|
|preparing|0.000016|
|Sortingresult|0.000002|
|executing|0.000002|
|Sendingdata|0.000016|
|Creatingsortindex|0.412123|
|end|0.000012|
|queryend|0.000004|
|closingtables|0.000013|
|freeingitems|0.000023|
|cleaningup|0.000015|
+----------------------+----------+

可以看到当eq_range_index_dive_limit加大使用indexdive时，优化器统计耗时明显比ndexstatistics方式来的长，但最终它使用了作出了更合理的执行计划。统计耗时0.032089svs.000086s，但是SQL执行耗时却是约0.03svs0.41s。

附：如何使用optimize_trace

setoptimizer_trace='enabled=on';
select*frominformation_schema.optimizer_trace\G
//注：optimizer_trace建议只在session模式下开启调试即可

参考资料

http://dev.mysql.com/doc/refman/5.6/en/range-optimization.html

http://imysql.com/2014/08/05/a-fake-bug-with-eq-range-index-dive-limit.shtml

http://blog.163.com/li_hx/blog/static/18399141320147521735442/

到此这篇关于MySQLSQL优化教程之in和range查询的文章就介绍到这了,更多相关MySQLSQL优化之in和range查询内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

MySQL SQL优化教程之in和range查询

热门推荐

随机推荐