MySQL SQL優(yōu)化教程之in和range查詢
首先我們來說下in()這種方式的查詢。在《高性能MySQL》里面提及用in這種方式可以有效的替代一定的range查詢，提升查詢效率，因?yàn)樵谝粭l索引里面，range字段后面的部分是不生效的。使用in這種方式其實(shí)MySQL優(yōu)化器是轉(zhuǎn)化成了n*m種組合方式來進(jìn)行查詢，最終將返回值合并，有點(diǎn)類似union但是更高效。同時它存在這一些問題：
老版本的MySQL在IN()組合條件過多的時候會發(fā)生很多問題。查詢優(yōu)化可能需要花很多時間，并消耗大量內(nèi)存。新版本MySQL在組合數(shù)超過一定的數(shù)量就不進(jìn)行計(jì)劃評估了，這可能導(dǎo)致MySQL不能很好的利用索引。
這里的“一定數(shù)量”在MySQL5.6.5以及以后的版本中是由eq_range_index_dive_limit這個參數(shù)控制（感謝@葉金榮同學(xué)的指點(diǎn)）。默認(rèn)設(shè)置是10，一直到5.7以后的版本默認(rèn)會修改成200，當(dāng)然我們是可以手動設(shè)置的。我們看下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優(yōu)化器對開銷代價的估算方法，前者統(tǒng)計(jì)速度慢但是能得到精準(zhǔn)的值，后者統(tǒng)計(jì)速度快但是數(shù)據(jù)未必精準(zhǔn)。
the optimizer can estimate the row count for each range using dives into the index or index statistics.
在MySQL5.7版本中將默認(rèn)值從10修改成200目的是為了盡可能的保證范圍等值運(yùn)算（IN()）執(zhí)行計(jì)劃盡量精準(zhǔn)，因?yàn)镮N()list的數(shù)量很多時候都是超過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 | | | +----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
看下執(zhí)行計(jì)劃：
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優(yōu)化器認(rèn)為這是一個range查詢，那么(tid,invisible,dateline)這條索引中，dateline字段肯定用不上了，也就是說這個SQL最后的排序肯定會生成一個臨時結(jié)果集，然后再結(jié)果集里面完成排序，而不是直接在索引中直接完成排序動作，于是我們嘗試增加了一條索引。
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)
實(shí)驗(yàn)證明效果是極好的，其實(shí)不難理解，上面我們就說了in()在MySQL優(yōu)化器里面是以多種組合方式來檢索數(shù)據(jù)的，如果加了一個排序或者分組那勢必只能在臨時結(jié)果集上操作，也就是說索引里面即使包含了排序或者分組的字段依然是沒用的。唯一不滿的是MySQL優(yōu)化器的選擇依然不夠靠譜。
總結(jié)下：在MySQL查詢里面使用in()，除了要注意in()list的數(shù)量以及eq_range_index_dive_limit的值以外（具體見下），還要注意如果SQL包含排序/分組/去重等等就需要注意索引的使用。
eq_range_index_dive_limit的說明
還是上面的案例，為什么idx_1無法直接使用？需要使用hint強(qiáng)制只用這個索引呢？這里我們首先看下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)
根據(jù)我們上面說的這種情況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優(yōu)化器選擇了displayorder這條索引。那么如果我們把eq_range_index_dive_limit設(shè)置>N是不是應(yīng)該就會使用index dive計(jì)算方式，得到更準(zhǔn)確的執(zhí)行計(jì)劃呢？
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結(jié)果如下
{
"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
// 在備選索引選擇中兩條索引都被選擇，在最后的邏輯優(yōu)化中選在了代價最小的索引也就是idx_1
以上就是在等值范圍查詢中eq_range_index_dive_limit的值怎么影響MySQL優(yōu)化器計(jì)算開銷，從而影響索引的選擇。另外我們可以通過profiling來看看優(yōu)化器的統(tǒng)計(jì)耗時：
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 | +----------------------+----------+
可以看到當(dāng)eq_range_index_dive_limit加大使用index dive時，優(yōu)化器統(tǒng)計(jì)耗時明顯比ndex statistics方式來的長，但最終它使用了作出了更合理的執(zhí)行計(jì)劃。統(tǒng)計(jì)耗時0.032089s vs .000086s，但是SQL執(zhí)行耗時卻是約0.03s vs 0.41s。
附：如何使用optimize_trace
set optimizer_trace='enabled=on'; select * from information_schema.optimizer_trace\G // 注：optimizer_trace建議只在session模式下開啟調(diào)試即可
參考資料
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/
到此這篇關(guān)于MySQL SQL優(yōu)化教程之in和range查詢的文章就介紹到這了,更多相關(guān)MySQL SQL優(yōu)化之in和range查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于Mysql事務(wù)中Update是否會鎖表的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
CentOS 6、7下mysql 5.7 詳細(xì)安裝教程
這篇文章主要為大家介紹了CentOS 6、7下mysql 5.7 詳細(xì)安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-08-08
mysql數(shù)據(jù)庫常見基本操作實(shí)例分析【創(chuàng)建、查看、修改及刪除數(shù)據(jù)庫】
這篇文章主要介紹了mysql數(shù)據(jù)庫常見基本操作,結(jié)合實(shí)例形式分析了mysql創(chuàng)建、查看、修改及刪除數(shù)據(jù)庫實(shí)現(xiàn)方法與操作注意事項(xiàng),需要的朋友可以參考下2020-04-04
這篇文章主要介紹了如何使用C語言連接MySQL數(shù)據(jù)庫,包括安裝MySQL連接庫、初始化MySQL、連接數(shù)據(jù)庫、執(zhí)行SQL查詢、獲取查詢結(jié)果、關(guān)閉連接等步驟,并提供了完整的代碼示例,需要的朋友可以參考下2025-03-03
這篇文章主要介紹了Windows下安裝MySQL5.5.19圖文教程,非常詳細(xì),對每一步都做了說明,需要的朋友可以參考下2014-07-07
MySQL中聚合函數(shù)count的使用和性能優(yōu)化技巧
這篇文章主要介紹了Windows 10，MySQL版本是5.7.12-log環(huán)境下mysql中聚合函數(shù)count的使用和性能優(yōu)化,需要的朋友可以參考下2018-06-06
MySQL安裝過程報(bào)starting?the?server報(bào)錯詳細(xì)解決方案(附MySQL安裝程序)
如果電腦是第一次安裝MySQL,一般不會出現(xiàn)這樣的報(bào)錯,starting the server失敗通常是因?yàn)樯洗伟惭b的該軟件未清除干凈,這篇文章主要給大家介紹了關(guān)于MySQL安裝過程報(bào)starting?the?server報(bào)錯的詳細(xì)解決方案,文中還附MySQL安裝程序,需要的朋友可以參考下2024-03-03
mysql數(shù)據(jù)庫忘記了root密碼是件很痛苦的事，本文介紹如何解決windows環(huán)境下mysql服務(wù)器忘記root密碼的解決方法,需要的朋友可以參考下2016-10-10
mysql使用insert into select插入查出的數(shù)據(jù)
這篇文章主要介紹了mysql使用insert into select插入查出的數(shù)據(jù)方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
mysql使用mysqld_multi部署單機(jī)多實(shí)例的方法教程
這篇文章主要給大家介紹了關(guān)于mysql使用mysqld_multi部署單機(jī)多實(shí)例的相關(guān)資料，文中通過示例代碼將實(shí)現(xiàn)的步驟一步步介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧。2018-03-03