SLOW_QUERY
SLOW_QUERY 表提供当前节点的慢查询信息，这些信息是 TiDB 慢日志文件 的解析结果。表中的列名与慢日志中的字段名一一对应。
USE INFORMATION_SCHEMA;
DESC SLOW_QUERY;
输出如下：
+-------------------------------+---------------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+---------------------+------+------+---------+-------+
| Time | timestamp(6) | NO | PRI | NULL | |
| Txn_start_ts | bigint(20) unsigned | YES | | NULL | |
| User | varchar(64) | YES | | NULL | |
| Host | varchar(64) | YES | | NULL | |
| Conn_ID | bigint(20) unsigned | YES | | NULL | |
| Session_alias | varchar(64) | YES | | NULL | |
| Exec_retry_count | bigint(20) unsigned | YES | | NULL | |
| Exec_retry_time | double | YES | | NULL | |
| Query_time | double | YES | | NULL | |
| Parse_time | double | YES | | NULL | |
| Compile_time | double | YES | | NULL | |
| Rewrite_time | double | YES | | NULL | |
| Preproc_subqueries | bigint(20) unsigned | YES | | NULL | |
| Preproc_subqueries_time | double | YES | | NULL | |
| Optimize_time | double | YES | | NULL | |
| Wait_TS | double | YES | | NULL | |
| Prewrite_time | double | YES | | NULL | |
| Wait_prewrite_binlog_time | double | YES | | NULL | |
| Commit_time | double | YES | | NULL | |
| Get_commit_ts_time | double | YES | | NULL | |
| Commit_backoff_time | double | YES | | NULL | |
| Backoff_types | varchar(64) | YES | | NULL | |
| Resolve_lock_time | double | YES | | NULL | |
| Local_latch_wait_time | double | YES | | NULL | |
| Write_keys | bigint(22) | YES | | NULL | |
| Write_size | bigint(22) | YES | | NULL | |
| Prewrite_region | bigint(22) | YES | | NULL | |
| Txn_retry | bigint(22) | YES | | NULL | |
| Cop_time | double | YES | | NULL | |
| Process_time | double | YES | | NULL | |
| Wait_time | double | YES | | NULL | |
| Backoff_time | double | YES | | NULL | |
| LockKeys_time | double | YES | | NULL | |
| Request_count | bigint(20) unsigned | YES | | NULL | |
| Total_keys | bigint(20) unsigned | YES | | NULL | |
| Process_keys | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_delete_skipped_count | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_key_skipped_count | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_block_cache_hit_count | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_block_read_count | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_block_read_byte | bigint(20) unsigned | YES | | NULL | |
| DB | varchar(64) | YES | | NULL | |
| Index_names | varchar(100) | YES | | NULL | |
| Is_internal | tinyint(1) | YES | | NULL | |
| Digest | varchar(64) | YES | | NULL | |
| Stats | varchar(512) | YES | | NULL | |
| Cop_proc_avg | double | YES | | NULL | |
| Cop_proc_p90 | double | YES | | NULL | |
| Cop_proc_max | double | YES | | NULL | |
| Cop_proc_addr | varchar(64) | YES | | NULL | |
| Cop_wait_avg | double | YES | | NULL | |
| Cop_wait_p90 | double | YES | | NULL | |
| Cop_wait_max | double | YES | | NULL | |
| Cop_wait_addr | varchar(64) | YES | | NULL | |
| Mem_max | bigint(20) | YES | | NULL | |
| Disk_max | bigint(20) | YES | | NULL | |
| KV_total | double | YES | | NULL | |
| PD_total | double | YES | | NULL | |
| Backoff_total | double | YES | | NULL | |
| Write_sql_response_total | double | YES | | NULL | |
| Result_rows | bigint(22) | YES | | NULL | |
| Warnings | longtext | YES | | NULL | |
| Backoff_Detail | varchar(4096) | YES | | NULL | |
| Prepared | tinyint(1) | YES | | NULL | |
| Succ | tinyint(1) | YES | | NULL | |
| IsExplicitTxn | tinyint(1) | YES | | NULL | |
| IsWriteCacheTable | tinyint(1) | YES | | NULL | |
| Plan_from_cache | tinyint(1) | YES | | NULL | |
| Plan_from_binding | tinyint(1) | YES | | NULL | |
| Has_more_results | tinyint(1) | YES | | NULL | |
| Resource_group | varchar(64) | YES | | NULL | |
| Request_unit_read | double | YES | | NULL | |
| Request_unit_write | double | YES | | NULL | |
| Time_queued_by_rc | double | YES | | NULL | |
| Tidb_cpu_time | double | YES | | NULL | |
| Tikv_cpu_time | double | YES | | NULL | |
| Plan | longtext | YES | | NULL | |
| Plan_digest | varchar(128) | YES | | NULL | |
| Binary_plan | longtext | YES | | NULL | |
| Prev_stmt | longtext | YES | | NULL | |
| Query | longtext | YES | | NULL | |
+-------------------------------+---------------------+------+------+---------+-------+
81 rows in set (0.00 sec)
Query 列的最大语句长度受 tidb_stmt_summary_max_sql_length 系统变量限制。
CLUSTER_SLOW_QUERY 表
CLUSTER_SLOW_QUERY 表提供整个集群所有节点的慢查询信息，这些信息是 TiDB 慢日志文件的解析结果。你可以像使用 SLOW_QUERY 表一样使用 CLUSTER_SLOW_QUERY 表。CLUSTER_SLOW_QUERY 表的表结构与 SLOW_QUERY 表的区别在于多了一个 INSTANCE 列。INSTANCE 列表示该慢查询信息所在的 TiDB 节点地址。
DESC CLUSTER_SLOW_QUERY;
输出如下：
+-------------------------------+---------------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+---------------------+------+------+---------+-------+
| INSTANCE | varchar(64) | YES | | NULL | |
| Time | timestamp(6) | NO | PRI | NULL | |
| Txn_start_ts | bigint(20) unsigned | YES | | NULL | |
| User | varchar(64) | YES | | NULL | |
| Host | varchar(64) | YES | | NULL | |
| Conn_ID | bigint(20) unsigned | YES | | NULL | |
| Session_alias | varchar(64) | YES | | NULL | |
| Exec_retry_count | bigint(20) unsigned | YES | | NULL | |
| Exec_retry_time | double | YES | | NULL | |
| Query_time | double | YES | | NULL | |
| Parse_time | double | YES | | NULL | |
| Compile_time | double | YES | | NULL | |
| Rewrite_time | double | YES | | NULL | |
| Preproc_subqueries | bigint(20) unsigned | YES | | NULL | |
| Preproc_subqueries_time | double | YES | | NULL | |
| Optimize_time | double | YES | | NULL | |
| Wait_TS | double | YES | | NULL | |
| Prewrite_time | double | YES | | NULL | |
| Wait_prewrite_binlog_time | double | YES | | NULL | |
| Commit_time | double | YES | | NULL | |
| Get_commit_ts_time | double | YES | | NULL | |
| Commit_backoff_time | double | YES | | NULL | |
| Backoff_types | varchar(64) | YES | | NULL | |
| Resolve_lock_time | double | YES | | NULL | |
| Local_latch_wait_time | double | YES | | NULL | |
| Write_keys | bigint(22) | YES | | NULL | |
| Write_size | bigint(22) | YES | | NULL | |
| Prewrite_region | bigint(22) | YES | | NULL | |
| Txn_retry | bigint(22) | YES | | NULL | |
| Cop_time | double | YES | | NULL | |
| Process_time | double | YES | | NULL | |
| Wait_time | double | YES | | NULL | |
| Backoff_time | double | YES | | NULL | |
| LockKeys_time | double | YES | | NULL | |
| Request_count | bigint(20) unsigned | YES | | NULL | |
| Total_keys | bigint(20) unsigned | YES | | NULL | |
| Process_keys | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_delete_skipped_count | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_key_skipped_count | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_block_cache_hit_count | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_block_read_count | bigint(20) unsigned | YES | | NULL | |
| Rocksdb_block_read_byte | bigint(20) unsigned | YES | | NULL | |
| DB | varchar(64) | YES | | NULL | |
| Index_names | varchar(100) | YES | | NULL | |
| Is_internal | tinyint(1) | YES | | NULL | |
| Digest | varchar(64) | YES | | NULL | |
| Stats | varchar(512) | YES | | NULL | |
| Cop_proc_avg | double | YES | | NULL | |
| Cop_proc_p90 | double | YES | | NULL | |
| Cop_proc_max | double | YES | | NULL | |
| Cop_proc_addr | varchar(64) | YES | | NULL | |
| Cop_wait_avg | double | YES | | NULL | |
| Cop_wait_p90 | double | YES | | NULL | |
| Cop_wait_max | double | YES | | NULL | |
| Cop_wait_addr | varchar(64) | YES | | NULL | |
| Mem_max | bigint(20) | YES | | NULL | |
| Disk_max | bigint(20) | YES | | NULL | |
| KV_total | double | YES | | NULL | |
| PD_total | double | YES | | NULL | |
| Backoff_total | double | YES | | NULL | |
| Write_sql_response_total | double | YES | | NULL | |
| Result_rows | bigint(22) | YES | | NULL | |
| Warnings | longtext | YES | | NULL | |
| Backoff_Detail | varchar(4096) | YES | | NULL | |
| Prepared | tinyint(1) | YES | | NULL | |
| Succ | tinyint(1) | YES | | NULL | |
| IsExplicitTxn | tinyint(1) | YES | | NULL | |
| IsWriteCacheTable | tinyint(1) | YES | | NULL | |
| Plan_from_cache | tinyint(1) | YES | | NULL | |
| Plan_from_binding | tinyint(1) | YES | | NULL | |
| Has_more_results | tinyint(1) | YES | | NULL | |
| Resource_group | varchar(64) | YES | | NULL | |
| Request_unit_read | double | YES | | NULL | |
| Request_unit_write | double | YES | | NULL | |
| Time_queued_by_rc | double | YES | | NULL | |
| Tidb_cpu_time | double | YES | | NULL | |
| Tikv_cpu_time | double | YES | | NULL | |
| Plan | longtext | YES | | NULL | |
| Plan_digest | varchar(128) | YES | | NULL | |
| Binary_plan | longtext | YES | | NULL | |
| Prev_stmt | longtext | YES | | NULL | |
| Query | longtext | YES | | NULL | |
+-------------------------------+---------------------+------+------+---------+-------+
82 rows in set (0.00 sec)
当查询集群系统表时，TiDB 并不会从所有节点拉取数据，而是将相关计算下推到其他节点。执行计划如下：
DESC SELECT COUNT(*) FROM CLUSTER_SLOW_QUERY WHERE user = 'u1';
输出如下：
+----------------------------+----------+-----------+--------------------------+------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+----------+-----------+--------------------------+------------------------------------------------------+
| StreamAgg_7 | 1.00 | root | | funcs:count(1)->Column#75 |
| └─TableReader_13 | 10.00 | root | | data:Selection_12 |
| └─Selection_12 | 10.00 | cop[tidb] | | eq(INFORMATION_SCHEMA.cluster_slow_query.user, "u1") |
| └─TableFullScan_11 | 10000.00 | cop[tidb] | table:CLUSTER_SLOW_QUERY | keep order:false, stats:pseudo |
+----------------------------+----------+-----------+--------------------------+------------------------------------------------------+
4 rows in set (0.00 sec)
在上述执行计划中，user = u1 条件被下推到其他（cop）TiDB 节点，聚合算子也被下推（图中的 StreamAgg 算子）。
目前，由于系统表没有收集统计信息，有时某些聚合算子无法下推，导致执行较慢。此时，你可以手动指定 SQL HINT，将聚合算子下推。例如：
SELECT /*+ AGG_TO_COP() */ COUNT(*) FROM CLUSTER_SLOW_QUERY GROUP BY user;
查看执行信息
通过在 SLOW_QUERY 表上执行 EXPLAIN ANALYZE 查询，你可以获得数据库获取慢查询信息的详细过程信息。但在 CLUSTER_SLOW_QUERY 表上执行 EXPLAIN ANALYZE 时不会返回这些信息。
示例：
EXPLAIN ANALYZE SELECT * FROM INFORMATION_SCHEMA.SLOW_QUERY LIMIT 1\G
*************************** 1. row ***************************
id: Limit_7
estRows: 1.00
actRows: 1
task: root
access object:
execution info: time:3.46ms, loops:2, RU:0.000000
operator info: offset:0, count:1
memory: N/A
disk: N/A
*************************** 2. row ***************************
id: └─MemTableScan_10
estRows: 10000.00
actRows: 64
task: root
access object: table:SLOW_QUERY
execution info: time:3.45ms, loops:1, initialize: 55.5µs, read_file: 1.21ms, parse_log: {time:4.11ms, concurrency:15}, total_file: 1, read_file: 1, read_size: 4.06 MB
operator info: only search in the current 'tidb-slow.log' file
memory: 1.26 MB
disk: N/A
2 rows in set (0.01 sec)
在输出结果中，可以关注 execution info 部分的以下字段（为便于阅读已格式化）：
initialize: 55.5µs,
read_file: 1.21ms,
parse_log: {
time:4.11ms,
concurrency:15
},
total_file: 1,
read_file: 1,
read_size: 4.06 MB
| 字段 | 说明 |
|---|---|
initialize | 初始化耗时 |
read_file | 读取慢日志文件耗时 |
parse_log.time | 解析慢日志文件耗时 |
parse_log.concurrency | 解析慢日志文件的并发度（由 tidb_distsql_scan_concurrency 设置） |
total_file | 慢日志文件总数 |
read_file | 实际读取的慢日志文件数 |
read_size | 从日志文件读取的字节数 |