我的书签
添加书签
移除书签SLOW_QUERY
SLOW_QUERY 表中提供了当前节点的慢查询相关的信息，其内容通过解析当前节点的 TiDB 慢查询日志而来，列名和慢日志中的字段名是一一对应。关于如何使用该表调查和改善慢查询，请参考慢查询日志文档。
1. USE INFORMATION_SCHEMA;
2. DESC slow_query;
输出结果示例如下：
1. +-------------------------------+---------------------+------+------+---------+-------+
2. | Field | Type | Null | Key | Default | Extra |
3. +-------------------------------+---------------------+------+------+---------+-------+
4. | Time | timestamp(6) | NO | PRI | NULL | |
5. | Txn_start_ts | bigint(20) unsigned | YES | | NULL | |
6. | User | varchar(64) | YES | | NULL | |
7. | Host | varchar(64) | YES | | NULL | |
8. | Conn_ID | bigint(20) unsigned | YES | | NULL | |
9. | Exec_retry_count | bigint(20) unsigned | YES | | NULL | |
10. | Exec_retry_time | double | YES | | NULL | |
11. | Query_time | double | YES | | NULL | |
12. | Parse_time | double | YES | | NULL | |
13. | Compile_time | double | YES | | NULL | |
14. | Rewrite_time | double | YES | | NULL | |
15. | Preproc_subqueries | bigint(20) unsigned | YES | | NULL | |
16. | Preproc_subqueries_time | double | YES | | NULL | |
17. | Optimize_time | double | YES | | NULL | |
18. | Wait_TS | double | YES | | NULL | |
19. | Prewrite_time | double | YES | | NULL | |
20. | Wait_prewrite_binlog_time | double | YES | | NULL | |
21. | Commit_time | double | YES | | NULL | |
22. | Get_commit_ts_time | double | YES | | NULL | |
23. | Commit_backoff_time | double | YES | | NULL | |
24. | Backoff_types | varchar(64) | YES | | NULL | |
25. | Resolve_lock_time | double | YES | | NULL | |
26. | Local_latch_wait_time | double | YES | | NULL | |
27. | Write_keys | bigint(22) | YES | | NULL | |
28. | Write_size | bigint(22) | YES | | NULL | |
29. | Prewrite_region | bigint(22) | YES | | NULL | |
30. | Txn_retry | bigint(22) | YES | | NULL | |
31. | Cop_time | double | YES | | NULL | |
32. | Process_time | double | YES | | NULL | |
33. | Wait_time | double | YES | | NULL | |
34. | Backoff_time | double | YES | | NULL | |
35. | LockKeys_time | double | YES | | NULL | |
36. | Request_count | bigint(20) unsigned | YES | | NULL | |
37. | Total_keys | bigint(20) unsigned | YES | | NULL | |
38. | Process_keys | bigint(20) unsigned | YES | | NULL | |
39. | Rocksdb_delete_skipped_count | bigint(20) unsigned | YES | | NULL | |
40. | Rocksdb_key_skipped_count | bigint(20) unsigned | YES | | NULL | |
41. | Rocksdb_block_cache_hit_count | bigint(20) unsigned | YES | | NULL | |
42. | Rocksdb_block_read_count | bigint(20) unsigned | YES | | NULL | |
43. | Rocksdb_block_read_byte | bigint(20) unsigned | YES | | NULL | |
44. | DB | varchar(64) | YES | | NULL | |
45. | Index_names | varchar(100) | YES | | NULL | |
46. | Is_internal | tinyint(1) | YES | | NULL | |
47. | Digest | varchar(64) | YES | | NULL | |
48. | Stats | varchar(512) | YES | | NULL | |
49. | Cop_proc_avg | double | YES | | NULL | |
50. | Cop_proc_p90 | double | YES | | NULL | |
51. | Cop_proc_max | double | YES | | NULL | |
52. | Cop_proc_addr | varchar(64) | YES | | NULL | |
53. | Cop_wait_avg | double | YES | | NULL | |
54. | Cop_wait_p90 | double | YES | | NULL | |
55. | Cop_wait_max | double | YES | | NULL | |
56. | Cop_wait_addr | varchar(64) | YES | | NULL | |
57. | Mem_max | bigint(20) | YES | | NULL | |
58. | Disk_max | bigint(20) | YES | | NULL | |
59. | KV_total | double | YES | | NULL | |
60. | PD_total | double | YES | | NULL | |
61. | Backoff_total | double | YES | | NULL | |
62. | Write_sql_response_total | double | YES | | NULL | |
63. | Result_rows | bigint(22) | YES | | NULL | |
64. | Warnings | longtext | YES | | NULL | |
65. | Backoff_Detail | varchar(4096) | YES | | NULL | |
66. | Prepared | tinyint(1) | YES | | NULL | |
67. | Succ | tinyint(1) | YES | | NULL | |
68. | IsExplicitTxn | tinyint(1) | YES | | NULL | |
69. | IsWriteCacheTable | tinyint(1) | YES | | NULL | |
70. | Plan_from_cache | tinyint(1) | YES | | NULL | |
71. | Plan_from_binding | tinyint(1) | YES | | NULL | |
72. | Has_more_results | tinyint(1) | YES | | NULL | |
73. | Plan | longtext | YES | | NULL | |
74. | Plan_digest | varchar(128) | YES | | NULL | |
75. | Binary_plan | longtext | YES | | NULL | |
76. | Prev_stmt | longtext | YES | | NULL | |
77. | Query | longtext | YES | | NULL | |
78. +-------------------------------+---------------------+------+------+---------+-------+
79. 74 rows in set (0.001 sec)
CLUSTER_SLOW_QUERY table
CLUSTER_SLOW_QUERY 表中提供了集群所有节点的慢查询相关的信息，其内容通过解析 TiDB 慢查询日志而来，该表使用上和 SLOW_QUERY 表一样。CLUSTER_SLOW_QUERY 表结构上比 SLOW_QUERY 多一列 INSTANCE，表示该行慢查询信息来自的 TiDB 节点地址。关于如何使用该表调查和改善慢查询，请参考慢查询日志文档。
1. DESC CLUSTER_SLOW_QUERY;
输出结果示例如下：
1. +-------------------------------+---------------------+------+------+---------+-------+
2. | Field | Type | Null | Key | Default | Extra |
3. +-------------------------------+---------------------+------+------+---------+-------+
4. | INSTANCE | varchar(64) | YES | | NULL | |
5. | Time | timestamp(6) | NO | PRI | NULL | |
6. | Txn_start_ts | bigint(20) unsigned | YES | | NULL | |
7. | User | varchar(64) | YES | | NULL | |
8. | Host | varchar(64) | YES | | NULL | |
9. | Conn_ID | bigint(20) unsigned | YES | | NULL | |
10. | Exec_retry_count | bigint(20) unsigned | YES | | NULL | |
11. | Exec_retry_time | double | YES | | NULL | |
12. | Query_time | double | YES | | NULL | |
13. | Parse_time | double | YES | | NULL | |
14. | Compile_time | double | YES | | NULL | |
15. | Rewrite_time | double | YES | | NULL | |
16. | Preproc_subqueries | bigint(20) unsigned | YES | | NULL | |
17. | Preproc_subqueries_time | double | YES | | NULL | |
18. | Optimize_time | double | YES | | NULL | |
19. | Wait_TS | double | YES | | NULL | |
20. | Prewrite_time | double | YES | | NULL | |
21. | Wait_prewrite_binlog_time | double | YES | | NULL | |
22. | Commit_time | double | YES | | NULL | |
23. | Get_commit_ts_time | double | YES | | NULL | |
24. | Commit_backoff_time | double | YES | | NULL | |
25. | Backoff_types | varchar(64) | YES | | NULL | |
26. | Resolve_lock_time | double | YES | | NULL | |
27. | Local_latch_wait_time | double | YES | | NULL | |
28. | Write_keys | bigint(22) | YES | | NULL | |
29. | Write_size | bigint(22) | YES | | NULL | |
30. | Prewrite_region | bigint(22) | YES | | NULL | |
31. | Txn_retry | bigint(22) | YES | | NULL | |
32. | Cop_time | double | YES | | NULL | |
33. | Process_time | double | YES | | NULL | |
34. | Wait_time | double | YES | | NULL | |
35. | Backoff_time | double | YES | | NULL | |
36. | LockKeys_time | double | YES | | NULL | |
37. | Request_count | bigint(20) unsigned | YES | | NULL | |
38. | Total_keys | bigint(20) unsigned | YES | | NULL | |
39. | Process_keys | bigint(20) unsigned | YES | | NULL | |
40. | Rocksdb_delete_skipped_count | bigint(20) unsigned | YES | | NULL | |
41. | Rocksdb_key_skipped_count | bigint(20) unsigned | YES | | NULL | |
42. | Rocksdb_block_cache_hit_count | bigint(20) unsigned | YES | | NULL | |
43. | Rocksdb_block_read_count | bigint(20) unsigned | YES | | NULL | |
44. | Rocksdb_block_read_byte | bigint(20) unsigned | YES | | NULL | |
45. | DB | varchar(64) | YES | | NULL | |
46. | Index_names | varchar(100) | YES | | NULL | |
47. | Is_internal | tinyint(1) | YES | | NULL | |
48. | Digest | varchar(64) | YES | | NULL | |
49. | Stats | varchar(512) | YES | | NULL | |
50. | Cop_proc_avg | double | YES | | NULL | |
51. | Cop_proc_p90 | double | YES | | NULL | |
52. | Cop_proc_max | double | YES | | NULL | |
53. | Cop_proc_addr | varchar(64) | YES | | NULL | |
54. | Cop_wait_avg | double | YES | | NULL | |
55. | Cop_wait_p90 | double | YES | | NULL | |
56. | Cop_wait_max | double | YES | | NULL | |
57. | Cop_wait_addr | varchar(64) | YES | | NULL | |
58. | Mem_max | bigint(20) | YES | | NULL | |
59. | Disk_max | bigint(20) | YES | | NULL | |
60. | KV_total | double | YES | | NULL | |
61. | PD_total | double | YES | | NULL | |
62. | Backoff_total | double | YES | | NULL | |
63. | Write_sql_response_total | double | YES | | NULL | |
64. | Result_rows | bigint(22) | YES | | NULL | |
65. | Warnings | longtext | YES | | NULL | |
66. | Backoff_Detail | varchar(4096) | YES | | NULL | |
67. | Prepared | tinyint(1) | YES | | NULL | |
68. | Succ | tinyint(1) | YES | | NULL | |
69. | IsExplicitTxn | tinyint(1) | YES | | NULL | |
70. | IsWriteCacheTable | tinyint(1) | YES | | NULL | |
71. | Plan_from_cache | tinyint(1) | YES | | NULL | |
72. | Plan_from_binding | tinyint(1) | YES | | NULL | |
73. | Has_more_results | tinyint(1) | YES | | NULL | |
74. | Plan | longtext | YES | | NULL | |
75. | Plan_digest | varchar(128) | YES | | NULL | |
76. | Binary_plan | longtext | YES | | NULL | |
77. | Prev_stmt | longtext | YES | | NULL | |
78. | Query | longtext | YES | | NULL | |
79. +-------------------------------+---------------------+------+------+---------+-------+
80. 75 rows in set (0.001 sec)
查询集群系统表时，TiDB 也会将相关计算下推给其他节点执行，而不是把所有节点的数据都取回来，可以查看执行计划，如下：
1. DESC SELECT COUNT(*) FROM CLUSTER_SLOW_QUERY WHERE user = 'u1';
输出结果示例如下：
1. +----------------------------+----------+-----------+--------------------------+------------------------------------------------------+
2. | id | estRows | task | access object | operator info |
3. +----------------------------+----------+-----------+--------------------------+------------------------------------------------------+
4. | StreamAgg_7 | 1.00 | root | | funcs:count(1)->Column#75 |
5. | └─TableReader_13 | 10.00 | root | | data:Selection_12 |
6. | └─Selection_12 | 10.00 | cop[tidb] | | eq(INFORMATION_SCHEMA.cluster_slow_query.user, "u1") |
7. | └─TableFullScan_11 | 10000.00 | cop[tidb] | table:CLUSTER_SLOW_QUERY | keep order:false, stats:pseudo |
8. +----------------------------+----------+-----------+--------------------------+------------------------------------------------------+
9. 4 rows in set (0.00 sec)
上面执行计划表示，会将 user = u1 条件下推给其他的 (cop) TiDB 节点执行，也会把聚合算子（即上面输出结果中的 StreamAgg 算子）下推。
目前由于没有对系统表收集统计信息，所以有时会导致某些聚合算子不能下推，导致执行较慢，用户可以通过手动指定聚合下推的 SQL HINT 来将聚合算子下推，示例如下：
1. SELECT /*+ AGG_TO_COP() */ COUNT(*) FROM CLUSTER_SLOW_QUERY GROUP BY user;
