做后台开发这些年我几乎每个月都会接到一次“数据库又卡死了”的反馈。大多数时候排查下来罪魁祸首根本不是服务器性能而是一条慢SQL或者一个缺失的索引。数据库工程做得好不好SQL写得好不好数据量小的时候看不出来一旦表里躺着千万级的数据性能差距直接就是数量级的同样一条业务查询规范写法百毫秒级随性写法能拖到十几秒。这篇文章要聊的就是数据库工程与SQL调优以及它们怎么把一条查询速度提升10倍甚至更多。我拿一个很有代表性的案例来讲某电商系统的订单查询接口订单表数据量涨到1800万行之后一条关联了3张表的统计查询从最初的1.8秒一路恶化到12秒运营人员点一次筛选要等半天导出任务经常超时。后来我们做了一轮完整的优化最终把这条查询稳定在0.18秒左右前后差了几十倍。整个过程不涉及增加硬件也没有改业务逻辑靠的就是数据库工程和SQL调优这两件事。这篇文章没有空谈理论的部分全是可以直接落地的实操如何定位慢查询、如何分析执行计划、如何设计索引、如何重写SQL、如何在生产环境安全变更。适合后端工程师、DBA以及所有被慢查询困扰的团队参考。不管你手头数据库是MySQL还是别的这些分析思路都是通用的。1. 优化前先搞明白慢在哪、目标是多少1.1 场景复现一条查询怎么变慢的先说当时的业务背景。某电商系统的“订单管理后台”里运营人员需要按时间范围、订单状态、用户账号、商品名称等条件组合筛选订单页面要展示用户昵称、商品标题、订单总额、支付状态这些字段。早期订单量小的时候这条查询毫秒级返回没人觉得有问题。随着平台业务增长订单表数据量突破千万级问题就冒出来了。最典型的一条SQL大概是这样的SELECT o.order_id, o.order_no, o.order_amount, o.pay_status, u.user_name, p.product_title FROM orders o LEFT JOIN users u ON o.user_id u.id LEFT JOIN products p ON p.id o.product_id WHERE o.created_at 2024-01-01 AND o.created_at 2024-03-01 AND o.pay_status IN (1, 2) ORDER BY o.created_at DESC LIMIT 20;这条SQL乍看没什么毛病但线上实测就是跑不动。排查后发现orders表上没有任何针对created_at的索引JOIN的字段user_id被设计成了varchar类型而users表的id是bigint两边类型不一致JOIN时MySQL要隐式转换索引直接失效WHERE条件里pay_status用了IN优化器面对多值范围条件时没法像等值条件那样高效地利用索引排序。再加上ORDER BY排序临时表直接落盘。这就是典型的“表结构能用、SQL也能跑但放到大数据量下就原形毕露”的场景。我在排查时还发现这条查询每天要被调用上千次几乎占到了后台接口总耗时的六成。也就是说优化这一条SQL整个后台管理页面的平均响应时间都会有肉眼可见的提升。定位慢查询时一定不能只看单次耗时还得看调用频率。同样的耗时一天执行10次和一天执行1000次优先级完全不同。1.2 基线测量决定优化上限任何调优第一步都不是直接改SQL而是先把现状摸清楚。我在项目里做的第一件事是用EXPLAIN看执行计划同时记录几条代表性查询的真实耗时。这里有一个容易被忽略的点必须在生产环境的稳定流量下测基线不能拿开发库或加了缓存后的结果当标准。我当时记录的基线数据如下查询耗时12.3秒稳定值重复执行5次取中位数执行计划type字段ALL即全表扫描rows估算18420000需要扫描整张orders表Extra列Using temporary; Using filesort这三项一出来问题就定位了一半。全表扫描说明没有可用索引临时表加文件排序说明排序字段也没走索引。优化目标我从来不拍脑袋定一般遵循两个原则第一先以“消除全表扫描”为最低目标第二以“响应时间进入秒内最好百毫秒级”为最终目标。10倍提升听起来很猛但其实只要把全表扫描改成走索引这个数字非常容易达成。真正难的是后面从秒级到百毫秒级的收敛过程那才是数据库工程和SQL调优综合能力的体现。注意基线测量的结果必须可复现。建议在生产环境低峰期做每次执行前关闭查询缓存或者使用能给出真实执行时间的分析工具避免被缓存数据误导。我习惯用“连续执行5次取中位数”的方法因为MySQL的Buffer Pool预热会显著影响前几次查询的耗时单次测量得到的结果波动太大。2. 数据库工程表与索引的重建指南2.1 表结构设计里那些隐蔽的坑很多慢查询问题根本不在SQL本身而在于建表时埋下的坑。我见过太多开发者在设计表的时候图省事把字段类型、字符集、默认值等问题全部甩给“反正能跑”。等到数据量上来这些坑会一个不落地炸出来。以我们的案例为例orders表的user_id字段建表时定义成了varchar(32)而users表的id是bigint。两个表JOIN时MySQL必须把varchar类型的user_id转换成数值才能和bigint比较这一转换直接导致user_id上的索引失效优化器宁可去做全表扫描也不会用索引。这个坑非常隐蔽因为表不大或者数据量小的时候JOIN两边各扫几百行性能差异不明显一旦数据量上万级查询立刻崩盘。我见过不止一个线上事故最后查到原因就是“两个表的关联字段都是id但一个建成了varchar一个建成了bigint”看似一样实际索引全部作废。另外就是NULL字段的问题。NULL在索引中的处理方式和普通值不同允许NULL的列索引维护成本更高查询时还容易因为“IS NULL / IS NOT NULL”的判断导致优化器放弃索引。对于状态类字段我一般建议用默认值而不是允许NULL比如pay_status定义成tinyint NOT NULL DEFAULT 0这样既省空间查询条件也能正常走索引。还有一点是字段类型的选择。能用int绝不用bigint能用varchar(20)绝不用varchar(255)。字符类型长度过大InnoDB在建立二级索引时每条索引记录都要存足够长的前缀导致索引页能容纳的记录数变少相同数据量下索引树更深IO次数自然增加。这个细节对性能的影响是潜移默化的单条查询可能只差几百微秒但并发一高差距就很明显。2.2 索引为什么能提速B树与覆盖索引要理解SQL调优首先得理解索引为什么快。InnoDB的索引底层是B树它本质上是一棵多路平衡搜索树所有数据都存储在叶子节点并且叶子节点通过链表串起来。和二叉树相比B树的“胖”是它最大的特点一个16KB的页能放下很多索引项树的高度通常只有3-4层。这意味着你从一个几千万行的表里根据主键查一行数据磁盘IO次数基本就是树的层数通常是3次左右而不是像全表扫描那样把几千万行全读一遍。你可以把B树想象成一本书的目录主键索引是大纲目录二级索引是按关键词排列的检索目录。全表扫描相当于一页一页翻书索引查询则是先查目录定位到具体页码然后翻到那一页。数据量越大这两种方式的速度差距就越悬殊。很多人对“索引能提升10倍”的理解停留在“用索引查询更快”但实际上索引还有一个巨大的能力叫覆盖索引。覆盖索引的意思是查询所需的全部列都能在索引中找到不需要回表读取实际数据行。比如我们有这样一个查询SELECT order_no, pay_status, created_at FROM orders WHERE created_at 2024-01-01 AND created_at 2024-03-01;如果我们在orders表上建立一个联合索引(created_at, pay_status, order_no)那么这个查询的所有字段都能从索引里直接取出来根本不用回表。InnoDB的二级索引叶子节点存的是索引列加上主键值如果查询列都能在索引里找到InnoDB会直接返回省掉一次主键索引的随机IO。这个优化在数据量大的时候非常显著因为回表的随机IO是数据库里最贵的操作之一。2.3 联合索引的正确姿势联合索引是最容易被用坏的东西。很多人喜欢把表里所有可能被查询的字段都建上单列索引结果优化器面对一堆索引反而不知道该用哪个。更糟糕的是联合索引的顺序如果建反了查询条件即使覆盖了所有索引列也可能只能走部分索引。联合索引要遵循最左前缀原则。比如索引(a, b, c)它实际能支持的查询条件组合是aa和ba、b、c。如果你直接查询b 1这个索引是用不上的。所以联合索引的字段顺序必须按照“查询条件的频繁度和区分度”来排列。我通常的做法是第一步把等值查询条件的字段排在前面比如status、user_id这些高频等值条件第二步把范围条件的字段排在后面比如created_at第三步把用于排序的字段纳入索引考虑范围避免filesort。在订单查询这个场景为了配合“按时间范围 支付状态”这个高频筛选条件我们最终在orders表上建立的索引是ALTER TABLE orders ADD INDEX idx_created_pay (created_at, pay_status);这个索引让WHERE条件里的created_at范围扫描可以直接走索引。同时因为created_at在索引的最左边B树叶子节点本身就按created_at排序ORDER BY created_at DESC可以借助索引反向扫描完成不需要额外的文件排序。这里有一个经验必须强调选择性高的字段优先放前面比如user_id的选择性远高于pay_status。如果你在业务里经常按user_id created_at筛选那应该另建一个(user_id, created_at)的索引。一张表可以有多个联合索引服务不同查询模式但单表索引总量要克制一般控制在5个以内。因为每个索引都会拖慢写入速度并占用额外空间。索引的本质是空间换时间不加选择地乱建索引反而会让INSERT、UPDATE变慢。我见过最夸张的一张表线上建了17个索引其中还有几个是几乎完全重复的。那表的写入速度已经慢到每小时积压几万条数据后来删掉11个冗余索引写入时间直接降了一个数量级。索引不是收藏品建之前一定要想清楚到底服务哪些查询。3. SQL调优从执行计划到语句重写3.1 EXPLAIN执行计划怎么读SQL调优有一句老话不看执行计划的调优都是耍流氓。拿到一条慢SQL第一件事就是EXPLAIN通过结果里几个字段快速判断瓶颈在哪。EXPLAIN输出里最重要的字段有type访问类型从好到差依次是system const eq_ref ref range index ALL。看到ALL就说明是全表扫描ref或range是正常水平。rows优化器估算需要扫描的行数。行数越大越危险千万级别的rows基本等同于灾难。Extra额外信息常见的Using where、Using index、Using temporary、Using filesort。其中Using temporary临时表和Using filesort文件排序都是性能杀手。key实际使用的索引名如果为NULL说明这条SQL根本没用索引。拿我们那条订单查询来说优化前EXPLAIN的结果是字段值说明typeALL全表扫描灾难rows18420000估算扫描约1842万行ExtraUsing temporary; Using filesort使用了临时表和文件排序看到这个结果问题的优先级非常清楚先解决索引失效问题再优化排序最后检查是否需要拆查询。很多新手会把精力浪费在微调SQL语法上其实EXPLAIN已经告诉你该动哪儿了。我一般在每个阶段优化后都会重新EXPLAIN一次对比rows和Extra的变化。只有执行计划里的rows真正降下来才说明优化是有效的而不是靠运气碰对了。3.2 常见SQL反模式与重写技巧在SQL层面我总结过几个最常出现的反模式每一个都可能导致查询从毫秒级坠入秒级。第一个是对索引列使用函数。比如WHERE DATE(created_at) CURDATE()这种写法完全无法利用created_at上的索引因为优化器需要在每一行上先执行DATE()函数才能比较。正确的做法是改成范围条件WHERE created_at 2024-01-01 AND created_at 2024-01-02这看起来是小事但能直接让一个千万级表查询从全表扫描变成索引范围扫描。第二个是隐式类型转换。比如字段order_no是varchar类型但你用WHERE order_no 123456传了一个整数MySQL会尝试把varchar转成数值来比较索引立刻失效。解决办法是统一类型Java代码里传字符串SQL里写单引号。前面提到的user_id字段类型不一致本质也是隐式转换问题这种关联字段的类型不匹配尤其隐蔽因为EXPLAIN里不会直接提示只能靠人工比对字段定义。第三个是SELECT *。这个反模式不仅浪费IO和内存还会让覆盖索引失效。比如我们明明只需要order_no和created_at两个字段你偏要SELECT *那么优化器只能回表去取全行覆盖索引的所有努力全部白费。我在团队里有一个死规矩业务代码里除了极少数数据导出场景一律禁止SELECT *。第四个是JOIN的表过多或者关联字段类型不一致。JOIN本身不是坏东西但每个JOIN都意味着一次额外的查找开销数据量大时非常可观。很多场景下我们可以通过冗余字段、汇总表或者拆成多个子查询来减少JOIN次数。第五个是子查询的误用。老版本MySQL里IN (SELECT ...)有时候会退化成逐行执行相关子查询性能非常差。碰到这种情况我一般优先改写为JOIN-- 不推荐的写法 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status 1); -- 改写为JOIN SELECT o.* FROM orders o JOIN users u ON u.id o.user_id AND u.status 1;当然新版本优化器对子查询的处理能力变强了但在复杂业务里手工改写JOIN通常能获得更稳定的执行计划。3.3 深分页问题的工程解法分页慢是另一个高频痛点。典型的场景是管理后台点下一页、再点下一页到第100页、第1000页的时候查询速度骤降。先解释为什么会慢SQL通常写成LIMIT 10000, 20MySQL需要先扫描并排序前10020行然后丢弃前10000行只返回最后的20行。数据量越大OFFSET越大扫描丢弃的行就越多耗时自然越久。解决深分页有几种常用方案。第一种是延迟关联也就是先用覆盖索引查出目标主键再与原表做JOIN取完整行SELECT o.* FROM orders o JOIN ( SELECT order_id FROM orders WHERE created_at 2024-01-01 AND created_at 2024-03-01 ORDER BY created_at DESC LIMIT 10000, 20 ) t ON o.order_id t.order_id;这个方案里内层查询用的是索引字段走的是覆盖索引扫描的行虽然也是10020行但每一行都从索引页直接取不需要回表速度远快于原始SQL。然后再用20个主键回原表取完整记录回表只有20次。第二种是基于排序字段的游标分页。比如按created_at排序时把上一页最后一条记录的created_at传进来作为下一次查询的起点WHERE created_at 2024-02-15 10:23:45 ORDER BY created_at DESC LIMIT 20;这种方式避开了OFFSET导致的无效扫描是天然的索引范围查询数据量再大也不会退化。缺点是要求排序字段唯一或重复度很低否则需要附加一个唯一字段作为二级排序条件。4. 完整优化记录从12秒到0.18秒4.1 第一轮索引修正回到我们的订单查询案例。第一轮优化我只做了一件事按前面分析的结果修正表结构和索引。首先处理JOIN字段类型不一致的问题。我把orders表的user_id从varchar(32)改成bigint并和users表统一使用bigint类型。数据迁移部分我写了一个临时脚本先把旧数据里无法转换成数字的脏数据清洗干净然后执行ALTER TABLE修改字段类型再重建外键和索引。这一步在低峰期执行同时用在线DDL工具控制锁表时间避免阻塞线上写入。然后在orders表上新增联合索引ALTER TABLE orders ADD INDEX idx_created_pay (created_at, pay_status);加索引的过程并不复杂但有两个细节必须注意第一生产环境大表直接ALTER TABLE加索引会锁表长时间阻塞写入。我当时的做法是在备库先执行然后通过主从切换把流量切过来或者用在线DDL工具分批创建第二新增索引后不一定马上生效需要手动执行ANALYZE TABLE更新统计信息让优化器能基于准确的数据分布选择合适的索引。第一轮优化后的实测效果是查询从12.3秒降到2.4秒。虽然离目标还很远但全表扫描已经被消灭type字段从ALL变成了rangerows从1842万降到了42万。这一步验证了一个道理索引的收益通常是最直接的但并不是所有慢查询都能靠索引解决。2.4秒里剩下的时间基本都花在临时表和文件排序上。4.2 第二轮SQL改写索引修好之后2.4秒里剩下的消耗主要来自临时表和文件排序。我们用EXPLAIN继续看发现Extra列依然有Using temporary; Using filesort。原因是pay_status用了IN (1, 2)这个多值范围条件会破坏索引的有序性。优化器在IN条件下只能把每个值当成一个独立范围去扫描合并结果时无法保证created_at的全局有序性。排序还是得用临时表。这轮的解法是拆分条件。既然pay_status只有几个固定值我可以把它拆成多个等值查询再用UNION ALL合并结果。示意如下SELECT * FROM ( SELECT order_id, order_amount, pay_status, created_at FROM orders WHERE created_at 2024-01-01 AND created_at 2024-03-01 AND pay_status 1 ORDER BY created_at DESC LIMIT 1000 ) t1 UNION ALL SELECT * FROM ( SELECT order_id, order_amount, pay_status, created_at FROM orders WHERE created_at 2024-01-01 AND created_at 2024-03-01 AND pay_status 2 ORDER BY created_at DESC LIMIT 1000 ) t2 ORDER BY created_at DESC LIMIT 20;这样每个分支都是pay_status的等值条件优化器可以在每个分支里直接利用索引的有序性order by created_at不再需要filesort。这里有个细节很多人会忽略内层LIMIT不能只取20因为两个分支各取前20再合并可能最终结果的前20条都来自其中一个分支另一个分支的数据被直接丢掉排序结果就不对了。所以内层要取一个足够大的量比如1000外层再做最终排序取20。实测效果是2.4秒降到0.9秒。这里有个反直觉的点SQL变长了查询反而更快了。很多人不敢拆SQL担心“多次查询”比“单次查询”慢。但在这个场景下拆成多个等值分支走的是完全不同的执行路径减少的是临时表和文件排序这种重量级操作收益远大于多执行几次的开销。4.3 第三轮覆盖索引与延迟关联0.9秒已经跨过了秒级门槛但在我看来还不够。业务方要求的是百毫秒级响应后台操作人员每点一次筛选都要等近一秒体验还是很糟糕。第三轮优化的重点是把查询改成纯覆盖索引。我们最终采用了延迟关联方案先查目标主键再回表取关联字段SELECT o.order_id, o.order_no, o.order_amount, o.pay_status, u.user_name, p.product_title FROM ( SELECT order_id FROM orders WHERE created_at 2024-01-01 AND created_at 2024-03-01 AND pay_status IN (1, 2) ORDER BY created_at DESC LIMIT 20 ) t JOIN orders o ON o.order_id t.order_id LEFT JOIN users u ON u.id o.user_id LEFT JOIN products p ON p.id o.product_id ORDER BY o.created_at DESC;内层查询只选order_id门槛条件都在索引(created_at, pay_status)上可以直接从索引获取完全不用访问数据行外层再按20个主键回表关联用户表和商品表。整个查询的回表次数从之前的几十万次下降到了20次。这轮优化后的实测结果是0.18秒从12.3秒到0.18秒提升约68倍。10倍的目标早早就完成了。这里有一个额外的工程手段值得提对于这种后台运营查询我还会在业务层加一层短时缓存把热门筛选条件的结果缓存60秒。毕竟运营人员反复点相同条件的概率很高缓存命中之后接口直接返回连0.18秒都不需要。缓存不是SQL调优的主角但如果调优已经到了极限适当的缓存策略能让体验再上一个台阶。5. 常见问题与避坑实录5.1 优化不生效的几种情况做SQL调优最让人崩溃的不是优化完没效果而是明明按照理论改了实测下来反而更慢。我在实际项目中遇到过几种典型的“优化不生效”场景。第一种是统计信息过期。优化器选择索引主要依赖表的统计信息如果长时间不执行ANALYZE TABLE统计值可能严重偏离实际行数优化器就会做出错误判断比如明明有索引却选择全表扫描。遇到这种情况手动ANALYZE TABLE往往立竿见影。第二种是环境参数拖后腿。我指的是innodb_buffer_pool_size、sort_buffer_size这些参数。如果缓冲池设置得太小任何优化都可能在排序或JOIN时卡在磁盘IO上。我见过有业务库buffer pool只有128MB表数据却有30GB所有查询都会频繁刷新缓冲哪怕SQL写得再好也快不起来。这种情况下先调参可能比改SQL更紧急。第三种是优化器没选到最优执行计划。MySQL的优化器并不总是“聪明”的特别是在OR条件、大量IN参数、复杂LIKE的情况下。碰到这种情况可以用FORCE INDEX强制指定索引或者拆SQL让执行计划变得简单可控。但要记住FORCE INDEX是最后的武器因为一旦数据分布变化强制索引可能不再合适。第四种是缓存造成的假象。优化前的“慢”和优化后的“快”如果都是带缓存测试的结果就完全没有可比性。正确的做法是关闭查询缓存或者用能显示真实执行时间的分析命令看清楚每一阶段的耗时。这一点我在前面已经强调过但真的是最常见的坑。5.2 一份能覆盖80%场景的检查清单我整理了在实际调优中经常用到的一份检查清单贴在下面。每次接到慢SQL排查任务我都会直接照着过一遍检查项操作方式典型陷阱是否全表扫描EXPLAIN看type是否为ALL建了索引但统计信息过期JOIN字段是否一致对比两表字段类型、字符集、排序规则类型一致但字符集不一致索引列是否被函数包裹查看WHERE条件的写法DATE()、UPPER()、IFNULL()都会导致索引失效是否隐式转换对比字段类型与传入参数类型varchar字段传int或关联字段类型不一致排序是否走索引看Extra列是否有filesortORDER BY顺序与索引顺序不一致是否回表太多结合查询列看索引是否覆盖SELECT *会破坏覆盖索引分页OFFSET是否过大查看LIMIT起始位置深分页延迟关联可解决WHERE范围是否过大估算rows值跨越多天的大范围条件数据量爆炸这份清单不是万能的但它能覆盖我遇到的80%的慢查询场景。真正复杂的问题比如多表关联SQL的性能瓶颈最终往往要落回到业务逻辑层面去拆解不能只依赖工具。6.1 给新手的第一个建议先把基线打准我见过太多工程师一上来就急着改SQL改了半天没啥效果回头一看基线数据都没记录根本说不清楚到底优化了多少。数据库调优这件事一旦脱离数据就会变成无休止的“猜谜游戏”。所以我的建议很简单拿到慢SQL先花十分钟测基线、保存EXPLAIN结果再动手。改完之后用同一套方法复测对比type、rows、Extra和真实耗时。这四个字段的变化比任何“我觉得快多了”都更有说服力。另外我强烈建议在团队里建立一份慢查询日志的例行巡检机制。数据库自带的慢查询日志开关一定打开阈值可以先设成1秒每周挑出Top 10的慢SQL逐一分析。这个习惯坚持半年能规避掉绝大多数线上性能事故。很多问题在最开始出现时只是一条几百毫秒的SQL放任不管等数据量翻倍之后就会变成几十秒的灾难。6.2 我的日常调优习惯从数据分布出发最后再分享一个我个人的工作习惯在动手改SQL之前永远先搞清楚数据分布和业务形态。表里总共有多少行筛选字段的选择性高不高是不是热点表查询频率高不高——这些信息决定了你该用索引、缓存还是该拆表。比如一个字段只有0和1两个值选择性极低给它建单独索引几乎没用但如果这个低选择性的字段能帮助去掉90%的数据那它在联合索引里依然有价值。踩过几次坑之后我现在做优化的顺序基本固定先看业务需求和数据分布再测基线然后依次处理表结构、索引、SQL写法、参数配置最后用缓存兜底。每一步都用数据验证效果不靠感觉。这套方法不敢说能解决所有数据库问题但至少让我经手的慢查询项目绝大多数都能达到“10倍提速”这个量级的目标。
