# 实验 3：基于成本的优化器 ## 概述 + 在本实验中，你将通过使用 EXPLAIN 和 EXPLAIN ANALYZE 命令，研究 TiDB 优化器针对几种不同 SELECT 查询的执行计划。 ### 实验时长 + 本实验大约需 60 分钟完成。 > [!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：使用 EXPLAIN 命令 + 在本练习中，你将通过使用 EXPLAIN 命令，研究 TiDB 优化器针对几种不同 SELECT 查询的执行计划。 ### 步骤 1. 执行以下语句查看 EXPLAIN 输出 ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id=100; +-------------+---------+------+--------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+--------------------------+---------------+ | Point_Get_1 | 1.00 | root | table:parameterReference | handle:100 | +-------------+---------+------+--------------------------+---------------+ 1 row in set (0.00 sec) ``` + 此查询的访问方式是什么？ + 答案：`Point_Get` + TiDB 执行此查询必须读取多少行数据？ + 答案：1 + 这种访问策略会带来良好的性能吗？为什么？ + 答案：会。当你将 `PRIMARY KEY（主键）` 或 `UNIQUE Index（唯一索引）` 的所有部分与常量值进行比较时，会应用 `Point_Get` 类型。在这个例子中，表最多有一行匹配的数据。优化器将这一行的列值视为常量，并且只读取一次。在 TiDB 中，`Point_Get` 不需要编译器生成执行计划。由于数据以 key-value 格式存储在 TiKV 中，当 `WHERE` 条件是主键或唯一索引时，我们可以直接使用 `Point_Get` 检索数据。 ### 步骤 2. 执行以下语句查看 EXPLAIN 输出 ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100+5; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id=100+5; +-------------+---------+------+--------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+--------------------------+---------------+ | Point_Get_5 | 1.00 | root | table:parameterReference | handle:105 | +-------------+---------+------+--------------------------+---------------+ 1 row in set (0.00 sec) ``` + 与步骤 1 中查看的查询相比，此查询的执行计划有什么不同吗？为什么？ + 答案：没有。优化器在执行查询之前会计算表达式 100 + 5。 ### 步骤 3. 执行以下语句查看 EXPLAIN 输出 ```sql EXPLAIN SELECT * FROM parameterReference WHERE id-5=100; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id-5=100; +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ | TableReader_7 | 1644.00 | root | | data:Selection_6 | | └─Selection_6 | 1644.00 | cop[tikv] | | eq(minus(universe.parameterreference.id, 5), 100) | | └─TableFullScan_5 | 2055.00 | cop[tikv] | table:parameterReference | keep order:false | +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ 3 rows in set (0.00 sec) ``` + 与步骤 1 和步骤 2 中查看的查询相比，此查询的执行计划有什么不同吗？为什么？ + 答案：有。优化器在执行查询之前无法将表达式 `ID-5=100` 简化为单个值。因此，它必须全表扫描（类型：TableFullScan）。行数是基于统计信息的估计值，可能与 `parameterReference` 表中的实际行数不相等。 ### 步骤 4. 以 JSON 格式显示查询的优化器成本 ```sql CREATE INDEX idx_host_name_ref ON planetarySystem(hostname, pl_name, ref_id); EXPLAIN FORMAT = "tidb_json" SELECT pl_name, sy_snum, sy_pnum FROM planetarySystem WHERE pl_name='11 Com b' AND hostname='11 Com b' AND ref_id=71\G DROP INDEX idx_host_name_ref on planetarySystem; ``` ```output *************************** 1. row *************************** TiDB_JSON: [ { "id": "Projection_4", "estRows": "1.00", "taskType": "root", "operatorInfo": "universe.planetarysystem.pl_name, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum", "subOperators": [ { "id": "IndexLookUp_10", "estRows": "1.00", "taskType": "root", "subOperators": [ { "id": "IndexRangeScan_8(Build)", "estRows": "1.00", "taskType": "cop[tikv]", "accessObject": "table:planetarySystem, index:idx_host_name_ref(hostname, pl_name, ref_id)", "operatorInfo": "range:[\"11 Com b\" \"11 Com b\" 71,\"11 Com b\" \"11 Com b\" 71], keep order:false" }, { "id": "TableRowIDScan_9(Probe)", "estRows": "1.00", "taskType": "cop[tikv]", "accessObject": "table:planetarySystem", "operatorInfo": "keep order:false" } ] } ] } ] 1 row in set (0.01 sec) ``` + 优化器为此查询计算的成本是多少？ + 答案：1.0（JSON 输出中 "estRows" 字段的值。成本信息 JSON 对象会更详细地展示成本细分。) + TiDB 必须访问哪些列来解析查询？ + 答案： `universe.planetarysystem.pl_name`、`universe.planetarysystem.sy_snum` 和 `universe.planetarysystem.sy_pnum` 列（JSON "operatorInfo" 数组字段中的元素） + 在 TiKV 上执行哪些操作？ + 答案：在 TiKV 上执行 `IndexRangeScan` 和 `TableRowIDScan` 操作（JSON 输出中 `taskType` 字段的值为 `cop[tikv]`）。 + 在 TiDB Server 上执行哪些操作？ + 答案：在 TiDB Server 上执行 `IndexLookUp` 操作（JSON 输出中 "taskType" 字段的值为 "root"）。 ## 模块 3：使用 EXPLAIN ANALYZE 命令 + 在本模块中，你将通过使用 EXPLAIN ANALYZE 命令，研究 TiDB 优化器针对几种不同 SELECT 查询的执行计划。 ### 步骤 1. 创建表 + 创建一个聚簇主键表 `T1` 和非聚簇主键表 `T2`。 ```sql DROP TABLE IF EXISTS `T1`; CREATE TABLE `T1` ( `id` bigint AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY (`name`) ); DROP TABLE IF EXISTS `T2`; CREATE TABLE `T2` ( `id` bigint AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`) NONCLUSTERED, KEY (`name`) ); ``` ```output Query OK, 0 rows affected (0.53 sec) ``` ### 步骤 2. 向表中插入数据 ```sql INSERT INTO T1 VALUES(NULL, 'Tom'); INSERT INTO T1 VALUES(NULL, 'Jack'); INSERT INTO T1 VALUES(NULL, 'Frank'); INSERT INTO T2 VALUES(NULL, 'Tom'); INSERT INTO T2 VALUES(NULL, 'Jack'); INSERT INTO T2 VALUES(NULL, 'Frank'); ``` ```output Query OK, 1 row affected (0.00 sec) ``` ### 步骤 3. 查看表 `T1` 的 `EXPLAIN ANALYZE` 输出 ```sql SELECT id, name from T1; EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=1\G ``` ```output +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | +----+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** id: Point_Get_1 estRows: 1.00 actRows: 1 task: root access object: table:T1 execution info: time:474.1µs, loops:2, RU:0.273086, Get:{num_rpc:1, total_time:443.5µs}, total_process_time: 67.5µs, total_wait_time: 56µs, tikv_wall_time: 149.5µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 39, total_keys: 1, get_snapshot_time: 18.2µs, rocksdb: {block: {cache_hit_count: 2}}} operator info: handle:1 memory: N/A disk: N/A 1 row in set (0.00 sec) ``` + 此查询的访问方式是什么？ + 答案：Point_Get + 此查询的实际执行时间是多少？ + 答案：time:474.1µs + 操作符（访问方式）执行了多少次？ + 答案：loops:2 次 ### 步骤 4. 查看表 `T1` 的 `EXPLAIN ANALYZE` 输出 ```sql SELECT id, name from T1; EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=4\G ``` ```output +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | +----+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** id: Point_Get_1 estRows: 1.00 actRows: 0 task: root access object: table:T1 execution info: time:473.8µs, loops:1, RU:0.272975, Get:{num_rpc:1, total_time:446.6µs}, total_process_time: 68.9µs, total_wait_time: 51.7µs, tikv_wall_time: 151.9µs, scan_detail: {total_keys: 1, get_snapshot_time: 17.3µs, rocksdb: {block: {cache_hit_count: 2}}} operator info: handle:4 memory: N/A disk: N/A 1 row in set (0.00 sec) ``` + 与上一步的查询相比，此查询的执行计划有什么不同吗？为什么？ + 答案：此查询的实际执行时间为 473.8µs，操作符（访问方法）只执行了一次。这是因为一旦我们确认聚簇主键表中没有 `id=4` 的行，就知道所需的数据不在表中。然而，在上一步中，即使我们找到了 `id=3`的行，由于主键是 `id`，我们仍然需要再次确认是否有其他符合条件的行。 ### 步骤 5. 查看表 `T2` 的 `EXPLAIN ANALYZE` 输出 ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE id=1\G ``` ```output +-------------+----+-------+ | _tidb_rowid | id | name | +-------------+----+-------+ | 2 | 1 | Tom | | 4 | 3 | Jack | | 6 | 5 | Frank | +-------------+----+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** id: Point_Get_1 estRows: 1.00 actRows: 1 task: root access object: table:T2, index:PRIMARY(id) execution info: time:592.9µs, loops:2, RU:0.520601, Get:{num_rpc:2, total_time:565.6µs}, total_process_time: 57.8µs, total_wait_time: 53.1µs, tikv_wall_time: 147.1µs, scan_detail: {total_process_keys: 2, total_process_keys_size: 87, total_keys: 2, get_snapshot_time: 12.6µs, rocksdb: {block: {cache_hit_count: 8}}} operator info: memory: N/A disk: N/A 1 row in set (0.00 sec) ``` + 与步骤 3 和步骤 4 中查看的查询相比，此查询的执行计划有什么不同吗？为什么？ + 答案：从 `Get:{num_rpc:2, total_time:565.6µs}` 字段，我们发现发送到 TiKV 的 Get RPC 请求数（num_rpc）为 2。这是因为 `T2` 是一个非聚簇主键表，我们必须先使用 `Point_Get` 从主键 `id` 获取 `_tidb_rowid` ，然后再使用 `_tidb_rowid` 从表中获取所需的行。因此，需要两个 RPC 请求。 ### 步骤 6. 查看表 `T2` 的 `EXPLAIN ANALYZE` 输出 ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE _tidb_rowid=2\G ``` ```output +-------------+----+-------+ | _tidb_rowid | id | name | +-------------+----+-------+ | 2 | 1 | Tom | | 4 | 3 | Jack | | 6 | 5 | Frank | +-------------+----+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** id: Point_Get_1 estRows: 1.00 actRows: 1 task: root access object: table:T2 execution info: time:518.2µs, loops:2, RU:0.274932, Get:{num_rpc:1, total_time:487.5µs}, total_process_time: 72.8µs, total_wait_time: 53.5µs, tikv_wall_time: 158.9µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 43, total_keys: 1, get_snapshot_time: 16.6µs, rocksdb: {block: {cache_hit_count: 4}}} operator info: handle:2 memory: N/A disk: N/A 1 row in set (0.00 sec) ``` + 与步骤 5 中查看的查询相比，此查询的执行计划有什么不同吗？为什么？ + 答案：从 `Get:{num_rpc:1, total_time:487.5µs}` 字段，我们可以得出发送到 TiKV 的 Get RPC 请求数（num_rpc）为 1。尽管 T2 表是非聚簇主键表，但如果我们直接使用 `_tidb_rowid` 作为查询条件，只需要一个 RPC 请求。 ### 步骤 7. 清理表 `T1` 和 `T2` ```sql DROP TABLE T1; DROP TABLE T2; ``` ```output Query OK, 0 rows affected (0.53 sec) ``` ## 实验结束