1. 从一条慢查询说起索引为什么是SQL优化的第一站干数据库调优这些年我见过太多团队在慢查询面前手忙脚乱。有人上来就改SQL写法有人先加缓存还有人直接把数据库配置翻了个底朝天最后发现收效甚微。其实绝大多数SQL性能问题根源都在索引策略上或者说在用没用对索引上。先说一个我印象很深的案例。之前帮一个电商项目做性能体检线上有个订单查询接口高峰期平均响应时间到了1.8秒数据库CPU飙到80%以上。开发同学很困惑说明明已经加了索引为什么还是慢我让他把执行计划发过来一看发现那条SQL查的是order_no字段索引确实建了但建的是idx_status单列索引查询条件里order_no根本没走索引全表扫描了几百万行。这就是典型的“有索引但不匹配”的问题。所以我想用这篇博文把SQL优化这条路从头到尾捋一遍从索引的底层原理到实际场景中的索引设计再到慢SQL的排查套路最后聊聊那些容易被忽略的参数调优和常见坑。不管你是刚接触数据库的初级开发还是已经被线上慢查询折磨过几轮的团队负责人这篇文章都能给你一套可以直接抄作业的优化思路。注意我不是在讲教科书里的理论而是在讲那些我在生产环境里踩过坑之后总结出来的实战经验。2. 索引策略的核心拆解B树、回表与最左前缀法则2.1 索引到底在加速什么先理解B树的读路径很多开发对索引的理解停留在“索引就是目录”这个层面这没错但不够。要真正做好索引优化你得知道索引在数据库里是怎么组织的否则就会犯那种“加了索引但没生效”的低级错误。以MySQL的InnoDB引擎为例主键索引也就是聚簇索引的叶子节点直接存储整行数据而二级索引非聚簇索引的叶子节点存储的是索引列的值加上主键值。当你通过二级索引查询时如果SELECT需要的字段不在二级索引里就得拿着主键去主键索引里再查一次这个过程叫回表。有一次我优化一条报表查询SQL长这样SELECT user_id, order_amount FROM orders WHERE order_status PAID AND create_time 2024-01-01;表上原先有两个独立索引idx_status(order_status)和idx_create_time(create_time)。结果优化器只能选其中一个选哪个都免不了回表查询耗时稳定在700ms左右。后来我把索引改成联合索引ALTER TABLE orders ADD INDEX idx_status_time_amount (order_status, create_time, user_id, order_amount);这个索引直接覆盖了WHERE条件里的两个字段和SELECT需要的两个字段查询走索引就能拿到全部数据不需要回表耗时直接降到80ms。这就是覆盖索引的威力。B树相比B树最大的特点是叶子节点之间通过链表串联非常适合范围查询和排序操作。你写WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31时数据库定位到起始叶子节点后顺着链表往后扫就行不需要反复回溯父节点。理解这个读路径之后你在设计索引时就会有意识地问自己这条SQL要走几个索引要不要回表排序能不能在索引上直接完成2.2 联合索引与最左前缀法则为什么顺序那么重要联合索引可能是面试里最常被问到的也是生产环境里最容易用错的。它的核心规则就是最左前缀法则查询条件里必须包含联合索引的最左侧列索引才会被用到而且只有从最左列开始连续匹配索引的后续列才能发挥排序和过滤的作用。举个例子索引如果建成了(a, b, c)以下查询能走索引WHERE a 1 WHERE a 1 AND b 2 WHERE a 1 AND b 2 AND c 3而这两条查询就废了WHERE b 2 WHERE c 3这里有读者可能有个误区WHERE a 1 AND c 3能用索引吗能用但只用了a这一列c列因为在b之后被跳过了索引在这里只能起到部分过滤作用。所以建联合索引的关键原则是把等值查询的列放前面把范围查询的列放后面。因为范围查询一旦命中后面的索引列就没法继续参与过滤了。比如WHERE a 1 AND b 10 AND c 3如果索引顺序是(a, b, c)那c就用不上索引如果调整成(a, c, b)那a和c都能走索引b负责范围过滤效果完全不一样。我在实际项目中常用的一个设计套路是拿到一组高频查询SQL后先把每个SQL的等值条件和范围条件分列出来然后合并成索引候选。等值条件合并后加范围条件再根据查询频率和SELECT字段决定要不要把常用查询列加进索引尾部做成覆盖索引。这一步做完大部分慢查询的问题就解决了七八成。2.3 主键索引、二级索引和唯一索引的选择逻辑主键索引和唯一索引的区别很多人能背概念但真到建表时还是容易纠结。这里我说说我的判断标准。主键索引是InnoDB表的数据组织方式每个表必须有且只有一个。主键的选择直接影响表的存储布局所以优先选自增整数ID或者趋势递增的业务ID。千万别用随机UUID当主键这个坑我踩过——UUID随机性很强插入时B树会频繁发生页分裂导致页碎片严重写入性能暴跌表空间膨胀得厉害。之前有个物联网项目就是UUID主键写入TPS到不了2000换成雪花ID后直接翻了好几倍。唯一索引是用来保证业务逻辑上某个字段值唯一的比如身份证号、订单号。它和普通二级索引的查询性能差别不大多了一份唯一性校验的开销。如果你只是查询用不需要唯一约束就别建唯一索引避免每次插入都多一次检查。还有个很多人忽略的点二级索引要控制数量。每张表建议控制在5个以内因为每次INSERT、UPDATE时所有二级索引都要同步更新。索引不是越多越好它是拿写入性能换查询性能的。我在优化一张千万级用户表时发现原表有12个索引其中好几个索引的区分度不到10%直接砍掉8个写入性能提升约35%查询性能一分没降。3. 索引失效的场景排查与SQL语句改造实践3.1 那些让索引“突然失效”的写法索引失效是SQL优化里最气人的问题——明明索引建了执行计划就是不按要求走。我总结了一下以下几类写法几乎是必踩的坑对索引列使用函数或运算WHERE YEAR(create_time) 2024这种写法会导致索引失效。正确做法是改成范围条件WHERE create_time 2024-01-01 AND create_time 2025-01-01。隐式类型转换字段是varchar类型查询条件是数字比如WHERE phone 13800138000。MySQL会隐式地把字段转成数字再比较索引就废了。排查方法很简单在查询里加EXPLAIN看type字段如果明明有索引却显示ALL或者index大概率就是类型的问题。LIKE前置通配符WHERE name LIKE %张这属于无法利用B树有序性的典型场景。实际业务里“以某关键词开头”的模糊查询LIKE 张%是可以走索引的但前置通配符就别想了。OR连接非索引列WHERE id 1 OR name 张三即使id是主键优化器也可能选择全表扫描。解决方法是拆成UNION ALL或者确保OR两侧的列都有可用索引。索引列参与计算WHERE price * 0.9 100这类写法同样会让索引失效改写为WHERE price 100 / 0.9就能走索引。这些坑的共同特点是对索引列做了“不可逆的变形”导致B树没办法按原始顺序去二分查找。我之前有个客户一条SQL从200ms优化到20ms什么额外操作都没做就是把WHERE DATE(create_time) CURDATE()改成了WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY。3.2 用EXPLAIN读懂执行计划通往优化的一把钥匙不会看执行计划SQL优化就永远是盲人摸象。我建议把这个技能练成本能反应。拿MySQL举例EXPLAIN SELECT user_id, order_amount FROM orders WHERE order_status PAID AND create_time 2024-01-01\G重点关注几个字段type效率排序从上到下依次是systemconsteq_refrefrangeindexALL。看到ALL基本就是全表扫描必须处理。ref和range是常见的高效访问方式index是扫描了整棵索引树比ALL好一点但也不理想。key实际使用的索引名。没显示的话Using index condition表示索引条件下推Using where表示有过滤但没有完全用上索引。rows预估扫描行数。这个数字和实际行数差得多的时候说明统计信息不够新可以跑一下ANALYZE TABLE。Extra出现Using filesort说明排序没走索引这在大数据量下非常致命出现Using temporary说明用了临时表常见于GROUP BY没走索引的场景。有一次排查线上慢SQLEXPLAIN显示type ALLrows 800万但代码里明明建了联合索引。后来仔细看才发现查询条件里把user_idvarchar类型和状态码拼接在了一起导致隐式类型转换。这种问题通过EXPLAIN一眼就能定位不用瞎猜。3.3 大分页为什么慢延迟关联的实战效果分页查询慢是业务系统里的经典问题。LIMIT 200000, 20这种写法在千万级表上可以直接把数据库拖垮。原因是MySQL会先把前20万条数据全部查出来再丢弃前19万9980条只返回最后20条。我常用的优化手段有两个延迟关联延迟join先用覆盖索引快速定位主键再回表取数据。SELECT u.id, u.name, u.email FROM users u INNER JOIN ( SELECT id FROM users WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20 ) tmp ON u.id tmp.id;内层SQL只查了主键和排序字段并且用上了覆盖索引扫描代价大幅降低。外层再通过主键精确取回需要的数据行。这个方案实测在500万行表上LIMIT 200000,20的查询时间从2.3秒降到不到300ms。记录上一页的最大ID如果业务允许用WHERE id last_max_id ORDER BY id LIMIT 20这种基于游标的分页方式性能最优。但要注意这只适合排序字段是主键或唯一递增列的场景如果排序字段频繁变动游标方案就会很麻烦。4. 慢SQL定位与参数优化实战4.1 从慢查询日志里找到“真凶”优化SQL之前先得知道哪些SQL是慢的。MySQL的慢查询日志是最直接的入口。slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes ON这几个参数配置好之后所有执行时间超过1秒且没走索引的SQL都会被记录到日志里。我建议线上环境把long_query_time先设成2秒跑一周看看最慢的那批SQL长什么样然后再逐步收紧。拿到慢日志后怎么分析我习惯用mysqldumpslow工具做个汇总mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log-s at表示按平均查询时间排序-t 10是只取前10条。这样能快速聚焦到那些“单次不算最慢但频繁出现”的SQL这类SQL往往才是拖垮数据库的元凶。比如有个订单统计接口单次执行300ms并不算慢但每分钟被调用上千次累积起来占的数据库时间远高于那些偶尔执行几秒的大报表查询。4.2 参数调优不是每个参数都要动很多DBA一上来就调innodb_buffer_pool_size把它设为内存的70%就完事。这方向没错但要注意参数调优是建立在SQL和索引已经合理的前提上的。SQL本身写得烂参数再怎么调也只是在大坑上面盖层薄纱。我接手过一个SQL Server项目服务器32G内存业务方说数据库很慢。查了一圈发现max server memory被设成了默认的2GBSQL Server根本没用上机器的大部分内存页生命周期极短大量数据需要反复从磁盘读。把max server memory调到24GB后预留系统内存整体性能直接上了一个台阶。在MySQL这里我最常调整的几个参数是innodb_buffer_pool_sizeInnoDB的缓存池建议设为可用内存的60%~70%。这个值太小热点数据会频繁被换出磁盘IO会成为瓶颈。innodb_flush_log_at_trx_commit默认1是最安全的每次事务提交都刷盘。如果业务能接受最多丢失1秒的事务数据改成2可以显著提升写入性能。max_connections默认151在很多高并发场景下不够用但也不要盲目调大。每个连接都会占用内存连接数过多反而会拖垮数据库。还有一点容易被忽略扫描行数和返回行数的比例。我统计过很多团队优化慢SQL费了半天劲把SQL改得花里胡哨结果rows还是百万级这种优化没有意义。优化的本质是减少数据库需要处理的数据量而不是让单条语句看起来更“高级”。4.3 优化器不按你的索引走怎么办有时候索引建得很好条件也匹配但优化器就是选了全表扫描。这通常有两个原因一是统计信息不准二是优化器认为全表扫描成本更低。前者执行ANALYZE TABLE刷新统计信息就能解决后者就要靠FORCE INDEX或USE INDEX来干预。不过FORCE INDEX是双刃剑线上数据分布一旦变化强制索引可能导致更严重的性能问题。我的原则是能通过调整索引结构解决的绝不依赖FORCE INDEX。如果实在要强制指定一定要加注释说明原因和有效期方便后续维护。顺带一提有时候ORDER BY排序会触发Using filesort。这个“filesort”并不是说用了磁盘文件而是在MySQL层面做了一次额外的排序。如果排序字段不能直接利用索引顺序可以在排序字段上单独建索引或者把排序字段作为联合索引的最后一个成员让B树的有序性替你做排序。5. 千万级数据项目的SQL优化实战复盘5.1 场景还原一张不断膨胀的订单表讲一个完整案例吧。去年帮一个SaaS平台做数据库治理核心问题是订单表orders已经涨到1800万行而且还在以每天十几万的速度增长。团队反馈说运营后台的订单查询越来越慢最典型的一条SQL是SELECT order_id, user_name, order_amount, order_status, pay_time FROM orders WHERE user_name 张三 AND order_status PAID AND pay_time BETWEEN 2024-01-01 AND 2024-06-30 ORDER BY pay_time DESC LIMIT 20;这条SQL在测试环境只有几十万行数据时跑得飞快上了生产后就崩了。EXPLAIN一看type ALL全表扫描1800万行查询耗时3.5秒。5.2 优化三步走索引重建、SQL改写、统计信息维护第一步分析字段特征。user_name是等值条件order_status是等值条件pay_time是范围条件兼排序字段。按照等值在前、范围在后的原则建联合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_name, order_status, pay_time);这里有个细节user_name的区分度其实一般重名用户很多联合索引的第1列用它虽然没问题但过滤效率不如用user_id这种唯一性强的字段。于是我又查了业务逻辑发现运营后台搜索其实是用会员ID关联的user_name只是冗余展示字段。最终把索引改成了ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, order_status, pay_time);第二步改SQL。为了尽量把ORDER BY也压进索引我给pay_time设计了降序索引MySQL 8.0支持这样索引顺序和查询排序方向一致连filesort都省了。ALTER TABLE orders ADD INDEX idx_user_status_time_desc (user_id, order_status, pay_time DESC);改写后的查询SELECT order_id, user_name, order_amount, order_status, pay_time FROM orders USE INDEX (idx_user_status_time_desc) WHERE user_id 10086 AND order_status PAID AND pay_time BETWEEN 2024-01-01 AND 2024-06-30 ORDER BY pay_time DESC LIMIT 20;第三步跑ANALYZE TABLE orders刷新统计信息然后重新执行计划确认。这次type变成了rangerows预估从1800万降到了3.6万耗时从3.5秒降到了约120ms。5.3 后续风险排查为什么不能只靠索引索引优化做完之后我又多做了几件事防止数据量继续增长后问题复发分区表或数据归档订单表是明显的“热冷分离”数据近3个月的数据是热点历史数据基本只读。如果索引优化后性能依然捉襟见肘下一步就是按月分区或者把历史数据归档到独立的历史表。有几个项目就是靠这个方案彻底摆脱了大表慢查询。大事务拆解检查了一下后台的批量更新接口发现有一次性更新几万条记录的大事务。这不仅是写性能问题大事务的锁范围会波及热度极高的订单表导致线上小事务排队。我在书里经常强调事务要短平快批量操作拆成每批500条提交效果立竿见影。索引监控机制在慢查询日志之外我额外做了个定时任务每周统计一次新增的慢SQL和索引使用情况。重点看哪些索引长期没被EXPLAIN命中这种索引就该考虑删除减少写入负担。这一步是很多团队忽略的——索引建完就完事两年后表结构都换了旧索引还在那里白白拖慢写入。6. 常见优化问题与排错速查表6.1 面试和实战中最常踩的索引问题这里我整理一份速查表也是我在面试候选人时常问的点同时也是我自己复盘知识体系时的清单问题场景原因分析解决方案明明有索引却不走隐式类型转换、函数运算、OR条件改写SQL保持索引列“原型不变”联合索引用了但效果差范围查询列放得太靠前把等值条件列前置范围条件后置查询快但排序慢排序字段没进索引或方向不一致建联合索引时考虑ORDER BY方向分页越深越慢大LIMIT导致大量数据被扫后丢弃延迟关联或基于游标的分页写入越来越慢二级索引过多或主键随机精简索引用自增或递增ID统计信息过期优化器估算行数偏差严重定期ANALYZE TABLE批量更新性能差大事务锁竞争分批提交缩短事务时间MySQL和SQL Server写法混用方言差异引发全表扫描明确优化目标库按对应语法改写6.2 两个容易被忽略的场景细节关于二级索引更新时的锁问题这个场景比较进阶但也算高频问题。MySQL InnoDB中通过二级索引更新数据时会先锁二级索引项再回表锁主键记录。如果并发场景下两条更新语句分别持有不同二级索引的锁并且都在等待对方的主键锁就可能形成交叉等待。解决思路是尽量保证二级索引到主键的回表顺序一致或者把更新操作统一到同一条索引路径上。虽然大多数业务不会触发这个极端场景但在做高并发订单更新时要有这个意识。关于去重查询SELECT DISTINCT在大表上很慢因为它需要在临时表或排序中完成去重。如果只是统计某个字段的去重数量可以改成COUNT(DISTINCT field)如果字段区分度高且查询频繁优先建立前缀索引降低索引体积提升扫描效率。6.3 我的几条独家避坑心得第一优化前一定要记录基线数据。同样一条SQL改完对比优化前的耗时和rows扫描行数再判断是否真的优化了。没有基线优化就成了一场没有裁判的比赛。第二不要只看单条SQL的快慢。有时候一条SQL从500ms优化到100ms但它每天只跑两次这种优化价值有限。反而那些单次300ms但每分钟跑几百次的SQL才是真正值得投入的地方。做优化要按“总消耗时间”排序而不是按“单次耗时”排序。第三生产环境改动一定要留回滚路径。索引变更、参数调整最好都通过脚本执行并做好监控对比。我见过太多人所有优化做完一句“为什么线上比预发还慢”就把所有成果否定原因就是忽略了线上数据量更大、并发更高的事实。上生产前我习惯用压测工具模拟线上流量而不只依赖测试环境的小数据量验证。7. 优化完成之后别忘了持续观察SQL优化不是一次性工作而是一个持续迭代的过程。索引策略做完一轮慢SQL清完一批后面要做的是把监控机制建立起来让问题在下一次爆发前就被发现。我个人习惯的做法是每周花半小时做一次数据库健康巡检看慢查询日志里的TOP SQL、检查索引使用频率、确认表碎片率、评估数据增长趋势。很多问题在小时级别就能暴露但大多数团队都是在秒级故障之后才去补救。话说回来SQL优化的天花板其实不在于你掌握了多少技巧而在于你对手上这套数据模型的熟悉程度。每张表的字段语义、每个索引的适用场景、每条高频SQL的执行路径这些东西积累起来比任何“一招鲜”的技巧都更持久。最后再分享一个小技巧优化SQL的时候可以把目标SQL拆成最小单位去验证。比如把一条多表联查SQL先拆成单表查询看扫描行数再逐步加回关联条件和过滤条件每一步都看执行计划的rows变化。这样定位瓶颈非常快也避免了“改了一堆东西但不知道是哪一步起了作用”的尴尬。这个过程看着笨但实际操作中比任何工具都可靠。
