专栏 | MySQL 5.7系列之sys schema(2) by吴炳锡

作者：吴炳锡（知数堂培训联合创始人）

0、导读

MySQL 5.7引入了sys schema，有了它，我们排查分析一些问题时将更得心应手。sys schema里主要存储的是视图、存储过程、函数等。

视图：用于结果的汇总展示及配置持久化；

存储过程：用于对Performance schema的控制及收集；

函数：对于Performance schema的配置及数据格式化。

本文约2000字，阅读时间约10分钟。

1、本文目标

上篇我们只是先简单介绍了下sys schema可以做什么，以及几个基础概念，本文继续深入介绍sys schema，回答上篇我们留下的几个问题：

• 哪个用户或者来自哪个IP的客户端使用了最多的资源；

• 数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的；

• 数据库中哪些SQL被频繁执行；

• 哪个文件产生了最多的IO，读多，还是写的多；

• 哪个表上的IO请求最多；

• 哪个表被访问的最多；

• 哪些语句延迟比较严重；

• 哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表；

• 哪个表占用了最多的buffer pool；

• 每个库（database）占用多少buffer pool；

• 每个连接分配多少内存；

• MySQL内部有多个线程在运行；

• 其他...

2、基本视图介绍

• host_summary视图

字段名	意义
host	从哪个客户机上连过来。如果是NULL，表示内部的进程
statements	该客户机共执行了多少语句
statement_latency	该客户机发来等待语句执行的时间
statement_avg_latency	该客户机等待语句执行的平均时间
table_scans	该客户机发生全表扫描的次数
file_ios	该客户机上IO事件请求的次数
file_io_latency	该客户机请求等待IO的时间
current_connections	该客户机当前的连接数
total_connections	该客户机连接DB共有多少次
unique_user	该客户机上有几个不同用户名的帐户连接过来
current_memory	该客户机上当前连接占用的内存
total_memory_allocated	该客户机上的请求总共使用的内存量（历史累计值）

• io_global_by_file_by_bytes视图

file	被操作的文件名
count_read	总共有多少次读
total_read	总共读了多少字节
avg_read	平均每次读的字节数
count_write	总共有多少次写
total_written	总共写了多少字节
avg_write	平均每次写的字节大小
total	读和写总共的IO大小
write_pct	写请求占总IO请求中的百分比（就是通常所说的读写比）

• user_summary视图

user	客户端连接过来的用户名，如果是NULL，表示内部进程
statements	该用户执行了多少SQL
statement_latency	该用户执行SQL的总延迟时间
statement_avg_latency	该用户执行SQL的平均延迟时间
table_scans	该用户执行SQL时发生全表扫描的次数
file_ios	该用户发生的IO请求总量
file_io_latency	该用户发生的IO请求总延迟时间
current_connections	该用户当前的连接
total_connections	该用户总的连接数
unique_hosts	该用户从几个不同客户机连接过来
current_memory	该用户当前占用的内存
total_memory_allocated	该用户总共申请到的内存（历史累计值）

• memory_global_total视图

total_allocated

Server总共分配出去的内存大小

• memory_by_thread_by_current_bytes视图

thread_id	MySQL内部线程ID，可以和session视图中的thd_id关联
user	当前线程是哪个用户创建
current_count_used	当前线程正在使用且未释放的内存块（内存块不是固定大小的）
current_allocated	当前线程正在使用且未释放的内存块大小（字节）
current_avg_alloc	当前线程每个内存块平均分配内存大小（字节）
current_max_alloc	当前线程单次曾经分配的最多内存大小（字节）
total_allocated	当前线程总共分配的内存大小

• statement_analysis视图

query	格式化后的SQL（将SQL中的参数替换成？）
db	在哪个DB中执行，如果为NULL表示在任何DB
full_scan	是否使用了全表扫描
exec_count	该SQL被执行的总次数
err_count	发生错误的次数
warn_count	发生警告的次数
total_latency	总共发生延迟的时间
max_latency	最大延迟的时间
avg_latency	平均延迟的时间
lock_latency	因锁等待占用的总时间
rows_sent	执行该SQL返回的总行数
rows_sent_avg	执行该SQL平均返回的行数
rows_examined	执行该SQL扫描的总行数
rows_examined_avg	执行该SQL平均每次扫描的行数
tmp_tables	该SQL生成内存临时表的总次数
tmp_disk_tables	该SQL生成磁盘文件临时表的总次数
rows_sorted	该SQL总共排序的行数
sort_merge_passes	用于排序中合并的总次数
digest	该语句的HASH值
first_seen	该SQL最早出现的时间
last_seen	该SQL最近出现的时间

