1. 接口慢不一定怪代码先揪出慢 SQL 再说排查过不少“接口越跑越慢”的问题说实话十次里有七八次根子都出在数据库那一层。前端拿不到数据后端日志里看时间全耗在等待数据库返回而数据库那边往往就是某几条 SQL 没走索引或者查出来的数据量远超预期。今天这篇不聊玄乎的架构设计就把我平时处理慢 SQL 的完整套路摊开来讲从定位、分析到优化落地每一步都给你能直接照抄的命令和思路。这个教程适合谁后端开发、运维、以及那些被线上告警逼着查慢接口的同学。哪怕你对 MySQL 只是刚入门只要跟着走一遍流程也能在下次接口变慢时不再像个无头苍蝇一样瞎猜。我会用真实场景里的案例来讲涉及执行计划、索引、explain、profile 这些东西都是我日常最常用的手段。先说个结论优化慢 SQL 不是把所有查询都改成“最快”的形式而是在满足业务逻辑的前提下让数据库用最小的代价把数据捞出来。很多时候少查一个字段、多建一个索引、调整一下查询顺序效果比换机器明显得多。2. 第一步是定位慢 SQL别等用户先喊卡2.1 打开慢查询日志让数据库自己“举报”自己MySQL 本身自带慢查询日志只是很多默认配置没打开。我接手过的不少项目上线两年了慢查询日志还是关闭状态等于让犯人逍遥法外。开启方式很简单有两招临时开启和持久化配置。临时开启适合在测试环境或者想立即生效时用SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;这里long_query_time 1表示执行时间超过 1 秒的 SQL 都会被记下来。生产环境我一般建议从 1 秒起步如果日志太多再调大。有些团队一开始就设成 0.1 秒结果日志文件每小时几个 G最后反而没人看了得不偿失。持久化配置需要在my.cnf或my.ini的[mysqld]段加上slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1注意最后一行log_queries_not_using_indexes它会把所有没走索引的查询也记录下来哪怕执行时间没超过阈值。这个开关对发现潜在问题 SQL 特别有用因为我碰到过不少 SQL 执行只要 0.2 秒但每次调用全表扫描数据量一涨就直接拖垮接口。先把它打开你会发现很多“隐形炸弹”。日志文件里列的每一条记录都包含时间、用户、主机、查询耗时、扫描行数以及具体 SQL 文本。我建议先看扫描行数扫描行数如果接近返回行数说明索引利用得不错如果扫描几百万行只返回几十行这 SQL 基本没救了。除了直接看文件也可以用mysqldumpslow工具做聚合分析把最耗时的前几条拉出来。比如mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log它会按总耗时排序显示最热的 10 条慢 SQL。不过我个人更习惯直接打开日志文件配合 grep 和 awk 处理因为有些业务 SQL 带一堆参数聚合后反而看不清原始形态。2.2 用 performance_schema 细查接口链路中的数据库耗时慢查询日志能抓到单条 SQL 的问题但有些接口慢是因为在一个事务里连续执行了十几条 SQL单看每一条都不算慢加在一起就超时了。这种时候就得靠performance_schema里面的表来做链路统计。MySQL 5.7 以上默认开启 performance_schema可以直接查events_statements_summary_by_digest这类汇总表。我常用的查询是找出某个时间段内执行次数多、平均耗时高的 SQL 类型SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms, SUM_ROWS_EXAMINED, SUM_ROWS_SENT FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME IS NOT NULL ORDER BY AVG_TIMER_WAIT DESC LIMIT 20;这里DIGEST_TEXT是把具体参数替换成?后的模板化 SQL方便同类归并。比如某条查询条件里的用户 ID 一直在变但 SQL 骨架相同就会被归到一行统计这样能看出这类型查询的整体表现。我用这个方案排查过一个案例接口 A 本身只查用户信息但每次调用还附带查了一遍用户的订单总数而订单总数是通过COUNT(*)全表扫描出来的。单次耗时 0.8 秒不触发慢查询日志阈值但 QPS 一高接口就成片超时。靠 performance_schema 把这类高频慢查询抓出来后改成冗余一个计数字段或者用缓存问题瞬间解决。定位慢 SQL 的大原则是先抓“多”的次数多再抓“久”的单次久结合接口调用链路基本不会漏掉真正的祸首。3. 用 EXPLAIN 看执行计划拆穿 SQL 的真实成本3.1 EXPLAIN 输出里到底要盯哪些列拿到一条慢 SQL 后第一步先加EXPLAIN看它是怎么执行的EXPLAIN SELECT * FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;输出会有一张表我几乎只看下面这几列type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描基本是性能杀手。index虽然走了索引但如果扫描的是整个索引树代价也不低。possible_keys可能用到的索引但不代表实际用了。key实际使用的索引。如果为 NULL说明没走索引。rows估算的需要扫描的行数。这个数字越大SQL 一般越慢。Extra重点看Using filesort、Using temporary、Using index、Using where这些关键字。Using filesort代表排序没走索引需要在内存或磁盘上额外排序数据量大时会非常慢。Using temporary更糟说明查询中需要建临时表常见于GROUP BY的字段没有索引的情况。而Using index是好消息表示覆盖索引生效查询所需字段全在索引里可以避免回表。举个例子我之前优化过一个订单列表接口原来的 SQL 是SELECT * FROM orders WHERE status 1 AND create_time 2024-01-01 ORDER BY create_time DESC;EXPLAIN 显示typeALLrows800000ExtraUsing where; Using filesort。这张订单表有 80 万行全部扫一遍确实不慢才怪。后来加了联合索引(status, create_time)后EXPLAIN 变成typerefrows12000ExtraUsing index condition; Using filesort。扫描行数降了两个数量级接口从 2 秒降到 200 毫秒。虽然排序还是没走索引但行数少了排序代价也能接受。3.2 从执行计划反推索引缺失和多余不少开发同学对索引的理解就是“给 where 字段加索引”。但实际工作中索引设计要根据一条 SQL 的完整执行路径来定。比如上面那个例子条件有status和create_time排序也有create_time所以联合索引(status, create_time)一举两得等值条件放在前面范围条件放在后面同时也覆盖了排序字段。如果 SQL 中还有ORDER BY设计索引时尽量让排序字段也在索引中且顺序和排序要求一致这样才能避免Using filesort。比如ORDER BY create_time DESC索引里的create_time升序或降序都能用因为 MySQL 8.0 支持降序索引在 5.7 里反向扫描也不吃亏。但要注意索引不是越多越好。每多一个索引写入和更新时就要多维护一棵 B 树代价很实在。我在生产环境见过一张 10 个索引的表每次插入都慢得离谱排查半天才发现是索引过多导致。所以加索引前先想清楚这条 SQL 是不是高频 SQL加了之后对写入影响多大如果有能合并的联合索引就用联合索引替代多个单列索引。3.3 覆盖索引和回表决定查询快慢的关键SELECT *在很多慢 SQL 里是帮凶。当索引中不包含所有需要的字段MySQL 就需要根据主键回表读取完整行。一次回表是随机 IO几千次回表就能形成明显的延迟。我优化过一条导出报表的 SQL原来写的是SELECT * FROM order_detail WHERE order_id IN (……)其中order_id有索引但*需要回表把 20 多个字段全部捞出来数据量一多就卡。改成只查业务需要的字段后配合覆盖索引速度提升非常明显。怎么判断符不符合覆盖索引条件EXPLAIN 的Extra列出现Using index就表示完全覆盖。养成习惯查询列表只写必要字段别图省事写*既能减少网络传输又能提升覆盖索引命中率。4. 针对具体场景的慢 SQL 优化实操4.1 分页深了慢到爆炸试试延迟关联列表接口最常见的问题就是深分页。比如前端点第 100 页SQL 可能写成SELECT * FROM orders ORDER BY id DESC LIMIT 10000, 20;这条 SQL 的 offset 是 10000意味着 MySQL 要把前 10000 条数据读完再扔掉然后才取第 10001 到 10020 条。如果总数据量几十万翻到后面几页每条查询都要扫几万行接口自然顶不住。优化方案我常用延迟关联也叫“先查主键再回表查详情”SELECT * FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id DESC LIMIT 10000, 20 ) t ON o.id t.id;子查询只查主键id这个操作能走覆盖索引速度极快。拿到 20 个主键后再回表查完整记录只回 20 次代价小得多。实测在 50 万行的表上原 SQL 耗时 1.8 秒改后直接降到 40 毫秒效果立竿见影。注意延迟关联适用于按主键或唯一索引排序的场景。如果排序字段不是索引子查询自身可能还会 filesort需要进一步优化排序字段。4.2 JOIN 查询慢多半是驱动表和索引顺序出了问题一条好的 JOIN 语句驱动表左表结果集应该尽量小被驱动表的连接字段必须要有索引。我在一个报表任务里遇到过SELECT * FROM users u LEFT JOIN orders o ON u.id o.user_id LEFT JOIN order_items oi ON o.id oi.order_id WHERE u.reg_time 2023-01-01 AND o.status 1;执行计划显示第一行走了全表扫描u 是驱动表结果集有几万行再拿这几万行去和 orders 匹配orders 没建索引性能直接崩了。优化思路分两步。第一步调整查询顺序尽量减少第一个驱动表的数据量。这里把用户条件提前先查出一小撮用户再往后面关联。第二步给orders.user_id、order_items.order_id加上索引让每一步 JOIN 都能快速查到对应记录。改完后 EXPLAIN 的rows从几万降到几十整个报表从分钟级降到秒级。关于 JOIN经常听到“小表驱动大表”的说法本质上就是让外层循环次数最少。MySQL 优化器一般会自己选但 SQL 写得过于复杂时优化器也可能选错。这时候可以在 JOIN 条件里加 STRAIGHT_JOIN 强制指定顺序或者增加use index提示不过这些属于高级玩法常规项目里先把索引补好更有效。4.3 GROUP BY / COUNT / DISTINCT 的隐藏陷阱统计类接口慢多数是GROUP BY或COUNT(DISTINCT ...)导致临时表和文件排序。比如SELECT user_id, COUNT(*) FROM order_logs GROUP BY user_id HAVING COUNT(*) 10;如果order_logs表有几十万行GROUP BY user_id先要全表扫描再把结果放进临时表分组。这时user_id若没有索引SQL 就会在 External temporary table 上折腾一轮慢得理所当然。解决方式是建立(user_id)单列索引或(user_id, 其他统计维度)联合索引让GROUP BY直接走索引有序扫描省掉临时表。另一种做法是改写 SQL比如把HAVING COUNT(*) 10换成子查询限制范围但这要看业务逻辑是否允许。COUNT(DISTINCT col)在数据量大时很容易被滥用。能提前在业务层面去重的就不要让数据库实时算。我经手过一个看板接口统计每天的独立访问用户数原来每次请求都COUNT(DISTINCT user_id)用户量一上来接口直接超时。后来把结果缓存到 Redis每小时增量更新一次查询从秒级降到毫秒级。这不是逃避 SQL 优化而是根据业务场景选择更合适的方案。4.4 OR、IN、LIKE 这些条件怎么写才不走偏很多慢 SQL 都是因为条件写法导致索引失效。常见坑我列一下OR条件如果OR两边的字段不是同一个索引MySQL 可能选择全表扫描。改成UNION ALL往往能走索引。LIKE %关键词%前导通配符会导致索引失效。业务允许的尽量改成LIKE 关键词%或者用全文索引、ES 等方案。IN大列表IN 列表元素过千时优化器不一定继续走索引甚至会拆成多路查找。可以考虑分批查询或用临时表。举个例子原来接口支持按多个状态查询订单SELECT * FROM orders WHERE status 1 OR status 2 OR status 3;这种写法即使 status 上有索引优化器也可能选择全表扫。改成WHERE status IN (1,2,3)索引利用情况会好很多。如果是多个字段的 OR比如WHERE name x OR phone y拆成两条查询再UNION ALL往往更赚。5. 从单条 SQL 到整体调优参数配置和表结构设计5.1 缓存参数调一调数据库响应速度肉眼可见有时候 SQL 本身没问题但数据库整体吞吐上不去接口自然被拖慢。MySQL 的内部缓存参数优化属于“免费午餐”尤其适合数据库服务器内存比较充足的场景。最核心的一个参数是innodb_buffer_pool_size它决定 InnoDB 缓存数据和索引的内存池大小。很多机器内存 16G这个参数还默认是 128M等于让数据库天天拿磁盘当内存用。我建议设置为主机物理内存的 60% 到 75%比如 16G 内存设 10Ginnodb_buffer_pool_size 10G改完后重启 MySQL 或使用动态调整SET GLOBAL innodb_buffer_pool_size 10 * 1024 * 1024 * 1024;注意动态修改在 5.7 及以上版本才支持而且最好在低峰期操作。改完后用SHOW ENGINE INNODB STATUS查看缓冲池命中率如果命中率长期低于 99%说明缓存还是太小。还有两个容易被忽视的参数innodb_flush_log_at_trx_commit默认 1 表示每次提交都刷盘安全但慢。如果业务对数据丢失容忍度稍高比如日志类可以改成 2提升明显。max_connections连接数上限设得太低接口稍一并发就排队表现就是“越跑越慢”。需要结合SHOW STATUS LIKE Threads_connected观察实际值留出裕量。不过改参数前一定要先确认瓶颈在数据库实例层面而不是某条 SQL。否则参数再大SQL 全表扫描照样卡。5.2 表结构设计与字段类型的隐性影响慢 SQL 也经常源于表结构设计不合理。比如存储订单金额用varchar查询时要隐式转换索引直接失效。我常遇到的是手机号存成 varchar 但查询时忘了加引号SELECT * FROM users WHERE phone 13800138000;phone是 varchar但条件写的是数字MySQL 会先把字段转成数字再比较结果索引用不上。改成phone 13800138000就好了。这也是慢 SQL 排查中容易忽略的细节。另外字段过多的大宽表也容易让查询慢因为每行记录占用的空间大同样大小的缓冲池能缓存的行数就少。我优化过一个订单宽表里面冗余了十来个冗余字段后来拆成核心订单表和扩展信息表按需关联查询性能和写入性能都明显提升。字段默认值也有讲究。比如创建时间created_at设置为DEFAULT CURRENT_TIMESTAMP更新时间设置为自动更新能减少很多应用层负担。但有些表用了DEFAULT NULL导致查询条件误判也会带来隐性问题。这些虽然不像索引那么瞬时见效但积少成多对整个接口稳定度帮助很大。5.3 当单表太大时考虑分区或归档一张表几千万上亿行即使有的索引查询也未必快。这时候最有效的思路是“缩小数据范围”。我有一次处理一个消息流水表每天新增百万行历史数据需要保留一年结果任何查询都像是在海里捞针。后来做了两件事按月份 RANGE 分区查询时带上时间条件MySQL 自动只扫描对应分区把超过 6 个月的流水定期归档到历史库表线上主力表始终保持可控行数。分区和归档的改造不算难但要注意分区字段必须包含在主键中否则 MySQL 不允许创建分区表。归档建议通过定时任务在低峰期分批执行避免一次性锁表堵死线上接口。6. 常见问题与排查技巧实录6.1 慢 SQL 定位到了但 EXPLAIN 显示走索引还是很慢这种情况不罕见。我之前遇到过一条 SQLEXPLAIN 显示keyidx_user_idrows200但实际执行要 1 秒多。后来发现问题是高版本 MySQL 的优化器统计信息不准导致估算行数远小于实际行数。解决方案是执行ANALYZE TABLE 表名更新统计信息。还有一种情况是索引选择性太差。比如性别字段建了索引区分度太低优化器虽然用了它但扫出来的数据几乎覆盖半个表回表成本更高。这种情况下优化器有时候会“聪明反被聪明误”直接改用全表扫。解决方式是考虑组合索引或者去掉这类低区分度索引。如果是查询条件里带函数比如SELECT * FROM orders WHERE DATE(create_time) 2024-01-01;即使 create_time 有索引DATE 函数导致索引失效。改成范围条件SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;索引就能正常使用了。6.2 加了索引之后插入和更新变慢了怎么平衡索引确实会增加写放大。线上有个场景是订单状态频繁更新每次 update 都要更新所有二级索引。后来发现有一半二级索引几乎没被查询用到纯属给更新添堵。我逐个对比慢查询日志和实际业务代码删掉三个冗余索引后更新耗时下降了 30%。删索引要谨慎先确认没有隐藏的查询依赖最好在测试环境模拟核心接口回归然后再到生产低峰期操作。另外 MySQL 8.0 支持索引不可见ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;先隐藏一段时间观察业务有没有异常确认没问题再真正删除这个过程非常安全。6.3 用好 profile 看每个阶段的耗时占比如果 EXPLAIN 看不出问题可以用 profiling 精确分析 SQL 执行各个阶段的时间SET profiling 1; -- 执行你的慢 SQL SHOW PROFILES; SHOW PROFILE FOR QUERY 1;输出会列出executing、Sending data、statistics等阶段各耗时多少。Sending data占比高通常意味着大量数据从存储引擎层返回statistics占比高可能是统计信息计算有问题Creating sort index则是排序开销大。靠这个工具能精准定位瓶颈在索引扫描、排序还是网络传输比盲猜靠谱得多。6.4 一个真实完整的优化案例复盘最后来个复盘。曾经有个电商后台的订单列表接口每天下午高峰期开始变慢用户点一次要 4 到 5 秒。我先抓慢查询日志发现一条SELECT * FROM orders WHERE user_id ? ORDER BY id DESC LIMIT 20;用 EXPLAIN 看typeALLrows300000没有可用索引。下一步检查表结构发现没有针对user_id建索引。原因也很现实当初上线为了省事只建了主键索引。加了一个(user_id, id)联合索引再跑 EXPLAINtyperefrows20接口耗时降到 50 毫秒。之后我又想这个接口是不是总按用户查最近订单于是再看了下业务代码发现还有个管理后台需要按状态筛选。于是把索引扩展成(user_id, status, id)既覆盖了按用户查询也覆盖了按用户状态过滤一次解决两类查询。这条优化前后只用了 20 分钟效果持续至今。日常工作中大部分慢 SQL 就是这么按部就班解决的不需要多高深的技巧关键是有章法定位 - 分析 - 改索引或改 SQL - 验证。真要说什么心得就是别把慢 SQL 优化当成一次性的救火行为平时监控、代码 review 时多看一眼 SQL 写法比事后补坑舒服太多。