MySQL SQL 最適化チュートリアル: IN クエリと RANGE クエリ

まず、in() クエリについて説明します。「High Performance MySQL」では、インデックスでは範囲フィールドの後の部分が無効であるため、in メソッドを使用すると、特定の範囲クエリを効果的に置き換えてクエリの効率を向上できることが説明されています。 in メソッドを使用する場合、MySQL オプティマイザは実際にクエリを n*m の組み合わせに変換し、最後に戻り値をマージします。これは、union に少し似ていますが、より効率的です。同時に、いくつかの問題もあります。

MySQL の古いバージョンでは、IN() の組み合わせ条件が多すぎると多くの問題が発生します。クエリの最適化には長い時間がかかり、大量のメモリを消費する可能性があります。新しいバージョンの MySQL では、組み合わせの数が一定数を超えるとプラン評価が実行されないため、MySQL がインデックスを有効に活用できない可能性があります。

ここでの「特定の数値」は、MySQL 5.6.5 以降のバージョンのパラメータ eq_range_index_dive_limit によって制御されます (アドバイスを提供してくれた @叶金荣に感謝します)。デフォルト設定は 10 ですが、バージョン 5.7 以降では 200 に変更されます。もちろん手動で設定することもできます。 5.6 マニュアルの手順を見てみましょう。

eq_range_index_dive_limit システム変数を使用すると、オプティマイザが 1 つの行推定戦略から別の行推定戦略に切り替える値の数を設定できます。統計の使用を無効にして常にインデックスダイブを使用するには、eq_range_index_dive_limit を 0 に設定します。最大 N 個の等価範囲の比較にインデックスダイブの使用を許可するには、eq_range_index_dive_limit を N + 1 に設定します。
eq_range_index_dive_limit は、MySQL 5.6.5 以降で使用できます。5.6.5 より前では、オプティマイザはインデックスダイブを使用します。これは、eq_range_index_dive_limit=0 と同等です。

つまり:

1. eq_range_index_dive_limit = 0: インデックスダイブのみ使用可能
2. 0 < eq_range_index_dive_limit <= N インデックス統計を使用する
3. eq_range_index_dive_limit > N: インデックスダイブのみ使用可能

インデックスダイブとインデックス統計は、MySQL オプティマイザがコストを見積もるために使用する方法です。前者は統計速度が遅いですが、正確な値を取得できます。一方、後者は統計速度が速いですが、データが正確でない可能性があります。

オプティマイザーは、インデックスまたはインデックス統計を詳しく調べることで、各範囲の行数を推定できます。

MySQL 5.7 では、IN() リストの数が 10 を超えることが多いため、範囲等価演算 (IN()) の実行プランが可能な限り正確になるように、デフォルト値が 10 から 200 に変更されました。

事前に言っておく

今日の記事のトピックは 2 つあります。

範囲クエリとインデックスの使用
eq_range_index_dive_limit の説明

範囲クエリとインデックスの使用

SQL は次のとおりです。

SELECT * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN('0','-2') 
ORDER BY dateline DESC LIMIT 10;

インデックスは次のとおりです。

+----------------+-------------+--------------+--------------+--------------+-------------+------------+-----------+-----------+-----------+------------+------------+
| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |
+----------------+-------------+--------------+--------------+--------------+-------------+------------+-----------+-----------+-----------+------------+------------+
| pre_forum_post | 0 | PRIMARY | 1 | tid | A | NULL | NULL | NULL | | BTREE | | | 
| pre_forum_post | 0 | PRIMARY | 2 | 位置 | 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 | 非表示 | A | 945236 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | displayorder | 3 | dateline | A | 25521392 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | 最初 | 1 | tid | A | 880048 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | 最初 | 2 | 最初 | 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 | 非表示 | 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 | 非表示 | 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 | | | 
+----------------+-------------+--------------+--------------+--------------+-------------+------------+-----------+-----------+-----------+------------+------------+

実行計画を見てみましょう:

root@localhost 16:08:27 [ultrax]> SELECT * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN('0','-2') を説明します 
  -> ORDER BY dateline DESC LIMIT 10;
