SQL優化系列之 in與range 查詢

2021-03-02 yangyidba

《高性能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手冊說明如下：

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 dive0 < eq_range_index_dive_limit <= N 使用index statisticseq_range_index_dive_limit > N 只能使用index dive

在MySQL5.7版本中將默認值從10修改成200目的是為了儘可能的保證範圍等值運算（IN()）執行計劃儘量精準，因為IN()list的數量很多時候都是超過10的。

在MySQL的官方手冊上有這麼一句話:

the optimizer can estimate the row count for each range using dives into the index or index statistics.

大意:

優化器預估每個範圍段－－如"a IN (10, 20, 30)" 視為等值比較, 括3個範圍段實則簡化為3個單值,分別是10,20,30－－中包括的元組數,用範圍段來表示是因為 MySQL 的"range"掃描方式多數做的是範圍掃描，此處單值可視為範圍段的特例;

估計方法有2種:

dive到index中即利用索引完成元組數的估算,簡稱index dive;

index statistics:使用索引的統計數值,進行估算;

對比這兩種方式

index dive: 速度慢,但能得到精確的值（MySQL的實現是數索引對應的索引項個數，所以精確）

index statistics: 速度快,但得到的值未必精確

簡單說，**選項 eq_range_index_dive_limit 的值設定了 IN列表中的條件個數上線，超過設定值時，會將執行計劃從 index dive 變成 index statistics **。

為什麼要區分這2種方式呢?

查詢優化器會使用代價估算模型計算每個計劃的代價,選擇其中代價最小的單表掃描時,需要計算代價;所以單表的索引掃描也需要計算代價單表的計算公式通常是: 代價 = 元組數 * IO平均值當遇到「a IN (10, 20, 30)」這樣的表達式的時候，發現a列存在索引，則需要看這個索引可以掃描到的元組數由多少而計算其索引掃描代價，所以就用到了本文提到的「index dive」、「index statistics」這2種方式。討論主題

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;

索引如下：

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@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模式下開啟調試即可

如何使用profile

set profiling=ON;
執行sql;
show profiles;
show profile for query 2;
show profile block io,cpu for query 2;

另外還可以看到memory,swaps,context switches,source 等信息

參考資料

[1]MySQL SQL優化系列之 in與range 查詢

http://myrock.github.io/2014/09/24/in-and-range/

[2]MySQL物理查詢優化技術---index dive辨析

http://blog.163.com/li_hx/blog/static/18399141320147521735442/

-The End-

本公眾號長期關注於資料庫技術以及性能優化，故障案例分析，資料庫運維技術知識分享，個人成長和自我管理等主題，歡迎掃碼關注。

