Information Schema
为了和 MySQL 保持兼容，TiDB 支持很多 INFORMATION_SCHEMA 表，其中有不少表都支持相应的 SHOW 命令。查询 INFORMATION_SCHEMA 表也为表的连接操作提供了可能。
CHARACTER_SETS Table
CHARACTER_SETS 表提供字符集相关的信息。TiDB 目前仅支持部分字符集。
mysql> 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 Table
COLLATIONS 表提供了 CHARACTER_SETS 表中字符集对应的排序规则列表。TiDB 当前仅支持二进制排序规则，包含该表仅为兼容 MySQL。
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 Table
COLLATION_CHARACTER_SET_APPLICABILITY 表将排序规则映射至适用的字符集名称。和 COLLATIONS 表一样，包含此表也是为了兼容 MySQL。
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 Table
COLUMNS 表提供了表的所有列的信息。
mysql> CREATE TABLE test.t1 (a int);
1 row in set (0.01 sec)
mysql> SELECT * FROM information_schema.columns WHERE table_schema='test' AND TABLE_NAME='t1'\G
*************************** 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 语句如下：
mysql> 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 Table
ENGINES 表提供了关于存储引擎的信息。从和 MySQL 兼容性上考虑，TiDB 会一直将 InnoDB 描述为唯一支持的引擎。
mysql> SELECT * FROM engines\G
*************************** 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 Table
KEY_COLUMN_USAGE 表描述了列的键约束，比如主键约束。
mysql> SELECT * FROM key_column_usage WHERE table_schema='mysql' and table_name='user'\G
*************************** 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)
SCHEMATA Table
SCHEMATA 表提供了关于数据库的信息。表中的数据与 SHOW DATABASES 语句的执行结果等价。
mysql> 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)
SESSION_VARIABLES Table
SESSION\_VARIABLES 表提供了关于 session 变量的信息。表中的数据跟 SHOW SESSION VARIABLES 语句执行结果类似。
mysql> 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 慢查询日志而来，列名和慢日志中的字段名是一一对应。关于如何使用该表调查和改善慢查询请参考慢查询日志文档。
mysql> 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 | |
| Process_time | double unsigned | YES | | NULL | |
| Wait_time | double unsigned | YES | | NULL | |
| Backoff_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_ids | 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 | |
| Query | longblob unsigned | YES | | NULL | |
+---------------+---------------------+------+------+---------+-------+
STATISTICS Table
STATISTICS 表提供了关于表索引的信息。
mysql> 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.STATISTICS
WHERE table_name = 'tbl_name'
AND table_schema = 'db_name'
SHOW INDEX
FROM tbl_name
FROM db_name
TABLES Table
TABLES 表提供了数据库里面关于表的信息。
mysql> SELECT * FROM tables WHERE table_schema='mysql' AND table_name='user'\G
*************************** 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
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']
TABLE_CONSTRAINTS Table
TABLE\_CONSTRAINTS 表记录了表的约束信息。
mysql> SELECT * FROM table_constraints WHERE constraint_type='UNIQUE'\G
*************************** 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
6 rows in set (0.00 sec)
其中：
CONSTRAINT_TYPE的取值可以是UNIQUE，PRIMARY KEY，或者FOREIGN KEY。UNIQUE和PRIMARY KEY信息与SHOW INDEX语句的执行结果类似。
USER_PRIVILEGES Table
USER_PRIVILEGES 表提供了关于全局权限的信息。该表的数据根据 mysql.user 系统表生成。
mysql> 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)
不支持的 Information Schema 表
TiDB 包含以下 INFORMATION_SCHEMA 表，但仅会返回空行：
COLUMN_PRIVILEGESEVENTSFILESGLOBAL_STATUSGLOBAL_VARIABLESOPTIMIZER_TRACEPARAMETERSPARTITIONSPLUGINSPROFILINGREFERENTIAL_CONSTRAINTSROUTINESSCHEMA_PRIVILEGESSESSION_STATUSTABLESPACESTABLE_PRIVILEGESTRIGGERSVIEWS