前言：作为后端开发者，几乎每天都会和MySQL打交道。你是否遇到过这样的场景？接口突然超时、数据库CPU飙到90%+、慢查询日志刷屏，排查后发现罪魁祸首竟是几句“不起眼”的SQL。其实MySQL性能优化的核心，80%都集中在SQL优化上——无需升级硬件，无需重构架构，只要优化好SQL，就能让数据库性能实现质的飞跃。

一、优化前必做：先找到“有问题”的SQL（拒绝盲目优化）

SQL优化的前提是“精准定位”，没有数据支撑的优化都是瞎忙活。很多人上来就改SQL，反而越改越乱。记住：不测量不优化，优先优化最耗资源的20% SQL（二八法则），这才是最高效的思路。以下3个工具，帮你快速锁定慢SQL，新手必学。

1. 慢查询日志：定位慢SQL的“黑匣子”

慢查询日志是MySQL自带的日志功能，专门记录执行时间超过指定阈值的SQL，是排查慢查询的首选工具。

（1）开启慢查询日志（临时生效/永久生效）

临时生效（重启MySQL后失效，适合临时排查）：

-- 开启慢查询日志

  SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值（单位：秒，生产环境建议设1~5秒，根据业务调整）

  SET GLOBAL long_query_time = 1;

-- 指定慢查询日志存储路径（需授权MySQL权限）

  SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 查看配置是否生效（需重新连接MySQL才能看到最新值）

  SHOW VARIABLES LIKE '%slow_query%';

  SHOW VARIABLES LIKE '%long_query_time%';

永久生效（修改配置文件，重启MySQL生效）：

  slow_query_log = 1

  slow_query_log_file = /var/lib/mysql/mysql-slow.log

  long_query_time = 1

-- 生产环境不建议长期开启，避免日志过大

  log_queries_not_using_indexes = 0

（2）解读慢查询日志核心信息

# Time: 2026-04-07T20:37:00.000000Z -- 查询执行时间

# User@Host: root(root) @ localhost () -- 执行用户和客户端

# Query_time: 5.23 Lock_time: 0.001 Rows_sent: 10 Rows_examined: 10000 -- 关键指标

SET timestamp=1712488620;

SELECT * FROM orders WHERE user_id = 12345; -- 有问题的SQL

关键指标解读：

• Query_time：查询执行时间（核心，越长越危险）；

• Lock_time：锁等待时间（过长说明有锁冲突）；

• Rows_sent：返回给客户端的行数；

• Rows_examined：MySQL实际扫描的行数（若远大于Rows_sent，说明索引失效或SQL写法有问题）。

2. EXPLAIN：分析SQL执行计划（核心工具）

找到慢SQL后，用EXPLAIN命令分析其执行计划，就能知道SQL是如何执行的——是否走索引、扫描了多少行、有没有做排序/临时表，从而精准定位优化点。

用法：在SQL前加EXPLAIN即可，例如：

EXPLAIN SELECT * FROM users WHERE age > 30 ORDER BY create_time DESC LIMIT 10

执行后会输出12列核心信息，新手无需全部掌握，重点关注以下5列，就能解决80%的问题，对应解读如下表：

列名	核心解读	理想值/优化方向
type	数据访问类型，决定查询效率	system > const > eq_ref > ref > range > index > ALL（避免ALL，即全表扫描）
key	实际使用的索引	非NULL，说明走了索引；为NULL，说明未走索引
key_len	使用的索引长度，越长越精准	匹配索引定义长度，避免索引失效导致长度变短
rows	预估扫描的行数	数值越小越好，越接近实际返回行数越好
Extra	额外执行信息（关键优化信号）	优先出现Using index（覆盖索引）；避免Using filesort（文件排序）、Using temporary（临时表）

二、SQL优化核心技巧（实战落地，直接套用）

SQL优化的核心逻辑是：减少数据扫描量、避免索引失效、减少IO和CPU消耗。以下技巧覆盖查询、索引、排序分组等高频场景，附正反案例，直接套用就能见效。

技巧1：查询优化——拒绝“SELECT *”，只查需要的字段

很多人习惯写SELECT *，看似方便，实则隐藏两大问题：① 读取无用字段，增加IO消耗；② 无法使用覆盖索引，导致回表查询，效率大幅下降。

补充：覆盖索引是指索引包含查询所需的所有字段，MySQL无需回表，直接从索引中获取数据，效率极高。例如给users表建立索引idx_phone(phone, username)，上述优化后的SQL就能直接使用该索引，无需回表。

技巧2：索引优化——避免索引失效，让索引“物尽其用”

索引是SQL优化的“灵魂”，但很多时候，我们建了索引，却因为写法问题导致索引失效，相当于白建。以下是最常见的索引失效场景，务必避开！

1.避免索引列使用函数或计算

2.避免隐式类型转换

        索引列类型与查询条件类型不匹配，会触发隐式转换，导致索引失效。最常见的就是字符串类型字段用数字查询。

3.避免前导通配符“%”

        LIKE查询中，前导通配符“%”会导致索引失效，若必须模糊查询，可采用后导通配符，或使用全文索引。

