MySQL调优系列（一）——性能监控

╰+攻爆jí腚メ 2024-03-27 16:34 100阅读 0赞

从本文开始，我们开始MySQL调优之路。  
本文主要从show profile、performance\_schema、show processlist讲解mysql的性能监控。

### 一、MySql架构体系 ###

调优之路的最开始，我们先了解MySql架构体系：  
![在这里插入图片描述][62586edff7a94cb981c4c54c5de5979d.png]  
这是官方给的体系图，从图中我们总结如下：  
（1）连接器：不同语言中与MySQL的交互。  
（2）连接池：存储和管理连接信息。  
（3）系统管理和控制工具：提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复，保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等。  
（4）SQL接口：接收客户命令，返回执行结果。  
（5）解析器：SQL命令传递到解析器的时候会被解析器验证和解析。分类/验证语义合法性。  
（6）查询优化器：优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。 （交由优化器将其转化成执行计划，然后与存储引擎交互。）  
（7）缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。（缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓 存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。）  
（8）可插拔存储引擎：主要负责数据的存储和读取，与底层的文件进行交互，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。后续会单独出一篇文章分析存储引擎。  
我这边5.7版本的mysql，默认存储引擎如下：show engines;  
![在这里插入图片描述][59358c8712fd4856a54916b20f361a2b.png]  
默认存储引擎是InnoDB，只有 InnoDB 支持事务。  
（9）系统文件层：该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。  
总结如下：  
![在这里插入图片描述][639be759b9154b1b8a4d394074de797d.png]

### 二、show profile ###

show profile能够查出服务器上执行的所有语句的运行状态，包括在运行过程中执行了哪些操作，各占用了多少时间。  
profile已经不推荐了，即将被淘汰，默认是禁用的。

select @@profiling;//查询当前状态
    set profiling=1;//设置打开

如图所示：  
![在这里插入图片描述][7599cd53654d47a18b8b98ab80293103.png]

#### 1、show profile语法 ####

SHOW PROFILE [type [, type] ... ]
        [FOR QUERY n]
        [LIMIT row_count [OFFSET offset]]
    
    type: {
        
        ALL                显示所有信息
      | BLOCK IO           显示块输入和输出操作的数量
      | CONTEXT SWITCHES   显示自愿上下文切换和非自愿上下文切换的数量
      | CPU                显示用户和系统的CPU使用时间
      | IPC                显示已发送和已接收消息（messages）的数量
      | MEMORY             -- 尚未生效
      | PAGE FAULTS        显示主要和次要页面错误的数量
      | SOURCE             显示源代码中函数名称以及该函数所在文件的名称和行号
      | SWAPS              显示SWAP数量
    }

#### 2、show profiles ####

会展示各个语句的执行时长。  
![在这里插入图片描述][1728057b3aca49a280ed5af1ed06bcb1.png]

#### 3、show profile ####

用于单独分析最近一条执行的sql语句的详细资源占用信息和工作状况，默认显示状态以及它们持续的时间。  
![在这里插入图片描述][ec6149666cd2448396f34261cc4f909a.png]  
可以给show profile 指定一个 for query id 来查看指定 id 的语句，还可以给输出添加新的列，新的列看之前的type。

show profile all for query id;//id 为 show profiles 查出来的某条记录的Query_ID。

![在这里插入图片描述][6542e1d2ffb44c4baebb45c673a4f69d.png]

### 三、performance\_schema ###

MySQL的performance\_schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。  
![在这里插入图片描述][2fc705058ac54839a10fa3229a3442a7.png]  
performance\_schema默认是打开的。  
关于performance\_schema的配置与参数，其他地方有很多讲解，我觉得又臭又长，就不进行总结了。  
实际查询操作，我们这边记录一下：

--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;

### 四、show processlist ###

show processlist 是显示用户正在运行的线程，观察线程的状态， root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程。  
show processlist 显示的信息都是来自MySQL系统库 information\_schema 中的 processlist 表。

![在这里插入图片描述][17b070314b4244488d4dba1ed65e8c70.png]  
各属性如下：  
![在这里插入图片描述][34904135d326410d817b37f019140125.png]  
参考：[show processlist 史上最全参数详解及解决方案][show processlist]

[62586edff7a94cb981c4c54c5de5979d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/067fb77b77124421892eaa29d9433d70.png
[59358c8712fd4856a54916b20f361a2b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f88c738e152f458bafd18868f5272712.png
[639be759b9154b1b8a4d394074de797d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/bbe08debc81c4294b47dd0fc7905eec7.png
[7599cd53654d47a18b8b98ab80293103.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/cee5aef11d684328822be7a3b9d9662a.png
[1728057b3aca49a280ed5af1ed06bcb1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5e0df38c94304d9b8489c0deda1c6215.png
[ec6149666cd2448396f34261cc4f909a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/977a4a28cc2143bb9dbce657fc66ed68.png
[6542e1d2ffb44c4baebb45c673a4f69d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/83d7db3747ac4d72b83753d823a9b4a0.png
[2fc705058ac54839a10fa3229a3442a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5ea037c384434695b6a6c13d7384ac97.png
[17b070314b4244488d4dba1ed65e8c70.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/397cbe3b7fe847de81177396d5ac105e.png
[34904135d326410d817b37f019140125.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/6e6d251040d44c02aeba79e3a8d3659c.png
[show processlist]: https://blog.csdn.net/WUFUSHANLI/article/details/124399079