查询优化

导学

索引解决"找得快"的问题，但 SQL 写法决定"找得对不对"。同样的业务需求，不同的 SQL 写法性能可能相差百倍。本节从 SQL 编写层面讲解查询优化技巧。

定义

查询优化：通过改写 SQL、调整查询策略、利用数据库特性，使查询以更少的资源消耗获得正确结果的过程。

查询优化技巧

技巧一：避免 SELECT *

-- 建表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    biography TEXT,
    INDEX idx_dept (dept)
);

INSERT INTO employees (emp_name, dept, score, biography) VALUES
('大翔', '技术部', 100, '热爱编程，喜欢开源技术'),
('白歌', '技术部', NULL, '专注算法研究');

当前数据状态：

emp_id	emp_name	dept	score	biography
1	大翔	技术部	100	热爱编程，喜欢开源技术
2	白歌	技术部	NULL	专注算法研究

示例：SELECT * vs 指定列

糟糕的写法：

EXPLAIN SELECT * FROM employees WHERE dept = '技术部';

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	idx_dept	idx_dept	63	const	2	Using index condition

结果解读：

SELECT * 需要返回所有列，包括 biography 等大字段
即使走了索引，也需要回表查询聚簇索引获取所有列的数据
浪费网络带宽和内存

优秀的写法：

EXPLAIN SELECT emp_id, emp_name, score FROM employees WHERE dept = '技术部';

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	idx_dept	idx_dept	63	const	2	Using index condition

结果解读：

只返回需要的列，减少数据传输量
虽然这个例子中仍然需要回表（因为 emp_id、emp_name、score 不在 idx_dept 中），但如果创建了覆盖索引，效果更明显

进一步优化（覆盖索引）：

CREATE INDEX idx_dept_name_score ON employees(dept, emp_name, score);

EXPLAIN SELECT emp_id, emp_name, score FROM employees WHERE dept = '技术部';

优化后的执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	idx_dept,idx_dept_name_score	idx_dept_name_score	63	const	2	Using index

结果解读：

Extra = Using index：覆盖索引，无需回表
SELECT * 无法利用覆盖索引（因为需要所有列），而指定列可以

技巧二：优化深度分页

-- 准备测试数据
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

-- 插入 10 条乐途员工数据
INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL),
('小崔', '技术部', 85),
('黄俪', '市场部', 90),
('李眉', '市场部', 88),
('孔蓝', '财务部', 92),
('赵鸣', '财务部', NULL),
('孙鹤', '技术部', 78),
('高英', '市场部', 95),
('杨英', '财务部', 80);

示例：深度分页问题

糟糕的写法：

-- 偏移量越大越慢，需要扫描 8 行然后丢弃
EXPLAIN SELECT * FROM employees ORDER BY emp_id LIMIT 5, 5;

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ALL	NULL	NULL	NULL	NULL	10	Using filesort

结果解读：

rows = 10：MySQL 需要扫描全部数据，排序后取出第 6-10 行
Using filesort：需要额外排序
当数据量达到百万级时，LIMIT 1000000, 10 会极其缓慢

优秀的写法：延迟关联

-- 先 LIMIT 取主键，再 JOIN 取完整行
EXPLAIN SELECT e.* FROM employees e
JOIN (
    SELECT emp_id FROM employees
    ORDER BY emp_id LIMIT 5, 5
) tmp ON e.emp_id = tmp.emp_id;

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5	NULL
1	PRIMARY	e	eq_ref	PRIMARY	PRIMARY	4	tmp.emp_id	1	NULL
2	DERIVED	employees	index	NULL	PRIMARY	4	NULL	10	Using index

结果解读：

子查询只扫描主键索引（Using index），取出 5 个 id
外层 JOIN 通过主键精确查找（eq_ref），只需 5 次查找
避免了扫描大量完整行数据

更优秀的写法：记录上一页最后 ID

-- 业务层记录上一页最后 ID = 5
-- 下一页查询：
EXPLAIN SELECT * FROM employees
WHERE emp_id > 5
ORDER BY emp_id LIMIT 5;

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	range	PRIMARY	PRIMARY	4	NULL	5	Using where

