3. SQL查询优化：EXPLAIN执行计划解读、慢查询日志分析、避免SELECT * 与索引失效场景

查询优化，说白了就是让数据库少干活。你写一条SQL，数据库怎么执行它？是走索引还是全表扫？这些信息都藏在执行计划里。我刚开始做优化时，也是两眼一抹黑，后来慢慢摸清了门道。今天咱们就聊聊这几个核心点。

3.1 EXPLAIN执行计划解读

EXPLAIN是MySQL里最常用的调优工具。你只要在SQL前面加上EXPLAIN，数据库就会告诉你它打算怎么干这活。我个人习惯，但凡线上要跑的SQL，都会先EXPLAIN一把看看。

核心字段解读：

  • type：访问类型，从好到差依次是 system > const > eq_ref > ref > range > index > ALL。ALL就是全表扫，能避免就避免。
  • key：实际使用的索引。如果为NULL，说明没走索引。
  • rows：预估扫描的行数。这个数字越小越好。
  • Extra：额外信息，比如Using filesort、Using temporary都是性能杀手。

举个例子，你写个查询：

EXPLAIN SELECT * FROM orders WHERE order_date = '2024-01-01';

如果type是ALL，rows是几十万，那基本可以断定这条SQL要出问题。我在项目中遇到过，一个报表查询跑了30秒，一看就是全表扫。加了个索引后，直接降到0.1秒。

小技巧：EXPLAIN的结果只是预估，不是实际执行。想看到真实执行情况，可以用EXPLAIN ANALYZE（MySQL 8.0.18+）。

3.2 慢查询日志分析

慢查询日志，就是数据库帮你记下来的那些跑得慢的SQL。你想想看，如果没人告诉你哪条SQL慢，你怎么优化？

开启慢查询日志很简单：

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置阈值（超过2秒就算慢）
SET GLOBAL long_query_time = 2;

日志文件默认在数据目录下，名字一般是主机名-slow.log。打开后你会看到类似这样的内容：

# Time: 2024-01-15T10:30:00
# Query_time: 5.234  Lock_time: 0.001
# Rows_sent: 100  Rows_examined: 500000
SELECT * FROM orders WHERE status = 'pending';

这里Query_time是5.234秒，Rows_examined是50万行，但只返回了100行。这说明什么？扫描了大量数据，但只返回了少量结果。典型的索引缺失。

注意：慢查询日志在生产环境要谨慎开启，尤其是磁盘IO紧张的时候。我曾经在高峰期开了慢查询日志，结果日志写入把磁盘打满了...嗯，从那以后我都是低峰期才开。

分析慢查询，我一般用mysqldumpslow工具：

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

这个命令按查询时间排序，显示最慢的10条SQL。省得你一条条翻日志。

3.3 避免SELECT *

SELECT * 是新手最爱，也是性能杀手。为什么？

  • 它会把所有字段都查出来，哪怕你只需要两个字段。
  • 它没法利用覆盖索引。覆盖索引的意思是，索引里已经包含了你要的数据，不用回表查。但SELECT * 通常需要回表。
  • 它增加了网络传输量。字段越多，数据包越大。

举个例子：

-- 不推荐
SELECT * FROM users WHERE age > 18;

-- 推荐
SELECT id, name, age FROM users WHERE age > 18;

如果users表有20个字段，但你只需要3个，SELECT * 就多查了17个字段。数据量大了以后，这个差距非常明显。我记得有个项目，接口响应慢，查了半天发现是SELECT * 惹的祸。改成指定字段后，响应时间从2秒降到了0.3秒。

习惯养成：写SQL时，先想清楚要哪些字段，再写SELECT。别偷懒用星号。

3.4 索引失效场景

索引建了，但SQL没走索引，这种情况我见得太多了。下面列几个常见的坑：

场景 错误写法 正确写法
对索引列使用函数 WHERE DATE(create_time) = '2024-01-01' WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'
隐式类型转换 WHERE phone = 13800138000（phone是varchar） WHERE phone = '13800138000'
LIKE以通配符开头 WHERE name LIKE '%张三%' WHERE name LIKE '张三%'
OR条件中有非索引列 WHERE age = 18 OR status = 1（status无索引） 拆成两个查询用UNION，或者给status加索引
联合索引未用最左前缀 索引(a,b,c)，但WHERE b = 1 按最左前缀使用，比如WHERE a = 1 AND b = 1

这里重点说一下联合索引。你建了(a,b,c)的联合索引，但查询条件是b = 1，那这个索引就用不上。因为MySQL是从左到右匹配的。我有个同事就踩过这个坑，建了联合索引但查询条件没带最左列，结果全表扫，业务直接卡死。

避坑指南：我曾经在线上环境遇到一个慢查询，排查了半天才发现是隐式类型转换。字段是varchar，但传参传了数字，MySQL做了类型转换，索引就失效了。所以写SQL时，类型一定要匹配。

还有一个容易被忽略的点：数据分布不均匀。比如性别字段，只有男和女两种值，就算建了索引，MySQL也可能不走。因为它觉得全表扫更快。这种情况，索引其实没什么用。

3.5 总结

查询优化，说白了就是三件事：看懂执行计划、用好慢查询日志、避免踩索引的坑。我个人习惯，每次上线前都会过一遍慢查询日志，看看有没有新增的慢SQL。发现问题及时处理，别等用户投诉了再救火。

嗯，今天就聊到这儿。下一章咱们聊聊分库分表，那个坑更多，到时候再细说。

← 上一章 📖 返回目录 下一章 →