SQL查询优化进阶技巧

SQL查询优化进阶技巧

写出能运行的SQL很容易，但写出高效的SQL却需要深入理解数据库的执行机制。本文将分享一些SQL查询优化的进阶技巧，帮助你在实际项目中写出更高效的查询。

一、执行计划深度解读

-- EXPLAIN FORMAT=JSON 提供更详细的信息
EXPLAIN FORMAT=JSON
SELECT o.id, u.name, o.amount
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = "active" AND o.created_at > "2024-01-01";

-- 关注要点：
-- 1. access_type: 确保避免ALL（全表扫描）
-- 2. key: 确认使用了正确的索引
-- 3. rows: 预估扫描行数
-- 4. filtered: 过滤比例
-- 5. Extra: Using index（好）, Using filesort（需优化）, Using temporary（需优化）

二、索引使用技巧

-- 索引失效的常见场景

-- 1. 对索引列使用函数（失效）
WHERE YEAR(created_at) = 2024       -- 索引失效
WHERE created_at >= "2024-01-01"    -- 索引有效

-- 2. 隐式类型转换（失效）
WHERE varchar_col = 123             -- 索引失效
WHERE varchar_col = "123"           -- 索引有效

-- 3. LIKE左侧通配符（失效）
WHERE name LIKE "%张%"             -- 索引失效
WHERE name LIKE "张%"              -- 索引有效

-- 4. OR条件包含非索引列（失效）
WHERE indexed_col = 1 OR non_indexed_col = 2  -- 索引失效

-- 5. 不等于（部分失效）
WHERE status != "deleted"           -- 索引可能失效
-- 改写为IN
WHERE status IN ("active", "pending")

三、复杂查询优化

-- 分页优化 - 延迟关联
-- 慢查询
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 优化：先查ID再关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

-- EXISTS vs IN
-- 子查询结果集小时用IN
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);

-- 子查询结果集大时用EXISTS
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

-- 窗口函数替代自连接
-- 每个用户最近一笔订单
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
    FROM orders
) t WHERE rn = 1;

四、批量操作优化

-- 批量插入
-- 不推荐：循环单条插入
INSERT INTO users (name, email) VALUES ("a", "a@test.com");
INSERT INTO users (name, email) VALUES ("b", "b@test.com");

-- 推荐：批量插入
INSERT INTO users (name, email) VALUES
("a", "a@test.com"),
("b", "b@test.com"),
("c", "c@test.com");

-- 批量更新 - CASE WHEN
UPDATE products SET price = CASE
    WHEN id = 1 THEN 99.9
    WHEN id = 2 THEN 199.9
    WHEN id = 3 THEN 299.9
END
WHERE id IN (1, 2, 3);

-- 大数据量删除 - 分批删除
-- 避免长时间锁表
DELETE FROM logs WHERE created_at < "2023-01-01" LIMIT 1000;
-- 重复执行直到影响行数为0

SQL优化是一个持续的过程，需要结合具体的数据库版本、数据量和查询模式来综合考虑。建议在开发环境中使用真实数据进行测试，通过EXPLAIN分析执行计划，逐步优化查询性能。