跳到主要内容

SQL 优化

为什么优化很重要​

查询优化对应用性能至关重要：

  • 用户体验：慢查询 = 慢页面加载
  • 系统负载：未优化的查询消耗过多 CPU、I/O、内存
  • 可扩展性：糟糕的查询限制扩展能力
  • 成本：低效查询需要更多硬件

实际影响：

  • 一个未优化的查询可能拖垮整个应用
  • 添加索引可以将性能提升 1000 倍
  • 合理的 Schema 设计避免后期昂贵的重构

示例：

-- 优化前：对 1000 万行全表扫描（10 秒）
SELECT * FROM orders WHERE user_id = 123;

-- 优化后：索引查找（10 毫秒）
CREATE INDEX idx_user_id ON orders(user_id);
SELECT * FROM orders WHERE user_id = 123;  -- 快 1000 倍

EXPLAIN 分析​

什么是 EXPLAIN？​

EXPLAIN 显示 MySQL 如何执行查询，揭示：

  • 访问类型：全表扫描 vs 索引查找
  • 索引使用：使用了哪个索引
  • 连接顺序：表如何连接
  • 扫描行数：读取了多少行

基本用法​

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders| ref  | idx_user_id   | idx_u   | 5       | const |  100 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

关键列说明​

select_type​

查询类型：

类型描述示例
SIMPLE简单 SELECT，无子查询/UNIONSELECT * FROM users
PRIMARY最外层 SELECT（子查询中）SELECT * FROM (SELECT ...)
SUBQUERY子查询中的第一个 SELECTWHERE id IN (SELECT ...)
DERIVED派生表（FROM 中的子查询）FROM (SELECT ...)
UNIONUNION 中第二个及之后的 SELECTSELECT a UNION SELECT b

示例：

EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- select_type: PRIMARY（外层查询），SUBQUERY（子查询）

type（访问类型）​

最重要的列：从优到差

类型描述示例
system表中只有 1 行SELECT * FROM config WHERE id = 1
const主键或唯一索引查找（1 行）WHERE id = 123
eq_ref唯一索引扫描（JOIN）JOIN users ON orders.user_id = users.id
ref非唯一索引查找WHERE user_id = 123
range索引范围扫描WHERE id > 100 AND id < 200
index全索引扫描SELECT COUNT(*) FROM orders
ALL全表扫描 ❌WHERE name = 'Alice'（无索引）

目标：避免 type=ALL（全表扫描）。追求 ref、range 或更好。

示例：

-- 好：ref（索引查找）
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- type: ref

-- 差：ALL（全表扫描）
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2024-02-14';
-- type: ALL（对列使用函数破坏索引）

key​

实际使用的索引（从 possible_keys 中选择）

EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- key: idx_user_id（使用 user_id 上的索引）

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- key: idx_user_status（使用组合索引，而非仅 user_id）

rows​

预估扫描行数（不精确，但对比较有用）

-- 添加索引前
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- rows: 1000000（预估）

-- 添加索引后
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- rows: 100（好得多）

Extra​

额外信息：

值含义好/差
Using index覆盖索引（无需表查找）✅ 最佳
Using whereWHERE 子句过滤✅ 正常
Using index condition索引条件下推优化✅ 好
Using filesort额外排序（ORDER BY 未使用索引）❌ 差
Using temporary临时表用于 GROUP BY / DISTINCT❌ 差
Using join buffer连接缓冲（连接列无索引）⚠️ 警告

示例：

-- 好：覆盖索引
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 123;
-- Extra: Using index

-- 差：Filesort
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;
-- Extra: Using filesort（created_at 上无索引）

EXPLAIN 示例​

好的查询（索引查找）​

CREATE INDEX idx_user_status ON orders(user_id, status);

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

+----+-------+-------+------+------------------+---------+-------+------+-------------+
| id | type  | table | type | key              | ref     | rows  | Extra |
+----+-------+-------+------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE| orders| ref  | idx_user_status  | const   |  100  | Using index |
+----+-------+-------+------+------------------+---------+-------+------+-------------+

