title

Information Schema

为了和 MySQL 保持兼容，TiDB 支持很多 INFORMATION_SCHEMA 表，其中有不少表都支持相应的 SHOW 命令。查询 INFORMATION_SCHEMA 表也为表的连接操作提供了可能。

ANALYZE_STATUS 表

ANALYZE_STATUS 表提供正在执行的收集统计信息的任务以及有限条历史任务记录。

select * from `ANALYZE_STATUS`;

+--------------+------------+----------------+-------------------+----------------+---------------------+----------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | JOB_INFO          | PROCESSED_ROWS | START_TIME          | STATE    |
+--------------+------------+----------------+-------------------+----------------+---------------------+----------+
| test         | t          |                | analyze index idx | 2              | 2019-06-21 19:51:14 | finished |
| test         | t          |                | analyze columns   | 2              | 2019-06-21 19:51:14 | finished |
| test         | t1         | p0             | analyze columns   | 0              | 2019-06-21 19:51:15 | finished |
| test         | t1         | p3             | analyze columns   | 0              | 2019-06-21 19:51:15 | finished |
| test         | t1         | p1             | analyze columns   | 0              | 2019-06-21 19:51:15 | finished |
| test         | t1         | p2             | analyze columns   | 1              | 2019-06-21 19:51:15 | finished |
+--------------+------------+----------------+-------------------+----------------+---------------------+----------+
6 rows in set

CHARACTER_SETS 表

CHARACTER_SETS 表提供字符集相关的信息。TiDB 目前仅支持部分字符集。

SELECT * FROM character_sets;

+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8               | utf8_bin             | UTF-8 Unicode |      3 |
| utf8mb4            | utf8mb4_bin          | UTF-8 Unicode |      4 |
| ascii              | ascii_bin            | US ASCII      |      1 |
| latin1             | latin1_bin           | Latin1        |      1 |
| binary             | binary               | binary        |      1 |
+--------------------+----------------------+---------------+--------+
5 rows in set (0.00 sec)

COLLATIONS 表

COLLATIONS 表提供了 CHARACTER_SETS 表中字符集对应的排序规则列表。TiDB 当前仅支持二进制排序规则，包含该表仅为兼容 MySQL。

SELECT * FROM collations WHERE character_set_name='utf8mb4';

+------------------------+--------------------+------+------------+-------------+---------+
| COLLATION_NAME         | CHARACTER_SET_NAME | ID   | IS_DEFAULT | IS_COMPILED | SORTLEN |
+------------------------+--------------------+------+------------+-------------+---------+
| utf8mb4_general_ci     | utf8mb4            |   45 | Yes        | Yes         |       1 |
| utf8mb4_bin            | utf8mb4            |   46 |            | Yes         |       1 |
| utf8mb4_unicode_ci     | utf8mb4            |  224 |            | Yes         |       1 |
| utf8mb4_icelandic_ci   | utf8mb4            |  225 |            | Yes         |       1 |
| utf8mb4_latvian_ci     | utf8mb4            |  226 |            | Yes         |       1 |
| utf8mb4_romanian_ci    | utf8mb4            |  227 |            | Yes         |       1 |
| utf8mb4_slovenian_ci   | utf8mb4            |  228 |            | Yes         |       1 |
| utf8mb4_polish_ci      | utf8mb4            |  229 |            | Yes         |       1 |
| utf8mb4_estonian_ci    | utf8mb4            |  230 |            | Yes         |       1 |
| utf8mb4_spanish_ci     | utf8mb4            |  231 |            | Yes         |       1 |
| utf8mb4_swedish_ci     | utf8mb4            |  232 |            | Yes         |       1 |
| utf8mb4_turkish_ci     | utf8mb4            |  233 |            | Yes         |       1 |
| utf8mb4_czech_ci       | utf8mb4            |  234 |            | Yes         |       1 |
| utf8mb4_danish_ci      | utf8mb4            |  235 |            | Yes         |       1 |
| utf8mb4_lithuanian_ci  | utf8mb4            |  236 |            | Yes         |       1 |
| utf8mb4_slovak_ci      | utf8mb4            |  237 |            | Yes         |       1 |
| utf8mb4_spanish2_ci    | utf8mb4            |  238 |            | Yes         |       1 |
| utf8mb4_roman_ci       | utf8mb4            |  239 |            | Yes         |       1 |
| utf8mb4_persian_ci     | utf8mb4            |  240 |            | Yes         |       1 |
| utf8mb4_esperanto_ci   | utf8mb4            |  241 |            | Yes         |       1 |
| utf8mb4_hungarian_ci   | utf8mb4            |  242 |            | Yes         |       1 |
| utf8mb4_sinhala_ci     | utf8mb4            |  243 |            | Yes         |       1 |
| utf8mb4_german2_ci     | utf8mb4            |  244 |            | Yes         |       1 |
| utf8mb4_croatian_ci    | utf8mb4            |  245 |            | Yes         |       1 |
| utf8mb4_unicode_520_ci | utf8mb4            |  246 |            | Yes         |       1 |
| utf8mb4_vietnamese_ci  | utf8mb4            |  247 |            | Yes         |       1 |
+------------------------+--------------------+------+------------+-------------+---------+
26 rows in set (0.00 sec)