结果解读：

type = range：直接通过主键范围扫描
rows = 5：只需扫描 5 行
这是深度分页的最佳方案，但需要业务层配合记录上一页的最后 ID

技巧三：用 JOIN 替代子查询

-- 准备测试表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    score DECIMAL(5,2),
    INDEX idx_emp_id (emp_id)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

INSERT INTO scores (emp_id, score) VALUES
(1, 90),
(2, 85);

当前数据状态：

employees 表：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

scores 表：

score_id	emp_id	score
1	1	90.00
2	2	85.00

示例：子查询 vs JOIN

子查询（相关子查询，性能差）：

EXPLAIN SELECT emp_name FROM employees e
WHERE (SELECT AVG(score) FROM scores WHERE emp_id = e.emp_id) > 80;

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	e	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	scores	ref	idx_emp_id	idx_emp_id	5	func	1	NULL

结果解读：

DEPENDENT SUBQUERY：相关子查询，对外层表的每一行都执行一次子查询
如果 employees 表有 10 万行，子查询会执行 10 万次

JOIN 改写（性能更好）：

EXPLAIN SELECT e.emp_name FROM employees e
JOIN (
    SELECT emp_id, AVG(score) AS avg_score
    FROM scores GROUP BY emp_id HAVING avg_score > 80
) t ON e.emp_id = t.emp_id;

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	NULL
1	PRIMARY	e	eq_ref	PRIMARY	PRIMARY	4	t.emp_id	1	NULL
2	DERIVED	scores	index	idx_emp_id	idx_emp_id	5	NULL	2	Using index

结果解读：

子查询先执行一次，对 scores 表分组聚合（Using index）
外层 JOIN 通过主键精确匹配（eq_ref）
无论 employees 表多大，子查询只执行一次

查询结果：

emp_name
大翔
白歌

技巧四：批量操作替代循环

-- 准备测试表（使用 employees 表结构）
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

示例：循环插入 vs 批量插入

糟糕的写法（循环执行）：

-- 伪代码，实际应在应用程序中循环执行
-- for emp in ['小崔', '黄俪', '李眉']:
--     INSERT INTO employees (emp_name, dept, score) VALUES (emp, '技术部', 80);

结果解读：

循环执行多次 INSERT，每次都要进行网络往返、事务日志写入
性能极差

优秀的写法（批量插入）：

INSERT INTO employees (emp_name, dept, score) VALUES
    ('小崔', '技术部', 85),
    ('黄俪', '市场部', 90),
    ('李眉', '市场部', 88);

查询结果：

emp_id	emp_name	dept	score
3	小崔	技术部	85
4	黄俪	市场部	90
5	李眉	市场部	88

结果解读：

一条 SQL 插入 3 行，只需一次网络往返
批量提交的事务日志也更高效
建议每批 1000-5000 行，根据 max_allowed_packet 调整

技巧五：避免大事务

-- 准备测试表
CREATE TABLE huge_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status TINYINT DEFAULT 0,
    created_at DATETIME
);

-- 插入测试数据
INSERT INTO huge_table (status, created_at)
SELECT 0, DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY)
FROM (
    SELECT @row := @row + 1 AS seq
    FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t3,
         (SELECT @row := 0) t4
) numbers
WHERE seq <= 100;

示例：大事务 vs 分批提交

糟糕的写法：

-- 一个事务更新大量数据
START TRANSACTION;
UPDATE huge_table SET status = 1 WHERE created_at < '2024-01-01';
COMMIT;

结果解读：

如果表中数百万行符合条件，这个事务会持有大量行的锁
Undo Log 会膨胀，影响性能
主从复制延迟增大
如果中途失败，回滚成本也很高

优秀的写法：分批提交

-- 分批更新，每批 10 行
SET @batch_size = 10;
SET @affected_rows = 1;

WHILE @affected_rows > 0 DO
    START TRANSACTION;
    UPDATE huge_table SET status = 1
    WHERE status <> 1 AND created_at < '2024-01-01'
    LIMIT 10;
    SET @affected_rows = ROW_COUNT();
    COMMIT;
    SELECT SLEEP(0.1);  -- 给从库复制留时间
