企业官网建设流程全解析

做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 = ON

long_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，我整理了一个速查表：

在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 TABLE：

ANALYZE 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 索引失效场景速查表

在这个环节，我需要一条条列清楚，面试也是高频考点：

有一次排查一个报表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条，批与批之间加一点sleep：

DELETE 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是ALL，rows估算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 效果验证

优化前后对比非常直观：

压测100个并发，接口平均响应从1.9秒降到80毫秒左右，数据库CPU使用率也降了下来。

5.4 上线后监控

优化上线不等于结束。我建议把慢日志阈值调回1秒继续观察一周，确认没有新的慢SQL暴露。同时定期检查索引使用情况：

SELECT * FROM sys.schema_unused_indexes;

跑一跑看看有没有长期没被用到的冗余索引，该清理就清理，减少写入时的维护成本。

6. 最后想说的实践中体会

我自己做SQL优化的习惯是：改任何SQL之前先看执行计划，改完之后一定要做真实业务路径验证，再看一轮慢日志。很多时候不是数据库缺索引，而是索引建错了，比如在区分度极低的字段上建单列索引，或者查询条件里用函数把索引字段包装一遍，等于白建。

还有一类情况确实容易误导人：慢查询日志打印出来的SQL看着像罪魁祸首，结果查完锁信息才发现是一条没提交的长事务卡住了所有更新。这种时候你换什么索引都没用，先把事务边界理清楚再说。

另外，交付优化结果时，我会把优化前后的EXPLAIN、执行时间、扫描行数、日志截图全部保存下来。这既是为了让自己复查，也是给团队一个明确的参照物：到底什么算优化成功了。希望这套思路能帮你在排查SQL性能问题时少走几步弯路。