跳到主要内容

SQL 优化

问题​

如何发现和优化慢 SQL？常见的 SQL 优化技巧有哪些？如何进行查询重写和索引优化？

答案​

SQL 优化流程​

发现慢 SQL​

慢查询日志​

开启慢查询日志
-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

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

-- 设置慢查询阈值（默认 10 秒，建议 1 秒）
SET GLOBAL long_query_time = 1;

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看慢查询日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';
使用 mysqldumpslow 分析慢查询日志
# 按执行时间排序，取前 10 条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 按出现次数排序
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

实时查看​

查看正在执行的慢查询
-- 查看当前进程列表
SHOW PROCESSLIST;

-- 查看执行时间超过 5 秒的查询
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 5;

EXPLAIN 执行计划​

EXPLAIN 是分析 SQL 性能最重要的工具，参见 EXPLAIN 详解 获取完整说明。这里给出关键列的速查：

列名重点关注
type访问类型：ALL(全表扫描) < index < range < ref < eq_ref < const
key实际使用的索引，NULL 表示没用索引
rows预估扫描行数，越小越好
ExtraUsing filesort（需优化排序）、Using temporary（需优化临时表）、Using index（覆盖索引 ✅）

常见优化技巧​

1. 索引优化​

为 WHERE 条件创建合适索引
-- ❌ 全表扫描
SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid';

-- ✅ 创建联合索引
ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);
利用覆盖索引避免回表
-- ❌ SELECT * 需要回表
SELECT * FROM user WHERE name = '张三';

-- ✅ 只查索引中的字段
SELECT id, name FROM user WHERE name = '张三';
避免索引失效
-- ❌ 函数破坏索引
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- ✅ 改为范围查询
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- ❌ 隐式类型转换
SELECT * FROM user WHERE phone = 13800138000;  -- phone 是 varchar
-- ✅ 字符串加引号
SELECT * FROM user WHERE phone = '13800138000';

-- ❌ 左模糊
SELECT * FROM user WHERE name LIKE '%三';
-- ✅ 右模糊可以用索引
SELECT * FROM user WHERE name LIKE '张%';

关于索引失效的详细说明，参见 索引原理与优化。

2. 查询重写​

用 EXISTS 替代 IN（大子查询时）
-- ❌ 子查询结果集很大时性能差
SELECT * FROM user WHERE id IN (SELECT user_id FROM orders);

-- ✅ EXISTS 对于外表小、子表大的场景更优
SELECT * FROM user u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);
用 JOIN 替代子查询
-- ❌ 相关子查询，每行都执行一次子查询
SELECT *, (SELECT COUNT(*) FROM orders WHERE orders.user_id = user.id) AS order_count
FROM user;

-- ✅ 改为 JOIN
SELECT u.*, COUNT(o.id) AS order_count
FROM user u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
分页优化
-- ❌ OFFSET 大时性能差（需要扫描 offset + limit 行）
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- ✅ 延迟关联：先查出主键，再回表
SELECT * FROM orders
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) AS t
ON orders.id = t.id;

-- ✅ 游标分页：记住上次的位置
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
UNION ALL 替代 UNION
-- ❌ UNION 会去重排序
SELECT name FROM table_a UNION SELECT name FROM table_b;

-- ✅ 不需要去重时用 UNION ALL
SELECT name FROM table_a UNION ALL SELECT name FROM table_b;

3. JOIN 优化​

小表驱动大表
-- MySQL 优化器通常会自动选择小表做驱动表
-- 但可以通过 STRAIGHT_JOIN 强制指定驱动表顺序
SELECT * FROM small_table
STRAIGHT_JOIN large_table ON small_table.id = large_table.ref_id;
JOIN 算法
  • Nested Loop Join：小表逐行驱动大表，适合大表有索引
  • Block Nested Loop Join（MySQL 5.7）：利用 Join Buffer 批量匹配
  • Hash Join（MySQL 8.0.18+）：无索引时自动使用，大幅提升无索引 JOIN 的性能

4. COUNT 优化​

COUNT 的不同写法
-- COUNT(*) 和 COUNT(1) 性能相同，MySQL 会自动优化
-- COUNT(列名) 会排除 NULL 值

-- ✅ 大表 COUNT 近似值（快但不精确）
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

对于精确计数需求，可以：

  1. 使用 Redis 等缓存维护计数
  2. 使用额外的计数表
  3. 使用 COUNT(*) + 合适的索引（InnoDB 会选择最小的索引树）

