## モジュール 2: インデックス戦略 + このモジュールでは、以下のクエリに対して実行計画の最適化と比較を行います。 ```sql SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ```sql SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048; ``` ```sql SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048; ``` ```sql SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname='11 Com' AND ref_id=2048; ``` ```sql SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname like '11%' AND ref_id=2048; ``` ```sql SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048; ``` ```sql SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048; ``` ### 手順 1. インデックスを追加し、次のクエリの実行計画を確認 ```sql CREATE INDEX idx_pl_name on planetarySystem(pl_name); ANALYZE TABLE planetarySystem; EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ```output +-------------------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+ | IndexLookUp_11 | 0.00 | root | | | | ├─IndexRangeScan_8(Build) | 5.62 | cop[tikv] | table:planetarySystem, index:idx_pl_name(pl_name) | range:["HAT-P-42 b","HAT-P-42 b"], keep order:false | | └─Selection_10(Probe) | 0.00 | cop[tikv] | | eq(universe.planetarysystem.hostname, "HAT-P-42") | | └─TableRowIDScan_9 | 5.62 | cop[tikv] | table:planetarySystem | keep order:false | +-------------------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+ 4 rows in set (0.00 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: IndexLookUpとIndexRangeScan。 + オプティマイザーはこのクエリに対してどのくらいのコストを計算しましたか？ + 答え: 5.62行です。 + このクエリを解決するためにTiDBがアクセスする必要のあるカラムはどれですか？ + 答え: `pl_name`と`hostname`カラムです。 + このアクセス戦略は良いパフォーマンスをもたらしますか？ なぜですか？ + 答え: 場合によります。IndexLookUpとIndexRangeScanのアクセス方法は、TiDBがキーバリューに基づいて単一の行を選択できないことを示しています。パフォーマンスは、オプティマイザーが使用するキーがわずか数行に一致する場合にのみ良好であると考えられます。 ```sql DROP INDEX idx_pl_name ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.36 sec) ``` ### 手順 2. 別のインデックスを追加し、同じクエリの実行計画を確認 + `planetarySystem`テーブルで、以前の手順で調べたクエリがテーブル行にアクセスせずに実行されるように、適切なカラムにインデックスを付けるカバリングインデックス `idx_pl_name_hostname` を作成します。 ```sql CREATE INDEX idx_pl_name_hostname on planetarySystem(pl_name, hostname); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected, 1 warning (5.23 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ```output +------------------------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------+ | IndexReader_6 | 5.62 | root | | index:IndexRangeScan_5 | | └─IndexRangeScan_5 | 5.62 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname(pl_name, hostname) | range:["HAT-P-42 b" "HAT-P-42","HAT-P-42 b" "HAT-P-42"], keep order:false | +------------------------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------+ 2 rows in set (0.01 sec) ``` + 出力の違いを説明してください。 + 答え: クエリは今回`idx_pl_name_hostname`インデックスを使用してクエリを解決しており、テーブル行にアクセスする必要がありません。クエリへのアクセス方法は`IndexReader`と`IndexRangeScan`です。 ```sql DROP INDEX idx_pl_name_hostname ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.37 sec) ``` ### 手順 3. インデックスを追加し、次のクエリの実行計画を確認 ```sql CREATE INDEX idx_pl_name_hostname_ref ON planetarySystem(pl_name, hostname, ref_id); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected, 1 warning (2.88 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048; ``` ```output +---------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum | | └─TableReader_7 | 0.00 | root | | data:Selection_6 | | └─Selection_6 | 0.00 | cop[tikv] | | eq(universe.planetarysystem.hostname, "11 Com"), eq(universe.planetarysystem.ref_id, 2048) | | └─TableFullScan_5 | 34713.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ 4 rowsにセット (0.00 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: `TableReader`と`TableFullScan`。 + インデックス`idx_pl_name_hostname_ref`はこのクエリのパフォーマンス向上に役立ちますか？ なぜですか？ + 答え: いいえ。これは、インデックス `idx_pl_name_hostname_ref` が `pl_name`、`hostname`、`ref_id` の順序で構成されているためです。そのため、クエリもインデックスと同じ順序、すなわち `pl_name`、`hostname`、`ref_id` で使用する必要があります。 ### 手順 4. 次のクエリの実行計画を確認 ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048; ``` ```output +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum | | └─IndexLookUp_11 | 0.00 | root | | | | ├─Selection_10(Build) | 0.00 | cop[tikv] | | eq(universe.planetarysystem.ref_id, 2048) | | │ └─IndexRangeScan_8 | 5.62 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname_ref(pl_name, hostname, ref_id) | range:["11 Com b","11 Com b"], keep order:false | | └─TableRowIDScan_9(Probe) | 0.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ 5 rowsにセット (0.00 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: `IndexLookUp`と`IndexRangeScan`。 + オプティマイザーはこのクエリに対してどのくらいのコストを計算しましたか？ + 答え: 5.62。 + このクエリを解決するためにTiDBがアクセスする必要のあるカラムはどれですか？ + 答え: `pl_name`、`hostname`、`sy_snum`、`sy_pnum`、および`ref_id`。 + インデックス`idx_pl_name_hostname_ref`はこのクエリのパフォーマンス向上に役立ちますか？ なぜですか？ + 答え: はい。このクエリにはカラム`pl_name`に関連する条件が含まれていますが、インデックス`idx_pl_name_hostname_ref`が使用されます。しかし、カラム`ref_id`に関連する条件はインデックスを使用しません。なぜなら、カラム`hostname`に関連する条件がクエリに現れなかったからです。EXPLAINの出力の3行目、Selectionのセクションから、`ref_id`に関連する条件は`IndexRangeScan`の後に別途完了されることがわかります。 ### 手順 5. 次のクエリの実行計画を確認 ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname='11 Com' AND ref_id=2048; ``` ```output +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 1.00 | root | | universe.planetarysystem.pl_name, universe.planetarySystem.hostname, universe.planetarySystem.sy_snum, universe.planetarySystem.sy_pnum | | └─IndexLookUp_10 | 1.00 | root | | | | ├─IndexRangeScan_8(Build) | 1.00 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname_ref(pl_name, hostname, ref_id) | range:["11 Com b" "11 Com" 2048,"11 Com b" "11 Com" 2048], keep order:false | | └─TableRowIDScan_9(Probe) | 1.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ 4 rowsにセット (0.00 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: `IndexLookUp`と`IndexRangeScan`。 + オプティマイザーはこのクエリに対してどのくらいのコストを計算しましたか？ + 答え: 1.00。 + このクエリを解決するためにTiDBがアクセスする必要のあるカラムはどれですか？ + 答え: `pl_name`、`hostname`、`sy_snum`、`sy_pnum`、および`ref_id`。 + インデックス`idx_pl_name_hostname_ref`はこのクエリのパフォーマンス向上に役立ちますか？ なぜですか？ + 答え: はい。クエリには`pl_name`、`hostname`、および`ref_id`のカラムが含まれているため、`index idx_pl_name_hostname_ref`が使用されます。 ### 手順 6. 次のクエリの実行計画を確認 ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname like '11%' AND ref_id=2048; ``` ```output +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum | | └─IndexLookUp_11 | 0.00 | root | | | | ├─Selection_10(Build) | 0.00 | cop[tikv] | | eq(universe.planetarysystem.ref_id, 2048) | | │ └─IndexRangeScan_8 | 0.00 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname_ref(pl_name, hostname, ref_id) | range:["11 Com b" "11","11 Com b" "12"), keep order,false | | └─TableRowIDScan_9(Probe) | 0.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ 5 rowsにセット (0.00 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: `IndexLookUp`と`IndexRangeScan`。 + このクエリを解決するためにTiDBがアクセスする必要のあるカラムはどれですか？ + 答え: `pl_name`、`hostname`、`sy_snum`、`sy_pnum`、および`ref_id`。 + インデックス`idx_pl_name_hostname_ref`はこのクエリのパフォーマンス向上に役立ちますか？ なぜですか？ + 答え: はい。インデックス `idx_pl_name_hostname_ref` 内のカラム `pl_name`、`hostname`、`ref_id` はすべてクエリ内に現れますが、`hostname` カラムに対する条件が等価クエリではなく範囲クエリであるため、インデックスで `ref_id` カラムを使用することはできません。EXPLAIN 出力の3行目、Selection セクションから、`ref_id` に関連する条件が IndexRangeScan の後に個別に完了することが確認できます。 ### 手順 7. 次のクエリの実行計画を確認 ```sql EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048; ``` ```output +---------------------------+----------+-----------+----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+----------+-----------+----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | universe.planetarysystem.id, universe.planetarysystem.pl_name, universe.planetarysystem.hostname | | └─IndexReader_10 | 0.00 | root | | index:Selection_9 | | └─Selection_9 | 0.00 | cop[tikv] | | eq(universe.planetarysystem.hostname, "11 Com"), eq(universe.planetarysystem.ref_id, 2048) | | └─IndexFullScan_8 | 34713.00 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname_ref(pl_name, hostname, ref_id) | keep order:false | +---------------------------+----------+-----------+----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+ 4 rowsにセット (0.01 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: `IndexReader`と`IndexFullScan`。 + インデックス`idx_pl_name_hostname_ref`はこのクエリのパフォーマンス向上に役立ちますか？ なぜですか？ + 答え: はい。インデックス `idx_pl_name_hostname_ref` のカラム `pl_name` はクエリ条件で使用されていませんが、SELECT ステートメントが `id`、`pl_name`、`hostname` のカラムを含んでいるため、これらすべてはインデックス `idx_pl_name_hostname_ref` に含まれていますので、`IndexFullScan` を使用することができます。 + このアクセス戦略は良いパフォーマンスをもたらしますか？ なぜですか？ + 答え: おそらくそうではありません。データボリュームが増加するにつれて、`IndexFullScan`がスキャンする必要があるインデックスデータが増加するため、パフォーマンスが低下します。 ```sql DROP INDEX idx_pl_name_hostname_ref ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.36 sec) ``` ### 手順 8. インデックスを追加し、次のクエリの実行計画を確認 + `planetarySystem`テーブルで、`ref_id`カラムでインデックス`idx_ref_id`を追加します。 ```sql CREATE INDEX idx_ref_id ON planetarySystem(ref_id); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected, 1 warning (3.26 sec) ``` ```sql EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048; ``` ```output +----------------------------------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------+ | Projection_4 | 17.00 | root | | universe.planetarysystem.id, universe.planetarysystem.pl_name, universe.planetarysystem.hostname | | └─IndexMerge_11 | 17.00 | root | | type: union | | ├─TableRangeScan_8(Build) | 1.00 | cop[tikv] | table:planetarySystem | range:[835868090839969012,835868090839969012], keep order:false | | ├─IndexRangeScan_9(Build) | 16.00 | cop[tikv] | table:planetarySystem, index:idx_ref_id(ref_id) | range:[2048,2048], keep order:false | | └─TableRowIDScan_10(Probe) | 17.00 | cop[tikv] | table:planetarySystem | keep order:false | +----------------------------------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------+ 5 rowsにセット (0.00 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: `IndexMerge`。 + このアクセス戦略は良いパフォーマンスをもたらしますか？ なぜですか？ + 答え: 場合によります。インデックスマージのアクセス戦略は、TiDBがクエリに使用できる複数のインデックスを見つけたことを示しています。ここでは、主キーと`ref_id`インデックスを使用しています。インデックスマージメソッドは複数のレンジスキャンで行を取得し、結果をマージするため、テーブルに高い選択性のインデックスがあればパフォーマンスは良好です。`operator info`のunionエントリが、TiDBがインデックスマージ結合タイプに対してインデックススキャンをどのようにマージするかを示しています。 ```sql DROP INDEX idx_ref_id ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.36 sec) ```