数据库教程FGMT43‑MySQL性能分析与优化调整

前言

MySQL数据库性能问题是DBA日常工作高频场景，业务响应缓慢、CPU冲高、IO打满、锁等待堆积、连接数爆满，都会直接影响业务可用性。性能优化不是单一调某个参数，而是一套完整体系，包含操作系统层、MySQL实例参数层、索引设计层、SQL语句层、事务锁机制、监控诊断工具多个维度。风哥教程本文基于fgedu‑net‑cn1实验主机，硬件规格64G内存，8颗CPU，混讲主流MySQL版本，不区分具体版本号；数据库实例名fgedudb，业务测试用户名fgedu，根目录统一使用/fgedudb。风哥 itpux‑com

本套风哥教程面向MySQL DBA、运维工程师、后端开发人员、性能测试工程师；覆盖性能诊断方法论、操作系统性能观测、慢查询日志、Performance_schema、sys系统库、EXPLAIN/EXPLAIN ANALYZE执行计划、索引设计与失效场景、SQL语句优化、InnoDB参数调优、事务隔离级别、锁与死锁排查、压测验证、生产故障模拟、上线验收最佳实践。风哥教程本文分为前言与大纲、核心理论知识、实战操作演练、风哥针对本文总结四大模块；实战包含大量可直接复制Shell、SQL脚本，读者可以在测试主机完整复现性能诊断与调优全流程。网上搜索风哥教程可以学习全套数据库教程

内容大纲

1. MySQL性能优化整体方法论，实验主机硬件环境规划，性能问题排查通用分析思路
2. 核心理论：性能瓶颈分层模型（操作系统、实例参数、索引、SQL、事务锁）；InnoDB内存与IO模型；查询优化器工作机制；索引底层B+Tree原理；隔离级别与锁机制；慢查询日志原理；Performance_schema与sys库诊断体系；性能调优常见误区
3. 适配64G内存8CPU服务器my.cnf生产参数模板；性能优化风险点汇总
4. 实战1：操作系统层性能观测，top、iostat、vmstat、pidstat工具实战，定位CPU、内存、IO瓶颈
5. 实战2：慢查询日志完整配置，mysqldumpslow、pt‑query‑digest慢日志解析实战
6. 实战3：Performance_schema性能采集开启，事件语句、等待事件、IO统计查询实战
7. 实战4：sys系统库实战，定位慢SQL、全表扫描、未使用索引、锁等待、内存占用
8. 实战5：EXPLAIN、EXPLAIN ANALYZE执行计划深度解析，索引失效场景复现
9. 实战6：索引优化实战，联合索引、覆盖索引，索引失效复现，无用索引清理
10. 实战7：常见SQL语句优化实战，分页、OR查询、like查询、子查询优化
11. 实战8：InnoDB核心参数调优实操，缓冲池、redo日志、刷盘策略、隔离级别调整
12. 实战9：锁等待、死锁故障排查实战，show engine innodb status，锁视图定位阻塞链
13. 实战10：业务压测验证，优化前后性能对比验证
14. 实战11：生产故障模拟：CPU100%、IO打满、连接数爆满、死锁阻塞，完整排查流程
15. MySQL性能上线验收检查清单，生产环境最佳实践，调优误区汇总

一、核心理论知识

本章节为本套风哥教程理论基础，建立分层性能分析思维，理解InnoDB存储引擎、查询优化器、索引、事务锁底层逻辑，掌握各类诊断工具原理，规避盲目调参带来业务故障。风哥教程 113257174

1.1 MySQL性能分层瓶颈模型

性能问题遵循自上而下分层排查思路，分为五层：

1. 操作系统层：CPU使用率、内存不足Swap抖动、磁盘IO饱和、网络延迟；很多数据库慢本质是操作系统资源瓶颈。
2. MySQL实例参数层：InnoDB缓冲池、redo日志、刷盘策略、连接参数、临时表参数配置不合理。
3. 索引层：缺少索引、索引设计不合理、联合索引顺序错误、索引失效、大量无用索引增加写入开销。
4. SQL语句层：全表扫描、深分页、不当子查询、函数操作索引列、大量排序创建临时表。
5. 事务与锁层：长事务、隔离级别选择不当、行锁升级、间隙锁、死锁，引发业务阻塞堆积。

