SQL 调优

SQL 调优

一、如何判断 SQL 性能好坏？

1. 观察执行时间

直接通过 SQL 的执行耗时初步判断性能：

SELECT * FROM users WHERE email = 'user@example.com'; -- 耗时 2 秒（性能差）

如果查询耗时明显偏高（如超过 1 秒），则需要进一步分析。

2. 使用 EXPLAIN 分析执行计划

在 SQL 前添加 EXPLAIN，查看 MySQL 如何执行查询：

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

输出结果的关键字段解析（见下文第四部分详细说明）。

3. 开启慢查询日志

作用：自动记录执行时间过长的 SQL，帮助定位性能瓶颈。

开启步骤

方法 1：修改配置文件（永久生效）

1. 找到 MySQL 配置文件 my.cnf（Linux 通常在 /etc/mysql/，Windows 在安装目录下）。
2. 添加以下配置：

   [mysqld]
   slow_query_log = 1                      # 开启慢查询日志
   slow_query_log_file = /var/log/mysql/slow.log  # 日志路径
   long_query_time = 2                     # 记录超过 2 秒的查询
   log_queries_not_using_indexes = 1       # 记录未使用索引的查询
   

3. 重启 MySQL 服务：

   service mysql restart  # Linux
   

方法 2：动态设置（临时生效）

SET GLOBAL slow_query_log = 'ON';          -- 开启日志
SET GLOBAL long_query_time = 2;            -- 设置阈值（单位：秒）
SET GLOBAL slow_query_log_file = '/path/to/slow.log';  -- 指定日志路径

查看慢查询日志

tail -f /var/log/mysql/slow.log  # 实时查看日志内容

二、SQL 调优实战案例

场景

用户表 users 有 100 万条数据，查询 email 字段耗时 2 秒。

原始 SQL

SELECT * FROM users WHERE email = 'user@example.com';  -- 耗时 2 秒

步骤 1：使用 EXPLAIN 分析问题

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

执行计划结果：

关键问题：

• type: ALL 表示全表扫描。
• rows: 1000000 预估扫描 100 万行。
• key: NULL 未使用索引。

步骤 2：添加索引优化

ALTER TABLE users ADD INDEX idx_email (email);  -- 为 email 创建索引

优化后再次执行

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

执行计划结果：

优化效果：

• type: ref 表示通过索引查找。
• rows: 1 仅扫描 1 行。
• 查询时间从 2 秒降至 0.01 秒！

三、EXPLAIN 结果深度解析

1. 核心字段说明

2. 其他重要字段

• possible_keys：可能使用的索引（若为空，说明查询条件不适合索引）。
• key_len：索引使用的字节数，可判断是否使用了索引的全部部分。
• filtered：查询条件过滤后的数据占比（值越小，索引效果越好）。

四、慢查询日志与 EXPLAIN 结合调优

操作流程

1. 开启慢查询日志：定位耗时长的 SQL。
2. 复制慢 SQL：从日志中提取问题语句。
3. 使用 EXPLAIN 分析：查看执行计划，定位全表扫描、未使用索引等问题。
4. 针对性优化：添加索引、改写 SQL 或调整表结构。

示例：优化分页查询

慢日志中的 SQL：

SELECT * FROM orders ORDER BY create_time LIMIT 100000, 10;  -- 耗时 5 秒

步骤 1：EXPLAIN 分析

EXPLAIN SELECT * FROM orders ORDER BY create_time LIMIT 100000, 10;

结果：

• type: index（全索引扫描）
• rows: 100010
• Extra: Using filesort

步骤 2：优化策略

• 避免深分页：通过 WHERE 条件定位上一页的末尾 ID。

SELECT * FROM orders 
WHERE create_time > '2023-10-01'  -- 基于业务场景添加时间范围
ORDER BY create_time LIMIT 10;

五、调优注意事项

1. 索引的代价

   • 索引会占用磁盘空间，并降低写入速度（如 INSERT/UPDATE）。
   • 只为高频查询字段添加索引。

2. 避免过度优化

   • 小表（如 1000 行以内）无需索引。
   • 优先优化耗时最长的 SQL。

3. 定期维护

   • 使用 ANALYZE TABLE orders; 更新索引统计信息。
   • 清理无效索引：ALTER TABLE orders DROP INDEX unused_index;

六、总结

• 慢查询日志：定位性能问题的入口。
• EXPLAIN：分析执行计划的必备工具，重点关注 type 和 rows。
• 调优核心：减少数据扫描量，合理利用索引。

通过日志定位问题 SQL，结合 EXPLAIN 分析执行计划，即使是新手也能快速提升数据库性能！