SQL 性能优化

SQL 性能优化是数据库开发中的核心技能。一个优化良好的查询可以从几小时缩短到几秒。本章将介绍 SQL 查询优化的核心原则和实用技巧。

为什么需要优化？

性能问题的表现

• 查询响应时间过长
• 数据库 CPU 占用过高
• 磁盘 I/O 繁忙
• 应用程序超时
• 用户体验差

性能优化的收益

• 提升系统响应速度
• 降低服务器资源消耗
• 改善用户体验
• 节省硬件成本
• 提高系统可扩展性

查询优化基础

使用 EXPLAIN 分析查询

EXPLAIN 是分析 SQL 查询执行计划的核心工具，可以帮助理解数据库如何执行查询。

-- 基本用法
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 更详细的分析（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

EXPLAIN 输出解读

字段	说明	关注点
id	查询标识符	相同 id 表示同时执行
select_type	查询类型	SIMPLE 最好，SUBQUERY 需关注
table	访问的表	-
type	访问类型	从好到差：system > const > eq_ref > ref > range > index > ALL
possible_keys	可能使用的索引	-
key	实际使用的索引	NULL 表示未使用索引
key_len	使用的索引长度	越短越好
rows	预计扫描行数	越少越好
Extra	额外信息	Using filesort、Using temporary 需关注

type 字段详解

从最优到最差：

system    → 表只有一行（系统表）
const     → 主键或唯一索引常量查询
eq_ref    → 主键或唯一索引关联查询
ref       → 非唯一索引查询
range     → 索引范围扫描
index     → 全索引扫描
ALL       → 全表扫描（最差）

-- type = const（最优）
EXPLAIN SELECT * FROM users WHERE id = 1;

-- type = ref（较好）
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- type = ALL（最差，需要优化）
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;

避免全表扫描

全表扫描是最耗时的操作，应该尽量避免。

-- 问题：没有使用索引，全表扫描
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 优化：使用索引列进行范围查询
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

索引优化

索引使用原则

1. 选择合适的列建立索引

-- 适合建立索引的列
-- 1. WHERE 条件中频繁使用的列
CREATE INDEX idx_status ON orders(status);

-- 2. JOIN 关联的列
CREATE INDEX idx_user_id ON orders(user_id);

-- 3. ORDER BY 排序的列
CREATE INDEX idx_created_at ON orders(created_at);

-- 4. 高选择性的列（区分度高）
CREATE INDEX idx_email ON users(email);

2. 复合索引的最左前缀原则

-- 创建复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 可以使用索引
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';

-- 不能使用索引（跳过了 name）
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE city = '北京';

-- 部分使用索引（只用到 name）
SELECT * FROM users WHERE name = '张三' AND city = '北京';

3. 覆盖索引

如果查询的所有列都在索引中，数据库可以直接从索引获取数据，无需回表。

-- 创建覆盖索引
CREATE INDEX idx_user_cover ON orders(user_id, status, amount);

-- 查询只需要索引中的列，无需回表
SELECT user_id, status, amount FROM orders WHERE user_id = 1;

索引失效场景

-- 1. 在索引列上使用函数
SELECT * FROM users WHERE UPPER(name) = 'ZHANG';  -- 索引失效
SELECT * FROM users WHERE name = 'ZHANG';         -- 索引有效

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;    -- phone 是 VARCHAR，索引失效
SELECT * FROM users WHERE phone = '13800138000';  -- 索引有效

-- 3. 使用 NOT、!=、<>、NOT IN
SELECT * FROM users WHERE status != 0;            -- 索引可能失效
SELECT * FROM users WHERE status IN (1, 2);       -- 索引有效

-- 4. LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%张';        -- 索引失效
SELECT * FROM users WHERE name LIKE '张%';        -- 索引有效

-- 5. OR 连接非索引列
SELECT * FROM users WHERE name = '张三' OR age = 25;  -- 如果 age 没有索引，整个索引失效

-- 6. 对索引列进行计算
SELECT * FROM orders WHERE amount + 100 > 1000;   -- 索引失效
SELECT * FROM orders WHERE amount > 900;          -- 索引有效

查询优化技巧

1. 只选择需要的列

-- 不推荐：SELECT * 查询所有列
SELECT * FROM users WHERE id = 1;

-- 推荐：只查询需要的列
SELECT id, name, email FROM users WHERE id = 1;