性能优化基本原则：优先优化SQL与索引，其次调整参数，最后升级硬件；不要优先通过修改参数掩盖SQL缺陷。网上搜索风哥教程可以学习全套数据库教程

1.2 InnoDB核心内存IO理论

1. innodb_buffer_pool_size缓冲池：InnoDB最重要参数，缓存数据页、索引页，64G物理内存生产配置物理内存50%‑70%；缓冲池命中率低代表大量IO读取磁盘。
2. Redo日志：崩溃恢复保障，记录物理页变更；redo日志容量过小会触发频繁checkpoint刷脏页，业务写入性能剧烈抖动。
3. innodb_flush_method=O_DIRECT：绕过操作系统文件缓存，避免双缓存，数据库生产标准配置。
4. innodb_flush_log_at_trx_commit：控制事务提交redo刷盘；金融业务设置1保证零丢失；高并发非核心业务可以调大换取性能，接受少量数据丢失风险。

1.3 查询优化器与执行计划原理

MySQL基于成本的优化器CBO，统计数据评估多种执行路径代价，选择代价最小执行计划。
影响优化器选择因素：表统计信息、索引可用、join连接顺序、hint提示。
EXPLAIN输出预估执行计划；EXPLAIN ANALYZE真实执行SQL，输出实际耗时、扫描行数。
type访问类型优先级：system > const > eq_ref > ref > range > index > ALL；ALL代表全表扫描，是优化重点对象。
Extra关键字：Using filesort文件排序、Using temporary创建临时表，代表SQL存在性能隐患。

1.4 B+Tree索引原理与索引失效场景

InnoDB主键聚簇索引，二级索引叶子存储主键值。
索引失效常见场景：

1. 索引列使用函数、运算；
2. like以%开头；
3. or条件一侧索引缺失；
4. 隐式类型转换；
5. 优化器评估代价，放弃索引选择全表扫描。

覆盖索引：查询所有字段全部包含在索引中，Extra出现Using index，不需要回表访问主键数据页，性能提升明显。

1.5 事务隔离级别与锁机制

1. READ‑COMMITTED（RC）：读已提交，消除间隙锁，减少死锁概率，互联网业务广泛使用；
2. REPEATABLE‑READ（RR）：MySQL默认隔离级别，存在间隙锁，容易出现锁范围扩大，死锁概率更高。
   行锁条件：查询条件必须命中索引；条件没有命中索引，行锁会升级为表锁，阻塞全部写入。
   长事务危害：事务长时间不提交，undo无法回收、MVCC版本链膨胀、锁持续占用，引发大量锁等待、磁盘空间暴涨。风哥数据库教程 itpux‑com

1.6 诊断工具原理

1. 慢查询slow_query_log：记录执行时间超过long_query_time阈值SQL；可记录没有使用索引的查询；适合抓取慢业务SQL。
2. Performance_schema：性能采集底层框架，采集等待事件、SQL语句、IO、锁、内存统计；默认部分采集关闭，开启会带来少量性能开销。
3. sys系统库：基于performance_schema封装视图，简化DBA查询，直接定位慢SQL、全表扫描、未使用索引、锁、内存统计。

1.7 适配64G内存8CPU my.cnf关键参数理论

[mysqld]
innodb_buffer_pool_size=32G
innodb_redo_log_capacity=4G
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
sync_binlog=1
max_connections=2000
table_open_cache=4096
tmp_table_size=2G
max_heap_table_size=2G
transaction_isolation='READ‑COMMITTED'
innodb_lock_wait_timeout=10
wait_timeout=1800
interactive_timeout=1800
slow_query_log=ON
slow_query_log_file=/fgedudb/mysql/log/slow.log
long_query_time=1
performance_schema=ON

1.8 性能调优常见误区

