# Lab 5: Statistics Management
### Overview
+ In this exercise, you test how statistics affect execution plans and the performance issues caused by the stale statistics.
### Notes
+ Click [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download the private key file, which is required to log into the Control Machine for this lab.
+ Commands and SQL statements presented on this page are clickable for easy copy and paste.
+ Your environment may be different from the one shown on the lab guide, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide.
+ The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt.
### Assumptions
+ The private key file is downloaded and placed under `~/.ssh/` folder locally. Relevant permission has been provided by [chmod 400 ~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)
+ For Windows users, please refer to this [guide](https://github.com/pingcap-inc/tidb-course-labs/blob/master/README_303_class_setup_guide_for_student_lab_control_en.md) for the private key preparation.
## Exercise 1
### Subject: Obsolete TiDB statistics
### Overview
+ In this exercise, you resolve the query performance problem caused by changes in execution plans.
### Duration
+ This exercise should take you approximately 15 minutes to complete.
### Tasks
+ Load some data into TiDB, check the statistics, and view the execution plan for a eew
### Solution
0. Login as user ec2-user on monitor1_address. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`.
```command
$ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address
```
```output
, #_
~\_ ####_ Amazon Linux 2
~~ \_#####\
~~ \###|
~~ \#/ ___
~~ V~' '->
~~~ / A newer version of Amazon Linux is available!
~~._. _/
_/ _/ Amazon Linux 2023, GA and supported until 2028-03-15.
_/m/' https://aws.amazon.com/linux/amazon-linux-2023/
```
1. Login as user `ec2-user` on `Control Machine`.
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
2. Make `test` the current database.
```command
USE test;
```
```output
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.
```command
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)
```
4. Execute the following statement to create table `T1` for your exercise.
```command
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
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.
```command
EXIT
```
```output
tidb>EXIT
Bye
```
```command
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('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('5',25,'Los Angeles','10')"; done;
```
6. Start an interactive TiDB session again.
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
7. Make `test` the current database.
```command
USE test;
```
```output
tidb>USE test;
Database changed
```
8. Collect the statistic of table `T1`.
```command
ANALYZE TABLE T1;
```
```output
tidb>ANALYZE TABLE T1;
Query OK, 0 rows affected, 1 warning (0.29 sec)
```
9. Check the data distribution of table `T1`.
```command
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)
```
10. Check the statistics information of table `T1`.
```command
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.
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`.
```command
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)
```
12. Execute the following 5 statements to view the EXPLAIN output for a query against the `T1` table.
```command
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.
13. Check the statistics information of table `T1` again.
```command
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 row1.(Please note that TopN only records the top 500 values by default.)
14. Execute the following statement to view the EXPLAIN output for a query against the `T1` table.
```command
EXPLAIN SELECT * FROM T1 WHERE month='6' and pid='1000';
```
```output
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.
```command
EXIT
```
```output
tidb>EXIT
Bye
```
```command
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;
```
```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('6',29,'Berlin','10001')"; done;
```
16. Start an interactive TiDB session again.
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
17. Make `test` the current database.
```command
USE test;
```
```output
tidb>USE test;
Database changed
```
18. Check the data distribution of table `T1`.
```command
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)
```
19. Execute the following 6 statements to view the EXPLAIN output for a query against the `T1` table.
```command
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.
20. Check the statistics information of table `T1` again.
```command
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 row1.(Please note that TopN only records the top 500 values by default.)
21. 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.
```command
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 in Step19? Answer: You need to manually collect statistics of table `T1` to solve the performance problem.
22. Collect the statistic of table `T1`.
```command
ANALYZE TABLE T1;
```
```output
tidb>ANALYZE TABLE T1;
Query OK, 0 rows affected, 1 warning (0.29 sec)
```
23. Re-issue the EXPLAIN statement from step 19.
```command
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.
24. Check the statistics information of table `T1` again.
```command
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)
```
25. Clean up the table T1.
```command
DROP TABLE T1;
```
```output
tidb>DROP TABLE T1;
Query OK, 0 rows affected (0.53 sec)
```
## End of Exercises