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