我的书签
添加书签
移除书签SHOW ANALYZE STATUS
SHOW ANALYZE STATUS 语句提供 TiDB 正在执行的统计信息收集任务以及有限条历史任务记录。
从 TiDB v6.1.0 起，执行 SHOW ANALYZE STATUS 语句将显示集群级别的任务，且 TiDB 重启后仍能看到重启之前的任务记录。在 TiDB v6.1.0 之前，执行 SHOW ANALYZE STATUS 语句仅显示实例级别的任务，且 TiDB 重启后任务记录会被清空。
从 TiDB v6.1.0 起，你可以通过系统表 mysql.analyze_jobs 查看过去 7 天内的历史记录。
从 TiDB v7.3.0 起，你可以通过系统表 mysql.analyze_jobs 或者 SHOW ANALYZE STATUS 查看当前 ANALYZE 任务的进度。
语法图
ShowAnalyzeStatusStmt
ShowLikeOrWhereOpt
1. ShowAnalyzeStatusStmt ::= 'SHOW' 'ANALYZE' 'STATUS' ShowLikeOrWhereOpt
3. ShowLikeOrWhereOpt ::= 'LIKE' SimpleExpr | 'WHERE' Expression
示例
1. mysql> create table t(x int, index idx(x)) partition by hash(x) partitions 2;
2. Query OK, 0 rows affected (0.69 sec)
4. mysql> set @@tidb_analyze_version = 1;
5. Query OK, 0 rows affected (0.00 sec)
7. mysql> analyze table t;
8. Query OK, 0 rows affected (0.20 sec)
10. mysql> show analyze status;
11. +--------------+------------+----------------+-------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+------------------+----------+---------------------+
12. | Table_schema | Table_name | Partition_name | Job_info | Processed_rows | Start_time | End_time | State | Fail_reason | Instance | Process_ID | Remaining_seconds| Progress | Estimated_total_rows|
13. +--------------+------------+----------------+-------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+------------------+----------+---------------------+
14. | test | t | p1 | analyze index idx | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
15. | test | t | p0 | analyze index idx | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
16. | test | t | p1 | analyze columns | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
17. | test | t | p0 | analyze columns | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
18. | test | t1 | p0 | analyze columns | 28523259 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | running | NULL | 127.0.0.1:4000 | 690208308 | 0s | 0.9843 | 28978290 |
19. +--------------+------------+----------------+-------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+------------------+----------+---------------------+
20. 4 rows in set (0.01 sec)
22. mysql> set @@tidb_analyze_version = 2;
23. Query OK, 0 rows affected (0.00 sec)
25. mysql> analyze table t;
26. Query OK, 0 rows affected, 2 warnings (0.03 sec)
28. mysql> show analyze status;
29. +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+--------------------+----------+----------------------+
30. | Table_schema | Table_name | Partition_name | Job_info | Processed_rows | Start_time | End_time | State | Fail_reason | Instance | Process_ID | Remaining_seconds | Progress | Estimated_total_rows |
31. +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+--------------------+----------+----------------------+
32. | test | t | p1 | analyze table all columns with 256 buckets, 500 topn, 1 samplerate | 0 | 2022-05-27 11:30:12 | 2022-05-27 11:30:12 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
33. | test | t | p0 | analyze table all columns with 256 buckets, 500 topn, 1 samplerate | 0 | 2022-05-27 11:30:12 | 2022-05-27 11:30:12 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
34. | test | t | p1 | analyze index idx | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
35. | test | t | p0 | analyze index idx | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
36. | test | t | p1 | analyze columns | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
37. | test | t | p0 | analyze columns | 0 | 2022-05-27 11:29:46 | 2022-05-27 11:29:46 | finished | NULL | 127.0.0.1:4000 | NULL | NULL | NULL | NULL |
38. +--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+--------------------+----------+----------------------+
39. 6 rows in set (0.00 sec)
MySQL 兼容性
该语句是 TiDB 对 MySQL 语法的扩展。
