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

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

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

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

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存储引擎,喜好专研开源技术,追求完美。

发表评论

电子邮件地址不会被公开。 必填项已用*标注