介绍一些MySQL information_schema库相关知识

下面一起来了解下MySQL information_schema库相关知识，相信大家看完肯定会受益匪浅，文字在精不在多，希望MySQL information_schema库相关知识这篇短内容是你想要的。

mysql> show create table test.test\G

1 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'  show  status 

SESSION_VARIABLES                      -- 不可查，see the documentation for 'show_compatibility_56'  show  variables 

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的事务的锁等待的状态

看完MySQL information_schema库相关知识这篇文章后，很多读者朋友肯定会想要了解更多的相关内容，如需获取更多的行业信息，可以关注我们的行业资讯栏目。