4.复合索引遵循“最左前缀原则”

        创建复合索引（多列索引）时，查询条件需匹配索引的最左列，否则索引失效。例如创建复合索引idx_a_b_c(a, b, c)，只有查询条件包含a，或a+b，或a+b+c时，才能使用该索引。

5.索引设计原则

• 选择性原则：选择区分度高的列建索引（区分度=不重复值数量/总行数，区分度>20%时，索引效果显著），例如phone字段区分度远高于gender字段，优先给phone建索引；

• 适度原则：避免过度索引，索引会占用存储空间，且会降低插入、更新、删除的效率（每次写操作都要维护索引），一张表索引建议不超过5个；

• 覆盖索引优先：尽量让查询字段都包含在索引中，避免回表查询。

技巧3：排序与分组优化——避免Using filesort和Using temporary

EXPLAIN输出中，Extra出现Using filesort（文件排序）或Using temporary（临时表），说明SQL效率较低，需优化。

1.排序优化：让排序字段包含在索引中

2.分组优化：分组字段优先走索引

技巧4：JOIN查询优化——减少关联次数，避免笛卡尔积

JOIN查询是高频场景，优化核心是：小表驱动大表（减少循环次数）、给关联字段建索引（避免全表扫描）、避免不必要的关联。

1. 给关联字段建索引：

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

2. 小表驱动大表（假设users是小表，orders是大表）

SELECT o.id, o.order_no, u.username FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 1;

补充：LEFT JOIN时，关联条件的索引建在右表；RIGHT JOIN时，索引建在左表；INNER JOIN时，索引建在任一表均可。

技巧5：分页查询优化——解决“深分页”性能问题

当分页页数较大时（如LIMIT 100000, 10），MySQL会扫描前100010行，再丢弃前100000行，效率极低，这就是“深分页”问题。

技巧6：子查询优化——尽量用JOIN替代子查询

子查询会创建临时表，增加IO消耗，尤其是子查询结果集较大时，效率远低于JOIN查询，建议尽量用JOIN替代。

三、实战案例：从慢SQL到优化完成（全程落地）

结合上面的技巧，用一个真实案例演示SQL优化的完整流程，让大家更易理解。

案例场景

某电商订单表orders（100万条数据），查询“2026年4月1日后，用户ID为100的待付款订单，按创建时间倒序取前10条”，原始SQL如下，执行耗时3.8秒。

SELECT * FROM orders WHERE user_id = 100 AND status = 0 AND create_time > '2026-04-01 00:00:00' ORDER BY create_time DESC LIMIT 10;

优化步骤

1. 用EXPLAIN分析执行计划，发现：type=ALL（全表扫描）、key=NULL（未走索引）、Extra=Using where; Using filesort（文件排序），这是慢查询的核心原因。

2. 分析索引：orders表未给user_id、status、create_time建索引，导致全表扫描和文件排序。

3. 优化SQL+创建索引：

   1. 创建复合索引（遵循最左前缀原则，包含查询条件和排序字段）：ALTER TABLE orders ADD INDEX idx_user_status_create (user_id, status, create_time);

   2. 优化SQL，避免SELECT *，只查需要字段：

SELECT id, order_no, create_time FROM orders WHERE user_id = 100 AND status = 0 AND create_time > '2026-04-01 00:00:00' ORDER BY create_time DESC LIMIT 10;

优化效果

执行耗时从3.8秒降至0.002秒，EXPLAIN分析显示：type=ref（走索引）、key=idx_user_status_create（使用创建的复合索引）、Extra=Using index（覆盖索引，无需回表），优化成功！

四、SQL优化避坑指南（新手必看）

• 避坑1：索引不是越多越好，过度索引会降低写入性能，一张表索引建议不超过5个；

• 避坑2：避免用OR连接多个条件，OR会导致索引失效（若必须用OR，需给每个条件字段单独建索引）；

• 避坑3：避免使用NULL值，NULL会增加判断成本，且索引不存储NULL值，建议用默认值替代（如用0替代NULL）；

• 避坑4：生产环境不要随意开启log_queries_not_using_indexes，否则慢查询日志会爆炸式增长，占用磁盘空间；

• 避坑5：优化后一定要测试，每次只改一个点，对比优化前后的执行时间，避免优化后出现新问题；

• 避坑6：不要强行用索引，MySQL优化器会自动选择最优执行计划，有时全表扫描比索引查询更快（如小表，数据量<10行）。

五、总结

MySQL SQL优化的核心，其实就是“减少数据扫描量、利用好索引、降低IO和CPU消耗”。它不是一蹴而就的，而是一个循序渐进的过程——先通过慢查询日志和EXPLAIN定位问题，再根据本文的技巧逐步优化，最后测试验证效果。

对于新手来说，不用追求复杂的优化技巧，先掌握“避免SELECT *、给核心字段建索引、避免索引失效、优化分页和JOIN”这几个基础点，就能解决大部分慢查询问题；对于老手，可结合高级技巧（如索引下推、不可见索引、分区表）进一步提升性能。

最后提醒：SQL优化没有标准答案，需结合业务场景和数据量灵活调整。建议大家在实际工作中，多动手、多分析执行计划，积累实战经验，才能真正掌握SQL优化的精髓。