## モジュール 4: JOIN によるインデックス戦略 + このモジュールでは、次の2つのクエリを最適化します： ```sql SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference FROM planetarySystem A, discoveryFacility B, parameterReference C WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; ``` ```sql SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ### 手順 1. 次のクエリの実行計画を確認する ```sql EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference FROM planetarySystem A, discoveryFacility B, parameterReference C WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; ``` ```output +------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_13 | 1.98 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.discoverymethod, universe.planetarysystem.disc_year, universe.discoveryfacility.name, universe.parameterreference.ref_name | | └─IndexJoin_18 | 1.98 | root | | inner join, inner:TableReader_15, outer key:universe.planetarysystem.df_id, inner key:universe.discoveryfacility.id, equal cond:eq(universe.planetarysystem.df_id, universe.discoveryfacility.id) | | ├─IndexJoin_31(Build) | 1.98 | root | | inner join, inner:TableReader_27, outer key:universe.planetarysystem.ref_id, inner key:universe.parameterreference.id, equal cond:eq(universe.planetarysystem.ref_id, universe.parameterreference.id) | | │ ├─TableReader_40(Build) | 1.98 | root | | data:Selection_39 | | │ │ └─Selection_39 | 1.98 | cop[tikv] | | gt(universe.planetarysystem.disc_year, 2020), like(universe.planetarysystem.hostname, "14 Her", 92) | | │ │ └─TableFullScan_38 | 34713.00 | cop[tikv] | table:A | keep order:false | | │ └─TableReader_27(Probe) | 0.00 | root | | data:Selection_26 | | │ └─Selection_26 | 0.00 | cop[tikv] | | like(universe.parameterreference.ref_name, "Rosenthal%", 92) | | │ └─TableRangeScan_25 | 1.98 | cop[tikv] | table:C | range: decided by [universe.planetarysystem.ref_id], keep order:false | | └─TableReader_15(Probe) | 1.98 | root | | data:TableRangeScan_14 | | └─TableRangeScan_14 | 1.98 | cop[tikv] | table:B | range: decided by [universe.planetarysystem.df_id], keep order:false | +------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.01 sec) ``` + 最初に結合される2つのテーブルはどれですか？使用する結合の種類は何ですか？ + 答え: テーブル `planetarySystem` (A) と `parameterReference` (C) が最初に結合され、結合タイプは `IndexJoin` です。 + どのテーブルが2番目のテーブル結合にのみ参加しますか？使用する結合の種類は何ですか？ + 答え: テーブル `discoveryFacility` (B)、結合タイプは `IndexJoin` です。 + 各テーブルクエリのアクセスメソッドは何ですか？ + 答え: テーブル `planetarySystem` (A) は `TableFullScan`、`Selection`、`TableReader`；テーブル `parameterReference` (C) は `TableRangeScan`、`Selection`、`TableReader`；テーブル `discoveryFacility` (B) は `TableRangeScan` と `TableReader`。 + このアクセス戦略は良いパフォーマンスをもたらしますか？ なぜですか？ + 答え: いいえ。これは、`WHERE`句に`C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020`のようなフィルター条件があるためです。これらのフィルター処理を高速化するためにTiKVノードにインデックスを作成し、`TableFullScan`や`SELECT`のような他のフィルタリング操作を回避することで、IO圧力を軽減することができます。 ### 手順 2. インデックスを追加し、次のクエリの実行計画を確認する ```sql CREATE INDEX idx_hostname_disc_year ON planetarySystem(hostname, disc_year); CREATE INDEX idx_ref_name ON parameterReference(ref_name); ANALYZE TABLE planetarySystem; ANALYZE TABLE parameterReference; ``` ```output Query OK, 0 rows affected, 1 warning (0.63 sec) ``` ```sql EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference FROM planetarySystem A, discoveryFacility B, parameterReference C WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; ``` ```output +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_13 | 1.98 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.discoverymethod, universe.planetarysystem.disc_year, universe.discoveryfacility.name, universe.parameterreference.ref_name | | └─IndexJoin_18 | 1.98 | root | | inner join, inner:TableReader_15, outer key:universe.planetarysystem.df_id, inner key:universe.discoveryfacility.id, equal cond:eq(universe.planetarysystem.df_id, universe.discoveryfacility.id) | | ├─HashJoin_37(Build) | 1.98 | root | | inner join, equal:[eq(universe.planetarysystem.ref_id, universe.parameterreference.id)] | | │ ├─IndexLookUp_43(Build) | 1.98 | root | | | | │ │ ├─IndexRangeScan_41(Build) | 1.98 | cop[tikv] | table:A, index:idx_hostname_disc_year(hostname, disc_year) | range:("14 Her" 2020,"14 Her" +inf], keep order:false | | │ │ └─TableRowIDScan_42(Probe) | 1.98 | cop[tikv] | table:A | keep order:false | | │ └─IndexReader_45(Probe) | 2.72 | root | | index:IndexRangeScan_44 | | │ └─IndexRangeScan_44 | 2.72 | cop[tikv] | table:C, index:idx_ref_name(ref_name) | range:["Rosenthal","Rosentham"), keep order:false | | └─TableReader_15(Probe) | 1.98 | root | | data:TableRangeScan_14 | | └─TableRangeScan_14 | 1.98 | cop[tikv] | table:B | range: decided by [universe.planetarysystem.df_id], keep order:false | +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec) ``` + 最初に結合される2つのテーブルはどれですか？使用する結合の種類は何ですか？ + 答え: テーブル `planetarySystem` (A) と `parameterReference` (C) が最初に結合され、結合タイプは `HashJoin` です。 + どのテーブルが2番目のテーブル結合にのみ参加しますか？使用する結合の種類は何ですか？ + 答え: テーブル `discoveryFacility` (B)、結合タイプは `IndexJoin` です。 + 各テーブルクエリのアクセスメソッドは何ですか？ + 答え: `IndexRangeScan` と `IndexLookUp` はテーブル `planetarySystem` (A); `IndexRangeScan` と `IndexReader` はテーブル `parameterReference` (C); `TableRangeScan` と `TableReader` はテーブル `discoveryFacility` (B)です。 + このアクセス戦略は良いパフォーマンスをもたらしますか？ なぜですか？ + 答え: はい。これは `TableFullScan` やその他のフィルタリング操作が、インデックスを使用することによって回避されるためです。 ### 手順 3. 索引を追加し、次のクエリの実行計画を確認する ```sql CREATE INDEX idx_ref_id ON planetarySystem(ref_id); CREATE INDEX idx_df_id ON planetarySystem(df_id); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected, 1 warning (4.00 sec) ``` ```sql EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference FROM planetarySystem A, discoveryFacility B, parameterReference C WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; ``` ```output +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_13 | 1.98 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.discoverymethod, universe.planetarysystem.disc_year, universe.discoveryfacility.name, universe.parameterreference.ref_name | | └─IndexJoin_19 | 1.98 | root | | inner join, inner:TableReader_16, outer key:universe.planetarysystem.df_id, inner key:universe.discoveryfacility.id, equal cond:eq(universe.planetarysystem.df_id, universe.discoveryfacility.id) | | ├─HashJoin_71(Build) | 1.98 | root | | inner join, equal:[eq(universe.planetarysystem.ref_id, universe.parameterreference.id)] | | │ ├─IndexLookUp_85(Build) | 1.98 | root | | | | │ │ ├─IndexRangeScan_83(Build) | 1.98 | cop[tikv] | table:A, index:idx_hostname_disc_year(hostname, disc_year) | range:("14 Her" 2020,"14 Her" +inf], keep order:false | | │ │ └─TableRowIDScan_84(Probe) | 1.98 | cop[tikv] | table:A | keep order:false | | │ └─IndexReader_87(Probe) | 2.72 | root | | index:IndexRangeScan_86 | | │ └─IndexRangeScan_86 | 2.72 | cop[tikv] | table:C, index:idx_ref_name(ref_name) | range:["Rosenthal","Rosentham"), keep order:false | | └─TableReader_16(Probe) | 1.98 | root | | data:TableRangeScan_15 | | └─TableRangeScan_15 | 1.98 | cop[tikv] | table:B | range: decided by [universe.planetarysystem.df_id], keep order:false | +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec) ``` + 三つのテーブルの結合順序が変わりましたか？ なぜですか？ + 答え: いいえ。オプティマイザーは、各テーブルに関与するデータ量に基づいてテーブルの結合順序を決定することがよくあります。データ量が最も少ないテーブルが最初に結合されます。クエリのフィルタリング条件に基づいて、結合に参加する各テーブルの行数を決定します。以下の通りです。 ```sql SELECT COUNT(*) FROM planetarysystem WHERE hostname LIKE '14 Her' AND disc_year > 2020; SELECT COUNT(*) FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'; SELECT COUNT(*) FROM discoveryFacility; ``` ```output +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec) +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) +----------+ | COUNT(*) | +----------+ | 70 | +----------+ 1 row in set (0.00 sec) ``` + 結論としては、`planetarySystem` と `parameterReference` テーブルは最も少ない行で結合に参加し、`discoveryFacility` テーブルは最も多い行で結合に参加します。 ### 手順 4. インデックスを削除 ```sql ALTER TABLE planetarySystem DROP INDEX idx_ref_id; ALTER TABLE planetarySystem DROP INDEX idx_df_id; ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year; ALTER TABLE ParameterReference DROP INDEX idx_ref_name; ``` ```output Query OK, 0 rows affected (0.52 sec) ``` ### 手順 5. 次のクエリの実行計画を確認する ```sql EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ```output tidb>EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); +------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ | HashJoin_26 | 45.97 | root | | inner join, equal:[eq(universe.parameterreference.id, universe.planetarysystem.ref_id)] | | ├─TableReader_31(Build) | 2.72 | root | | data:Selection_30 | | │ └─Selection_30 | 2.72 | cop[tikv] | | like(universe.parameterreference.ref_name, "Rosenthal%", 92) | | │ └─TableFullScan_29 | 2055.00 | cop[tikv] | table:parameterReference | keep order:false | | └─TableReader_28(Probe) | 34713.00 | root | | data:TableFullScan_27 | | └─TableFullScan_27 | 34713.00 | cop[tikv] | table:planetarySystem | keep order:false | +------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) ``` + 結合における結合タイプは何ですか？ + 答え: `HashJoin` + 結合におけるテーブルの選択タイプは何ですか？ + 答え: `TableFullScan`, `Selection`, `TableReader` が `parameterReference` テーブルに; `TableFullScan`, `TableReader` が `planetarySystem` テーブルに使用されます。 + このアクセス戦略は良いパフォーマンスをもたらしますか？ なぜですか？ + 答え: いいえ。まず、WHERE 句でフィルタリングするために、`parameterReference` テーブルのインデックスを作成できます。次に、`planetarySystem` テーブルの結合カラムに対してもインデックスを作成することができます。 ### 手順 6. インデックスを追加し、次のクエリの実行計画を確認する ```sql CREATE INDEX idx_ref_id ON planetarySystem(ref_id); CREATE INDEX idx_ref_name ON ParameterReference(ref_name); ANALYZE TABLE planetarySystem; ANALYZE TABLE ParameterReference; ``` ```output Query OK, 0 rows affected, 1 warning (0.83 sec) ``` ```sql EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ```output +----------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | IndexHashJoin_20 | 45.97 | root | | inner join, inner:IndexLookUp_17, outer key:universe.parameterreference.id, inner key:universe.planetarysystem.ref_id, equal cond:eq(universe.parameterreference.id, universe.planetarysystem.ref_id) | | ├─IndexReader_46(Build) | 2.72 | root | | index:IndexRangeScan_45 | | │ └─IndexRangeScan_45 | 2.72 | cop[tikv] | table:parameterReference, index:idx_ref_name(ref_name) | range:["Rosenthal","Rosentham"), keep order:false | | └─IndexLookUp_17(Probe) | 45.97 | root | | | | ├─IndexRangeScan_15(Build) | 45.97 | cop[tikv] | table:planetarySystem, index:idx_ref_id(ref_id) | range: decided by [eq(universe.planetarysystem.ref_id, universe.parameterreference.id)], keep order:false | | └─TableRowIDScan_16(Probe) | 45.97 | cop[tikv] | table:planetarySystem | keep order:false | +----------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) ``` + 結合における結合タイプは何ですか？ + 答え: `IndexHashJoin` + 結合におけるテーブルの選択タイプは何ですか？ + 答え: `IndexRangeScan` と `IndexReader` は `parameterReference` テーブルに; `IndexRangeScan` と `IndexLookUp` は `planetarySystem` テーブルに使用されます。 + このアクセス戦略は良いパフォーマンスをもたらしますか？ なぜですか？ + 答え: はい。`idx_ref_id` のインデックスによって `planetarySystem` テーブルの結合カラムにインデックスが存在することが保証され、`HashJoin` は `IndexHashJoin` に変わり、これにより完全なテーブルスキャンを避けることができます。同時に `ParameterReference` テーブルに `idx_ref_name` インデックスが存在することを保証することにより、フィルタリングが最初に行われ、完全なテーブルスキャンを避けることができます。 ### 手順 7. インデックスを削除 ```sql DROP INDEX idx_ref_id ON planetarysystem; DROP INDEX idx_ref_name ON parameterreference; ``` ```output Query OK, 0 rows affected (0.52 sec) ```