MySQL information_schema库学习
mysql> show create table test.test\G1 row in *************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`a` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8set (0.00 sec)
parameters
information_schema
一、关于字符集和排序规则相关的系统表
CHARACTER_SETS -- 字符集
COLLATIONS -- 字符集校验集
COLLATION_CHARACTER_SET_APPLICABILITY-- 字符集和字符校验集的关系 这些列等效于SHOW COLLATION的前两个显示字段。
字符集（character sets）存储字符串，是指人类语言中最小的表义符号。例如'A'、'B'等；
排序规则（collations）规则比较字符串，collations是指在同一字符集内字符之间的比较规则
每个字符序唯一对应一种字符集，但一个字符集可以对应多种字符序，其中有一个是默认字符序(Default Collation)
MySQL中的字符序名称遵从命名惯例：以字符序对应的字符集名称开头；以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如：在字符序"utf8_general_ci"下，字符"a"和"A"是等价的
看一下有关于字符集和校对相关的MySQL变量：
character_set_client：客户端来源数据使用的字符集
character_set_connection：连接层字符集
character_set_database：当前选中数据库的默认字符集
character_set_system：系统元数据(字段名等)字符集
character_set_server：默认的内部操作字符集
character_set_results：查询结果字符集
再看一下MySQL中的字符集转换过程：
（1）MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
（2）进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：
使用每个数据字段的CHARACTER SET设定值；
若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
若上述值不存在，则使用character_set_server设定值。
（3）将操作结果从内部操作字符集转换为character_set_results。
二、权限相关的表
SCHEMA_PRIVILEGES -- 提供了数据库的相关权限，信息来自mysql.db
TABLE_PRIVILEGES -- 提供的是表权限相关信息，信息来自mysql.tables_priv 表中加载的
COLUMN_PRIVILEGES -- 给出了关于列权限的信息。该信息源自mysql.columns_priv授权表
USER_PRIVILEGES -- 提供的是表权限相关信息，信息来自mysql.user 表
三、存储数据库系统的实体对象的一些表
COLUMNS -- select * from COLUMNS where TABLE_NAME='test' \G等价于 desc test.test或者 show columns from test.test;
INNODB_SYS_COLUMNS --存放的是INNODB的元数据， 他是依赖于SYS_COLUMNS这个统计表而存在的。
ENGINES -- 支持的engine和默认的engine
EVENTS -- 等价于 show events 或者mysql.event，备份是要加上--event，主库设置event_scheduler=ON，从库event_scheduler=OFF
FILES -- 表空间文件，MySQL的表空间中的数据存储的文件的信息，文件存储的位置，
PARAMETERS -- 参数表存储了一些存储过程和方法的参数，以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。
PLUGINS -- 基本上是MySQL的插件信息，是否是活动状态等信息。SHOW PLUGINS的信息来自此
ROUTINES --关于存储过程和方法function的一些信息以及帮助文档，不过这个信息是不包括用户自定义的，只是系统的一些信息。mysql.proc name
SCHEMATA --这个表提供了实例下有多少个数据库，而且还有数据库默认的字符集
TRIGGERS 这个表记录的就是触发器的信息，包括所有的相关的信息。系统的和自己用户创建的触发器。
VIEWS -- 视图的信息，也是系统的和用户的基本视图信息。
四、约束外键等相关的一些表
REFERENTIAL_CONSTRAINTS --提供的外键相关的信息，而且只提供外键相关信息
TABLE_CONSTRAINTS --提供的是相关的约束信息,比较全面
INNODB_SYS_FOREIGN_COLS --存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的
INNODB_SYS_FOREIGN --存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的，只不过是单独对于INNODB来说的
KEY_COLUMN_USAGE --描述了具有约束的键列。
五、关于管理的一些的一些表：
GLOBAL_STATUS -- 不可查，see the documentation for 'show_compatibility_56'show global status
GLOBAL_VARIABLES -- 不可查，see the documentation for 'show_compatibility_56'show global variables
SESSION_STATUS -- 不可查，see the documentation for 'show_compatibility_56'showstatus
SESSION_VARIABLES -- 不可查，see the documentation for 'show_compatibility_56'showvariables
PARTITIONS -- MySQL分区表相关的信息
PROCESSLIST --SHOW PROCESSLIST 数据来自此表
INNODB_CMP_PER_INDEX -- 存储关于压缩INNODB表的相关信息
INNODB_CMP_PER_INDEX_RESET -- 存储关于压缩INNODB表的相关信息
INNODB_CMPMEM -- 存放关于MySQL INNODB的压缩页的buffer pool信息，和show variables like 'innodb_cmp_per_index_enabled';有关，打开影响性能
INNODB_CMPMEM_RESET -- 存放关于MySQL INNODB的压缩页的buffer pool信息，和show variables like 'innodb_cmp_per_index_enabled';有关，打开影响性能
INNODB_BUFFER_POOL_STATS --供有关INNODB 的buffer pool相关信息，和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。
INNODB_BUFFER_PAGE_LRU -- 维护了INNODB LRU LIST的相关信息
INNODB_BUFFER_PAGE --存放的是buffer里面缓冲的页数据，查询这个表会对性能产生很严重的影响
INNODB_SYS_DATAFILES --这张表就是记录的表的文件存储的位置和表空间的一个对应关系
INNODB_TEMP_TABLE_INFO --这个表记录所有的INNODB的所有用户使用到的信息，但是只能记录在内存中和没有持久化的信息。
INNODB_METRICS -- 提供INNODB的各种的性能指数，是对INFORMATION_SCHEMA的补充，收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的：innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。
INNODB_SYS_VIRTUAL --存储的是INNODB表的虚拟列的信息，
INNODB_CMP --存储的是关于压缩INNODB信息表的时候的相关信息
INNODB_CMP_RESET--存储的是关于压缩INNODB信息表的时候的相关信息
六、关于表信息和索引信息的一些表
TABLES -- 记录的数据库中表的信息，其中包括系统数据库和用户创建的数据库。show table status like 'test1'\G的来源就是这个表
TABLESPACES -- 这个表是不提供关于innodb的表空间信息的，对于我们来说并没有太大作用，因为我们生产库是强制INNODB的；
INNODB_SYS_TABLES-- 此表提供了有关表格的格式和存储特性，包括行格式，压缩页面大小位级别的信息
INNODB_SYS_TABLESPACES -- 提供的是关于INNODB的表空间信息，其实和SYS_TABLESPACES 中的INNODB信息是一致的。
STATISTICS -- 提供关于表的索引信息，所有索引的相关信息。
INNODB_SYS_INDEXES --提供相关INNODB表的索引的相关信息，和 SYS_INDEXES 这个表存储的信息基本是一样的，
INNODB_SYS_TABLESTATS -- 重要，记录的是MySQL的INNODB表信息以及MySQL优化器会预估SQL选择合适的索引信息，其实就是MySQL数据库的统计信息
INNODB_SYS_FIELDS --存放INNODB的表索引字段信息，以及字段的排名
INNODB_FT_CONFIG--存放的是全文索引的信息
INNODB_FT_DEFAULT_STOPWORD-- 存放的是stopword 的信息,是和全文索引匹配起来使用的
INNODB_FT_INDEX_TABLE --存储的是关于INNODB表有全文索引的索引使用信息的，同样这个表也是要设置innodb_ft_aux_table以后才能够使用的，一般情况下是空的
INNODB_FT_INDEX_CACHE --存放的是插入前的记录信息，也是为了避免DML时候昂贵的索引重组
七、关于MySQL优化相关的一些表
OPTIMIZER_TRACE -- 查看执行计划的选择，set optimizer_trace="enabled=on";SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; desc select * ...; select OPTIMIZER_TRACE\G
PROFILING --SHOW PROFILES,不能查看和剖析其他连接的语句，以及剖析时所引起的消耗。profiling_history_size 设为0等价于关闭分析功能
INNODB_FT_BEING_DELETED --INNODB_FT_BEING_DELETED 这张表是INNODB_FT_DELETED的一个快照
INNODB_FT_DELETED -- OPTIMIZE TABLE 的时候才会使用
八、关于MySQL事物和锁的相关的一些表
INNODB_LOCKS INNODB_LOCKS表主要包含了InnoDB事务锁的具体情况，包括事务正在申请加的锁和事务加上的锁。
INNODB_TRX INNODB_TRX表主要是包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务
INNODB_LOCK_WAITS INNODB_LOCK_WAITS表包含了blocked的事务的锁等待的状态
参考：https://www.cnblogs.com/shengdimaya/p/6920677.html
页:
[1]