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