数据库工程实战：SQL优化从入门到生产落地

最新推荐文章于 2026-07-24 18:49:26 发布

原创最新推荐文章于 2026-07-24 18:49:26 发布 · 382 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

话题

#暑假·七月创作之星博客挑战赛

数据库工程实战：SQL优化从入门到生产落地

做后端开发的人，几乎都遇到过这样的场景：业务量刚破百万，数据库CPU突然冲到99%，核心接口从几百毫秒直接飙到十几秒，线上告警短信一条接一条往手机里蹦。运维同学在群里疯狂@人，产品经理站在身后问“什么时候能恢复”，你盯着满屏的慢查询日志，翻了半天却不知道从哪下手。很多人把SQL调优当成“加索引就能解决”的简单事，真到了生产环境才发现，同样一条SQL，加错索引反而会让性能更差，看似简单的关联查询，在千万级表上能跑出几十秒的结果。今天我就结合自己在电商订单系统里踩过的坑，把从慢查询定位到索引落地、再到全链路优化的完整流程拆解清楚，帮你把那些拖垮系统的SQL，从“慢得离谱”改成“快得飞起”。

一、SQL优化不是玄学，是数据库工程的核心基本功

很多开发者对SQL优化的理解，还停留在“给where条件加个索引”的层面，觉得这只是DBA的专属工作，普通业务开发没必要深究。但在实际生产环境里，80%以上的数据库性能瓶颈，根本不是硬件不够用，而是写出来的SQL没有被正确执行。我之前接触过一个日均订单量20万的电商系统，刚上线三个月，订单表数据量突破800万，后台的订单统计接口每次查询都要等15秒以上，运营人员导出报表的时候，直接把主库的连接数打满，导致全平台用户都无法下单。一开始团队里的同学想当然地给订单表的所有查询字段都加上了索引，结果索引数量从3个涨到12个，写入性能直接下降了40%，高峰期订单提交经常出现超时。后来我们花了两天时间，把所有慢查询全部梳理了一遍，删掉了7个完全没用的冗余索引，重新设计了3条联合索引，最后接口响应时间直接降到了200毫秒以内，数据库CPU使用率从95%稳定到了30%左右。

这件事之后我才意识到，SQL优化从来不是“头痛医头脚痛医脚”的临时补救手段，而是数据库工程里贯穿需求设计、代码编写、上线运维全流程的核心能力。它不需要你掌握多么高深的数据库内核原理，但要求你能站在优化器的视角，理解一条SQL从提交到返回结果的完整执行路径：优化器是怎么选择索引的？扫描行数和回表成本是怎么计算的？关联查询的时候驱动表是怎么确定的？这些问题想明白了，你写出来的SQL天然就不会慢，遇到性能问题的时候也不会乱加索引瞎试。

二、用Explain定位慢查询，别再靠“猜”调优很多人调优SQL的第一步就错了：拿到一条慢SQL，上来就直接加索引，改完之后在测试库跑一下很快就上线，结果到了生产环境数据量不一样，反而跑得更慢。正确的做法永远是先搞清楚这条SQL当前是怎么执行的，再针对性地做优化，而Explain就是我们看懂执行计划最核心的工具。很多人用Explain只会看type字段，觉得ALL就是全表扫描，看到就慌，其实Explain返回的每一列信息，都藏着优化器的执行决策，把这些信息拼起来，你就能完整还原这条SQL的执行全过程。

1、Explain核心字段的实战解读首先我们先看最基础的执行计划输出，拿一条常见的订单查询SQL举例：

EXPLAIN SELECT * FROM order_info

WHERE user_id = 10086 AND create_time >= '2025-01-01'

ORDER BY order_no DESC;

