Server层统计信息字典表 | 全方位认识 information_schema

作者  罗小波·沃趣科技高级数据库技术专家

出品  沃趣科技

在上一篇《初相识|全方位认识information_schema》中，我们针对 information_schema 系统库做了一个简单的认识，本期我们将为大家带来系列第二篇《Server层统计信息字典表 | 全方位认识 information_schema》，下面请跟随我们一起开始 sys 系统库的学习之旅吧~

1. COLUMNS

该表提供查询表对象中的列(字段)信息

• 该表为InnoDB引擎临时表

下面是该表中存储的信息内容

admin@localhost : information_schema 05:32:07> select * from COLUMNS where TABLE_NAME='sbtest1' limit 1\G;
*************************** 1. row ***************************
      TABLE_CATALOG: def
        TABLE_SCHEMA: sbtest
          TABLE_NAME: sbtest1
        COLUMN_NAME: id
    ORDINAL_POSITION: 1
      COLUMN_DEFAULT: NULL
        IS_NULLABLE: NO
          DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
  NUMERIC_PRECISION: 10
      NUMERIC_SCALE: 0
  DATETIME_PRECISION: NULL
  CHARACTER_SET_NAME: NULL
      COLLATION_NAME: NULL
        COLUMN_TYPE: int(10) unsigned
          COLUMN_KEY: PRI
              EXTRA: auto_increment
          PRIVILEGES: select,insert,update,references
      COLUMN_COMMENT: 
GENERATION_EXPRESSION: 
1 row in set (0.00 sec)

字段含义如下：

• TABLE_SCHEMA：显示列信息对应表所在的库名

• TABLE_NAME：显示列信息所在的表名

• COLUMN_NAME：显示列名称

• ORDINAL_POSITION：显示列在表中的创建顺序

• COLUMN_DEFAULT：显示列默认值

• IS_NULLABLE：显示列是否带有NULL属性

• DATA_TYPE：显示列的数据类型，不包含列的数据类型定义的额外属性

• CHARACTER_MAXIMUM_LENGTH：显示字符类型字段的定义长度

• CHARACTER_OCTET_LENGTH：显示字符类型字段的定义长度对应的字节数，如果是单字节字符集，则该字段值与CHARACTER_MAXIMUM_LENGTH字段值相同(多字节字符集除外)

• NUMERIC_PRECISION：显示数字类型字段的精度（定义长度），如果不是数字类型则该类为NULL

• NUMERIC_SCALE：显示数字类型字段的标度（小数位数），如果不是数字类型则该类为NULL

• DATETIME_PRECISION：显示时间类型字段的精度（5.6版本之后，datetime时间类型字段在存储引擎层存储时都当作int类型处理，但存储时会比timestamp多一个字节），如果不是时间类型则该类为NULL

• CHARACTER_SET_NAME：显示表列的字符集，如果使用SHOW FULL COLUMNS语句查看，那么可以从结果集的Collation列中看到字符集类型，例如：Collation值为latin1_swedish_ci，则该字符集就是latin1

• COLLATION_NAME：显示列的校对规则

• COLUMN_TYPE：显示列的定义类型，包含列数据类型定义的额外属性（在show columns语句的结果集中该字段信息显示在Type列），例如：varchar(32)，该字段为 "MySQL extension" 列

• COLUMN_KEY：如果字段是索引列，则这里会显示出索引的类型

• EXTRA：显示生成列的类型，有效值为：VIRTUAL GENERATED或VIRTUAL STORED，该字段为 "MySQL extension" 列

• PRIVILEGES：显示列的可授予权限列表（这里指的是可对单个列授予的列级别权限），未列出的权限无法使用grant语句授予

• COLUMN_COMMENT：显示列的注释信息

• GENERATION_EXPRESSION：显示生成列的计算表达式，该字段为 "MySQL extension" 列

COLUMNS表中记录的信息实际上还可以使用show columns语句进行查询，下面给出两者的一个对比查询结果供大家鉴赏。

# 使用show columns语句来查询表对象的列信息
## 语法如下
SHOW [full] COLUMNS
FROM tbl_name
[FROM db_name]
[LIKE 'wild']

