做SQL优化这些年我最常见的场景不是新系统上线而是老系统跑着跑着突然慢了。明明表不大、数据量也没到千万级接口却从几十毫秒变成两秒三秒打开慢日志一看要么统计类SQL没走索引要么查询把整张表扫了一遍。所谓SQL优化实战本质就是围绕索引策略把查询性能提起来这是一套有套路、有章法、也能量化的活儿。这篇文章适合后端开发、业务架构师和正在准备数据库面试的同学我不讲存粹的理论只讲能直接落到项目里的方案。先说结论SQL优化不是靠背几条优化口诀就能搞定的它需要你先建立“慢查询证据链”再理解索引为什么快、什么时候失效最后动手重写SQL并验证效果。接下来我会按排查、原理、实战、踩坑、案例这条线完整走一遍。1. 先搞清楚SQL到底慢在哪1.1 慢查询日志排查的第一手证据很多人接到慢SQL报警第一反应是“把这条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 ONlong_query_time设为1秒意味着执行超过1秒的查询都会被记录。log_queries_not_using_indexes这个开关很有意思它会把那些即使执行很快、但没走索引的查询也记下来特别适合用来排查“潜在性能地雷”。线上环境不建议长期全量开启慢日志因为会产生大量IO和磁盘占用。我的习惯是平时关闭或者设置long_query_time为5秒排查问题期间临时调低到1秒分析完再改回去。拿到慢日志后先用mysqldumpslow做聚合分析看看排名前几的SQL长什么样mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log这里-at表示按平均执行时间排序-t 10表示显示前10条。重点看“Query_time”和“Rows_examined”如果Rows_examined远大于结果集行数基本可以断定存在全表扫描或扫描范围过大的问题。还有个小技巧show status like Handler_read%也能辅助判断索引使用情况但慢日志更直观它能告诉你具体是哪一条SQL、在什么时间点、扫描了多少行。1.2 一条EXPLAIN读懂执行计划type列到底该怎么看拿到慢SQL后第一步永远是执行EXPLAIN而不是急着改SQL。EXPLAIN会告诉你MySQL优化器打算怎么执行这条查询EXPLAIN SELECT id, user_id, status, amount FROM orders WHERE user_id 10234 ORDER BY create_time DESC LIMIT 20;执行计划里最核心的几列是type、key、rows和Extra我整理了一个速查表type含义性能评估system系统表通常只有一行极快const主键或唯一索引等值匹配极快eq_ref联表查询中被驱动表通过唯一索引匹配很快ref非唯一索引等值匹配快range索引范围扫描如 BETWEEN、、中等偏快index扫描整棵索引树中等ALL全表扫描危险在EXPLAIN结果里type列从好到差大致是const eq_ref ref range index ALL。看到ALL基本就要注意了说明这条查询没有利用索引。还有一个高频坑是possible_keys有值但key列是NULL。这说明虽然表上存在可用索引但优化器认为用不上、不想用。比如对索引列做了函数运算或者优化器觉得回表成本太高宁可全表扫描。rows列是优化器估算的扫描行数不是精确值但量级很有参考价值。Extra列最值得关注的是Using filesort和Using temporary出现这两个代表排序或去重需要额外的临时文件/临时表通常也是慢查询的元凶。1.3 为什么执行计划和你想的不一样有时候你明明给字段建了索引EXPLAIN出来还是ALL这可能不是索引没用而是优化器觉得用索引更亏。MySQL优化器会基于表的统计信息来估算成本包括行数、数据页数量、索引基数等。如果表数据量很小全表扫描可能只需要读几十个数据页而走索引反而要额外回表成本更高优化器自然会选择全表扫描。另一种情况是统计信息过期。频繁增删改之后表的行数或索引区分度变化很大但统计信息没更新优化器就会做出错误判断。这时候执行一下ANALYZE TABLEANALYZE TABLE orders;再跑EXPLAIN往往执行计划就正常了。养成习惯排查慢SQL前先确认统计信息是不是“新鲜”的这个细节能帮你避免很多无效优化。2. 索引策略从结构到选型的完整逻辑2.1 聚簇索引、二级索引和回表索引为什么能变快索引为什么能让查询变快根本在于把“顺序扫描”变成了“树查找”。InnoDB的索引用的是B树叶子节点之间双向链表连接既能快速定位又适合范围扫描。InnoDB表本身就是一棵以主键为索引的B树这叫聚簇索引。你可以把它想象成一本按拼音排序的字典正文直接就是按照主键排列的数据本身。而其他索引叫二级索引它的叶子节点存储的是主键值而不是完整数据行。这就是“回表”的由来你通过二级索引找到了主键值还要再回聚簇索引里把整行数据取出来。就好比按偏旁部首查字典先看目录找到页码再翻到正文那一页去看内容。回表次数越多性能损耗越大。所以你会理解为什么SELECT *有时候特别伤性能。如果查询列都包含在二级索引里MySQL连回表都省了这个叫覆盖索引后面细说。2.2 主键索引、唯一索引、普通索引怎么选主键索引不用多说每张InnoDB表必须有。问题在于主键怎么选。最推荐的是自增整数主键因为新数据插入时总是在B树的末尾追加写页顺序减少页分裂。如果业务使用UUID作为主键由于UUID无序插入时会不断在索引中间位置随机写导致频繁页分裂、空间碎片化性能会明显下降。唯一索引和普通索引的区别不只是“是否允许重复值”。唯一索引因为必须保证唯一性每次插入或更新都需要额外检查冲突写入成本略高但查询时一旦在二级索引命中一条记录就可以立刻停止扫描不需要继续找下一条可能重复的记录所以等值查询上唯一索引的终止条件更明确。业务场景里如果需要强约束业务键不重复比如订单号、身份证号用唯一索引如果只是用来加速查询普通索引更合适。还有一类坑表中存在大量“软删”数据逻辑删除标记在唯一索引列上当同一业务键多次插入且老记录被标记删除时唯一索引会冲突这时候要结合deleted字段设计联合唯一索引。2.3 覆盖索引与索引下推两条白嫖的优化手段覆盖索引是最常见的免费午餐。如果一个二级索引包含查询需要的所有列Extra列会显示Using index此时不需要回表。比如CREATE INDEX idx_user_status_create ON orders(user_id, status, create_time); SELECT user_id, status, create_time FROM orders WHERE user_id 10234 AND status 1 ORDER BY create_time DESC;这条查询可以直接在二级索引上完成因为要的字段全在索引里天然省掉回表。实际项目中覆盖索引能把I/O降低一个量级。索引下推是MySQL 5.6引入的优化Extra列显示Using index condition。它允许在索引遍历过程中先对索引包含的字段做条件过滤减少回表次数。举例联合索引(col1, col2)查询条件是col1范围加col2等值在旧版本里要先把所有符合col1范围的记录都回表再过滤col2有了索引下推会在索引层提前过滤col2回表量大幅减少。这个优化不需要你改任何SQL但前提是建好联合索引。2.4 联合索引设计最左前缀、区分度与字段顺序联合索引是SQL优化里最值得花心思的地方。它遵循最左前缀原则索引(a,b,c)可以匹配(a)、(a,b)、(a,b,c)三种组合但不能直接匹配单独(b)或单独(c)。设计字段顺序时通常参考两条经验区分度高的字段放前面高频等值条件的字段放前面。区分度可以这样算SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM orders; SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders;比如status字段只有几个固定值区分度极低单独建索引基本没有意义但放在联合索引靠后的位置可以作为过滤条件参与索引下推。还有热点问题MySQL通过二级索引更新时先锁二级索引项再回表锁主键记录这个时间窗口在旧版本中容易形成交叉死锁。MySQL 8.0对二级索引加锁逻辑做了改进但生产环境遇到死锁不要只盯着SQL先检查是否长事务、批量更新是否涉及多个二级索引。减少大事务、尽量走主键或覆盖索引更新能明显降低这类锁问题。2.5 索引失效场景速查表在这个环节我需要一条条列清楚面试也是高频考点失效场景原因分析正确姿势索引列使用函数或表达式索引存储的是原始值无法直接用于计算后的查询写法改成列值避免函数包裹隐式类型转换字符串列和数字比较触发隐式转换索引失效字段和参数类型保持一致前导模糊匹配 LIKE %abcB树无法从中间开始匹配改写为LIKE abc%或使用全文索引OR连接条件部分无索引优化器只能全部扫描拆成多个查询用UNION ALL或补全索引范围查询后的等值条件联合索引中范围字段后面的列不会走索引调整字段顺序把范围条件放最后NOT IN / NOT EXISTS优化器通常放弃索引根据数据分布改写为LEFT JOIN或 EXISTS有一次排查一个报表SQL发现开发用了WHERE DATE(create_time) 2024-05-20虽然create_time有索引但函数包裹让索引完全失效。改成 WHERE create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00 后查询时间从3秒降到80毫秒。这就是最典型的“非必要性写法”造成的性能浪费。3. 慢SQL重写实战从写法到结构3.1 深分页优化LIMIT 100000,20为什么越来越慢分页查询是慢SQL重灾区。比如SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;这条SQL慢在LIMIT的offset太大。数据库要把前100000行全部扫描并排序然后丢弃只返回最后20行。数据量越往后翻页扫描量越大性能指数级下降。两个常用解法延迟关联和游标分页。延迟关联的思路是先用覆盖索引找到分页范围内的主键再回原表取完整数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;子查询只扫二级索引不访问数据行能省掉大量回表开销。游标分页更适合APP或后台管理系统“上/下一翻页”场景SELECT * FROM orders WHERE status 1 AND create_time #{lastCreateTime} ORDER BY create_time DESC LIMIT 20;用上一页最后一条记录的create_time作为查询条件天然跳过offset。不过要保证排序字段唯一否则可能出现漏数据一般建议用(create_time, id)联合排序。3.2 SELECT * 到底害在哪SELECT *的问题不光是多传了几个字段那么简单。第一它会导致回表概率增加索引覆盖变得很难因为二级索引不可能覆盖全部列第二大字段比如TEXT、BLOB会额外增加IO和网络传输第三排序或分组时可能把不需要的列放进临时表。正确姿势只查询业务需要的列。比如订单列表只需要展示订单号、状态、金额、时间就写这些字段让二级索引尽量能覆盖降低回表次数。很多开发为了图省事统一写SELECT *等系统一上量慢查询日志里一大半都是这些语句。3.3 多表JOIN驱动表怎么选多表JOIN的性能关键在驱动表。MySQL会选择一个表作为驱动表用它的每一行去被驱动表匹配。一般原则是“小表驱动大表”让被驱动表的连接字段走索引。比如订单表和用户表关联通常订单表数据量远大于用户表用用户表驱动订单表更合适。但如果SQL写法或过滤条件导致优化器选错了驱动顺序可以使用STRAIGHT_JOIN强制指定SELECT u.name, o.order_no FROM users u STRAIGHT_JOIN orders o ON u.id o.user_id WHERE u.user_type 1;STRAIGHT_JOIN会强制左边users表作为驱动表。不过这个用法要谨慎因为强制指定后如果数据分布变了性能可能更差。另外JOIN连接字段一定记得建索引否则每匹配一行就要全表扫描一次被驱动表。还有一个经验避免三张以上大表直接JOIN。如果业务实在绕不开优先考虑预计算汇总表或者先用子查询缩小结果集再参与关联。3.4 DISTINCT去重的正确姿势SELECT DISTINCT常见但很多人不知道它内部怎么执行。DISTINCT本质上是把结果集排序或哈希后去重如果涉及多个字段且没有合适索引会触发Using temporary和Using filesort。先看有没有必要去重。很多场景是因为多表JOIN产生了笛卡尔积才需要DISTINCT这种情况优化JOIN条件或查询粒度的收益更大。如果确实需要去重可以改用GROUP BY有些版本里两者执行计划相同但GROUP BY后续扩展性更好。还可以借助索引让去重走有序扫描SELECT DISTINCT user_id FROM orders WHERE pay_status 1;这时如果存在(pay_status, user_id)联合索引全索引扫描时user_id已经是排好序的去重不需要额外排序EXPLAIN里看不到Using temporary。3.5 大数据量批量操作UPDATE/DELETE的锁与日志很多人优化查询很熟练一遇到大量UPDATE/DELETE就翻车。比如一次性执行DELETE FROM operation_log WHERE create_time 2023-01-01;如果涉及百万行这条语句会持有大量行锁还会让undo日志和redo日志迅猛膨胀导致整个数据库写性能雪崩甚至复制延迟拉满。正确做法是分批删除或更新每批1000到5000条批与批之间加一点sleepDELETE FROM operation_log WHERE id IN ( SELECT id FROM operation_log WHERE create_time 2023-01-01 LIMIT 2000 );执行完后COMMIT再等几十毫秒继续下一批直到删除完成。这个过程对用户无感也不容易产生锁等待。SQL Server和MySQL在处理日志上存在差异SQL Server的Write Log也是老生常谈的性能瓶颈大批量事务会撑满事务日志空间。经验是无论哪种数据库大批量写操作都尽量拆小长事务是性能和一致性的共同敌人。另外用MyBatis Plus这类ORM根据Java实体类生成建表SQL时要注意设置合适的字符集通常utf8mb4和合理的索引字段否则建出来的表连基础索引都没有后面所有查询都得跟着遭殃。4.3 锁等待造成的慢别甩锅给SQL我遇到过不少“慢SQL”SQL本身很简单索引也走了但执行计划里的时间还是很高。最后发现问题根本不是SQL而是行锁等待。比如一张订单表只有几万行某条UPDATE把一批订单锁住不提交后面所有相关查询全部卡住。排查方法SELECT * FROM information_schema.innodb_trx\G看看是否有长时间未提交的事务trx_started字段能看出事务开启时间。再用SHOW ENGINE INNODB STATUS\G查看LATEST DETECTED DEADLOCK和当前锁等待信息。如果确认是长事务先让对应应用把事务提交或回滚再考虑从代码层面优化事务边界。开发同学容易忽略的一点事务不是越短越好但也不是把所有数据库操作都放进去还要跑一堆外部接口。事务范围里尽量只包含必要的数据库写操作不要在事务里做远程调用、大循环。4.4 参数优化Buffer Pool和其他该调的参数有些慢查询靠改SQL不一定能解决还得看MySQL实例参数。最核心的innodb_buffer_pool_size我一般建议设置为物理内存的60%到70%因为InnoDB的数据页和索引页都缓存在这里命中率高了对随机读性能提升非常明显。排序相关参数sort_buffer_size不是越大越好默认2MB左右通常够用如果大量排序都超过内存限制优先看SQL能不能避免filesort而不是一味调大排序缓冲区。join_buffer_size同理。还有一个容易被忽略的max_execution_time。可以在会话级别设置SET max_execution_time 5000;让超过5秒的查询主动终止防止接口被一条慢SQL拖死。不过这个设置对存储过程里某些特殊语句可能不生效使用前先读一下当前版本的文档。4.5 ORM生成的SQL也要查执行计划用MyBatis Plus这类框架很多SQL是自动生成的。比如LambdaQueryWrapper构造的查询开发很少有人会去手工检查执行计划但它最终生成的还是普通SQL索引照样会失效。建议在开发环境开启SQL日志实际跑一遍业务把日志里的SQL拿出来EXPLAIN一下。ORM常见的三个问题循环查询、join处理不当、批量操作拆得太多。典型的是在循环里调用单条查询一万条数据就一万次数据库往返性能极差。这种场景改成IN查询或分批批量查询效果立竿见影SELECT * FROM product WHERE id IN (?, ?, ? ...);不过IN列表一次性塞几千个值也有隐患建议每批500个左右。5. 案例复盘一个订单查询从2.8秒到30毫秒5.1 原始SQL和执行计划之前帮一个电商团队看后台订单列表页面打开要3秒左右。抽取出来的SQL是这样SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id LEFT JOIN products p ON o.product_id p.id WHERE o.status 1 ORDER BY o.create_time DESC LIMIT 10000, 20;EXPLAIN结果很典型orders表type是ALLrows估算12万Extra里还有Using filesort。虽然orders表在user_id和status上都有单列索引但查询条件只用了status区分度极低优化器觉得走status索引也没优势干脆全扫。5.2 索引调整我做的第一个调整是新建联合索引ALTER TABLE orders ADD INDEX idx_status_create (status, create_time);这里没有把user_id放进去因为这条查询展示的是全部状态订单按时间排序索引设计要贴合实际SQL。联合索引(status, create_time)既满足WHERE过滤又让ORDER BY create_time直接走索引顺序不需要额外排序。随后把SELECT *改成明确字段并且把深分页改成延迟关联SELECT o.id, o.order_no, o.user_id, o.status, o.amount, o.create_time, u.name AS user_name, p.product_name FROM orders o INNER JOIN ...整体改写后子查询部分先用覆盖索引取得id再回表取详细字段。5.3 效果验证优化前后对比非常直观指标优化前优化后执行时间2.8s32ms扫描行数约12万分页范围内约20行typeALLrangeExtraUsing filesort无压测100个并发接口平均响应从1.9秒降到80毫秒左右数据库CPU使用率也降了下来。5.4 上线后监控优化上线不等于结束。我建议把慢日志阈值调回1秒继续观察一周确认没有新的慢SQL暴露。同时定期检查索引使用情况SELECT * FROM sys.schema_unused_indexes;跑一跑看看有没有长期没被用到的冗余索引该清理就清理减少写入时的维护成本。6. 最后想说的实践中体会我自己做SQL优化的习惯是改任何SQL之前先看执行计划改完之后一定要做真实业务路径验证再看一轮慢日志。很多时候不是数据库缺索引而是索引建错了比如在区分度极低的字段上建单列索引或者查询条件里用函数把索引字段包装一遍等于白建。还有一类情况确实容易误导人慢查询日志打印出来的SQL看着像罪魁祸首结果查完锁信息才发现是一条没提交的长事务卡住了所有更新。这种时候你换什么索引都没用先把事务边界理清楚再说。另外交付优化结果时我会把优化前后的EXPLAIN、执行时间、扫描行数、日志截图全部保存下来。这既是为了让自己复查也是给团队一个明确的参照物到底什么算优化成功了。希望这套思路能帮你在排查SQL性能问题时少走几步弯路。