information_schema和performance_schema的一点知识
information_schema和performance_schema的一点知识
//
information_schema和performance_schema的一点知识
//
今天调了一天接口，整个人比较懵逼，明天早上还有维护，晚上写点东西早点睡觉了，累了，赶快放假吧~
information_schema是mysql数据库的元信息库，里面的表存储了mysql的一些信息。information_schema里面的表本质是一些视图，他们使用了不同的存储引擎，并非全都是默认的innodb存储引擎。通过下面的SQL语句可以看出，有些表是memory的存储引擎，有些表是innodb的存储引擎。
mysql> select table_schema,table_name,engine,table_type from information_schema.tables where table_schema='information_schema';
+--------------------+---------------------------------------+--------+-------------+
| table_schema | table_name | engine | table_type |
+--------------------+---------------------------------------+--------+-------------+
| information_schema | CHARACTER_SETS | MEMORY | SYSTEM VIEW || information_schema | COLLATIONS | MEMORY | SYSTEM VIEW |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | MEMORY | SYSTEM VIEW || information_schema | COLUMNS | InnoDB | SYSTEM VIEW |
| information_schema | COLUMN_PRIVILEGES | MEMORY | SYSTEM VIEW || information_schema | ENGINES | MEMORY | SYSTEM VIEW |
| information_schema | EVENTS | InnoDB | SYSTEM VIEW || information_schema | FILES | MEMORY | SYSTEM VIEW |
| information_schema | GLOBAL_STATUS | MEMORY | SYSTEM VIEW || information_schema | GLOBAL_VARIABLES | MEMORY | SYSTEM VIEW |
| information_schema | KEY_COLUMN_USAGE | MEMORY | SYSTEM VIEW || information_schema | TRIGGERS | InnoDB | SYSTEM VIEW || information_schema | USER_PRIVILEGES | MEMORY | SYSTEM VIEW |
| information_schema | VIEWS | InnoDB | SYSTEM VIEW || information_schema | INNODB_FT_INDEX_TABLE | MEMORY | SYSTEM VIEW |
| information_schema | INNODB_FT_INDEX_CACHE | MEMORY | SYSTEM VIEW || information_schema | INNODB_SYS_TABLESPACES | MEMORY | SYSTEM VIEW |
| information_schema | INNODB_METRICS | MEMORY | SYSTEM VIEW || information_schema | INNODB_SYS_FOREIGN_COLS | MEMORY | SYSTEM VIEW |
| information_schema | INNODB_CMPMEM | MEMORY | SYSTEM VIEW || information_schema | INNODB_BUFFER_POOL_STATS | MEMORY | SYSTEM VIEW |
| information_schema | INNODB_SYS_COLUMNS | MEMORY | SYSTEM VIEW || information_schema | INNODB_SYS_FOREIGN | MEMORY | SYSTEM VIEW |
| information_schema | INNODB_SYS_TABLESTATS | MEMORY | SYSTEM VIEW |
+--------------------+---------------------------------------+--------+-------------+
xxx rows in set (0.00 sec)
如果我们使用show create table的命令来查看表，你可以看到，表基本上都是temporary table，如下：
mysql> show create table tables\G
*************************** 1. row ***************************
Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
information_schema在磁盘上没有对应的元文件，在我们初始化一个实例的时候，我们只能看到mysql表以及performance_schema表的磁盘文件，information_schema没有对应的磁盘文件，它也不能被执行insert、update、delete等操作。
基本上，MySQL中的show开头的语句，我们都可以通过information_schema中的对应的表找到它的等价内容，例如show tables语句就可以使用select * from information_schema.tables语句来替代，而且它的信息比show tables语句更全。例如以下的show 语句：
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW INDEX
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES
除此之外，information_schema中还记录了一些关于innodb 信息的表，幸运的是，MySQL官方文档上对于每一张表都做了介绍了说明，详情可以参考官方文档。
performance_schema这个库提供的是MySQL数据库性能的一些指标，该功能可以显示关闭，也就是在my.cnf文件中配置performance_schema=off,关于这个知识点，之前还出现过一个小问题，就是performance_schema引起的bug，可以参看文章：维护过程中积累的一点经验
与information_schema不同，performance_schema中的表是小写的，该系统数据库中的表还是值得研究的，里面包含了很多性能参数，后续有时间，我们可以抓出一些来分析分析。
还有一个奇怪的现象，就是performance_schema还被视为一个存储引擎，加入我们输入show engines，可以看到如下结果：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.05 sec)
另外，performation_schema能够检测的性能比较多，这些性能检测不是全部打开的，如果我们想要打开某一个功能，可以使用下面的语句，补充相应的where条件即可：
mysql> UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 560 rows affected (0.04 sec)
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES';
Query OK, 10 rows affected (0.00 sec)
时间原因，就这么多吧，更多详情，情参考官方文档。