SQL优化教程之in与range查询
前言
《高性能MySQL》里面提及用in这种方式可以有效的替代一定的range查询，提升查询效率，因为在一条索引里面，range字段后面的部分是不生效的（ps.需要考虑ICP）。MySQL优化器将in这种方式转化成 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.
换言之，
eq_range_index_dive_limit=0只能使用indexdive
0 eq_range_index_dive_limit>N只能使用indexdive 在MySQL5.7版本中将默认值从10修改成200目的是为了尽可能的保证范围等值运算（IN()）执行计划尽量精准，因为IN()list的数量很多时候都是超过10的。 在MySQL的官方手册上有这么一句话: theoptimizercanestimatetherowcountforeachrangeusingdivesintotheindexorindexstatistics. 大意: 优化器预估每个范围段－－如"aIN(10,20,30)"视为等值比较,括3个范围段实则简化为3个单值,分别是10,20,30－－中包括的元组数,用范围段来表示是因为MySQL的"range"扫描方式多数做的是范围扫描，此处单值可视为范围段的特例; 估计方法有2种: 对比这两种方式 简单说，**选项eq_range_index_dive_limit的值设定了IN列表中的条件个数上线，超过设定值时，会将执行计划从indexdive变成indexstatistics**。 为什么要区分这2种方式呢? 讨论主题 range查询与索引使用 SQL如下： 索引如下： 看下执行计划： MySQL优化器认为这是一个range查询，那么(tid,invisible,dateline)这条索引中，dateline字段肯定用不上了，也就是说这个SQL最后的排序肯定会生成一个临时结果集，然后再结果集里面完成排序，而不是直接在索引中直接完成排序动作，于是我们尝试增加了一条索引。 实验证明效果是极好的，其实不难理解，上面我们就说了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的值。 根据我们上面说的这种情况0 我们可以看到displayorder索引的cost是66.81，而idx_1的cost是120646，而最终MySQL优化器选择了displayorder这条索引。那么如果我们把eq_range_index_dive_limit设置>N是不是应该就会使用indexdive计算方式，得到更准确的执行计划呢？ optimize_trace结果如下 在备选索引选择中两条索引都被选择，在最后的逻辑优化中选在了代价最小的索引也就是idx_1以上就是在等值范围查询中eq_range_index_dive_limit的值怎么影响MySQL优化器计算开销，从而影响索引的选择。另外我们可以通过profiling来看看优化器的统计耗时： indexdive indexstatistics 可以看到当eq_range_index_dive_limit加大使用indexdive时，优化器统计耗时明显比ndexstatistics方式来的长，但最终它使用了作出了更合理的执行计划。统计耗时0.032089svs.000086s，但是SQL执行耗时却是约0.03svs0.41s。 附： 如何使用optimize_trace 注：optimizer_trace建议只在session模式下开启调试即可 如何使用profile 另外还可以看到memory,swaps,contextswitches,source等信息 参考资料 [1]MySQLSQL优化系列之in与range查询 https://www.nhooo.com/article/201251.htm [2]MySQL物理查询优化技术---indexdive辨析 http://blog.163.com/li_hx/blog/static/18399141320147521735442/ 到此这篇关于SQL优化教程之in与range查询的文章就介绍到这了,更多相关SQL优化之in与range查询内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
SELECT*FROMpre_forum_postWHEREtid=7932552ANDinvisibleIN('0','-2')ORDERBYdatelineDESCLIMIT10;
PRIMARY(tid,position),
pid(pid),
fid(tid),
displayorder(tid,invisible,dateline)
first(tid,first)
new_auth(authorid,invisible,tid)
idx_dt(dateline)
mul_test(tid,invisible,dateline,pid)
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)
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)
root@localhost22:38:05[ultrax]>showvariableslike'eq_range_index_dive_limit';
+---------------------------+-------+
|Variable_name|Value|
+---------------------------+-------+
|eq_range_index_dive_limit|2|
+---------------------------+-------+
1rowinset(0.00sec)
{
"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"
}
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)
{
"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
+----------------------+----------+
|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|
+----------------------+----------+
+----------------------+----------+
|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|
+----------------------+----------+
setoptimizer_trace='enabled=on';
select*frominformation_schema.optimizer_trace\G
setprofiling=ON;
执行sql;
showprofiles;
showprofileforquery2;
showprofileblockio,cpuforquery2;