## Module 1: Obsolete TiDB Statistics + In this exercise, you resolve the query performance problem caused by changes in execution plans. ### 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. ### Step 2. Connect to TiDB and change to `test` database ```command mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### Step 3. Check the parameters + 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. ```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'; ``` ```output 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) ``` ### Step 4. Create a table `T1` ```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) ); ``` ```output Query OK, 0 rows affected (0.53 sec) ``` ```sql EXIT ``` ```output Bye ``` ### Step 5. Insert the data ```bash for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -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} -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} -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} -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} -e "insert into test.T1(month,cnt,pname,pid)values('5',25,'Los Angeles','10')"; done; ``` ```output for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "insert into test.T1(month,cnt,pname,pid)values('5',25,'Los Angeles','10')"; done; ``` ### Step 6. Connect to TiDB again ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### Step 7. Collect the statistic of table `T1` ```sql ANALYZE TABLE T1; ``` ```output Query OK, 0 rows affected, 1 warning (0.29 sec) ``` ### Step 8. Check the data distribution of table `T1` ```sql SELECT COUNT(*) FROM T1; SELECT month, COUNT(*) FROM T1 GROUP BY month; ``` ```output 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) ``` ### Step 9. Check the statistics of table `T1` ```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'; ``` ```output 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) 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) ``` + What can you find 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. ### Step 10. Update the values of the `pid` column and create an index ```sql UPDATE test.T1 set pid=id; ALTER TABLE test.T1 ADD INDEX idx_pid(pid); ANALYZE TABLE test.T1; ``` ```output 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) ``` ### Step 11. Check the execution plans for the following queries ```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'; ``` ```output 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 close to 1:1. ### Step 12. Check the statistics of table `T1` again ```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'); ``` ```output 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 row.(Please note that TopN only records the top 500 values by default.) ### Step 13. Check the execution plan for the following query ```sql EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000'; ``` ```output +-------------------------------+---------+-----------+----------------------------------+-----------------------------------+ | 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. ### Step 14. Exit the TiDB Session and insert more data into T1 ```sql EXIT ``` ```output Bye ``` ```bash for i in `seq 2000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "insert into test.T1(month,cnt,pname,pid)values('6',29,'Berlin','10001')"; done; ``` ### Step 15. Connect to TiDB again ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### Step 16. Check the data distribution of table `T1` ```sql SELECT COUNT(*) FROM T1; SELECT month, COUNT(*) FROM T1 GROUP BY month; ``` ```output 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) ``` ### Step 17. Check the execution plan of the following queries ```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'; ``` ```output 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. ### Step 18. Check the statistics of table `T1` again ```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'); ``` ```output 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 row.(Please note that TopN only records the top 500 values by default.) + 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. ```sql SHOW STATS_META WHERE table_name='T1'\G ``` ```output 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 here? + Answer: You need to manually collect statistics of table `T1` to solve the performance problem. ### Step 19. Collect the statistic of table `T1` ```sql ANALYZE TABLE T1; ``` ```output Query OK, 0 rows affected, 1 warning (0.29 sec) ``` ### Step 20. Check the execution plan for the following queries again ```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'; ``` ```output 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. ### Step 21. Check the statistics of table `T1` again ```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'); ``` ```output 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) 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) 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) ``` ### Step 22. Clean up the table `T1` ```sql DROP TABLE T1; ``` ```output Query OK, 0 rows affected (0.53 sec) ```