MySQL 8.0性能调优实战：从体检到参数优化的完整流程

很多人拿到一台MySQL 8.0实例第一反应就是找一份“优化清单”把网上流传的几十个参数照着抄进去。我处理过不少性能问题发现真正让数据库变慢的往往不是参数没调到位而是你上来就动了不该动的几项结果把旧瓶颈压下去新瓶颈又冒出来而且更难定位。这篇文章不打算给你一份冷冰冰的参数大全而是按一次完整的优化流程来写先体检再从容内存、日志、慢查询、并发几个方向逐个动手每一步都用压测数据验证最后留下回退路径。无论是刚接手业务的开发还是有一定经验的DBA都可以照着这个思路落地至少不会再把线上实例改成事故现场。1. 第一件事不是调参而是把实例“体检”做完整1.1 半小时内能拿全的配置与运行状态清单我先说一个原则在没搞清楚现状之前不要改任何参数。改之前你需要三样东西当前配置、当前运行状态、机器的系统层指标。配置直接用下面这几条拿全SHOW VARIABLES LIKE innodb%; SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE binlog%; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE slow_query_log;运行状态要看增量不能只看累计值。比如Uptime是1000秒Questions是10万平均每秒100条这没问题。可如果你只看那一瞬间的累计数字根本不知道当前实例是在喘气还是已经趴窝。我习惯分两次取样间隔60秒左右mysqladmin extended-status -r -i 60 -uroot -p-r表示显示相对值两次采样之间的差值才是这一分钟的真实变化。重点看这些状态值状态变量看什么Threads_connected当前连接数接近max_connections就有风险Threads_running真正在执行语句的线程数持续偏高说明并发堆积Max_used_connections历史峰值连接数判断连接压力Questions每秒请求量业务负载的风向标Innodb_buffer_pool_read_requests / reads算buffer pool命中率Created_tmp_disk_tables磁盘临时表数量过多说明排序或关联有问题系统层指标同样不能跳过。在MySQL机器上用一段时间的vmstat 3、iostat -x 3看r运行队列、b阻塞进程、waIO等待、磁盘%util和await。如果%util长期在80%以上、await明显高于设备标称值那么调整日志参数或buffer pool可能比优化SQL更急迫。反过来如果磁盘很闲但CPU跑满那就是典型的SQL逻辑问题先去查慢查询。1.2 从系统指标反推MySQL的真实瓶颈拿到这些数据之后怎么判断瓶颈在哪我的经验是看三组组合。第一组CPU高、磁盘不高、Threads_running高。说明数据库在大量计算或者大量扫描绝大多数情况是索引缺失或者SQL写得太糙先别动配置去抓慢查询。第二组CPU不高、wa高、磁盘%util高。说明是IO瓶颈优先看redo log容量、刷盘策略、buffer pool命中率以及是不是有大量磁盘临时表。第三组内存吃紧、swap使用率上升、si和so频繁变化。这种情况最危险多半是buffer pool或者每会话缓冲设置过大实例处于OOM边缘。先解决内存再谈什么性能调优。顺带提醒一句所有运行状态采样尽量覆盖一个业务高峰周期比如跑满15分钟到1小时。采样时间太短很容易被偶发任务干扰得出错误结论。之前某项目找我排查头一天采样5分钟Threads_running才个位数第二天下午高峰直接飙到160多差点把连接数打满。前半天的“体检报告”完全没有参考价值。2. 内存配置不是越大越好buffer pool与每会话缓冲的取舍2.1 innodb_buffer_pool_size按照数据量和命中率来定内存类是MySQL调优的重头戏首当其冲是innodb_buffer_pool_size8.0里的默认值只有128MB生产环境基本不够用。但“调大”不是拍脑袋调大得按数据量来。先看实例的整体数据量SELECT ROUND(SUM(data_length index_length) / 1024 / 1024 / 1024, 2) AS total_gb FROM information_schema.tables WHERE table_schema NOT IN (mysql,information_schema,performance_schema,sys);注意information_schema统计的是表和索引的“理论占用”和实际文件大小有差距但作为半定量参考足够了。再看当前命中率用之前拿到的状态值算一下命中率 Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads) * 100%如果命中率低于99%说明大部分数据读请求落到了磁盘buffer pool可能偏小。我之前遇到过一台实例数据总量120GB热数据大约60GB物理内存256GBinnodb_buffer_pool_size只配了8GB命中率在95%附近晃荡。后来把pool扩到64GB命中率稳定在99.8%以上同样的业务SQL响应时间肉眼可见地降下来。但这里有个坑不是所有机器都能把60%、70%的内存给buffer pool。如果是云主机还要考虑同机其他进程、page cache、文件系统缓存。如果是主机上还跑着应用更要留足余量。我通常建议MySQL独占机器时buffer pool可以按物理内存的50%~70%配混部场景先减掉其他进程的固定占用再按剩余内存的60%左右配。2.2 sort_buffer_size这类“每会话”缓冲为什么最坑很多人知道sort_buffer_size、join_buffer_size、read_buffer_size这类参数但没意识到它们和buffer pool完全是两回事。buffer pool是全局共享的内存池这些参数是每个会话、每次操作都可能分配的私有缓冲。sort_buffer_size默认只有256KB看着不起眼。问题是如果有500个连接同时在排序每个连接分配4MB到16MB内存直接膨胀几个GB。而且MySQL对排序缓冲可以“超额分配”一次排序用到上限甚至超过上限都很常见根本不受你心里那个“4MB不大”的预期控制。所以我的建议很简单第一保持小不要盲目调到32MB、64MB。第二如果你确实有大的排序需求优先在SQL层面解决比如优化order by、合理使用索引避免filesort或者减少排序字段数量。调大排序缓冲只是掩盖问题不是解决问题。max_heap_table_size和tmp_table_size也有类似问题。它们的坑在于如果SQL生成了大临时表内存临时表会自动转为磁盘临时表此时Created_tmp_disk_tables会飙升。这本身是个保护机制但如果你为了“提升性能”盲目调大这两个值内存压力会变得不可控。2.3 一台实例总内存怎么粗算防止被OOM内存优化的底线是不被OOM killer盯上。我见过不止一次因为buffer pool调太大或者奇葩的per-session缓冲配置直接把实例整崩溃的。给你一个粗算公式实例预估内存 ≈ innodb_buffer_pool_size (read_buffer_size sort_buffer_size join_buffer_size) × 活跃连接数 redo log容量 binlog cache和临时表占用的均值 performance_schema等系统层开销举个例子buffer pool 64GB连接数1200每个连接各缓冲平均加起来按1MB算那么这部分就是1.2GB左右redo log按4GB算再加上performance_schema和系统缓冲整体在70GB附近。如果物理内存只有96GB这还能活如果物理内存只有64GB那铁定OOM。粗算之后再看实际使用。Linux下用free -g观察available和free的变化趋势如果available持续走低、swap开始有进有出先考虑调小buffer pool或者限制连接数再去谈性能调优。实话说一个频繁触发swap的MySQL实例任何参数优化都是打水漂因为内存页换进换出的代价远大于你省下的那点CPU。3. 日志与磁盘刷盘redo log、binlog和事务提交的参数组合3.1 8.0的redo log容量变化与checkpoint的关系MySQL 8.0在redo log配置上和5.7差别不小。8.0.30之前innodb_log_file_size决定了单个redo日志文件大小配合innodb_log_files_in_group控制总容量。8.0.30引入innodb_redo_log_capacity统一控制redo log总容量默认值并不大写密集业务建议起步往2GB到4GB规划。为什么redo log容量这么重要你可以把redo log理解成一个环形缓冲区。事务提交前要先把redo日志写到磁盘InnoDB后台再异步把脏页刷到数据文件。如果redo log太小日志写满的时候就必须强制推进checkpoint、加速刷脏页这时候磁盘IO会被瞬间打满吞吐直接掉下来。典型表现是高峰期间磁盘%util很高但仔细看读写分布很多是在刷脏页而不是服务业务请求。检查redo log压力除了看系统层IO还可以看SHOW ENGINE INNODB STATUS;关注LOG段落里的几个序号Log sequence number和Log flushed up to。两者差距长期偏大说明日志推进跟不上写入速度redo log容量或者磁盘fsync能力是短板。这里有个小经验扩redo log并不能解决所有写入慢的问题它只解决“日志空间不足导致的刷脏页阻塞”。如果磁盘本身是普通机械盘先把容量调大再结合下一节的刷盘参数一起看。SSD环境下redo log容量的收益会更明显。3.2 binlog参数怎么设才既保安全又不拖吞吐binlog默认就是ROW格式这点8.0做得很好不用为了某些旧工具去改回STATEMENT。重点要关注两个东西保留时长和刷盘频率。binlog_expire_logs_seconds 2592000 max_binlog_size 512M sync_binlog 1binlog_expire_logs_seconds按秒控制binlog保留时间比5.7的expire_logs_days更精细。max_binlog_size不是越越大越好设得太大单文件坏了损失范围大恢复也不方便512MB到1GB是常用区间。sync_binlog默认是1意思是每次事务提交都把binlog刷到磁盘。这保证了主从同步和崩溃恢复不丢binlog但代价是每次提交多一次fsync。如果业务能容忍极端情况下丢失最近一小段binlog可以考虑sync_binlog0或者配成N让MySQL攒几个事务再刷盘。但我要强调这是安全性和性能的取舍不是“调大就能变快”的白嫖选项。做这类取舍之前建议先查一下当前磁盘的fsync能力。如果SSD本身很快sync_binlog1带来的吞吐损失可能没那么明显那就没必要冒险降安全级别。3.3 innodb_flush_log_at_trx_commit和group commit的取舍innodb_flush_log_at_trx_commit这个参数值得单独拎出来说。它有三个值取值行为安全级别1每次事务提交都刷redo log到磁盘最高不丢已提交事务2每次事务提交只写入OS缓存每秒刷盘中等实例崩溃可能丢1秒数据0每秒才把redo log刷入磁盘最低性能最好丢失窗口更大默认值是1这也是“双1”配置里的一半innodb_flush_log_at_trx_commit1和sync_binlog1组合是数据安全性的黄金标准。如果业务对丢失容忍度低建议就保持双1别为了那点吞吐去动它。在双1前提下还想提升吞吐可以走group commit。MySQL 8.0里有两个参数binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count。简单理解就是让多个事务凑一波再一起刷盘用一点点延迟换更高的吞吐。线上经验是把binlog_group_commit_sync_delay设在几百微秒到几毫秒级别特别适合大量短事务的写入场景。但注意这会让单个事务的提交延迟变大如果业务对延迟敏感需要压测后再决定。我在实际调优中有一个习惯写密集场景先看redo log容量是否够再看刷盘组合是否需要动。大多数情况下容量补足后双1配置反而没那么难扛。4. 慢查询优化的完整路径从慢日志打开到EXPLAIN ANALYZE执行计划4.1 慢日志配置与聚合分析的正确姿势配置慢日志不算难难的是分析。slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON min_examined_row_limit 100long_query_time设成1秒起步对于大多数在线业务已经够用。如果还想抓更细可以调到0.5。这里有个反直觉的点log_queries_not_using_indexes在生产分析阶段可以打开但别长期开因为很多正常的全表扫描查询会刷爆慢日志反而干扰判断。日志存储方式也有讲究。如果存成文件方便mysqldumpslow这种工具做聚合如果存成表可以这样分析SET GLOBAL log_output TABLE;然后直接查mysql.slow_log表按业务和时间维度聚合。更省事的方式是直接用performance_schema的语句汇总表因为8.0里performance_schema默认开启不需要额外配置SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1e12, 3) AS total_sec, ROUND(AVG_TIMER_WAIT/1e9, 3) AS avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY total_sec DESC LIMIT 20;这个结果按“总耗时”排序能一眼看出最拖系统的SQL是哪些。注意DIGEST_TEXT里的参数是归一化之后的所以同一条SQL的不同参数会聚合到一行非常方便。4.2 EXPLAIN ANALYZE别再只盯着type和rows慢日志定位到SQL之后就该看执行计划了。很多人还在用传统EXPLAIN看type是不是ref、rows大不大。这些信息足够但不够直接。MySQL 8.0.18起提供了EXPLAIN ANALYZE它可以真实执行语句并给出每一步的实际耗时、实际行数和循环次数。比如EXPLAIN ANALYZE SELECT user_id, status FROM t_order WHERE order_no SO123456;输出类似- Index lookup on t_order using idx_order_no (order_noSO123456) (actual time0.129..0.246 rows1 loops1)注意输出里的actual time和rows是执行过程中的真实测量值而不是优化器的估算值。这两者差距很大时多半是统计信息过期或者优化器估算模型失准。用EXPLAIN ANALYZE有个大前提它会真正执行语句。所以千万不能在慢日志分析完就直接跑到大表上跑EXPLAIN ANALYZE尤其不能对DML语句直接执行建议在只读从库或者事先准备好的事务里操作。我习惯先以EXPLAIN看框架再用EXPLAIN ANALYZE验证关键SQL的实际开销避免线上被一条全表扫描语句拖垮。4.3 一个varchar隐式转换导致的索引失效案例分享一个我印象很深的案例。某张订单表里order_no是varchar(20)索引建得好好的但某天突然出现大量慢查询语句长这样SELECT * FROM t_order WHERE order_no 123456;用EXPLAIN看type是ALL走了全表扫描。原因很典型order_no列是字符串等号右边是数字字面量MySQL会先把列值隐式转换成数字再比较索引也就失效了。修复很简单把参数改成字符串SELECT * FROM t_order WHERE order_no 123456;再跑一次EXPLAINtype变成ref扫描行数从几十万降到几条。这一类问题在开发联调阶段很难暴露因为数据量小全表扫描也很快。上线后数据一多就是事故。排查这类问题我现在的固定动作是拿到一条慢SQL先看执行计划里有没有typeALL或rows暴涨再检查等号两边的类型是否一致。不要小看隐式转换它和函数套列、前导通配符%xxx并列是索引失效最常见的三大元凶。5. 并发连接是最容易翻车的一环max_connections别盲目调大5.1 max_connections与Threads_running的区别一提到连接数不够很多人第一反应是把max_connections从默认的151调到1000、3000。这个思路要不得。max_connections只是连接数的上限它不等于能同时高效运行的线程数。更值得关注的是Threads_running它代表此刻真正在CPU上执行语句的线程数。一台机器就算连接数3000如果真正在跑的只有20那这3000个连接只是占着内存睡觉。反过来哪怕连接数只有200如果Threads_running长期在80以上你的CPU已经严重过载了。每增加一个连接MySQL至少要为它分配线程栈和会话级别的内存还要承担上下文切换的代价。把max_connections盲目调到3000往往结果不是业务跑得更快而是内存先报警接着连接风暴、锁等待连环爆发。我建议观察两个比值Max_used_connections / max_connections和Threads_running / CPU核数。前者接近1的时候先做应用侧的连接收敛再考虑调整上限后者长期超过核数的1.5到2倍就要先从SQL和索引入手降低单语句开销。5.2 应用侧连接池才是并发控制的真正入口很多连接数打满的问题根源根本不在数据库而在应用侧连接池配得太大或者连接获取和归还的逻辑有问题。数据库侧调max_connections是治标应用侧管好连接池才是治本。连接池的核心就三个值最大连接数、最小空闲数、获取连接超时时间。我见过某项目组为了提升吞吐把一个服务实例的连接池最大连接数配到300又部署了多个副本总量直接超过数据库上限。结果业务高峰期数据库Threads_connected打满新连接超时旧连接还在跑最后整个服务雪崩。在数据库侧排查时可以用一条命令快速看清连接状态SHOW PROCESSLIST;如果发现大量Sleep连接堆积很可能就是连接池空闲连接没回收或者连接生命周期配置过长。这时候优先调整应用侧参数把最大连接数降到一个合理范围比如单个实例50到80再设置一个较短的连接超时时间。数据库侧再配合wait_timeout控制空闲连接回收通常能腾出大量资源。5.3 一次连接风暴触发的锁等待排查记录那次连接风暴我印象很深。现象是业务侧大量报错数据库日志里出现“Too many connections”同时SHOW PROCESSLIST看到几十个线程卡在Waiting for table metadata lock和行锁等待上。排查顺序是这样的先看SHOW ENGINE INNODB STATUS的TRANSACTIONS段落找到长时间未提交的事务。查information_schema.innodb_trx按trx_started排序找到最早开启的事务线程。用sys.innodb_lock_waits视图看阻塞链锁定是谁堵住了谁。最后定位到的原因很有代表性一个批量任务开启事务后某一步查询走了全表扫描跑了很久没提交后面一大片正常业务SQL都要访问同一批行全部被这个长事务卡住。再加上连接数配得高越来越多请求挤进来连接池也不断重试数据库负载彻底失控。处理办法分两步先把长事务kill掉恢复业务再把应用侧连接池上限收紧同时给批量任务加上分批提交逻辑。数据库本身没改任何参数问题就解决了。这个案例给我的教训是高连接数会放大任何一处的短板不管短板是慢SQL、锁竞争还是磁盘抖动。所以处理连接压力千万别只盯着max_connections这个数字。6. 每次改动都压测用数据说话也留好回退路径6.1 压测前必须统一的三件事调优是否有效不能靠“感觉变快了”。同一套改动必须用可复现的压测来验证。压测前有三件事必须统一。第一数据集不能变。同一张表、同样的数据量、同样的索引结构。如果在两次压测之间往表里插了几百万行结果对比就没有任何意义。第二参数基线要固定。每次只改一个变量其他参数保持一致。比如这次只动innodb_buffer_pool_size那么sort_buffer_size、max_connections这些都别碰。这样改完出了效果或者出了问题你知道该归因到谁。第三压测要错峰。不要在压测期间再跑批量任务、备份或者其他分析查询。尽量挑一个空闲窗口连续跑三轮以上取中位数结果避免单次压测被系统偶发抖动污染。工具方面mysqlslap是官方自带的小工具适合做快速冒烟测试。更复杂、更接近真实业务的场景可以把SQL脚本交给通用基准工具去跑。你要做的只是保证脚本里包含线上典型的读、写、排序、关联语句。6.2 除了TPS/QPS还要盯住哪些指标压测结果不能只看TPS、QPS涨了多少。这两个数字很直观但容易掩盖其他隐患。我每次压测都会额外记录一份清单延迟分布特别是p99。TPS可能涨了但p99从10毫秒飙到300毫秒那就是拿长尾延迟换吞吐不可取。Threads_running是否稳定。并发压测下这个值如果持续攀高说明系统已经逼近处理上限。Innodb_row_lock_wait次数和等待时间。这个值飙升说明写冲突和锁等待在增加。Created_tmp_disk_tables。这个值大量出现说明排序或关联语句需要优化而不是继续压参数。Slow_queries计数。如果优化以后慢查询反而增多那基本可以判定改动有问题。给个我常用的对比模板指标优化前优化后结论QPS52007800提升p99延迟42ms28ms提升Threads_running峰值9658改善Created_tmp_disk_tables41035改善只要有一项恶化就要停下来分析原因。只挑好看的指标写在报告里迟早会还债。6.3 改错参数后的定位与快速回退调优不可能永远一次到位我也改错过参数。有一回为了压内存我把sort_buffer_size从2MB降到了256KB结果线上一个常用排序SQL从200毫秒变成2秒慢日志直接被打爆。幸好改动前我把SHOW VARIABLES完整存了一份压测结果又明显异常马上动态改回去业务就恢复了。所以每次改参数之前我习惯先跑一次备份mysql -uroot -p -e SHOW VARIABLES /backup/variables_before_$(date %F).txt改完以后再存一份mysql -uroot -p -e SHOW VARIABLES /backup/variables_after_$(date %F).txt执行对比diff /backup/variables_before_$(date %F).txt /backup/variables_after_$(date %F).txt如果改动用的是SET GLOBAL生效是动态的但实例重启后会丢所以一定要在配置文件里同步持久化。线上出问题时先动态改回原值缓解再找业务窗口重启。我见过有人只改配置文件没执行SET GLOBAL然后重启前参数根本没生效排查半天才发现是乌龙。调优记录也别偷懒。文件名、日期、改前值、改后值、改动原因、压测结果每行都写清楚。三个月后你再翻这份记录会感激当时的自己。最后分享一个我的习惯调优这件事永远不要同时动超过一个参数。每改一个就压测一轮记录一轮。最好的优化状态不是把参数调成某个网传“标准值”而是针对你的业务负载找到一组参数组合并用压测反复确认它确实稳定。