SQL 优化核心策略
伪代码示例，现实比这个复杂
1. 子查询优化
(1) 避免低效的 IN 和 NOT IN
-
问题：
NOT IN可能导致全表扫描，尤其是子查询结果集较大时。 -
优化方案：
-
替换为
LEFT JOIN：sql">-- 原查询（低效） SELECT * FROM table_a WHERE id NOT IN (SELECT id FROM table_b);-- 优化后 SELECT a.* FROM table_a a LEFT JOIN table_b b ON a.id = b.id WHERE b.id IS NULL; -
适用场景：
子查询结果集较大，且关联字段有索引。
-
(2) 优先使用 EXISTS 而非 IN
-
优势：
EXISTS在找到第一条匹配后终止扫描，效率更高。 -
示例：
sql">-- 低效（子查询结果集大时） SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);-- 高效 SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
2. JOIN 优化
(1) 减少 DISTINCT，改用 GROUP BY
-
问题：
DISTINCT可能导致全表排序和去重，内存消耗大。 -
优化方案：
sql">-- 低效 SELECT DISTINCT user_id, order_date FROM orders;-- 高效（若需要聚合） SELECT user_id, order_date FROM orders GROUP BY user_id, order_date;
(2) 避免关联字段使用函数或操作符
-
问题：
关联字段的表达式（如||、CONCAT）会导致索引失效。 -
优化示例：
sql">-- 低效 SELECT DISTINCT user_id, order_date FROM orders;-- 高效（若需要聚合） SELECT user_id, order_date FROM orders GROUP BY user_id, order_date;sql">-- 高效（直接字段匹配） SELECT * FROM table_a a JOIN table_b b ON a.ticket_no = b.ticket_no AND a.ticket_serial = b.ticket_serial;
3. 数据操作优化
(1) 增删改宽表数据先创建临时表
把先写入后改的结果表的程序，改为一次性写入，从而避免update操作锁表
比如：
1.insert 结果表（大表）
2.update 结果表（大表）
改为：
insert 临时表
update 临时表
insert 结果表（大表）
复杂查询改为：
1.insert 临时表 1
2.insert 临时表 2
3.insert 结果表 from 临时表1 left join 临时表2
把update ,delete结果表（大表）的语句延后执行，减少锁表时间
比如：
1.update 或者 delete 结果表
2.许多待查询的临时表
3.insert 结果表
改为：
1.许多待查询的临时表
2.update 或者 delete 结果表
3.insert 宽表
(2) 类型转换优化策略
核心原则：先筛选数据，后执行类型转换
在 SQL 查询中，优先通过原始字段类型完成数据筛选，将类型转换操作推迟到最终结果处理阶段。此策略可显著减少需处理的数据量，提升性能。
优化优势
-
减少计算开销
-
仅对筛选后的结果进行类型转换，避免对全表数据的冗余处理。
-
示例：若从 100 万行数据中筛选出 1 万行，类型转换操作量减少 99%。
-
-
避免索引失效
-
在
WHERE或JOIN条件中对字段进行类型转换（如CAST(amount AS VARCHAR)）会导致索引失效，引发全表扫描。 -
优化后：直接基于原字段类型（如数值型
amount）筛选，确保索引生效。
-
-
降低内存与 IO 压力
-
大数据场景下，减少中间结果集的数据处理量，降低内存和磁盘 IO 负载。
-
具体策略
筛选阶段保持字段原生类型，转换放在最后
sql">-- 先筛选，再转换
SELECT id, CAST(created_at AS DATE) AS create_date -- 转换放在最后
FROM orders
WHERE created_at >= '2023-01-01'; -- 用原生类型过滤4. 表设计与维护
(1) 统一关联字段类型
-
问题：
字段类型不匹配（如INTvsVARCHAR）会导致隐式转换和性能下降。 -
优化方案：
与上游协商统一字段类型
(2) 视图转结果表
-
场景：
高频查询的复杂视图（如报表接口）。 -
优化步骤：
-
将视图转为结果表：
在查询结果表之前 用存储过程将结果写入结果表，然后再进行查询
-
查询时直接查询结果表，提升查询效果
-
5. 内存与 IO 优化
(1) 合理使用临时表
内存临时表减少磁盘 IO，但需注意内存容量。
(2) 分页查询优化
-
避免
OFFSET深度分页：
使用WHERE条件+游标方式（如基于时间或主键）。
sql">-- 低效（OFFSET 100000）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;-- 高效（基于上次查询的末尾 ID）
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 10;6. 定期维护统计信息
更新表的统计信息（如 ANALYZE table），帮助优化器生成高效计划。
