上次做支付链路的压测200并发一打上去TPS直接趴窝在500左右数据库CPU飙到95%慢查询日志哗哗往外吐。开发同学第一反应是加索引DBA扫了一眼说这条SQL的索引都用烂了最后我拿着执行计划一行行抠发现问题根本不在索引而在一个隐式类型转换加上一条不该有的范围查询。这种排查过程性能测试的同学应该都不陌生——压测最难的往往不是把压力打上去而是系统出问题时怎么从一堆表象里精准揪出MySQL这个罪魁祸首。这篇文章把我这些年做性能测试时积累的MySQL问题定位方法、慢SQL分析思路和SQL优化手段完整整理了一遍。从怎么开慢查询日志、怎么看执行计划到哪些场景会让索引失效、怎么改SQL才能从秒级降到毫秒级再到锁、事务和MySQL参数层面的调优全是一条条实测过的经验。无论你是刚接触性能测试的新手还是已经被线上问题蹂躏过几次的老菜皮这篇文章应该都能给你一些可以直接抄作业的东西。1. 性能压测中MySQL拖后腿的典型病征与压测准备1.1 判断瓶颈是不是MySQL先看四条曲线压测过程中系统变慢不一定就是数据库的问题也可能卡在应用线程、网络或者第三方接口。我在定位MySQL瓶颈之前会先拉四组指标出来对照着看应用服务器的CPU和线程状态、数据库服务器的CPU和IO、慢SQL数量、以及TPS响应时间曲线。有个特别典型的组合应用服务器CPU不高但数据库CPU打满同时慢查询数在压测进行中持续上涨——这种情况下九成是SQL或数据库配置出问题了。反过来如果数据库CPU正常但应用服务器的线程池全忙那问题大概率在应用的业务逻辑上比如同步调第三方接口、线程等待锁之类的。我习惯用Prometheus加Grafana搭一套基础监控数据库侧的指标重点盯这几项QPS/TPS压测时突增但很快跌落的QPS往往伴随大量慢SQL。连接数超过max_connections就会报Too many connections应用侧表现为获取连接超时。InnoDB Buffer Pool命中率低于95%就要考虑内存是否给得不够或者数据扫描量太大了。磁盘IO的util和await值全表扫描、临时表落盘、频繁刷脏页都会把IO打高。注意压测时的监控一定是从发压前就开始记录的不要等发现问题才开监控。没有基线数据的排查全凭猜。1.2 压测前MySQL侧必做的三件事针对性能测试场景我每次开工前都会强制自己做三件事能省掉后面一大半排查时间。第一确认慢查询日志是开着的。这一步听着基础但确实踩过好几次坑——压测跑完了想看慢SQL结果slow_query_log参数是OFF等于白测。推荐用下面的配置MySQL 5.7和8.0通用slow_query_log ON slow_query_log_file /var/log/mysql/slow-query.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time设成1秒就行性能测试场景下超过1秒的SQL都有分析价值没必要设成0去抓所有SQL日志量太大反而干扰判断。log_queries_not_using_indexes这个参数打开后即使SQL执行很快但没走索引也会被记录下来对抓隐性问题特别有用。第二把performance_schema打开。MySQL 5.7以后默认是开启的8.0默认也开但有些云数据库或者精简安装的实例会被关掉。这个组件后面分析锁等待、语句执行统计都靠它。第三压测环境的MySQL配置和线上保持同一套。我知道很多团队压测库内存只有4G线上却是32G压出来的结果毫无参考意义。至少在innodb_buffer_pool_size、max_connections、事务隔离级别这几个关键参数上要保持一致否则你在压测环境调优出来的结论放到线上根本不成立。2. 慢SQL的完整定位链路日志、系统表、实时会话三层抓取2.1 慢查询日志的正确打开方式慢查询日志是定位问题的第一站但很多人不会看。拿到一个几十MB的慢日志文件后我一般分三步走第一步先看整体规模用mysqldumpslow做个汇总它会按SQL语句的抽象结构把同类SQL归并在一起并统计平均执行时间、次数、扫描行数。这是我最常用的命令mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log-s at表示按平均耗时排序-t 10取前10条。这样能快速识别压测期间最拖的十条SQL而不是被海量日志淹没。第二步针对排序靠前的SQL把具体的执行时间和扫描行数记下来作为优化前后的对比基线。注意慢日志里面每一行都有价值# Query_time: 5.213700 Lock_time: 0.000182 Rows_sent: 10 Rows_examined: 420000 SET timestamp... SELECT * FROM order_info WHERE order_no 20230315001;Query_time是总耗时Rows_sent是返回给客户端的行数Rows_examined是实际扫描的行数。当Rows_examined比Rows_sent大几个数量级比如从42万行里找出10行几乎可以确定这条SQL在做大量无效扫描优化空间巨大。第三步如果环境装了Percona Toolkit用pt-query-digest分析慢日志会更顺手它会把同类SQL聚合得更清晰还能统计每个SQL占总耗时的百分比。这条工具是我强烈建议团队装上的pt-query-digest /var/log/mysql/slow-query.log2.2 performance_schema与sys库的考古式分析慢查询日志只能看到现在的问题但如果压测结束了、日志被刷掉了或者问题发生在高峰期想回看历史performance_schema就是那座宝库。MySQL 5.7开始自带的sys库里有个视图叫statement_analysis它基于performance_schema的事件统计表记录了各类SQL的累计执行次数、总耗时、平均耗时、扫描行数等。我压测中途想看当前哪些SQL最消耗资源直接跑SELECT query, exec_count, avg_latency, rows_examined, rows_sent FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 20;这个视图拉出来的数据是实例启动以来的累计值压测期间看它基本能覆盖到这台MySQL上跑过的所有业务SQL。它的价值在于哪怕你的慢查询日志只设置了超过5秒才记录那些每秒跑几百次、每次耗时300毫秒的隐性慢SQL也能被抓住。另外如果怀疑某个时间段有阻塞可以查sys.innodb_lock_waits它会把等锁的SQL、持有锁的SQL、以及阻塞时间直接关联展示出来这在后面排查锁问题的时候会详细讲。2.3 现场抓取show full processlist的瞬时快照慢日志和sys表都是事后视角但如果问题正在发生——比如压测中数据库CPU突然100%你切到终端想看看此刻在跑什么show full processlist就是最有用的现场快照SHOW FULL PROCESSLIST;重点关注State列和Time列。出现大段时间特别长、State为Waiting for table metadata lock的会话基本可以断定有DDL操作在阻塞或者有未提交事务占着元数据锁State为Sending data且Time很大说明这条SQL正在全表扫描。另外一个高频场景是连接数打满。压测中如果报Too many connections我用下面这条SQL快速看当前的连接来源和状态分布SELECT db, user, command, state, COUNT(*) FROM information_schema.processlist GROUP BY db, user, command, state;如果大量连接处于Sleep状态说明连接池配置过大或者事务没及时关闭如果大量连接卡在Query状态那就要回到SQL本身去找原因。3. explain执行计划逐字段拆解理解MySQL的脑回路3.1 type与key_len走没走索引走到哪一步停定位到具体慢SQL之后下一步就是分析它为什么慢。explain是每个做性能测试的人必须啃下的硬骨头它相当于把MySQL优化器怎么想的底牌亮给你看。我每次拿到一条慢SQL先看两个字段type和key_len。type表示访问类型从好到差大致是system const eq_ref ref range index ALL。ALL是全表扫描index是扫描整个索引树这两个在实际业务SQL里都是要尽量避免的。range是范围扫描比如where条件里的、、between、like abc%说明优化器在索引上划了一个范围去取这个是可以接受的。ref和eq_ref一般出现在等值匹配和联表查询的驱动表上属于比较理想的访问方式。key_len是优化器实际使用到的索引长度单位是字节。这个字段最能看出联合索引到底用到了几列。举个例子有一个联合索引(a, b, c)如果key_len只等于a这一列的长度说明优化器只用到了第一列后面的b和c都没用上这往往是where条件的顺序或写法有问题。计算key_len有一个小技巧字符串列要加上字符集字节数和变长字段的2字节整数列按类型长度算int是4字节bigint是8字节。刚开始不熟练很正常多算几次就条件反射了。3.2 Extra常见标记filesort、temporary、Using indexExtra字段是执行计划里信息量最大的部分我特别关注几个标记Using filesort——所有的排序只要不是走索引顺序都会出现这个标记。它意味着MySQL要在内存或磁盘上额外做一次排序操作数据量大时会生成临时文件慢得离谱。出现这个标记优先考虑在ORDER BY的字段上建合适的索引让排序走索引顺序。Using temporary——说明查询过程用了临时表常见于GROUP BY、DISTINCT、UNION这类操作。临时表如果大到超过tmp_table_size就会落盘到磁盘临时文件性能断崖式下跌。优化方向是让分组和去重走索引或者改写SQL减少中间结果集。Using index——这是个好东西表示查询所需要的数据直接从索引树里取不需要回表查一行行数据。这种叫做覆盖索引是优化中比较理想的状态。比如联合索引(col1, col2)查询只select col1、col2那么整个查询可以不回表。还有一个组合要特别警惕Using where; Using index和Using index condition。前者是过滤和取数都发生在索引上后者是索引条件下推MySQL 5.6以后的优化手段看到它不用慌说明优化器已经把一部分where条件下推到索引层面过滤了。3.3 用一张表快速评估执行计划健康度看多了执行计划之后我总结了一套快速评估标准分享给你评估维度健康表现危险信号typeref、range为主ALL、index出现频率高key使用的索引与where完全匹配key为NULL或只用联合索引的部分列rows与返回结果集数量级接近rows是返回行数的百倍千倍以上Extra出现Using index出现Using filesort、Using temporaryfiltered越高越好接近100低于30%说明大部分行被where过滤掉过滤到这一步基本就能判断一条SQL病在哪里了。但执行计划只是第一层实际开发中最容易坑人的是索引明明存在SQL却因为某些写法让索引失效这一块我单独拉出来讲因为实在太常见了。4. 索引失效高频场景实测最常见的六个坑4.1 隐式类型转换你以为走索引其实没走压测中最容易踩的坑就是隐式类型转换。比如订单表里order_no是varchar类型但代码里传参数时用了数字型SELECT * FROM order_info WHERE order_no 20230315001;MySQL看到字符串列和数字比较会先把字符串转成数字再比较这样一来order_no列上的索引就失效了优化器选择全表扫描。我在一次压测中就因为这种写法一条本应毫秒级返回的查询硬生生跑了3秒多。排查方法很简单用explain看一眼type是不是ALL然后检查字段定义和传入参数类型是否一致。修复也简单把参数改成字符串SELECT * FROM order_info WHERE order_no 20230315001;这里有个通用经验在开发规范里强制要求查询参数的字段类型必须与表结构定义一致能从源头避免一整个类别的索引失效问题。4.2 函数包裹列与前导通配符在WHERE条件中对索引列做函数运算也是让优化器放弃索引的高频原因。比如统计某天的订单SELECT COUNT(*) FROM order_info WHERE DATE(create_time) 2025-01-15;create_time明明是索引列但外面套了DATE()函数优化器没法直接走索引。你可以改成范围查询SELECT COUNT(*) FROM order_info WHERE create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00;这样既不影响语义又能让索引生效。同理在索引列上做加减乘除运算、使用SUBSTRING等函数都会产生相同的效果。LIKE的前导通配符也是老生常谈SELECT * FROM product WHERE name LIKE %保温杯%;以%开头的模糊查询无法走索引因为它不知道从哪个索引位置开始找。如果业务确实需要这种搜索可以考虑用全文索引或者专门的搜索引擎不要在MySQL里硬扛。但如果是后缀匹配保温杯%那是可以走索引的。4.3 OR、范围查询与联合索引的顺序陷阱联合索引有个最左前缀原则——索引按定义时的字段顺序排列查询条件必须从最左列开始连续匹配。假如索引是(a, b, c)查询条件只用b和c那这个索引就用不上。这个规则比较容易理解麻烦的是和范围查询组合的情况。一个常见的场景是SELECT * FROM table WHERE a 1 AND b 10 AND c 5;联合索引(a, b, c)到这里a用了等值匹配b用了范围匹配c就没法继续走索引了因为b的范围条件中断了后面的索引匹配。优化思路是调整索引顺序为(a, c, b)让等值条件的c排在前面这样a和c都走索引b作为范围收尾。OR导致索引失效也经常出现SELECT * FROM user WHERE phone 13800000000 OR name 张三;如果phone和name各自有单列索引这个OR查询在MySQL里可能用上索引合并index merge但更多情况下优化器会干脆全表扫。比它更稳的写法是拆成两条SQL用UNION ALL合并SELECT * FROM user WHERE phone 13800000000 UNION ALL SELECT * FROM user WHERE name 张三;如果OR条件是同一个列的多个值更推荐直接用INSELECT * FROM user WHERE status IN (1, 2, 3);IN在MySQL优化器里通常能走索引而且比多个OR拼接更清爽。还有一个让很多人困惑的场景NOT IN和。这两个运算符同样容易让索引失效因为它们需要扫描所有不等于某个值的行优化器一算还不如全表扫来得快。如果非要用并且列的可选择性很高可以试试拆成两次范围查询用UNION合并但总体而言这类业务需求更适合在应用层做处理。5. 一个完整的SQL优化案例从30秒到80毫秒的改造过程5.1 压测暴露的问题与第一轮排查理论讲再多不如看一个真实改造过程。之前做过一个订单中心系统的压测场景是查最近一个月的订单列表并关联用户信息。300并发压了十分钟接口的TP99从800ms一路涨到6秒数据库CPU维持在90%以上慢日志里刷出来一条SQLSELECT o.order_id, o.order_no, o.amount, o.create_time, u.user_name, u.phone FROM order_info o LEFT JOIN user_info u ON o.user_id u.user_id WHERE o.create_time BETWEEN 2025-01-01 AND 2025-01-31 AND o.status IN (PAID,FINISHED) ORDER BY o.create_time DESC LIMIT 10000, 20;第一轮排查就发现了两个问题ORDER BY create_time触发了Using filesortLIMIT的偏移量达到了10000意味着前面一万行全部要扫描然后丢弃。但真正的核心问题还在执行计划里。5.2 执行计划暴露出的两个真正痛点执行计划拉出来一看id select_type table type key rows Extra 1 SIMPLE order_info range idx_create_time 980000 Using where; Using filesort 1 SIMPLE user_info ALL NULL 200000 Using where第一个痛点驱动表是order_info优化器选择用idx_create_time这个索引但因为BETWEEN的范围过滤和IN条件的存在它实际扫描了98万行然后对结果做了排序。第二个痛点关联user_info时type是ALL全表200万行。正常情况下user_id是主键关联应该走eq_ref才对为什么没走我单独拿关联条件去explain发现user_info表确实有主键索引但驱动表order_info的user_id字段类型是varchar而user_info.user_id是bigint——又是隐式类型转换。这一下关联查询变成了逐行全表扫描代价被放大了几个数量级。5.3 改写SQL与重建索引before/after对比针对这两个痛点改造方案如下第一步把深分页改成延迟关联写法。先从一个窄小的索引结果集中取ID再回表关联其它字段避免让数据库扫描前面一万行的全量数据SELECT o.order_id, o.order_no, o.amount, o.create_time, u.user_name, u.phone FROM ( SELECT order_id FROM order_info WHERE create_time BETWEEN 2025-01-01 AND 2025-01-31 AND status IN (PAID,FINISHED) ORDER BY create_time DESC LIMIT 10000, 20 ) t JOIN order_info o ON o.order_id t.order_id LEFT JOIN user_info u ON u.user_id o.user_id ORDER BY o.create_time DESC;第二步表结构层面把order_info.user_id的类型从varchar改成bigint并与user_info.user_id保持一致。这一步在压测环境做完后关联查询恢复到了eq_ref。第三步调整索引。发现原索引idx_create_time只有create_time一列而排序、过滤都要用到干脆重建联合索引ALTER TABLE order_info ADD INDEX idx_create_time_status (create_time, status);改造后的执行计划order_info的rows从98万降到了2万左右user_info的type变成了eq_refUsing filesort消失。同一压测脚本再跑一遍接口TP99从6秒降到280ms数据库CPU从90%降到35%。这条SQL的Query_time更是从30秒级别降到了80ms级别。这次排查最有价值的经验是SQL优化不能只盯着单条语句看字段类型的一致性、索引设计的合理性、分页方式的取舍是一个系统工程。任何一环有短板整体性能都会被拖垮。6. 跳出SQL本身锁、长事务和MySQL参数层面的瓶颈6.1 行锁升级与锁等待并发场景的隐形杀手有些问题SQL本身不慢但压测时TPS就是上不去请求全在等锁。MySQL的锁等待是性能测试里比慢SQL更隐蔽的坑。最典型的是行锁升级为表锁。在InnoDB里更新语句如果无法通过索引精确定位到行就会退化成全表扫描加锁相当于把整张表的行都锁了。举个例子UPDATE order_info SET status PAID WHERE order_no 20230315001;如果order_no没有索引这条UPDATE执行时InnoDB为了找到目标行会把扫描过的所有行都加上锁最终变成锁表。并发一高后面所有更新操作全部排队TPS直接崩掉。排查锁等待时我一般用sys.innodb_lock_waitsSELECT * FROM sys.innodb_lock_waits;它会把阻塞者和被阻塞者的线程ID、SQL语句、等待时间直接列出来。再有就是看information_schema.innodb_trx找出那些长时间未提交的事务SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx WHERE trx_state RUNNING AND trx_started NOW() - INTERVAL 5 SECOND;顺带提一个压测环境常用的技巧InnoDB有死锁检测机制innodb_deadlock_detect默认是开启的压测发现大量死锁报错时可以先看有没有某个事务持锁时间异常多数死锁的根源是事务顺序不一致或者单事务更新的行数太多、锁范围太大。6.2 长事务和undo膨胀长事务是压测中的另一个杀手。一个事务长时间不提交除了它自己持有的锁不释放之外还有一个容易被忽略的影响——InnoDB的MVCC机制依靠undo log保留多版本数据事务不结束那些旧版本数据就清不掉。假设有一个查询事务开启后在应用层做了30秒的远程调用才提交这期间数据库里这条数据被频繁更新undo log会不断堆积版本链越来越长。后续其它查询要读取这个老版本数据需要顺着版本链一次次回滚读性能严重下降。压测前的检查姿势很简单SELECT trx_started, trx_rows_locked, trx_rows_modified, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds FROM information_schema.innodb_trx ORDER BY running_seconds DESC LIMIT 10;一旦发现有事务跑了十几秒还没结束就要回应用层查代码——是不是事务边界没控制好。我在项目里坚持一个原则事务里只放必要的SQL远程调用、消息发送、文件处理一律搬出事务。这条规则能避掉一大半长事务问题。6.3 压测前就该调好的几个核心参数除了SQL和事务层面的调优MySQL实例参数对压测结果的影响也极其明显。我在压测前会重点检查这几个参数它们是最常导致性能测试结果失真的元凶参数建议值/原则说人话解释innodb_buffer_pool_size物理内存的50%-70%InnoDB的数据页和索引页缓存池太小会导致频繁读盘max_connections按压测并发估算不低于500连接上限超出直接报错应用表现为连不上数据库innodb_flush_log_at_trx_commit压测环境按需调整默认1每次事务提交都刷磁盘最安全但最慢0或2更快但有丢数据风险sync_binlog默认1即可不轻易改与上一个参数配合控制binlog刷盘频率tmp_table_size建议32M以上临时表内存上限超过后落盘性能骤降max_heap_table_size与tmp_table_size保持一致内存临时表大小上限两者取较小者生效sort_buffer_size建议4M-8M不要贪大排序缓冲区是会话级参数开太大反而浪费内存innodb_lock_wait_timeout默认50秒压测可调小等锁超时时间调小能更快暴露锁问题需要特别强调的是innodb_flush_log_at_trx_commit和sync_binlog这两个参数是数据安全性和性能之间的权衡。压测环境为了数据安全通常保持默认值1但如果你压测的是只读场景这两个参数影响不大如果是写密集型压测每次事务提交都等磁盘刷盘性能会明显下降。线上怎么设置要根据业务对数据丢失的容忍度来定压测环境建议先确认一下团队的安全基线不要自己拍脑袋改。innodb_buffer_pool_size是大多数MySQL性能问题的大号创可贴。一次压测中一条SQL扫描了400万行的数据通过分析发现这些行占用的数据也才3.5G左右Buffer Pool只有1G导致每次查询都有一部分数据要跑到磁盘上读IO等待非常严重。把Buffer Pool调大到12G后同样的SQL执行时间降了一半。归根结底MySQL的绝大多数查询热数据都应该能在内存里解决如果你的数据量不大但响应慢先检查Buffer Pool是不是配得太小了。7. 优化效果的回归验证与可持续监控7.1 回归压测怎么做才有说服力SQL改完了、参数调完了最后一步是回归验证。但回归验证有个很重要的前提压测脚本、并发数、数据量、持续时长必须和优化前完全一致否则对比无效。我在做回归压测时有一套固定流程先记录优化前的基线数据接口TPS、TP99、最大响应时间、错误率、数据库CPU峰值、慢SQL数量。执行优化动作哪怕只是改一条SQL。跑同样的压测场景持续同样的时长。对比优化后的数据重点看TPS是否提升、TP99是否下降、数据库资源消耗是否降低。如果优化后TPS上去了但CPU也跟着满了这其实不算坏事——说明瓶颈从SQL转移到了其它环节系统整体吞吐能力提升了。真正的成功标准是在同样的资源消耗下系统能扛起更高的并发或者在同样的并发下资源消耗明显降低。回归压测时还有一个容易忽略的点清理压测产生的脏数据。有的压测会大量写入数据导致第二次压测时表的数据量比第一次大很多对比出来的结果就会有水分。每次压测结束后最好把测试数据清理干净或者把数据量控制在相同规模。7.2 建立慢SQL告警与日常巡检机制一次压测治好了眼前的病但防不住以后的新问题。测试环境、预发环境、甚至线上环境都应该建立一套慢SQL的监控告警机制。我在团队里落地的方式是用Prometheus的mysqld_exporter采集MySQL状态配上两条告警规则一条是慢查询数在最近5分钟内持续大于某个阈值另一条是单条SQL执行时间超过2秒立即告警生产环境阈值根据业务调整比如线上单条超过1秒就已经很危险了。再配合每两周用pt-query-digest拉一次慢日志做趋势分析看看慢SQL是不是在增多、有没有新的潜力股出现。很多潜在线性能问题比如数据量增长导致某个索引选择性下降都是靠这种周期性巡检才能提前发现的。对于SQL优化的投入产出比我个人越来越倾向于二八原则80%的性能收益来自20%的SQL。先把慢日志里跑得最慢、出现频率最高的那几条优化掉然后回头再看整体趋势。如果一条SQL只被调用几次但耗时很长和一条SQL每秒调用上百次但耗时中等优化后者对系统整体的收益往往更大。写在最后做性能测试这些年最深的体会是MySQL问题定位和SQL优化从来不是一个单纯的技术活。它需要你同时具备三种视角——测试视角看指标曲线、DBA视角看参数和锁、开发视角看代码和表结构。任何一个环节脱节都会让排查过程变成猜谜游戏。我自己比较习惯的一个做法是每次压测结束后把定位链路、执行计划截图、优化前后的对比数据整理成一份简短的问题复盘文档沉淀到团队的知识库里。下次再遇到类似的压测瓶颈直接翻出来对照能省掉大量的重复排查时间。最后再分享一个小技巧压测时先在数据库侧开一个终端窗口循环执行SHOW FULL PROCESSLIST配合watch -n 1命令实时刷新能帮你捕捉到很多一闪而过的瞬时问题。这种方法虽然原始但在紧急定位时比任何监控工具都直接。希望这篇整理能帮你在下次压测时少走几个弯路。