相關焦點

  • 一次神奇的 sql 查詢經歷,group by 慢查詢優化記錄
    一、問題背景　　現網出現慢查詢，在500萬數量級的情況下，單表查詢速度在30多秒，需要對sql進行優化，sql如下：　　我在測試環境構造了500萬條數據，模擬了這個慢查詢。　　簡單來說，就是查詢一定條件下，都有哪些用戶的，很簡單的sql，可以看到，查詢耗時為37秒。
  • 《MySQL慢查詢優化》之SQL語句及索引優化
    1、慢查詢優化方式伺服器硬體升級優化Mysql伺服器軟體優化資料庫表結構優化SQL語句及索引優化>本文重點關注於SQL語句及索引優化，關於其他優化方式以及索引原理等，請關注本人《MySQL慢查詢優化》系列博文。
  • SQL 查詢優化之 WHERE 和 LIMIT 使用索引的奧秘
    查詢結果：第一條要5.018s，第二條0.016s為什麼會是這樣的結果呢？第一，acct_id和create_time都有索引，不應該出現5s查詢時間這麼慢啊 仔細觀察會發現，索引只使用了idx_create_time，沒有用到idx_acct_id。這能解釋第一條sql很慢，因為where查詢未用到索引，那麼第二條為什麼這麼快？看起來匪夷所思，其實搞清楚mysql查詢的原理之後，其實很簡單。
  • MySQL資料庫SQL查詢優化技巧之SELECT
    ，以及SELECT的執行流程，了解了SELECT的執行流程還是很重要的，重要我們才能有針對性地去進行優化，比如select在執行的時候 會把所以的欄位編程成大寫字母，這樣我們在起初編寫SELECT語句時就可以把所以的字母進行大寫，這樣就省去了程序為把SQL語句變成大寫的消耗，速度自然就會提高。
  • 「MySQL系列」分析Sql執行時間及查詢執行計劃(附資料庫和一千萬數據)
    Netty系列文章先告一段落了，因為這些知識已經滿足我當前工作的需求。後續項目中遇到問題和問題解決，會繼續更新Netty相關文章。哈哈哈 囉嗦的話有點多。接下來我將更新MySQL系列的文章，主要涉及MySQL的索引、調優、整體架構、引擎、分庫分表、擴容、索引的深入探究等等。感興趣的可以持續關注，不感興趣的略過。
  • 面對MySQL 查詢索引失效,程式設計師的六大優化技巧!
    不知道你有沒有碰到過這種情況，一條創建了索引的SQL語句在查詢過程中卻沒有使用索引，或是一條本來可以執行的很快的語句，卻由於MySQL選錯了索引，而導致查詢速度變得很慢？充分優化和利用索引能夠大大提高數據的查詢效率，但是在實際的應用中MySQL可能並不總會選擇合適且效率高的索引。
  • 打造出色查詢:如何優化SQL查詢?
    本文將給出關於優化SQL語句的幾點建議，希望能夠幫到你。1.嘗試不去用select *來查詢SQL，而是選擇專用欄位。5.優化limit分頁通常用limits來實現日常分頁，但當偏移量特別大時，查詢效率便會降低。因為Mysql不會跳過偏移量，而是直接獲取數據。
  • 女朋友都能看懂的,SQL優化乾貨
    '%老師'優化：在欄位後面使用模糊查詢select * from teacher where name like '李%'如果一定要在欄位開頭模糊查詢，那可以使用INSTR(str,substr)意思是：在字符串str裡面,字符串substr出現的第一個位置(index
  • 一張900w的數據表,17s執行的SQL優化到300ms?
    先說一下背景有一張9555695條數據量的財務流水錶，未做分庫分表，使用limit關鍵字來進行分頁查詢，未做優化前常規查詢需要耗時16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，下面我將一步一步講解如何做優化，使得優化後的ＳＱＬ能在耗時347 ms (execution
  • 資料庫SQL開發需要關注的6種優化手段
    sql語句指令由於語法簡潔、操作高效備受了很多用戶的喜愛。可是，sql語句指令的速率備受不一樣的資料庫查詢功能的限制，特別是在計算時間層面，加上語言的高效率都不代表優化方案會更容易，因此每一個資料庫查詢都需要根據具體情況做特殊調節。
  • MySQL之SQL優化方法
    type=index：索引全掃描，MySQL遍歷整個索引來查詢。type=range：索引範圍掃描，常見於<、<=、>、 >=、 between。type=ref：使用非唯一索引掃描或唯一索引的前綴掃描，返回匹配某個單獨值的記錄。
  • 優化SQL查詢:如何寫出高性能SQL語句
    執行計劃是資料庫根據SQL語句和相關表的統計信息作出的一個查詢方案，這個方案是由查詢優化器自動分析產生的，比如一條SQL語句如果用來從一個 10萬條記錄的表中查1條記錄，那查詢優化器會選擇「索引查找」方式，如果該表進行了歸檔，當前只剩下5000條記錄了，那查詢優化器就會改變方案，採用 「全表掃描」方式。
  • sqltoy-orm-4.16.11 發版,部分功能優化
    的十四個關鍵特點：1、最簡最直觀的sql編寫方式(不僅僅是查詢語句)，採用條件參數前置處理規整法，讓sql語句部分跟客戶端保持高度一致2、sql中支持注釋(規避了對hint特性的影響,知道hint嗎?搜oracle hint)，和動態更新加載，便於開發和後期維護整個過程的管理3、支持緩存翻譯和反向緩存條件檢索(通過緩存將名稱匹配成精確的key)，實現sql簡化和性能大幅提升4、支持快速分頁和分頁優化功能，實現分頁最高級別的優化，同時還考慮到了cte多個with as情況下的優化支持5、支持並行查詢6、根本杜絕sql注入問題，以後不需要討論這個話題7、支持行列轉換
  • mysql 如何優化left join
    c.hotel_id =h.hotel_id where h.hotel_id is null    這個sql是用來查詢出c表中有h表中無的記錄，所以想到了用left join的特性（返回左邊全部記錄，右表不滿足匹配條件的記錄對應行返回null）來滿足需求，不料這個查詢非常慢。
  • hive sql 優化心得
    如何寫好一個hql作為一個數據開發工程師，hive sql是我們必備的技能，可能大家都知道一些基本的優化方法（例如：使用分區、小表join大表、不使用distinct、where條件儘量寫到子查詢裡面減少數據量等等），但是你有沒有想過為什麼？是不是真的對執行效率有提升。
  • MySQL優化:定位慢查詢的兩種方法以及使用explain分析SQL
    定位慢查詢SQL在平時工作中，我想你肯定遇到過一條sql發出去了，但是等了好久才出現了返回值，這不僅僅影響了測試速度也大大降低了開發效率。所以我們有必要學習sql慢查詢定位。發現慢查詢及時優化或者提醒開發改寫。一般測試環境建議 long_query_time 設置的閥值比生產環境的小，比如生產環境是 1 秒，則測試環境建議配置成 0.5 秒。便於在測試環境及時發現一些效率低的 SQL。
  • 資料庫SQL腳本優化
    ，因為子查詢會在查詢前建立臨時表，查詢完又刪除臨時表，對於結果集大的查詢，性能影響更加明顯4、避免沒有where過濾條件的查詢 （前兩天就看到一個查詢bc_freight（400W+）表裡所有數據，沒有過濾條件，我看到的時候已經執行一萬多秒了）5、sql語句儘可能簡單點，一個大的sql要是在滿足業務需求的情況下可以分解為多個小
  • Oracle優化:sql語句的執行順序
    理解 sql 語句的執行順序對我們優化 sql 有很大的幫助，那麼 sql 語句的執行順序是怎樣的呢，以一條簡單的的語句做分析：① 先執行 from 子句，明確數據的來源，從哪個表或哪個視圖來查詢② 接著執行
  • PHP資料庫編程之MySQL優化策略概述
    http://www.ice-breaker.cn/post/7本文簡單講述了PHP資料庫編程之MySQL優化策略。分享給大家供大家參考，具體如下：前些天看到一篇文章說到PHP的瓶頸很多情況下不在PHP自身，而在於資料庫。我們都知道，PHP開發中，數據的增刪改查是核心。
  • Django QuerySet查詢基礎與技巧.有了她,再也不用擔心SQL注入了.
    先讓不會使用QuerySet的同學順利使用SQL語句查詢。不熟悉SQL的可以跳過該部分，如下代碼：sql = 'select * from blog_blog' #需要查詢資料庫具體Blog對應表名qs = Blog.objects.raw(sql) #將sql語句轉成RawQuerySet對象該SQL是獲取全部記錄，相當於QuerySet如下查詢：qs