1. 先把性能账算清楚慢查询到底在烧掉什么最难调的 SQL往往不是那种一眼就能看出写错了的而是曾经飞快数据涨了半年之后突然变慢的查询。我接手某业务系统时遇到过这样一张订单表不到两千万行数据一个简单的分页查询已经跑到了 2 秒以上接口整体超时率开始走高。真正去排查的时候发现SQL 的写法没有任何变化变化的只是数据规模。1.1 慢查询的真实成本不只是用户多等两秒很多人把 SQL 调优理解成让用户少等一会儿这个视角太窄了。一条慢查询真正消耗的是数据库服务器的 CPU 时间片、磁盘 I/O 带宽、内存缓冲区以及最重要的——行锁和表锁的持有时间。我先给你看一个小账本。假设一个接口正常耗时 50 毫秒单机可以支撑 200 QPS某个查询由于索引设计不合理实际耗时 800 毫秒这个接口瞬间就只剩下 12 QPS 的余量。更麻烦的是数据库连接池的连接是有限的慢查询把连接占住了后面的请求只能排队。连接池打满之后连那些本身很快的查询也进不来整个系统的表现不是变慢而是雪崩。所以在调优之前我建议你先建立一条判断标准什么样的查询必须处理不是所有慢查询都要优化而是那些高频执行、且单次扫描行数远大于返回行数的查询优先级最高。扫描行数和返回行数之间的差距就是数据库在做无用功的量级。1.2 数据库工程里的三个优化层次很多人一上来就改 SQL、加索引这属于头痛医头。站在数据库工程的角度性能问题通常分布在三个层次里调优时要一层一层往下看层次典型问题优化手段见效速度SQL 层查询写法不合理、索引失效改写 SQL、调整 JOIN 顺序最快表结构层字段类型不合理、缺少合适索引加索引、改类型、拆分字段中等架构层单表数据量过大、读写互相干扰分库分表、读写分离、冷热分离慢但彻底我见过不少团队在第一层反复折腾第二层却一直没人动。实际上很多时候把一张表的索引重新设计一遍效果比改写十条 SQL 都好。原因在于SQL 写法只是如何提问表结构和索引才是如何组织答案。2. 执行计划就是 SQL 调优的罗盘从 EXPLAIN 开始建立直觉调优这件事第一原则是不要猜。你的直觉在数据分布面前往往不靠谱真正靠谱的是执行计划。执行计划就是数据库告诉你我打算怎么执行这条 SQL的全过程。2.1 EXPLAIN 输出里的几个关键信号以常见的关系型数据库为例执行计划里最需要关注的几个字段我整理成了一张速查表字段含义关注点type访问类型从 const → ref → range → index → ALL越往右越差key实际使用的索引如果为 NULL大概率没走索引rows预估扫描行数和返回行数差距越大浪费越严重Extra附加信息Using filesort、Using temporary 都是危险信号这里最容易忽略的是type字段。const表示最多只有一行匹配ref表示走了非唯一索引range表示索引范围扫描index表示全索引扫描ALL表示全表扫描。很多人只看有没有走到索引也就是key字段有没有值但type才是判断访问方式是否高效的核心。举个例子一个查询走了索引但type显示index说明数据库扫了整棵索引树。如果索引树比数据表还大这个走索引其实比全表扫描更慢。所以执行计划要几个字段一起看别只看单个字段。2.2 通过一个例子理解执行计划的胡思乱想我之前处理过这样一条 SQLSELECT id, order_no, amount FROM t_order WHERE status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;表上原本有一个idx_create_time单列索引。直觉告诉很多人条件里用到了create_time应该会走索引。但执行计划显示rows接近十万Extra里有Using filesort。为什么因为WHERE里还有一个status 1它不在索引里。数据库用idx_create_time扫出满足时间条件的所有主键再逐个回表去查status字段最后还要把结果重新排序。这里的filesort并不是磁盘排序而是指没有直接利用索引顺序需要额外排序操作。这个例子的真正解法不是只看create_time而是让索引同时覆盖过滤和排序需求。于是我把索引改成组合索引(status, create_time)ALTER TABLE t_order ADD INDEX idx_status_time (status, create_time);改完之后执行计划里的type从index变成rangeExtra里也不再有Using filesort。因为status 1先在索引里把范围缩到很小create_time DESC又能直接利用索引的物理顺序排序这步就省了。调优的一个核心直觉就是执行计划里出现的每一处 额外操作都对应着一次数据搬运或比较的开销。你要做的就是让这些额外尽量消失。3. 索引设计是性价比最高的优化手段也是翻车率最高的环节索引这东西不是建得越多越好也不是建了就一定有用。我见过一张表上挂了十几个索引结果每次写入都要维护所有索引写性能被拖垮也见过明明加了索引却因为函数包裹或隐式转换导致索引完全失效。3.1 组合索引与左前缀规律的实战理解组合索引的底层原理是多个字段按顺序构成一棵 B 树左边字段的排序优先级更高。所以(a, b, c)索引能匹配a、ab、abc三种查询条件但b单独查询或bc查询时用不上这个索引的左边前缀。我在实际项目中经常遇到这样一个场景订单查询页有用户 下单时间和门店 下单时间两个高频筛选组合。很多人的第一反应是建两个组合索引(user_id, create_time)和(store_id, create_time)。没问题这确实能满足两套查询。但接下来有个细节很容易踩坑如果某天需求加了按订单状态筛选并且状态字段被加到查询条件最前面原来的组合索引就失效了。更常见的错误是为了省事只建了一个(user_id, order_type, create_time)索引想着以后左边两个字段的组合查询都能覆盖。可实际查询里order_type的选择性很差只有几个固定值把它放在中间会导致大量数据需要回表。组合索引里字段的先后顺序应该按照过滤能力从强到弱、查询频率从高到低来排而不是按 SQL 里出现的顺序来排。3.2 索引选择性的困境低区分度字段怎么处理一个字段的区分度太低比如性别、状态这类只有几个取值的字段单独建索引基本是浪费。原因不难理解走索引后仍然要回表查大量数据优化器估算后可能觉得不如直接全表扫描。但低区分度字段并非完全没救。它可以通过配合高频字段出现在组合索引的前缀或中间也可以利用覆盖索引来降低回表成本。所谓覆盖索引就是查询需要的所有字段都包含在索引树里不需要回表拿数据。比如SELECT order_no, amount FROM t_order WHERE status 1 AND create_time 2024-06-01;如果索引是(status, create_time, order_no, amount)那么查询的所有数据都能从索引树里拿到Extra里会显示Using index。这种情况下即使status区分度不高由于不需要回表整体开销依然可控。这里我特别想说一个反直觉的结论有时候一条 SQL 没走索引恰恰是优化器聪明的选择。比如数据量很小、全表扫描可能比走索引回表更快。这时不要硬逼它走索引真正该做的是结合数据量和访问频率重新设计索引结构。3.3 索引失效的几类高频触发场景根据我踩过的坑最常见的索引失效场景有这几类对索引列使用函数或表达式比如WHERE DATE(create_time) 2024-01-01索引失效。应该改成WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换索引列是字符串类型查询参数却传了数字数据库会先把列转成数字索引失效。LIKE 以通配符开头LIKE %abc%无法利用索引但LIKE abc%可以。OR 条件连接非索引列WHERE a 1 OR b 2中如果b没有索引a的索引也可能用不上。组合索引中间列断裂查询条件跳过中间字段只用了第一列和第三列第三列的可能无法利用索引。这些场景说起来简单真正难的是在代码审查时发现它们。所以我通常会在团队里约定一个规则凡是新 SQL 上线之前必须跑一次 EXPLAIN把执行计划截图放到代码评审里。这条规则看着繁琐但能拦下绝大多数低级性能问题。4. 三个来自一线的慢查询修复全过程前面讲了原理这一节我把三个不同场景的真实案例完整复盘一遍。这三个案例分别是深分页、OR 条件、排序字段问题基本覆盖了慢查询最常见的三种成因。4.1 案例一深分页查询从 2.3 秒降到 30 毫秒某业务后台的订单列表页用户翻到第 100 页之后请求就开始变慢最严重的时候一次查询要 2.3 秒。排查后发现 SQL 长这样SELECT id, order_no, user_id, amount, status FROM t_order WHERE create_time 2024-01-01 ORDER BY id DESC LIMIT 2000, 20;问题在于LIMIT 2000, 20。数据库要先把前 2020 行全部查出来然后扔掉前 2000 行只保留最后 20 行。随着页数越来越深扫描和丢弃的行数越来越大性能自然越来越差。经典的解法是使用延迟关联也叫子查询分批取主键SELECT t.id, t.order_no, t.user_id, t.amount, t.status FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE create_time 2024-01-01 ORDER BY id DESC LIMIT 2000, 20 ) tmp ON t.id tmp.id;先把深分页的扫描锁定在主键查询上然后用主键去关联取完整数据。这一改动同样的查询从 2.3 秒降到了 30 毫秒左右。但这个方案只适合一次性优化不能解决根本问题。真正抗深分页的做法是键集分页也就是记住上一页最后一条记录的 idSELECT id, order_no, user_id, amount, status FROM t_order WHERE create_time 2024-01-01 AND id 上一页最大id ORDER BY id DESC LIMIT 20;这种写法不再需要扫描和丢弃大量行每条记录只被处理一次页数再深也不怕。不过它要求排序字段必须是唯一且连续的通常用主键来承担。4.2 案例二OR 条件引发的索引合并问题另一个案例是用户端查询接口筛选条件允许按手机号查或按会员号查SQL 长这样SELECT id, user_name, phone, member_no, level FROM t_user WHERE phone 13800001111 OR member_no M20240001;phone和member_no各自都有单列索引但执行计划显示走了全表扫描rows接近全部记录数。原因是对于 OR 条件优化器需要分别评估两个分支如果两个分支的索引合并代价很高它宁愿全表扫一遍。这里有两个改造方向。第一个是把 OR 改写为 UNIONSELECT id, user_name, phone, member_no, level FROM t_user WHERE phone 13800001111 UNION SELECT id, user_name, phone, member_no, level FROM t_user WHERE member_no M20240001;这样每个分支各自走索引再把结果合并去重扫描量大幅降低。第二个方向是直接建组合索引(phone, member_no)让优化器走索引合并。不过组合索引对 OR 的使用场景有限制实际测试中许多版本的优化器仍然会选择全表扫描所以稳妥做法还是改写 SQL。这个案例给我的教训是在多条件或查询时不要相信两个单列索引能自动配合好。优化器不是人它的选择标准是成本估算而不是看起来个体面。4.3 案例三排序字段不在索引里引发的 Using filesort第三个案例来自一个报表查询SQL 是SELECT store_id, SUM(amount) AS total_amount FROM t_payment WHERE create_time 2024-03-01 GROUP BY store_id ORDER BY total_amount DESC;支付表的create_time上有索引所以WHERE过滤没问题但GROUP BY和ORDER BY都需要做额外的分组和排序。数据量一上来执行计划里就出现了Using temporary和Using filesort查询耗时直接翻倍。这里的核心问题是分组字段store_id和过滤字段create_time没有处在同一个索引结构里。我调整后的方案是建立一个跨两个字段的索引ALTER TABLE t_payment ADD INDEX idx_time_store (create_time, store_id);由于create_time在左过滤条件正好匹配左前缀store_id在右分组操作可以直接利用索引的有序性不再额外创建临时表。这一改Using temporary消失了查询时间从 4.8 秒降到了 0.4 秒左右。需要注意的是这里的分组 排序需求对字段顺序极其敏感。如果把索引建成(store_id, create_time)过滤条件反而无法利用左前缀等于白建。案例核心问题改动前耗时改动后耗时深分页LIMIT 深度越大扫描行越多2.3s0.03sOR 条件单列索引无法有效合并全表扫描级别每个分支走索引分组排序排序/分组字段不在索引中4.8s0.4s5. 工程层面的配套表结构、连接池与长尾问题SQL 调优不能只盯着单条语句。数据库工程的视角里表结构设计、连接池配置、数据增长趋势都会对查询性能产生决定性影响。很多慢查询是表结构和查询方式不匹配造成的靠改 SQL 只能治标。5.1 字段类型决定索引的体格字段类型直接影响索引的存储大小和比较速度。我见过一个典型的例子一个机构表的主键用了 36 位字符串 UUID索引 B 树又宽又深插入和查询都比自增整数慢几个量级。用一个粗略的对比来说主键类型索引大小相对值插入性能随机 IO 次数自增 BIGINT1 倍高少UUID 字符串约 4-6 倍低多业务短字符串中中中UUID 除了随机性导致页分裂频繁外还有字符串比较比整数比较更耗 CPU 的问题。所以只要业务上没有必须暴露主键的强需求优先用自增整数或雪花 ID 类数值型。字符集也容易被忽视。如果字段只需要存英文和数字没必要用 utf8mb4它的索引存储开销比 ascii 或 latin1 大不少。当然现在业务大多需要存中文utf8mb4 是常态但至少要意识到更宽的字符集 更宽的索引 更慢的扫描和排序。5.2 SELECT * 和隐性回表查询面没你想的那么小SELECT *是让索引失效的另一类隐形推手。很多开发觉得多查几个字段没关系反正数据都在表里但问题在于索引覆盖。举个例子如果表上有组合索引(create_time, status)而你只需要order_no和amount这个查询可能还要回表去拿这两个字段。但如果你明确只查这两列把索引设计成(create_time, order_no, amount)就能直接利用索引完成查询跳过回表。我并不是建议把所有需求字段都塞进索引——那样索引会越来越大写入成本飙升。我的意思是对于高频查询有意识地用覆盖索引去包住需要查询的列是性价比很高的一种优化。所以平时写 SQL 时尽量把字段列明确出来别一上来就SELECT *。5.3 连接池参数与慢查询的化学反应数据库侧的优化做完以后别忘了看连接池。连接池里有个常见的坑叫连接池撑爆一旦出现几条慢查询它们长时间占着连接不释放连接池耗尽其他正常请求全部排队。我处理过一个系统数据库负载并不高但接口超时率很高。排查发现连接池最大连接数 200某次定时任务一次性启动了 50 个并发查询每条查询要跑 5 秒也就是瞬间占掉 50 个连接、持续 5 秒。这个窗口期内其他业务请求根本拿不到连接。调优思路有两个一是缩短单次查询时间这是前面所有工作的意义二是设置合理的连接池参数比如maximum-pool-size不要盲目调大connection-timeout要设置足够短让拿不到连接的请求快速失败而不是无限排队。这里引入一个工程思维当系统资源有限时快速失败比无限等待更安全。快速失败能让调用方感知问题从而触发降级或重试策略无限等待只会把故障从数据库传导到整个业务链路。连接池参数建议原因maximum-pool-size根据数据库 max_connections 反推留 20% 余量防止连接打满数据库connection-timeout200ms~1s避免调用方长时间挂死idle-timeout与数据库 wait_timeout 对齐防止空闲连接被服务端切断6. 真正能复用的调优方法基线、压测和长期监控调优不是一次性的。今天优化了这条 SQL下周数据量涨了、有了新需求问题可能换个形式再出现。所以我最后想分享的是一套可以反复使用的调优验证方法。6.1 调优前后的三明治对比法我管这个方法叫三明治先建立基线再执行变更最后复测对比。基线不是随便跑一次就完事而是要在同样的环境、同样的数据规模下记录这条 SQL 的耗时、扫描行数、CPU 消耗、执行计划。变更之后再跑同样一组测试。两次结果之间如果差值不明显说明这次调优并没有真正解决瓶颈。具体操作上我习惯准备一份压测脚本模拟真实请求的并发量和数据分布。比如用如下方式验证分页查询优化# 模拟 50 个并发用户连续压测 300 秒 # 观察 p95、p99 延迟和错误率这里有一个很容易被忽略的点压测数据要尽量贴近线上分布。如果只在几十万行的小表上测很多索引失效问题根本看不出来把数据量涨到两千万行问题才会暴露。所以有条件的话最好先做一份线上的脱敏数据备份恢复到测试环境里再压测。6.2 长期监控哪些指标才能避免慢查询复发长期来看至少要监控四类数据监控项具体指标预警信号慢查询日志超过阈值如 1s的 SQL数量持续上升扫描行数单条 SQL 的 rows_examined远高于 rows_returned数据库整体负载CPU、磁盘 I/O、连接数高峰期接近 80%执行计划变化优化器选择的索引是否改变关键查询从 range 变成 ALL在这些指标里我最看重的是扫描行数和返回行数的比值。这个比值越高说明数据库做无用功越严重。它比满屏的监控图表更直接因为它直接对应到具体 SQL 的效率。另外慢查询日志别只开了不管。我建议配合一个简单的巡检脚本每周跑一次统计慢查询条数、最耗时的前 10 条 SQL、以及对应的执行计划变化。这样能让调优从救火变成预防。从我个人的实践经验看SQL 调优这件事80% 的收益都来自执行计划分析 索引设计 表结构审视这三个基本功。技巧固然重要但真正撑住系统性能的是把这些基本功变成日常开发习惯新 SQL 上线前看执行计划、数据涨到一定程度重新评估索引、定期的慢查询巡检。做到这些以后那些突然变慢的问题大多数都能提前被发现而不是等用户来反馈。 
长河君 · 2026/10/20 14:35:57 · 阅读 1.2k SQL调优实战：从执行计划到索引设计的性能优化指南
1. 先把性能账算清楚慢查询到底在烧掉什么最难调的 SQL往往不是那种一眼就能看出写错了的而是曾经飞快数据涨了半年之后突然变慢的查询。我接手某业务系统时遇到过这样一张订单表不到两千万行数据一个简单的分页查询已经跑到了 2 秒以上接口整体超时率开始走高。真正去排查的时候发现SQL 的写法没有任何变化变化的只是数据规模。1.1 慢查询的真实成本不只是用户多等两秒很多人把 SQL 调优理解成让用户少等一会儿这个视角太窄了。一条慢查询真正消耗的是数据库服务器的 CPU 时间片、磁盘 I/O 带宽、内存缓冲区以及最重要的——行锁和表锁的持有时间。我先给你看一个小账本。假设一个接口正常耗时 50 毫秒单机可以支撑 200 QPS某个查询由于索引设计不合理实际耗时 800 毫秒这个接口瞬间就只剩下 12 QPS 的余量。更麻烦的是数据库连接池的连接是有限的慢查询把连接占住了后面的请求只能排队。连接池打满之后连那些本身很快的查询也进不来整个系统的表现不是变慢而是雪崩。所以在调优之前我建议你先建立一条判断标准什么样的查询必须处理不是所有慢查询都要优化而是那些高频执行、且单次扫描行数远大于返回行数的查询优先级最高。扫描行数和返回行数之间的差距就是数据库在做无用功的量级。1.2 数据库工程里的三个优化层次很多人一上来就改 SQL、加索引这属于头痛医头。站在数据库工程的角度性能问题通常分布在三个层次里调优时要一层一层往下看层次典型问题优化手段见效速度SQL 层查询写法不合理、索引失效改写 SQL、调整 JOIN 顺序最快表结构层字段类型不合理、缺少合适索引加索引、改类型、拆分字段中等架构层单表数据量过大、读写互相干扰分库分表、读写分离、冷热分离慢但彻底我见过不少团队在第一层反复折腾第二层却一直没人动。实际上很多时候把一张表的索引重新设计一遍效果比改写十条 SQL 都好。原因在于SQL 写法只是如何提问表结构和索引才是如何组织答案。2. 执行计划就是 SQL 调优的罗盘从 EXPLAIN 开始建立直觉调优这件事第一原则是不要猜。你的直觉在数据分布面前往往不靠谱真正靠谱的是执行计划。执行计划就是数据库告诉你我打算怎么执行这条 SQL的全过程。2.1 EXPLAIN 输出里的几个关键信号以常见的关系型数据库为例执行计划里最需要关注的几个字段我整理成了一张速查表字段含义关注点type访问类型从 const → ref → range → index → ALL越往右越差key实际使用的索引如果为 NULL大概率没走索引rows预估扫描行数和返回行数差距越大浪费越严重Extra附加信息Using filesort、Using temporary 都是危险信号这里最容易忽略的是type字段。const表示最多只有一行匹配ref表示走了非唯一索引range表示索引范围扫描index表示全索引扫描ALL表示全表扫描。很多人只看有没有走到索引也就是key字段有没有值但type才是判断访问方式是否高效的核心。举个例子一个查询走了索引但type显示index说明数据库扫了整棵索引树。如果索引树比数据表还大这个走索引其实比全表扫描更慢。所以执行计划要几个字段一起看别只看单个字段。2.2 通过一个例子理解执行计划的胡思乱想我之前处理过这样一条 SQLSELECT id, order_no, amount FROM t_order WHERE status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;表上原本有一个idx_create_time单列索引。直觉告诉很多人条件里用到了create_time应该会走索引。但执行计划显示rows接近十万Extra里有Using filesort。为什么因为WHERE里还有一个status 1它不在索引里。数据库用idx_create_time扫出满足时间条件的所有主键再逐个回表去查status字段最后还要把结果重新排序。这里的filesort并不是磁盘排序而是指没有直接利用索引顺序需要额外排序操作。这个例子的真正解法不是只看create_time而是让索引同时覆盖过滤和排序需求。于是我把索引改成组合索引(status, create_time)ALTER TABLE t_order ADD INDEX idx_status_time (status, create_time);改完之后执行计划里的type从index变成rangeExtra里也不再有Using filesort。因为status 1先在索引里把范围缩到很小create_time DESC又能直接利用索引的物理顺序排序这步就省了。调优的一个核心直觉就是执行计划里出现的每一处 额外操作都对应着一次数据搬运或比较的开销。你要做的就是让这些额外尽量消失。3. 索引设计是性价比最高的优化手段也是翻车率最高的环节索引这东西不是建得越多越好也不是建了就一定有用。我见过一张表上挂了十几个索引结果每次写入都要维护所有索引写性能被拖垮也见过明明加了索引却因为函数包裹或隐式转换导致索引完全失效。3.1 组合索引与左前缀规律的实战理解组合索引的底层原理是多个字段按顺序构成一棵 B 树左边字段的排序优先级更高。所以(a, b, c)索引能匹配a、ab、abc三种查询条件但b单独查询或bc查询时用不上这个索引的左边前缀。我在实际项目中经常遇到这样一个场景订单查询页有用户 下单时间和门店 下单时间两个高频筛选组合。很多人的第一反应是建两个组合索引(user_id, create_time)和(store_id, create_time)。没问题这确实能满足两套查询。但接下来有个细节很容易踩坑如果某天需求加了按订单状态筛选并且状态字段被加到查询条件最前面原来的组合索引就失效了。更常见的错误是为了省事只建了一个(user_id, order_type, create_time)索引想着以后左边两个字段的组合查询都能覆盖。可实际查询里order_type的选择性很差只有几个固定值把它放在中间会导致大量数据需要回表。组合索引里字段的先后顺序应该按照过滤能力从强到弱、查询频率从高到低来排而不是按 SQL 里出现的顺序来排。3.2 索引选择性的困境低区分度字段怎么处理一个字段的区分度太低比如性别、状态这类只有几个取值的字段单独建索引基本是浪费。原因不难理解走索引后仍然要回表查大量数据优化器估算后可能觉得不如直接全表扫描。但低区分度字段并非完全没救。它可以通过配合高频字段出现在组合索引的前缀或中间也可以利用覆盖索引来降低回表成本。所谓覆盖索引就是查询需要的所有字段都包含在索引树里不需要回表拿数据。比如SELECT order_no, amount FROM t_order WHERE status 1 AND create_time 2024-06-01;如果索引是(status, create_time, order_no, amount)那么查询的所有数据都能从索引树里拿到Extra里会显示Using index。这种情况下即使status区分度不高由于不需要回表整体开销依然可控。这里我特别想说一个反直觉的结论有时候一条 SQL 没走索引恰恰是优化器聪明的选择。比如数据量很小、全表扫描可能比走索引回表更快。这时不要硬逼它走索引真正该做的是结合数据量和访问频率重新设计索引结构。3.3 索引失效的几类高频触发场景根据我踩过的坑最常见的索引失效场景有这几类对索引列使用函数或表达式比如WHERE DATE(create_time) 2024-01-01索引失效。应该改成WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换索引列是字符串类型查询参数却传了数字数据库会先把列转成数字索引失效。LIKE 以通配符开头LIKE %abc%无法利用索引但LIKE abc%可以。OR 条件连接非索引列WHERE a 1 OR b 2中如果b没有索引a的索引也可能用不上。组合索引中间列断裂查询条件跳过中间字段只用了第一列和第三列第三列的可能无法利用索引。这些场景说起来简单真正难的是在代码审查时发现它们。所以我通常会在团队里约定一个规则凡是新 SQL 上线之前必须跑一次 EXPLAIN把执行计划截图放到代码评审里。这条规则看着繁琐但能拦下绝大多数低级性能问题。4. 三个来自一线的慢查询修复全过程前面讲了原理这一节我把三个不同场景的真实案例完整复盘一遍。这三个案例分别是深分页、OR 条件、排序字段问题基本覆盖了慢查询最常见的三种成因。4.1 案例一深分页查询从 2.3 秒降到 30 毫秒某业务后台的订单列表页用户翻到第 100 页之后请求就开始变慢最严重的时候一次查询要 2.3 秒。排查后发现 SQL 长这样SELECT id, order_no, user_id, amount, status FROM t_order WHERE create_time 2024-01-01 ORDER BY id DESC LIMIT 2000, 20;问题在于LIMIT 2000, 20。数据库要先把前 2020 行全部查出来然后扔掉前 2000 行只保留最后 20 行。随着页数越来越深扫描和丢弃的行数越来越大性能自然越来越差。经典的解法是使用延迟关联也叫子查询分批取主键SELECT t.id, t.order_no, t.user_id, t.amount, t.status FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE create_time 2024-01-01 ORDER BY id DESC LIMIT 2000, 20 ) tmp ON t.id tmp.id;先把深分页的扫描锁定在主键查询上然后用主键去关联取完整数据。这一改动同样的查询从 2.3 秒降到了 30 毫秒左右。但这个方案只适合一次性优化不能解决根本问题。真正抗深分页的做法是键集分页也就是记住上一页最后一条记录的 idSELECT id, order_no, user_id, amount, status FROM t_order WHERE create_time 2024-01-01 AND id 上一页最大id ORDER BY id DESC LIMIT 20;这种写法不再需要扫描和丢弃大量行每条记录只被处理一次页数再深也不怕。不过它要求排序字段必须是唯一且连续的通常用主键来承担。4.2 案例二OR 条件引发的索引合并问题另一个案例是用户端查询接口筛选条件允许按手机号查或按会员号查SQL 长这样SELECT id, user_name, phone, member_no, level FROM t_user WHERE phone 13800001111 OR member_no M20240001;phone和member_no各自都有单列索引但执行计划显示走了全表扫描rows接近全部记录数。原因是对于 OR 条件优化器需要分别评估两个分支如果两个分支的索引合并代价很高它宁愿全表扫一遍。这里有两个改造方向。第一个是把 OR 改写为 UNIONSELECT id, user_name, phone, member_no, level FROM t_user WHERE phone 13800001111 UNION SELECT id, user_name, phone, member_no, level FROM t_user WHERE member_no M20240001;这样每个分支各自走索引再把结果合并去重扫描量大幅降低。第二个方向是直接建组合索引(phone, member_no)让优化器走索引合并。不过组合索引对 OR 的使用场景有限制实际测试中许多版本的优化器仍然会选择全表扫描所以稳妥做法还是改写 SQL。这个案例给我的教训是在多条件或查询时不要相信两个单列索引能自动配合好。优化器不是人它的选择标准是成本估算而不是看起来个体面。4.3 案例三排序字段不在索引里引发的 Using filesort第三个案例来自一个报表查询SQL 是SELECT store_id, SUM(amount) AS total_amount FROM t_payment WHERE create_time 2024-03-01 GROUP BY store_id ORDER BY total_amount DESC;支付表的create_time上有索引所以WHERE过滤没问题但GROUP BY和ORDER BY都需要做额外的分组和排序。数据量一上来执行计划里就出现了Using temporary和Using filesort查询耗时直接翻倍。这里的核心问题是分组字段store_id和过滤字段create_time没有处在同一个索引结构里。我调整后的方案是建立一个跨两个字段的索引ALTER TABLE t_payment ADD INDEX idx_time_store (create_time, store_id);由于create_time在左过滤条件正好匹配左前缀store_id在右分组操作可以直接利用索引的有序性不再额外创建临时表。这一改Using temporary消失了查询时间从 4.8 秒降到了 0.4 秒左右。需要注意的是这里的分组 排序需求对字段顺序极其敏感。如果把索引建成(store_id, create_time)过滤条件反而无法利用左前缀等于白建。案例核心问题改动前耗时改动后耗时深分页LIMIT 深度越大扫描行越多2.3s0.03sOR 条件单列索引无法有效合并全表扫描级别每个分支走索引分组排序排序/分组字段不在索引中4.8s0.4s5. 工程层面的配套表结构、连接池与长尾问题SQL 调优不能只盯着单条语句。数据库工程的视角里表结构设计、连接池配置、数据增长趋势都会对查询性能产生决定性影响。很多慢查询是表结构和查询方式不匹配造成的靠改 SQL 只能治标。5.1 字段类型决定索引的体格字段类型直接影响索引的存储大小和比较速度。我见过一个典型的例子一个机构表的主键用了 36 位字符串 UUID索引 B 树又宽又深插入和查询都比自增整数慢几个量级。用一个粗略的对比来说主键类型索引大小相对值插入性能随机 IO 次数自增 BIGINT1 倍高少UUID 字符串约 4-6 倍低多业务短字符串中中中UUID 除了随机性导致页分裂频繁外还有字符串比较比整数比较更耗 CPU 的问题。所以只要业务上没有必须暴露主键的强需求优先用自增整数或雪花 ID 类数值型。字符集也容易被忽视。如果字段只需要存英文和数字没必要用 utf8mb4它的索引存储开销比 ascii 或 latin1 大不少。当然现在业务大多需要存中文utf8mb4 是常态但至少要意识到更宽的字符集 更宽的索引 更慢的扫描和排序。5.2 SELECT * 和隐性回表查询面没你想的那么小SELECT *是让索引失效的另一类隐形推手。很多开发觉得多查几个字段没关系反正数据都在表里但问题在于索引覆盖。举个例子如果表上有组合索引(create_time, status)而你只需要order_no和amount这个查询可能还要回表去拿这两个字段。但如果你明确只查这两列把索引设计成(create_time, order_no, amount)就能直接利用索引完成查询跳过回表。我并不是建议把所有需求字段都塞进索引——那样索引会越来越大写入成本飙升。我的意思是对于高频查询有意识地用覆盖索引去包住需要查询的列是性价比很高的一种优化。所以平时写 SQL 时尽量把字段列明确出来别一上来就SELECT *。5.3 连接池参数与慢查询的化学反应数据库侧的优化做完以后别忘了看连接池。连接池里有个常见的坑叫连接池撑爆一旦出现几条慢查询它们长时间占着连接不释放连接池耗尽其他正常请求全部排队。我处理过一个系统数据库负载并不高但接口超时率很高。排查发现连接池最大连接数 200某次定时任务一次性启动了 50 个并发查询每条查询要跑 5 秒也就是瞬间占掉 50 个连接、持续 5 秒。这个窗口期内其他业务请求根本拿不到连接。调优思路有两个一是缩短单次查询时间这是前面所有工作的意义二是设置合理的连接池参数比如maximum-pool-size不要盲目调大connection-timeout要设置足够短让拿不到连接的请求快速失败而不是无限排队。这里引入一个工程思维当系统资源有限时快速失败比无限等待更安全。快速失败能让调用方感知问题从而触发降级或重试策略无限等待只会把故障从数据库传导到整个业务链路。连接池参数建议原因maximum-pool-size根据数据库 max_connections 反推留 20% 余量防止连接打满数据库connection-timeout200ms~1s避免调用方长时间挂死idle-timeout与数据库 wait_timeout 对齐防止空闲连接被服务端切断6. 真正能复用的调优方法基线、压测和长期监控调优不是一次性的。今天优化了这条 SQL下周数据量涨了、有了新需求问题可能换个形式再出现。所以我最后想分享的是一套可以反复使用的调优验证方法。6.1 调优前后的三明治对比法我管这个方法叫三明治先建立基线再执行变更最后复测对比。基线不是随便跑一次就完事而是要在同样的环境、同样的数据规模下记录这条 SQL 的耗时、扫描行数、CPU 消耗、执行计划。变更之后再跑同样一组测试。两次结果之间如果差值不明显说明这次调优并没有真正解决瓶颈。具体操作上我习惯准备一份压测脚本模拟真实请求的并发量和数据分布。比如用如下方式验证分页查询优化# 模拟 50 个并发用户连续压测 300 秒 # 观察 p95、p99 延迟和错误率这里有一个很容易被忽略的点压测数据要尽量贴近线上分布。如果只在几十万行的小表上测很多索引失效问题根本看不出来把数据量涨到两千万行问题才会暴露。所以有条件的话最好先做一份线上的脱敏数据备份恢复到测试环境里再压测。6.2 长期监控哪些指标才能避免慢查询复发长期来看至少要监控四类数据监控项具体指标预警信号慢查询日志超过阈值如 1s的 SQL数量持续上升扫描行数单条 SQL 的 rows_examined远高于 rows_returned数据库整体负载CPU、磁盘 I/O、连接数高峰期接近 80%执行计划变化优化器选择的索引是否改变关键查询从 range 变成 ALL在这些指标里我最看重的是扫描行数和返回行数的比值。这个比值越高说明数据库做无用功越严重。它比满屏的监控图表更直接因为它直接对应到具体 SQL 的效率。另外慢查询日志别只开了不管。我建议配合一个简单的巡检脚本每周跑一次统计慢查询条数、最耗时的前 10 条 SQL、以及对应的执行计划变化。这样能让调优从救火变成预防。从我个人的实践经验看SQL 调优这件事80% 的收益都来自执行计划分析 索引设计 表结构审视这三个基本功。技巧固然重要但真正撑住系统性能的是把这些基本功变成日常开发习惯新 SQL 上线前看执行计划、数据涨到一定程度重新评估索引、定期的慢查询巡检。做到这些以后那些突然变慢的问题大多数都能提前被发现而不是等用户来反馈。