我的书签
添加书签
移除书签- 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 表
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: defTABLE_SCHEMA: testTABLE_NAME: t1COLUMN_NAME: aORDINAL_POSITION: 1COLUMN_DEFAULT: NULLIS_NULLABLE: YESDATA_TYPE: intCHARACTER_MAXIMUM_LENGTH: NULLCHARACTER_OCTET_LENGTH: NULLNUMERIC_PRECISION: 11NUMERIC_SCALE: 0DATETIME_PRECISION: NULLCHARACTER_SET_NAME: NULLCOLLATION_NAME: NULLCOLUMN_TYPE: int(11)COLUMN_KEY:EXTRA:PRIVILEGES: select,insert,update,referencesCOLUMN_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: InnoDBSUPPORT: DEFAULTCOMMENT: Supports transactions, row-level locking, and foreign keysTRANSACTIONS: YESXA: YESSAVEPOINTS: YES1 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: defCONSTRAINT_SCHEMA: mysqlCONSTRAINT_NAME: PRIMARYTABLE_CATALOG: defTABLE_SCHEMA: mysqlTABLE_NAME: userCOLUMN_NAME: HostORDINAL_POSITION: 1POSITION_IN_UNIQUE_CONSTRAINT: NULLREFERENCED_TABLE_SCHEMA: NULLREFERENCED_TABLE_NAME: NULLREFERENCED_COLUMN_NAME: NULL*************************** 2. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: mysqlCONSTRAINT_NAME: PRIMARYTABLE_CATALOG: defTABLE_SCHEMA: mysqlTABLE_NAME: userCOLUMN_NAME: UserORDINAL_POSITION: 2POSITION_IN_UNIQUE_CONSTRAINT: NULLREFERENCED_TABLE_SCHEMA: NULLREFERENCED_TABLE_NAME: NULLREFERENCED_COLUMN_NAME: NULL2 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) UNSIGNED | YES | | NULL | || COLUMN_NAME | varchar(21) | YES | | NULL | || COLLATION | varchar(1) | YES | | NULL | || CARDINALITY | bigint(21) UNSIGNED | YES | | NULL | || SUB_PART | bigint(3) UNSIGNED | 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 | |+---------------|---------------------|------|------|---------|-------+
下列语句是等价的：
SELECT * FROM INFORMATION_SCHEMA.STATISTICSWHERE table_name = 'tbl_name'AND table_schema = 'db_name'
SHOW INDEXFROM tbl_nameFROM db_name
TABLES 表
TABLES 表提供了数据库里面关于表的信息。
SELECT * FROM tables WHERE table_schema='mysql' AND table_name='user';
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: mysqlTABLE_NAME: userTABLE_TYPE: BASE TABLEENGINE: InnoDBVERSION: 10ROW_FORMAT: CompactTABLE_ROWS: 0AVG_ROW_LENGTH: 0DATA_LENGTH: 0MAX_DATA_LENGTH: 0INDEX_LENGTH: 0DATA_FREE: 0AUTO_INCREMENT: 0CREATE_TIME: 2019-03-29 09:17:27UPDATE_TIME: NULLCHECK_TIME: NULLTABLE_COLLATION: utf8mb4_binCHECKSUM: NULLCREATE_OPTIONS:TABLE_COMMENT:TIDB_TABLE_ID: 5TIDB_ROW_ID_SHARDING_INFO: NULL1 row in set (0.00 sec)
以下操作是等价的：
SELECT table_name FROM INFORMATION_SCHEMA.TABLESWHERE table_schema = 'db_name'[AND table_name LIKE 'wild']
SHOW TABLESFROM 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: defCONSTRAINT_SCHEMA: mysqlCONSTRAINT_NAME: nameTABLE_SCHEMA: mysqlTABLE_NAME: help_topicCONSTRAINT_TYPE: UNIQUE*************************** 2. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: mysqlCONSTRAINT_NAME: tblTABLE_SCHEMA: mysqlTABLE_NAME: stats_metaCONSTRAINT_TYPE: UNIQUE*************************** 3. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: mysqlCONSTRAINT_NAME: tblTABLE_SCHEMA: mysqlTABLE_NAME: stats_histogramsCONSTRAINT_TYPE: UNIQUE*************************** 4. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: mysqlCONSTRAINT_NAME: tblTABLE_SCHEMA: mysqlTABLE_NAME: stats_bucketsCONSTRAINT_TYPE: UNIQUE*************************** 5. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: mysqlCONSTRAINT_NAME: delete_range_indexTABLE_SCHEMA: mysqlTABLE_NAME: gc_delete_rangeCONSTRAINT_TYPE: UNIQUE*************************** 6. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: mysqlCONSTRAINT_NAME: delete_range_done_indexTABLE_SCHEMA: mysqlTABLE_NAME: gc_delete_range_doneCONSTRAINT_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: defTABLE_SCHEMA: testTABLE_NAME: v1VIEW_DEFINITION: select 1CHECK_OPTION: CASCADEDIS_UPDATABLE: NODEFINER: root@127.0.0.1SECURITY_TYPE: DEFINERCHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ci1 row in set (0.00 sec)
SQL 诊断相关的表
information_schema.cluster_infoinformation_schema.cluster_configinformation_schema.cluster_hardwareinformation_schema.cluster_loadinformation_schema.cluster_systeminfoinformation_schema.cluster_loginformation_schema.metrics_tablesinformation_schema.metrics_summaryinformation_schema.metrics_summary_by_labelinformation_schema.inspection_resultinformation_schema.inspection_summary
不支持的 Information Schema 表
TiDB 包含以下 INFORMATION_SCHEMA 表，但仅会返回空行：
COLUMN_PRIVILEGESEVENTSFILESGLOBAL_STATUSGLOBAL_VARIABLESOPTIMIZER_TRACEPARAMETERSPARTITIONSPLUGINSPROFILINGREFERENTIAL_CONSTRAINTSROUTINESSCHEMA_PRIVILEGESSESSION_STATUSTABLESPACESTABLE_PRIVILEGESTRIGGERS