5. INSERT 优化​

批量插入
-- ❌ 逐条插入
INSERT INTO user (name, age) VALUES ('张三', 25);
INSERT INTO user (name, age) VALUES ('李四', 30);

-- ✅ 批量插入（一条 SQL 插入多行）
INSERT INTO user (name, age) VALUES
('张三', 25),
('李四', 30),
('王五', 28);
大量数据导入
-- 使用 LOAD DATA 导入 CSV（比 INSERT 快 20 倍）
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE user
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

-- 导入前关闭索引和约束检查
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- 执行批量插入
-- 导入后恢复
SET unique_checks = 1;
SET foreign_key_checks = 1;
COMMIT;

6. ORDER BY 优化​

利用索引避免排序
-- 假设有索引 idx_user_create(user_id, create_time)
-- ✅ ORDER BY 的字段在索引中，无需额外排序
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time;

-- ❌ 排序字段不在索引中，触发 filesort
SELECT * FROM orders WHERE user_id = 1001 ORDER BY amount;

ORDER BY 的两种排序方式：

  • Using index：直接利用索引的有序性，无需排序 ✅
  • Using filesort：需要额外排序（内存或磁盘），性能差

SQL 优化速查表​

场景优化方案
全表扫描添加合适索引
Using filesort让 ORDER BY 字段在索引中
Using temporary优化 GROUP BY、DISTINCT
大 OFFSET 分页延迟关联或游标分页
SELECT *只查需要的字段
子查询改为 JOIN 或 EXISTS
UNION不需去重时用 UNION ALL
逐条 INSERT批量 INSERT
索引失效避免函数、类型转换、左模糊
长事务拆分事务、减少锁持有时间

常见面试问题​

Q1: 如何定位慢 SQL？​

答案：

  1. 开启慢查询日志：设置 slow_query_log = ON，long_query_time = 1
  2. 分析日志：使用 mysqldumpslow 或 pt-query-digest 找出频繁出现的慢 SQL
  3. 实时监控：SHOW PROCESSLIST 查看当前执行中的查询
  4. EXPLAIN 分析：对慢 SQL 执行 EXPLAIN，查看执行计划
  5. 关注 key 指标：type 是否为 ALL、rows 是否过大、Extra 是否有 filesort/temporary

Q2: 深分页（大 OFFSET）为什么慢？如何优化？​

答案：

LIMIT 100000, 10 实际上要先扫描 100010 行，丢弃前 100000 行，效率极低。

优化方案：

  1. 延迟关联：先在索引中快速定位主键，再回表
SELECT * FROM orders
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t
ON orders.id = t.id;
  1. 游标分页（推荐）：记住上次查询的最后一条记录的 ID
-- 前端传入 last_id（上一页最后一条的 id）
SELECT * FROM orders WHERE id > #{lastId} ORDER BY id LIMIT 10;

游标分页的局限：不支持随机跳页，只能"上一页/下一页"。

Q3: 为什么不建议使用 SELECT *？​

答案：

  1. 无法使用覆盖索引：SELECT * 返回所有字段，必须回表查询完整行数据
  2. 增加网络传输开销：返回不需要的字段浪费带宽
  3. 增加内存消耗：数据库和应用都需要处理更多数据
  4. 影响查询优化器判断：可能导致优化器选择不同的执行计划

Q4: EXISTS 和 IN 怎么选择？​

答案：

关键看驱动表和被驱动表的大小：

  • IN 适合子查询结果集小的场景：SELECT * FROM A WHERE id IN (SELECT id FROM B)，先执行子查询获取 B 的结果集，再用结果集驱动 A
  • EXISTS 适合外表小、子表大的场景：SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id)，遍历外表 A 的每一行，对每行执行 EXISTS 子查询

简单记忆：小表驱动大表。实际上 MySQL 优化器在很多情况下会自动优化 IN 和 EXISTS 的执行方式。

Q5: 如何优化大表的 COUNT(*)？​

答案：

InnoDB 的 COUNT(*) 需要遍历索引（会选择最小的二级索引），大表很慢。

优化方案：

方案精确度适用场景
information_schema.TABLES.TABLE_ROWS近似值（误差可达 50%）只需大概数量
额外计数表精确需要精确值，用触发器或 INSERT/DELETE 时同步更新
Redis 缓存计数最终一致高并发场景，允许短暂不一致
COUNT(*) + 索引精确数据量可接受（百万级）

相关链接​