1. 盲目调大缓冲池，耗尽操作系统内存，触发Swap；
2. 只调参数不优化SQL，根源问题没有解决；
3. 隔离级别随意设置RR，业务大量死锁阻塞；
4. 索引越多越好，索引会降低写入性能；
5. 生产随意修改innodb_flush_log_at_trx_commit=0，忽略数据丢失风险；
6. 调优之后没有压测验证，直接上线。

网上搜索风哥教程可以学习全套数据库教程

二、实战操作演练

环境说明：
实验主机fgedu‑net‑cn1；硬件规格64G内存8CPU；MySQL实例根目录/fgedudb/mysql；datadir/fgedudb/mysql/data；日志目录/fgedudb/mysql/log；业务库fgedudb，业务用户fgedu；端口3306；操作系统用户mysql。
前置：数据库实例正常运行，操作系统工具包安装：

yum install -y sysstat percona‑toolkit
mkdir -p /fgedudb/mysql/log
chown -R mysql:mysql /fgedudb/mysql/log

实战1：操作系统层性能观测实战

2.1.1 top整体资源观测

top
#只看mysql进程
top -p `pidof mysqld`

重点观测：%cpu、%mem、swap si so交换；swap出现si代表内存不足，操作系统开始交换。

2.1.2 vmstat观测CPU、内存、IO上下文切换

vmstat 1

重点：si so swap交换；us sy CPU；bi bo磁盘IO；in cs上下文切换。

2.1.3 iostat磁盘IO观测，定位磁盘是否打满

iostat -x 1

关键指标：%util接近100%代表磁盘IO饱和；awaitIO平均等待时间飙升代表IO压力大。

2.1.4 pidstat按进程看IO、CPU

pidstat -u 1
pidstat -d 1

故障定位思路：CPU高看是用户us还是系统sy；IO高看await、util；swap有si优先解决内存不足问题。

实战2：慢查询日志完整配置，慢日志解析实战

2.2.1 my.cnf配置慢查询

修改/fgedudb/mysql/my.cnf

slow_query_log=ON
slow_query_log_file=/fgedudb/mysql/log/slow.log
long_query_time=1
log_output=FILE
min_examined_row_limit=100
#log_queries_not_using_indexes=ON #测试环境开启，生产谨慎开启，日志量暴涨

重启实例生效

systemctl restart mysqld

登录数据库查看参数

show variables like '%slow_query_log%';
show variables like 'long_query_time';
show global status like 'slow_queries';

2.2.2 模拟生成慢SQL

use fgedudb;
create table t_slow(id int primary key,name varchar(128),age int);
insert into t_slow values(1,'a',21),(2,'b',22);
#模拟全表扫描慢查询
select * from t_slow where age>10;

2.2.3 mysqldumpslow官方解析慢日志

cd /fgedudb/mysql/log
#按总耗时排序，取前20条
mysqldumpslow -s t -t 20 slow.log > /fgedudb/slow_report1.txt
cat /fgedudb/slow_report1.txt

2.2.4 pt‑query‑digest解析慢日志（percona‑toolkit）

pt‑query‑digest /fgedudb/mysql/log/slow.log > /fgedudb/slow_pt_report.txt
cat /fgedudb/slow_pt_report.txt

pt‑query‑digest把同类SQL做指纹聚合，统计执行次数、平均耗时，生产定位TOP慢SQL首选。风哥数据库教程 itpux‑com

实战3：Performance_schema性能采集开启实战

Performance_schema默认开启，但部分消费者关闭。

--查看采集开关
SELECT * FROM performance_schema.setup_consumers;
SELECT * FROM performance_schema.setup_instruments;

--开启语句、事务、锁采集
UPDATE performance_schema.setup_consumers
SET enabled='YES'
WHERE NAME IN('events_statements_current','events_statements_history','events_transactions_current','events_transactions_history');

UPDATE performance_schema.setup_instruments
SET enabled='YES',timed='YES'
WHERE NAME LIKE 'statement/%' OR NAME LIKE 'wait/lock/%';

3.1 查询耗时TOP SQL，按总等待时间排序

