SQL 优化实战——从慢查询到秒级响应

作者： CaoZH
日期： 2026-05-20
本文为原创教程

---

数据库性能优化是后端开发的核心技能之一。一条慢 SQL 可能拖垮整个系统。本文通过真实案例，系统性地介绍 SQL 优化的思路和技巧。

一、找到慢查询

开启慢查询日志

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

-- 开启慢查询日志（MySQL）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询
SET GLOBAL log_queries_not_using_indexes = ON;

使用 EXPLAIN

EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;

EXPLAIN 输出解读：

列	值	含义
type	ALL	全表扫描 ❌
type	ref / range	走索引 ✅
rows	50000	扫描行数
Extra	Using filesort	需要额外排序 ❌
Extra	Using index	索引覆盖 ✅
possible_keys	idx_status	可用的索引
key	NULL	实际没走索引 ❌

二、索引优化

最左前缀原则

-- 联合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 走索引 ✅
WHERE user_id = 1 AND status = 'paid';
WHERE user_id = 1;
WHERE user_id = 1 AND created_at > '2025-01-01';

-- 不走索引 ❌
WHERE status = 'paid';               -- 跳过了 user_id
WHERE created_at > '2025-01-01';     -- 跳过了前两列

索引设计要点

-- ✅ 高区分度字段放前面
CREATE INDEX idx_email ON users(email);  -- email 区分度极高

-- ✅ 覆盖索引（查询的字段都在索引中）
CREATE INDEX idx_user_status ON orders(user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 1;  -- Using index

-- ❌ 低区分度字段不适合单独索引
-- status 只有 'pending','paid','shipped' 三种值

三、常见慢查询案例

案例 1：分页查询越来越慢

-- ❌ 大偏移量分页
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 扫描 100020 行，只取 20 行

-- ✅ 基于游标的分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- ✅ 子查询优化
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;

案例 2：N+1 查询

// ❌ N+1：循环查数据库
List<Order> orders = orderMapper.selectList();
for (Order order : orders) {
    User user = userMapper.selectById(order.getUserId());  // N 次查询
    order.setUserName(user.getName());
}

-- ✅ JOIN 一次查出
SELECT o.*, u.name as user_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
LIMIT 20;

-- ✅ 批量查询
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 20);

案例 3：SELECT * 问题

-- ❌ 查询所有字段
SELECT * FROM users WHERE email = 'test@test.com';

-- ✅ 只查需要的字段
SELECT id, name, email FROM users WHERE email = 'test@test.com';

-- 如果所有查询字段都在索引中 → 覆盖索引，不回表查询

案例 4：函数导致索引失效

-- ❌ 对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2025-01-15';
-- 不走索引

-- ✅ 使用范围查询
SELECT * FROM orders 
WHERE created_at >= '2025-01-15 00:00:00' 
  AND created_at < '2025-01-16 00:00:00';
-- 走索引

四、表结构优化

字段类型优化

-- ❌ 能用更小的类型
CREATE TABLE users (
    id BIGINT,           -- 实际不会超过 1000 万用户
    status VARCHAR(10),  -- 只有 0 和 1
    created_at VARCHAR(20)  -- 应该用 DATETIME
);

-- ✅ 选择合适的类型
CREATE TABLE users (
    id INT UNSIGNED,         -- 最大 42 亿
    status TINYINT,          -- 0/1
    created_at DATETIME      -- 占用更少，支持日期函数
);

反范式优化

-- ❌ 每次查询都需要计算
SELECT o.*, 
    (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) as item_count,
    (SELECT SUM(price * quantity) FROM order_items WHERE order_id = o.id) as total
FROM orders o;

-- ✅ 冗余字段，写入时维护
ALTER TABLE orders ADD COLUMN item_count INT DEFAULT 0;
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2) DEFAULT 0.00;

-- 插入订单项时同步更新
INSERT INTO order_items (order_id, price, quantity) VALUES (1, 99.9, 2);
UPDATE orders SET 
    item_count = item_count + 1,
    total_amount = total_amount + 99.9 * 2
WHERE id = 1;

五、大表分页优化方案

-- 方案一：标签记录法（推荐）
SELECT * FROM orders 
WHERE id > #{lastId}     -- 记录上一页最后一条的 ID
ORDER BY id 
LIMIT 20;

-- 方案二：子查询优化
SELECT * FROM orders 
WHERE id >= (
    SELECT id FROM orders 
    ORDER BY id 
    LIMIT 100000, 1
)
ORDER BY id 
LIMIT 20;

-- 方案三：延迟关联
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY id 
    LIMIT 100000, 20
) tmp ON o.id = tmp.id;

六、监控与巡检

-- 查看当前正在运行的查询
SHOW FULL PROCESSLIST;

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看表大小
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'size_mb'
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY size_mb DESC;

-- 查看索引使用率
SELECT 
    index_name,
    cardinality,
    (cardinality / GREATEST(
        (SELECT COUNT(*) FROM information_schema.statistics 
         WHERE table_name = 'orders'), 1
    )) * 100 as selectivity
FROM information_schema.statistics 
WHERE table_name = 'orders';

七、总结

## SQL 优化检查清单

### 查询优化
□ 是否全表扫描？（EXPLAIN type != ALL）
□ 是否走了正确的索引？（EXPLAIN key）
□ 是否使用了文件排序？（Extra != Using filesort）
□ 是否使用了临时表？（Extra != Using temporary）
□ 是否 SELECT *？（只查需要的字段）
□ 是否对索引列使用了函数？

### 索引优化
□ 区分度高的字段在前
□ 避免冗余索引
□ 覆盖索引
□ 联合索引遵循最左前缀

### 架构优化
□ 读写分离
□ 分表分库
□ 引入缓存（Redis）
□ 定期分析慢查询

---

首发于 CaoZH 的笔记