原因：

• 减少数据传输量
• 提高查询速度
• 可能使用覆盖索引

2. 使用 LIMIT 限制结果

-- 不推荐：查询所有数据
SELECT * FROM orders WHERE user_id = 1;

-- 推荐：限制返回数量
SELECT * FROM orders WHERE user_id = 1 LIMIT 100;

3. 避免使用 SELECT DISTINCT

-- 不推荐：DISTINCT 需要排序和去重
SELECT DISTINCT user_id FROM orders;

-- 推荐：使用 GROUP BY 或 EXISTS
SELECT user_id FROM orders GROUP BY user_id;

4. 使用 UNION ALL 替代 UNION

-- UNION 会去重，需要额外排序
SELECT id FROM users WHERE status = 1
UNION
SELECT id FROM users WHERE status = 2;

-- UNION ALL 不去重，性能更好
SELECT id FROM users WHERE status = 1
UNION ALL
SELECT id FROM users WHERE status = 2;

5. 使用 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);

选择原则：

• 子查询结果集小，用 IN
• 主查询结果集小，用 EXISTS

6. 批量操作代替循环单条操作

-- 不推荐：循环插入
INSERT INTO logs (message) VALUES ('log1');
INSERT INTO logs (message) VALUES ('log2');
INSERT INTO logs (message) VALUES ('log3');

-- 推荐：批量插入
INSERT INTO logs (message) VALUES ('log1'), ('log2'), ('log3');

7. 使用 JOIN 替代子查询

-- 子查询方式
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- JOIN 方式（通常性能更好）
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

分页优化

传统分页的问题

-- 当 offset 很大时，性能很差
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 数据库需要扫描前 100010 行，然后丢弃前 100000 行

优化方案

1. 使用索引列进行分页

-- 方式一：使用 WHERE 条件
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 方式二：使用子查询先获取 ID
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t
ON o.id = t.id;

2. 延迟关联

-- 先通过索引获取 ID，再关联查询详情
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    WHERE user_id = 1
    ORDER BY created_at DESC
    LIMIT 100000, 10
) t ON o.id = t.id;

大表优化

分区表

对于非常大的表，可以使用分区来提高查询性能。

-- 按日期分区
CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

分表策略

当单表数据量过大时，可以考虑分表：

1. 垂直分表：将不常用的列拆分到另一张表
2. 水平分表：按规则将数据分散到多张表

垂直分表示例：
users 表 → users_basic（id, name, email）+ users_detail（id, bio, avatar）

水平分表示例：
orders 表 → orders_2023, orders_2024, orders_2025

慢查询分析

开启慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

分析慢查询

-- 使用 mysqldumpslow 分析慢查询日志
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t: 按查询时间排序
-- -t 10: 显示前 10 条

使用 Performance Schema

-- 查看执行时间最长的 SQL
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 AS avg_time_ms,
    SUM_TIMER_WAIT/1000000000 AS total_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

优化检查清单

查询优化检查

• 使用 EXPLAIN 分析执行计划
• 避免全表扫描（type = ALL）
• 确保 WHERE 条件使用了索引
• 只选择需要的列
• 使用 LIMIT 限制结果
• 避免在索引列上使用函数
• 避免隐式类型转换
• 使用覆盖索引

索引优化检查

• 为频繁查询的列创建索引
• 复合索引遵循最左前缀原则
• 避免冗余索引
• 定期分析和优化索引
• 监控索引使用率

表结构优化检查

• 选择合适的数据类型
• 避免过多的列
• 合理使用 NULL 和 NOT NULL
• 考虑垂直拆分大表
• 考虑水平分表

小结

本章我们学习了：

1. EXPLAIN 分析：理解查询执行计划
2. 索引优化：索引使用原则和失效场景
3. 查询优化：选择列、LIMIT、JOIN 等技巧
4. 分页优化：大 offset 分页的优化方案
5. 大表优化：分区和分表策略
6. 慢查询分析：定位和解决性能问题

练习

1. 使用 EXPLAIN 分析一个复杂查询的执行计划
2. 找出并修复一个索引失效的问题
3. 优化一个慢查询
4. 设计一个覆盖索引优化查询
5. 实现一个高效的分页查询

参考资源

• MySQL 官方文档 - 优化
• MySQL 官方文档 - EXPLAIN
• MySQL 官方文档 - 索引