## モジュール1: 古いTiDB統計 + この演習では、実行計画の変更によって引き起こされるクエリパフォーマンスの問題を解決します。 ### 手順 1. リモート Linux にログインします + ここをクリック - [ターミナル 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` で、それぞれが2,000行を持っていることがわかりました。 ### 手順 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つの異なる値があり、テーブル`T1`の`pid`カラムには10,000の異なる値があることがわかりました。`STATS_TOPN`では、`month`カラムの5つの異なる値が`1`、`2`、`3`、`4`、`5`であり、それぞれが2,000行あることがわかりました。また、`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.00 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つの異なる値があり、テーブル `T1` の `pid` カラムに10,000の異なる値があることがわかりました。 `STATS_TOPN` では、`month` カラムの5つの異なる値が `1`、`2`、`3`、`4`、`5` で、それぞれ2,000行あることがわかりました。また、`pid` カラムのトップ500の異なる値が `1`…`1447` で、それぞれ1行1です。(TopNはデフォルトでトップ500の値のみを記録することに注意してください。) + 統計は期限切れですか? + 答え: はい、統計は2000行の `month` カラムの値6を挿入する前(Step 12)と同じままです。そのため、オプティマイザーはテーブル `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) ```