MySQL Index dive の動きを観測してみた
Index dive とは
range スキャンの場合に、オプティマイザーがその範囲に含まれる行数を正確に見積もるための仕組みです。
インデックスダイブは特に値の分布が偏っているデータに対して、効果を発揮します。
eq_range_index_dive_limit パラメータ
インデックスダイブは、正確な行見積もりを提供しますが、式内の比較値の数が増えるほど、オプティマイザの行見積もりの生成に時間がかかるようになります。 インデックス統計の使用は、インデックスダイブより正確ではありませんが、大きな値リストの場合に、行見積もりが高速になります。
https://dev.mysql.com/doc/refman/5.6/ja/range-optimization.html
OR条件の数や IN句に指定された条件の数 が多ければ多いほど、インデックスダイブのコストが高くなるとされています。
大量のOR/IN条件に対してはインデックスダイブを回避するよう、 eq_range_index_dive_limit パラメータで制限がされています。
ORやINの数が eq_range_index_dive_limit - 1 以上になると、インデックスダイブが無効化されます（代わりに統計情報から見積もりを取得します）。
eq_range_index_dive_limit=0 の場合は、上限なしです。常にインデックスダイブが有効となります。
MySQL 8.0 の eq_range_index_dive_limit の デフォルト値は 200 です。
Index dive の効果を確認してみる
テストデータは以下のような分布のデータを用います。pk_p1 は 1〜100万まで連番で値が入っています。
pk_p1 が 1〜10 のレコードだけ、それぞれ 10万行あり、残りは1行です。効果をわかりやすくするため、非常に分布が偏ったデータで試します。
mysql> SELECT pk_p1, count(*) FROM t GROUP BY pk_p1 LIMIT 15 ;
+-------+----------+
| pk_p1 | count(*) |
+-------+----------+
| 1 | 100000 |
| 2 | 100000 |
| 3 | 100000 |
| 4 | 100000 |
| 5 | 100000 |
| 6 | 100000 |
| 7 | 100000 |
| 8 | 100000 |
| 9 | 100000 |
| 10 | 100000 |
| 11 | 1 |
| 12 | 1 |
| 13 | 1 |
| 14 | 1 |
| 15 | 1 |
+-------+----------+
15 rows in set (0.15 sec)
mysql> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`pk_p1` int NOT NULL,
`pk_p2` int NOT NULL,
`c1` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`pk_p1`,`pk_p2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
このクエリの見積もりをインデックスダイブあり・なしで比較してみます。実際のレコード数は100万件です。 オプティマイザーが 100万件に近い値を出していれば、正確な見積もりができていると言えます。
mysql> SELECT count(*) FROM t WHERE pk_p1 in (1,2,3,4,5,6,7,8,9,10) ; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.18 sec)
Index dive なし
eq_range_index_dive_limit を小さくして、インデックスダイブを発動しないようにします。
rows が 10 と、100万行から大きく外れた見積もりになっています。
mysql> SET eq_range_index_dive_limit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT count(*) FROM t WHERE pk_p1 in (1,2,3,4,5,6,7,8,9,10) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 10
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
Index dive あり
eq_range_index_dive_limit を大きくして、インデックスダイブを発動できるようにします。
rows が 1992788 と、インデックスダイブなしよりかなり近い見積もりになることが確認できました。
mysql> SET eq_range_index_dive_limit=100;
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT count(*) FROM t WHERE pk_p1 in (1,2,3,4,5,6,7,8,9,10) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1992788
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
Optimizer Trace で Index dive の有無を確認する
EXPLAIN 結果には インデックスダイブの有無は直接表現されないようです。
FORMAT=JSONとしても、インデックスダイブが行われてたかを示すフィールドは見つかりません。
mysql> EXPLAIN FORMAT=JSON SELECT count(*) FROM t WHERE pk_p1 IN (1,2,3,4,5,6,7,8,9,10) \G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "399006.02"
},
"table": {
"table_name": "t",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"pk_p1"
],
"key_length": "4",
"rows_examined_per_scan": 1992788,
"rows_produced_per_join": 1992788,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "199727.23",
"eval_cost": "199278.80",
"prefix_cost": "399006.03",
"data_read_per_join": "1G"
},
"used_columns": [
"pk_p1"
],
"attached_condition": "(`test`.`t`.`pk_p1` in (1,2,3,4,5,6,7,8,9,10))"
}
}
}
1 row in set, 1 warning (0.00 sec)
Optimizer Trace で実行計画を詳細に表示してみます。
range_scan_alternatives の中に、index_dives_for_eq_ranges でインデックスダイブの有無が確認できました！
mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT count(*) FROM t WHERE pk_p1 IN (1,2,3,4,5,6,7,8,9,10) \G
*************************** 1. row ***************************
count(*): 1000000
1 row in set (0.18 sec)
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: SELECT count(*) FROM t WHERE pk_p1 IN (1,2,3,4,5,6,7,8,9,10)
TRACE: {
<snip>
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"1 <= pk_p1 <= 1",
"2 <= pk_p1 <= 2",
"3 <= pk_p1 <= 3",
"4 <= pk_p1 <= 4",
"5 <= pk_p1 <= 5",
"6 <= pk_p1 <= 6",
"7 <= pk_p1 <= 7",
"8 <= pk_p1 <= 8",
"9 <= pk_p1 <= 9",
"10 <= pk_p1 <= 10"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
"rows": 1992788,
"cost": 199727,
"chosen": true
}
],
<snip>
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.04 sec)
次回予告
次回は、インデックスダイブのオーバーヘッドの計測にチャレンジしてみたいと思います。
