## モジュール 3: ORDER BY によるインデックス戦略 + このモジュールでは、以下のクエリを最適化します ```sql SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ### 手順 1. 次のクエリの実行計画を確認する ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output +---------------------------+----------+-----------+-----------------------+-------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+----------+-----------+-----------------------+-------------------------------------------------+ | Sort_5 | 5.81 | root | | universe.planetarysystem.disc_year:desc | | └─TableReader_10 | 5.81 | root | | data:Selection_9 | | └─Selection_9 | 5.81 | cop[tikv] | | eq(universe.planetarysystem.hostname, "14 Her") | | └─TableFullScan_8 | 34713.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------+----------+-----------+-----------------------+-------------------------------------------------+ 4 rows in set (0.00 sec) ``` + このクエリのアクセスメソッドは何ですか？ + 答え: TableFullScan、Selection、TableReader、Sort。 + オプティマイザーがこのクエリで使用できるインデックスはどれですか？ + 答え: なし + `operator info` 列にはどのような情報が含まれていますか？ + 答え: `eq(universe.planetarysystem.hostname, "14 Her")`: クエリは WHERE 句を使用して出力行をフィルタリングします。`universe.planetarysystem.disc_year:desc`: TiDBはテーブル全体をもう一度通過して、行をソート順に取得する方法を決定する必要があります。ソートは全ての行を反復して、WHERE句に一致する行のソートキーを行のポインターと共に格納することによって行われます。TiDBサーバーはそのキーをソートし、要求された順序で行を取得します。 + このクエリのパフォーマンスは良いですか？ なぜですか？ + 答え: いいえ。このクエリは完全なテーブルスキャンを必要とし、キーを使用することなく、`disc_year` カラムに対するソートも必要です。ソートは全てのデータを1つのTiDBサーバーにロードしてタスクを完了する必要があります。 + テーブルの完全なテーブルスキャンを防ぐためにどのような変更を加える必要がありますか？ + 答え: `planetarySystem` テーブルの `hostname` カラムにインデックスを追加する必要があります。 ### 手順 2. インデックスを追加し、次のクエリの実行計画を確認する ```sql ALTER TABLE planetarySystem ADD INDEX idx_hostname(hostname); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected, 1 warning (3.41 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output +----------------------------------+---------+-----------+-----------------------------------------------------+---------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+-----------------------------------------------------+---------------------------------------------+ | Sort_5 | 5.81 | root | | universe.planetarysystem.disc_year:desc | | └─IndexLookUp_13 | 5.81 | root | | | | ├─IndexRangeScan_11(Build) | 5.81 | cop[tikv] | table:planetarySystem, index:idx_hostname(hostname) | range:["14 Her","14 Her"], keep order:false | | └─TableRowIDScan_12(Probe) | 5.81 | cop[tikv] | table:planetarySystem | keep order:false | +----------------------------------+---------+-----------+-----------------------------------------------------+---------------------------------------------+ 4 rows in set (0.00 sec) ``` + このクエリのアクセスメソッドは何ですか？ + 答え: IndexRangeScan、IndexLookUp、Sort。 + オプティマイザーが使用するキーはどれですか？ + 答え: `hostname` + `operator info` 列にはどのような情報が含まれていますか？ + 答え: `range:["14 Her","14 Her"], keep order:false`; `universe.planetarysystem.disc_year:desc`。 + 前の手順で実装した変更はクエリのパフォーマンスを改善しますか？ なぜですか？ + 答え: はい。クエリは完全なテーブルスキャンを必要としなくなります。代わりに、`hostname` カラムのインデックスから行を取得するために `IndexRangeScan` と `IndexLookUp` を使用します。さらに、`task: cop[tikv]` の列は、すべてのインデックスフィルタリング操作が TiKV で並行して完了することを示しています。 + ソートの実行を防ぐためには何をしなければなりませんか？ + 答え: `planetarySystem` テーブルに `hostname` と `disc_year` カラムを含む複合インデックスを追加する必要があります。`hostname` カラムのインデックスはこの場合必要ありません、なぜなら TiDB はインデックスプレフィックスを使用できるからです。 ### 手順 3. インデックスを追加し、次のクエリの実行計画を確認する + 前の手順で特定した変更を行い、ソート操作を削除します。 ```sql ALTER TABLE planetarySystem DROP INDEX idx_hostname; ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year(hostname, disc_year); ``` ```output Query OK, 0 rows affected (3.06 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output +----------------------------------+---------+-----------+--------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+--------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+ | Projection_18 | 5.81 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.disc_year | | └─IndexLookUp_17 | 5.81 | root | | | | ├─IndexRangeScan_15(Build) | 5.81 | cop[tikv] | table:planetarySystem, index:idx_hostname_disc_year(hostname, disc_year) | range:["14 Her","14 Her"], keep order:true, desc | | └─TableRowIDScan_16(Probe) | 5.81 | cop[tikv] | table:planetarySystem | keep order:false | +----------------------------------+---------+-----------+--------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + オプティマイザーが使用するキーはどれですか？ + 答え: 前の手順で追加したキーです。（例の解決策では `idx_hostname_disc_year`） + `operator info` 列にはどのような情報が含まれていますか？ + 答え: `range:["14 Her","14 Her"], keep order:true, desc`。 + この変更はクエリのパフォーマンスを改善しますか？ なぜですか？ + 答え: はい。`hostname` と `disc_year` カラムに複合キーを追加することで、TiDB サーバーでのソートを行う必要がなくなります。 + インデックス行を見つけた後、主キーのルックアップを必要としないようにするためには何をしなければなりませんか？ + 答え: `planetarySystem` テーブルに `hostname`、`disc_year` と `pl_name` カラムを含む複合インデックスを追加する必要があります。一方で `hostname` と `disc_year` カラムの複合インデックスを削除する必要があります、なぜなら TiDB はインデックスプレフィックスを使用できるからです。 ### 手順 4. インデックスを追加し、次のクエリの実行計画を確認する ```sql ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year; ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year_pl_name(hostname, disc_year, pl_name); ``` ```output Query OK, 0 rows affected (0.35 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output +-------------------------+---------+-----------+-------------------------------------------------------------------------------------------+--------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+---------+-----------+-------------------------------------------------------------------------------------------+--------------------------------------------------+ | IndexReader_12 | 5.81 | root | | index:IndexRangeScan_11 | | └─IndexRangeScan_11 | 5.81 | cop[tikv] | table:planetarySystem, index:idx_hostname_disc_year_pl_name(hostname, disc_year, pl_name) | range:["14 Her","14 Her"], keep order:true, desc | +-------------------------+---------+-----------+-------------------------------------------------------------------------------------------+--------------------------------------------------+ 2 rows in set (0.00 sec) ``` + オプティマイザーが使用するキーはどれですか？ + 答え: idx_hostname_disc_year_pl_name + `operator info` 列にはどのような情報が含まれていますか？ + 答え: `range:["14 Her","14 Her"], keep order:true, desc`; `index:IndexRangeScan_11` + この変更はクエリのパフォーマンスを改善しますか？ なぜですか？ + 答え: はい。クエリは現在必要な全データをインデックスから取得しており、基本テーブルにアクセスしていません（`operator info`: `index:IndexRangeScan_11`）。この種のインデックスはカバリングインデックスとして知られています。読み取りパフォーマンスは非常に良くなりますが、TiDBはインデックスも更新しなければならないため、テーブルの更新にはパフォーマンスへの悪影響があるかもしれません。 ### 手順 5. インデックスを削除 + この練習で追加したインデックスを削除し、`planetarySystem` テーブルを元の状態に戻します。 ```sql ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year_pl_name; ``` ```output Query OK, 0 rows affected (0.35 sec) ```