前言
此案例介紹因為MySQL索引下探參數eq_range_index_dive_limit設置問題，導致包含in表達式的SQL執行計劃不穩定。
1 環境信息
RDS MySQL：5.7
2 錯誤描述
業務反饋，線上某個云RDS數據庫，查詢語句里面，where過濾條件有in表達式（in里面有多個值），當值的的數量超過100個后，查詢不走索引（全表掃描）；如果小于100個，就可以走該字段的索引。
select col1, col2, ....., colN from t1 where code1 in ( 'S100809280' , 'S100601788' , 'S100204388' , 'S100198706' , 'S100202941' , 'S100829425' , 'S100722717' , 'S100200238' , 'S100103341' , 'S100103224' , 'S100103478' , 'S100170079' , 'S100559854' , 'S100601936' , 'S100160005' , 'S100103223' , 'S100201577' , 'S100602024' , 'S101073651' , 'S100602104' , 'S101015799' , 'S100081466' , 'S100204897' , 'S100642083' , 'S101770606' , 'S100559164' , 'S100103242' , 'S100191428' , 'S100161978' , 'S100103533' , 'S100103241' , 'S100212199' , 'S100103234' , 'S100103233' , 'S100211435' , 'S100103470' , 'S100642143' , 'S100559227' , 'S100103471' , 'S100103383' , 'S100170475' , 'S100559241' , 'S100840238' , 'S100212356' , 'S100212350' , 'S100359991' , 'S100201421' , 'S100103439' , 'S100192508' , 'S100225372' , 'S100821468' , 'S100192566' , 'S100205406' , 'S100420398' , 'S100169168' , 'S100555710' , 'S100205438' , 'S100186856' , 'S100533307' , 'S100471335' , 'S100192976' , 'S100513283' , 'S100114066' , 'S100103490' , 'S100824251' , 'S100800010' , 'S100195066' , 'S100103420' , 'S100655582' , 'S100523113' , 'S100203199' , 'S100161990' , 'S100205694' , 'S100203240' , 'S100211457' , 'S100595087' , 'S100211805' , 'S101077103' , 'S100203159' , 'S100731134' , 'S101161307' , 'S100754053' , 'S100103436' , 'S100208961' , 'S101213023' , 'S101016396' , 'S100217812' , 'S100192159' , 'S100373711' , 'S100770037' , 'S100204361' , 'S100206111' , 'S100829338' , 'S100163599' , 'S100204748' , 'S100950944' , 'S100950888' , 'S101106892' , 'S100103229' , 'S100204717' ) and activity_type = 1;后臺驗證了一下執行計劃，確實如上面描述描述。
3 排查
3.1 查看建表語句
CREATE TABLE `t1` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`col1` varchar(32) DEFAULT NULL,
..........
`activity_type` tinyint(4) DEFAULT '0',
`code1` varchar(32) DEFAULT '',
`repeat` tinyint(4) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_code1` (`code1`),
KEY `idx_activity_type` (`activity_type`)
) ENGINE=InnoDB AUTO_INCREMENT=13406057 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;開始以為是優化器代價選擇的原因。當in里面多了一個值后，優化器認為走全表掃描的代價比走索引代價低。
進一步測試后發現，去掉上面SQL語句in里面任何一個或多個值，均會走索引，并且刪除的值并沒有明顯的數據傾斜。
3.2 eq_range_index_dive_limit參數
后來想到可能是索引下探參數（eq_range_index_dive_limit）問題。
- 大部分人喜歡叫索引潛水；
- 個人覺得oracle的綁定變量窺探和這個類似；
- 索引下探使用會有一些限制，參考3.6；
這個參數對應特性叫索引下探，主要是數據傾斜的時候比較有用。當數據存在傾斜時，索引統計信息可能不準，會導致執行計劃不準或者不穩定。
MySQL 5.7和8.0，該參數默認為200；5.6，該參數默認為10。
3.3 官方文檔該參數介紹
This variable indicates the number of equality ranges in an equality comparison condition when
the optimizer should switch from using index dives to index statistics in estimating the number of
qualifying rows. It applies to evaluation of expressions that have either of these equivalent forms,
where the optimizer uses a nonunique index to look up col_name values:
當優化器估計滿足條件的行數時，該參數控制優化器從使用索引下探評估，切換到使用索引統計信息評估。
該參數值表示相等比較條件中相等范圍的數量。
它適用于具有下面這些等效形式的表達式的評估，其中優化器使用非唯一索引來查找 col_name 值：
col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valNIn both cases, the expression contains N equality ranges. The optimizer can make row
estimates using index dives or index statistics. 對于上面兩種語句，表達式都包含 N 個相等范圍。優化器可以使用索引下探或索引統計信息進行行估計。
If eq_range_index_dive_limit is
greater than 0, the optimizer uses existing index statistics instead of index dives if there are
eq_range_index_dive_limit or more equality ranges. Thus, to permit use of index dives for up
to N equality ranges, set eq_range_index_dive_limit to N + 1. To disable use of index statistics
and always use index dives regardless of N, set eq_range_index_dive_limit to 0.如果 0 < eq_range_index_dive_limit <= N - 1，優化器對這N - 1 個相等范圍進行索引下探，得到準確的行評估，產生相對準確的執行計劃；
如果 eq_range_index_dive_limit > N - 1，優化器使用現有的索引統計信息進行行評估；
如果 eq_range_index_dive_limit = 0，則會禁用索引統計信息，始終使用索引下探進行行評估。
無論 N 多少，始終使用索引下探，將 eq_range_index_dive_limit 設置為 0。
To update table index statistics for best estimates, use ANALYZE TABLE.可以通過使用 ANALYZE TABLE 更新表的索引統計信息，以獲得最佳行估計。
For more information, see Equality Range Optimization of Many-Valued Comparisons有關詳細信息，請參閱多值比較的等式范圍優化。
3.4 多值比較的相等范圍優化
Consider these expressions, where col_name is an indexed column:
下面的表達式，檢索字段 col_name 上有索引：
col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valNEach expression is true if col_name is equal to any of several values. These comparisons are equality
range comparisons (where the “range” is a single value). The optimizer estimates the cost of reading
qualifying rows for equality range comparisons as follows:
如果 col_name 等于多個值中的任何一個，則每個表達式都為真。這些比較是相等范圍比較（其中“范圍”是單個值）。
對應相等范圍比較，優化器估計讀取符合條件的行的成本如下：
? If there is a unique index on col_name, the row estimate for each range is 1 because at most one row can have the given value.
? Otherwise, any index on col_name is nonunique and the optimizer can estimate the row count for each range using dives into the index or index statistics.
如果 col_name 上存在唯一索引，則每個范圍的行估計值為 1，因為最多一行可以具有給定值。
否則，col_name 上的任何索引都是非唯一的，優化器可以通過索引下探或索引統計信息來估計每個范圍的行數，進行行評估。
With index dives, the optimizer makes a dive at each end of a range and uses the number of rows in
the range as the estimate. For example, the expression col_name IN (10, 20, 30) has three
equality ranges and the optimizer makes two dives per range to generate a row estimate. Each pair of
dives yields an estimate of the number of rows that have the given value.
使用索引下探，優化器在范圍的每一端進行下探，并使用在此范圍內的行數作為估計值。
例如，表達式 col_name IN (10, 20, 30) 有三個相等范圍，優化器會對每個范圍進行兩次下探，來生成行估計。每個范圍的兩次下探，都會產生滿足給定值的行數的估計值。
開啟 optimizer_trace 追蹤執行計劃可以驗證
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_col_name",
"ranges": [
"10 <= coln_name <= 10",
"20 <= coln_name <= 20",
"30 <= coln_name <= 30"
],
"index_dives_for_eq_ranges": true, --此處說明使用了索引下探
3.5 比較
Index dives provide accurate row estimates, but as the number of comparison values in the expression increases, the optimizer takes longer to generate a row estimate.
Use of index statistics is less accurate than index dives but permits faster row estimation for large value lists.
索引下探雖然可以提供準確的行估計，但隨著表達式中比較值數量的增加，優化器需要更長的時間來生成行估計；
使用索引統計信息的到的行估計不如索引下探準確，但當表達式中有超多比較值的時候，使用索引統計信息更快的行估計。
3.6 跳過使用索引下探進行行估計
Even under conditions when index dives would otherwise be used, they are skipped for queries that satisfy all these conditions:即使滿足使用索引下探的條件，但是如果查詢滿足下面所有條件，該查詢也不會使用索引下探進行行估計。
? A single-index FORCE INDEX index hint is present. The idea is that if index use is forced, there is nothing to be gained from the additional overhead of performing dives into the index.
? The index is nonunique and not a FULLTEXT index.
? No subquery is present.
? No DISTINCT, GROUP BY, or ORDER BY clause is present.
Those dive-skipping conditions apply only for single-table queries. Index dives are not skipped for multiple-table queries (joins).
- 查詢在單列索引上使用了hint，來強制使用索引；
- 該列上索引為非唯一索引，并且不是全文索引；
- 該查詢不存在子查詢；
- 不存在 DISTINCT、GROUP BY 或 ORDER BY 子句；
- 該查詢只是針對單個表，不是多表通過join關聯查詢；
上面查詢滿足5個條件，可以跳過使用索引下探進行行評估的描述，MySQL 5.7 和MySQL 8.0 官方文檔描述是一致的。但是 8.0 多出如下描述：
Prior to MySQL 8.0, there is no way of skipping the use of index dives to estimate index usefulness,
except by using the eq_range_index_dive_limit system variable. In MySQL 8.0, index dive
skipping is possible for queries that satisfy all these conditions:MySQL 8.0版本之前，除了使用 eq_range_index_dive_limit 系統變量外，沒有辦法跳過索引下探；在MySQL 8.0版本中，如果滿足上面五個條件，是可能跳過索引下探的。
For EXPLAIN FOR CONNECTION, the output changes as follows if index dives are skipped:
? For traditional output, the rows and filtered values are NULL.
? For JSON output, rows_examined_per_scan and rows_produced_per_join do not appear,skip_index_dive_due_to_force is true, and cost calculations are not accurate.
Without FOR CONNECTION, EXPLAIN output does not change when index dives are skipped.
After execution of a query for which index dives are skipped, the corresponding row in the
INFORMATION_SCHEMA.OPTIMIZER_TRACE table contains an index_dives_for_range_access
value of skipped_due_to_force_index.5.7 版本上，雖然官方文檔也描述了跳過索引下探的條件，但是經過測試，查詢即使滿足上面5個條件，也不會跳過索引下探；具體測試過程參見6.2
8.0 版本則可以。具體測試過程參見6.3
For EXPLAIN FOR CONNECTION, the output changes as follows if index dives are skipped:
? For traditional output, the rows and filtered values are NULL.
? For JSON output, rows_examined_per_scan and rows_produced_per_join do not appear,skip_index_dive_due_to_force is true, and cost calculations are not accurate.
如果使用EXPLAIN FOR CONNECTION查看正在執行語句的執行計劃，該語句如果跳過索引下探，輸出會有如下變化。
Without FOR CONNECTION, EXPLAIN output does not change when index dives are skipped.
只使用EXPLAIN生成的執行計劃，即使跳過索引下探，執行計劃輸出也看不出變化。
After execution of a query for which index dives are skipped, the corresponding row in the
INFORMATION_SCHEMA.OPTIMIZER_TRACE table contains an index_dives_for_range_access
value of skipped_due_to_force_index.如果開啟了optimezer_trace查看執行計劃，如果跳過了索引下探，輸出信息里可以看到"index_dives_for_range_access": "skipped_due_to_force_index"，而不是"index_dives_for_range_access": "true"
3.5 線上庫參數值
[root@test][test]> show variables like '%eq_range_index_dive_limit%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 100 |
+---------------------------+-------+
1 row in set (0.00 sec)開啟 optimizer_trace 追蹤執行計劃，in表達式里面值超過100后
select * from information_schema.optimizer_trace\G;可以看到
"index_dives_for_eq_ranges": false確實沒有使用索引下探。
4 原因
線上設置的100，可能是rds當時版本的默認值。當IN表達式里有100個值，也就是N=100時，eq_range_index_dive_limit > N - 1，優化器使用現有的索引統計信息進行行評估，而優化器認為，使用全表掃描的代價比索引的代價低。
后面業務也反饋過類似問題，比如并發執行的同一個SQL語句，IN表達式里面值個數一樣，只是值不一樣，會出現有的SQL全表掃描，有的SQL索引查詢情況，也是一樣問題導致。
5 解決方案
該SQL業務頻繁用到，考慮調大該參數。調大到多少業務評估，最終調大到200。
6 補充
6.1 造數據
CREATE TABLE `t1` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`phone` int(11) DEFAULT NULL,
`stu` int(11) DEFAULT NULL,
`stu2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_stu` (`stu`)
) ENGINE = InnoDB AUTO_INCREMENT = 201 DEFAULT CHARSET = utf8mb4;INSERT INTO
`t1`
VALUES
(2, '2', NULL, 2, NULL),(3, '3', NULL, 3, NULL),(4, 'b', NULL, 4, NULL),(5, 'c', NULL, 5, NULL),(8, 'c', NULL, 8, NULL),(10, 'c', NULL, 10, NULL),(11, 'd', NULL, 11, NULL),(13, 'c', NULL, 13, NULL),(200, 'cccccccccccccccccccc', NULL, 200, NULL),(300, 'a', NULL, 300, NULL);[root@localhost] {14:28:26} (d2) [17]> select * from t1;
+-----+----------------------+-------+------+------+
| id | name | phone | stu | stu2 |
+-----+----------------------+-------+------+------+
| 2 | 2 | NULL | 2 | NULL |
| 3 | 3 | NULL | 3 | NULL |
| 4 | b | NULL | 4 | NULL |
| 5 | c | NULL | 5 | NULL |
| 8 | c | NULL | 8 | NULL |
| 10 | c | NULL | 10 | NULL |
| 11 | d | NULL | 11 | NULL |
| 13 | c | NULL | 13 | NULL |
| 200 | cccccccccccccccccccc | NULL | 200 | NULL |
| 300 | a | NULL | 300 | NULL |
+-----+----------------------+-------+------+------+
10 rows in set (0.00 sec)
[root@localhost] {14:28:30} (d2) [18]> show variables like '%dive%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200 |
+---------------------------+-------+
1 row in set (0.04 sec)
開啟optimizer_trace:
SET optimizer_trace="enabled=on"; 6.2 5.7 版本測試
[root@localhost] {14:33:02} (d2) [6]>select version();
+------------+
| version() |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)
[root@localhost] {14:33:06} (d2) [7]> select * from t1 where stu in(3,5,100);
+----+------+-------+------+------+
| id | name | phone | stu | stu2 |
+----+------+-------+------+------+
| 3 | 3 | NULL | 3 | NULL |
| 5 | c | NULL | 5 | NULL |
+----+------+-------+------+------+
2 rows in set (0.00 sec)
[root@localhost] {14:33:09} (d2) [8]>select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from t1 where stu in(3,5,100)
TRACE: {
"steps": [
........省略.............
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_stu",
"ranges": [
"3 <= stu <= 3",
"5 <= stu <= 5",
"100 <= stu <= 100"
],
"index_dives_for_eq_ranges": true, --確實使用索引下探
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3,
"cost": 6.61,
"chosen": false,
"cause": "cost"
}
],
查詢加上force index的hint，使該查詢滿足跳過索引下探的5個條件，再次測試，執行計劃上沒看出區別
[root@localhost] {14:33:39} (d2) [9]>select * from t1 force index(idx_stu) where stu in(3,5,100);
+----+------+-------+------+------+
| id | name | phone | stu | stu2 |
+----+------+-------+------+------+
| 3 | 3 | NULL | 3 | NULL |
| 5 | c | NULL | 5 | NULL |
+----+------+-------+------+------+
2 rows in set (0.00 sec)
[root@localhost] {14:37:03} (d2) [10]>select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from t1 force index(idx_stu) where stu in(3,5,100)
TRACE: {
"steps": [
........省略.............
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_stu",
"ranges": [
"3 <= stu <= 3",
"5 <= stu <= 5",
"100 <= stu <= 100"
],
"index_dives_for_eq_ranges": true, --依舊使用了索引下探
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3,
"cost": 6.61,
"chosen": true
}
6.3 8.0 版本測試
[root@localhost] {14:39:20} (d2) [24]> select version();
+-----------+
| version() |
+-----------+
| 8.0.16 |
+-----------+
1 row in set (0.00 sec)
[root@localhost] {14:39:23} (d2) [25]> select * from t1 where stu in(3,5,100);;
+----+------+-------+------+------+
| id | name | phone | stu | stu2 |
+----+------+-------+------+------+
| 3 | 3 | NULL | 3 | NULL |
| 5 | c | NULL | 5 | NULL |
+----+------+-------+------+------+
2 rows in set (0.01 sec)
ERROR:
No query specified
[root@localhost] {14:39:26} (d2) [26]> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from t1 where stu in(3,5,100)
TRACE: {
"steps": [
........省略.............
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_stu",
"ranges": [
"3 <= stu <= 3",
"5 <= stu <= 5",
"100 <= stu <= 100"
],
"index_dives_for_eq_ranges": true, --使用了索引下探
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3,
"cost": 1.81,
"chosen": true
}
],
查詢加上force index的hint，使該查詢滿足跳過索引下探的5個條件，再次測試，執行計劃上看到跳過了索引下探
[root@localhost] {14:41:31} (d2) [28]> select * from t1 force index(idx_stu) where stu in(3,5,100);
+----+------+-------+------+------+
| id | name | phone | stu | stu2 |
+----+------+-------+------+------+
| 3 | 3 | NULL | 3 | NULL |
| 5 | c | NULL | 5 | NULL |
+----+------+-------+------+------+
2 rows in set (0.00 sec)
[root@localhost] {14:41:36} (d2) [29]> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from t1 force index(idx_stu) where stu in(3,5,100)
TRACE: {
"steps": [
........省略.............
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_stu",
"ranges": [
"3 <= stu <= 3",
"5 <= stu <= 5",
"100 <= stu <= 100"
],
"index_dives_for_range_access": "skipped_due_to_force_index", --跳過了索引下探
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": "not applicable",
"cost": "not applicable",
"chosen": true
}
],
最后修改時間：2022-11-03 16:46:54
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
