【MySQL】information_schema的了解与应用-1

一、什么是information_schema

information_schema提供了对数据库元数据、统计信息以及有关MySQL Server信息的访问（例如：数据库名或表名、字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典或系统目录。

在MySQL中通过使用show往往可以和通过查询information_schema库下的表得到类似的数据信息，但是通过select查询有以下优点

• 符合‘codd法则’，所有访问都是基于表实现的。
• 通过select查询可以方便进行数据的过滤、排序、联结等操作。
• 和其它数据库中的操作具有互操作性。

访问information_schema的权限

• 所有用户都有访问information_schema下的表权限（但只能看到这些表中与用户具有访问权限的对象相对应的数据行），但只能访问Server层的部分数据字典表。Server层的部分数据字典表以及InnoDB层的数据字典表需要额外授权才能访问，如果用户权限不足，当查询Server层的数据字典表时将不会返回任何数据，或者对某个列没有权限访问时，该列返回NULL值；当查询InnoDB层的数据字典表时将直接拒绝访问
• 从information_schema中查询相关数据需要的权限也适用于SHOW语句。无论使用哪种查询方式，都必须拥有访问某个对象的权限才能看到相关的数据。

二、information_schema组成对象

information_schema系统库下的表都是使用的memory和innodb存储引擎，而且都是临时表不是持久的，所有的数据在数据库重启后都会消失。information_schema也是唯一一个系统文件中没有对应库表目录和文件的系统库。
下图查看mysql的目录有sys和performance_schema系统库，但是没有information_schema系统库

1、server层的统计信息字典表

• COLUMNS
  提供查询表的列信息，该表innodb存储引擎临时表。
  查询onepiece表的列的信息，包括数据类型，索引。

# f_id为外键
alter table peng_db.onepiece add constarint fruitid foreign key(f_id) references peng_db.fruit(fruit_id);

• KEY_COLUMN_USAGE
  可以查询哪些索引列存在约束条件，包括主键索引，唯一索引，外键等约束信息。该表为memory引擎临时表。

select constraint_schema,constraint_name,table_name,column_name,referenced_table_schema, referenced_table_name,referenced_column_name from key_column_usage where table_name='onepiece'\G;

查询的结果中只有主键，唯一索引和外键，不包含普通索引。对于外键约束会显示主表的消息

• REFERENTIAL_CONSTRAINTS
  查询外键约束的一些信息，该表为memory引擎临时表。
  

• STATISTICS
  提供关于索引的统计信息。该表为memory引擎临时表。
  会显示所有的索引，而且会显示索引的存储引擎，默认是BTree。
  

• TABLE_CONSTRAINTS
  类似于key_column_usage表中存储的数据，但是该表中没有存储约束引用的库列表的消息。
  也就是referenced_table_schema,referenced_table_name,referenced_column_name这些信息。该表为memory引擎临时表。
  

• FILES
  提供查询与MySQL的数据表空间文件相关的信息，包含与InnoDB存储引擎和NDB存储引擎相关的数据文件信息。（不是很懂）该表为memory引擎临时表。

• ENGINES
  提供查询MYSQL执行的引擎相关信息。刚开始学performance_schema系统库的时候就使用过该表查看performance_schema引擎。该表为memory引擎临时表。
  到目前为止了解的引擎就有Innodb，myisam，performance_schema，memory
  

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

• SHEMATA
  提供查询MYSQL SERVER中的数据库列表信息。
  

2、server层表级别字典表

• views
  查询数据库中的视图相关信息，查询该表的用户需要持有show view权限。该表为InnoDB引擎临时表。
  之前学到过sys中有许多视图，可以查询schema_redundant_indexes视图的相关信息
  

• triggers
  提供查询数据库中触发器的相关信息，查询用户必须要有trigger权限，该表为InnoDB引擎临时表。
  sys表中有一些触发器，例如sys.sys_config_insert_set_user
  

• tables
  提供查询数据库中表的基本信息，该表为memory引擎临时表。
  
  下面的就不截图啦，大致都一样

• routines
  提供查询关于存储过程和存储函数的信息（不包括用户自定义函数）。该表中的信息与mysql.proc中记录的信息相对应（如果该表中有值的话）。该表为InnoDB引擎临时表。

• PARTITIONS
  提供查询关于分区表的信息。 该表为InnoDB引擎临时表。

• EVENTS
  提供查询与计划任务事件相关的信息。 该表是InnoDB引擎临时表。

• PARAMETERS
  提供有关存储过程和函数的参数信息，以及有关存储函数的返回值信息。这些参数信息与mysql.proc表中的param_list列记录的内容类似。该表为InnoDB引擎临时表。

3、server层混杂信息字典表

• GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、SESSION_VARIABLES
  提供查询全局、会话级别的状态变量与系统变量信息。这些表为Memory引擎临时表。
  默认情况下这几个表都是无法访问的，需要开启show_campatibility_56参数
  
  之前学performance_chema系统库时一些history表存储数据的行数有最大值，可以通过变量表查询
  

• OPTIMIZER_TRACE
  提供优化程序跟踪功能产生的信息。跟踪功能默认是关闭的，使用optimizer_trace系统变量启用跟踪功能。如果开启该功能，则每个会话只能跟踪它自己执行的语句，不能看到其他会话执行的语句，且每个会话只能记录最后一条跟踪的SQL语句。该表为InnoDB引擎临时表。

• PLUGINS
  提供查询关于MySQL Server支持哪些插件的信息。该表为InnoDB引擎临时表。

• PROCESSLIST
  提供查询一些关于线程运行过程中的状态信息。该表为InnoDB引擎临时表。
  该表中的数据可以直接通过show processlist命令打印出来
  

• PROFILING
  提供查询关于语句性能分析的信息。其记录内容对应于SHOW PROFILES和SHOW PROFILE语句产生的信息。该表只有在会话变量profiling=1时才会记录语句性能分析信息，否则该表不记录。注意：从MySQL 5.7.2开始，此表不再推荐使用，在未来的MySQL版本中删除，改用Performance Schema代替。该表为Memory引擎临时表。

• CHARACTER_SETS
  提供查询MySQL Server支持的可用字符集。该表为Memory引擎临时表。

• COLLATIONS
  提供查询MySQL Server支持的可用校对规则。该表为Memory引擎临时表

• COLLATION_CHARACTER_SET_APPLICABILITY
  提供查询MySQL Server中哪种字符集适用于什么校对规则。查询结果集相当于从SHOW COLLATION获得的结果集的前两个字段值。目前并没有发现该表有太大的作用。该表为Memory引擎临时表。

4 、InnoDB层的系统字典表

感觉没有必要深入了解，其实之前serve层表级别字典表中存储引擎为InnoDB的数据就和这里类似。
这一类型的数据库表都是INNODB_SYS_*的结构。
INNODB_SYS_DATAFILES：表空间数据。
INNODB_SYS_VIRTUAL：虚拟生成列相关联的列信息。
INNODB_SYS_INDEXES：索引的元素据信息。
INNODB_SYS_TABLES：表信息
INNODB_SYS_FIELDS：索引字段信息
INNODB_SYS_TABLESPACES
INNODB_SYS_FOREIGN_COLS
INNODB_SYS_COLUMNS：字段信息
INNODB_SYS_FOREIGN：外键信息
INNODB_SYS_TABLESTATS
以外键信息为例，通过Innodb层的表和server层表级别信息表查出来的数据基本一致。

5、InnoDB层的锁、事务统计信息字典表。

另开新篇啦，后面再补连接