END WHILE;

结果解读：

每批只更新 10 行，锁持有时间短
分批提交减少 Undo Log 膨胀
SLEEP(0.1) 给主从复制留出缓冲时间
即使某一批失败，已提交的批次不会回滚，需要在应用层处理幂等性

技巧六：EXISTS 替代 IN（某些场景）

-- 使用 IN 子查询
EXPLAIN SELECT * FROM employees WHERE emp_id IN (
    SELECT emp_id FROM scores WHERE score > 80
);

执行计划（MySQL 5.7 优化器可能自动优化为半连接）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	scores	index	idx_emp_id	idx_emp_id	5	NULL	2	Using where; Using index; Start temporary
1	SIMPLE	employees	eq_ref	PRIMARY	PRIMARY	4	scores.emp_id	1	Using index condition; End temporary

使用 EXISTS 改写：

EXPLAIN SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM scores WHERE emp_id = e.emp_id AND score > 80
);

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	e	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	scores	ref	idx_emp_id	idx_emp_id	5	func	1	Using where

结果解读：

MySQL 5.7 优化器对 IN 和 EXISTS 都有优化，有时会自动转换
一般来说：子查询结果大、主查询结果小时 EXISTS 更好；子查询结果小、主查询结果大时 IN 更好
应以 EXPLAIN 和实际测试为准，不要迷信"EXISTS 一定比 IN 快"

SQL 示例

场景一：优化 COUNT 查询

-- 需要精确计数（必须扫描索引或表）
EXPLAIN SELECT COUNT(*) FROM huge_table WHERE status = 1;

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	huge_table	ALL	NULL	NULL	NULL	NULL	100	Using where

近似计数（快，但不精确）：

SHOW TABLE STATUS LIKE 'huge_table';

示例输出：

Name	Engine	Rows
huge_table	InnoDB	100

结果解读：

SHOW TABLE STATUS 的 Rows 是估算值，来自索引统计信息
对于 InnoDB，这个值可能不准确，但获取极快
适合不需要精确计数的场景（如分页显示"约 X 条"）

场景二：优化 GROUP BY

-- 确保 GROUP BY 的列有索引
CREATE INDEX idx_status ON huge_table(status);

-- 使用索引完成 GROUP BY
EXPLAIN SELECT status, COUNT(*) FROM huge_table GROUP BY status;

执行计划：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	huge_table	index	idx_status	idx_status	2	NULL	100	Using index

结果解读：

type = index：全索引扫描，但 Extra = Using index 表示覆盖索引
MySQL 可以直接使用索引完成 GROUP BY，无需临时表

常见误区

误区	正解
优化就是加索引	SQL 写法、查询策略、事务控制同样重要。
子查询总是慢的	MySQL 5.7 优化器会自动将某些子查询转为半连接。应 EXPLAIN 后判断。
事务越大越好	大事务持有锁时间长，增加死锁风险，影响复制延迟。应分批提交。

面试考点

Q：深度分页怎么优化？

延迟关联（先 LIMIT 取主键，再 JOIN 取完整行）；2. 业务层记录上一页最后 ID，用 WHERE id > last_id LIMIT N；3. 限制最大页码。

Q：大事务有什么危害？

锁持有时间长，阻塞其他事务；2. Undo Log 膨胀，影响性能；3. 主从复制延迟；4. 故障恢复时间长。应分批提交。

Q：EXISTS 一定比 IN 快吗？

不一定。MySQL 5.7 优化器对两者都有优化。一般来说：子查询结果大、主查询结果小时 EXISTS 更好；子查询结果小、主查询结果大时 IN 更好。应以 EXPLAIN 和实际测试为准。

小结

查询优化包括索引优化和 SQL 写法优化
避免 SELECT *、优化分页、用 JOIN 替代子查询、批量操作
控制事务大小，避免大事务带来的锁和复制问题
优化是系统工程，需要 EXPLAIN、慢查询日志和实际测试验证

下一章引子：查询优化让单条 SQL 更快，而事务则让多条 SQL 组合在一起具有原子性和一致性——这是数据库最核心的特性之一。