★ FEATURED ARTICLE 1. 故障初现一次让我重新认识MySQL的线上事故MySQL这个老朋友平时看起来老老实实可真出问题的时候往往是一连串蝴蝶效应。前阵子我负责的一个新系统经历了一次典型的数据库性能事故主库负载突然从10%飙到95%业务侧大量请求超时监控大屏一片飘红。当时现场的同事第一个反应是“是不是被刷接口了”但我排查了一圈后发现流量并没有明显上涨问题出在数据库自己身上。这次事故触发了我对MySQL的一次系统化复盘。以前写SQL、建索引、配参数都是按习惯来没怎么深究背后的原理。真正踩过坑之后才发现MySQL性能优化这件事表面上是在调参数、改SQL本质上是在理解它的存储结构、执行计划、并发模型和数据刷新机制。这篇文章把我这次排障和复盘的全过程整理出来包括排查思路、核心操作、参数选择逻辑以及一些在文档里不太容易找到的实战心得希望对正在被MySQL折磨的同行有所帮助。无论你是刚接手MySQL运维的开发还是已经在写复杂业务SQL的工程师这篇文章都值得花十几分钟读完。文中涉及的排查方法、优化策略和工具用法我尽量都用可以直接照做的形式给你同时也解释清楚每一步背后的原因让你在遇到类似问题时能举一反三而不是只会复制命令。2. 第一轮排查慢查询和状态指标的全方位体检2.1 用慢查询日志锁定罪魁祸首事故发生后我第一时间打开了慢查询日志这是MySQL排查性能问题最基础也最有效的入口。如果你还不知道自己的慢查询开关是开还是关建议立刻检查一下SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE slow_query_log_file;我当时的long_query_time设置的是1秒日志里居然积累了上千条慢SQL而平时这个数字几乎是个位数。把日志拖下来按执行次数和总耗时排序很快就发现了几个共同特点它们集中在同一张业务表上且查询条件里使用了一个原本没建索引的字段。这里面有个容易忽略的细节慢查询日志记录的不只是SELECTlog_slow_admin_statements和log_queries_not_using_indexes这两个参数也值得打开。前者会记录ALTER TABLE等管理语句后者会把那些没走索引的查询也记下来——哪怕单条执行很快高频出现也可能是隐患。很多人在定位问题时只看执行时间忽略了扫描行数这是不全面的。2.2 状态变量瞬时指标里读出来的线索慢查询日志帮我锁定了大方向但要判断问题的真实严重程度还需要看实时状态。我惯用的几个命令是SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Threads_connected; SHOW ENGINE INNODB STATUS; SHOW FULL PROCESSLIST;Threads_running当时显示的值在20到30之间波动正常情况下应该是个位数。SHOW FULL PROCESSLIST里则出现了大量Waiting for table metadata lock和Copying to tmp table状态的会话这就说明有DDL或者复杂查询卡住了后续的所有操作。SHOW ENGINE INNODB STATUS里有一大段内容新手刚看可能会被劝退其实重点是看LATEST DETECTED DEADLOCK、TRANSACTIONS和BUFFER POOL AND MEMORY这几块。那次事故中我注意到TRANSACTIONS部分出现了一个长时间未提交的事务结合业务代码排查发现是一个接口里的事务注解没有正确回滚导致持有锁的时间甚至超过了接口超时时间。这一轮排查下来问题基本定性大量慢查询占用了CPU和IO资源加上一个长时间未提交的事务持有行锁后续所有操作同一行数据的会话全部排队链路雪崩。接下来要做的就是把慢查询一个个剿灭同时把连接和事务处理机制调到一个更健壮的状态。3. 慢SQL分析与索引优化实战3.1 一个被忽略的索引引发的全表扫描我选取一条最有代表性的慢SQL来分析它原来长这样SELECT id, order_no, user_id, amount, status, create_time FROM pay_order WHERE status 1 AND create_time BETWEEN 2024-01-01 00:00:00 AND 2024-03-01 00:00:00 ORDER BY create_time DESC LIMIT 50;表里数据量大约800万行这条SQL执行耗时在事故期间达到了3.8秒。用EXPLAIN查看执行计划关键指标如下指标结果问题分析typeALL全表扫描没有走任何索引rows7934200估算扫描近800万行ExtraUsing where; Using filesort过滤在存储引擎层之上完成且要额外排序常规思路是给status加索引或者给create_time加索引。但我要提醒一句单列索引在这里都不够好。查一下这个表的已有索引就知道user_id上有一个索引create_time上也有一个索引但status上没有。如果只给status建索引过滤掉大部分行之后仍然要在800万行里扫一遍符合条件的记录索引选择性和回表代价都很大。3.2 最优索引长什么样我的选择是建立一个联合索引把过滤条件和排序条件都覆盖进去ALTER TABLE pay_order ADD INDEX idx_status_time (status, create_time);这样做的原理在于B树索引的存储结构索引项先按status有序同一status下再按create_time有序。查询的时候数据库能直接定位到status1的第一个create_time在范围内的记录然后一路顺序扫描直到超出范围天然有序不再需要filesort。同时Extra里就不会再出现Using filesort了type会变成range扫描行数骤降到几千。加完索引后这条SQL的执行耗时从3.8秒降到了30毫秒左右。一个优秀的联合索引在这个场景里直接带来了百倍性能提升这不是调的什么高深参数就是数据结构设计的基本功。3.3 设计联合索引时最容易被忽略的四个细节先说字段顺序问题。联合索引遵循最左前缀原则(status, create_time)这个索引对单独用status的查询、对status create_time的查询都有效但如果查询只带create_time条件索引就用不上了。所以设计时要把等值条件字段放在前面范围条件字段放在后面。status1是等值条件create_time BETWEEN ...是范围条件所以我把status放在第一列。第二个细节是覆盖索引。如果查询的列都能从索引树里直接取得不需要回表那性能还能再上一个台阶ALTER TABLE pay_order ADD INDEX idx_status_time_cover (status, create_time, amount, user_id);当查询列刚好是索引列的子集时Extra会显示Using index意味着整个查询不用访问聚簇索引只扫描辅助索引就够了尤其是在高并发、大偏移量的分页场景中效果非常明显。第三个细节是LIKE %xxx%、函数包裹、隐式类型转换会导致索引失效。比如WHERE create_time BETWEEN ... AND ...如果有PHP或Java代码把它拼成字符串再通过某个ORM框架自动转换类型实际执行时可能会触发全表扫描。那次事故里就有一处WHERE user_id 10001的字符型比较但user_id实际是整型MySQL在比较时会做隐式转换索引直接失效。排查的时候要特别留意这一层。第四个细节是分页很深时的性能坑。之前那个SQL用LIMIT 50看着还好但如果业务需求要LIMIT 400000, 50即使走了索引也很慢因为MySQL要先扫描并丢弃前40万条记录。理想做法是改成“延迟关联”或者“基于上一页最大ID”的方式SELECT t.* FROM pay_order t INNER JOIN ( SELECT id FROM pay_order WHERE status 1 AND create_time BETWEEN 2024-01-01 AND 2024-03-01 ORDER BY create_time DESC LIMIT 400000, 50 ) tmp ON t.id tmp.id;子查询只返回主键ID辅助索引就能覆盖完整扫描无需回表再将这批ID关联回原表获取全字段实测在深分页场景里能快一个数量级以上。这个优化点我平时见很多人忽略其实成本低、收益大。4. 连接管理、参数调优与负载治理4.1 连接数为什么会打满慢查询剿灭了一批但随之而来的另一个严重问题是连接数告警。Threads_connected一度冲到900多而我们的max_connections设置的只有1000。这个参数不在SQL层面解决而要看整个应用与数据库的交互方式。大部分MySQL连接池打满本质上都是“慢查询长事务”造成的应用侧线程发出一个数据库请求后如果数据库迟迟不返回线程就卡在等待中随着业务请求继续进来连接池就会不断创建新的连接直到打满。更麻烦的是连接建立和销毁的成本并不低涉及TCP握手、权限校验、线程栈分配等一旦大量新建连接CPU上下文切换压力也会飙升。所以我当时做了几个调整max_connections 2000 max_connect_errors 100000 thread_cache_size 128 skip-name-resolveskip-name-resolve是个经常被遗漏但极其重要的选项。默认情况下MySQL拿到客户端IP后会做一次反向DNS解析如果DNS解析慢或者不通这个连接请求会阻塞很长时间白白占用一个线程。线上环境如果都是通过IP连接的建议直接打开skip-name-resolve能减少大量无谓等待。另外也会遇到连接数真的因为业务量增长需要扩容的情况。但扩容连接数只是缓解症状不要无脑调大。每多一个连接InnoDB内部都要维护对应的数据结构线程切换成本也在增加。最终还是要回到“SQL快起来连接自然就释放得早”这个基本逻辑上来。4.2 InnoDB核心参数的调优思路说几个我最常调、也最推荐认真调的核心参数都是在实例重启后或者灰度环境里验证过的取值调整方案。innodb_buffer_pool_size这个参数决定了有多少数据页和索引页常驻内存。一般建议设置成总内存的70%左右但不要超过75%要给操作系统和其他进程留出余量。我们那台机器内存是32G我把它从默认的128M调到了20G效果立竿见影。注意如果在云上购买的是基础版云数据库默认值往往会缩水生产环境一定要改。innodb_flush_log_at_trx_commit这个参数控制事务提交时redolog的刷盘策略。默认值是1也就是说每次事务提交都要把日志刷到磁盘数据最安全但性能开销最大。如果业务对数据一致性要求没那么极端比如一些非金融类的互动业务可以改成2表示每秒刷一次盘。但如果你是做支付或者订单类的核心业务还是老老实实保持1别为了性能赌数据安全。sync_binlog这个参数和binlog刷盘相关。如果和innodb_flush_log_at_trx_commit同时设置为1就是常说的“双1”配置事务提交时redolog和binlog都强制落盘安全性最高适合金融级场景。但性能损耗很大我见过不少团队为了极限性能把它调成0或者100代价就是一旦主机掉电可能丢最近的事务日志。innodb_io_capacity这个参数控制InnoDB后台刷脏页的IO上限。如果你用的是普通SSD可以设置为200到500如果是高性能NVMe SSD可以设置为1000到2000。它决定了脏页刷新的积极性设得太低缓冲池里脏页堆积过多最终触达innodb_max_dirty_pages_pct水位后会导致性能突然抖动。我当时的调整方案是一个平衡板——因为这是一个读写混合的在线交易库我对关键配置做了以下设置参数修改前修改后备注innodb_buffer_pool_size128M20G总内存32Ginnodb_flush_log_at_trx_commit11保持安全性不变sync_binlog11保持双1不变innodb_io_capacity2001000适配NVMe SSDinnodb_max_dirty_pages_pct7550降低抖动峰值风险max_connections10002000给业务侧留缓冲这套组合下整体吞吐保持住了也没有以牺牲事务安全为代价。这里也想多说一句网上很多“MySQL优化必改100个参数”之类的文章多半是拷贝党写出来的真正上线之前你要清楚每个参数对应的是什么行为改完会产生什么后果否则宁可不改。4.3 为什么恢复后的第一晚我又加班了不要以为把SQL优化完、参数调完这次事故就彻底结束了。恢复后第一个晚上我正准备松口气报警又来了这次是主从延迟。业务侧的同事反馈说读取库的数据存在明显延迟运营后台看到的数据和用户端不一致。当时主从延迟达到了30多秒虽然不至于立刻挂掉业务但已经影响到了功能可用性。查SHOW SLAVE STATUS之后Seconds_Behind_Master稳定在30秒下不来Exec_Master_Log_Pos基本没怎么动而Relay_Log_Space已经堆积到了几百兆。这意味着从库的SQL线程在等待执行某个事务IO线程一直在把主库的binlog拉下来但没有消费掉。打开多线程复制后问题定位到一个大事务。主库凌晨有个定时任务在批量更新一张大表的某个标记位一次性UPDATE了几十万行长事务在主库执行了差不多十几秒产生了一份很大的binlog事件。从库的SQL线程是单线程的要把这些行级变更逐条重放自然就落后了。这里给所有用MySQL主从架构的朋友提个醒大事务是主从延迟的头号杀手。尽量避免一次UPDATE或DELETE影响超过几千行可以把批量任务拆分成小批次循环提交。如果确实无法避免大事务从库侧可以开启并行复制MTS比如在处理能力允许的情况下把slave_parallel_workers设成4或者8同时把slave_parallel_type设置为LOGICAL_CLOCK让从库能并行回放不同事务组的事件。而且主从延迟还有一个危害容易被低估延迟会导致从库上读到旧数据影响业务逻辑正确性而不是单纯的性能问题。很多团队的主从分离方案里一开始就把“强一致”和“最终一致”混为一谈结果线上各种灵异Bug都要从主从延迟角度排查起。5. 从半同步复制到高可用演进5.1 异步复制、半同步复制与全同步复制这次主从延迟事故让我重新审视了复制模式。MySQL默认的异步复制模型下主库提交事务后不会等待从库确认主库一旦宕机未及时传送到从库的binlog就直接丢了。这种模式性能最好但对一致性保护最弱。半同步复制则是主库在事务提交后至少要等待一个从库确认收到binlog并写入relay log才向客户端返回成功。它在性能和一致性之间取了中间值。现在MySQL 5.7以上的版本对半同步复制的支持已经很成熟线上业务只要不是那种极端追求写吞量的场景我都建议至少开半同步。开启方式INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; SET GLOBAL rpl_semi_sync_master_enabled 1; SET GLOBAL rpl_semi_sync_master_timeout 1000;要注意rpl_semi_sync_master_timeout的意义如果从库确认超时半同步会退化成异步复制以免主库无限等待。设置太大会在从库故障时拖垮主库写入设置太小又会频繁导致半同步失效。我一般设置1000毫秒也就是1秒。这个参数必须在实测中调整每台机器的网络状况稳定度不同不能照抄。另外半同步其实有多个从库时还可以配置rpl_semi_sync_master_wait_for_slave_count如果是重要金融系统可以要求至少2个从库都确认代价是写延迟进一步增加。5.2 高可用架构的取舍在未做高可用改造之前我们是单主单从架构。主库一宕从库要手动切换binlog点位要找对齐整个故障恢复时间按小时计算。这种事情经历过一次就想好好做个高可用方案了。当时评估过某云厂商的高可用版也评估过自建方案的资源成本。最终我们选了基于半同步复制自研切换脚本配合某开源管理工具的方案理由是自己对这套MySQL环境有完整的控制权后续做定制化巡检和接入告警体系都方便成本也更可控。高可用没有银弹核心是要把切换的流程做自动化把误操作的概率降到最低。如果你想自建建议至少把节点架构做成“一主一从半同步 一个延迟复制节点”延迟节点负责提供误删数据后的快速恢复能力。延迟复制节点可以设置delay为3600秒也就是说它严格执行主库1小时前的binlog日志。一旦出现恶意DELETE或者误ALTER TABLE窗口期内可以截停这个节点用它来抢救数据这个场景在真实生产里太常发生了。5.3 备份体系不能有一丝侥幸与高可用同样重要的还有备份恢复。我们以前是用某定时任务工具每天做一次全量逻辑备份恢复过一次数据库之后我就再也不满足于这种方案了。逻辑备份mysqldump适合库比较小的场景表结构、数据以SQL文本形式导出恢复方便但速度慢而且备份过程对线上有一定压力。物理备份方案比如XtraBackup则是物理拷贝数据文件速度更快备份时几乎不影响在线业务恢复时也更加可靠。我们的数据量已经超过100G了就切换到了物理备份方案。关于备份有两个经验要反复强调第一备份一定要定期做“恢复演练”。很多团队备份脚本每天都在跑但没人在一台干净的服务器上真正恢复过。等你某天急需恢复时才发现备份文件损坏、依赖缺失、平台版本不兼容那就不是“有备份就能睡安稳觉”的事了。我建议每个季度做一次完整恢复演练时间花得值。第二binlog一定要开启并且保留足够长的时间。备份文件只能保证你恢复到备份完成的那一刻如果业务数据在备份之后发生了变更你需要用binlog把数据追到故障前时间点。binlog_expire_logs_seconds尽量设置得大一点比如604800秒7天这样即使周五发现周一的误操作也还有机会追回数据。磁盘贵但数据更贵这个成本不能省。6. 常用运维命令与排障速查6.1 一条命令看懂当前数据库在忙什么每次别人让我帮忙远程看一眼MySQL我上手基本都是同一个套路。先看进程列表再看全局状态再看InnoDB引擎状态全程不超过一分钟mysql -uroot -p -e SHOW FULL PROCESSLIST; | grep -v Sleep这个命令列出所有非空闲会话能立刻看到哪些语句正在执行、执行了多久、处于什么状态。Info列会显示SQL文本Time列会显示耗时。如果大量State列是Sending data或Copying to tmp table基本可以判断是慢查询或排序作祟。如果是连接数问题我会顺手执行mysql -uroot -p -e SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Threads_running; SELECT sleep(1); SHOW GLOBAL STATUS LIKE Threads_running;连续两次查看Threads_running并间隔1秒能判断这个值是不是在持续走高。正常系统里它应该在一个平稳区间内波动如果只增不减那就要小心是连接堆积了。6.2 现场排障的几条SQL建议收藏这里把我常用的几条“保平安”SQL放出来可以直接复制到你的工具库里-- 查看所有非空闲会话的详细信息 SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC; -- 查看InnoDB行锁等待和阻塞事务信息 SELECT * FROM sys.innodb_lock_waits; -- 查看当前事务详情未提交事务 SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ASC; -- 查看执行次数最多的SQL SELECT digest_text, count_star, sum_rows_examined, sum_rows_affected FROM sys.statement_analysis WHERE digest_text IS NOT NULL ORDER BY sum_rows_examined DESC LIMIT 20; -- 查找全表扫描的SQL SELECT query, executions, rows_examined, rows_sent FROM sys.statements_with_full_table_scans ORDER BY rows_examined DESC LIMIT 20;sys.statement_analysis和sys.statements_with_full_table_scans两张表特别适合常规巡检它们直接从performance_schema聚合出统计结果能比人工翻慢查询日志更快地找到问题SQL。6.3 一张速查表看清常见故障我把线上常见故障、可能原因、排查方向整理成了一个速查表方便遇到问题时直接对照判断故障现象常见原因排查思路CPU飙升慢SQL、全表扫描、大量并发排序查慢查询日志、sys.statement_analysis用EXPLAIN分析连接数打满慢查询导致连接持久占用、连接池泄漏SHOW FULL PROCESSLIST检查应用侧连接池参数主从延迟持续增长大事务、从库单线程回放、网络抖动SHOW SLAVE STATUS拆大事务开并行复制锁等待频繁长事务持有行锁、间隙锁冲突查innodb_trx与sys.innodb_lock_waits优化事务提交时机内存持续上涨buffer pool过大、连接数过多、临时表内存过大调整buffer pool检查连接数优化临时表策略磁盘IO占比高刷脏页、大批量写入、未走索引扫描查innodb_io_capacity、脏页比例优化SQL偶发超时半同步超时、连接建立频繁检查半同步参数、skip-name-resolve、thread_cache_size这张表不能替代实际排查但可以帮你快速缩小范围节省宝贵的故障抢修时间。7. 日常巡检清单与长期优化节奏7.1 每周巡检低成本防大患我会在每周的计划任务里安排一轮MySQL巡检统一跑一组固定断言脚本开销很小但能在问题放大前提前发现苗头。巡检项包括慢查询数量与同环比慢查询日志里超过1秒的SQL条数是否突增超过10秒的SQL是否有新增。主从状态与延迟Seconds_Behind_Master是否接近0Slave_IO_Running和Slave_SQL_Running是否为Yes。连接数与线程状态Threads_connected是否超过max_connections的80%水位Threads_running是否有峰值持续。磁盘空间数据盘使用率是否超过80%binlog保留时间是否满足恢复需求。 而那个每周看一次的大筛选SQL往往是数据库里最需要清理的冗余索引和失效索引查sys.schema_unused_indexes把超过30天未被使用的索引逐条和开发团队确认后删除。还看到有些索引的区分度极低比如性别字段配合其他字段做了联合索引基本没人在查询条件里用那就是纯写放大。7.2 性能基线的价值很多人做MySQL性能治理都是等到报警了才动手。后来我养成了一个习惯在系统相对稳定的时段通过各种基准测试工具记录一个性能基线包括TPS、QPS、最大连接数、平均查询延迟、内存和磁盘IO水位。有了这个基线日常巡检里一旦发现某个指标偏离了20%以上就能识别出系统已经起了变化不一定要等到出现事故才去查。具体做法并不复杂选一个非高峰时段的夜间窗口用某种开源压测工具对核心表做只读和读写混合压测记录各项数据。之后每次部署新功能或者做参数调整都跑一遍同样的压测脚本用数据说话而不是靠感觉。7.3 从救火到防火的演进路径这次事故给我最大的启示是运维数据库与其花大力气去优化那些极端场景不如先把基本盘做扎实。基本盘包括所有核心表都有合适的索引且没有冗余索引、慢查询日志和审计日志持续开启、核心事务尽量短小、主从延迟有监控、备份可恢复且有定期演练、连接池参数与应用侧配置匹配。这一个盘做完大部分线上故障基本都能提前被拦下。如果还想再往前走一步可以考虑用数据库代理层做读写分离和限流或者在应用层做更细粒度的缓存设计减少数据库压力。但有一点一定要记住任何架构升级都要在灰度环境里反复验证不要在生产上搞“一键式变革”那是在给自己挖坑。MySQL优化的道路没有终点不同业务模式、数据规模、并发模型下的最优解都不一样。但有一条是共通的理解它背后的存储与执行原理比盲目套用任何参数和技巧都更重要。希望这篇文章能帮你少走一些弯路在下次数据库报警来袭时心里多几分底气。我个人在实际操作中的体会是MySQL无论版本怎么升级核心问题永远是数据如何组织、索引如何匹配查询、事务如何缩短、复制如何稳定。把这几条主线理清楚数据库就稳了一大半。如果你也在维护MySQL建议从今天开始开启慢查询日志用完这周的时间看看里面到底藏着多少条被忽视的慢SQL也许下一个事故的种子就埋在那里。
阅读完成 · 觉得有帮助？