+----+-------------+----------------+-------+--------------------------------------------------------+--------------+---------+------+------+---------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------------+-------+--------------------------------------------------------+--------------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | pre_forum_post | range | PRIMARY,displayorder,first,mul_test,idx_1 | displayorder | 4 | NULL | 54 | インデックス条件の使用; filesort の使用 | 
+----+-------------+----------------+-------+--------------------------------------------------------+--------------+---------+------+------+---------------------------------------+
セット内の 1 行 (0.00 秒)

MySQL オプティマイザは、これは範囲クエリであると認識するため、(tid、invisible、dateline) インデックスの dateline フィールドは確実に使用されません。つまり、この SQL の最終的なソートでは、インデックス内で直接ソートアクションを完了するのではなく、一時的な結果セットが生成され、その後結果セット内でソートが完了します。そこで、インデックスを追加しようとしました。

root@localhost 16:09:06 [ultrax]> テーブル pre_forum_post を変更し、インデックス idx_1 (tid,dateline) を追加します。  
クエリは正常、20374596 行が影響を受け、警告は 0 件 (600.23 秒)
レコード: 0 重複: 0 警告: 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 | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------------+------+---------------+--------+---------+---+-------------+-------------+
| 1 | SIMPLE | pre_forum_post | ref | idx_1 | idx_1 | 3 | const | 120646 | where の使用 | 
+----+-------------+----------------+------+---------------+--------+---------+---+-------------+-------------+
セット内の 1 行 (0.00 秒)
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 行セット (0.40 秒)
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 です (0.00 秒)

実験により、その効果は優れていることが証明されました。実際、理解するのは難しくありません。上で述べたように、in() は MySQL オプティマイザでさまざまな組み合わせでデータを取得します。ソートやグループが追加された場合、一時的な結果セットに対してのみ操作できます。つまり、インデックスにソートやグループフィールドが含まれていても、それはまだ役に立ちません。唯一の不満は、MySQL オプティマイザーの選択がまだ十分に信頼できないことです。

要約すると、MySQL クエリで in() を使用する場合は、in() リストの数と eq_range_index_dive_limit の値 (詳細は下記を参照) に注意するだけでなく、SQL に並べ替え/グループ化/重複排除などが含まれている場合は、インデックスの使用にも注意する必要があります。

eq_range_index_dive_limit の説明

上記の例を引き続き使用する場合、idx_1 を直接使用できないのはなぜですか?このインデックスのみを強制的に使用するにはヒントを使用する必要がありますか?ここではまず、eq_range_index_dive_limit の値を確認します。

root@localhost 22:38:05 [ultrax]> 'eq_range_index_dive_limit' のような変数を表示します。
+---------------------------+-------+
| 変数名 | 値 |
+---------------------------+-------+
| 等価範囲インデックスダイブ制限 | 2 | 
+---------------------------+-------+
セット内の 1 行 (0.00 秒)

前述の状況によると、0 < eq_range_index_dive_limit <= N はインデックス統計を使用するので、OPTIMIZER_TRACE を使用して確認してみましょう。

{
 "インデックス": "表示順序",
 「範囲」: [
  「7932552 <= tid <= 7932552 かつ -2 <= 非表示 <= -2」、
  「7932552 <= tid <= 7932552 かつ 0 <= 非表示 <= 0」
 ]、
 "index_dives_for_eq_ranges": false,
 "rowid_ordered": 偽、
 "using_mrr": 偽、
 "index_only": 偽、
 「行」: 54,
 「コスト」: 66.81,
 「選択済み」: true
}
// インデックス ダイブは false で、最終的に選択されたものは true です
...
{
 "インデックス": "idx_1",
 「範囲」: [
  「7932552 <= tid <= 7932552」
 ]、
 "index_dives_for_eq_ranges": true、
 "rowid_ordered": 偽、
 "using_mrr": 偽、
 "index_only": 偽、
 「行数」: 120646,
 「コスト」: 144776,
 「選択」：偽、
 「原因」：「コスト」
}

displayorder インデックスのコストは 66.81 であるのに対し、idx_1 のコストは 120646 であり、最終的に MySQL オプティマイザーは displayorder インデックスを選択することがわかります。したがって、eq_range_index_dive_limit を > N に設定する場合、より正確な実行プランを取得するためにインデックスダイブ計算方法を使用する必要がありますか?