分析：✅ 使用索引，ref 类型，覆盖索引

差的查询（全表扫描）​

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;

+----+-------+-------+------+------+---------+------+-------------+
| id | type  | table | type | key  | rows    | Extra |
+----+-------+-------+------+------+---------+------+-------------+
|  1 | SIMPLE| orders| ALL  | NULL | 1000000 | Using where |
+----+-------+-------+------+------+---------+------+-------------+

分析：❌ 全表扫描（type=ALL），未使用索引

修复：重写查询

SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

优化技巧​

1. 避免全表扫描​

问题：查询读取整个表而非使用索引

-- ❌ 对列使用函数（索引失效）
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- EXPLAIN: type=ALL

-- ✅ 重写：使用函数索引或存储小写值
ALTER TABLE users ADD COLUMN email_lower VARCHAR(255);
UPDATE users SET email_lower = LOWER(email);
CREATE INDEX idx_email_lower ON users(email_lower);
SELECT * FROM users WHERE email_lower = 'test@example.com';

-- ❌ 类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone 是 VARCHAR
-- EXPLAIN: type=ALL（隐式转换）

-- ✅ 使用字符串字面量
SELECT * FROM users WHERE phone = '13800138000';

2. 优化 JOIN​

问题：低效的连接顺序，连接列缺少索引

-- ❌ 连接列无索引
SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'pending';
-- EXPLAIN: users 表 type=ALL

-- ✅ 在连接列添加索引
CREATE INDEX idx_user_id ON orders(user_id);  -- 已存在（外键）
-- JOIN 现在使用索引（eq_ref 类型）

-- ❌ 连接顺序（MySQL 优化器通常处理此问题）
SELECT * FROM small_table s JOIN large_table l ON s.id = l.small_id;
-- 如果优化器选择错误顺序，使用 STRAIGHT_JOIN：
SELECT * FROM small_table s STRAIGHT_JOIN large_table l ON s.id = l.small_id;
-- 强制 small_table 在前（左表）

3. 深度分页优化​

问题：LIMIT offset, count 需要扫描 offset + count 行

-- ❌ 慢：扫描 1,000,010 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- EXPLAIN: rows=1000010

-- ✅ 方案 1：延迟关联（子查询只查主键）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) tmp ON o.id = tmp.id;
-- EXPLAIN: rows=10（子查询只扫描索引）

-- ✅ 方案 2：记住上次 ID（需要顺序访问）
SELECT * FROM orders WHERE id > last_seen_id ORDER BY id LIMIT 10;
-- 第一页：SELECT * FROM orders ORDER BY id LIMIT 10;
-- 第二页：SELECT * FROM orders WHERE id > 10 ORDER BY id LIMIT 10;
-- 第三页：SELECT * FROM orders WHERE id > 20 ORDER BY id LIMIT 10;

-- 可视化
-- 差：扫描 [0, 1, 2, ..., 999999, 1000000, ..., 1000009]
-- 好：扫描 [1000000, ..., 1000009]（直接定位到偏移量）

性能对比：

  • LIMIT 1000000, 10：约 10 秒（扫描 100 万行）
  • 延迟关联：约 0.1 秒（通过索引扫描 10 行）

4. 子查询 vs JOIN​

问题：关联子查询每行执行一次

-- ❌ 子查询（每行执行一次）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'pending');
-- 如果有 1000 个用户，子查询执行 1000 次

-- ✅ JOIN（优化器可以更好地优化）
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'pending';
-- 单次查询执行计划

-- ✅ 替代方案：EXISTS（有适当索引时通常更快）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'pending'
);

5. COUNT 优化​

问题：COUNT(*) 在大表上很慢（需要全扫描）

-- ❌ 慢：全表扫描或索引扫描
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- EXPLAIN: rows=1000000（扫描整个表/索引）

-- ✅ 方案 1：维护计数器表
CREATE TABLE counters (
    name VARCHAR(50) PRIMARY KEY,
    value INT NOT NULL
);

INSERT INTO counters (name, value) VALUES ('pending_orders', 0);