SELECT
DIGEST_TEXT,
COUNT_STAR 执行次数,
ROUND(AVG_TIMER_WAIT/1000000000,3) avg_ms,
ROUND(SUM_TIMER_WAIT/1000000000,3) sum_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3.2 查询锁等待事件

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

3.3 查询表IO等待统计

SELECT OBJECT_NAME,COUNT_READ,COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA='fgedudb'
ORDER BY SUM_TIMER_WAIT DESC;

注意：performance_schema重启之后统计清零；适合实时故障诊断，不适合长期历史保存。网上搜索风哥教程可以学习全套数据库教程

实战4：sys系统库实战

sys库是封装视图，简化DBA查询，底层数据来源于performance_schema。

4.1 查找高消耗SQL

SELECT * FROM sys.x$statements_with_runtimes_in_95th_percentile LIMIT 10;

4.2 查找发生全表扫描的表

SELECT * FROM sys.schema_tables_with_full_table_scans;

4.3 查找业务未使用的索引，可考虑清理

SELECT * FROM sys.schema_unused_indexes WHERE table_schema='fgedudb';

4.4 查看排序、临时表开销大的SQL

SELECT * FROM sys.statements_with_sorting limit 10;
SELECT * FROM sys.statements_with_temp_tables limit 10;

4.5 查看内存占用统计

SELECT * FROM sys.memory_global_total;
SELECT * FROM sys.memory_by_thread_by_current_bytes limit 10;

实战5：EXPLAIN与EXPLAIN ANALYZE执行计划深度解析

5.1 创建测试业务表，准备数据

use fgedudb;
DROP TABLE IF EXISTS t_order;
CREATE TABLE t_order(
id INT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(64),
user_id INT,
amount DECIMAL(10,2),
create_time DATETIME
);
CREATE INDEX idx_userid ON t_order(user_id);
CREATE INDEX idx_userid_ctime ON t_order(user_id,create_time);

INSERT INTO t_order(user_id,order_no,amount,create_time)
VALUES
(1001,'O20260101001',100.00,'2026‑01‑01 10:00:00'),
(1001,'O20260101002',200.00,'2026‑01‑01 10:05:00'),
(1002,'O20260101003',50.00,'2026‑01‑01 11:00:00');

5.2 EXPLAIN基础执行计划

EXPLAIN SELECT * FROM t_order WHERE user_id=1001;

重点观察type、key、rows、Extra字段。

5.3 EXPLAIN ANALYZE真实执行，输出实际耗时扫描行数

EXPLAIN ANALYZE SELECT * FROM t_order WHERE user_id=1001;

5.4 模拟索引失效场景1：索引列使用函数

EXPLAIN SELECT * FROM t_order WHERE DATE(create_time)='2026‑01‑01';
--改写，索引生效
EXPLAIN SELECT * FROM t_order WHERE create_time >= '2026‑01‑01 00:00:00' AND create_time < '2026‑01‑02 00:00:00';

5.5 模拟索引失效场景2：like左通配

EXPLAIN SELECT * FROM t_order WHERE order_no LIKE '%001';
--索引有效
EXPLAIN SELECT * FROM t_order WHERE order_no LIKE 'O2026%';

5.6 覆盖索引演示 Extra显示Using index

EXPLAIN SELECT user_id,create_time FROM t_order WHERE user_id=1001;

实战6：索引优化实战，无用索引清理

6.1 联合索引最左前缀原则

索引idx_userid_ctime(user_id,create_time)

• where user_id=xxx 可以使用索引；
• where user_id=xxx and create_time=xxx 可以使用索引；
• where create_time=xxx 无法使用该联合索引，丢失最左列。

6.2 识别无用索引，删除多余索引

SELECT * FROM sys.schema_unused_indexes WHERE table_schema='fgedudb';
DROP INDEX idx_userid ON t_order;

注意：删除索引业务低峰操作，避免锁表；大表优先online DDL。

实战7：常见SQL语句优化实战

7.1 深分页优化

不良写法：offset跳过大量数据

SELECT * FROM t_order ORDER BY id LIMIT 10000,10;