很多人看完这个执行计划，只关注type是不是ALL，其实我们要按顺序看这几个关键字段：第一是id字段，它代表执行计划中表的加载顺序。如果id相同，执行顺序从上到下；如果id不同，id值越大越先执行。比如你写了一个带子查询的SQL，子查询的id会比外层查询大，优化器会先执行子查询得到临时结果集，再和外层表做关联。很多人写的慢查询里，子查询的id特别大，扫描了几十万行数据生成临时表，外层再关联的时候自然就慢了。第二是type字段，它代表访问数据的方式，性能从差到好依次是ALL、index、range、ref、eq_ref、const、system。很多人以为ALL就是绝对不能出现，其实在只有几百行数据的小表里，全表扫描的性能反而比走索引更好，因为优化器选择索引本身也有成本。但在数据量超过100万的表里，出现ALL就意味着你要扫描全表所有数据，这时候必须重点优化。第三是key和key_len字段，key代表优化器最终选择使用的索引，key_len代表使用到的索引字节数。很多时候你明明建了联合索引，但是执行计划里key显示的却是另一个单值索引，这说明优化器判断走这个索引的成本更低，这时候你就要看扫描行数的差异，判断优化器是不是选错了执行计划。第四是rows和filtered字段，rows代表优化器预估需要扫描的行数，filtered代表返回结果占扫描行数的百分比。比如一条SQL的rows是100万，filtered是1%，意味着它要扫描100万行数据，最后只返回1万行结果，这说明你选的索引过滤性特别差，大量无效数据被扫描了出来。第五是Extra字段，这个字段里的信息是最容易被忽略，但也是最能暴露问题的地方。如果Extra里出现Using filesort，说明数据库在内存或者磁盘里做了额外的排序操作，这种操作的性能特别差，尤其是排序的数据量超过sort_buffer_size的时候，会产生大量的临时文件IO。如果出现Using temporary，说明数据库创建了临时表来保存中间结果，常见于group by、distinct多字段的场景，临时表的读写开销会直接把查询速度拖慢好几倍。如果出现Using index，说明你用到了覆盖索引，不需要回表就能拿到所有查询字段，这是SQL优化里性价比极高的优化手段。

2、Explain对比实战：同一条SQL的两种执行路径我之前在订单系统里遇到过一条特别典型的慢SQL，业务需求是查询某个用户在2025年之后的所有订单，并且按订单号倒序排列。一开始开发同学直接写了这样的SQL，只给user_id建了一个普通的单值索引，执行计划出来之后，type是ref，key用了user_id索引，rows预估是1200行，Extra里出现了Using filesort。当时开发同学觉得rows只有1200，应该很快，结果线上这条SQL平均响应时间是3.2秒，高峰期甚至超过10秒。后来我们给这个表建了一个联合索引idx_user_create_no(user_id, create_time, order_no)，重新执行Explain之后，执行计划发生了明显的变化：type变成了ref，key_len从4字节变成了28字节，说明联合索引的三个字段都被用到了，rows还是1200行，但Extra里的Using filesort消失了，变成了Using index。优化之后这条SQL的响应时间直接降到了2毫秒，性能提升了1600倍。我们把两次的执行计划整理成对比表格，就能清晰看到差异：

执行计划字段

优化前（单值索引user_id）

优化后（联合索引idx_user_create_no）

type

ref

key

idx_user_id

idx_user_create_no

key_len

rows

1200

Extra

Using where; Using filesort

Using index

实际响应时间

3200ms

2ms

很多人会疑惑，为什么扫描行数一样，性能差距这么大？因为优化前的执行路径是：先通过user_id索引找到这个用户的所有订单主键，然后回表1200次拿到所有行的create_time字段，过滤出符合时间条件的数据，最后在内存里对剩下的数据做filesort排序。而优化后的联合索引，本身就是按照user_id、create_time、order_no的顺序排序存储的，找到user_id对应的位置之后，直接往后遍历就能拿到所有符合时间条件的数据，而且order_no已经是预先排好序的，不需要额外排序，也不需要回表，直接从索引里就能拿到所有需要的字段，自然速度就快了几百倍。

三、可落地的索引策略示例，避开90%的常见坑很多人建索引的时候，要么是想到哪个字段就给哪个字段加索引，最后索引泛滥写入性能暴跌，要么是不敢建索引，遇到慢查询就束手无策。其实索引设计是有一套可落地的方法论的，我在千万级订单表里总结出来的这几个索引策略，几乎可以覆盖80%以上的业务场景。

