SQL優(yōu)化教程之in與range查詢
前言
《高性能MySQL》里面提及用in這種方式可以有效的替代一定的range查詢，提升查詢效率， 因?yàn)樵谝粭l索引里面，range字段后面的部分是不生效的（ps.需要考慮 ICP） 。MySQL優(yōu)化器將in這種方式轉(zhuǎn)化成 n*m 種組合進(jìn)行查詢，最終將返回值合并，有點(diǎn)類似union但是更高效。
MySQL在 IN() 組合條件過(guò)多的時(shí)候會(huì)發(fā)生很多問(wèn)題。查詢優(yōu)化可能需要花很多時(shí)間，并消耗大量?jī)?nèi)存。新版本MySQL在組合數(shù)超過(guò)一定的數(shù)量就不進(jìn)行計(jì)劃評(píng)估了，這可能導(dǎo)致MySQL不能很好的利用索引。
這里的 一定數(shù) 在MySQL5.6.5以及以后的版本中是由eq_range_index_dive_limit這個(gè)參數(shù)控制 。默認(rèn)設(shè)置是10，一直到5.7以后的版本默認(rèn)修改為200，當(dāng)然可以手動(dòng)設(shè)置的。5.6手冊(cè)說(shuō)明如下：
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.
換言之，
eq_range_index_dive_limit = 0 只能使用index dive
0 < eq_range_index_dive_limit <= N 使用index statistics
eq_range_index_dive_limit > N 只能使用index dive
在MySQL5.7版本中將默認(rèn)值從10修改成200目的是為了盡可能的保證范圍等值運(yùn)算（IN()）執(zhí)行計(jì)劃盡量精準(zhǔn)，因?yàn)镮N()list的數(shù)量很多時(shí)候都是超過(guò)10的。
在MySQL的官方手冊(cè)上有這么一句話:
the optimizer can estimate the row count for each range using dives into the index or index statistics.
大意:
優(yōu)化器預(yù)估每個(gè)范圍段－－如"a IN (10, 20, 30)" 視為等值比較, 括3個(gè)范圍段實(shí)則簡(jiǎn)化為3個(gè)單值,分別是10,20,30－－中包括的元組數(shù),用范圍段來(lái)表示是因?yàn)?MySQL 的"range"掃描方式多數(shù)做的是范圍掃描，此處單值可視為范圍段的特例;
估計(jì)方法有2種:
- dive到index中即利用索引完成元組數(shù)的估算,簡(jiǎn)稱index dive;
- index statistics:使用索引的統(tǒng)計(jì)數(shù)值,進(jìn)行估算;
對(duì)比這兩種方式
- index dive: 速度慢,但能得到精確的值（MySQL的實(shí)現(xiàn)是數(shù)索引對(duì)應(yīng)的索引項(xiàng)個(gè)數(shù)，所以精確）
- index statistics: 速度快,但得到的值未必精確
簡(jiǎn)單說(shuō)，**選項(xiàng) eq_range_index_dive_limit 的值設(shè)定了 IN列表中的條件個(gè)數(shù)上線，超過(guò)設(shè)定值時(shí)，會(huì)將執(zhí)行計(jì)劃從 index dive 變成 index statistics **。
為什么要區(qū)分這2種方式呢?
- 查詢優(yōu)化器會(huì)使用代價(jià)估算模型計(jì)算每個(gè)計(jì)劃的代價(jià),選擇其中代價(jià)最小的
- 單表掃描時(shí),需要計(jì)算代價(jià);所以單表的索引掃描也需要計(jì)算代價(jià)
- 單表的計(jì)算公式通常是: 代價(jià) = 元組數(shù) * IO平均值
- 所以不管是哪種掃描方式,都需要計(jì)算元組數(shù)
- 當(dāng)遇到“a IN (10, 20, 30)”這樣的表達(dá)式的時(shí)候，發(fā)現(xiàn)a列存在索引，則需要看這個(gè)索引可以掃描到的元組數(shù)由多少而計(jì)算其索引掃描代價(jià)，所以就用到了本文提到的“index dive”、“index statistics”這2種方式。
討論主題
- range查詢與索引使用
- eq_range_index_dive_limit的說(shuō)明
range查詢與索引使用
SQL如下：
SELECT * FROM pre_forum_post WHERE tid=7932552 AND invisible IN('0','-2') ORDER BY dateline DESC LIMIT 10;
索引如下：
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)
看下執(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)為這是一個(gè)range查詢，那么(tid,invisible,dateline)這條索引中，dateline字段肯定用不上了，也就是說(shuō)這個(gè)SQL最后的排序肯定會(huì)生成一個(gè)臨時(shí)結(jié)果集，然后再結(jié)果集里面完成排序，而不是直接在索引中直接完成排序動(dòng)作，于是我們嘗試增加了一條索引。
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í)不難理解，上面我們就說(shuō)了in()在MySQL優(yōu)化器里面是以多種組合方式來(lái)檢索數(shù)據(jù)的，如果加了一個(gè)排序或者分組那勢(shì)必只能在臨時(shí)結(jié)果集上操作，也就是說(shuō)索引里面即使包含了排序或者分組的字段依然是沒(méi)用的。唯一不滿的是MySQL優(yōu)化器的選擇依然不夠靠譜。
總結(jié)下：在MySQL查詢里面使用in()，除了要注意in()list的數(shù)量以及eq_range_index_dive_limit的值以外（具體見(jiàn)下），還要注意如果SQL包含排序/分組/去重等等就需要注意索引的使用。
eq_range_index_dive_limit的說(shuō)明
還是上面的案例，為什么idx_1無(wú)法直接使用？需要使用hint強(qiáng)制只用這個(gè)索引呢？這里我們首先看下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ù)我們上面說(shuō)的這種情況0 < eq_range_index_dive_limit <= N使用index statistics，那么接下來(lái)我們用OPTIMIZER_TRACE來(lái)一看究竟。
{
"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)該就會(huì)使用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)化中選在了代價(jià)最小的索引也就是idx_1 以上就是在等值范圍查詢中eq_range_index_dive_limit的值怎么影響MySQL優(yōu)化器計(jì)算開銷，從而影響索引的選擇。另外我們可以通過(guò)profiling來(lái)看看優(yōu)化器的統(tǒng)計(jì)耗時(shí)：
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時(shí)，優(yōu)化器統(tǒng)計(jì)耗時(shí)明顯比ndex statistics方式來(lái)的長(zhǎng)，但最終它使用了作出了更合理的執(zhí)行計(jì)劃。統(tǒng)計(jì)耗時(shí)0.032089s vs .000086s，但是SQL執(zhí)行耗時(shí)卻是約0.03s vs 0.41s。
附：
如何使用optimize_trace
set optimizer_trace='enabled=on'; select * from information_schema.optimizer_trace\G
注：optimizer_trace建議只在session模式下開啟調(diào)試即可
如何使用profile
set profiling=ON; 執(zhí)行sql; show profiles; show profile for query 2; show profile block io,cpu for query 2;
另外還可以看到memory,swaps,context switches,source 等信息
參考資料
[1]MySQL SQL優(yōu)化系列之 in與range 查詢
http://www.dhdzp.com/article/201251.htm
[2]MySQL物理查詢優(yōu)化技術(shù)---index dive辨析
http://blog.163.com/li_hx/blog/static/18399141320147521735442/
到此這篇關(guān)于SQL優(yōu)化教程之in與range查詢的文章就介紹到這了,更多相關(guān)SQL優(yōu)化之in與range查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- 分析MySQL中優(yōu)化distinct的技巧
- mysql in語(yǔ)句子查詢效率慢的優(yōu)化技巧示例
- MySQL查詢優(yōu)化:連接查詢排序limit(join、order by、limit語(yǔ)句)介紹
- MySQL優(yōu)化之使用連接(join)代替子查詢
- SQL語(yǔ)句優(yōu)化之JOIN和LEFT JOIN 和 RIGHT JOIN語(yǔ)句的優(yōu)化
- SQL優(yōu)化之針對(duì)count、表的連接順序、條件順序、in及exist的優(yōu)化
- MySQL中對(duì)于not in和minus使用的優(yōu)化
- 關(guān)于mysql中innodb的count優(yōu)化問(wèn)題分享
- MySQL中insert語(yǔ)句的使用與優(yōu)化教程
相關(guān)文章
MySql 8.0及對(duì)應(yīng)驅(qū)動(dòng)包匹配的注意點(diǎn)說(shuō)明
這篇文章主要介紹了MySql 8.0及對(duì)應(yīng)驅(qū)動(dòng)包匹配的注意點(diǎn)說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-06-06
這篇文章主要介紹了pt-kill 常用殺進(jìn)程參數(shù)介紹,需要的朋友可以參考下2016-04-04
一文帶你將csv文件導(dǎo)入到mysql數(shù)據(jù)庫(kù)(親測(cè)有效)
一直不大懂csv怎么通過(guò)mysql圖形化的界面直接導(dǎo)入,看了很多帖,才覺(jué)得自己會(huì)了,下面這篇文章主要給大家介紹了關(guān)于將csv文件導(dǎo)入到mysql數(shù)據(jù)庫(kù)的相關(guān)資料,需要的朋友可以參考下2022-08-08
mysql分區(qū)后每個(gè)分區(qū)成了獨(dú)立的文件,雖然從邏輯上還是一張表其實(shí)已經(jīng)分成了多張獨(dú)立的表,下面這篇文章主要給大家介紹了關(guān)于MySQL分區(qū)之指定各分區(qū)路徑的相關(guān)資料,需要的朋友可以參考下2022-04-04
MySQL MGR搭建過(guò)程中常遇見(jiàn)的問(wèn)題及解決辦法
這篇文章主要介紹了MySQL MGR搭建過(guò)程中常遇見(jiàn)的問(wèn)題及解決辦法，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-03-03
MySQL分區(qū)表主要用于提高查詢效率,通過(guò)將數(shù)據(jù)分割成更小的部分進(jìn)行管理,文章詳細(xì)介紹了如何創(chuàng)建、查詢、修改和存儲(chǔ)分區(qū)表,包括創(chuàng)建復(fù)合主鍵、按年份和月份分區(qū)、刪除分區(qū)、查詢分區(qū)數(shù)據(jù)以及利用存儲(chǔ)過(guò)程批量轉(zhuǎn)換非分區(qū)表為分區(qū)表等方法2025-02-02
MySQL 基于時(shí)間點(diǎn)的快速恢復(fù)方案
這篇文章主要介紹了MySQL 基于時(shí)間點(diǎn)的快速恢復(fù)方案，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-11-11
這篇文章主要介紹了mySQL服務(wù)器連接,斷開及cmd使用操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-07-07
本文主要介紹了MySQL數(shù)據(jù)庫(kù)表空間回收的解決，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-02-02
這篇文章主要為大家分享了mysql 5.7.14 下載安裝配置方法圖文教程,感興趣的朋友可以參考一下2016-08-08