优化写法，主键游标分页

SELECT * FROM t_order WHERE id>10000 ORDER BY id LIMIT 10;

7.2 OR条件索引失效优化

--user_id有索引，order_no无索引，or导致索引失效
EXPLAIN SELECT * FROM t_order WHERE user_id=1001 OR order_no='O20260101003';
--改写union all
SELECT * FROM t_order WHERE user_id=1001
UNION ALL
SELECT * FROM t_order WHERE order_no='O20260101003';

7.3 避免select *，减少回表，使用覆盖索引

--不推荐
SELECT * FROM t_order WHERE user_id=1001;
--推荐，只查询业务需要字段
SELECT id,order_no,amount FROM t_order WHERE user_id=1001;

实战8：InnoDB核心参数调优实操（64G内存8CPU）

编辑/fgedudb/mysql/my.cnf

[mysqld]
innodb_buffer_pool_size=32G
innodb_redo_log_capacity=4G
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
sync_binlog=1
max_connections=2000
table_open_cache=4096
tmp_table_size=2G
max_heap_table_size=2G
transaction_isolation='READ‑COMMITTED'
innodb_lock_wait_timeout=10
wait_timeout=1800
interactive_timeout=1800
performance_schema=ON
slow_query_log=ON
slow_query_log_file=/fgedudb/mysql/log/slow.log
long_query_time=1

重启实例，验证参数生效

systemctl restart mysqld

登录数据库校验

show variables like 'innodb_buffer_pool_size';
show variables like 'transaction_isolation';
show variables like 'innodb_lock_wait_timeout';

缓冲池命中率观测

SHOW ENGINE INNODB STATUS\G

重点观测Buffer pool hit rate，生产建议大于99%。

实战9：锁等待、死锁故障排查实战

9.1 模拟锁阻塞会话

会话A：

use fgedudb;
begin;
update t_order set amount=10 where id=1;
--不提交，持有行锁

会话B，执行同样更新，发生锁等待：

begin;
update t_order set amount=20 where id=1;

9.2 查看当前锁等待

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

9.3 查看InnoDB引擎状态，抓取死锁信息

SHOW ENGINE INNODB STATUS\G

输出中LATEST DETECTED DEADLOCK段记录最近死锁详细信息。

9.4 查看长时间运行事务

SELECT trx_id,trx_started,trx_query FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(),trx_started))>10;

优化建议：业务尽量缩小事务，不要在事务中做外部http调用；更新条件必须命中索引，防止行锁升级表锁。
上51CTO搜索风哥可以学习全套数据库教程

实战10：业务压测验证，优化前后对比

使用mysqlslap做简单压测，优化前后对比QPS、平均延迟。

#压测命令
/fgedudb/mysql/bin/mysqlslap -ufgedu -pfgedudb -h127.0.0.1 \
‑‑concurrency=64 ‑‑iterations=10 \
‑‑create‑schema=fgedudb \
‑‑query="select * from t_order where user_id=1001"

记录优化前平均延迟；索引、SQL调整之后再次执行压测，对比性能指标。

实战11：生产故障模拟完整排查流程

场景1：MySQL CPU 100%故障模拟

排查步骤：

1. 操作系统top确认mysqld占用CPU；
2. show full processlist查看活跃会话；
3. performance_schema/events_statements_summary_by_digest抓取TOP高消耗SQL；
4. EXPLAIN分析SQL，确认大量全表扫描；
5. 增加合适索引，业务验证，再次观测CPU回落。

场景2：锁等待大量堆积

1. show engine innodb status\G查看锁信息；
2. 查询performance_schema.data_lock_waits定位阻塞源会话ID；
3. 找到持有锁长事务，业务允许情况下kill阻塞会话；
4. 优化业务，缩小事务，查询条件命中索引。

场景3：连接数爆满，业务无法连接

1. show variables like 'max_connections'确认上限；
2. show status like 'Threads_connected'查看当前连接；
3. 查看大量sleep空闲连接，确认wait_timeout超时参数；
4. 调小wait_timeout，清理僵尸连接；检查业务连接池泄漏。