COLLATION_CHARACTER_SET_APPLICABILITY 表

COLLATION_CHARACTER_SET_APPLICABILITY 表将排序规则映射至适用的字符集名称。和 COLLATIONS 表一样，包含此表也是为了兼容 MySQL。

SELECT * FROM collation_character_set_applicability WHERE character_set_name='utf8mb4';

+------------------------+--------------------+
| COLLATION_NAME         | CHARACTER_SET_NAME |
+------------------------+--------------------+
| utf8mb4_general_ci     | utf8mb4            |
| utf8mb4_bin            | utf8mb4            |
| utf8mb4_unicode_ci     | utf8mb4            |
| utf8mb4_icelandic_ci   | utf8mb4            |
| utf8mb4_latvian_ci     | utf8mb4            |
| utf8mb4_romanian_ci    | utf8mb4            |
| utf8mb4_slovenian_ci   | utf8mb4            |
| utf8mb4_polish_ci      | utf8mb4            |
| utf8mb4_estonian_ci    | utf8mb4            |
| utf8mb4_spanish_ci     | utf8mb4            |
| utf8mb4_swedish_ci     | utf8mb4            |
| utf8mb4_turkish_ci     | utf8mb4            |
| utf8mb4_czech_ci       | utf8mb4            |
| utf8mb4_danish_ci      | utf8mb4            |
| utf8mb4_lithuanian_ci  | utf8mb4            |
| utf8mb4_slovak_ci      | utf8mb4            |
| utf8mb4_spanish2_ci    | utf8mb4            |
| utf8mb4_roman_ci       | utf8mb4            |
| utf8mb4_persian_ci     | utf8mb4            |
| utf8mb4_esperanto_ci   | utf8mb4            |
| utf8mb4_hungarian_ci   | utf8mb4            |
| utf8mb4_sinhala_ci     | utf8mb4            |
| utf8mb4_german2_ci     | utf8mb4            |
| utf8mb4_croatian_ci    | utf8mb4            |
| utf8mb4_unicode_520_ci | utf8mb4            |
| utf8mb4_vietnamese_ci  | utf8mb4            |
+------------------------+--------------------+
26 rows in set (0.00 sec)

COLUMNS 表

COLUMNS 表提供了表的所有列的信息。

CREATE TABLE test.t1 (a int);

1 row in set (0.01 sec)

SELECT * FROM information_schema.columns WHERE table_schema='test' AND TABLE_NAME='t1';

*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test
              TABLE_NAME: t1
             COLUMN_NAME: a
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 11
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
   GENERATION_EXPRESSION:
1 row in set (0.01 sec)

对应的 SHOW 语句如下：

SHOW COLUMNS FROM t1 FROM test;

+-------+---------+------+------+---------+-------+
| Field | Type    | Null | Key  | Default | Extra |
+-------+---------+------+------+---------+-------+
| a     | int(11) | YES  |      | NULL    |       |
+-------+---------+------+------+---------+-------+
1 row in set (0.00 sec)

ENGINES 表

ENGINES 表提供了关于存储引擎的信息。从和 MySQL 兼容性上考虑，TiDB 会一直将 InnoDB 描述为唯一支持的引擎。

SELECT * FROM engines;

*************************** 1. row ***************************
      ENGINE: InnoDB
     SUPPORT: DEFAULT
     COMMENT: Supports transactions, row-level locking, and foreign keys
TRANSACTIONS: YES
          XA: YES
  SAVEPOINTS: YES
1 row in set (0.00 sec)

KEY_COLUMN_USAGE 表

KEY_COLUMN_USAGE 表描述了列的键约束，比如主键约束。

SELECT * FROM key_column_usage WHERE table_schema='mysql' and table_name='user';

*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: mysql
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: mysql
                   TABLE_NAME: user
                  COLUMN_NAME: Host
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 2. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: mysql
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: mysql
                   TABLE_NAME: user
                  COLUMN_NAME: User
             ORDINAL_POSITION: 2
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
2 rows in set (0.00 sec)

PROCESSLIST 表

PROCESSLIST 和 show processlist 的功能一样，都是查看当前正在处理的请求。

PROCESSLIST 表会比 show processlist 多一个 MEM 列，MEM 是指正在处理的请求已使用的内存，单位是 byte。