1、最左匹配原则不是死规则，要结合业务查询顺序设计很多人背最左匹配原则的时候，只知道联合索引要遵循从左到右的匹配规则，但是实际设计的时候，经常把区分度最高的字段放在最右边，导致索引利用率特别差。比如订单表的查询条件，常见的有user_id、order_status、create_time这三个字段，很多人会想当然地把create_time放在最左边，建一个idx_create_status_user的联合索引，结果这个索引几乎用不上。因为同一个时间点可能有成千上万个订单，区分度特别低，优化器宁愿选择全表扫描，也不愿意走这个索引。正确的设计思路是，把等值查询的字段放在最左边，范围查询的字段放在最右边。比如user_id是等值查询，order_status是等值查询，create_time是范围查询，那联合索引的顺序就应该是idx_user_status_time(user_id, order_status, create_time)。这样的话，所有带user_id的查询、带user_id+order_status的查询、带user_id+order_status+create_time的范围查询，都能用上这个索引，一个索引就能覆盖三类业务场景，不用重复建多个冗余索引。我之前遇到过一个团队，给这三个字段分别建了三个单值索引，结果索引占用的空间是联合索引的3倍，写入的时候每次提交订单都要写三次索引，高峰期TPS直接上不去。后来我们删掉了三个单值索引，换成了这个联合索引，写入性能提升了35%，所有相关的查询速度反而更快了。

2、覆盖索引是性价比最高的优化手段，能不用回表就别回表 InnoDB引擎的索引结构里，二级索引的叶子节点保存的是主键值，当你通过二级索引找到主键之后，还要回到聚簇索引里再查一次才能拿到完整的数据，这个过程就叫回表。回表是随机IO操作，当你要扫描几千行数据的时候，几千次随机IO的开销特别大，这也是很多SQL明明走了索引还是很慢的核心原因。覆盖索引的核心思路就是，把你查询需要用到的所有字段，都放到联合索引里，这样直接从二级索引的叶子节点就能拿到全部数据，完全不需要回表。比如你要统计某个用户2025年的订单总金额，原来的SQL是这样的：

SELECT sum(order_amount) FROM order_info

WHERE user_id = 10086 AND create_time >= '2025-01-01';

如果你只建了idx_user_id(user_id)索引，执行的时候需要先通过user_id找到所有订单的主键，然后回表拿到每一行的create_time和order_amount字段，再做过滤和求和。如果我们建一个联合索引idx_user_time_amount(user_id, create_time, order_amount)，整个查询过程就完全不需要回表，直接遍历索引就能拿到所有需要的数据，查询速度能提升几十倍。我之前在一个千万级订单表里做过测试，统计某个用户的年度订单总额，用普通单值索引的时候需要1.2秒，换成覆盖索引之后，响应时间直接降到了10毫秒以内，性能差距超过100倍。

3、索引下推优化，把过滤逻辑下推到索引层 MySQL 5.6之后推出的索引下推特性，很多人知道但不会用，这个特性可以在索引遍历的时候，直接用索引里的字段做过滤，把不符合条件的数据直接过滤掉，减少回表的次数。比如我们有一个联合索引idx_user_name(user_id, user_name)，现在写这样一条SQL：

SELECT * FROM order_info

WHERE user_id = 10086 AND user_name LIKE '张%';

如果没有索引下推，优化器会先把user_id=10086的所有记录全部回表，拿到user_name字段之后，再过滤出符合like条件的数据。如果这个用户有1万条订单，就要回表1万次。开启索引下推之后，优化器会在索引遍历的时候，直接判断user_name是不是以“张”开头，把不符合条件的记录直接跳过，最后只对符合条件的几百条数据做回表，回表次数直接减少了90%以上。很多人写SQL的时候喜欢在索引字段上做函数运算，比如把user_name字段用left函数处理之后再做判断，这样会直接导致索引下推失效，优化器无法在索引层做过滤，只能全量回表，性能直接暴跌。所以我们在写查询条件的时候，一定不要在索引字段上套函数，保持字段本身出现在where条件的左边，才能让索引下推正常生效。

