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

首先我们来说下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)

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: 0root@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)

总结下：在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)

{ "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"}

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)

{ "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

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 | +----------------------+----------+

+----------------------+----------+| 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 | +----------------------+----------+

附：如何使用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/

更多相关文章

随机推荐