performance_schema 和 information_schema 的区别

performance_schema

performance_schema这个库提供的是MySQL数据库性能的一些指标，主要保存 MySQL 服务器运行过程中的一些状态信息， 算是对MySQL 服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

该功能可以显示关闭，也就是在my.cnf文件中配置performance_schema=off。

查看是否开启：show variables like 'performance_schema'; 5.6版本之后是默认开启的

与information_schema不同，performance_schema中的表是小写的。

其中的表的含义，记录什么内容，可以参考：

查看表的默认引擎:

1
2
3

select table_catalog ,table_schema ,table_name ,engine
from information_schema.tables
where  table_schema = 'performance_schema';

查找执行最多的SQL：

1

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR desc LIMIT 1\G

此外其他列的含义如下，可以视情况加入select list

  • AVG_TIMER_WAIT SQL的平均响应时间
  • SUM_SORT_ROWS SQL排序记录数
  • SUM_ROWS_EXAMINED SQL扫描记录数
  • SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES SQL使用临时表
  • SUM_ROWS_SENT SQL返回结果集

 通过上述指标我们可以间接获得某类SQL的逻辑IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，网络带宽(SUM_ROWS_SENT)的对比，但还无法得到某类SQL的物理IO消耗，以及某类SQL访问数据的buffer命中率。

查找响应时间最长的SQL：

1

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT desc LIMIT 1\G

查找逻辑IO最高的表、文件（热数据）：

1
2
3

SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE 
FROM file_summary_by_instance 
ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2\G

查找使用最多的索引：

1
2
3

SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE 
FROM table_io_waits_summary_by_index_usage 
ORDER BY SUM_TIMER_WAIT DESC limit 1;

查找未使用过的索引：

1
2
3

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME 
FROM table_io_waits_summary_by_index_usage 
WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

查找最耗时的等待：

1
2
3

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT 
FROM events_waits_summary_global_by_event_name 
WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;

更多操作参考：

information_schema

information_schema是mysql数据库的元信息库，里面的表存储了mysql的一些信息。information_schema里面的表本质是一些视图，他们使用了不同的存储引擎，并非全都是默认的innodb存储引擎。通过下面的SQL语句可以看出，有些表是memory的存储引擎，有些表是innodb的存储引擎。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

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)

information_schema数据库表说明:

1、SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

2、TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

3、COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

4、STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

5、USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

6、SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

7、TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

8、COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

9、CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

10、COLLATIONS表：提供了关于各字符集的对照信息。

11、COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

12、TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

13、KEY_COLUMN_USAGE表：描述了具有约束的键列。

14、ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

15、VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

16、TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

查看引擎information_schema.ENGINS          对应于 show engines;     

查看数据库information_schema.SCHEMATA        对应于 show databases;

查看表information_schema.TABLES          对应于 show tables;

查看列information_schema.COLUMNS         对应于show columns from table_name

查看索引information_schema.STATISTICS      对应于show index from table_name

查看线程information_schema.PROCESSLIST     对应于 show processlist

show语句：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

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

参考