+----+------+------+--------------------+---------+------+-------+---------------------------+-----+
| ID | USER | HOST | DB                 | COMMAND | TIME | STATE | INFO                      | MEM |
+----+------+------+--------------------+---------+------+-------+---------------------------+-----+
| 1  | root | ::1  | INFORMATION_SCHEMA | Query   | 0    | 2     | select * from PROCESSLIST | 0   |
+----+------+------+--------------------+---------+------+-------+---------------------------+-----+

SCHEMATA 表

SCHEMATA 表提供了关于数据库的信息。表中的数据与 SHOW DATABASES 语句的执行结果等价。

SELECT * FROM schemata;

+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | INFORMATION_SCHEMA | utf8mb4                    | utf8mb4_bin            | NULL     |
| def          | mynewdb            | utf8mb4                    | utf8mb4_bin            | NULL     |
| def          | mysql              | utf8mb4                    | utf8mb4_bin            | NULL     |
| def          | PERFORMANCE_SCHEMA | utf8mb4                    | utf8mb4_bin            | NULL     |
| def          | test               | utf8mb4                    | utf8mb4_bin            | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.00 sec)

CLUSTER_PROCESSLIST

CLUSTER_PROCESSLIST 是 PROCESSLIST 对应的集群系统表，用于查询集群中所有 TiDB 节点的 PROCESSLIST 信息。表结构上比 PROCESSLIST 多一列 INSTANCE，表示该行数据来自的 TiDB 节点地址。

SELECT * FROM information_schema.cluster_processlist;

+-----------------+-----+------+----------+------+---------+------+------------+------------------------------------------------------+-----+----------------------------------------+
| INSTANCE        | ID  | USER | HOST     | DB   | COMMAND | TIME | STATE      | INFO                                                 | MEM | TxnStart                               |
+-----------------+-----+------+----------+------+---------+------+------------+------------------------------------------------------+-----+----------------------------------------+
| 10.0.1.22:10080 | 150 | u1   | 10.0.1.1 | test | Query   | 0    | autocommit | select count(*) from usertable                       | 372 | 05-28 03:54:21.230(416976223923077223) |
| 10.0.1.22:10080 | 138 | root | 10.0.1.1 | test | Query   | 0    | autocommit | SELECT * FROM information_schema.cluster_processlist | 0   | 05-28 03:54:21.230(416976223923077220) |
| 10.0.1.22:10080 | 151 | u1   | 10.0.1.1 | test | Query   | 0    | autocommit | select count(*) from usertable                       | 372 | 05-28 03:54:21.230(416976223923077224) |
| 10.0.1.21:10080 | 15  | u2   | 10.0.1.1 | test | Query   | 0    | autocommit | select max(field0) from usertable                    | 496 | 05-28 03:54:21.230(416976223923077222) |
| 10.0.1.21:10080 | 14  | u2   | 10.0.1.1 | test | Query   | 0    | autocommit | select max(field0) from usertable                    | 496 | 05-28 03:54:21.230(416976223923077225) |
+-----------------+-----+------+----------+------+---------+------+------------+------------------------------------------------------+-----+----------------------------------------+

SESSION_VARIABLES 表

SESSION_VARIABLES 表提供了关于 session 变量的信息。表中的数据跟 SHOW SESSION VARIABLES 语句执行结果类似。

SELECT * FROM session_variables LIMIT 10;

+----------------------------------+----------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE       |
+----------------------------------+----------------------+
| max_write_lock_count             | 18446744073709551615 |
| server_id_bits                   | 32                   |
| net_read_timeout                 | 30                   |
| innodb_online_alter_log_max_size | 134217728            |
| innodb_optimize_fulltext_only    | OFF                  |
| max_join_size                    | 18446744073709551615 |
| innodb_read_io_threads           | 4                    |
| session_track_gtids              | OFF                  |
| have_ssl                         | DISABLED             |
| max_binlog_cache_size            | 18446744073709547520 |
+----------------------------------+----------------------+
10 rows in set (0.00 sec)

SLOW_QUERY 表

SLOW_QUERY 表中提供了当前节点的慢查询相关的信息，其内容通过解析当前节点的 TiDB 慢查询日志而来，列名和慢日志中的字段名是一一对应。关于如何使用该表调查和改善慢查询请参考慢查询日志文档。

desc information_schema.slow_query;

