做后端开发这些年被问得最多的一个技术问题就是MySQL查询慢怎么办说实话我刚开始也走过弯路——加索引、改配置、换机器各种手段都试过但真正让线上系统稳定下来的并不是某个一招制敌的绝活而是一套可以反复执行的MySQL优化流程。这篇内容我把多年实战经验整理成一条相对完整的优化路径适合刚接手一个慢系统的开发者也适合做过基本调优但想更进一步的技术负责人核心目标只有一个把定位慢查询、设计索引、改写SQL、调整配置这件事变成有章法可循的工作流程而不是每次靠运气救火。1. 优化工作启动前先把“度量”这件事做对很多人一上来就盯着慢查询日志看发现几条慢SQL就急着加索引。这种直觉不能说错但效率很低。因为你看到的慢往往是结果不是原因。一条SQL慢可能是因为SQL本身写得差也可能是因为索引没生效还可能是因为锁等待、CPU争抢、内存不足、网络延迟、大事务回滚甚至是因为连接池被打满导致请求排队。同样一条SQL在IO util 90%的机器上和空闲机器上表现完全两样。所以优化启动的第一步不是改任何东西而是确认瓶颈到底在哪一层。1.1 先看性能瓶颈在哪一层我在处理线上问题时习惯先执行一轮“体检”而不是直接看SQL。这套体检只需要几条命令能在五分钟内把问题大致定位到某一个方向。先看系统层top命令看CPU和负载iostat看磁盘读写free看内存使用。这里有个容易踩的坑CPU使用率低不代表机器不忙。如果负载很高但CPU很低大概率是在等磁盘IO或者等锁如果CPU突然飙到接近100%而且大量时间花在用户态那多半是计算密集型的SQL太多比如对超大结果集做排序、做复杂的正则匹配、做笛卡尔积式的关联查询。再看MySQL层show global status里重点看几个计数器。Threads_running如果持续偏高说明并发执行的查询太多高峰期容易堆积InnoDB_row_lock_current_waits高说明行锁竞争激烈Buffer_pool_read_requests与Buffer_pool_reads的比例能反映缓冲池命中率命中率长期低于99%时说明内存可能不够用。最后才是看SQL层。通过慢查询日志、performance_schema或者sys.schema_table_statistics这类现成的表找出TOP N的SQL。这里的建议是不要只按单次耗时排序还要看总耗时。一次跑10秒但一天只跑两三次的SQL和一次跑2秒但每秒执行上百次的SQL前者看着吓人后者对整体性能的影响反而大得多。区分“单条慢”和“总量慢”是后续优化优先级排序的关键。1.2 优化目标怎么定才不算瞎忙明确瓶颈之后还要做一件事定目标。没有目标的优化最后都会被“感觉快了一点”糊弄过去。我的习惯是把目标写成可量化的数字比如“让订单列表页接口的P99响应时间从800ms降到200ms以内”“让某个批处理任务的整体耗时从40分钟压到15分钟以下”。定目标有个实用的技巧先测量再优化边做边对比。优化前用同样的测试数据、同样的并发量把现有耗时记录下来每做一次改动就重新测一遍。不要同时改多个变量——一次只改一个改了之后能明确知道是哪个动作带来的收益。很多人优化失败就是因为同时加了索引、改了配置、换了SQL写法最后系统确实快了但问是哪个环节起的作用完全说不清楚下次遇到相似问题依然得重新试。另外要提醒一点优化不是越猛越好。比如把一个频繁执行的查询从扫描全表改成走一个刚建的联合索引查询是快了但如果这个索引让写入变慢且磁盘空间额外消耗不少就要权衡值不值。优化的原则永远是“为业务目标服务”而不是“把每个指标都调到最好”。一个只读报表系统和一个高并发交易系统最优解完全不一样照搬别人的优化方案去套自己的业务大概率会翻车。2. 慢查询日志所有优化工作的起点慢查询日志是MySQL自带的“黑匣子”它记录了执行时间超过设定阈值的所有SQL。如果你的系统还没有打开慢查询日志那优化工作其实还没有真正开始因为你在盲目地、凭感觉地找问题。打开日志、把数据收集起来这是后续一切分析的基础。2.1 慢查询日志的正确配置先看几个核心参数我建议直接在MySQL配置文件里设置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON log_throttle_queries_not_using_indexes 10long_query_time设为1秒是把超过1秒的查询记录下来。有些生产环境把阈值设到0.5甚至0.1秒这要看机器负载。如果日志量太大分析本身就会成为负担。比较稳妥的做法是先从1秒开始把明显慢的抓出来等这些处理完再把阈值降到0.5秒抓更细的问题。log_queries_not_using_indexes会把所有没走索引的查询都记下来即使执行时间很短。这个开关对发现隐藏问题很有用但开启后日志量可能剧增所以要配合log_throttle_queries_not_using_indexes限制同类日志的记录频率。我见过有人开了这个选项后一天产生了十几个G的日志直接把磁盘写满了。合理的做法是开启但配合流量控制和定时清理。一个重要细节MySQL 5.7和8.0里除了把日志写到文件还可以把慢查询记录到mysql.slow_log表中查询起来更灵活但对性能有一定影响。生产环境我一般还是写文件配合mysqldumpslow或pt-query-digest做离线分析。2.2 用工具解读慢日志的关键思路日志文件拿到手不要用肉眼看——SQL量大时肉眼效率太低。mysqldumpslow是MySQL自带的工具简单实用mysqldumpslow -s t -t 10 /var/log/mysql/slow.logs t表示按总耗时排序-t 10表示取前10条。这个工具会把结构相似、只有参数不同的SQL自动归并成一组比如WHERE user_id N会被抽象成WHERE user_id N方便看出哪类查询整体最耗时。第三方工具里我推荐pt-query-digest它不仅能输出更详细的报表还能看到每个查询的响应时间分布、执行频率、扫描行数、返回行数比例。分析时重点看三个数Rows_examined扫描行数、Rows_sent返回行数、以及二者的比例。如果一个查询扫描了10万行只返回10行99.9%的扫描都是浪费这就是典型的索引或SQL写法问题如果扫描行数很少但速度依然慢那问题可能不在SQL本身而在锁等待或系统资源。还有一点容易忽略慢查询日志里记录的耗时是SQL从开始执行到执行完成的时间这段时间包括了CPU计算、磁盘IO、锁等待。如果一个慢SQL其实大部分时间在等锁那么你在它上面建索引也快不了多少。要区分是执行慢还是等待慢可以把SQL拿到数据库里手动执行一次如果手动执行很快那说明是并发环境下的阻塞问题需要查锁、查事务而不是纠结SQL本身。3. 索引优化收益最高也最容易翻车的环节索引优化是整个MySQL优化系列里投入产出比最高的一环。一个合适的索引往往能让查询从几百毫秒降到几毫秒。同时它也是最容易翻车的环节——不加思索地建索引索引失效了还不知道才是最常见的问题。说句实话我见过太多系统性能差的根子不在SQL写法不在配置而是索引设计压根没走心。3.1 索引失效的常见场景与原理先理解InnoDB里索引的底层结构。主键索引是聚簇索引叶子节点直接存完整行数据二级索引的叶子节点存的是主键值。所以通过二级索引查数据要先在二级索引树上找到主键再回聚簇索引查一次完整行这个动作就叫回表。理解这一点很多索引失效的场景就能想明白了。最常见的失效场景之一对索引列做了函数或计算。比如WHERE MONTH(create_time) 5create_time字段上的索引完全用不上因为MySQL要先对每一行做函数运算才能比较。解决办法是改成范围查询WHERE create_time 2025-05-01 AND create_time 2025-06-01。隐式类型转换也是个大坑。如果手机号字段是varchar类型查询写成WHERE phone 13800138000数字会被转成字符串再比较索引大概率失效。解决方法是保证查询条件的类型和字段类型一致。还有左模糊匹配。LIKE %abc没法走索引因为B树的索引是按前缀顺序排列的你想查后缀匹配的内容它没有办法快速定位。但LIKE abc%是可以走索引的。业务上如果确实需要后缀搜索建议使用全文索引或搜索引擎而不是硬着头皮在MySQL里用左模糊。至于我在实际项目中印象最深的失效场景是对索引列使用OR连接非索引条件。比如WHERE user_id 1 OR status active如果只有user_id有索引而status没有MySQL可能放弃user_id索引转而走全表扫描。更稳的改写方式是拆成两个查询用UNION合并或者考虑给status也建索引。3.2 联合索引设计顺序、覆盖、冗余联合索引是最容易被误解的地方。很多人以为建了(a, b, c)三个字段的联合索引就相当于同时有了三个索引。这个理解不准确——它确实是“三个字段合起来排好序”的一棵索引树遵循最左前缀原则。查询条件从最左边的字段开始连续匹配时索引才能生效。比如索引(a, b, c)查询WHERE a 1 AND b 2能走索引WHERE b 2 AND c 3就走不了。设计联合索引时的字段顺序有一个朴素的原则把等值查询的字段放在前面把范围查询的字段放在后面。为什么因为联合索引在多个等值条件下可以精确定位到一个很小的区间而一旦遇到范围条件后面的字段就无法继续利用索引的排序了。举个例子一个订单表经常按WHERE user_id ? AND status ? AND create_time ?查询。索引设计为(user_id, status, create_time)就比(create_time, user_id, status)更贴合查询特征。另外一个很有价值的概念是覆盖索引。如果查询要的字段已经全部包含在二级索引的叶子节点上也就是“索引字段包含查询字段”那MySQL就可以直接在这个二级索引上完成查询不需要回表。覆盖索引在高频查询中的收益非常明显。比如用户表经常查SELECT user_name FROM user WHERE user_id 123那么可以建user_id和user_name的联合索引这个查询就完全不需要回表。但这里我要泼一盆冷水索引不是越多越好。每多一个索引就意味着每次INSERT、UPDATE、DELETE都要额外维护一棵B树。一个订单表如果有十几个索引写入性能会明显退化。我见过有团队为了优化各种查询先后建了一堆索引结果单条插入从1ms涨到了15ms。索引设计的思路应该是“少而精”——用尽量少的联合索引覆盖尽量多的核心查询路径同时定期排查和删除冗余索引。sys.schema_unused_indexes这个视图可以帮你找出从未被使用过的索引是清理冗余的好帮手。3.3 索引优化实操记录举一个我实际处理过的案例。某系统里的一个用户订单列表接口线上高峰期频繁超时慢查询日志里抓到了这样一条SQLSELECT order_id, total_amount, status, create_time FROM orders WHERE user_id 202518 ORDER BY create_time DESC LIMIT 20;orders表当时的数据量在800万行左右这显然不能允许全表扫描。但实际执行计划显示它扫描了接近50万行状态是Using filesort。分析下来表上只有user_id上的单列索引MySQL通过user_id定位到一个用户的几十万条订单然后全部捞出来做排序再取前20条。这其实已经是走了索引但排序导致了性能瓶颈。优化方案很直接修改索引为(user_id, create_time)的联合索引。这样索引本身就按用户和创建时间排好序了查询时可以沿着索引顺序直接取出前20条既过滤了user_id又省掉了filesort。改动之后这条SQL的耗时从平均1.2秒降到了15毫秒左右。整个过程没有改任何业务代码只是调整了一个联合索引的字段顺序加了一个排序字段。这也是为什么我一直强调不要一上来就改SQL改写先看索引设计是否匹配查询特征很多时候问题出在索引少了一个字段或者顺序不对。4. 拿高分的SQL写法与拿低分的错误写法索引优化做到位之后SQL写法本身的影响就开始凸显。很多SQL虽然也能走索引但写法低效导致扫描范围偏大、回表次数多、临时表和文件排序频繁。这一章我挑几个高频场景说透。4.1 改写思路减少扫描范围与回表次数最直接有效的优化方向就是让数据库“少干活”。同样的业务结果一条SQL扫描1万行和扫描100万行就算都走了索引性能也天差地别。第一个高频问题是SELECT *。对于OLTP系统我一直不推荐在生产环境无脑SELECT *。我理解很多人写SELECT *是图省事但在MySQL里SELECT *意味着如果表上有几十个字段InnoDB可能要把这些字段全部读出来不仅增加IO和网络传输还会破坏覆盖索引的优势。比如前面说的SELECT order_id, total_amount, status, create_time如果建了(user_id, create_time)的联合索引它其实还需要回表取total_amount和status但如果你只需要order_id和create_time这个查询就能完全走覆盖索引不回表。把查询字段缩减到实际需要的范围往往顺手就能减少大量回表。第二个高频问题是深分页。经典的翻页慢SQL长这样SELECT * FROM orders WHERE user_id 202518 ORDER BY create_time DESC LIMIT 100000, 20;MySQL执行这条SQL时会把前100020条数据都查出来然后丢掉前面的100000条只返回最后20条。数据量越大这种浪费越恐怖。优化方案有几种一是延迟关联先利用覆盖索引查出目标主键ID再用主键去关联回表取其他字段SELECT o.* FROM orders o INNER JOIN ( SELECT order_id FROM orders WHERE user_id 202518 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.order_id t.order_id;子查询在联合索引(user_id, create_time)上只需要扫描二级索引和主键不需要回表取完整行成本大大降低。另一种方案是基于上一页最大ID或时间做游标查询SELECT * FROM orders WHERE user_id 202518 AND create_time 2025-05-10 12:00:00 ORDER BY create_time DESC LIMIT 20;这种方式对用户无感且无论翻到第几页性能都稳定。但需要注意游标翻页和“跳页”这种需求的冲突产品上如果一定要支持随机跳页延迟关联才是更合理的选项。4.2 分页、排序、统计类SQL的优化要点排序优化的核心思路是尽量让排序操作利用索引完成避免Using filesort。如果查询条件中的字段和排序字段能组合成一个联合索引且排序方向与索引方向一致MySQL就可以直接按索引顺序读取数据不需要额外的排序步骤。比如WHERE user_id ? ORDER BY order_time DESC索引(user_id, order_time)就能支撑这个排序。关于统计类SQL比如COUNT和SUM我的建议是大表上的精确统计不要在业务高峰期直接跑。一个5000万行的订单表一次COUNT(*)就是一次全表扫描走二级索引也需要扫描几乎所有行。如果业务需要实时精确值可以考虑把统计数据维护到Redis里或者用汇总表定期累加。如果只是要个大概量级用EXPLAIN估算的行数或者information_schema.tables里的table_rows就能顶上差个几万行对报表来说通常无伤大雅。还有一类容易被忽略的写法是IN子查询和EXISTS的混用。对于MySQL 5.6以前的版本IN子查询优化得不好可能产生逐行执行子查询的低效行为5.6之后优化器做了大量改进大部分场景下IN和EXISTS已经能改写为相应的半连接semi-join性能差距不大。所以不必死记硬背“一定要用EXISTS”这种偏方而是要看执行计划——真正的问题是某些版本下关联子查询无法改写会退化成逐行扫描。遇到这种场景我的习惯是手动把子查询先查出来再在业务代码里做关联或者用JOIN代替效果通常更可控。5. 配置参数与硬件选型优化不能只盯SQLSQL和索引优化到一定程度后如果性能还没有达到目标就该考虑MySQL配置参数和硬件层面的问题了。但请注意顺序——一个SQL都写成全表扫描的系统你给它配128G内存、NVMe磁盘也是白搭反过来SQL已经优化到极致却依然慢那就得看看是不是MySQL本身没吃到该吃的资源。5.1 几个高性价比的MySQL配置参数MySQL的配置参数很多但真正对大部分业务影响最明显的集中在下面这几个InnoDB Buffer Pool这是InnoDB用来缓存数据和索引的内存区域。它的大小直接决定了热点数据有多少能留在内存里。如果是数据库专用实例我通常建议设置为物理内存的50%到70%。比如一台32G内存的机器buffer pool设为18G到22G比较合理。注意8.0里可以配置多个innodb_buffer_pool_instances来减少并发访问时的锁竞争但这个参数本身不能太大否则内存碎片反而增加。InnoDB_flush_log_at_trx_commit这个参数需要根据业务对数据安全的要求来权衡。设为1时每次事务提交都强制刷盘数据最安全但性能开销最大设为2时只写入操作系统缓存每秒批量刷一次盘性能明显提升但数据库进程崩溃时会丢最多1秒的事务。对于订单支付这类数据绝对不可以设为2但对于日志、点赞、阅读数这类容忍少量丢失的业务设置为2能带来可观的吞吐提升。我见过有人把核心交易库也设为2这是严重的性能与安全错位。max_connections连接数不是越大越好。每个连接都要占用线程和内存连接数太多反而加剧上下文切换和锁竞争。更稳妥的做法是限制连接数同时在应用层用连接池把并发控制在合理范围。MySQL官网默认151如果业务并发确实高可以逐步调到500到1000同时观察Threads_connected是否频繁打满。如果打满了优先检查应用层是不是有连接泄漏而不是无限调大这个参数。temp_table_size和max_heap_table_size这两个参数影响临时表能占用多少内存。如果排序和分组操作生成的临时表超过内存限制MySQL会把临时表写到磁盘上性能会断崖式下降。调大这两个参数能改善这种情况但要注意同时设置两者中的较小值才生效。5.2 什么时候该升级硬件配置参数调完之后如果瓶颈确实在硬件层那么该升级还是得升级。我这里说的“该升级”是指SQL已经优化、索引已经合理、配置已经达标但业务增长带来的数据量和并发量已经超出了当前机器的处理能力。此时与其继续压榨数据库不如考虑扩展。硬件层面最直接的收益来自磁盘。传统机械盘换成固态盘尤其是NVMe盘对大量随机IO场景的提升非常明显。以前我们在老机器上跑一个数据量两个亿的分析表一条统计SQL要30多秒换到NVMe盘之后同样的SQL只要8秒。磁盘IO往往是数据库最容易感知的瓶颈这个钱花得通常很值。内存也是常见的升级选项。buffer pool变大热点数据全部缓存很多查询直接从内存返回这比任何参数调优都见效快。但内存升级有一个预算边际递减效应——当buffer pool已经能覆盖整个热数据集时继续加内存的收益就很小。CPU则对复杂计算型SQL更敏感。排序、分组、正则、JSON处理这些操作都吃CPU。如果慢查询日志里大量SQL的扫描行数不多但CPU时间很长那升级CPU确实能解决一部分问题。另外如果业务已经是多实例部署可以考虑把读流量分流到只读从库让主库专注于写入。读写分离的收益通常比单纯升级主库硬件更大。6. 问题排查与避坑记录最后分享几个典型的线上问题排查思路和踩坑心得。这些场景很多人应该都遇到过我尽量把排查的思维链条讲清楚而不是只给结论。6.1 线上SQL突然变慢的排查思路最让人头疼的问题不是“一直慢”而是“以前挺快突然变慢了”。遇到这种情况我的排查顺序大致是先看监控有没有异常——CPU、磁盘IO、连接数、慢查询数量有没有同时飙升。如果有先恢复服务再查原因。第二步是看表结构和索引有没有变化。线上最常见的情况是某天新加了一个索引结果MySQL优化器为另一条SQL选择了新的执行计划而这个计划更差。我遇到过几次加索引后反而把别的查询带慢的情况这不是索引本身的问题而是统计信息和执行计划的选择问题。解决办法是检查EXPLAIN必要时用FORCE INDEX先稳住再分析为什么优化器做了错误的选择。第三步是看数据量变化。一张表从100万行涨到1000万行之前合理的执行计划可能就变得不合理了。比如分页排序的场景数据量小时全表扫描加文件排序可能比走索引还快优化器选了前者数据量大后这个计划就崩了。这种问题要定期复查执行计划特别是数据量快速增长的接口。第四步是看统计信息是否过期。InnoDB的优化器依赖一些统计信息来估算扫描行数。如果统计信息严重失真优化器可能选择一个实际扫描量巨大的执行计划。遇到这种情况执行ANALYZE TABLE刷新统计信息往往就能解决问题。注意这个操作本身会短暂占用资源尽量在低峰期做。6.2 几个印象深刻的踩坑场景第一个坑是关于EXPLAIN的误读。很多初学者看到执行计划里出现了index就以为已经走了索引、性能很好。实际上index代表的是全索引扫描和全表扫描的区别只是数据源不同性能差距并不大。真正的快速路径是ref、eq_ref或const它们才表示通过索引精确定位到少量行。我建议大家看EXPLAIN时重点关注type、key、rows这三维type至少要是range才算可以接受。第二个坑是在大表上直接加索引。一张5000万行的表直接执行ALTER TABLE ADD INDEX在InnoDB里默认会锁住整表写入业务直接不可用。我在处理这类操作时通常会提供两种方案如果表不大可以接受短暂锁表就在低峰期执行如果表很大且业务不能停务必使用在线DDL工具来执行尽量减小对线上写入的影响。这个经验是我付出过线上事故代价才学到的做DBA和架构的同行一定要重视。第三个坑是参数调整后没有复测。有个项目同事把innodb_buffer_pool_size直接从8G调到24G结果系统内存不足触发OOM数据库直接挂掉。原因是他没看机器上还有其他服务占着内存。这里我给个最朴素的建议任何参数调整先看操作系统当前的内存分配情况调完之后要持续观察至少一个业务高峰期确认没有副作用再彻底收尾。第四个坑和业务代码有关。数据库层面的优化做得再漂亮也架不住应用层“强行放大”查询量。有一次某展示页面的接口代码里循环调用了同一个查询SQL几十次每次都是单点查询单次耗时1毫秒但整个接口要跑几十毫秒再加上网络损耗就慢得离谱。后来改成批量查询接口时间一下子掉了90%。所以排查慢接口时不要只看SQL本身业务代码里的循环、串行调用、连接获取方式都可能成为隐形瓶颈。写到最后的一点心得MySQL优化这条路不存在一步到位的银弹更没有哪套配置能通吃所有业务。我在处理过足够多的线上问题之后最大的体会是优化工作的核心并不在于你知道多少技巧而在于你是否建立了完整的排查框架。先度量再定位然后针对性地做最小改动每一步都验证效果这是最笨、也是最有效的方法。最后再分享一个使用习惯把每一次性能优化的背景、现象、分析过程、改动内容和效果数据记录成文档。这个习惯带来的好处是半年后回头看你能清楚地知道每一个索引为什么存在、每一项配置为什么是这个值而不是对着线上数据库猜谜。持续积累下来这些记录会变成团队里最有价值的运维资产。遇到新问题的时候翻一翻旧文档往往能比重新排查更快地找到思路。