root@localhost 22:52:52 [ultrax]> eq_range_index_dive_limit を 3 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
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 | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------------+------+------------------------------------------+--------+---------+---+-------------+-------------+
| 1 | SIMPLE | pre_forum_post | ref | PRIMARY,displayorder,first,mul_test,idx_1 | idx_1 | 3 | const | 120646 | where の使用 | 
+----+-------------+----------------+------+------------------------------------------+--------+---------+---+-------------+-------------+
セット内の 1 行 (0.00 秒)

optimize_traceの結果は次のとおりです。

{
 "インデックス": "表示順序",
 「範囲」: [
  「7932552 <= tid <= 7932552 かつ -2 <= 非表示 <= -2」、
  「7932552 <= tid <= 7932552 かつ 0 <= 非表示 <= 0」
 ]、
 "index_dives_for_eq_ranges": true、
 "rowid_ordered": 偽、
 "using_mrr": 偽、
 "index_only": 偽、
 「行数」: 188193,
 「コスト」: 225834,
 「選択済み」: true
}
...
{
 "インデックス": "idx_1",
 「範囲」: [
  「7932552 <= tid <= 7932552」
 ]、
 "index_dives_for_eq_ranges": true、
 "rowid_ordered": 偽、
 "using_mrr": 偽、
 "index_only": 偽、
 「行数」: 120646,
 「コスト」: 144776,
 「選択済み」: true
}
...
 「プランの費用」: 144775,
 "計画行数": 120646,
 「選択済み」: true
// 代替インデックス選択では、両方のインデックスが選択されます。最終的なロジック最適化では、コストが最も低いインデックス、つまり idx_1 が選択されます。

上記は、eq_range_index_dive_limit の値が等範囲クエリにおける MySQL オプティマイザの計算オーバーヘッドにどのように影響し、それによってインデックスの選択にどのように影響するかを示しています。さらに、プロファイリングを使用して、オプティマイザの統計的な時間消費を確認することもできます。

インデックスダイブ

+----------------------+----------+
| ステータス | 期間 |
+----------------------+----------+
| 開始 | 0.000048 | 
| 権限を確認中 | 0.000004 | 
| オープニングテーブル | 0.000015 | 
| 初期化 | 0.000044 | 
| システムロック | 0.000009 | 
| 最適化 | 0.000014 | 
| 統計 | 0.032089 | 
| 準備中 | 0.000022 | 
| ソート結果 | 0.000003 | 
| 実行中 | 0.000003 | 
| データ送信中 | 0.000101 | 
| 終了 | 0.000004 | 
| クエリ終了 | 0.000002 | 
| テーブルのクローズ | 0.000009 | 
| アイテムを解放 | 0.000013 | 
| クリーンアップ | 0.000012 | 
+----------------------+----------+

インデックス統計

+----------------------+----------+
| ステータス | 期間 |
+----------------------+----------+
| 開始 | 0.000045 | 
| 権限を確認中 | 0.000003 | 
| オープニングテーブル | 0.000014 | 
| 初期化 | 0.000040 | 
| システムロック | 0.000008 | 
| 最適化 | 0.000014 | 
| 統計 | 0.000086 | 
| 準備中 | 0.000016 | 
| ソート結果 | 0.000002 | 
| 実行中 | 0.000002 | 
| データ送信中 | 0.000016 | 
| ソートインデックスを作成しています | 0.412123 | 
| 終了 | 0.000012 | 
| クエリ終了 | 0.000004 | 
| テーブルのクローズ | 0.000013 | 
| アイテムを解放 | 0.000023 | 
| クリーンアップ | 0.000015 | 
+----------------------+----------+

eq_range_index_dive_limit を増やしてインデックスダイブを使用すると、オプティマイザ統計は ndex 統計方法よりも大幅に時間がかかりますが、最終的にはより合理的な実行プランが使用されることがわかります。統計時間は 0.032089 秒対 0.000086 秒ですが、SQL 実行時間は 0.41 秒対 0.03 秒程度です。

付録: optimize_trace の使い方

optimizer_trace を 'enabled=on' に設定します。 
information_schema.optimizer_trace\G から * を選択
// 注意: optimizer_trace はセッション モードでのみデバッグを有効にすることを推奨します

参考文献

http://dev.mysql.com/doc/refman/5.6/en/範囲最適化.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/

これで、in クエリと range クエリに関する MySQL SQL 最適化チュートリアルに関するこの記事は終了です。in クエリと range クエリに関する MySQL SQL 最適化の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: