一些SQL优化经验（非添加索引版）

SQL 优化核心策略

伪代码示例，现实比这个复杂

1. 子查询优化

(1) 避免低效的 `IN` 和 `NOT IN`

问题：
NOT IN 可能导致全表扫描，尤其是子查询结果集较大时。

优化方案：

替换为 LEFT JOIN：

-- 原查询（低效）
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 在找到第一条匹配后终止扫描，效率更高。

示例：

-- 低效（子查询结果集大时）
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 可能导致全表排序和去重，内存消耗大。

优化方案：

-- 低效
SELECT DISTINCT user_id, order_date FROM orders;-- 高效（若需要聚合）
SELECT user_id, order_date 
FROM orders 
GROUP BY user_id, order_date;

(2) 避免关联字段使用函数或操作符

问题：
关联字段的表达式（如 ||、CONCAT）会导致索引失效。

优化示例：

-- 低效
SELECT DISTINCT user_id, order_date FROM orders;-- 高效（若需要聚合）
SELECT user_id, order_date 
FROM orders 
GROUP BY user_id, order_date;

-- 高效（直接字段匹配）
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 负载。

具体策略

筛选阶段保持字段原生类型，转换放在最后

-- 先筛选，再转换
SELECT id, CAST(created_at AS DATE) AS create_date  -- 转换放在最后
FROM orders 
WHERE created_at >= '2023-01-01';           -- 用原生类型过滤

4. 表设计与维护

(1) 统一关联字段类型

问题：
字段类型不匹配（如 INT vs VARCHAR）会导致隐式转换和性能下降。
优化方案：
与上游协商统一字段类型

(2) 视图转结果表

场景：
高频查询的复杂视图（如报表接口）。
优化步骤：
1. 将视图转为结果表：
```
在查询结果表之前 用存储过程将结果写入结果表，然后再进行查询
```
2. 查询时直接查询结果表，提升查询效果

5. 内存与 IO 优化

(1) 合理使用临时表

内存临时表减少磁盘 IO，但需注意内存容量。

(2) 分页查询优化

避免 OFFSET 深度分页：
使用 WHERE 条件+游标方式（如基于时间或主键）。

-- 低效（OFFSET 100000）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;-- 高效（基于上次查询的末尾 ID）
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id 
LIMIT 10;