# Optimizer ### Overview + In this practice, you examine the TiDB optimizer's execution plan for several different SELECT queries by using the EXPLAIN and EXPLAIN ANALYZE command. ### Notes + The following experiments are designed to run on TiDB v7.5. Please install and deploy the TiDB v7.5 version database before the exercises. If you use an earlier version of TiDB database, the results may not be consistent with the guide. + Many of the steps require that you type commands in a terminal window. You might find it convenient to launch a terminal window and keep it open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The `$` in your terminal window is an OS prompt. + The "`tidb>`" or "`tidb>`" in your terminal window is SQL command prompt. + In these exercises, we need 10 machines connected in the network. The `Role` and `IP Address` (represented by environment variables) are as follows: | `Node Role` | `IP Address` | | :----------------- | :-------------------------------------------------------- | | `Monitor 1` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Control Machine` | `${HOST_CM_PRIVATE_IP}, ${HOST_CM_PUBLIC_IP}` | | `TiDB server 1` | `${HOST_DB1_PRIVATE_IP}, ${HOST_DB1_PUBLIC_IP}` | | `TiDB server 2` | `${HOST_DB2_PRIVATE_IP}, ${HOST_DB2_PUBLIC_IP}` | | `TiKV server 1` | `${HOST_KV1_PRIVATE_IP}, ${HOST_KV1_PUBLIC_IP}` | | `TiKV server 2` | `${HOST_KV2_PRIVATE_IP}, ${HOST_KV2_PUBLIC_IP}` | | `TiKV server 3` | `${HOST_KV3_PRIVATE_IP}, ${HOST_KV3_PUBLIC_IP}` | | `PD server 1` | `${HOST_PD1_PRIVATE_IP}, ${HOST_PD1_PUBLIC_IP}` | | `PD server 2` | `${HOST_PD2_PRIVATE_IP}, ${HOST_PD2_PUBLIC_IP}` | | `PD server 3` | `${HOST_PD3_PRIVATE_IP}, ${HOST_PD3_PUBLIC_IP}` | | `TiFlash server 1` | `${HOST_TIFLASH1_PRIVATE_IP}, ${HOST_TIFLASH1_PUBLIC_IP}` | | `Alertmanager` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Grafana` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Prometheus` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | + Exercise 1 requires the experimental data imported in Exercise 0, so please complete Exercise 0 before starting Exercise 1. ### Assumptions + You are given the user credentials (for example: the private key) of the exercise environment. + You are given access to 10 machines that have been set up. + Your environment has TiDB cluster deployed and running. ## Exercise 0: Creating the universe Database in the TiDB Cluster + In this exercise, you will import more experimental data into TiDB database for the subsequent experiments. We will create a database called universe. ### Duration + This exercise should take you approximately 5 minutes to complete. ### Tasks 1. Copy the `universeV2.sql` script and data files `discoveryFacility.csv`, `parameterReference.csv`, and `planetarySystem.csv`, to the experimental environment. 2. Open a Linux terminal window and start an interactive TiDB session. 3. Execute the script `universeV2.sql`. 4. Make `universe` the current database. 5. Check the imported data. 6. Leave the terminal window with the TiDB session open for the next practice. ### Solution 1. Copy the `universeV2.sql` script and data files `discoveryFacility.csv`, `parameterReference.csv`, and `planetarySystem.csv`, to the experimental environment. ``` $ ls discoveryFacility.csv parameterReference.csv planetarySystem.csv universeV2.sql ``` 2. Login as user `ec2-user` on `Control Machine` (`${HOST_MONITOR1_PUBLIC_IP}`). Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> prompt tidb> PROMPT set to 'tidb>' ``` 3. Execute the script `universeV2.sql`. Enter the following command at the terminal prompt and receive the results shown: ``` tidb> source universeV2.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.54 sec) Database changed Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected, 1 warning (0.53 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.53 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected, 1 warning (0.58 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected, 1 warning (0.52 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) Query OK, 0 rows affected (0.53 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.52 sec) Query OK, 0 rows affected, 1 warning (0.53 sec) Query OK, 1 row affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 10 rows affected, 13 warnings (0.02 sec) Records: 10 Duplicates: 0 Warnings: 13 Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 70 rows affected (0.05 sec) Records: 70 Deleted: 0 Skipped: 0 Warnings: 0 Query OK, 2055 rows affected (0.07 sec) Records: 2055 Deleted: 0 Skipped: 0 Warnings: 0 Query OK, 34713 rows affected (0.47 sec) Records: 34713 Deleted: 0 Skipped: 0 Warnings: 0 Query OK, 0 rows affected, 1 warning (0.23 sec) Query OK, 0 rows affected, 1 warning (0.51 sec) Query OK, 0 rows affected, 1 warning (2.03 sec) ``` 4. Make `universe` the current database. Enter the following statement at the TiDB prompt and receive the results shown: ```sql use universe; ``` ``` tidb>use universe; Database changed ``` 5. Check the imported data. Enter the following command at the terminal prompt and receive the results shown: ``` 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 starts; ERROR 1146 (42S02): Table 'universe.starts' doesn't exist 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) ``` 6. Leave the terminal window with the TiDB session open for the next practice. ## Exercise 1: Understanding the Query Execution Plan with the EXPLAIN Statement + In this exercise, you will you examine the TiDB optimizer's execution plan for several different SELECT queries by using the EXPLAIN command. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. Open a Linux terminal window and start an interactive TiDB session. 2. Make `universe` the current database. 3. Execute the following statement to view the EXPLAIN output for a query against the `parameterReference` table: ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100; ``` + What is the access method for this query? + How many rows must TiDB read to perform this query? + Does this access strategy result in good performance? Why? 4. Execute the following statement to view the EXPLAIN output for a query against the `parameterReference` table that uses an expression to determine the value of the ID field to search for: ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100 + 5; ``` Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 3? Why? 5. Execute the following statement that uses a different expression to determine the value of the `id` field to search for: ```sql EXPLAIN SELECT * FROM parameterReference WHERE id - 5 =100; ``` Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 4? Why? 6. Display the optimizer cost for the query in the preceding step by viewing the EXPLAIN information that is available in JSON format. + What cost did the optimizer calculate for this query? + Which columns must TiDB access to resolve the query? + Which operations are carried out on TiKV? + Which operations are carried out on the TiDB Server? 7. Leave the terminal window with the TiDB session open for the next practice. ### Solution 1. Login as user `ec2-user` on `Control Machine` (`${HOST_MONITOR1_PUBLIC_IP}`). Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> prompt tidb> PROMPT set to 'tidb>' ``` 2. Make `universe` the current database. Enter the following statement at the TiDB prompt and receive the results shown: ```sql use universe; ``` ``` tidb>use universe; Database changed ``` 3. Execute the following statement to view the EXPLAIN output for a query against the `parameterReference` table, Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100; ``` ``` 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 Point_Get type tells you that the table has at the most one matching row, which TiDB reads at the start of the query. Because there is only one row, the optimizer treats this row's column values as constants and reads them only once. In TiDB database, even 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. 4. Execute the following statement to view the EXPLAIN output for a query against the `parameterReference` table that uses an expression to determine the value of the `id` field to search for, Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100+5; ``` ``` 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 3? Why? Answer: No. The optimizer evaluates the expression 100+5 before executing the query. 5. Execute the following statement that uses a different expression to determine the value of the `id` field to search for, Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM parameterReference WHERE id-5=100; ``` ``` 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 4? Why? Answer: Yes. The optimizer cannot reduce the expression ID-5=100 to a single value before executing the query and therefore must scan the full table (type: TableFullScan). Note that the number of rows is an estimate based on statistics and does not correspond to the exact number of rows in the `parameterReference` table (2,055). 6. Display the optimizer cost for the query in the preceding step by viewing the EXPLAIN information that is available in JSON format. Enter the following statement at the TiDB prompt and receive the results shown: ```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; ``` ``` tidb>CREATE INDEX idx_host_name_ref ON planetarySystem(hostname, pl_name, ref_id); Query OK, 0 rows affected (6.03 sec) tidb>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; *************************** 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) tidb>DROP INDEX idx_host_name_ref ON planetarySystem; Query OK, 0 rows affected (0.52 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"). 7. Leave the terminal window with the TiDB session open for the next exercise. ## Exercise 2: Understanding the Query Execution Plan with the EXPLAIN ANALYZE Statement ### Overview + In this exercise, you will you examine the TiDB optimizer's execution plan for several different SELECT queries by using the EXPLAIN ANALYZE command. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. Open a Linux terminal window and start an interactive TiDB session. 2. Make `test` the current database. 3. 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`) ); ``` 4. Insert 3 rows into each of the table `T1` and `T2`: ```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'); ``` 5. Execute the following statement to view the EXPLAIN ANALYZE output for a query against the `T1` table that uses an expression to determine the value of the `id` field to search for: ```sql SELECT id, name from T1; EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=1\G; ``` + What is the access method for this query? + What is the actual execution time of this query？ + sHow many times the operator(access method) was executed? 6. Execute the following statement to view the EXPLAIN ANALYZE output for a query against the `id` table that uses an expression to determine the value of the ID field to search for: ```sql SELECT id, name from T1; EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=4\G; ``` Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 5? Why? 7. Execute the following statement to view the EXPLAIN ANALYZE output for a query against the `T2` table that uses an expression to determine the value of the `id` field to search for: ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE id=1\G; ``` Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 5? Why? 8. Execute the following statement to view the EXPLAIN ANALYZE output for a query against the `T2` table that uses an expression to determine the value of the `id` field to search for: ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE _tidb_rowid=2\G; ``` Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 7? Why? 9. Clean up the table `T1` and `T2`. 10. Leave the terminal window with the TiDB session open for the next practice. ### Solution 1. Login as user `ec2-user` on `Control Machine` (`${HOST_MONITOR1_PUBLIC_IP}`). Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> prompt tidb> PROMPT set to 'tidb>' ``` 2. Make `test` the current database. Enter the following statement at the TiDB prompt and receive the results shown: ```sql USE test; ``` ``` tidb>USE test; Database changed ``` 3. Create a clustered primary key table `T1` and non-clustered primary key table `T2`, enter the following statement at the TiDB prompt and receive the results shown: ```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`) ); ``` ``` tidb>DROP TABLE IF EXISTS `T1`; Query OK, 0 rows affected (0.52 sec) tidb>CREATE TABLE `T1` ( -> `id` bigint AUTO_INCREMENT, -> `name` varchar(20) NOT NULL DEFAULT '', -> PRIMARY KEY (`id`), -> KEY (`name`) -> ); Query OK, 0 rows affected (0.53 sec) tidb>DROP TABLE IF EXISTS `T2`; Query OK, 0 rows affected (0.52 sec) tidb>CREATE TABLE `T2` ( -> `id` bigint AUTO_INCREMENT, -> `name` varchar(20) NOT NULL DEFAULT '', -> PRIMARY KEY (`id`) NONCLUSTERED, -> KEY (`name`) -> ); Query OK, 0 rows affected (0.53 sec) ``` 4. Insert 3 rows into each of the table `T1` and `T2`, enter the following statement at the TiDB prompt and receive the results shown: ```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'); ``` ``` tidb>INSERT INTO T1 VALUES(NULL, 'Tom'); Query OK, 1 row affected (0.02 sec) tidb>INSERT INTO T1 VALUES(NULL, 'Jack'); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO T1 VALUES(NULL, 'Frank'); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO T2 VALUES(NULL, 'Tom'); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO T2 VALUES(NULL, 'Jack'); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO T2 VALUES(NULL, 'Frank'); Query OK, 1 row affected (0.00 sec) ``` 5. Execute the following statement to view the EXPLAIN ANALYZE output for a query against the `T1` table that uses an expression to determine the value of the `id` field to search for, enter the following statement at the TiDB prompt and receive the results shown: ```sql SELECT id, name from T1; EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=1; ``` ``` tidb>SELECT id, name from T1; +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | +----+-------+ 3 rows in set (0.00 sec) tidb>EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=1\G; *************************** 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 6. Execute the following statement to view the EXPLAIN ANALYZE output for a query against the `id` table that uses an expression to determine the value of the ID field to search for, enter the following statement at the TiDB prompt and receive the results shown: ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE id=4\G; ``` ``` tidb>SELECT id, name from T1; +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | +----+-------+ 3 rows in set (0.00 sec) tidb>EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=4\G; *************************** 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 you looked at in step 5? 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 can know that the data we need is not stored in the table. However, in Step 5, 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`. 7. Execute the following statement to view the EXPLAIN ANALYZE output for a query against the `T2` table that uses an expression to determine the value of the `id` field to search for, enter the following statement at the TiDB prompt and receive the results shown: ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE id=1\G; ``` ``` tidb>SELECT _tidb_rowid, id, name from T2; +-------------+----+-------+ | _tidb_rowid | id | name | +-------------+----+-------+ | 2 | 1 | Tom | | 4 | 3 | Jack | | 6 | 5 | Frank | +-------------+----+-------+ 3 rows in set (0.00 sec) tidb>EXPLAIN ANALYZE SELECT * FROM T2 WHERE id=1\G; *************************** 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 5? Why? Answer: From the `Get:{num_rpc:2, total_time:565.6µs}` field, we found 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. 8. Execute the following statement to view the EXPLAIN ANALYZE output for a query against the `T2` table that uses an expression to determine the value of the `id` field to search for, enter the following statement at the TiDB prompt and receive the results shown: ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE _tidb_rowid=2\G; ``` ``` tidb>SELECT _tidb_rowid, id, name from T2; +-------------+----+-------+ | _tidb_rowid | id | name | +-------------+----+-------+ | 2 | 1 | Tom | | 4 | 3 | Jack | | 6 | 5 | Frank | +-------------+----+-------+ 3 rows in set (0.00 sec) tidb>EXPLAIN ANALYZE SELECT * FROM T2 WHERE _tidb_rowid=2\G; *************************** 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 7? Why? Answer:From the `Get:{num_rpc:1, total_time:487.5µs}` field, we found 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. 9. Clean up the table `T1` and `T2`. ```sql DROP TABLE T1; DROP TABLE T2; ``` ``` tidb>DROP TABLE T1; Query OK, 0 rows affected (0.52 sec) tidb>DROP TABLE T2; Query OK, 0 rows affected (0.53 sec) ``` 10. Leave the terminal window with the TiDB session open for the next practice. ## End of Exercises