# Statistics Management ### Overview + In this practice, you test how statistics affect execution plans and the performance issues caused by the untimely updating of statistics. ### 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}` | ### 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 1: Resolve performance problem caused by changes in execution plans. + In this exercise, you resolve the query performance problem caused by changes in execution plans. ### Duration + This exercise should take you approximately 45 minutes to complete. ### Tasks 1. Open a Linux terminal window and start an interactive TiDB session. 2. Make `test` the current database. 3. Check the parameters related to automatic collection of statistics, and ensure that `tidb_enable_auto_analyze`, `tidb_auto_analyze_ratio`, `tidb_auto_analyze_start_time`, and `tidb_auto_analyze_end_time` are set to the default values. 4. Execute the following statement to create table `T1` for your exercise: ```sql CREATE TABLE test.T1(id int(11) AUTO_INCREMENT PRIMARY KEY, month VARCHAR(32), cnt int(11), pname VARCHAR(32), pid VARCHAR(32), KEY idx_month(month)); ``` 5. Exit the TiDB Session to Linux terminal window and insert date into T1. ``` for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('1',21,'BeiJing','10')"; done; for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('2',22,'ShangHai','10')"; done; for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('3',23,'NewYork','10')"; done; for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('4',24,'Tokyo','10')"; done; for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('5',25,'Los Angeles','10')"; done; ``` 6. Start an interactive TiDB session again. 7. Make `test` the current database. 8. Collect the statistic of table `T1`. 9. Check the data distribution of table `T1`. ```sql SELECT COUNT(*) FROM T1; SELECT month, COUNT(*) FROM T1 GROUP BY month; ``` 10. Check the statistics information of table `T1`. + How is the information on the `month` column and the index 'idx_month' recorded in the statistics? 11. Make the values of the `pid` column in each row unique, create an index `idx_pid` on the `pid` column, and manually collect statistics of table `T1`. ```sql UPDATE test.T1 set pid=id; ALTER TABLE test.T1 ADD INDEX idx_pid(pid); ANALYZE TABLE test.T1; ``` 12. Check the statistics information of table `T1` again. + How is the information on the `month` and `pid` columns and the index `idx_month` and `idx_pid` recorded in the statistics? 13. Execute the following 5 statements to view the EXPLAIN output for a query against the `T1` table: ```sql EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; ``` + Which index does the query use? Why? 14. Execute the following statement to view the EXPLAIN output for a query against the `T1` table: ```sql EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; ``` + Which index does the query use? Why? 15. Exit the TiDB Session to Linux terminal window and insert date into T1. ``` for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('6',29,'Berlin','10001')"; done; ``` 16. Start an interactive TiDB session again. 17. Make `test` the current database. 18. Check the data distribution of table `T1`. ```sql SELECT COUNT(*) FROM T1; SELECT month, COUNT(*) FROM T1 GROUP BY month; ``` 19. Execute the following 6 statements to view the EXPLAIN output for a query against the `T1` table: ```sql EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; ``` + Is there a performance problem here? Why? 20. Check the statistics information of table `T1` again. + How is the information on the `month` and `pid` columns and the index `idx_month` and `idx_pid` recorded in the statistics? 21. Please answer the following questions: + Are the statistics expired? + Why would the statistics be expired? + How should you solve the performance issue in Step19? + Are the statistics expired? Answer: Yes, the statistics is the same as before(Step 12) inserting 2000 rows with value 6 of `month` column. Therefore, the optimizer assumes that there is no row in table `T1` with `month=6` and uses the `idx_month` index as before. + Why would the statistics be expired? Answer: This is because the system variable `tidb_auto_analyze_ratio` is 0.5, which means that the statistics is only automatically updated when the modified data reaches 50%. Obviously, updating 2000 rows of data does not reach 50%. You can check `STATS_META` to confirm. Enter the following command at the terminal prompt and receive the results shown: ```sql SHOW STATS_META WHERE table_name='T1'\G; ``` ``` tidb>SHOW STATS_META WHERE table_name='T1'\G; *************************** 1. row *************************** Db_name: test Table_name: T1 Partition_name: Update_time: 2023-12-04 15:44:29 Modify_count: 2000 Row_count: 12000 1 row in set (0.00 sec) ``` + How should you solve the performance issue in Step19? Answer: You need to manually collect statistics of table `T1` to solve the performance problem. 22. Collect the statistic of table `T1`. 23. Re-issue the EXPLAIN statement from step 19. Has the previous performance problem been resolved? ```sql EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; ``` 24. Check the statistics information of table `T1` again. 25. Clean up the table T1. 26. 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. Check the parameters related to automatic collection of statistics, and ensure that `tidb_enable_auto_analyze`, `tidb_auto_analyze_ratio`, `tidb_auto_analyze_start_time`, and `tidb_auto_analyze_end_time` are set to the default values. Enter the following statement at the TiDB prompt and receive the results shown: ```sql show variables like 'tidb_enable_auto_analyze'; show variables like 'tidb_auto_analyze_ratio'; show variables like 'tidb_auto_analyze_start_time'; show variables like 'tidb_auto_analyze_end_time'; ``` ``` tidb>show variables like 'tidb_enable_auto_analyze'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | tidb_enable_auto_analyze | ON | +--------------------------+-------+ 1 row in set (0.00 sec) tidb>show variables like 'tidb_auto_analyze_ratio'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | tidb_auto_analyze_ratio | 0.5 | +-------------------------+-------+ 1 row in set (0.00 sec) tidb>show variables like 'tidb_auto_analyze_start_time'; +------------------------------+-------------+ | Variable_name | Value | +------------------------------+-------------+ | tidb_auto_analyze_start_time | 00:00 +0000 | +------------------------------+-------------+ 1 row in set (0.00 sec) tidb>show variables like 'tidb_auto_analyze_end_time'; +----------------------------+-------------+ | Variable_name | Value | +----------------------------+-------------+ | tidb_auto_analyze_end_time | 23:59 +0000 | +----------------------------+-------------+ 1 row in set (0.00 sec) ``` 4. Execute the following statement to create table `T1` for your exercise. Enter the following statement at the TiDB prompt and receive the results shown: ```sql CREATE TABLE test.T1(id int(11) AUTO_INCREMENT PRIMARY KEY, month VARCHAR(32), cnt int(11), pname VARCHAR(32), pid VARCHAR(32), KEY idx_month(month)); ``` ``` tidb>CREATE TABLE test.T1(id int(11) AUTO_INCREMENT PRIMARY KEY, month VARCHAR(32), cnt int(11), pname VARCHAR(32), pid VARCHAR(32), KEY idx_month(month)); Query OK, 0 rows affected (0.53 sec) ``` 5. Exit the TiDB Session to Linux terminal window and insert date into T1. Enter the following statement at the TiDB prompt and receive the results shown: ``` for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('1',21,'BeiJing','10')"; done; for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('2',22,'ShangHai','10')"; done; for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('3',23,'NewYork','10')"; done; for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('4',24,'Tokyo','10')"; done; for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('5',25,'Los Angeles','10')"; done; ``` ``` $ for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('1',21,'BeiJing','10')"; done; mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ... mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. $ for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('2',22,'ShangHai','10')"; done; mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ... mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. $ for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('3',23,'NewYork','10')"; done; mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ... mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. $ for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('4',24,'Tokyo','10')"; done; mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ... mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. $ for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('5',25,'Los Angeles','10')"; done; mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ... mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ``` 6. Start an interactive TiDB session again. Enter the following statement at the TiDB prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> prompt tidb> PROMPT set to 'tidb>' ``` 7. 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 ``` 8. Collect the statistic of table `T1`. Enter the following statement at the TiDB prompt and receive the results shown: ```sql ANALYZE TABLE T1; ``` ``` tidb>ANALYZE TABLE T1; Query OK, 0 rows affected, 1 warning (0.29 sec) ``` 9. Check the data distribution of table `T1`. Enter the following statement at the TiDB prompt and receive the results shown: ```sql SELECT COUNT(*) FROM T1; SELECT month, COUNT(*) FROM T1 GROUP BY month; ``` ``` tidb>SELECT COUNT(*) FROM T1; +----------+ | COUNT(*) | +----------+ | 10000 | +----------+ 1 row in set (0.01 sec) tidb>SELECT month, COUNT(*) FROM T1 GROUP BY month; +-------+----------+ | month | COUNT(*) | +-------+----------+ | 3 | 2000 | | 1 | 2000 | | 2 | 2000 | | 4 | 2000 | | 5 | 2000 | +-------+----------+ 5 rows in set (0.01 sec) ``` 10. Check the statistics information of table `T1`. Enter the following statement at the TiDB prompt and receive the results shown: ```sql SHOW STATS_HISTOGRAMS WHERE Table_name='T1' AND Db_name='test' AND Column_name IN ('month', 'idx_month')\G; SHOW STATS_TOPN WHERE Db_name = 'test' AND Table_name = 'T1' AND Column_name = 'idx_month'; ``` ``` tidb>SHOW STATS_HISTOGRAMS WHERE Table_name='T1' AND Db_name='test' AND Column_name IN ('month', 'idx_month')\G; *************************** 1. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: month Is_index: 0 Update_time: 2023-12-04 14:36:23 Distinct_count: 5 Null_count: 0 Avg_col_size: 2 Correlation: 1 Load_status: allEvicted Total_mem_usage: 0 Hist_mem_usage: 0 Topn_mem_usage: 0 Cms_mem_usage: 0 *************************** 2. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: idx_month Is_index: 1 Update_time: 2023-12-04 14:36:23 Distinct_count: 5 Null_count: 0 Avg_col_size: 0 Correlation: 0 Load_status: allLoaded Total_mem_usage: 242 Hist_mem_usage: 0 Topn_mem_usage: 242 Cms_mem_usage: 0 2 rows in set (0.00 sec) ERROR: No query specified tidb>SHOW STATS_TOPN WHERE Db_name = 'test' AND Table_name = 'T1' AND Column_name = 'idx_month'; +---------+------------+----------------+-------------+----------+-------+-------+ | Db_name | Table_name | Partition_name | Column_name | Is_index | Value | Count | +---------+------------+----------------+-------------+----------+-------+-------+ | test | T1 | | idx_month | 1 | 1 | 2000 | | test | T1 | | idx_month | 1 | 2 | 2000 | | test | T1 | | idx_month | 1 | 3 | 2000 | | test | T1 | | idx_month | 1 | 4 | 2000 | | test | T1 | | idx_month | 1 | 5 | 2000 | +---------+------------+----------------+-------------+----------+-------+-------+ 5 rows in set (0.00 sec) ``` + How is the information on the `month` column and the index 'idx_month' recorded in the statistics? Answer: In `STATS_HISTOGRAMS`, you found there are 5 different values in the table `T1`. In `STATS_TOPN`, you found the 5 different values of `month` column is `1`,`2`,`3`,`4` and `5`, each of them has 2,000 rows. 11. Make the values of the `pid` column in each row unique, create an index `idx_pid` on the `pid` column, and manually collect statistics of table `T1`. Enter the following statement at the TiDB prompt and receive the results shown: ```sql UPDATE test.T1 set pid=id; ALTER TABLE test.T1 ADD INDEX idx_pid(pid); ANALYZE TABLE test.T1; ``` ``` tidb>UPDATE test.T1 set pid=id; Query OK, 9999 rows affected (0.12 sec) Rows matched: 10000 Changed: 9999 Warnings: 0 tidb>ALTER TABLE test.T1 ADD INDEX idx_pid(pid); Query OK, 0 rows affected (3.04 sec) tidb>ANALYZE TABLE test.T1; Query OK, 0 rows affected, 1 warning (0.67 sec) ``` 12. Check the statistics information of table `T1` again. Enter the following statement at the TiDB prompt and receive the results shown: ```sql SHOW STATS_HISTOGRAMS WHERE Table_name='T1' AND Db_name='test' AND Column_name IN ('month', 'idx_month','pid','idx_pid')\G; SHOW STATS_TOPN WHERE Db_name = 'test' AND Table_name = 'T1' AND Column_name IN ('idx_month', 'idx_pid'); ``` ``` tidb>SHOW STATS_HISTOGRAMS WHERE Table_name='T1' AND Db_name='test' AND Column_name IN ('month', 'idx_month','pid','idx_pid')\G; *************************** 1. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: month Is_index: 0 Update_time: 2023-12-04 15:07:28 Distinct_count: 5 Null_count: 0 Avg_col_size: 2 Correlation: 1 Load_status: allEvicted Total_mem_usage: 0 Hist_mem_usage: 0 Topn_mem_usage: 0 Cms_mem_usage: 0 *************************** 2. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: pid Is_index: 0 Update_time: 2023-12-04 15:07:28 Distinct_count: 10000 Null_count: 0 Avg_col_size: 4.89 Correlation: 0.8186540409145404 Load_status: allEvicted Total_mem_usage: 0 Hist_mem_usage: 0 Topn_mem_usage: 0 Cms_mem_usage: 0 *************************** 3. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: idx_month Is_index: 1 Update_time: 2023-12-04 15:07:28 Distinct_count: 5 Null_count: 0 Avg_col_size: 0 Correlation: 0 Load_status: allLoaded Total_mem_usage: 242 Hist_mem_usage: 0 Topn_mem_usage: 242 Cms_mem_usage: 0 *************************** 4. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: idx_pid Is_index: 1 Update_time: 2023-12-04 15:07:28 Distinct_count: 10000 Null_count: 0 Avg_col_size: 0 Correlation: 0 Load_status: allLoaded Total_mem_usage: 42703 Hist_mem_usage: 21671 Topn_mem_usage: 21032 Cms_mem_usage: 0 4 rows in set (0.00 sec) tidb>SHOW STATS_TOPN WHERE Db_name = 'test' AND Table_name = 'T1' AND Column_name IN ('idx_month', 'idx_pid'); +---------+------------+----------------+-------------+----------+-------+-------+ | Db_name | Table_name | Partition_name | Column_name | Is_index | Value | Count | +---------+------------+----------------+-------------+----------+-------+-------+ | test | T1 | | idx_month | 1 | 1 | 2000 | | test | T1 | | idx_month | 1 | 2 | 2000 | | test | T1 | | idx_month | 1 | 3 | 2000 | | test | T1 | | idx_month | 1 | 4 | 2000 | | test | T1 | | idx_month | 1 | 5 | 2000 | | test | T1 | | idx_pid | 1 | 1 | 1 | | test | T1 | | idx_pid | 1 | 10 | 1 | | test | T1 | | idx_pid | 1 | 100 | 1 | | test | T1 | | idx_pid | 1 | 1000 | 1 | | test | T1 | | idx_pid | 1 | 10000 | 1 | ... | test | T1 | | idx_pid | 1 | 1445 | 1 | | test | T1 | | idx_pid | 1 | 1446 | 1 | | test | T1 | | idx_pid | 1 | 1447 | 1 | +---------+------------+----------------+-------------+----------+-------+-------+ 505 rows in set (0.00 sec) ``` + How is the information on the `month` and `pid` columns and the index `idx_month` and `idx_pid` recorded in the statistics? Answer: In `STATS_HISTOGRAMS`, you found there are 5 different values in the `month` column of table `T1`, and 10,000 different values in the `pid` column of table `T1`. In `STATS_TOPN`, you found the 5 different values of `month` column is `1`,`2`,`3`,`4` and `5`, each of them has 2,000 rows, you found the top 500 different values of `pid` column is `1`...`1447`, each of them has 1 row1.(Please note that TopN only records the top 500 values by default.) 13. Execute the following 5 statements to view the EXPLAIN output for a query against the `T1` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; ``` ``` tidb>EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.20 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.20 | cop[tikv] | | eq(test.t1.month, "1") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.01 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.20 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.20 | cop[tikv] | | eq(test.t1.month, "2") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.20 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.20 | cop[tikv] | | eq(test.t1.month, "3") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.01 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.20 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.20 | cop[tikv] | | eq(test.t1.month, "4") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.20 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.20 | cop[tikv] | | eq(test.t1.month, "5") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) ``` + Which index does the query use? Why? Answer: `idx_pid`, This is because the selectivity of `idx_pid`(1:1) is higher than the selectivity of `idx_month`(10000:5). + Selectivity is a ratio between the number of values in a column and the number of values that are distinct or unique. High selectivity means that the ratio is approaching 1:1. 14. Execute the following statement to view the EXPLAIN output for a query against the `T1` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; ``` ``` tidb>EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; +-------------------------------+---------+-----------+----------------------------------+-----------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+----------------------------------+-----------------------------------+ | IndexLookUp_11 | 0.00 | root | | | | ├─IndexRangeScan_8(Build) | 0.00 | cop[tikv] | table:T1, index:idx_month(month) | range:["6","6"], keep order:false | | └─Selection_10(Probe) | 0.00 | cop[tikv] | | eq(test.t1.pid, "1000") | | └─TableRowIDScan_9 | 0.00 | cop[tikv] | table:T1 | keep order:false | +-------------------------------+---------+-----------+----------------------------------+-----------------------------------+ 4 rows in set (0.00 sec) ``` + Which index does the query use? Why? Answer: `idx_month`. This is because the optimizer can deduce from the TopN statistics(which was check during Step 12) that there are no rows with a value of 6 in the `month` column. 15. Exit the TiDB Session to Linux terminal window and insert date into T1. Enter the following statement at the TiDB prompt and receive the results shown: ``` for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('6',29,'Berlin','10001')"; done; ``` ``` $ for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -e "insert into test.T1(month,cnt,pname,pid)values('6',29,'Berlin','10001')"; done; mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ... mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ``` 16. Start an interactive TiDB session again. Enter the following statement at the TiDB prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> prompt tidb> PROMPT set to 'tidb>' ``` 17. 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 ``` 18. Check the data distribution of table `T1`. Enter the following statement at the TiDB prompt and receive the results shown: ```sql SELECT COUNT(*) FROM T1; SELECT month, COUNT(*) FROM T1 GROUP BY month; ``` ``` tidb>SELECT COUNT(*) FROM T1; +----------+ | COUNT(*) | +----------+ | 12000 | +----------+ 1 row in set (0.01 sec) tidb>SELECT month, COUNT(*) FROM T1 GROUP BY month; +-------+----------+ | month | COUNT(*) | +-------+----------+ | 1 | 2000 | | 3 | 2000 | | 4 | 2000 | | 2 | 2000 | | 6 | 2000 | | 5 | 2000 | +-------+----------+ 6 rows in set (0.01 sec) ``` 19. Execute the following 6 statements to view the EXPLAIN output for a query against the `T1` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; ``` ``` tidb>EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.24 | root | | | | ├─IndexRangeScan_12(Build) | 1.20 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.24 | cop[tikv] | | eq(test.t1.month, "1") | | └─TableRowIDScan_13 | 1.20 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.24 | root | | | | ├─IndexRangeScan_12(Build) | 1.20 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.24 | cop[tikv] | | eq(test.t1.month, "2") | | └─TableRowIDScan_13 | 1.20 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.24 | root | | | | ├─IndexRangeScan_12(Build) | 1.20 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.24 | cop[tikv] | | eq(test.t1.month, "3") | | └─TableRowIDScan_13 | 1.20 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.24 | root | | | | ├─IndexRangeScan_12(Build) | 1.20 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.24 | cop[tikv] | | eq(test.t1.month, "4") | | └─TableRowIDScan_13 | 1.20 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.01 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.24 | root | | | | ├─IndexRangeScan_12(Build) | 1.20 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.24 | cop[tikv] | | eq(test.t1.month, "5") | | └─TableRowIDScan_13 | 1.20 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; +-------------------------------+---------+-----------+----------------------------------+-----------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+----------------------------------+-----------------------------------+ | IndexLookUp_11 | 0.00 | root | | | | ├─IndexRangeScan_8(Build) | 0.00 | cop[tikv] | table:T1, index:idx_month(month) | range:["6","6"], keep order:false | | └─Selection_10(Probe) | 0.00 | cop[tikv] | | eq(test.t1.pid, "1000") | | └─TableRowIDScan_9 | 0.00 | cop[tikv] | table:T1 | keep order:false | +-------------------------------+---------+-----------+----------------------------------+-----------------------------------+ 4 rows in set (0.01 sec) ``` + Is there a performance problem here? Why? Answer: Yes. In the current `T1` table, there are 6 values in the `month` column, and each value has 2000 rows. When the `WHERE` predicate has a `month` value of 1, 2, 3, 4, or 5, the optimizer can correctly choose the index `idx_pid` with a higher selectivity. However, when `month`=6, the optimizer chooses an index, `idx_month`, with a very low selectivity. Therefore, it is suspected that the reason is expired statistics. 20. Check the statistics information of table `T1` again. Enter the following statement at the TiDB prompt and receive the results shown: ```sql SHOW STATS_HISTOGRAMS WHERE Table_name='T1' AND Db_name='test' AND Column_name IN ('month', 'idx_month','pid','idx_pid')\G; SHOW STATS_TOPN WHERE Db_name = 'test' AND Table_name = 'T1' AND Column_name IN ('idx_month', 'idx_pid'); ``` ``` tidb>SHOW STATS_HISTOGRAMS WHERE Table_name='T1' AND Db_name='test' AND Column_name IN ('month', 'idx_month','pid','idx_pid')\G; *************************** 1. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: month Is_index: 0 Update_time: 2023-12-04 15:07:28 Distinct_count: 5 Null_count: 0 Avg_col_size: 2 Correlation: 1 Load_status: allEvicted Total_mem_usage: 0 Hist_mem_usage: 0 Topn_mem_usage: 0 Cms_mem_usage: 0 *************************** 2. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: pid Is_index: 0 Update_time: 2023-12-04 15:07:28 Distinct_count: 10000 Null_count: 0 Avg_col_size: 4.89 Correlation: 0.8186540409145404 Load_status: allEvicted Total_mem_usage: 0 Hist_mem_usage: 0 Topn_mem_usage: 0 Cms_mem_usage: 0 *************************** 3. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: idx_month Is_index: 1 Update_time: 2023-12-04 15:07:28 Distinct_count: 5 Null_count: 0 Avg_col_size: 0 Correlation: 0 Load_status: allLoaded Total_mem_usage: 242 Hist_mem_usage: 0 Topn_mem_usage: 242 Cms_mem_usage: 0 *************************** 4. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: idx_pid Is_index: 1 Update_time: 2023-12-04 15:07:28 Distinct_count: 10000 Null_count: 0 Avg_col_size: 0 Correlation: 0 Load_status: allLoaded Total_mem_usage: 42703 Hist_mem_usage: 21671 Topn_mem_usage: 21032 Cms_mem_usage: 0 4 rows in set (0.00 sec) tidb>SHOW STATS_TOPN WHERE Db_name = 'test' AND Table_name = 'T1' AND Column_name IN ('idx_month', 'idx_pid'); +---------+------------+----------------+-------------+----------+-------+-------+ | Db_name | Table_name | Partition_name | Column_name | Is_index | Value | Count | +---------+------------+----------------+-------------+----------+-------+-------+ | test | T1 | | idx_month | 1 | 1 | 2000 | | test | T1 | | idx_month | 1 | 2 | 2000 | | test | T1 | | idx_month | 1 | 3 | 2000 | | test | T1 | | idx_month | 1 | 4 | 2000 | | test | T1 | | idx_month | 1 | 5 | 2000 | | test | T1 | | idx_pid | 1 | 1 | 1 | | test | T1 | | idx_pid | 1 | 10 | 1 | | test | T1 | | idx_pid | 1 | 100 | 1 | | test | T1 | | idx_pid | 1 | 1000 | 1 | | test | T1 | | idx_pid | 1 | 10000 | 1 | ... | test | T1 | | idx_pid | 1 | 1445 | 1 | | test | T1 | | idx_pid | 1 | 1446 | 1 | | test | T1 | | idx_pid | 1 | 1447 | 1 | +---------+------------+----------------+-------------+----------+-------+-------+ 505 rows in set (0.00 sec) ``` + How is the information on the `month` and `pid` columns and the index `idx_month` and `idx_pid` recorded in the statistics? Answer: In `STATS_HISTOGRAMS`, you found there are 5 different values in the `month` column of table `T1`, and 10,000 different values in the `pid` column of table `T1`. In `STATS_TOPN`, you found the 5 different values of `month` column is `1`,`2`,`3`,`4` and `5`, each of them has 2,000 rows, you found the top 500 different values of `pid` column is `1`...`1447`, each of them has 1 row1.(Please note that TopN only records the top 500 values by default.) 21. Please answer the following questions: + Are the statistics expired? Answer: Yes, the statistics is the same as before(Step 12) inserting 2000 rows with value 6 of `month` column. Therefore, the optimizer assumes that there is no row in table `T1` with `month=6` and uses the `idx_month` index as before. + Why would the statistics be expired? Answer: This is because the system variable `tidb_auto_analyze_ratio` is 0.5, which means that the statistics is only automatically updated when the modified data reaches 50%. Obviously, updating 2000 rows of data does not reach 50%. You can check `STATS_META` to confirm. Enter the following command at the terminal prompt and receive the results shown: ```sql SHOW STATS_META WHERE table_name='T1'\G; ``` ``` tidb>SHOW STATS_META WHERE table_name='T1'\G; *************************** 1. row *************************** Db_name: test Table_name: T1 Partition_name: Update_time: 2023-12-04 15:44:29 Modify_count: 2000 Row_count: 12000 1 row in set (0.00 sec) ``` + How should you solve the performance issue in Step19? Answer: You need to manually collect statistics of table `T1` to solve the performance problem. 22. Collect the statistic of table `T1`. Enter the following command at the terminal prompt and receive the results shown: ```sql ANALYZE TABLE T1; ``` ``` tidb>ANALYZE TABLE T1; Query OK, 0 rows affected, 1 warning (0.29 sec) ``` 23. Re-issue the EXPLAIN statement from step 19. Enter the following command at the terminal prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; ``` ``` tidb>EXPLAIN SELECT * FROM T1 WHERE month='1' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.17 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.17 | cop[tikv] | | eq(test.t1.month, "1") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='2' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.17 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.17 | cop[tikv] | | eq(test.t1.month, "2") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='3' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.17 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.17 | cop[tikv] | | eq(test.t1.month, "3") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='4' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.17 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.17 | cop[tikv] | | eq(test.t1.month, "4") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='5' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.17 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.17 | cop[tikv] | | eq(test.t1.month, "5") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) tidb>EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ | IndexLookUp_15 | 0.17 | root | | | | ├─IndexRangeScan_12(Build) | 1.00 | cop[tikv] | table:T1, index:idx_pid(pid) | range:["1000","1000"], keep order:false | | └─Selection_14(Probe) | 0.17 | cop[tikv] | | eq(test.t1.month, "6") | | └─TableRowIDScan_13 | 1.00 | cop[tikv] | table:T1 | keep order:false | +--------------------------------+---------+-----------+------------------------------+-----------------------------------------+ 4 rows in set (0.00 sec) ``` + Has the previous performance issue been resolved? Answer: Yes. All queries have utilized the correct indexes. 24. Check the statistics information of table `T1` again. Enter the following command at the terminal prompt and receive the results shown: ```sql SHOW STATS_META WHERE table_name='T1'\G; SHOW STATS_HISTOGRAMS WHERE Table_name='T1' AND Db_name='test' AND Column_name IN ('month', 'idx_month','pid','idx_pid')\G; SHOW STATS_TOPN WHERE Db_name = 'test' AND Table_name = 'T1' AND Column_name IN ('idx_month', 'idx_pid'); ``` ``` tidb>SHOW STATS_META WHERE table_name='T1'\G; *************************** 1. row *************************** Db_name: test Table_name: T1 Partition_name: Update_time: 2023-12-04 16:21:17 Modify_count: 0 Row_count: 12000 1 row in set (0.00 sec) ERROR: No query specified tidb>SHOW STATS_HISTOGRAMS WHERE Table_name='T1' AND Db_name='test' AND Column_name IN ('month', 'idx_month','pid','idx_pid')\G; *************************** 1. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: month Is_index: 0 Update_time: 2023-12-04 16:21:17 Distinct_count: 6 Null_count: 0 Avg_col_size: 2 Correlation: 1 Load_status: allLoaded Total_mem_usage: 284 Hist_mem_usage: 0 Topn_mem_usage: 284 Cms_mem_usage: 0 *************************** 2. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: pid Is_index: 0 Update_time: 2023-12-04 16:21:17 Distinct_count: 9982 Null_count: 0 Avg_col_size: 5.07 Correlation: 0.06233068719674088 Load_status: allLoaded Total_mem_usage: 41407 Hist_mem_usage: 20375 Topn_mem_usage: 21032 Cms_mem_usage: 0 *************************** 3. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: idx_month Is_index: 1 Update_time: 2023-12-04 16:21:17 Distinct_count: 6 Null_count: 0 Avg_col_size: 0 Correlation: 0 Load_status: allLoaded Total_mem_usage: 284 Hist_mem_usage: 0 Topn_mem_usage: 284 Cms_mem_usage: 0 *************************** 4. row *************************** Db_name: test Table_name: T1 Partition_name: Column_name: idx_pid Is_index: 1 Update_time: 2023-12-04 16:21:17 Distinct_count: 9982 Null_count: 0 Avg_col_size: 0 Correlation: 0 Load_status: allLoaded Total_mem_usage: 42767 Hist_mem_usage: 21735 Topn_mem_usage: 21032 Cms_mem_usage: 0 4 rows in set (0.00 sec) ERROR: No query specified tidb>SHOW STATS_TOPN WHERE Db_name = 'test' AND Table_name = 'T1' AND Column_name IN ('idx_month', 'idx_pid'); +---------+------------+----------------+-------------+----------+-------+-------+ | Db_name | Table_name | Partition_name | Column_name | Is_index | Value | Count | +---------+------------+----------------+-------------+----------+-------+-------+ | test | T1 | | idx_month | 1 | 1 | 2000 | | test | T1 | | idx_month | 1 | 2 | 2000 | | test | T1 | | idx_month | 1 | 3 | 2000 | | test | T1 | | idx_month | 1 | 4 | 2000 | | test | T1 | | idx_month | 1 | 5 | 2000 | | test | T1 | | idx_month | 1 | 6 | 2000 | | test | T1 | | idx_pid | 1 | 1 | 1 | | test | T1 | | idx_pid | 1 | 10 | 1 | | test | T1 | | idx_pid | 1 | 100 | 1 | | test | T1 | | idx_pid | 1 | 1000 | 1 | | test | T1 | | idx_pid | 1 | 10000 | 1 | | test | T1 | | idx_pid | 1 | 10001 | 2000 | | test | T1 | | idx_pid | 1 | 1001 | 1 | ... | test | T1 | | idx_pid | 1 | 1444 | 1 | | test | T1 | | idx_pid | 1 | 1445 | 1 | | test | T1 | | idx_pid | 1 | 1446 | 1 | +---------+------------+----------------+-------------+----------+-------+-------+ 506 rows in set (0.00 sec) ``` 25. Clean up the table T1. Enter the following command at the terminal prompt and receive the results shown: ```sql DROP TABLE T1; ``` ``` tidb>DROP TABLE T1; Query OK, 0 rows affected (0.53 sec) ``` 26. Leave the terminal window with the TiDB session open for the next practice. ## End of Exercises