SQL优化技能实战：从基础查询到高性能调优

8次阅读

共计 2314 个字符，预计需要花费 6 分钟才能阅读完成。

上周我们系统突然出现大量 500 错误，监控显示订单查询接口平均响应时间从 200ms 暴涨到 8 秒。排查发现是下面这条 SQL 惹的祸：

SELECT * FROM orders 
WHERE user_id = 10086 
AND create_time > '2023-01-01'
ORDER BY total_price DESC;

这个查询看似简单，但在 200 万订单数据下：
– 没有为 user_id 和 create_time 建立联合索引
– 使用了 SELECT * 获取不需要的字段
– 排序操作导致大量临时文件生成

数据库索引就像书本的目录，常见的 B + 树索引结构有以下特点：

叶子节点存储实际数据（InnoDB）或指针（MyISAM）
非叶子节点只存储键值，可以容纳更多分支
所有叶子节点通过指针相连，适合范围查询

创建高效索引的原则：

最左前缀原则：联合索引 (a,b,c) 可以优化a=1、a=1 AND b=2，但无法优化b=2
区分度高：如手机号比性别更适合建索引
覆盖索引：索引包含查询所需字段时无需回表

使用 EXPLAIN 分析开头的问题 SQL：

EXPLAIN SELECT * FROM orders WHERE user_id = 10086;

关键字段说明：

type：ALL(全表扫描) → index(索引扫描) → range(范围扫描) → ref(非唯一索引) → eq_ref(唯一索引) → const(主键等值)
rows：预估扫描行数
Extra：
Using filesort：需要额外排序
Using temporary：使用临时表
Using index：覆盖索引

SELECT *：
获取不需要的字段增加 I / O 压力
破坏覆盖索引优化机会

滥用 OR 条件：

-- 优化前
SELECT * FROM products 
WHERE category = 'phone' OR price > 5000;

-- 优化后
SELECT * FROM products WHERE category = 'phone'
UNION ALL
SELECT * FROM products WHERE price > 5000 AND category != 'phone';

函数操作索引列：

-- 索引失效
SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';

-- 优化方案
SELECT * FROM users 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

场景：查询用户最近 3 个月待发货订单，包含商品信息

-- 原始 SQL（执行时间：1.2s）SELECT o.*, p.product_name 
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.user_id = 10086 
AND o.status = 'pending'
AND o.create_time > DATE_SUB(NOW(), INTERVAL 3 MONTH);

-- 优化方案
-- 1. 创建联合索引：(user_id, status, create_time)
-- 2. 只查询必要字段
-- 3. 使用 INNER JOIN 明确关联类型

-- 优化后 SQL（执行时间：28ms）SELECT o.order_no, o.create_time, p.product_name, p.price
FROM orders o FORCE INDEX(idx_user_status_time)
INNER JOIN products p ON o.product_id = p.id
WHERE o.user_id = 10086 
AND o.status = 'pending'
AND o.create_time > DATE_SUB(NOW(), INTERVAL 3 MONTH);

问题：传统 LIMIT OFFSET 在深度分页时性能急剧下降

-- 低效写法（OFFSET 100000 时需扫描 100010 行）SELECT * FROM orders 
ORDER BY create_time DESC
LIMIT 10 OFFSET 100000;

-- 优化方案：记住上次查询位置
SELECT * FROM orders
WHERE create_time < '2023-06-01 00:00:00'  -- 上一页最后一条记录的 create_time
ORDER BY create_time DESC
LIMIT 10;