数据库性能瓶颈分析（mysql）

MySQL 数据库监控指标

MySQL 结构介绍

网络连接层：提供与 Mysql 服务器建立连接的支持
核心服务层：主要包含系统管理和控制工具、连接池、SQL 接口、解析器、查询优化器和缓存六个部分
存储引擎：负责 Mysql 中数据的存储与提取，与底层系统文件进行交互
文件系统：负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层

Mysql架构

主要性能指标

CPU 使用率低，响应时间高
- 数据库连接数不够
- 死锁
CPU 使用率高
- 慢查询，索引使用不当
- 磁盘 IO 性能不足

性能监控软件

MySQL Enterprise Monitor：一款强大的数据库监控工具，提供完整的 MySQL 性能和可用性监控功能，通过生成图表和报告，以及提供警报功能通知管理员数据库性能问题和错误的详细信息。
Percona Monitoring and Management（PMM）：一款免费的数据库监控工具，支持 MySQL、MariaDB 和 Percona Server 数据库，提供实时性能监控和诊断功能，帮助管理员快速识别和解决数据库性能问题。

Performance Schema 介绍

在 MySQL 的 5.7 版本中，性能模式是默认开启的。如果想要显式地关闭的话需要修改配置文件，不能直接进行修改，会报错Variable 'performance_schema' is a read only variable。

----- 查看`performance_schema`的属性

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

----- 在配置文件中修改`performance_schema`的属性值

[mysqld]
performance_schema=ON

Performance Schema 表类型

语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表 summary，其中，summary 表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分):show tables like '%statement%';
等待事件记录表：与语句事件类型的相关记录表类似:SHOW TABLES LIKE '%wait%';
阶段事件记录表：记录语句执行的阶段事件的表:SHOW TABLES LIKE '%stage%';
事务事件记录表：记录事务相关的事件的表:SHOW TABLES LIKE '%transaction%';
监控文件系统层调用的表:SHOW TABLES LIKE '%file%';
监视内存使用的表:SHOW TABLES LIKE '%memory%';
动态对 Performance Schema 进行配置的配置表:SHOW TABLES LIKE '%setup%';

Performance Schema 实践操作

哪类的 SQL 执行最多？

SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类 SQL 的平均响应时间最多？(AVG_TIMER_WAIT 皮秒为单位，1000000000000 皮秒=1 秒)

2.SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC;

哪类 SQL 排序记录数最多？

SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类 SQL 扫描记录数最多？

SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类 SQL 使用临时表最多？

SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类 SQL 返回结果集最多？

SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪个表物理 IO 最多？

SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC

哪个表逻辑 IO 最多？

SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC

哪个索引访问最多？

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

哪个索引从来没有用过？

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;

哪个等待事件消耗时间最多？

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

剖析某条 SQL 的执行情况，包括 statement 信息，stage 信息，wait 信息

SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';

查看每个阶段的时间消耗

SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;

查看每个阶段的锁等待情况

SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event;

sys Schema 实践操作

sys Schema 包含视图、存储过程和存储函数。视图中对 Performance Schema 的数据进行汇总，并使用易于理解的格式进行展现。

索引情况

查询冗余索引（比如对于 name 字段创建了一个单列索引，有创建了一个 name 和 code 的联合索引）
```
SELECT * FROM sys.schema_redundant_indexes;
```

查询未使用过的索引

SELECT * FROM sys.schema_unused_indexes;

查询索引的使用情况

SELECT index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
FROM sys.schema_index_statistics WHERE table_schema='dbname';

表相关

查询表的访问量

select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;

查询占用 buffer pool 较多的表

select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;

查看表的全表扫描情况

select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

监控 SQL 执行的频率

select db,exec_count,query from sys.statement_analysis
order by exec_count desc;

监控使用了排序的 SQL

select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;

监控使用了临时表或者磁盘临时表的 SQL

select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc

I/O 相关

查看消耗磁盘 IO 的文件

select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

执行计划

type：表示表的访问类型，包括 ALL（全表扫描）、index（索引扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）等；
key：Mysql 选择使用的索引；
ref：表示索引被哪个字段或常量所引用；
rows：表示 MySQL 根据表统计信息估算出的查询结果集的行数
filtered：表示通过条件过滤后的结果集占总结果集的百分比；

查看表是否被锁：

直接在 mysql 命令行执行:show engine innodb status
查看造成死锁的 sql 语句，分析索引情况，然后优化 sql
然后 show processlist,查看造成死锁占用时间长的 sql 语句: show status like '%lock%'

查看 CPU 消耗最大的 SQL

通过 top 命令查看资源消耗的 mysql 线程，先使用 top 命令查看 cpu 整体使用情况：

再使用 top -H 查看线程 cpu 使用情况：

查看线程信息

SELECT * FROM performance_schema.threads WHERE thread_os_id IN('1604', '1560', '1561','1568');

查看 events_statements_current 中更详细信息

SELECT *
FROM
performance_schema.events_statements_current
WHERE thread_id IN
(SELECT thread_id
FROM performance_schema.threads
WHERE thread_os_id IN ('1604', '1560', '1561','1568')) ;