在上一篇《InnoDB 层压缩相关字典表 | 全方位认识information_schema》中，我们详细介绍了InnoDB层记录压缩表信息的字典表，本期我们将为大家带来系列第九篇《应用示例荟萃 | 全方位认识information_schema》，也是"全方位认识information_schema"的最后一篇，下面请跟随我们一起开始information_schema系统库的系统学习之旅吧
PS：为了方便大家更直观地理解下文中的示例信息，我们在这一期里特地把示例中涉及到的相关字段含义重新罗列了出来，所以本文会稍微长一些。
1. 使用Server层的字典表查询相关的元数据信息
1.1. 查看数据库中是否有使用外键
KEY_COLUMN_USAGE表提供查询哪些索引列存在约束条件(该表记录的约束信息除了外键之外，还包括主键和唯一索引约束的信息)，假设我们想查询一下employees库下是否存在外键，我们可以使用如下SQL进行查询
代码语言：javascript复制root@localhost : information_schema 04:29:04> select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA='employees' and REFERENCED_TABLE_SCHEMA is not nullG
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: employees
CONSTRAINT_NAME: dept_emp_ibfk_1
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: dept_emp
COLUMN_NAME: emp_no
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
REFERENCED_TABLE_SCHEMA: employees
REFERENCED_TABLE_NAME: employees
REFERENCED_COLUMN_NAME: emp_no
*************************** 2. row ***************************
......
6 rows in set (0.01 sec)
从查询结果来看，发现employees库下多达6个外键，这些查询结果代表什么意思呢，下面对这些结果做一个简单的解读
- CONSTRAINT_CATALOG：约束登记名称，该列值总是为def
- CONSTRAINT_SCHEMA：约束所在的数据库名，这里指外键所在的数据库名
- CONSTRAINT_NAME：约束名称，这里指外键名称
- TABLE_CATALOG：约束所在的表的登记名称，该列值总是为def
- TABLE_SCHEMA：约束所在的数据库名，与CONSTRAINT_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：约束所引用列的名称，这里指外键所引用的列名
PS：通常在开发规范中不允许使用外键，如果确实是审核不规范导致用了外键，那么就可以找开发沟通是否可用使用程序逻辑来做一些数据上的约束
1.2. 查看InnoDB表空间文件信息
FILES表供查询MySQL的数据表空间文件相关的信息，包含InnoDB存储引擎和NDB存储引擎相关的数据文件信息，由于NDB存储引擎在国内较少使用，我们大多数场景(95%以上场景InnoDB存储引擎都满可以使用)都是使用InnoDB存储引擎。假设我们想查询employees库下的InnoDB表dept_emp的表空间使用情况，我们可以使用如下语句进行查询
代码语言：javascript复制root@localhost : information_schema 05:08:06> select * from information_schema.FILES where file_name='./employees/dept_emp.ibd'G
*************************** 1. row ***************************
FILE_ID: 49
FILE_NAME: ./employees/dept_emp.ibd
FILE_TYPE: TABLESPACE
TABLESPACE_NAME: innodb_file_per_table_49
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: 1
TOTAL_EXTENTS: 30
EXTENT_SIZE: 1048576
INITIAL_SIZE: 65536
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 1048576
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: 2097152
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
1 row in set (0.00 sec)
上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- 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表不记录。
1.3. 查看索引的统计信息
STATISTICS表提供查询关于索引的一些统计信息，一个索引列对应一行记录。假设我们需要查询employees库下的InnoDB表dept_emp的主键索引统计信息，我们可以使用如下语句进行查询
代码语言：javascript复制root@localhost : information_schema 06:07:20> select * from information_schema.STATISTICS where TABLE_SCHEMA='employees' and TABLE_NAME='dept_emp' and INDEX_NAME='primary'G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: dept_emp
NON_UNIQUE: 0
INDEX_SCHEMA: employees
INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
COLUMN_NAME: emp_no
COLLATION: A
CARDINALITY: 299600
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: dept_emp
NON_UNIQUE: 0
INDEX_SCHEMA: employees
INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 2
COLUMN_NAME: dept_no
COLLATION: A
CARDINALITY: 331143
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
2 rows in set (0.00 sec)
从上面的结果集中，我们可以看到该表的主键有2个列，说明是多列主键，那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- 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
- NULLABLE：表示该字段是否包含NULL和空值，如果有，则该字段值为YES，否则为空。要注意，索引列只要允许为NULL，则该字段值就为YES
- INDEX_TYPE：索引类型，有效值为：BTREE、HASH、RTREE、FULLTEXT等
- COMMENT：索引中没有描述的索引信息，例如：disabled表示该索引处于禁用状态。要注意：InnoDB的表的索引不支持关闭（MyISAM支持）
- INDEX_COMMENT：索引注释信息
1.4. 在Server层查看表的列相关的信息
COLUMNS表提供查询表对象中的列(字段)信息。假设我们需要查询employees库下的InnoDB表dept_emp的字段名称及其各个列在表中的创建顺序信息，我们可以使用如下语句进行查询
代码语言：javascript复制root@localhost : information_schema 06:39:39> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME='dept_emp';
-------------- ------------ ------------- ------------------ ---------------- ------------- -------------------- ---------------- ------------- ------------ ----------------
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | COLUMN_COMMENT |
-------------- ------------ ------------- ------------------ ---------------- ------------- -------------------- ---------------- ------------- ------------ ----------------
| employees | dept_emp | emp_no | 1 | NULL | NO | NULL | NULL | int(11) | PRI | |
| employees | dept_emp | dept_no | 2 | NULL | NO | utf8 | utf8_bin | char(4) | PRI | |
| employees | dept_emp | from_date | 3 | NULL | NO | NULL | NULL | date | | |
| employees | dept_emp | to_date | 4 | NULL | NO | NULL | NULL | date | | |
-------------- ------------ ------------- ------------------ ---------------- ------------- -------------------- ---------------- ------------- ------------ ----------------
4 rows in set (0.00 sec)
从上面的结果集中，我们可以看到dept_emp表各个列的创建顺序以及列名称，以及对应的字符集和字段的数据类型等信息，那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- TABLE_SCHEMA：显示列信息对应表所在的库名
- TABLE_NAME：显示列信息所在的表名
- COLUMN_NAME：显示列名称
- ORDINAL_POSITION：显示列在表中的创建顺序
- COLUMN_DEFAULT：显示列默认值
- IS_NULLABLE：显示列是否带有NULL属性
- CHARACTER_SET_NAME：显示表列的字符集，如果使用SHOW FULL COLUMNS语句查看，那么可以从结果集的Collation列中看到字符集类型，例如：Collation值为latin1_swedish_ci，则该字符集就是latin1
- COLLATION_NAME：显示列的校对规则
- COLUMN_TYPE：显示列的定义类型，包含列数据类型定义的额外属性（在show columns语句的结果集中该字段信息显示在Type列），例如：varchar(32)
- COLUMN_KEY：如果字段是索引列，则这里会显示出索引的类型
- COLUMN_COMMENT：显示列的注释信息
1.5. 查看数据库中是否有使用存储程序
ROUTINES表可以提供查询关于存储过程和存储函数的信息（不包括用户自定义函数UDF），假设我们需要查询一下employees库下是否存在存储程序，我们可以使用如下SQL进行查询
代码语言：javascript复制root@localhost : information_schema 06:55:18> select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,CHARACTER_SET_NAME,COLLATION_NAME,DTD_IDENTIFIER,CREATED,LAST_ALTERED from information_schema.ROUTINES where ROUTINE_SCHEMA='employees';
---------------- ------------------ -------------- -------------------- ---------------- ---------------- --------------------- ---------------------
| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | CREATED | LAST_ALTERED |
---------------- ------------------ -------------- -------------------- ---------------- ---------------- --------------------- ---------------------
| employees | current_manager | FUNCTION | utf8 | utf8_bin | varchar(32) | 2018-08-14 18:48:08 | 2018-08-14 18:48:08 |
| employees | emp_dept_id | FUNCTION | utf8 | utf8_bin | char(4) | 2018-08-14 18:48:08 | 2018-08-14 18:48:08 |
| employees | emp_dept_name | FUNCTION | utf8 | utf8_bin | varchar(40) | 2018-08-14 18:48:08 | 2018-08-14 18:48:08 |
| employees | emp_name | FUNCTION | utf8 | utf8_bin | varchar(32) | 2018-08-14 18:48:08 | 2018-08-14 18:48:08 |
| employees | show_departments | PROCEDURE | NULL | NULL | NULL | 2018-08-14 18:48:08 | 2018-08-14 18:48:08 |
---------------- ------------------ -------------- -------------------- ---------------- ---------------- --------------------- ---------------------
5 rows in set (0.01 sec)
从上面的结果集中，我们可以看到employees库下存在着5个存储函数，那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- ROUTINE_SCHEMA：存储程序所在的数据库名称
- ROUTINE_NAME：存储程序名称
- ROUTINE_TYPE：存储程序类型，有效值为：PROCEDURE和FUNCTION
- CHARACTER_SET_NAME：如果是存储函数，那么该字段表示返回字符串的字符集，如果是存储过程，则该字段为NULL
- COLLATION_NAME：如果是存储函数，那么该字段表示返回字符串的排序规则，如果是存储过程，则该字段为NULL
- DTD_IDENTIFIER：如果存储程序为函数，则该字段为返回数据类型的值，如果为存储过程，则该字段为空
- CREATED：表示创建存储程序的日期和时间。是一个TIMESTAMP值
- LAST_ALTERED：表示存储过程最近一次修改的日期和时间。也是一个TIMESTAMP值。如果自存储程序创建以来从未修改过，则该列值与CREATED列值相同
PS：通常在开发规范中不允许使用存储程序，如果确实是审核不规范导致用了存储程序，那么就可以找开发沟通是否可用使用程序逻辑来代替存储程序的逻辑
1.6. 查看数据库中的分区表信息
PARTITIONS表提供查询关于分区表的信息。假设我们需要查询employees库下的salaries表的分区信息，我们可以使用如下SQL进行查询
代码语言：javascript复制root@localhost : information_schema 07:41:20> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,DATA_FREE from information_schema.PARTITIONS where TABLE_SCHEMA='employees' and TABLE_NAME='salaries' and PARTITION_NAME is not null;
-------------- ------------ ---------------- ------------------ ---------------------- ----------------------- ------------ -----------
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_FREE |
-------------- ------------ ---------------- ------------------ ---------------------- ----------------------- ------------ -----------
| employees | salaries | p01 | RANGE | year(from_date) | 1985 | 0 | 0 |
| employees | salaries | p02 | RANGE | year(from_date) | 1986 | 18212 | 0 |
| employees | salaries | p03 | RANGE | year(from_date) | 1987 | 38294 | 0 |
| employees | salaries | p04 | RANGE | year(from_date) | 1988 | 57908 | 0 |
| employees | salaries | p05 | RANGE | year(from_date) | 1989 | 77055 | 0 |
| employees | salaries | p06 | RANGE | year(from_date) | 1990 | 96202 | 0 |
| employees | salaries | p07 | RANGE | year(from_date) | 1991 | 114882 | 0 |
| employees | salaries | p08 | RANGE | year(from_date) | 1992 | 132628 | 0 |
| employees | salaries | p09 | RANGE | year(from_date) | 1993 | 151308 | 0 |
| employees | salaries | p10 | RANGE | year(from_date) | 1994 | 168120 | 0 |
| employees | salaries | p11 | RANGE | year(from_date) | 1995 | 185399 | 0 |
| employees | salaries | p12 | RANGE | year(from_date) | 1996 | 201744 | 0 |
| employees | salaries | p13 | RANGE | year(from_date) | 1997 | 212625 | 0 |
| employees | salaries | p14 | RANGE | year(from_date) | 1998 | 233033 | 0 |
| employees | salaries | p15 | RANGE | year(from_date) | 1999 | 247510 | 0 |
| employees | salaries | p16 | RANGE | year(from_date) | 2000 | 261053 | 0 |
| employees | salaries | p17 | RANGE | year(from_date) | 2001 | 255916 | 0 |
| employees | salaries | p18 | RANGE | year(from_date) | 2002 | 247510 | 0 |
| employees | salaries | p19 | RANGE | year(from_date) | MAXVALUE | 141034 | 0 |
-------------- ------------ ---------------- ------------------ ---------------------- ----------------------- ------------ -----------
19 rows in set (0.00 sec)
从上面的结果集中，我们可以看到salaries表一共有19个分区，使用时间范围分区，那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- TABLE_SCHEMA：表示该分区表所属的数据库的名称
- TABLE_NAME：表示该分区表的表名称
- PARTITION_NAME：表示分区表的分区名称
- PARTITION_METHOD：表示分区表的分区函数类型，有效值为：RANGE、LIST、HASH、LINEAR HASH、KEY、LINEAR KEY
- PARTITION_EXPRESSION：表示分区函数中的分区表达式，在创建分区表或修改分区表的分区函数时指定，例如：指定了分区表达式为 "PARTITION BY HASH(c1 c2)"，则在该字段中记录表达式 "c1 c2"
- PARTITION_DESCRIPTION：表示RANGE和LIST分区定义的分区定义值。对于RANGE分区，它表示每个分区的VALUES LESS THAN子句中设置的值，该值可以是整数或MAXVALUE。对于LIST分区，它表示每个分区的VALUES IN子句中定义的值，该值为逗号分隔的整数值列表。另外，对于非range和list分区的其他分区类型，该字段为NULL
- TABLE_ROWS：分区中的记录行数，对于InnoDB分区表，TABLE_ROWS列值只是SQL优化中使用的估计值，并不是精确值
- DATA_FREE：分配给分区或子分区但未使用的空间大小字节数
1.7. 查看数据库中的触发器
TRIGGERS表可以提供查询关于某个数据库下的触发器相关的信息，要查询某个表的触发器，查询的账户必须要有trigger权限。假设我们需要查询sys库下是否存在触发器，我们可以使用如下SQL进行查询
代码语言：javascript复制root@localhost : information_schema 07:49:23> select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_ORIENTATION,ACTION_TIMING,CREATED from information_schema.TRIGGERS where TRIGGER_SCHEMA='sys';
---------------- ---------------------------- -------------------- -------------------- -------------------- --------------- ------------------------
| TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_ORIENTATION | ACTION_TIMING | CREATED |
---------------- ---------------------------- -------------------- -------------------- -------------------- --------------- ------------------------
| sys | sys_config_insert_set_user | INSERT | sys_config | ROW | BEFORE | 2018-07-23 16:39:16.51 |
| sys | sys_config_update_set_user | UPDATE | sys_config | ROW | BEFORE | 2018-07-23 16:39:16.51 |
---------------- ---------------------------- -------------------- -------------------- -------------------- --------------- ------------------------
2 rows in set (0.01 sec)
从上面的结果集中，我们可以看到sys库下有2个触发器，那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- TRIGGER_SCHEMA和TRIGGER_NAME：表示触发器所属的数据库名称和触发器名称
- EVENT_MANIPULATION：表示触发器触发事件在关联表上的操作类型，有效值为：“INSERT”（表示插入了一行数据）、“DELETE”（表示一行被删除）、“UPDATE”（表示一行被修改）
- EVENT_OBJECT_SCHEMA和EVENT_OBJECT_TABLE：每个触发器只与一个表相关联。这两个字段表示触发器关联的表所在的数据库名称和触发器关联的表名
1.8. 查看数据库中的计划任务
EVENTS表可以提供查询计划任务事件相关的信息，假设我们要查询sbtest库下面是否存在计划任务，可以使用如下SQL进行查询
代码语言：javascript复制root@localhost : sbtest 10:22:30> select * from information_schema.events where EVENT_SCHEMA='sbtest'G
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: sbtest
EVENT_NAME: test_event
DEFINER: root@localhost
TIME_ZONE: 08:00
EVENT_BODY: SQL
EVENT_DEFINITION: BEGIN
insert into test_table select max(id) from sbtest1;
END
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
STARTS: 2018-08-15 10:22:04
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2018-08-15 10:22:04
LAST_ALTERED: 2018-08-15 10:22:04
LAST_EXECUTED: NULL
EVENT_COMMENT: 每天统计sbtest1表中的最大自增值
ORIGINATOR: 3306162
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_bin
1 row in set (0.00 sec)
从上面的结果集中，我们可以看到sbtest库下有1个计划任务，那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- EVENT_CATALOG：此列的值始终为def
- EVENT_SCHEMA：此事件所属的数据库名称
- EVENT_NAME：事件的名称
- DEFINER：创建事件的账户名称
- TIME_ZONE：事件的时区，是用于调度事件的时区，且在事件执行时生效。默认值为SYSTEM，代表使用system_time_zone系统变量设置的时区
- EVENT_BODY：用于事件的DO子句中的语句的语言类型，在MySQL 5.7中，总是"SQL"。注意：不要将此列值与早期MySQL版本中存在的同名列（该列先更名为EVENT_DEFINITION列）列混淆
- EVENT_DEFINITION：构成事件的DO子句的SQL语句的文本。即被事件执行的SQL语句
- EVENT_TYPE：事件重复类型，一次（transient）或重复（RECURRING）
- EXECUTE_AT：对于一次性事件，该字段表示创建事件的CREATE EVENT语句中、或修改事件的最后一个ALTER EVENT语句的AT子句中指定的DATETIME值（例如，如果事件是使用"ON SCHEDULE AT CURRENT_TIMESTAMP '1：6'DAY_HOUR"子句创建，且事件在2018-01-21 14:05:30创建的，则此列中显示的值为'2018-01-22 20:05:30'，表示这个一次性事件将在创建时间2018-01-21 14:05:30的基础上再过一天 6小时之后执行）。如果事件的计时由EVERY子句而不是AT子句确定（则表示该事件是一个重复事件），则此列的值为NULL。
- INTERVAL_VALUE：对于重复事件，此列包含事件的EVERY子句中的数字部分。但对于一次性事件，此列为NULL。
- INTERVAL_FIELD：对于重复事件，此列包含EVERY子句的单位部分，用于管理事件的时间。此列有效值可能包含“YEAR”，“QUARTER”，“DAY”等值。但对于一次性事件，此列为NULL。
- SQL_MODE：创建或更改事件时MySQL Server的SQL模式
- STARTS：对于其定义中包含STARTS子句的重复事件，此列包含相应的DATETIME值。与EXECUTE_AT列类似，此值可解析定义语句中所使用的任何表达式并计算出结果值存放在该列中。如果没有STARTS子句，则此列为NULL
- ENDS：对于其定义中包含ENDS子句的重复事件，此列包含相应的DATETIME值。与EXECUTE_AT列类似，此值可解析定义语句中所使用的任何表达式并计算出结果值存放在该列中。如果没有ENDS子句，则此列为NULL
- STATUS：该列包含三个有效值，ENABLED、DISABLED、SLAVESIDE_DISABLED * SLAVESIDE_DISABLED：表示事件是通过主备复制中的binlog重放方式在从库上创建的，事件运行状态在从库上被关闭
- ON_COMPLETION：该列包含两个有效值，PRESVEVE、NOT PRESERVE
- CREATED：创建事件的日期和时间。是一个TIMESTAMP值
- LAST_ALTERED：上次修改事件的日期和时间。是一个TIMESTAMP值。如果该事件自创建以来从未修改，则此列与CREATED列值相同
- LAST_EXECUTED：事件上次执行的日期和时间。是一个 DATETIME值。如果事件从未执行，则此列值为NULL。LAST_EXECUTED表示事件是从什么时候开始的。因此，ENDS列的时间值总是大于LAST_EXECUTED
- EVENT_COMMENT：事件的注释文本信息，如果事件没有注释信息，则该字段为空串
- ORIGINATOR：创建事件的MySQL Server的server id，用于复制。默认值为0
- CHARACTER_SET_CLIENT：创建事件时的character_set_client系统变量的会话值
- COLLATION_CONNECTION：创建事件时的collation_connection系统变量的会话值
- DATABASE_COLLATION：与事件关联的数据库的排序规则
1.9. 查看客户端会话的状态信息
PROCESSLIST表提供查询一些关于线程运行过程中的状态信息，可以使用如下SQL进行查询
代码语言：javascript复制root@localhost : information_schema 10:40:55> select * from information_schema.PROCESSLIST;
---- ------ ----------- -------------------- --------- ------ ----------- ----------------------------------------------
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
---- ------ ----------- -------------------- --------- ------ ----------- ----------------------------------------------
| 16 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.PROCESSLIST |
---- ------ ----------- -------------------- --------- ------ ----------- ----------------------------------------------
1 row in set (0.00 sec)
那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- ID：连接进程标识符。这与在INFORMATION_SCHEMA.PROCESSLIST表的ID列，performance_schema.threads表的PROCESSLIST_ID列中显示的值是相同的值，都是由CONNECTION_ID（）函数返回的值
- USER：执行语句的MySQL用户名称。如果显示的是“system user”，它指的是由服务器生成的非客户端线程正在执行内部任务。例如主备复制中从库上使用的I/O或SQL线程或延迟行处理程序的线程。“unauthenticated user”指的是已经建立客户端连接但是还没有对客户端连接的用户进行客户端用户的认证的线程。 “event_scheduler”是指监视计划任务调度事件的线程。对于“system user”，在Host列中显示为Null值
- HOST：执行语句的客户端的主机名（除了没有主机信息的“system user”之外）。 SHOW PROCESSLIST的Host列以host_name：client_port格式显示TCP/IP连接的主机名，以便更容易确定哪个客户端正在做什么事情
- DB：客户端连接的默认数据库（如果连接时指定了库名），否则显示为NULL值
- COMMAND：线程正在执行的命令的类型。此列的值对应于C/S协议和Com_xxx状态变量的COM_xxx命令。
- TIME：线程处于当前状态的时间数（以秒为单位）。对于从库SQL线程，该值是最后复制事件的时间戳和从库的实际时间之间的秒数（也可以理解为事件等待的时间）
- STATE：提示线程正在做什么样的操作，事件或状态。大多数状态所对应于的操作都执行的非常快。如果线程停留在某个状态很长时间，则表名该线程可能执行过程中碰到了某个问题，需要进行排查。对于SHOW PROCESSLIST语句，State列的值始终为NULL
- INFO：线程正在执行的语句，如果没有执行任何语句，则显示为NULL。语句可以是发送到服务器的语句，或者如果语句内部调用执行其他语句，即指的最内层调用的语句。例如，如果CALL语句调用存储过程，而存储过程中执行SELECT语句，则Info值将显示存储过程中的SELECT语句
2. 使用InnoDB 层的字典表查询相关的元数据信息
2.1. InnoDB 层查看索引列的信息
INNODB_SYS_FIELDS表提供查询有关InnoDB索引列（字段）的元数据信息，等同于InnoDB数据字典中SYS_FIELDS表的信息
INNODB_SYS_INDEXES表提供查询有关InnoDB索引的元数据信息，等同于InnoDB数据字典内部SYS_INDEXES表中的信息
INNODB_SYS_TABLES表提供查询有关InnoDB表的元数据，等同于InnoDB数据字典中SYS_TABLES表的信息
假设我们需要查询employees库下的InnoDB表dept_emp的索引字段和索引列顺序信息，我们可以使用如下SQL进行查询
代码语言：javascript复制root@localhost : information_schema 11:10:11> select t.name as db_tb_name,i.name as index_name,i.type as index_type,i.N_FIELDS as index_column_numbers,f.name as index_column_name,f.pos as index_position from INNODB_SYS_TABLES as t,INNODB_SYS_INDEXES as i,INNODB_SYS_FIELDS as f where t.TABLE_ID=i.TABLE_ID and i.INDEX_ID=f.INDEX_ID and t.name='employees/dept_emp';
-------------------- ------------ ------------ ---------------------- ------------------- ----------------
| db_tb_name | index_name | index_type | index_column_numbers | index_column_name | index_position |
-------------------- ------------ ------------ ---------------------- ------------------- ----------------
| employees/dept_emp | PRIMARY | 3 | 2 | emp_no | 0 |
| employees/dept_emp | PRIMARY | 3 | 2 | dept_no | 1 |
| employees/dept_emp | emp_no | 0 | 1 | emp_no | 0 |
| employees/dept_emp | dept_no | 0 | 1 | dept_no | 0 |
-------------------- ------------ ------------ ---------------------- ------------------- ----------------
4 rows in set (0.00 sec)
从上面的结果集中，我们可以看到employees库下的dept_emp表有3个索引，一个双列主键索引，两个单列普通索引，那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- db_tb_name(INNODB_SYS_TABLES.name)：表名称。该字符串包含db_name tb_name，例如"test/t1"，该字符串值可能受lower_case_table_names系统参数设置的影响
- index_name(INNODB_SYS_INDEXES.name)：索引名称。索引名称可以在创建时指定，如果不指定InnoDB将隐含地创建名称与列名一致的索引，但索引名称在整个实例中不一定是唯一的(但在每个表中需要唯一)。例如：PRIMARY用于主键索引、GEN_CLUST_INDEX用于表示未指定主键时innodb隐式创建的一个主键索引、以及ID_IND，FOR_IND和REF_IND用于外键约束等
- index_type(INNODB_SYS_INDEXES.type)：表示索引类型的数字ID，0 =二级索引、1 =集群索引、2 =唯一索引、3 =主键索引、32 =全文索引、64 =空间索引、128 =包含虚拟生成列的二级索引
- index_column_numbers(INNODB_SYS_INDEXES.N_FIELDS)：索引key中的列数量。对于GEN_CLUST_INDEX索引(innodb隐式创建的主键)，此字段值为0，因为该索引是使用伪造的列值而非实际表列创建的
- index_column_name(INNODB_SYS_FIELDS.name)：索引列的名称，与INNODB_SYS_COLUMNS表中的NAME字段值相同
- index_position(INNODB_SYS_FIELDS.pos)：索引字段的序号位置，从0开始依次递增。 当一个列被删除时，剩下的列被重新排序，以便该序列无间隙
2.2. InnoDB 层查看表的列相关的信息
INNODB_SYS_TABLES表（参考2.1节）
INNODB_SYS_COLUMNS表可以提供查询有关InnoDB表列的元数据信息，等同于InnoDB数据字典中SYS_COLUMNS表的信息
假设我们需要查询employees库下的dept_emp表的字段信息，我们可以使用如下SQL语句进行查询
代码语言：javascript复制root@localhost : information_schema 03:21:10> select t.name as db_table_name,c.name as column_name,c.pos as column_position,c.mtype as column_type,c.len as column_len from INNODB_SYS_TABLES as t,INNODB_SYS_COLUMNS as c where t.TABLE_ID=c.TABLE_ID and t.name='employees/dept_emp';
-------------------- ------------- ----------------- ------------- ------------
| db_table_name | column_name | column_position | column_type | column_len |
-------------------- ------------- ----------------- ------------- ------------
| employees/dept_emp | emp_no | 0 | 6 | 4 |
| employees/dept_emp | dept_no | 1 | 13 | 12 |
| employees/dept_emp | from_date | 2 | 6 | 3 |
| employees/dept_emp | to_date | 3 | 6 | 3 |
-------------------- ------------- ----------------- ------------- ------------
4 rows in set (0.00 sec)
那么上面的查询结果代表什么意思呢？下面对这些结果做一个简单的解读
- db_table_name(INNODB_SYS_TABLES.name)：表名称。该字符串包含db_name tb_name，例如"test/t1"，该字符串值可能受lower_case_table_names系统参数设置的影响
- column_name(INNODB_SYS_COLUMNS.name)：列名称，名称可以是大写也可以是小写字母，具体取决于lower_case_table_names系统变量的设置
- column_position(INNODB_SYS_COLUMNS.pos)：列在表中的顺序位置，从0开始并依次递增。当一个列被删除时，剩下的列会被重新排序，以使得该序列无间隙
- column_type(INNODB_SYS_COLUMNS.mtype)：列类型的数字ID编号。1 = VARCHAR、2 = CHAR、3 = FIXBINARY、4 = BINARY、5 = BLOB、6 = INT、7 = SYS_CHILD、8 = SYS、9 = FLOAT、10 = DOUBLE、11 = DECIMAL、12 = VARMYSQL、13 = MYSQL、14 = GEOMETRY
- column_len(INNODB_SYS_COLUMNS.len)：列字节长度，例如INT为4字节，BIGINT为8字节。对于多字节字符集中的列字节长度，此长度值是为定义长度所需的最大字节，如VARCHAR（N），如果字符集为laitin1，则该字段的字节长度为N，如果字符集为gbk，则字段长度为2N，如果字符集为utf8，则字段长度为3N
2.3. 查看事务锁等待信息
详见《会话和锁信息查询视图|全方位认识 sys 系统库》一文中"1、innodb_lock_waits,x$innodb_lock_waits"部分
2.4. 查看innodb buffer pool中的热点数据有哪些
详见《内存分配统计视图|全方位认识 sys 系统库》一文中"2、innodb_buffer_stats_by_table,x$innodb_buffer_stats_by_table"部分
| 作者简介
罗小波·数据库技术专家
《千金良方——MySQL性能优化金字塔法则》、《数据生态：MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构，擅长数据库的整体调优，喜好专研开源技术，并热衷于开源技术的推广，在线上线下做过多次公开的数据库专题分享，发表过近100篇数据库相关的研究文章。
全文完。
