# 实验 4：查询优化 ## 概述 + 在这些练习中，你将确定优化器针对特定查询的策略。为查询所访问的表添加索引，并评估其对查询执行计划的影响，从而提升特定查询的性能。 > [!TIP] > 实验室环境部署完成后，点击底部栏上的 **前进** 进入 **模块 1**。 ## 模块 1：数据准备 + 在本练习中，你将向 TiDB 数据库导入更多实验数据，以供后续实验使用。我们将创建一个名为 universe 的数据库。 ### 步骤 1：打开终端，SSH 登录到 TiUP 控制机 + 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 + 登录后显示的横幅如下所示。 ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### 步骤 2. 连接到 TiDB 并进入 `test` 数据库 ```sql cd universe/ ``` ```sql mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```output Database changed ``` ### 步骤 3. 导入数据 ```sql source universeV2.sql ``` ```output Query OK, 0 rows affected, 1 warning (2.03 sec) ``` ### 步骤 4. 将 `universe` 设为当前数据库 ```sql use universe; ``` ```output Database changed ``` ### 步骤 5. 检查导入的数据 ```sql SHOW TABLES; SELECT COUNT(*) FROM stars; SELECT COUNT(*) FROM planets; SELECT COUNT(*) FROM moons; SELECT COUNT(*) FROM planet_categories; SELECT COUNT(*) FROM discoveryFacility; SELECT COUNT(*) FROM parameterReference; SELECT COUNT(*) FROM planetarySystem; ``` ```output tidb>show tables; +--------------------+ | Tables_in_universe | +--------------------+ | discoveryFacility | | moons | | parameterReference | | planet_categories | | planetarySystem | | planets | | stars | +--------------------+ 7 rows in set (0.00 sec) tidb>select count(*) from stars; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planets; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from moons; +----------+ | count(*) | +----------+ | 28 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planet_categories; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from discoveryFacility; +----------+ | count(*) | +----------+ | 70 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from parameterReference; +----------+ | count(*) | +----------+ | 2055 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planetarySystem; +----------+ | count(*) | +----------+ | 34713 | +----------+ 1 row in set (0.02 sec) ``` ## 模块 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 in set (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 in set (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 输出的第三行 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 in set (0.00 sec) ``` + 此查询的访问方法是什么？ + 答案：`IndexLookUp` 和 `IndexRangeScan`。 + 优化器为此查询计算的成本是多少？ + 答案：1.00。 + TiDB 必须访问哪些列来解析查询？ + 答案：`pl_name`、`hostname`、`sy_snum`、`sy_pnum` 和 `ref_id` 列。 + 索引 `idx_pl_name_hostname_ref` 对提升此查询的性能有帮助吗？为什么？ + 答案：有帮助。索引 `index idx_pl_name_hostname_ref` 中的 `pl_name`、`hostname` 和 `ref_id` 列都出现在查询中。 ### 步骤 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 in set (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` 列都出现在查询中，但我们仍然无法使用索引中的 `ref_id` 列，因为 `hostname`列上的条件不是等值查询，而是范围查询。从 EXPLAIN 输出的第三行 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 in set (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 in set (0.00 sec) ``` + 此查询的访问方法是什么？ + 答案：`IndexMerge`。 + 这种访问策略会带来良好的性能吗？为什么？ + 答案：可能会。索引合并访问策略表明 TiDB 找到了多个可用于该查询的索引。在这种情况下，它使用了主键和 `ref_id` i索引。索引合并方法通过多次范围扫描检索行并合并结果。如果表上存在高选择性索引，性能就会良好。使用 union entry 的 `operator info` 展示了 TiDB 如何为索引合并连接类型合并索引扫描。 ```sql DROP INDEX idx_ref_id ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.36 sec) ``` ## 模块 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 Server 对键进行排序，然后按所需顺序检索行。 + 此查询性能好吗？为什么？ + 答案：不好。此查询需要全表扫描（类型：TableFullScan），无法使用任何键，并且需要对 `disc_year` 列进行排序。排序需要将所有数据加载到一个 TiDB Server 中才能完成任务。 + 要防止此查询进行全表扫描，必须对表进行什么更改？ + 答案：必须在 `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`。 + 上一步中实施的更改是否提升了查询的性能？为什么？ + 答案：提升了。现在该查询无需进行全表扫描。相反，它使用 `IndexRangeScan` 和 `IndexLookUp` 从 `hostname` 列的索引中检索行。此外，`task: cop[tikv]` 列表明所有索引过滤操作都在 TiKV 中并行完成。 + 要防止此查询进行排序，必须怎么做？ + 答案：在 `planetarySystem` 表上添加一个包含 `hostname` 和 `disc_year` c列的复合索引。在这种情况下，不再需要 `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`。 + 此更改是否提升了查询的性能？为什么？ + Answer: 提升了。在 `hostname` 和 `disc_year` 列上添加复合键消除了在 TiDB Server 中进行排序的需求。 + 要防止此查询在找到索引行后进行主键查找，必须怎么做？ + 答案：在 `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` + 此更改是否提升了查询的性能？为什么？ + Answer: 答案：提升了。该查询现在从索引中检索所需的所有数据，而无需访问基表 (`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) ``` ## 模块 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) ``` ## 实验结束