## 查询结果如下，当然，从这里我们也可以看到，使用show columns语句查询的列信息明显比直接查询columns表中的信息要少得多
root@localhost : information_schema 12:42:34> show columns from sbtest.sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL    | auto_increment |
| k    | int(10) unsigned | NO  | MUL | 0      |                |
| c    | char(120)        | NO  | MUL |        |                |
| pad  | char(60)        | NO  |    |        |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

## 还可以结合where语句来进行过滤
root@localhost : information_schema 12:44:46> show columns from sbtest.sbtest1 where Field like 'id%';
+-------+------------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

# 使用select语句来查询，这里我们就直接指定与show columns语句输出信息相对应的列来做一个对比
## 语法
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
[AND table_schema = 'db_name']
[AND column_name LIKE 'wild']

## 查询结果如下
root@localhost : information_schema 12:42:20> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT  FROM INFORMATION_SCHEMA.COLUMNS where table_schema='sbtest' and table_name='sbtest1';
+-------------+-----------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
+-------------+-----------+-------------+----------------+
| id          | int      | NO          | NULL          |
| k          | int      | NO          | 0              |
| c          | char      | NO          |                |
| pad        | char      | NO          |                |
+-------------+-----------+-------------+----------------+
4 rows in set (0.00 sec)

# 另外，还可以使用desc语句和show create table tb_name;语句来查询列信息，这里不再赘述，请自行尝试
desc db_name.tb_name column_name;
desc db_name.tb_name;
show create table tb_name;

PS：查询某个表所有的字段的可授予权限，除了直接查询columns表之外，还可以使用show full columns语句，如下（Privileges列内容展示了对应的字段所有的可授予权限）

root@localhost : information_schema 09:29:07> show full columns from sbtest.sbtest1;
+-------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type            | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(10) unsigned | NULL      | NO  | PRI | NULL    | auto_increment | select,insert,update,references |        |
| k    | int(10) unsigned | NULL      | NO  | MUL | 0      |                | select,insert,update,references |        |
| c    | char(120)        | utf8_bin  | NO  | MUL |        |                | select,insert,update,references |        |
| pad  | char(60)        | utf8_bin  | NO  |    |        |                | select,insert,update,references |        |
+-------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
4 rows in set (0.00 sec)

2. KEY_COLUMN_USAGE

该表提供查询哪些索引列存在约束条件

• 该表为Memory引擎临时表

下面是该表中存储的信息内容

admin@localhost : information_schema 05:52:31> select * from KEY_COLUMN_USAGE where TABLE_NAME='sbtest1' limit 1\G;
*************************** 1. row ***************************
      CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: sbtest
          CONSTRAINT_NAME: PRIMARY
            TABLE_CATALOG: def
            TABLE_SCHEMA: sbtest
              TABLE_NAME: sbtest1
              COLUMN_NAME: id
        ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
  REFERENCED_TABLE_SCHEMA: NULL
    REFERENCED_TABLE_NAME: NULL
  REFERENCED_COLUMN_NAME: NULL
1 row in set (0.00 sec)

字段含义如下：

• CONSTRAINT_CATALOG：约束登记名称，该列值总是为def

• CONSTRAINT_SCHEMA：约束所在的数据库名

• CONSTRAINT_NAME：约束名称

• TABLE_CATALOG：约束所在的表的登记名称，该列值总是为def

• TABLE_SCHEMA：约束所在的数据库名

• TABLE_NAME：约束所在的表名

• COLUMN_NAME：约束所在的列名（索引列）

• ORDINAL_POSITION：约束索引列在约束内的位置顺序(不是列在表内的位置顺序)。列位置顺序从1开始编号

• POSITION_IN_UNIQUE_CONSTRAINT：对于唯一主键约束，POSITION_IN_UNIQUE_CONSTRAINT的值为NULL。 对于外键约束，它表示被外键引用列在其所在表中的索引中的位置顺序

• REFERENCED_TABLE_SCHEMA：约束引用表所在的数据库名称

• REFERENCED_TABLE_NAME：约束所引用表的名称