+---------------------------+---------------------+------+-----+---------+-------+
| Field                     | Type                | Null | Key | Default | Extra |
+---------------------------+---------------------+------+-----+---------+-------+
| Time                      | timestamp unsigned  | YES  |     | <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>  |       |
| Query_time                | double unsigned     | YES  |     | <null>  |       |
| Parse_time                | double unsigned     | YES  |     | <null>  |       |
| Compile_time              | double unsigned     | YES  |     | <null>  |       |
| Prewrite_time             | double unsigned     | YES  |     | <null>  |       |
| Wait_prewrite_binlog_time | double unsigned     | YES  |     | <null>  |       |
| Commit_time               | double unsigned     | YES  |     | <null>  |       |
| Get_commit_ts_time        | double unsigned     | YES  |     | <null>  |       |
| Commit_backoff_time       | double unsigned     | YES  |     | <null>  |       |
| Backoff_types             | varchar(64)         | YES  |     | <null>  |       |
| Resolve_lock_time         | double unsigned     | YES  |     | <null>  |       |
| Local_latch_wait_time     | double unsigned     | YES  |     | <null>  |       |
| Write_keys                | bigint(22) unsigned | YES  |     | <null>  |       |
| Write_size                | bigint(22) unsigned | YES  |     | <null>  |       |
| Prewrite_region           | bigint(22) unsigned | YES  |     | <null>  |       |
| Txn_retry                 | bigint(22) unsigned | YES  |     | <null>  |       |
| Process_time              | double unsigned     | YES  |     | <null>  |       |
| Wait_time                 | double unsigned     | YES  |     | <null>  |       |
| Backoff_time              | double unsigned     | YES  |     | <null>  |       |
| LockKeys_time             | double unsigned     | YES  |     | <null>  |       |
| Request_count             | bigint(20) unsigned | YES  |     | <null>  |       |
| Total_keys                | bigint(20) unsigned | YES  |     | <null>  |       |
| Process_keys              | bigint(20) unsigned | YES  |     | <null>  |       |
| DB                        | varchar(64)         | YES  |     | <null>  |       |
| Index_names               | varchar(100)        | YES  |     | <null>  |       |
| Is_internal               | tinyint(1) unsigned | YES  |     | <null>  |       |
| Digest                    | varchar(64)         | YES  |     | <null>  |       |
| Stats                     | varchar(512)        | YES  |     | <null>  |       |
| Cop_proc_avg              | double unsigned     | YES  |     | <null>  |       |
| Cop_proc_p90              | double unsigned     | YES  |     | <null>  |       |
| Cop_proc_max              | double unsigned     | YES  |     | <null>  |       |
| Cop_proc_addr             | varchar(64)         | YES  |     | <null>  |       |
| Cop_wait_avg              | double unsigned     | YES  |     | <null>  |       |
| Cop_wait_p90              | double unsigned     | YES  |     | <null>  |       |
| Cop_wait_max              | double unsigned     | YES  |     | <null>  |       |
| Cop_wait_addr             | varchar(64)         | YES  |     | <null>  |       |
| Mem_max                   | bigint(20) unsigned | YES  |     | <null>  |       |
| Succ                      | tinyint(1) unsigned | YES  |     | <null>  |       |
| Plan_from_cache           | tinyint(1)          | YES  |     | <null>  |       |
| Plan                      | longblob unsigned   | YES  |     | <null>  |       |
| Plan_digest               | varchar(128)        | YES  |     | <null>  |       |
| Prev_stmt                 | longblob unsigned   | YES  |     | <null>  |       |
| Query                     | longblob unsigned   | YES  |     | <null>  |       |
+---------------------------+---------------------+------+-----+---------+-------+

CLUSTER_SLOW_QUERY 表

CLUSTER_SLOW_QUERY 表中提供了集群所有节点的慢查询相关的信息，其内容通过解析 TiDB 慢查询日志而来，该表使用上和 SLOW_QUERY 一样，表结构上比 SLOW_QUERY 多一列 INSTANCE，表示该行慢查询信息来自的 TiDB 节点地址。关于如何使用该表调查和改善慢查询请参考慢查询日志文档。

desc information_schema.cluster_slow_query;

