## 模块 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) ```