## 模块 4：含 JOIN 的索引策略 + 在本模块中，我们将优化以下两个查询： ```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) ``` + 哪两个表首先进行连接？使用的是哪种表 JOIN 类型？ + 答案：表 `planetarySystem`(A) 和 表 `parameterReference`(C) 首先进行 JOIN，类型是 `IndexJoin`。 + 哪个表仅参与第二次表连接？使用的是哪种表 JOIN 类型？ + 答案：表 `discoveryFacility`(B)，JOIN 类型是 `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) ``` + 哪两个表首先进行连接？使用的是哪种表 JOIN 类型？ + 答案：表 `planetarySystem` (A) 和表 `parameterReference` (C) 首先进行 JOIN，类型是 `HashJoin`。 + 哪个表仅参与第二次表连接？使用的是哪种表 JOIN 类型？ + 答案：表 `discoveryFacility` (B)，JOIN 类型是 `IndexJoin`。 + 每个表查询的访问方法是什么？ + 答案：表 `planetarySystem` (A) 的访问方法是 `IndexRangeScan` 和 `IndexLookUp`；表 `parameterReference` (C) 的访问方法是 `IndexRangeScan` 和 `IndexReader`；表 `discoveryFacility` (B) 的访问方法是 `TableRangeScan` 和 `TableReader`。 + 这种访问策略会带来良好的性能吗？为什么？ + 答案：会。这是因为通过使用索引避免了表连接之前的 `TableFullScan` 或其他诸如 SELECT 的过滤操作。 ### 步骤 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) ``` + 三个表的 JOIN 序改变了吗？为什么？ + 答案：没有。因为优化器通常根据每个表涉及的数据量来确定 JOIN 顺序，数据量最小的表首先进行 JOIN。它根据查询中的过滤条件确定每个表参与 JOIN 的行数，如下所示： ```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` 表参与 JOIN 的行数最少，而 `discoveryFacility` 表参与 JOIN 的行数最多。 ### 步骤 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) ``` + JOIN 中的 JOIN 类型是什么？ + 答案：`HashJoin` + JOIN 中表的 select 类型是什么？ + Answer: `parameterReference` 表的 select 类型是 `TableFullScan`、`Selection` 和 `TableReader`；`planetarySystem` 表的 select 类型是 `TableFullScan` 和 `TableReader`。 + 这种访问策略会带来良好的性能吗？为什么？ + 答案：不会。首先，对于使用 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) ``` + 连接中的 JOIN 类型是什么？ + 答案：`IndexHashJoin` + 连接中表的 select 类型是什么？ + 答案：`parameterReference` 表的 select 类型是 `IndexRangeScan` 和 `IndexReader`；`planetarySystem` 表的 select 类型是 `IndexRangeScan` 和 `IndexLookUp`。 + 这种访问策略会带来良好的性能吗？为什么？ + 答案：会。索引 `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) ```