-- INSERT/UPDATE/DELETE 时更新计数器
INSERT INTO orders (user_id, status) VALUES (123, 'pending');
UPDATE counters SET value = value + 1 WHERE name = 'pending_orders';

-- 快速读取
SELECT value FROM counters WHERE name = 'pending_orders';

-- ✅ 方案 2：近似计数（表统计信息）
SELECT TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'orders';
-- 不精确，但很快（基于统计估算）

-- ✅ 方案 3：覆盖索引（如果 WHERE 在索引列上）
CREATE INDEX idx_status ON orders(status);
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- EXPLAIN: Extra='Using index'（只扫描索引）

6. 避免 Filesort​

问题：ORDER BY 需要额外排序（未使用索引）

-- ❌ Filesort：ORDER BY 未使用索引
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;
-- EXPLAIN: Extra='Using filesort'

-- ✅ 添加覆盖索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- EXPLAIN: type=ref, Extra=''（无 filesort，索引已排序）

-- ❌ ORDER BY DESC 未使用索引（MySQL 8.0 之前）
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;
-- MySQL 8.0+：支持 DESC 索引
CREATE INDEX idx_user_created_desc ON orders(user_id, created_at DESC);

7. 优化 GROUP BY​

问题：GROUP BY 使用临时表

-- ❌ 临时表
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- EXPLAIN: Extra='Using temporary'

-- ✅ 在 GROUP BY 列添加索引
CREATE INDEX idx_user_id ON orders(user_id);
-- EXPLAIN: Extra='Using index'（无临时表）

Schema 设计​

数据类型​

原则：使用足够的最小类型

-- ❌ 过大：小数字用 BIGINT
CREATE TABLE users (
    id BIGINT PRIMARY KEY,  -- 8 字节，< 20 亿用户不需要
    age INT                 -- 4 字节，年龄 < 255
);

-- ✅ 合适的类型
CREATE TABLE users (
    id INT PRIMARY KEY,                 -- 4 字节，最多 20 亿用户
    age TINYINT UNSIGNED,               -- 1 字节，0-255
    status ENUM('pending', 'paid', 'shipped'),  -- 1 字节，而非 VARCHAR
    created_at TIMESTAMP                -- 4 字节，而非 DATETIME（8 字节）
);

对比：

类型存储范围
TINYINT1 字节-128 到 127
SMALLINT2 字节-32768 到 32767
INT4 字节-20 亿到 20 亿
BIGINT8 字节-9E18 到 9E18
VARCHAR(255)可变0-255 字符 + 1-2 字节长度
CHAR(10)固定10 字符（填充）
DATE3 字节'1000-01-01' 到 '9999-12-31'
DATETIME8 字节'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP4 字节'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC

最佳实践：

  • 尽可能用 INT 而非 BIGINT（每行省 4 字节）
  • 可变长度数据用 VARCHAR 而非 CHAR
  • 需要时区感知的时间戳用 TIMESTAMP 而非 DATETIME
  • 固定字符串集合用 ENUM

范式化 vs 反范式化​

范式化（3NF）：消除冗余，确保数据完整性

-- 范式化 Schema
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    total DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 优点：数据完整性，无冗余
-- 缺点：获取订单的用户名需要 JOIN

反范式化：为读性能复制数据

-- 反范式化 Schema
CREATE TABLE orders_with_user (
    id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(100),   -- 反范式化：用户名存在订单中
    user_email VARCHAR(100),  -- 反范式化：邮箱存在订单中
    total DECIMAL(10, 2)
);

-- 优点：读快（无需 JOIN）
-- 缺点：数据冗余（用户名在多个订单中重复），更新异常

何时反范式化：

  • 读密集工作负载（分析、报表）
  • 频繁访问的数据（用户资料与帖子）
  • 性能关键的查询

权衡：

  • 范式化：更好的写性能，数据完整性
  • 反范式化：更好的读性能，数据冗余

分区​

定义：将大表拆分为更小、更易管理的片段

优势：

  • 更快的查询（只查相关分区）
  • 更易维护（删除分区而非 DELETE）
  • 并行 I/O（并行扫描多个分区）