+---------------------------+---------------------+------+-----+---------+-------+
| Field                     | Type                | Null | Key | Default | Extra |
+---------------------------+---------------------+------+-----+---------+-------+
| INSTANCE                  | varchar(64)         | YES  |     | <null>  |       |
| Time                      | timestamp unsigned  | YES  |     | <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>  |       |
| Query_time                | double unsigned     | YES  |     | <null>  |       |
| Parse_time                | double unsigned     | YES  |     | <null>  |       |
| Compile_time              | double unsigned     | YES  |     | <null>  |       |
| Prewrite_time             | double unsigned     | YES  |     | <null>  |       |
| Wait_prewrite_binlog_time | double unsigned     | YES  |     | <null>  |       |
| Commit_time               | double unsigned     | YES  |     | <null>  |       |
| Get_commit_ts_time        | double unsigned     | YES  |     | <null>  |       |
| Commit_backoff_time       | double unsigned     | YES  |     | <null>  |       |
| Backoff_types             | varchar(64)         | YES  |     | <null>  |       |
| Resolve_lock_time         | double unsigned     | YES  |     | <null>  |       |
| Local_latch_wait_time     | double unsigned     | YES  |     | <null>  |       |
| Write_keys                | bigint(22) unsigned | YES  |     | <null>  |       |
| Write_size                | bigint(22) unsigned | YES  |     | <null>  |       |
| Prewrite_region           | bigint(22) unsigned | YES  |     | <null>  |       |
| Txn_retry                 | bigint(22) unsigned | YES  |     | <null>  |       |
| Process_time              | double unsigned     | YES  |     | <null>  |       |
| Wait_time                 | double unsigned     | YES  |     | <null>  |       |
| Backoff_time              | double unsigned     | YES  |     | <null>  |       |
| LockKeys_time             | double unsigned     | YES  |     | <null>  |       |
| Request_count             | bigint(20) unsigned | YES  |     | <null>  |       |
| Total_keys                | bigint(20) unsigned | YES  |     | <null>  |       |
| Process_keys              | bigint(20) unsigned | YES  |     | <null>  |       |
| DB                        | varchar(64)         | YES  |     | <null>  |       |
| Index_names               | varchar(100)        | YES  |     | <null>  |       |
| Is_internal               | tinyint(1) unsigned | YES  |     | <null>  |       |
| Digest                    | varchar(64)         | YES  |     | <null>  |       |
| Stats                     | varchar(512)        | YES  |     | <null>  |       |
| Cop_proc_avg              | double unsigned     | YES  |     | <null>  |       |
| Cop_proc_p90              | double unsigned     | YES  |     | <null>  |       |
| Cop_proc_max              | double unsigned     | YES  |     | <null>  |       |
| Cop_proc_addr             | varchar(64)         | YES  |     | <null>  |       |
| Cop_wait_avg              | double unsigned     | YES  |     | <null>  |       |
| Cop_wait_p90              | double unsigned     | YES  |     | <null>  |       |
| Cop_wait_max              | double unsigned     | YES  |     | <null>  |       |
| Cop_wait_addr             | varchar(64)         | YES  |     | <null>  |       |
| Mem_max                   | bigint(20) unsigned | YES  |     | <null>  |       |
| Succ                      | tinyint(1) unsigned | YES  |     | <null>  |       |
| Plan_from_cache           | tinyint(1)          | YES  |     | <null>  |       |
| Plan                      | longblob unsigned   | YES  |     | <null>  |       |
| Plan_digest               | varchar(128)        | YES  |     | <null>  |       |
| Prev_stmt                 | longblob unsigned   | YES  |     | <null>  |       |
| Query                     | longblob unsigned   | YES  |     | <null>  |       |
+---------------------------+---------------------+------+-----+---------+-------+

查询集群系统表时，TiDB 也会将相关计算下推给其他节点执行，而不是把所有节点的数据都取回来，可以查看执行计划如下：

desc select count(*) from information_schema.cluster_slow_query where user = 'u1';

