Mysql调优（一）监控

最新推荐文章于 2026-03-20 01:51:30 发布

原创最新推荐文章于 2026-03-20 01:51:30 发布 · 198 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #mysql优化

mysql 同时被 2 个专栏收录

5 篇文章

订阅专栏

监控

2 篇文章

订阅专栏

本文介绍了MySQL的基础知识，包括client-server架构和查询执行流程。重点讲解了性能监控，如使用showprofile进行SQL性能分析，并详细阐述了PerformanceSchema的特性及其在性能监控中的重要作用。通过示例查询展示了如何利用PerformanceSchema监控和诊断SQL性能问题，为数据库调优提供依据。

首先先上mysql官方地址

官网是核心一切知识以官网为准其他都是二次翻译没有一次的准确核心多看官网解释会齐全

mysql基础

分为client server 存储引擎

server:

连接器
分析器
优化器
- RBO
- CBO
执行器

ps:

mysql8之前有查询缓存,之后就废掉了

因为我们的数据修改会非常频繁所以缓存的数据都是没有什么作用的缓存命中率特别低。

性能监控

有的人肯定听到过这句话，无监控无调优。一切的调优都是建立在有监控的基础之上。因为没有监控你无从下手，也不知道调的好坏。都是需要监控和压测进行的。所以在调优之前一定要了解到监控。所以mysql自己就有一系列的监控机制可以使用，只不过大部分的人都不怎么使用，或者很少使用罢了。

使用show profiled

set profiling = 1

show profiled;

然后就可以看到时间了

show profile(默认是最近执行的sql)

show profile for query 2(id可变)

也可以增加命令增加展现形式（展现更多的内容）

所以可以增加参数

all 显示所有性能信息 show profile all for query n
block io:显示块io操作的次数 show profile block io for query n
context switches: 显示上下文切换次数，被动和主动 show profile context switches for query n
cpu: 显示用户cpu时间、系统cpu时间 show profile cpu for query n
IPC：显示发送和接受的消息数量 show profile ipc for query n
memory: 暂未实现
page faults 显示页错误数量 show profile page faults for query n
source: 显示源码中的函数名称和位置 show profile source for query n
swaps: 显示swap的次数 show profile swap for query n

这个只是低版本的时候可以使用如果是高级版本的可能需要查一下官网才能看到具体反正5.7的是有可以放心使用，后面高级的版本就有了替代 Performance Schema

Performance Schema

提了一些更加复杂的机制来监控我们对于MySQL的性能，开启了一个监控，监控是一个长期的，是肯定会需要消耗资源的。但你不能因为他消耗资源就有理由不用它，这是不对的。

特点（官网翻译)：

1、提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息

2、performance_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。

3、performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。

4、performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。

5、当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。

6、PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同

7、收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）

8、performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）

9、MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。

虽然Performance Schema是开启的，但是也有其他的东西也是没有开启

-打开等待事件的采集器配置项开关，需要修改setup_instruments配置表中对应的采集器配置项
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'where name like 'wait%';

--打开等待事件的保存表配置开关，修改setup_consumers配置表中对应的配置项
UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%';

这个主要是了解并使用并不需要了解所有的表一共有87个表研究都要研究两三天

基本了解了表的相关信息之后，可以通过这些表进行实际的查询操作来进行实际的分析。

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