4、避免索引失效的常见场景，别写看似走索引实际全表扫描的SQL 我见过太多开发同学写出来的SQL，明明建了索引，但是执行计划里type还是ALL，全表扫描几十万行数据，最后把数据库拖垮。这些常见的索引失效场景，一定要在写SQL的时候就避开：第一是隐式类型转换，比如user_id字段是int类型，你写where user_id = '10086'，虽然MySQL会自动做类型转换，但是索引会直接失效，优化器只能走全表扫描。我之前遇到过一次线上故障，就是因为前端传过来的user_id是字符串类型，后端SQL直接拼接进去，导致原本毫秒级的查询变成了十几秒，瞬间打满了数据库连接。第二是用or连接没有索引的字段，比如where user_id = 10086 or order_status = 1，order_status字段没有建索引，整个索引就会失效，优化器直接选择全表扫描。遇到这种场景，最好的做法是把SQL拆成两条单独的查询，用union all把结果合并起来，两条SQL各自走自己的索引，性能会比原来好很多。第三是like查询以%开头，比如where user_name like '%张%'，这种写法无法利用索引的有序性，只能全表扫描。如果是模糊搜索的场景，不要强行用普通索引优化，应该接入专门的搜索引擎，比如Elasticsearch，用倒排索引来实现全文检索。

四、真实查询优化案例：

从18秒到30毫秒的完整改造过程讲完了理论和策略，我给大家分享一个我去年在电商大促前处理的真实慢查询案例，这条SQL当时是整个系统里最大的性能隐患，优化前平均响应时间18秒，高峰期直接超过30秒，每次运营后台一导出报表，整个主库的CPU就直接冲到100%。

1、慢SQL的原始场景与问题定位这条SQL的业务需求是，运营后台按指定的时间范围、订单状态、支付方式，统计每天的订单总数量和总金额，原始的SQL写法是这样的：

SELECT date(create_time) as dt, count(*) as total_cnt, sum(order_amount) as total_amt

FROM order_info

WHERE create_time between '2025-06-01' and '2025-06-30'

AND order_status in (1,2,3)

AND pay_type = 2

GROUP BY date(create_time);

当时订单表的数据量是1200万，开发同学只给create_time建了一个普通的单值索引，我们用Explain看执行计划的时候，发现type是range，key用了create_time索引，但是rows预估是320万，Extra里出现了Using index condition; Using filesort。一开始我们以为给条件加个联合索引就能解决，建了idx_time_status_pay(create_time, order_status, pay_type)之后，发现性能只从18秒降到了12秒，提升并不明显。后来我们仔细看执行计划才发现，问题出在group by后面的date(create_time)函数上，索引里的create_time是完整的datetime类型，优化器无法直接按date(create_time)做分组，只能先把320万行数据全部扫描出来，在内存里做filesort排序，再完成分组统计，大量的时间都浪费在了排序操作上。

2、分步骤优化，逐步压缩执行时间我们没有直接上来就改表结构，而是分了三步做优化，每一步都验证性能收益，确保不会影响线上业务：第一步，调整联合索引的字段顺序，把等值查询的字段放在前面，范围查询的字段放在后面。原来的索引是把create_time放在最左边，我们改成idx_pay_status_time(pay_type, order_status, create_time)，这样优化器可以先通过pay_type和order_status两个等值条件，快速过滤掉90%以上的数据，再用create_time做范围扫描。调整之后，预估的扫描行数从320万直接降到了45万，响应时间从12秒降到了5秒。第二步，把需要分组的统计字段也加到联合索引里，做成覆盖索引。我们把索引改成idx_pay_status_time_amt(pay_type, order_status, create_time, order_amount)，这样整个查询不需要回表，直接从索引里就能拿到所有需要的字段，去掉了所有随机IO的开销。优化之后，响应时间从5秒降到了800毫秒，性能又提升了6倍。第三步，解决group by的filesort问题。因为我们要按date(create_time)分组，但是索引里的create_time是完整的时间，优化器无法直接利用索引的有序性完成分组。我们在表里新增了一个冗余字段stat_date，类型是date，每次写入订单的时候自动把create_time的日期部分同步到这个字段里，然后把联合索引改成idx_pay_status_date_amt(pay_type, order_status, stat_date, order_amount)。这时候索引本身就是按stat_date有序排列的，优化器遍历索引的时候，不需要任何额外排序，直接按顺序累加就能得到每天的统计结果，Using filesort彻底消失了。优化完成之后，这条SQL的响应时间直接降到了30毫秒，比最开始的18秒性能提升了600倍，运营导出整个6月的统计报表，几乎是点完按钮立刻就能出结果，再也不会出现拖垮主库的情况。

