SQL 查询优化实战：让数据库查询快 10 倍

数据库查询慢是常见性能瓶颈。掌握 SQL 优化技巧，能让你的应用响应速度提升 10 倍以上。

为什么查询会慢？

常见原因：

• 没有索引或索引失效
• 全表扫描
• 查询语句写得差
• 表结构不合理
• 数据量过大

索引优化

1. 创建合适的索引

-- 为常用查询字段创建索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(created_at);

-- 复合索引（注意字段顺序）
CREATE INDEX idx_user_status ON users(status, created_at);

索引选择原则：

• WHERE 子句的字段
• JOIN 连接的字段
• ORDER BY 排序的字段
• 区分度高的字段

2. 避免索引失效

-- 错误：对索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 正确：使用范围查询
SELECT * FROM users 
WHERE created_at >= "2024-01-01" 
AND created_at < "2025-01-01";

-- 错误：LIKE 以%开头
SELECT * FROM users WHERE name LIKE "%张%";

-- 正确：LIKE 不以%开头
SELECT * FROM users WHERE name LIKE "张%";

查询语句优化

1. 只查需要的字段

-- 错误：查询所有字段
SELECT * FROM users;

-- 正确：只查需要的字段
SELECT id, name, email FROM users;

2. 使用 LIMIT 限制结果数

-- 分页查询
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;

3. 优化 JOIN 查询

-- 确保 JOIN 字段有索引
SELECT o.id, o.amount, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = "paid";

-- 小表驱动大表
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.small_id;

4. 避免子查询，改用 JOIN

-- 错误：相关子查询（慢）
SELECT name, 
  (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u;

-- 正确：LEFT JOIN（快）
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE email = "test@example.com";

关键字段说明：

• type：访问类型（system > const > eq_ref > ref > range > index > ALL）
• key：实际使用的索引
• rows：估计扫描的行数
• Extra：额外信息（Using index 是好现象）

常见优化场景

1. 分页优化

-- 错误：深分页（慢）
SELECT * FROM orders LIMIT 100000, 20;

-- 正确：使用覆盖索引
SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders 
  ORDER BY created_at DESC 
  LIMIT 100000, 20
) tmp ON o.id = tmp.id;

-- 或者：记录上次查询的最大 ID
SELECT * FROM orders 
WHERE id < last_max_id 
ORDER BY id DESC 
LIMIT 20;

2. COUNT 优化

-- 错误：COUNT(*) 全表扫描
SELECT COUNT(*) FROM users WHERE status = 1;

-- 正确：使用覆盖索引
SELECT COUNT(id) FROM users WHERE status = 1;

-- 或者：维护计数表

3. OR 条件优化

-- 错误：OR 导致索引失效
SELECT * FROM users 
WHERE email = "test@example.com" OR phone = "123456";

-- 正确：UNION ALL
SELECT * FROM users WHERE email = "test@example.com"
UNION ALL
SELECT * FROM users WHERE phone = "123456";

4. GROUP BY 优化

-- 确保 GROUP BY 字段有索引
SELECT status, COUNT(*) 
FROM orders 
GROUP BY status;

-- 使用索引覆盖
SELECT status, COUNT(id) 
FROM orders 
GROUP BY status;

表结构优化

1. 选择合适的数据类型

-- 错误：用 VARCHAR 存时间
created_at VARCHAR(20)

-- 正确：用 DATETIME
created_at DATETIME

-- 错误：用 INT 存布尔值
is_active INT

-- 正确：用 TINYINT
is_active TINYINT(1)

2. 垂直分表

把大字段拆分到扩展表：

-- 主表（常用字段）
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

-- 扩展表（大字段）
CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,
  bio TEXT,
  avatar TEXT
);

3. 水平分表

按时间或 ID 范围分表：

orders_2024_q1, orders_2024_q2, ...

实战检查清单

• 查询字段是否都有索引
• 是否避免 SELECT *
• JOIN 字段是否有索引
• 是否避免函数操作索引列
• 分页是否优化
• 是否用 EXPLAIN 分析过查询
• 表结构是否合理

结语

SQL 优化是数据库性能的关键。从索引开始，逐步优化查询语句和表结构，你的数据库性能会有质的提升。

记住：先测量，再优化。用 EXPLAIN 找出瓶颈，针对性解决。

现在就去检查你的慢查询吧！