• processlist视图

thd_id	内部线程ID
conn_id	连接的ID（对应show processlist中的"Id"列）
user	该线程创建的用户名
db	连接的DB，如果是NULL表示后台线程
command	Client发起命令的类型提示
state	命令的状态
time	基于上面state停留的时间
current_statement	该线程执行的语句
statement_latency	语句运行总共占用时间
progress	该语句运行完成的百分比
lock_latency	该语句用于锁等待的时间
rows_examined	该语句扫描的行数
rows_send	该语句返回的行数
rows_affected	该语句影响到的行数(写入语句)
tmp_tables	形成内存临时表的次数
tmp_disk_tables	形成磁盘临时表的次数
full_scan	全表描扫的次数
last_statement	上一条被执行的SQL
last_statement_latency	上一条被执行的SQL时延
current_memory	当前线程占用的内存
last_wait	上一次等待事件
last_wait_latency	上一次等待时间时延
source	上一次等待事件对应的源码位置
trx_latency	事务时延
trx_state	当前事务状态
pid	对应到系统里的pid
program_name	连接进来的标识名

• session视图

session视图和processlist视图基本一样，只是把后台线程过滤掉。

• innodb_buffer_stats_by_schema视图

object_schema	schema名
allocated	该schema上分配的buffer pool大小（字节）
data	该schema中缓存的数据大小（字节）
pages	该schema中缓存的data page数
pages_hashed	该schema中分配了多少hashed page
pages_old	该schema中的old page数量
rows_cached	该schema中缓存的row data数量

• innodb_buffer_stats_by_table视图

和innodb_buffer_stats_by_schema基本一致，只是比上面那个视图多了一个ojbect_name用于指定表名。

3、sys schema运用

上面把几个基础的视图都介绍完了，接下来就可以回答上篇提出的那些问题了。

• 哪个用户或者来自哪个IP的客户端使用了最多的资源

查看每个客户端IP过来的连接消耗了多少资源：

查看某个数据文件上发生了多少IO请求：

查看哪个用户消耗了多少资源：

查看总共分配了多少内存：

• 数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的

查看当前连接情况：

select host, current_connections, statements from host_summary;

查看当前正在执行的SQL：

select  conn_id, user, current_statement, last_statement from session;

和执行show full processlist的效果相当。

• 数据库中哪些SQL被频繁执行

执行下面命令查询TOP 10最热SQL：

select db,exec_count,query from statement_analysis order by exec_count desc limit 10;

• 哪个文件产生了最多的IO，读多，还是写的多

select * from io_global_by_file_by_bytes limit 10;

• 哪个表上的IO请求最多

select * from io_global_by_file_by_bytes where file like '%ibd' order by total desc limit 10;

• 哪个表被访问的最多

先访问statement_analysis，根据热门SQL排序找到相应的数据表。

• 哪些语句延迟比较严重

查看 statement_analysis 中 avg_latency的最高的SQL：

select * from statement_analysis order by avg_latency desc limit 10;

• 哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表

查看 statement_analysis 中哪个SQL的 tmp_tables 、tmp_disk_tables  值大于0即可：

select db, query, tmp_tables, tmp_disk_tables  from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;

• 哪个表占用了最多的buffer pool

select * from innodb_buffer_stats_by_table order by allocated desc limit 10;

• 每个库（database）占用多少buffer pool

select * from innodb_buffer_stats_by_schema order by allocated desc limit 10;

• 每个连接分配多少内存

利用session表和memory_by_thread_by_current_bytes分配表进行关联查询：

select b.user, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated,current_statement from memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id;

• MySQL内部有多个线程在运行

MySQL内部的线程类型及数量：

select user, count(*) from processlist group by user;

写到这里，其实我们只涉及到 sys schema 很少的一部分功能。估计MySQL的官方AWR也要快出现了，大家也可以期待一下（MySQL 8.0这块又进一步规范了一下）

本文要特别感谢 知数堂 徐伟 同学的校验。