mysql information_schema简介
在MySQL中,把 information_schema 看作是一个数据库,确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权 限等。在INFORMATION_SCHEMA中,有数个只读表。它们实际上是视图,而不是基本表,因此,你将无法看到与之相关的任何文件。
information_schema数据库表说明:
SCHEMATA
提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
TABLES
提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema,表类型,表引擎,创建时间等信息。是show tables from schemaname的结果取之此表。
COLUMNS
提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
STATISTICS
提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES(用户权限)
给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
SCHEMA_PRIVILEGES(方案权限)
给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。是非标准表。
TABLE_PRIVILEGES(表权限)
给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
COLUMN_PRIVILEGES(列权限)
给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
CHARACTER_SETS(字符集)
提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
COLLATIONS表
提供了关于各字符集的对照信息。
COLLATION_CHARACTER_SET_APPLICABILITY表
指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS表
描述了存在约束的表。以及表的约束类型。
KEY_COLUMN_USAGE表
描述了具有约束的键列。
ROUTINES表
提供了关于存储子程序(存储程序和函数)的信息。此时,ROUTINES表不包含自定义函数(UDF)。名为"mysql.proc name"的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
VIEWS表
给出了关于数据库中的视图的信息。需要有show views权限,否则无法查看视图信息。
TRIGGERS表
提供了关于触发程序的信息。必须有super权限才能查看该表
FILES表
表提供有关存储MySQL表空间数据的文件信息。
ENGINES表
ENGINES表提供了有关存储引擎的信息。
EVENTS表
EVENTS表提供了有关定时事件(scheduled events)的信息,events类似与linux crontab计划任务,用于时间触发。通过单独或调用存储过程使用,在某一特定的时间点,触发相关的SQL语句或存储过程。
GLOBAL_STATUS & SESSION_STATUS & GLOBAL_VARIABLES & SESSION_VARIABLES
GLOBAL_STATUS和SESSION_STATUS表提供有关服务器状态变量的信息。 它们的内容对应于SHOW GLOBAL STATUS和SHOW SESSION STATUS语句产生的信息
GLOBAL_VARIABLES和SESSION_VARIABLES表提供有关服务器状态变量的信息。 它们的内容对应于SHOW GLOBAL VARIABLES和SHOW SESSION VARIABLES语句所产生的信息。
从MySQL 5.7.6开始,这里描述的4张表中的信息也可在Performance Schema中获取。 INFORMATION_SCHEMA表不推荐优先于Performance Schema表,并将在未来的MySQL版本中被删除。(从mysql 5.7.6开始 information_schema.global_status 已经开始被舍弃,为了兼容性,此时需要打开 show_compatibility_56)
TABLESPACES
TABLESPACES表提供有关active表空间的信息。
INFORMATION_SCHEMA.TABLESPACES表不提供有关InnoDB表空间的信息。对于InnoDB表空间元数据,请参阅INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES。 从MySQL 5.7.8开始,INFORMATION_SCHEMA.FILES表还提供了InnoDB表空间的元数据。
REFERENTIAL_CONSTRAINTS
REFERENTIAL_CONSTRAINTS表提供有关外键的信息。
PROFILING
PROFILING表提供语句分析信息。 其内容对应于SHOW PROFILES和SHOW PROFILE声明所产生的信息。
warning:INFORMATION_SCHEMA.PROFILING 在以后的版本将被移除,请使用 Performance Schema 代替。
OPTIMIZER_TRACE
OPTIMIZER_TRACE表提供了优化器跟踪功能产生的信息。要启用跟踪(tracking),使用optimizer_trace系统变量。
PARAMETERS
PARAMETERS表提供有关存储过程和函数参数的信息,以及有关存储函数的返回值的信息。 参数信息与mysql.proc表中param_list列的内容类似
PROCESSLIST
PROCESSLIST表提供有关正在运行的线程信息。
PLUGINS
PLUGINS表提供了有关服务器插件的信息。
PARTITIONS
PARTITIONS表提供有关表分区的信息。
innodb_trx
当前运行的所有事务
innodb_locks
当前出现的锁
innodb_lock_waits
锁等待的对应关系
INNODB_SYS_DATAFILES
这张表就是记录的表的文件存储的位置和表空间的一个对应关系(INNODB)
INNODB_FT_CONFIG
这张表存的是全文索引的信息
INNODB_SYS_VIRTUAL
表存储的是INNODB表的虚拟列的信息。
INNODB_CMP、INNODB_CMP_RESET
存储的是关于压缩INNODB信息表的时候的相关信息
INNODB_FT_BEING_DELETED、INNODB_FT_DELETED
这张表是INNODB_FT_DELETED的一个快照,只在OPTIMIZE TABLE 的时候才会使用。
INNODB_CMP_PER_INDEX、INNODB_CMP_PER_INDEX_RESET
这两个表存储的是关于压缩INNODB信息表的时候的相关信息,有关整个表和索引信息都有.我们知道对于一个INNODB压缩表来说,不管是数据还是二级索引都是会被压缩的,因为数据本身也可以看作是一个聚集索引。
INNODB_CMPMEM、INNODB_CMPMEM_RESET
这两个表是存放关于MySQL INNODB的压缩页的buffer pool信息,但是要注意一点的就是,用这两个表来收集所有信息的表的时候,是会对性能造成严重的影响的,所以说默认是关闭状态的。
INNODB_BUFFER_PAGE_LRU、INNODB_BUFFER_PAGE
维护了INNODB LRU LIST的相关信息
INNODB_TEMP_TABLE_INFO
这个表惠记录所有的INNODB的所有用户使用到的信息,但是只能记录在内存中和没有持久化的信息。
INNODB_SYS_INDEXES
提供相关INNODB表的索引的相关信息,和SYS_INDEXES 这个表存储的信息基本是一样的,只不过后者提供的是所有存储引擎的索引信息,后者只提供INNODB表的索引信息。
INNODB_SYS_TABLES
张表依赖的是SYS_TABLES数据字典中拉取出来的。此表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用)
提供的是关于INNODB的表空间信息,其实和SYS_TABLESPACES 中的INNODB信息是一致的。
INNODB_SYS_FIELDS
这个表记录的是INNODB的表索引字段信息,以及字段的排名
INNODB_FT_DEFAULT_STOPWORD
这个表存放的是stopword 的信息,是和全文索引匹配起来使用的,和innodb的 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的,这个STOPWORD必须是在创建索引之前创建,而且必须指定字段为varchar。stopword 也就是我们所说的停止词,全文检索时,停止词列表将会被读取和检索,在不同的字符集和排序方式下,会造成命中失败或者找不到此数据,这取决于停止词的不同的排序方式。我们可以使用这个功能筛选不必要字段。
INNODB_FT_INDEX_TABLE
这个表存储的是关于INNODB表有全文索引的索引使用信息的,同样这个表也是要设置innodb_ft_aux_table以后才能够使用的,一般情况下是空的
INNODB_FT_INDEX_CACHE
这张表存放的是插入前的记录信息,也是为了避免DML时候昂贵的索引重组
INNODB_SYS_TABLESPACES
这张表依赖的是SYS_TABLES数据字典中拉取出来的。此表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用)
提供的是关于INNODB的表空间信息,其实和SYS_TABLESPACES 中的INNODB信息是一致的。
INNODB_METRICS
提供INNODB的各种的性能指数,是对INFORMATION_SCHEMA的补充,收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的:innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。
INNODB_SYS_FOREIGN_COLS
这个表也是存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的
INNODB_BUFFER_POOL_STATS
表提供有关INNODB 的buffer pool相关信息,和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。
INNODB_SYS_COLUMNS
存放的是INNODB的元数据, 他是依赖于SYS_COLUMNS这个统计表而存在的。
INNODB_SYS_FOREIGN
存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的,只不过是单独对于INNODB来说的
INNODB_SYS_TABLESTATS
这个表就比较重要了,记录的是MySQL的INNODB表信息以及MySQL优化器会预估SQL选择合适的索引信息,其实就是MySQL数据库的统计信息
这个表的记录是记录在内存当中的,是一个内存表,每次重启后就会重新记录,所以只能记录从上次重启后的数据库统计信息。有了这个表,我们对于索引的维护就更加方便了,我们可以查询索引的使用次数,方便清理删除不常用的索引,提高表的更新插入等效率,节省磁盘空间。