+--------------------------+----------+-----------+--------------------------+------------------------------------------------------+
| id                       | estRows  | task      | access object            | operator info                                        |
+--------------------------+----------+-----------+--------------------------+------------------------------------------------------+
| StreamAgg_20             | 1.00     | root      |                          | funcs:count(Column#53)->Column#51                    |
| └─TableReader_21         | 1.00     | root      |                          | data:StreamAgg_9                                     |
|   └─StreamAgg_9          | 1.00     | cop[tidb] |                          | funcs:count(1)->Column#53                            |
|     └─Selection_19       | 10.00    | cop[tidb] |                          | eq(information_schema.cluster_slow_query.user, "u1") |
|       └─TableFullScan_18 | 10000.00 | cop[tidb] | table:CLUSTER_SLOW_QUERY | keep order:false, stats:pseudo                       |
+--------------------------+----------+-----------+--------------------------+------------------------------------------------------+

上面执行计划表示，会将 user = u1 条件下推给其他的（cop） TiDB 节点执行，也会把聚合算子下推（即图中的 StreamAgg 算子）。

目前由于没有对系统表收集统计信息，所以有时会导致某些聚合算子不能下推，导致执行较慢，用户可以通过手动指定聚合下推的 SQL HINT 来将聚合算子下推，示例如下：

select /*+ AGG_TO_COP() */ count(*) from information_schema.cluster_slow_query group by user;

STATISTICS 表

STATISTICS 表提供了关于表索引的信息。

desc statistics;

+---------------+---------------+------+------+---------+-------+
| Field         | Type          | Null | Key  | Default | Extra |
+---------------+---------------+------+------+---------+-------+
| TABLE_CATALOG | varchar(512)  | YES  |      | NULL    |       |
| TABLE_SCHEMA  | varchar(64)   | YES  |      | NULL    |       |
| TABLE_NAME    | varchar(64)   | YES  |      | NULL    |       |
| NON_UNIQUE    | varchar(1)    | YES  |      | NULL    |       |
| INDEX_SCHEMA  | varchar(64)   | YES  |      | NULL    |       |
| INDEX_NAME    | varchar(64)   | YES  |      | NULL    |       |
| SEQ_IN_INDEX  | bigint(2)     | YES  |      | NULL    |       | 
| COLUMN_NAME   | varchar(21)   | YES  |      | NULL    |       |
| COLLATION     | varchar(1)    | YES  |      | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |      | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |      | NULL    |       |
| PACKED        | varchar(10)   | YES  |      | NULL    |       |
| NULLABLE      | varchar(3)    | YES  |      | NULL    |       |
| INDEX_TYPE    | varchar(16)   | YES  |      | NULL    |       | 
| COMMENT       | varchar(16)   | YES  |      | NULL    |       |
| INDEX_COMMENT | varchar(1024) | YES  |      | NULL    |       |
| IS_VISIBLE    | varchar(3)    | YES  |      | NULL    |       |
| Expression    | varchar(64)   | YES  |      | NULL    |       | 
+---------------+---------------+------+------+---------+-------+

下列语句是等价的：

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_name = 'tbl_name'
  AND table_schema = 'db_name'

SHOW INDEX
  FROM tbl_name
  FROM db_name

TABLES 表

TABLES 表提供了数据库里面关于表的信息。

SELECT * FROM tables WHERE table_schema='mysql' AND table_name='user';

*************************** 1. row ***************************
            TABLE_CATALOG: def
             TABLE_SCHEMA: mysql
               TABLE_NAME: user
               TABLE_TYPE: BASE TABLE
                   ENGINE: InnoDB
                  VERSION: 10
               ROW_FORMAT: Compact
               TABLE_ROWS: 0
           AVG_ROW_LENGTH: 0
              DATA_LENGTH: 0
          MAX_DATA_LENGTH: 0
             INDEX_LENGTH: 0
                DATA_FREE: 0
           AUTO_INCREMENT: 0
              CREATE_TIME: 2019-03-29 09:17:27
              UPDATE_TIME: NULL
               CHECK_TIME: NULL
          TABLE_COLLATION: utf8mb4_bin
                 CHECKSUM: NULL
           CREATE_OPTIONS:
            TABLE_COMMENT:
            TIDB_TABLE_ID: 5
TIDB_ROW_ID_SHARDING_INFO: NULL
1 row in set (0.00 sec)

以下操作是等价的：

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLES
  FROM db_name
  [LIKE 'wild']

表中的信息大部分定义自 MySQL，此外有两列是 TiDB 新增的：

TIDB_TABLE_ID：标识表的内部 ID，该 ID 在一个 TiDB 集群内部唯一。
TIDB_ROW_ID_SHARDING_INFO：标识表的 Sharding 类型，可能的值为：
- "NOT_SHARDED"：表未被 Shard。
- "NOT_SHARDED(PK_IS_HANDLE)"：一个定义了整型主键的表未被 Shard。
- "PK_AUTO_RANDOM_BITS={bit_number}"：一个定义了整型主键的表由于定义了 AUTO_RANDOM 而被 Shard。
- "SHARD_BITS={bit_number}"：表使用 SHARD_ROW_ID_BITS={bit_number} 进行了 Shard。
- NULL：表属于系统表或 View，无法被 Shard。

TABLE_CONSTRAINTS 表

TABLE_CONSTRAINTS 表记录了表的约束信息。

SELECT * FROM table_constraints WHERE constraint_type='UNIQUE';

*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mysql
   CONSTRAINT_NAME: name
      TABLE_SCHEMA: mysql
        TABLE_NAME: help_topic
   CONSTRAINT_TYPE: UNIQUE
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mysql
   CONSTRAINT_NAME: tbl
      TABLE_SCHEMA: mysql
        TABLE_NAME: stats_meta
   CONSTRAINT_TYPE: UNIQUE
*************************** 3. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mysql
   CONSTRAINT_NAME: tbl
      TABLE_SCHEMA: mysql
        TABLE_NAME: stats_histograms
   CONSTRAINT_TYPE: UNIQUE
*************************** 4. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mysql
   CONSTRAINT_NAME: tbl
      TABLE_SCHEMA: mysql
        TABLE_NAME: stats_buckets
   CONSTRAINT_TYPE: UNIQUE
*************************** 5. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mysql
   CONSTRAINT_NAME: delete_range_index
      TABLE_SCHEMA: mysql
        TABLE_NAME: gc_delete_range
   CONSTRAINT_TYPE: UNIQUE
*************************** 6. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: mysql
   CONSTRAINT_NAME: delete_range_done_index
      TABLE_SCHEMA: mysql
        TABLE_NAME: gc_delete_range_done
   CONSTRAINT_TYPE: UNIQUE

其中：

CONSTRAINT_TYPE 的取值可以是 UNIQUE，PRIMARY KEY，或者 FOREIGN KEY。
UNIQUE 和 PRIMARY KEY 信息与 SHOW INDEX 语句的执行结果类似。

TIDB_HOT_REGIONS 表

TIDB_HOT_REGIONS 表提供了关于热点 REGION 的相关信息。

desc TIDB_HOT_REGIONS;

+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| TABLE_ID       | bigint(21) unsigned | YES  |     | <null>  |       |
| INDEX_ID       | bigint(21) unsigned | YES  |     | <null>  |       |
| DB_NAME        | varchar(64)         | YES  |     | <null>  |       |
| TABLE_NAME     | varchar(64)         | YES  |     | <null>  |       |
| INDEX_NAME     | varchar(64)         | YES  |     | <null>  |       |
| TYPE           | varchar(64)         | YES  |     | <null>  |       |
| MAX_HOT_DEGREE | bigint(21) unsigned | YES  |     | <null>  |       |
| REGION_COUNT   | bigint(21) unsigned | YES  |     | <null>  |       |
| FLOW_BYTES     | bigint(21) unsigned | YES  |     | <null>  |       |
+----------------+---------------------+------+-----+---------+-------+

TIDB_INDEXES 表

TIDB_INDEXES 记录了所有表中的 INDEX 信息。

desc TIDB_INDEXES;

+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| TABLE_SCHEMA  | varchar(64)         | YES  |     | <null>  |       |
| TABLE_NAME    | varchar(64)         | YES  |     | <null>  |       |
| NON_UNIQUE    | bigint(21) unsigned | YES  |     | <null>  |       |
| KEY_NAME      | varchar(64)         | YES  |     | <null>  |       |
| SEQ_IN_INDEX  | bigint(21) unsigned | YES  |     | <null>  |       |
| COLUMN_NAME   | varchar(64)         | YES  |     | <null>  |       |
| SUB_PART      | bigint(21) unsigned | YES  |     | <null>  |       |
| INDEX_COMMENT | varchar(2048)       | YES  |     | <null>  |       |
| INDEX_ID      | bigint(21) unsigned | YES  |     | <null>  |       |
+---------------+---------------------+------+-----+---------+-------+

TIKV_REGION_PEERS 表

TIKV_REGION_PEERS 表提供了所有 REGION 的 peer 信息。

desc TIKV_REGION_PEERS;

+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| REGION_ID    | bigint(21) unsigned | YES  |     | <null>  |       |
| PEER_ID      | bigint(21) unsigned | YES  |     | <null>  |       |
| STORE_ID     | bigint(21) unsigned | YES  |     | <null>  |       |
| IS_LEARNER   | tinyint(1) unsigned | YES  |     | <null>  |       |
| IS_LEADER    | tinyint(1) unsigned | YES  |     | <null>  |       |
| STATUS       | varchar(10)         | YES  |     | <null>  |       |
| DOWN_SECONDS | bigint(21) unsigned | YES  |     | <null>  |       |
+--------------+---------------------+------+-----+---------+-------+

TIKV_REGION_STATUS 表

TIKV_REGION_STATUS 表提供了所有 REGION 的状态信息。

desc TIKV_REGION_STATUS;

+------------------+---------------------+------+-----+---------+-------+
| Field            | Type                | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| REGION_ID        | bigint(21) unsigned | YES  |     | <null>  |       |
| START_KEY        | text                | YES  |     | <null>  |       |
| END_KEY          | text                | YES  |     | <null>  |       |
| EPOCH_CONF_VER   | bigint(21) unsigned | YES  |     | <null>  |       |
| EPOCH_VERSION    | bigint(21) unsigned | YES  |     | <null>  |       |
| WRITTEN_BYTES    | bigint(21) unsigned | YES  |     | <null>  |       |
| READ_BYTES       | bigint(21) unsigned | YES  |     | <null>  |       |
| APPROXIMATE_SIZE | bigint(21) unsigned | YES  |     | <null>  |       |
| APPROXIMATE_KEYS | bigint(21) unsigned | YES  |     | <null>  |       |
+------------------+---------------------+------+-----+---------+-------+

TIKV_STORE_STATUS 表

TIKV_STORE_STATUS 表提供了所有 TiKV Store 的状态信息。

desc TIKV_STORE_STATUS;

+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| STORE_ID          | bigint(21) unsigned | YES  |     | <null>  |       |
| ADDRESS           | varchar(64)         | YES  |     | <null>  |       |
| STORE_STATE       | bigint(21) unsigned | YES  |     | <null>  |       |
| STORE_STATE_NAME  | varchar(64)         | YES  |     | <null>  |       |
| LABEL             | json unsigned       | YES  |     | <null>  |       |
| VERSION           | varchar(64)         | YES  |     | <null>  |       |
| CAPACITY          | varchar(64)         | YES  |     | <null>  |       |
| AVAILABLE         | varchar(64)         | YES  |     | <null>  |       |
| LEADER_COUNT      | bigint(21) unsigned | YES  |     | <null>  |       |
| LEADER_WEIGHT     | bigint(21) unsigned | YES  |     | <null>  |       |
| LEADER_SCORE      | bigint(21) unsigned | YES  |     | <null>  |       |
| LEADER_SIZE       | bigint(21) unsigned | YES  |     | <null>  |       |
| REGION_COUNT      | bigint(21) unsigned | YES  |     | <null>  |       |
| REGION_WEIGHT     | bigint(21) unsigned | YES  |     | <null>  |       |
| REGION_SCORE      | bigint(21) unsigned | YES  |     | <null>  |       |
| REGION_SIZE       | bigint(21) unsigned | YES  |     | <null>  |       |
| START_TS          | datetime unsigned   | YES  |     | <null>  |       |
| LAST_HEARTBEAT_TS | datetime unsigned   | YES  |     | <null>  |       |
| UPTIME            | varchar(64)         | YES  |     | <null>  |       |
+-------------------+---------------------+------+-----+---------+-------+

USER_PRIVILEGES 表

USER_PRIVILEGES 表提供了关于全局权限的信息。该表的数据根据 mysql.user 系统表生成。

desc USER_PRIVILEGES;

+----------------|--------------|------|------|---------|-------+
| Field          | Type         | Null | Key  | Default | Extra |
+----------------|--------------|------|------|---------|-------+
| GRANTEE        | varchar(81)  | YES  |      | NULL    |       |
| TABLE_CATALOG  | varchar(512) | YES  |      | NULL    |       |
| PRIVILEGE_TYPE | varchar(64)  | YES  |      | NULL    |       |
| IS_GRANTABLE   | varchar(3)   | YES  |      | NULL    |       |
+----------------|--------------|------|------|---------|-------+
4 rows in set (0.00 sec)

VIEWS 表

VIEWS 表提供了关于 SQL 视图的信息。

create view test.v1 as select 1;

Query OK, 0 rows affected (0.00 sec)

select * from views;

*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: v1
     VIEW_DEFINITION: select 1
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: NO
             DEFINER: root@127.0.0.1
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)