3、最终的架构兜底方案虽然单条SQL已经优化到了30毫秒，但是我们考虑到后续订单表的数据量会突破5000万，直接在主库做统计查询，还是会有影响核心交易的风险。最后我们做了架构层面的兜底：把订单数据通过Canal同步到数仓里，每天凌晨预计算好所有运营需要的统计报表，运营后台直接查询预计算好的结果表，完全不碰主库。这样就算后续数据量涨到几亿，统计查询的响应时间也能稳定在10毫秒以内，从根本上解决了大数据量统计的性能问题。

五、SQL调优的长期工程思维，不要只做临时补救

很多团队对SQL优化的理解，就是出了慢查询故障之后紧急救火，从来不会提前做预防，结果每次大促之前都要熬夜排查几十条慢SQL，随时可能出线上故障。真正成熟的数据库工程体系，是把SQL优化的能力前置到开发流程里，从根源上避免慢SQL的产生。 1、上线前做SQL评审，把问题挡在测试环境我们团队现在的开发流程里，所有涉及到多表关联、group by、limit分页的SQL，上线之前都必须经过DBA或者资深开发的评审。用Explain看执行计划，确认type不会出现ALL，Extra里不会出现Using temporary和Using filesort，索引设计符合最左匹配原则，绝对不允许带着全表扫描的SQL直接上线。很多慢查询在数据量小的测试环境里根本暴露不出来，等到了生产环境数据量上来了才爆发，提前做SQL评审，能避免80%以上的后续性能问题。 2、慢查询日志常态化巡检，提前发现潜在风险我们把MySQL的慢查询日志阈值设置成了200毫秒，每天凌晨自动跑脚本，统计当天所有执行时间超过200毫秒的SQL，按执行次数和总耗时排序，把Top10的慢SQL发给对应的业务开发同学优化。很多SQL单次执行只有300毫秒，但是一天要执行几万次，累计下来要消耗几个小时的CPU时间，这种隐形的慢查询如果不提前处理，等到业务量翻倍的时候，瞬间就会把数据库打垮。 3、大表数据提前做冷热分离，不要让单表无限膨胀很多慢查询的根源，其实是单表数据量太大，超过了千万级之后，哪怕你索引设计得再完美，查询性能也会慢慢下降。我们现在的订单系统，超过一年的历史订单数据，会自动归档到冷库里，线上的热订单表只保留最近一年的数据，单表数据量永远控制在500万以内。这样所有的查询都能在热表里快速完成，不需要面对几千万甚至几亿的历史数据，从根本上降低了SQL优化的难度。

很多人总觉得SQL调优是一件特别难的事，需要掌握很深的数据库内核知识，其实你只要沉下心来，把Explain的每一个字段搞懂，把索引的底层结构理解清楚，多在生产环境里踩几次坑，你就会发现，99%的慢SQL，都逃不过“看执行计划、找扫描行数、优化索引、减少回表”这几个步骤。数据库工程从来不是靠堆硬件就能解决所有问题的领域，你写的每一条SQL，设计的每一个索引，最终都会变成系统性能的一部分。把这些基础的优化能力打磨扎实，你再也不用在凌晨三点的线上故障里，对着满屏的慢查询日志手足无措。