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

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 秒)

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 秒)

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

{
 "インデックス": "表示順序",
 「範囲」: [
  「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,
 「選択」：偽、
 「原因」：「コスト」
}

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 秒)

{
 "インデックス": "表示順序",
 「範囲」: [
  「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 が選択されます。

+----------------------+----------+
| ステータス | 期間 |
+----------------------+----------+
| 開始 | 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 | 
+----------------------+----------+

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