Oracle优化器 索引失效原因分析 Oracle执行计划不走索引的原因总结

Oracle优化器 | 索引失效原因分析：为什么我的SQL突然变慢了？

场景还原：
"明明昨天还跑得飞快的报表，今天突然卡成PPT？" 凌晨2点，DBA老王盯着屏幕上那条全表扫描的SQL欲哭无泪——索引就像突然"失忆"了一样，这种抓狂时刻，每个Oracle开发者都经历过，今天我们就来拆解那些让索引"罢工"的隐蔽陷阱。

数据分布失衡：优化器的"数学题"算错了

当你说"这字段明明有索引啊"，Oracle优化器可能正在心里嘀咕："但用索引更亏啊！"

典型场景：

• 状态字段status有95%的值都是'ACTIVE'，你查WHERE status='INACTIVE'时走索引，查活跃数据时反而全表扫描
• 索引列存在大量空值,而查询条件为IS NULL（注意：Oracle B树索引不存储NULL值）

怎么破：

-- 检查列数据分布（以EMPLOYEE表的DEPT_ID为例）
SELECT DEPT_ID, COUNT(*) CNT 
FROM EMPLOYEE 
GROUP BY DEPT_ID
ORDER BY CNT DESC;

优化方案：

• 对严重倾斜的字段考虑/*+ INDEX_SS */（索引跳跃扫描）提示
• 使用直方图统计信息：ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS FOR COLUMNS DEPT_ID SIZE 254

隐式类型转换：索引的"语言障碍"

当你在VARCHAR2字段上用WHERE user_id = 123（数字类型），Oracle会默默做类型转换——就像让两个说不同语言的人强行聊天。

危险写法：

-- 假设PHONE_NO是VARCHAR2类型
SELECT * FROM CUSTOMERS WHERE PHONE_NO = 13800138000;  -- 隐式转换

正确姿势：

SELECT * FROM CUSTOMERS WHERE PHONE_NO = '13800138000'; -- 类型匹配

检查方法：

-- 查看执行计划的Predicate Information部分
EXPLAIN PLAN FOR SELECT...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 如果看到"TO_NUMBER(PHONE_NO)"等转换函数，就是中招了

函数操作索引列：让索引"武功尽废"

在索引列上套函数,就像让短跑运动员穿着高跟鞋比赛——再强的索引也废了。

常见踩坑：

-- 假设CREATE_TIME是DATE类型且有索引
SELECT * FROM ORDERS 
WHERE TO_CHAR(CREATE_TIME,'YYYY-MM-DD') = '2025-08-20'; -- 全表扫描
-- 正确写法（利用日期范围）
SELECT * FROM ORDERS 
WHERE CREATE_TIME >= TO_DATE('2025-08-20','YYYY-MM-DD') 
AND CREATE_TIME < TO_DATE('2025-08-21','YYYY-MM-DD');

函数索引方案：

-- 如果必须用函数查询
CREATE INDEX IDX_ORDERS_CREATE_STR ON ORDERS(TO_CHAR(CREATE_TIME,'YYYY-MM-DD'));

组合索引的"左前缀原则"

组合索引就像电话号码的区号——不拨前几位，后面数字再完整也找不到人。

失效案例：

-- 组合索引(IDX_A_B_C)顺序为A,B,C
SELECT * FROM T1 WHERE B = 1 AND C = 2; -- 不走索引
SELECT * FROM T1 WHERE A = 1 AND C = 2; -- 仅用A列索引

黄金法则：

• 把高筛选性（区分度高）的列放左边
• 最常用查询条件要覆盖左前缀

统计信息过时：优化器的"老地图"

当表数据量暴涨10倍但统计信息还是三个月前的,优化器就像拿着旧地图找新修的高速公路。

诊断方法：

-- 检查统计信息最后收集时间
SELECT TABLE_NAME, LAST_ANALYZED 
FROM USER_TABLES 
WHERE TABLE_NAME = 'YOUR_TABLE';

解决方案：

-- 手动收集（注意业务低峰期执行）
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'YOUR_TABLE', CASCADE=>TRUE);

不该忽略的"边缘情况"

1. 索引被标记UNUSABLE：

   -- 重建失败索引
   ALTER INDEX IDX_EMP_NAME REBUILD;

2. 使用了或NOT IN：

   

   -- 优化NOT IN写法
   SELECT * FROM PRODUCTS WHERE PRODUCT_ID NOT IN (1,2,3);
   -- 可改为
   SELECT * FROM PRODUCTS 
   WHERE PRODUCT_ID >3 
   UNION ALL
   SELECT * FROM PRODUCTS WHERE PRODUCT_ID BETWEEN 1 AND 3 AND PRODUCT_ID NOT IN (1,2,3);

3. OR连接非索引列：

   -- 低效写法
   SELECT * FROM USERS WHERE USER_ID = 100 OR USER_NAME = '张三';
   -- 高效改写
   SELECT * FROM USERS WHERE USER_ID = 100
   UNION ALL
   SELECT * FROM USERS WHERE USER_NAME = '张三' AND USER_ID != 100;

终极排查路线图

当索引失效时,按这个顺序"破案"：

1. 检查执行计划确认是否真的没走索引
2. 验证SQL写法是否存在隐式转换/函数
3. 确认统计信息是否最新
4. 检查索引状态SELECT INDEX_NAME, STATUS FROM USER_INDEXES
5. 考虑使用/*+ INDEX */提示强制走索引测试性能

记住：索引不是银弹，有时候全表扫描反而是最优解——关键要看返回数据量占总数据的比例，当优化器决定使用全表扫描时，不妨先问问它："这次你又发现了什么我不知道的秘密？"

（注：本文基于Oracle 19c版本验证，部分特性在不同版本中可能存在差异）