SQL 诊断相关的表

information_schema.cluster_info
information_schema.cluster_config
information_schema.cluster_hardware
information_schema.cluster_load
information_schema.cluster_systeminfo
information_schema.cluster_log
information_schema.metrics_tables
information_schema.metrics_summary
information_schema.metrics_summary_by_label
information_schema.inspection_result
information_schema.inspection_summary

不支持的 Information Schema 表

TiDB 包含以下 INFORMATION_SCHEMA 表，但仅会返回空行：

COLUMN_PRIVILEGES
EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PLUGINS
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMA_PRIVILEGES
SESSION_STATUS
TABLESPACES
TABLE_PRIVILEGES
TRIGGERS

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

system-table-information-schema.md

system-table-information-schema.md

Information Schema

ANALYZE_STATUS 表

CHARACTER_SETS 表

COLLATIONS 表

COLLATION_CHARACTER_SET_APPLICABILITY 表

COLUMNS 表

ENGINES 表

KEY_COLUMN_USAGE 表

PROCESSLIST 表

SCHEMATA 表

CLUSTER_PROCESSLIST

SESSION_VARIABLES 表

SLOW_QUERY 表

CLUSTER_SLOW_QUERY 表

STATISTICS 表

TABLES 表

TABLE_CONSTRAINTS 表

TIDB_HOT_REGIONS 表

TIDB_INDEXES 表

TIKV_REGION_PEERS 表

TIKV_REGION_STATUS 表

TIKV_STORE_STATUS 表

USER_PRIVILEGES 表

VIEWS 表

SQL 诊断相关的表

不支持的 Information Schema 表

Files

system-table-information-schema.md

Latest commit

History

system-table-information-schema.md

File metadata and controls

Information Schema

ANALYZE_STATUS 表

CHARACTER_SETS 表

COLLATIONS 表

COLLATION_CHARACTER_SET_APPLICABILITY 表

COLUMNS 表

ENGINES 表

KEY_COLUMN_USAGE 表

PROCESSLIST 表

SCHEMATA 表

CLUSTER_PROCESSLIST

SESSION_VARIABLES 表

SLOW_QUERY 表

CLUSTER_SLOW_QUERY 表

STATISTICS 表

TABLES 表

TABLE_CONSTRAINTS 表

TIDB_HOT_REGIONS 表

TIDB_INDEXES 表

TIKV_REGION_PEERS 表

TIKV_REGION_STATUS 表

TIKV_STORE_STATUS 表

USER_PRIVILEGES 表

VIEWS 表

SQL 诊断相关的表

不支持的 Information Schema 表