跳到主要内容

SQL 查询优化

问题​

如何分析和优化慢 SQL？EXPLAIN 怎么看？

面试速答版

如何分析和优化慢 SQL？ 三步走：定位 → 分析 → 优化：

  • 定位：开 slow_query_log，long_query_time=1，用 mysqldumpslow 或 pt-query-digest 按 总耗时 / 调用次数排序。
  • 分析：在 SQL 前加 EXPLAIN，看五个关键列（下面详述）。
  • 优化：常见手段是加/调整索引、避免 SELECT *、小表驱动大表、大分页用「延迟关联」（先查主键再 JOIN）。

EXPLAIN 怎么看？

  • type：访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL，看到 ALL 就要警惕（全表扫描）。
  • key：实际用了哪个索引，NULL 说明没走索引。
  • rows：预估扫描行数，越小越好。
  • Extra：Using filesort（额外排序）、Using temporary（临时表）要重点优化；Using index 则是覆盖索引，最理想。
  • key_len：联合索引用了几个列，可以验证是否命中最左前缀。

答案​

慢查询发现​

slow-query.sql
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log_file';

EXPLAIN 分析​

explain.sql
EXPLAIN SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.age > 20
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;
列说明关注点
type访问类型ALL(全表扫描) → index → range → ref → const
key使用的索引NULL 表示未使用索引
rows预估扫描行数越小越好
Extra额外信息Using filesort、Using temporary 需关注
type 性能排序

system > const > eq_ref > ref > range > index > ALL

出现 ALL（全表扫描）通常需要优化。

常见优化技巧​

optimization.sql
-- 1. 避免 SELECT *
-- ❌
SELECT * FROM users WHERE id = 1;
-- ✅ 只查需要的列
SELECT name, email FROM users WHERE id = 1;

-- 2. 分页优化（深翻页问题）
-- ❌ OFFSET 越大越慢
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- ✅ 游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 3. 避免在索引列上使用函数
-- ❌ 索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- ✅ 范围查询，可以使用索引
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 4. 用 EXISTS 替代 IN（大子查询时）
-- ❌ IN 子查询可能全表扫描
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- ✅ EXISTS 可以提前终止
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 5. 批量操作
-- ❌ 逐条插入
INSERT INTO logs (msg) VALUES ('a');
INSERT INTO logs (msg) VALUES ('b');
-- ✅ 批量插入
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');

-- 6. COUNT 优化
-- ❌ COUNT(*)（InnoDB 需全表扫描）
SELECT COUNT(*) FROM users;
-- ✅ 维护计数器（Redis 或 summary 表）

Node.js 中的查询优化​

query-optimization.ts
// Prisma: 使用 select 替代 include 减少数据量
const users = await prisma.user.findMany({
  select: { id: true, name: true },  // 只取需要的字段
  where: { status: 'active' },
});

// 批量操作
await prisma.user.createMany({
  data: users,     // 一次插入多条
  skipDuplicates: true,
});

// 原生 SQL（复杂查询）
const result = await prisma.$queryRaw`
  SELECT u.name, COUNT(o.id) as order_count
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.created_at > ${startDate}
  GROUP BY u.id
  HAVING order_count > 5
`;

常见面试问题​

Q1: 深翻页（大 OFFSET）为什么慢？​

答案：

LIMIT 10 OFFSET 100000 实际上会扫描前 100010 行，然后丢弃前 100000 行。解决方案：

  1. 游标分页：WHERE id > last_id LIMIT 10
  2. 延迟关联：先查主键，再 JOIN 取数据

Q2: 联合索引的列顺序怎么确定？​

答案：

遵循「区分度高的列在前」原则：

  1. 等值条件的列放前面
  2. 范围条件放后面
  3. 排序字段放最后
  4. 区分度高的列优先

Q3: COUNT(1) 和 COUNT(*) 有区别吗？​

答案：

在 MySQL InnoDB 中没有区别，优化器会统一处理。COUNT(column) 不计算 NULL 值。

相关链接​