• REFERENCED_COLUMN_NAME：约束所引用列的名称

如果约束是外键的，则该表中查询到的就是外键列(外键索引列)，但没有记录引用关联关系。假设有两个表名为t1和t3，其定义如下：

# 父表（主表）
CREATE TABLE t1
(
s1 INT,
s2 INT,
s3 INT,
PRIMARY KEY(s3)
) ENGINE=InnoDB;

# 子表（从表）
CREATE TABLE t3
(
s1 INT,
s2 INT,
s3 INT,
KEY(s1),
CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;

# 对于这两个表，KEY_COLUMN_USAGE表中会记录如下两行约束信息，其中t1表的约束是主键，t3表的约束是外键：
CONSTRAINT_NAME ='PRIMARY'，TABLE_NAME ='t1'，COLUMN_NAME ='s3'，ORDINAL_POSITION = 1，POSITION_IN_UNIQUE_CONSTRAINT = NULL
CONSTRAINT_NAME ='CO'，TABLE_NAME ='t3'，COLUMN_NAME ='s2'，ORDINAL_POSITION = 1，POSITION_IN_UNIQUE_CONSTRAINT = 1

3. REFERENTIAL_CONSTRAINTS

该表提供查询关于外键约束的一些信息

• 该表为Memory引擎临时表

下面是该表中存储的信息内容

# 使用MySQL样例数据库
root@localhost : information_schema 05:31:15> select * from REFERENTIAL_CONSTRAINTS where CONSTRAINT_SCHEMA='employees' limit 1\G;
*************************** 1. row ***************************
  CONSTRAINT_CATALOG: def
    CONSTRAINT_SCHEMA: employees
      CONSTRAINT_NAME: dept_emp_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
UNIQUE_CONSTRAINT_SCHEMA: employees
UNIQUE_CONSTRAINT_NAME: PRIMARY
        MATCH_OPTION: NONE
          UPDATE_RULE: RESTRICT
          DELETE_RULE: CASCADE
          TABLE_NAME: dept_emp
REFERENCED_TABLE_NAME: employees
1 row in set (0.00 sec)

字段含义如下:

• CONSTRAINT_SCHEMA和CONSTRAINT_NAME：表示外键所在的数据库名称和外键名称

• UNIQUE_CONSTRAINT_SCHEMA，UNIQUE_CONSTRAINT_NAME和REFERENCED_TABLE_NAME：表示外键约束引用的表名、引用表所在的数据库名称和约束名称

• MATCH_OPTION：唯一有效值是NONE

• UPDATE_RULE或DELETE_RULE：表示update和delete约束的具体规则。有效值为CASCADE、SET NULL、SET DEFAULT、RESTRICT、NO ACTION。

• TABLE_NAME：外键所在的表名，与INFORMATION_SCHEMA.TABLE_CONSTRAINTS中的TABLE_NAME的值相同

4. STATISTICS

该表提供查询关于索引的一些统计信息，一个索引列对应一行记录

• 该表为Memory引擎临时表

下面是该表中存储的信息内容

admin@localhost : information_schema 06:15:14> select * from STATISTICS where TABLE_NAME='sbtest1' limit 1\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sbtest
TABLE_NAME: sbtest1
NON_UNIQUE: 0
INDEX_SCHEMA: sbtest
INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
COLUMN_NAME: id
COLLATION: A
CARDINALITY: 4392231
SUB_PART: NULL
  PACKED: NULL
NULLABLE: 
INDEX_TYPE: BTREE
  COMMENT: 
INDEX_COMMENT: 
1 row in set (0.00 sec)

字段含义如下（部分字段）

• TABLE_CATALOG：该字段总是为def

• TABLE_SCHEMA：表示索引对应的表所属的数据库名称

• TABLE_NAME：表示索引所属的表名

• NON_UNIQUE：表示索引是否是非唯一索引

• INDEX_SCHEMA：表示索引所属的数据库名称

• INDEX_NAME：表示索引名称

• SEQ_IN_INDEX：由于STATISTICS表中记录的内容是一个索引列记录一行信息，所以，该字段用于记录索引列在索引中的顺序，从数字1开始计数

• COLUMN_NAME：索引涉及的字段名称

• COLLATION：索引的排序方式，有效值为：A（表示asc顺序排序）、D（desc倒序排序）、NULL（未排序）

• CARDINALITY：索引的基数值（唯一值比例），该值是基于内部的统计信息来对索引唯一值进行估算的，要更新估算值，可以使用语句analyze table(对于myisam表，可以使用myisamchk -a 命令行工具更新)

• SUB_PART：索引前缀长度。如果索引列只有部分被索引，则该字段值表示索引列的前缀字符数量（字节数量），如果整列被索引，则该字段值为NULL 
  * 注意：前缀限制数量是以字节为单位的。 所以，在使用CREATE TABLE，ALTER TABLE和CREATE INDEX语句创建前缀索引时，你需要考虑字符集的因素。

• NULLABLE：表示该字段是否包含NULL和空值，如果有，则该字段值为YES，否则为空。要注意，索引列只要允许为NULL，则该字段值就为YES

• INDEX_TYPE：索引类型，有效值为：BTREE、HASH、RTREE、FULLTEXT等（该字段为 "MySQL extension" 列）

• COMMENT：索引中没有描述的索引信息，例如：disabled表示该索引处于禁用状态。要注意：InnoDB的表的索引不支持关闭（MyISAM支持）

• INDEX_COMMENT：索引注释信息（该字段为 "MySQL extension" 列）

PS：该表中的信息还可以使用show语句查询

# show index有两种方法，两种方法差别不大，如下
root@localhost : information_schema 07:04:47> show index from sbtest1 from sbtest;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sbtest1 |          0 | PRIMARY  |            1 | id          | A        |    7522488 |    NULL | NULL  |      | BTREE      |        |              |
| sbtest1 |          1 | k_1      |            1 | k          | A        |      713252 |    NULL | NULL  |      | BTREE      |        |              |
| sbtest1 |          1 | i_c      |            1 | c          | A        |    4176349 |    NULL | NULL  |      | BTREE      |        |              |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

root@localhost : information_schema 07:04:29> show index from sbtest.sbtest1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sbtest1 |          0 | PRIMARY  |            1 | id          | A        |    7522488 |    NULL | NULL  |      | BTREE      |        |              |
| sbtest1 |          1 | k_1      |            1 | k          | A        |      713252 |    NULL | NULL  |      | BTREE      |        |              |
| sbtest1 |          1 | i_c      |            1 | c          | A        |    4176349 |    NULL | NULL  |      | BTREE      |        |              |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

5. TABLE_CONSTRAINTS

该表提供查询表相关的约束信息

• 该表为Memory引擎临时表

下面是该表中存储的信息内容

admin@localhost : information_schema 06:19:37> select * from TABLE_CONSTRAINTS where TABLE_NAME='sbtest1';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | sbtest            | PRIMARY        | sbtest      | sbtest1    | PRIMARY KEY    |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
1 row in set (0.00 sec)

字段含义如下:

• CONSTRAINT_SCHEMA：约束所属的数据库名称

• CONSTRAINT_NAME：约束名称

• TABLE_SCHEMA：约束所在的表所属的数据库名称

• TABLE_NAME：约束所在的表的表名

• CONSTRAINT_TYPE：约束类型，有效值为：UNIQUE、PRIMARY KEY、FOREIGN KEY 
  * 当索引具有唯一约束时(主键或唯一索引)，主键索引该字段为PRIMARY KEY，唯一索引该字段为UNIQUE 
  * 另外，该列值还支持一个CHECK值，但目前还不支持该类型的约束

6. FILES

该表提供查询MySQL的数据表空间文件相关的信息，包含InnoDB存储引擎和NDB存储引擎相关的数据文件信息，由于NDB存储引擎在国内较少使用，我们大多数场景(95%以上场景InnoDB存储引擎都可以使用)都是使用InnoDB存储引擎，以下我们只针对InnoDB存储引擎做介绍

• 该表是Memory存储引擎表

下面是该表中存储的信息内容

# 表完整行记录数据
admin@localhost : information_schema 05:44:39> select * from FILES limit 1\G;
*************************** 1. row ***************************
        FILE_ID: 0
      FILE_NAME: /home/mysql/data/mysqldata1/innodb_ts/ibdata1
      FILE_TYPE: TABLESPACE
TABLESPACE_NAME: innodb_system
  TABLE_CATALOG: 
    TABLE_SCHEMA: NULL
      TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
          ENGINE: InnoDB
  FULLTEXT_KEYS: NULL
    DELETED_ROWS: NULL
    UPDATE_COUNT: NULL
    FREE_EXTENTS: 3
  TOTAL_EXTENTS: 2048
    EXTENT_SIZE: 1048576
    INITIAL_SIZE: 2147483648
    MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 67108864
  CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
    RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
        VERSION: NULL
      ROW_FORMAT: NULL
      TABLE_ROWS: NULL
  AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
    INDEX_LENGTH: NULL
      DATA_FREE: 2105540608
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
      CHECK_TIME: NULL
        CHECKSUM: NULL
          STATUS: NORMAL
          EXTRA: NULL
1 row in set (0.00 sec)

# 该表字段较多，通常，我们可能只需要查询一部分字段即可，类似如下：
root@localhost : information_schema 12:22:27> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE FROM INFORMATION_SCHEMA.FILES \G

字段含义如下（该表中所有字段都为 "MySQL extension" 列）

• FILE_ID：表空间ID，也称"space_id"或"fil_space_t::id"

• FILE_NAME：数据文件(表空间)名称。使用独立表空间和常规表空间的表的数据文件名中都有一个.ibd文件扩展名。undo log的前缀为"undo"，系统表空间的前缀是"ibdata"，临时表空间的前缀是"ibtmp"，这些表空间文件的名称中都包含了文件路径(与MySQL数据目录相关，通常只有undo log为绝对路径，其他表空间文件为相对路径)

• FILE_TYPE：表空间文件类型。 InnoDB表空间文件有三种可能的文件类型。 
  * TABLESPACE：表示表相关的系统表空间、常规表空间、独立表空间文件或其他形式的用户数据文件类型 
  * TEMPORARY：表示临时表空间的文件类型 
  * UNDO LOG：表示undo log表空间文件类型

• TABLESPACE_NAME：表空间的SQL名称。常规表空间名称是SYS_TABLESPACES.NAME值。对于其他表空间文件，名称以"innodb_"开头，例如："innodb_system"、"innodb_undo"和"innodb_file_per_table"。其中，"innodb_file_per_table_##"表示独立表空间名称(##表示表空间ID)

• ENGINE：存储引擎名称。对于InnoDB存储引擎的文件，该列值总是InnoDB

• FREE_EXTENTS：表示当前数据文件中空闲的可用区块的数量

• TOTAL_EXTENTS：表示当前数据文件中总的区块数量。不计算文件末尾的部分

• EXTENT_SIZE：表示数据文件的区块大小。对于4k，8k或16k页面大小的数据文件，区块是1048576（1MB）。对于32k页面大小的文件，区块大小为2097152字节（2MB），对于64k页面大小的文件，区块大小为4194304（4MB）。 INFORMATION_SCHEMA.FILES不记录InnoDB页面大小。页面大小由系统配置参数innodb_page_size定义。另外，区块大小信息也可以从INNODB_SYS_TABLESPACES中查询，其中FILES.FILE_ID 即为 INNODB_SYS_TABLESPACES.SPACE_ID

• INITIAL_SIZE：表示数据文件的初始大小，以字节为单位

• MAXIMUM_SIZE：表示数据文件中允许的最大字节数。除了系统表空间和临时表空间可以使用参数定义一个最大值之外(不设置自动扩展，指定的表空间大小即为该数据文件的最大值。系统表空间文件大小由innodb_data_file_path定义，临时表空间文件大小由innodb_temp_data_file_path定义)，所有的数据文件的最大值均为NULL。当数据空间文件的最大值为NULL时，表示不限制表空间文件的文件大小限制

• AUTOEXTEND_SIZE：表示表空间文件的自动扩展大小，由innodb_data_file_path系统配置参数定义(临时表空间的自动扩展大小由系统配置参数innodb_temp_data_file_path定义)

• DATA_FREE：表示整个表空间的可用空间总量（以字节为单位）。预定义的系统表空间（包括系统表空间和临时表空间）可能有一个或多个数据文件

• STATUS：默认为NORMAL。 InnoDB的独立表空间文件的信息可能会被记录为IMPORTING，此时表明表空间文件不可用

PS：

• 以上字段含义解释仅适用于InnoDB引擎的数据文件。下面没有提及到的INFORMATION_SCHEMA.FILES表中的字段不适用于InnoDB存储引擎，且当文件为InnoDB引擎时这些未提及的字段信息显示为NULL值。

• 该表中的数据是根据缓存在内存中的已打开数据文件来记录的，与查询INFORMATION_SCHEMA.INNODB_SYS_DATAFILES表中的内部数据字典信息不同，INFORMATION_SCHEMA.INNODB_SYS_DATAFILES中的数据是来自InnoDB存储引擎的内部数据字典表SYS_DATAFILES

• INFORMATION_SCHEMA.FILES表中记录的数据包含了临时表空间数据信息（undo log独立表空间文件的信息也由INFORMATION_SCHEMA.FILES表记录）。临时表空间文件的数据信息在内部数据字典表SYS_DATAFILES中不可用，因此INNODB_SYS_DATAFILES表不记录。

注意：该表中的数据没有对应的show语句查询方式

7. ENGINES

该表提供查询MySQL Server支持的引擎相关的信息

• 该表是Memory引擎临时表

下面是该表中存储的信息内容

admin@localhost : information_schema 05:43:13> select * from ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE            | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA  | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES    | CSV storage engine                                            | NO          | NO  | NO        |
| MRG_MYISAM        | YES    | Collection of identical MyISAM tables                          | NO          | NO  | NO        |
| MyISAM            | YES    | MyISAM storage engine                                          | NO          | NO  | NO        |
| BLACKHOLE          | YES    | /dev/null storage engine (anything you write to it disappears) | NO          | NO  | NO        |
| InnoDB            | DEFAULT | Supports transactions, row-level locking, and foreign keys    | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES    | Performance Schema                                            | NO          | NO  | NO        |
| ARCHIVE            | YES    | Archive storage engine                                        | NO          | NO  | NO        |
| MEMORY            | YES    | Hash based, stored in memory, useful for temporary tables      | NO          | NO  | NO        |
| FEDERATED          | NO      | Federated MySQL storage engine                                | NULL        | NULL | NULL      |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

字段含义如下（该表中所有字段都为 "MySQL extension" 列）

• ENGINE：引擎名称

• SUPPORT：表示ENGINE字段显示的引擎在MySQL Server中是否支持

• COMMENT：表示ENGINE字段显示的引擎相关的解释信息，例如："Supports transactions, row-level locking, and foreign keys"，表示支持事务、支持行级锁，支持外键

• TRANSACTIONS：表示ENGINE字段显示的引擎是否支持事务

• XA：表示ENGINE字段显示的引擎是否支持XA事务

• SAVEPOINTS：表示ENGINE字段显示的引擎是否支持事务保存点

PS：该表中的内容还可以通过show语句查询

# 语法
SHOW [STORAGE] ENGINES

# 示例
root@localhost : information_schema 10:21:05> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine            | Support | Comment                                                        | Transactions | XA  | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY            | YES    | Hash based, stored in memory, useful for temporary tables      | NO          | NO  | NO        |
| CSV                | YES    | CSV storage engine                                            | NO          | NO  | NO        |
| MRG_MYISAM        | YES    | Collection of identical MyISAM tables                          | NO          | NO  | NO        |
| BLACKHOLE          | YES    | /dev/null storage engine (anything you write to it disappears) | NO          | NO  | NO        |
| InnoDB            | DEFAULT | Supports transactions, row-level locking, and foreign keys    | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES    | Performance Schema                                            | NO          | NO  | NO        |
| ARCHIVE            | YES    | Archive storage engine                                        | NO          | NO  | NO        |
| MyISAM            | YES    | MyISAM storage engine                                          | NO          | NO  | NO        |
| FEDERATED          | NO      | Federated MySQL storage engine                                | NULL        | NULL | NULL      |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

8. TABLESPACES

该表提供查询关于活跃表空间的相关信息（主要记录的是NDB存储引擎表空间信息）

• 该表为Memory引擎临时表

• 注意：该表不提供有关InnoDB存储引擎的表空间的信息。 对于InnoDB表空间元数据信息，请查询INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES表。另外，从MySQL 5.7.8开始，INFORMATION_SCHEMA.FILES表也提供查询InnoDB表空间的元数据信息

下面是该表中存储的信息内容

# 暂无，需要创建NDB存储引擎表

字段含义如下（该表中的所有字段为 "MySQL extension" 列）

• TABLESPACE_NAME：表空间文件名称

• ENGINE：引擎名称

• TABLESPACE_TYPE：表空间类型

• LOGFILE_GROUP_NAME：日志文件组名称

• EXTENT_SIZE：区块大小

• AUTOEXTEND_SIZE：自动扩展大小

• MAXIMUM_SIZE：表空间文件最大大小

• NODEGROUP_ID：节点ID

• TABLESPACE_COMMENT：表空间文件注释信息

9. SCHEMATA

该表提供查询MySQL Server中的数据库列表信息，一个schema就代表一个database

• 该表为Memory引擎临时表

下面是该表中存储的信息内容

root@localhost : information_schema 06:35:32> select * from schemata;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                      | utf8_general_ci        | NULL    |
| def          | employees          | utf8                      | utf8_bin              | NULL    |
| def          | luoxiaobo          | utf8                      | utf8_bin              | NULL    |
| def          | mysql              | utf8                      | utf8_bin              | NULL    |
| def          | performance_schema | utf8                      | utf8_general_ci        | NULL    |
| def          | qfsys              | utf8                      | utf8_bin              | NULL    |
| def          | sbtest            | utf8                      | utf8_bin              | NULL    |
| def          | sys                | utf8                      | utf8_general_ci        | NULL    |
| def          | test              | utf8                      | utf8_bin              | NULL    |
+--------------+--------------------+----------------------------+------------------------+----------+
9 rows in set (0.00 sec)

字段含义如下：

• SCHEMA_NAME：数据库名称

• DEFAULT_CHARACTER_SET_NAME：数据库的默认字符集名称

• DEFAULT_COLLATION_NAME：数据库的默认校对规则名称

• SQL_PATH：总是为NULL

PS：该表中的信息还可以使用show语句查询

root@localhost : information_schema 06:41:30> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| employees          |
| luoxiaobo          |
| mysql              |
| performance_schema |
| qfsys              |
| sbtest            |
| sys                |
| test              |
+--------------------+
9 rows in set (0.00 sec)

本期内容就介绍到这里，本期内容参考链接如下：

• https://dev.mysql.com/doc/refman/5.7/en/columns-table.html

• https://dev.mysql.com/doc/refman/5.7/en/key-column-usage-table.html

• https://dev.mysql.com/doc/refman/5.7/en/referential-constraints-table.html

• https://dev.mysql.com/doc/refman/5.7/en/statistics-table.html

• https://dev.mysql.com/doc/refman/5.7/en/table-constraints-table.html

• https://dev.mysql.com/doc/refman/5.7/en/files-table.html

• https://dev.mysql.com/doc/refman/5.7/en/engines-table.html

• https://dev.mysql.com/doc/refman/5.7/en/tablespaces-table.html

• https://dev.mysql.com/doc/refman/5.7/en/schemata-table.html

| 作者简介

罗小波·沃趣科技高级数据库技术专家

IT从业多年，历任运维工程师，高级运维工程师，运维经理，数据库工程师，曾参与版本发布系统，轻量级监控系统，运维管理平台，数据库管理平台的设计与编写，熟悉MySQL的体系结构时，InnoDB存储引擎，喜好专研开源技术，追求完美。

点击查看招聘信息

相关链接

更多干货，欢迎来撩~