一些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 查询中，优先通过原始字段类型完成数据筛选，将类型转换操作推迟到最终结果处理阶段。此策略可显著减少需处理的数据量，提升性能。

优化优势

1. 减少计算开销

   • 仅对筛选后的结果进行类型转换，避免对全表数据的冗余处理。

   • 示例：若从 100 万行数据中筛选出 1 万行，类型转换操作量减少 99%。

2. 避免索引失效

   • 在 WHERE 或 JOIN 条件中对字段进行类型转换（如 CAST(amount AS VARCHAR)）会导致索引失效，引发全表扫描。

   • 优化后：直接基于原字段类型（如数值型 amount）筛选，确保索引生效。

3. 降低内存与 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;

6. 定期维护统计信息

更新表的统计信息（如 ANALYZE table），帮助优化器生成高效计划。