首先我们来说下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手册中的说明：
The eq_range_index_dive_limit system variable enables you to configure the number of values at which the optimizer switches from one row estimation strategy to the other. To disable use of statistics and always use index dives, set eq_range_index_dive_limit to 0. To permit use of index dives for comparisons of up to N equality ranges, set eq_range_index_dive_limit to N + 1.
eq_range_index_dive_limit is available as of MySQL 5.6.5. Before 5.6.5, the optimizer uses index dives, which is equivalent to eq_range_index_dive_limit=0.
也就是说：
1. eq_range_index_dive_limit = 0 只能使用index dive
2. 0 < eq_range_index_dive_limit <= N 使用index statistics
3. eq_range_index_dive_limit > N 只能使用index dive
index dive与index statistics是MySQL优化器对开销代价的估算方法，前者统计速度慢但是能得到精准的值，后者统计速度快但是数据未必精准。
the optimizer can estimate the row count for each range using dives into the index or index statistics.
在MySQL5.7版本中将默认值从10修改成200目的是为了尽可能的保证范围等值运算（IN()）执行计划尽量精准，因为IN()list的数量很多时候都是超过10的。
说在前面
今天文章的主题有两个：
- range查询与索引使用
- eq_range_index_dive_limit的说明
range查询与索引使用
SQL如下：
SELECT * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN('0','-2')
ORDER BY dateline DESC LIMIT 10;
索引如下：
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | 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@localhost 16:08:27 [ultrax]> explain SELECT * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN('0','-2')
-> ORDER BY dateline DESC LIMIT 10;
+----+-------------+----------------+-------+-------------------------------------------+--------------+---------+------+------+---------------------------------------+
| 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 | Using index condition; Using filesort |
+----+-------------+----------------+-------+-------------------------------------------+--------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
MySQL优化器认为这是一个range查询，那么(tid,invisible,dateline)这条索引中，dateline字段肯定用不上了，也就是说这个SQL最后的排序肯定会生成一个临时结果集，然后再结果集里面完成排序，而不是直接在索引中直接完成排序动作，于是我们尝试增加了一条索引。
root@localhost 16:09:06 [ultrax]> alter table pre_forum_post add index idx_1 (tid,dateline);
Query OK, 20374596 rows affected, 0 warning (600.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost 16:20:22 [ultrax]> explain SELECT * FROM pre_forum_post force index (idx_1) WHERE tid=7932552 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 10;
+----+-------------+----------------+------+---------------+-------+---------+-------+--------+-------------+
| 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 | Using where |
+----+-------------+----------------+------+---------------+-------+---------+-------+--------+-------------+
1 row in set (0.00 sec)
root@localhost 16:22:06 [ultrax]> SELECT sql_no_cache * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 10;
...
10 rows in set (0.40 sec)
root@localhost 16:23:55 [ultrax]> SELECT sql_no_cache * FROM pre_forum_post force index (idx_1) WHERE tid=7932552 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 10;
...
10 rows in set (0.00 sec)
实验证明效果是极好的，其实不难理解，上面我们就说了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@localhost 22:38:05 [ultrax]> show variables like 'eq_range_index_dive_limit'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | eq_range_index_dive_limit | 2 | +---------------------------+-------+ 1 row in set (0.00 sec)
根据我们上面说的这种情况0 < eq_range_index_dive_limit <= N使用index statistics，那么接下来我们用OPTIMIZER_TRACE来一看究竟。
{
"index": "displayorder",
"ranges": [
"7932552 <= tid <= 7932552 AND -2 <= invisible <= -2",
"7932552 <= tid <= 7932552 AND 0 <= invisible <= 0"
],
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 54,
"cost": 66.81,
"chosen": true
}
// index dive为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是不是应该就会使用index dive计算方式，得到更准确的执行计划呢？
root@localhost 22:52:52 [ultrax]> set eq_range_index_dive_limit = 3;
Query OK, 0 rows affected (0.00 sec)
root@localhost 22:55:38 [ultrax]> explain SELECT * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 10;
+----+-------------+----------------+------+-------------------------------------------+-------+---------+-------+--------+-------------+
| 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 | Using where |
+----+-------------+----------------+------+-------------------------------------------+-------+---------+-------+--------+-------------+
1 row in set (0.00 sec)
optimize_trace结果如下
{
"index": "displayorder",
"ranges": [
"7932552 <= tid <= 7932552 AND -2 <= invisible <= -2",
"7932552 <= tid <= 7932552 AND 0 <= 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来看看优化器的统计耗时：
index dive
+----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000048 | | checking permissions | 0.000004 | | Opening tables | 0.000015 | | init | 0.000044 | | System lock | 0.000009 | | optimizing | 0.000014 | | statistics | 0.032089 | | preparing | 0.000022 | | Sorting result | 0.000003 | | executing | 0.000003 | | Sending data | 0.000101 | | end | 0.000004 | | query end | 0.000002 | | closing tables | 0.000009 | | freeing items | 0.000013 | | cleaning up | 0.000012 | +----------------------+----------+
index statistics
+----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000045 | | checking permissions | 0.000003 | | Opening tables | 0.000014 | | init | 0.000040 | | System lock | 0.000008 | | optimizing | 0.000014 | | statistics | 0.000086 | | preparing | 0.000016 | | Sorting result | 0.000002 | | executing | 0.000002 | | Sending data | 0.000016 | | Creating sort index | 0.412123 | | end | 0.000012 | | query end | 0.000004 | | closing tables | 0.000013 | | freeing items | 0.000023 | | cleaning up | 0.000015 | +----------------------+----------+
可以看到当eq_range_index_dive_limit加大使用index dive时，优化器统计耗时明显比ndex statistics方式来的长，但最终它使用了作出了更合理的执行计划。统计耗时0.032089s vs .000086s，但是SQL执行耗时却是约0.03s vs 0.41s。
附：如何使用optimize_trace
set optimizer_trace='enabled=on'; select * from information_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/
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线
暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。
艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。
更新日志
- 凤飞飞《我们的主题曲》飞跃制作[正版原抓WAV+CUE]
- 刘嘉亮《亮情歌2》[WAV+CUE][1G]
- 红馆40·谭咏麟《歌者恋歌浓情30年演唱会》3CD[低速原抓WAV+CUE][1.8G]
- 刘纬武《睡眠宝宝竖琴童谣 吉卜力工作室 白噪音安抚》[320K/MP3][193.25MB]
- 【轻音乐】曼托凡尼乐团《精选辑》2CD.1998[FLAC+CUE整轨]
- 邝美云《心中有爱》1989年香港DMIJP版1MTO东芝首版[WAV+CUE]
- 群星《情叹-发烧女声DSD》天籁女声发烧碟[WAV+CUE]
- 刘纬武《睡眠宝宝竖琴童谣 吉卜力工作室 白噪音安抚》[FLAC/分轨][748.03MB]
- 理想混蛋《Origin Sessions》[320K/MP3][37.47MB]
- 公馆青少年《我其实一点都不酷》[320K/MP3][78.78MB]
- 群星《情叹-发烧男声DSD》最值得珍藏的完美男声[WAV+CUE]
- 群星《国韵飘香·贵妃醉酒HQCD黑胶王》2CD[WAV]
- 卫兰《DAUGHTER》【低速原抓WAV+CUE】
- 公馆青少年《我其实一点都不酷》[FLAC/分轨][398.22MB]
- ZWEI《迟暮的花 (Explicit)》[320K/MP3][57.16MB]