Oracle-SQL-优化：NULL 空值影响 与 按照某字段取最大值优化

1. 核心前提：索引中 NULL 值的存储差异

MySQL 与 Oracle 对索引中 NULL 值的处理存在本质区别，这是后续优化差异的基础：

• MySQL：索引会保存 NULL 值记录。即使字段定义为NULL，单列索引仍会包含NULL值的索引条目。
• Oracle：不保存全为 NULL 的索引记录。若索引字段为NULL，该记录不会进入索引；若为联合索引，仅当所有字段均为NULL时不记录，部分字段为NULL时仍会保存。

直接影响：
对于SELECT * FROM t WHERE col IS NULL这类查询：

• MySQL 可直接使用col上的单列索引；
• Oracle 需创建(col, 0)等联合索引（通过非 NULL 常量填充），才能使查询利用索引。

2. 案例对比：GROUP BY + MAX查询的优化差异

以查询SELECT owner, MAX(object_id) FROM tbig GROUP BY owner（owner唯一值少）为例，对比两者的优化逻辑。

2.1. 表结构与索引

两张数据库中表结构一致：

-- 表结构
CREATE TABLE tbig (
  id INT NOT NULL PRIMARY KEY,
  owner VARCHAR(30) NULL,  -- 可NULL
  object_id INT NULL,      -- 可NULL
  create_date DATETIME NULL
);
-- 联合索引
CREATE INDEX idx_owner_object_id ON tbig(owner, object_id);

 

2.2. MySQL 的优化：loose index scan

loose index scan是 MySQL 针对GROUP BY+ 聚合函数（如MAX/MIN）的专项优化，核心是无需扫描所有索引记录，仅通过索引的有序性定位聚合结果，适用于：

• 索引前缀包含GROUP BY字段（如owner）；
• 聚合函数仅依赖索引后续字段（如object_id）。

优化表现：

• 当owner为NOT NULL时，MySQL 优化器可直接使用loose index scan，通过索引快速定位每个owner对应的MAX(object_id)，避免全表扫描或索引全扫描，explain analyze中会显示index_for_group_by标识。
• 即使owner改回NULL，仍可能使用loose index scan（用户疑问点），推测原因：
  • MySQL 优化器可能根据统计信息判断owner的NULL值占比极低，仍选择索引优化；
  • 执行计划缓存（若未重新解析 SQL）可能导致优化器沿用之前的高效计划。

2.3. Oracle 的优化限制

Oracle 虽有index skip scan（类似 MySQL 的松散扫描），但在该场景下表现受限：

• 当owner可为 NULL 时：由于 Oracle 索引不保存全 NULL 记录，优化器无法通过idx_owner_object_id高效定位owner分组，可能选择全表扫描。
• 当owner为NOT NULL或加WHERE owner IS NOT NULL时：可使用index fast full scan（快速全索引扫描），利用索引比表小的特性减少 I/O，但本质仍是扫描整个索引，效率低于 MySQL 的loose index scan。

补救方案：
借助 “小表驱动大表” 思想，结合标量子查询优化：

-- t_user为保存唯一owner的小表（因owner唯一值少）
SELECT username AS owner,
       (SELECT MAX(object_id) FROM tbig s WHERE s.owner = m.username) AS max_object_id
FROM t_user m
WHERE max_object_id IS NOT NULL;

 

原理：通过小表t_user的username遍历所有唯一owner，每个owner对应一次tbig的索引查询（利用idx_owner_object_id定位MAX(object_id)），避免全表 / 全索引扫描，效率接近 MySQL 的loose index scan。

3. 核心差异总结

维度	MySQL	Oracle
NULL 与索引	索引保存 NULL 值，支持IS NULL索引查询	不保存全 NULL 索引记录，需特殊索引
GROUP BY + MAX 优化	用loose index scan直接定位聚合结果	依赖index fast full scan（扫描全索引）或标量子查询改写
优化器灵活性	对NULL字段更宽容，易触发高效索引扫描	对NULL字段敏感，需人工改写 SQL 优化

4. 结论

在GROUP BY + 聚合函数且GROUP BY字段唯一值少的场景中：

• MySQL 的loose index scan展现了更优的优化器智能，即使字段可为 NULL，仍可能通过索引高效执行；
• Oracle 受限于 NULL 值在索引中的存储机制，优化器表现更保守，需通过表设计（如NOT NULL）或 SQL 改写（如标量子查询）弥补。

这一案例体现了 “新兴数据库在特定场景下优化器可能超越传统数据库” 的特点，也为跨库性能优化提供了对比参考。