--- title: "Cost Based Optimizer" version: "v2" status: "GA" description: "In this hands-on lab, you will examine the TiDB optimizer's execution plan for several different SELECT queries by using the EXPLAIN and EXPLAIN ANALYZE command." --- # Lab 3: Cost Based Optimizer ## Overview >[!NOTE] > This hands-on lab is also included in the [TiDB Certification Training: TiDB SQL Tuning](https://www.pingcap.com/education/course-tidb-sql-tuning/) course. In this lab, you will examine the TiDB optimizer's execution plan for several different SELECT queries by using the EXPLAIN and EXPLAIN ANALYZE command. ### Duration + This lab should take you approximately 60 minutes to complete. > [!TIP] > Once the lab environment is deployed, click **Next** on the bottom bar to begin **Module 1**. ## Module 1: Data Preparation + In this exercise, you will import more experimental data into TiDB database for the subsequent experiments. We will create a database called universe. ### Step 1. Log in to the remote Linux + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner displayed after logging in appears as follows. ```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/ ``` ### Step 2. Connect to TiDB and use `test` database ```sql cd universe/ ``` ```sql mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```output Database changed ``` ### Step 3. Import the data ```sql source universeV2.sql ``` ```output Query OK, 0 rows affected, 1 warning (2.03 sec) ``` ### Step 4. Make `universe` the current database ```sql use universe; ``` ```output Database changed ``` ### Step 5. Check the imported data ```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) ``` ## Module 2: Using EXPLAIN Command + In this exercise, you will you examine the TiDB optimizer's execution plan for several different SELECT queries by using the EXPLAIN command. ### Step 1. Execute the following statement to view the EXPLAIN output ```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) ``` + What is the access method for this query? + Answer: `Point_Get` + How many rows must TiDB read to perform this query? + Answer: 1 + Does this access strategy result in good performance? Why? + Answer: Yes. The `Point_Get` type applies when you compare all parts of a `PRIMARY KEY` or `UNIQUE Index` to constant values. In this example, the table has at the most one matching row. The optimizer treats this row's column values as constants and reads them only once. In TiDB, `Point_Get` does not require compiler to generate an execution plan. As data is stored in TiKV in key-value format, we can directly retrieve data using `Point_Get` when the `WHERE` condition is a primary key or unique index. ### Step 2. Execute the following statement to view the EXPLAIN output ```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) ``` + Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 1? Why? + Answer: No. The optimizer evaluates the expression 100+5 before executing the query. ### Step 3. Execute the following statement to view the EXPLAIN output ```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) ``` + Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 1 and 2? Why? + Answer: Yes. The optimizer cannot reduce the expression `ID-5=100` to a single value before executing the query. Thus, it must scan the full table (type: TableFullScan). The number of rows is an estimate based on statistics and may not equal to the exact number of rows in the `parameterReference` table. ### Step 4. Display the optimizer cost for the query in JSON format ```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) ``` + What cost did the optimizer calculate for this query? + Answer: 1.0 (the value of the "estRows" field in the JSON output. The cost info JSON object displays a more detailed breakdown of the cost.) + Which columns must TiDB access to resolve the query? + Answer: The `universe.planetarysystem.pl_name`, `universe.planetarysystem.sy_snum`, `universe.planetarysystem.sy_pnum` columns (the elements in the JSON "operatorInfo" array field) + Which operations are carried out on TiKV? + Answer: The `IndexRangeScan` and `TableRowIDScan` are carried out on TiKV (the value of the `taskType` field in the JSON output is `cop[tikv]`). + Which operations are carried out on the TiDB Server? + Answer: The `IndexLookUp` is carried out on TiDB Server (the value of the "taskType" field in the JSON output is "root"). ## Module 3: Using EXPLAIN ANALYZE Command + In this exercise, you will you examine the TiDB optimizer's execution plan for several different SELECT queries by using the EXPLAIN ANALYZE command. ### Step 1. Create tables + Create a clustered primary key table `T1` and non-clustered primary key table `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) ``` ### Step 2. Insert data into the tables ```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) ``` ### Step 3. View the output of `EXPLAIN ANALYZE` for table `T1` ```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) ``` + What is the access method for this query? + Answer: Point_Get + What is the actual execution time of this query? + Answer: time:474.1µs + How many times the operator(access method) was executed? + Answer: loops:2 times ### Step 4. View the output of `EXPLAIN ANALYZE` for table `T1` ```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) ``` + Is there any difference in the execution plan for this query, when compared to the query that in the previously step? Why? + Answer: The actual execution time of this query is 473.8µs, and the times the operator(access method) was executed is just once. This is because once we confirm that there is no row with `id=4` in the clustered primary key table, we know that the data we need is not stored in the table. However, in the previous step, even after we find the row with `id=3`, we still need to confirm again whether there are any rows that meet the condition, even though the primary key is `id`. ### Step 5. View the output of `EXPLAIN ANALYZE` for table `T2` ```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) ``` + Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 3 and 4? Why? + Answer: From the `Get:{num_rpc:2, total_time:565.6µs}` field, we find that the number of the Get RPC requests (num_rpc) sent to TiKV is 2. This is because `T2` is a non-clustered primary key table, and we have to use `Point_Get` to obtain the `_tidb_rowid` from the primary key `id` first, and then use the `_tidb_rowid` to obtain the required row from the table. Therefore, there needs to be two RPC requests. ### Step 6. View the output of `EXPLAIN ANALYZE` for table `T2` ```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) ``` + Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 5? Why? Answer: From the `Get:{num_rpc:1, total_time:487.5µs}` field, we can conclude that the number of the Get RPC requests (num_rpc) sent to TiKV is 1. Although T2 table is a non-clustered primary key table, if we use `_tidb_rowid` directly as the query condition, only one RPC request is needed. ### Step 7. Clean up the table `T1` and `T2` ```sql DROP TABLE T1; DROP TABLE T2; ``` ```output Query OK, 0 rows affected (0.53 sec) ``` ## End of Exercises