范围分区（按日期）：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    created_at DATE,
    total DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查询使用分区裁剪
SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- 只扫描 p2024 分区，而非整个表

-- 删除旧数据（快速）
ALTER TABLE orders DROP PARTITION p2023;

哈希分区：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) PARTITION BY HASH(id) PARTITIONS 4;

常见优化陷阱​

1. 过度索引​

问题：过多索引拖慢写入

-- ❌ 写密集表上太多索引
CREATE TABLE logs (
    id INT PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    created_at TIMESTAMP,
    INDEX idx_user_id (user_id),        -- 索引 1
    INDEX idx_action (action),          -- 索引 2
    INDEX idx_created_at (created_at),  -- 索引 3
    INDEX idx_user_action (user_id, action),  -- 索引 4（冗余）
    INDEX idx_user_created (user_id, created_at)  -- 索引 5（冗余）
);

-- 每次 INSERT/UPDATE/DELETE 都要更新 5 个索引
-- 更好：只保留频繁使用的索引

原则：为频繁运行或性能关键的查询创建索引。删除未使用的索引。

2. 未使用覆盖索引​

-- ❌ 查询：SELECT user_id, status, created_at FROM orders WHERE user_id = 123
-- 索引：(user_id)
-- EXPLAIN: type=ref, Extra=''（需要表查找）

-- ✅ 添加覆盖索引
CREATE INDEX idx_covering ON orders(user_id, status, created_at);
-- EXPLAIN: type=ref, Extra='Using index'（无需表查找）

3. SELECT *​

-- ❌ SELECT *（获取所有列）
SELECT * FROM orders WHERE user_id = 123;
-- 读取不需要的列，更多 I/O

-- ✅ 只选择需要的列
SELECT id, status, total FROM orders WHERE user_id = 123;
-- 如果有覆盖索引，可以完全避免表查找

面试题​

Q1：如何分析慢查询？​

答案：

  1. 启用慢查询日志（slow_query_log = 1，long_query_time = 1）
  2. 对查询运行 EXPLAIN 查看执行计划
  3. 检查 type 列（避免 ALL）、key（索引使用）、Extra（filesort、temporary）
  4. 检查 rows 列（预估扫描行数）
  5. 添加缺失索引、重写查询、优化 Schema

Q2：EXPLAIN 中 ref 和 eq_ref 有什么区别？​

答案：

  • ref：非唯一索引查找（可能匹配多行）
  • eq_ref：唯一索引查找（每次连接只有一行，通常用于 JOIN 中的主键或唯一索引）

Q3：如何优化深度分页？​

答案：

  • 延迟关联：子查询只查主键，然后 JOIN 获取完整行
  • 记住上次 ID：WHERE id > last_seen_id ORDER BY id LIMIT 10
  • Seek 方法：使用覆盖索引定位到偏移量

Q4：为什么 WHERE LOWER(name) = 'alice' 不使用索引？​

答案：对列使用函数阻止索引使用，因为索引存储原始值而非计算结果。改写为：

  • 存储小写列并索引：WHERE name_lower = 'alice'
  • 使用函数索引（MySQL 8.0+）：CREATE INDEX idx_lower_name ON users((LOWER(name)))

Q5：何时应该反范式化 Schema？​

答案：

  • 读密集工作负载（分析、报表）
  • 频繁访问的数据（用户资料与帖子）
  • 性能关键查询中 JOIN 太慢
  • 权衡：更好的读性能 vs 数据冗余和更新异常

Q6：Using index 和 Using index condition 有什么区别？​

答案：

  • Using index：覆盖索引（所有列来自索引，无需表查找）
  • Using index condition：索引条件下推优化（使用索引过滤行，但仍需表查找获取某些列）

Q7：如何优化 COUNT(*) 查询？​

答案：

  • 维护计数器表（INSERT/DELETE 时更新）
  • 使用 information_schema.TABLES 的近似计数
  • 如果 WHERE 在索引列上，使用覆盖索引
  • 对于大表的精确计数，考虑定时聚合

延伸阅读​