2.13 MySQL性能上线验收检查清单

1. 操作系统：CPU、内存、磁盘IO基准指标；swap尽量不发生交换；磁盘挂载noatime。
2. 实例参数：innodb_buffer_pool_size适配内存；redo日志容量合理；隔离级别RC；锁等待超时时间合理；slow_query_log开启，long_query_time阈值符合业务；performance_schema开启基础采集。
3. 索引：核心业务SQL全部命中索引；无大量无用索引；联合索引遵循最左前缀；避免索引列运算、隐式转换。
4. SQL：避免select *、深分页、or索引失效；大表避免filesort、using temporary；
5. 事务：业务禁止长事务；更新条件命中索引，防止锁升级；
6. 压测：上线前压测验证QPS、延迟指标；模拟高峰并发。
7. 监控：监控CPU、IO、缓冲池命中率、慢查询数量、锁等待、连接数；
8. 文档：记录核心SQL执行计划，参数变更记录，故障排查手册。

2.14 高频调优误区汇总

1. 只调大参数，不去优化业务SQL，治标不治本；
2. RR隔离级别不加评估直接用于高并发业务，大量死锁；
3. 索引越多越好，索引会增加insert/update/delete写入开销；
4. 生产随意设置innodb_flush_log_at_trx_commit=0，忽略崩溃丢数据风险；
5. 不做压测直接修改线上参数，引发业务抖动；
6. 忽视长事务，造成undo膨胀、锁阻塞。

三、风哥针对本文总结

本套风哥教程完整讲解MySQL性能分析与调优全流程，建立分层性能分析方法论，覆盖操作系统观测、慢查询日志、Performance_schema、sys诊断库、EXPLAIN执行计划、索引设计、SQL优化、InnoDB参数调优、锁与死锁排查、压测验证、故障模拟排查。

1. MySQL性能问题遵循分层排查思路：操作系统资源→MySQL实例参数→索引设计→SQL语句→事务锁；优先优化SQL与索引，再调整实例参数，最后升级硬件，不能靠调参掩盖业务SQL缺陷。
2. 熟练使用整套诊断工具：操作系统top、iostat、vmstat；慢查询日志搭配mysqldumpslow、pt‑query‑digest抓取TOP慢SQL；Performance_schema采集等待事件、锁、SQL统计；sys库封装视图简化DBA定位全表扫描、无用索引、排序临时表开销；EXPLAIN/EXPLAIN ANALYZE分析执行计划，重点关注type、key、Extra字段。
3. 索引基于B+Tree聚簇索引；注意最左前缀原则；索引列函数运算、like左通配、or条件缺失索引、隐式转换会造成索引失效；覆盖索引可以消除回表，提升查询性能；定期清理业务不再使用的索引，减少写入开销。
4. 事务隔离级别生产优先评估READ‑COMMITTED，减少间隙锁，降低死锁概率；更新删除条件必须命中索引，否则行锁升级表锁，阻塞全部业务写入；禁止长事务，长事务会带来锁堆积、undo膨胀、MVCC版本链问题。
5. 64G内存8CPU硬件环境，innodb_buffer_pool_size设置32G，缓冲池命中率尽量维持99%以上；redo日志容量配置4G，防止频繁checkpoint引发写入抖动；金融业务innodb_flush_log_at_trx_commit=1保障事务持久化。
6. 深分页、OR查询、select *是高频SQL性能问题点；深分页优先游标分页；OR条件尽量改写UNION ALL；只查询业务需要字段，利用覆盖索引减少回表。
7. 性能调优之后，必须进行压测验证，确认QPS、平均延迟指标改善；上线做好监控，监控CPU、IO、缓冲池命中率、慢查询、锁等待、连接数；生产环境修改参数前，需要测试环境复现验证，避免直接上线引发业务故障。

全部Shell、SQL脚本，建议读者在fgedu‑net‑cn1测试主机完整复现性能诊断、索引SQL调优、锁故障模拟，建立完整MySQL性能调优思维，再落地企业MySQL生产运维与性能优化项目。