## 模块 1：过时的 TiDB 统计信息 + 在本练习中，你将解决因执行计划变化而导致的查询性能问题。 ### 步骤 1：打开终端，SSH 登录到 TiUP 控制机 + 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 ### 步骤 2. 连接到 TiDB 并切换到 `test` 数据库 ```command mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### 步骤 3. 检查参数 + 检查与自动收集统计信息相关的参数，确保 `tidb_enable_auto_analyze`、`tidb_auto_analyze_ratio`、`tidb_auto_analyze_start_time` 和 `tidb_auto_analyze_end_time` 设置为默认值。 ```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) ``` ### 步骤 4. 创建表 `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 ``` ### 步骤 5. 插入数据 ```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; ``` ### 步骤 6. 再次连接到 TiDB ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### 步骤 7. 收集表 `T1` 的统计信息 ```sql ANALYZE TABLE T1; ``` ```output Query OK, 0 rows affected, 1 warning (0.29 sec) ``` ### 步骤 8. 检查表 `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) ``` ### 步骤 9. 检查表 `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) ``` + 关于 `month` 列以及统计信息中记录的索引 `idx_month`，你能发现什么？ + 答案：在 `STATS_HISTOGRAMS`中，你会发现表 `T1` 中有 5 个不同的值。在 `STATS_TOPN`中，你会发现 `month` 列的 5 个不同值为 `1`、`2`、`3`、`4` 和 `5`，每个值对应 2000 行。 ### 步骤 10. 更新 `pid` 列的值并创建一个索引 ```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) ``` ### 步骤 11. 检查以下查询的执行计划 ```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) ``` + 该查询使用了哪个索引？为什么？ + 答案：`idx_pid`, ，这是因为 `idx_pid` 的选择性 (1:1) 高于 `idx_month` 的选择性 (10000:5)。 + 选择性是指一列中的值的数量与不同或唯一值的数量之间的比率。高选择性意味着该比率接近 1 : 1。 ### 步骤 12. 再次检查表 `T1` 的统计信息 ```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) ``` + 统计信息中是如何记录 `month` 和 `pid` 列以及索引 `idx_month` 和 `idx_pid` 的信息的? + 答案：在 `STATS_HISTOGRAMS`中，你会发现表 `T1` 的 `month` 列中有 5 个不同的值，`pid` 列中有 10000 个不同的值。在 `STATS_TOPN`,中，你会发现 `month` 列的 5 个不同值为 `1`、`2`、`3`、`4` 和 `5`，每个值对应 2000 行；以及 `pid` 列的前 500 个不同值为`1`...`1447`，每个值对应 1 行。（请注意，默认情况下 TopN 仅记录前 500 个值。） ### 步骤 13. 检查以下查询的执行计划 ```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) ``` + 该查询使用了哪个索引？为什么？ + 答案：`idx_month`. 。这是因为优化器可以从 TopN 统计信息（在步骤 12 中检查过）推断出 `month` 列中不存在值为 6 的行。 ### 步骤 14. 退出 TiDB 会话并向 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; ``` ### 步骤 15. 再次连接到 TiDB ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### 步骤 16. 检查表 `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) ``` ### 步骤 17. 检查以下查询的执行计划 ```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) ``` + 这里存在性能问题吗？为什么？ + 答案：存在。在当前的 `T1` 表中，`month` 列有 6 个值，每个值有 2000 行。当 `WHERE` 谓词中的 `month` 值为 1、2、3、4 或 5 时，优化器可以正确选择选择性更高的索引 `idx_pid`。然而，当 `month`=6 时，优化器选择了选择性非常低的索引`idx_month`。因此，怀疑原因是统计信息过期。 ### 步骤 18. 再次检查表 `T1` 的统计信息 ```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) ``` + 统计信息中是如何记录 `month` 和 `pid` 列以及索引 `idx_month` 和 `idx_pid` 的信息的？ + 答案：在 `STATS_HISTOGRAMS`中，你会发现表 `T1` 的 `month` 列中有 5 个不同的值，`pid` 列中有 10000 个不同的值。在 `STATS_TOPN`中，你会发现 `month` 列的 5 个不同值为 `1`、`2`、`3`、`4` 和 `5`，每个值对应 2000 行；以及 `pid` 列的前 500 个不同值为 `1`...`1447`，每个值对应 1 行。（请注意，默认情况下 TopN 仅记录前 500 个值。） + 统计信息过期了吗？ + 答案：是的，统计信息与之前（步骤 12）在 `month` 列插入值为 6 的 2000 行数据之前相同。因此，优化器假定表 `T1` 中不存在 `month=6` 的行，并像以前一样使用 `idx_month` 索引。 + 为什么统计信息会过期？ + 答案：这是因为系统变量 `tidb_auto_analyze_ratio` 为 0.5，这意味着只有当修改的数据达到 50%时，统计信息才会自动更新。显然，更新 2000 行数据未达到 50%。你可以查看 `STATS_META` 进行确认。 ```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) ``` + 你应该如何解决这里的性能问题？ + 答案：你需要手动收集表 `T1` 的统计信息来解决性能问题。 ### 步骤 19. 收集表 `T1` 的统计信息 ```sql ANALYZE TABLE T1; ``` ```output Query OK, 0 rows affected, 1 warning (0.29 sec) ``` ### 步骤 20. 再次检查以下查询的执行计划 ```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) ``` + 之前的性能问题解决了吗？答案：解决了。所有查询都使用了正确的索引。 ### 步骤 21. 再次检查表 `T1` 的统计信息 ```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) ``` ### 步骤 22. 清理表 `T1` ```sql DROP TABLE T1; ``` ```output Query OK, 0 rows affected (0.53 sec) ```