MySQL 调优之性能监控

最新推荐文章于 2024-09-13 11:25:40 发布

码农Ralap

最新推荐文章于 2024-09-13 11:25:40 发布

阅读量425

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/weixin_36510400/article/details/106600541

版权

java 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

性能监控

MySQL基本架构：
show profile
performance schema

MySQL基本架构：

想要了解SQL的性能，我们需要先了解下MySQL是如何玩起来的：
在这里插入图片描述

连接器：负责跟客户端建立连接，获取权限，维持和管理连接，包括用户名密码验证，权限的查询与分配，自动断开连接等，可分为长连接和短连接。
查询缓存：当执行查询语句的时候，先去查询缓存中的数据，如果能找到结果则直接返回，如果没有，再去数据库中进行查询。如果数据更新比较频繁，只要表更新，缓存就会被清空，此时不建议使用缓存，命中率会比较低。（MySQL5.8已经废弃这个功能）
分析器：1. 语法分析，根据语法规则判断当前的sql是否满足mysql的语法，如果不满足则报错
2.词法分析，如select id from student，需要将字符串"student"识别成表student，字符串"id"识别成列id
最终生成ASTree，简称抽象语法树
优化器：在具体执行SQL语句之前，语句要经过优化处理：如，当表中有多个索引时，决定用哪个索引；当表需要进行join时，需要决定表的连接顺序等。不同的执行顺序对性能影响很大。主要有两种优化方式：RBO（基于规则优化），CBO（基于成本优化），主要是使用CBO
执行器：执行实际的SQL语句，主要是跟存储引擎进行交互。
存储引擎：定义数据不同的存放位置以及文件不同的存储格式：
InnoDB：存储在磁盘
MyIsam：存储在磁盘
memory：存储在内存
other：其他存储引擎

内容\ 格式	MyIsam	InnoDB
存储格式	每个MyISAM在磁盘上存储成三个文件： .frm文件存储表的定义 .MYD 文件存储具体数据 .MYI存储索引数据	InnoDB表空间数据文件（.idb）和它的日志文件（redo）
事务	不支持	支持
场景	大量select	大量增删改
锁	表锁	行锁、表锁
外键	不支持	支持
索引类型	非聚簇索引	聚簇索引

以MySQL5.8，mac OS为例，新建study数据库，新建两张表：student、greade，默认存储路径：/usr/local/mysql/data/：
在这里插入图片描述

show profile

MySQL官方规定后续版本可能不在支持这个功能，目前最新的5.8还是支持这个功能的
我们先来查询下student表里的数据：
在这里插入图片描述
显示耗时用了0秒，我们实际知道这个肯定是时间精度的问题，肯定是有耗时的，那具体怎么看这条SQL耗时多少呢？
我们首先需要开启show profile：

set profiling=1;

然后我们就可以输入show profile命令查看：
在这里插入图片描述
我们就可以看到MySQL在执行这条命令的每一个过程中，花费了多少时间。
PS：show profile仅显示最后执行的那条SQL执行信息。

如果我们想看所有已经执行了的SQL信息怎么办呢？这时候我们就可以使用show profiles命令了：
在这里插入图片描述
那show profile命令怎么使用呢：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
  |  ALL 				（所有信息信息）
  | BLOCK IO			（显示块IO操作的次数）
  | CONTEXT SWITCHES	（显示上下文切换的次数，包括主动和被动）
  | CPU					（显示CPU使用情况）
  | IPC					（显示发送和接收的消息数量）
  | MEMORY		
  | PAGE FAULTS			（显示错误数量）
  | SOURCE				（显示源码中函数名称和位置）	
  | SWAPS				（显示swap次数）
}

官网文档地址：

https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

用上图的例子，假设我们现在想查看第2条SQL语句执行时CPU的运行情况：

show profile CPU for query 2;

在这里插入图片描述

performance schema

performance schema 用于监控MySQL server在一个较底层的运行过程中的资源消耗、资源等待等情况，MySQL默认开启该功能。
下面我们来查看下数据库：
在这里插入图片描述
我们可以看到，默认已经有了一个叫performance_schema的数据库，我们进入该数据库中看下里面的表信息：

我们可以看到里面定义了大一堆的表，有account账户相关，有data数据相关，有event事件相关，还有replication复制相关，等等。
performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息。
我们通过查看相应的表就能查询是SQL性能信息。

总结部分可能使用到比较多的SQL：

--1、哪类的SQL执行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪类SQL的平均响应时间最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪类SQL排序记录数最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪类SQL扫描记录数最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪类SQL使用临时表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪类SQL返回结果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪个表物理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
--8、哪个表逻辑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
--9、哪个索引访问最多？
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
--10、哪个索引从来没有用过？
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;
--11、哪个等待事件消耗时间最多？
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
--12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

performance schema官网地址：