编写执行效率更高的SQL语句

DB2 查询优化实践

本文基于 IBM DB2 的查询优化机制，和Oracle、SQL SERVER我认为差不多，从执行计划和 I/O 成本角度，对常见 SQL 写法进行分析与优化说明。

注：示例表结构已简化，仅用于说明优化原理。

---

1. 避免 全字段检索

问题本质

SELECT * 
FROM company
WHERE product_name LIKE 'itemName%'
ORDER BY price_amount;

该写法的问题不只是“字段多”，而是：

执行计划层面

• 优化器可能选择：
  • Index Scan + Key Lookup（回表）
• 即：
  1. 先扫描索引定位行（Index I/O）
  2. 再根据 RID 回表取完整行（Table I/O）

I/O 成本分析

操作	成本
Index Scan	顺序 / 半顺序 I/O
Key Lookup	随机 I/O（最昂贵）

当列很多时：

• 每一行都需要 额外一次随机 I/O
• Buffer Pool 命中率下降
• CPU 参与数据拼装（Tuple Reconstruction）

---

优化写法（覆盖索引 / Index-Only Scan）

SELECT product_name, product_price, product_level, product_supplier
FROM company
WHERE product_name LIKE 'itemName%'
ORDER BY price_amount;

执行计划变化

• Index-Only Scan（覆盖索引）
• 无需回表（No Table Fetch）

优势

• 仅访问索引页（Index Pages）
• 避免随机 I/O
• 减少 Buffer Pool 压力
• 提升响应时间

---

2. 避免在索引列上使用函数或表达式

WHERE UPPER(product_name) = 'ABC'

执行计划影响

• 索引失效（Non-SARGable Predicate）
• 优化器无法使用 B+Tree 查找
• 退化为：
  • Table Scan / Index Scan + Filter

本质原因

函数破坏了：

• 索引的有序性（Order Preservation）
• 谓词可下推性（Predicate Pushdown）

优化建议

WHERE product_name = 'abc'

或：

• 使用函数索引（Function-Based Index）（若 DB2 支持）

---

3. 避免隐式转换

WHERE product_price > '50'

问题

• 字符串 → 数值的隐式转换
• 可能发生在：
  • 列上（最差情况）
  • 常量上（较好）

执行计划影响

• 索引失效
• 额外 CPU 计算成本
• Predicate无法下推到索引层

正确写法

WHERE product_price > 50

---

4. 谓词选择

优化器核心逻辑

优化器基于：

• 统计信息
• 谓词选择性）

选择最优访问路径：

• Index Scan
• Table Scan
• Index-Only Scan

---

常见谓词对比

谓词	是否走索引	说明
=	是	高选择性，最优
IN	是	等价多个 =
LIKE 'abc%'	是	前缀匹配
LIKE '%abc'	否	无法使用索引
<> / !=	否	低选择性
IS NULL	视情况	依赖索引设计

---

5. 排序优化（ORDER BY）与执行计划

排序（SORT）是高成本操作，尤其在大数据量下：

触发排序的情况

• 索引不覆盖排序列
• 排序列顺序与索引不一致
• 多表 Join 后排序

执行计划表现

• SORT 操作符出现
• 使用临时表空间（TEMP TABLESPACE）
• 可能发生磁盘溢出（Sort Spill）

优化策略

• 建立符合 ORDER BY 顺序的索引
• 使用覆盖索引避免排序
• 减少排序列数量

---

6. LIMIT / FETCH 与优化器策略

FETCH FIRST 10000 ROWS ONLY

问题

• 仅限制返回结果
• 不影响优化器访问路径

---

优化器提示

OPTIMIZE FOR 10000 ROWS

作用

• 告诉优化器：
  • 优先优化“前 N 条”返回速度

执行计划变化

• 更倾向：
  • Index Scan
  • Nested Loop
• 避免：
  • 全表扫描 + 排序

---

7. 总结

影响性能的关键因素

SQL 优化的本质：减少随机 I/O + 提高索引利用率 + 避免不必要的数据访问

1. 是否回表（Table Fetch）
2. 是否发生随机 I/O
3. 是否触发排序（SORT）
4. 谓词是否可下推（SARGable）
5. 索引是否覆盖（Covering Index）