往期回顾

前情提要：未结束的追查

在上一集的调查中，我们揭开了四起索引“失踪”或“失效”的悬案，揪出了前导%、MOVE命令、无索引外键和SHRINK后遗症这四位“嫌疑人”。然而，数据库世界的性能谜案远未告终。索引的“失踪”手法层出不穷，有些甚至伪装得天衣无缝，连经验丰富的DBA都可能被其迷惑。

今天，我们的侦查将继续。准备好你的放大镜，我们将深入另外五个更为诡谲的“案发现场”，继续我们的“寻踪之旅”。

悬案五：反向键索引的“范围恐惧症”

案情简介：

这是一个令人费解的案件。一个在等值查询（=）中表现完美的索引，在面对一个再简单不过的范围查询（BETWEEN或>=）时，却突然“罢工”，选择了全表扫描。它就像一个只能进行“点对点传送”，却无法进行“区域巡航”的导航仪。

现场勘查与证据收集：

1. 场景一：等值查询

--以下语句能用到索引。
select * from t where id=28;
执行计划
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    69 |   138K|   401   (0)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |    69 |   138K|   401   (0)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |   486 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

2. 场景二：范围查询

--不过奇怪的是，缘何下列语句却用不到索引，索引去哪儿了？
select * from t where id>=28 and id<=50;
执行计划
--------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   304 |   608K|  1709   (1)| 00:00:21 |
|*  1 |  TABLE ACCESS FULL| T    |   304 |   608K|  1709   (1)| 00:00:21 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6303  consistent gets
          0  physical reads
          0  redo size
       2263  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         23  rows processed

破案分析：

索引肯定没有失效，否则where id=28也用不到。范围查询最终也不过返回23条记录，为何就用不到索引了？我们来检查一下索引的“真实身份”：

select index_name,index_type from user_indexes where table_name='T';
INDEX_NAME                     INDEX_TYPE
------------------------------ -----------
IDX_T_ID                       NORMAL/REV

真相大白！罪魁祸首是NORMAL/REV，即反向键索引（Reverse Key Index）。

反向键索引会将索引列的值在存入索引前进行“字节反转”。例如，数值12345会被反转成54321再存储。这样做的好处是，对于连续增长的序列（如自增ID），可以避免所有新的索引条目都集中在索引的同一个“热点块”上，从而有效缓解高并发插入时的争用。

但它的“副作用”也显而易见：它彻底破坏了索引值的自然顺序。 28和29在数值上是相邻的，但它们反转后的值可能相差十万八千里。因此，Oracle无法利用它进行范围扫描。它失去了“范围”的概念，自然就患上了“范围恐惧症”。

侦探笔记：

反向键索引是解决高并发插入热点块争用的一把“利刃”，但它以牺牲范围扫描能力为代价。在决定使用它之前，请务必确认你的业务场景中，对该列的查询绝大多数都是等值查询。

在这里插入图片描述

悬案六：回收站里的“变形记”

案情简介：

这是一起经典的DBA“惊魂一刻”：有人不小心DROP了一张重要表。幸运的是，Oracle 10g后的回收站机制让他能通过FLASHBACK轻松找回。然而，当他庆幸不已时，却发现原本写在代码里的索引Hint（/*+index(...)*/）似乎“失灵”了，索引好像凭空消失了。

现场勘查与证据收集：

-- 准备环境
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
-- 索引正常时，Hint工作良好
select /*+index(t idx_object_id)*/ * from t where object_id=19;
执行计划
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
-- 模拟误操作与恢复
DROP TABLE t;
FLASHBACK TABLE t TO BEFORE DROP;
-- 检查原索引名，发现“失踪”
select status from user_indexes where index_name='IDX_OBJECT_ID';
未选定行

破案分析：

索引并没有真的“失踪”，它只是上演了一出“变形记”，换了一个我们不认识的名字。

-- 揭开索引的“新面纱”
select index_name,status from user_indexes where table_name='T';
INDEX_NAME                     STATUS
------------------------------ -------
BIN$9VPINfhiSnqqKgiI9qMgmw==$0 VALID

当你DROP一个表时，Oracle会将其及所有依赖对象（如索引、约束）一起放入回收站，并给它们重命名为一个以BIN$开头的系统生成名称，以避免与后续新建的对象冲突。当你FLASHBACK时，表名会恢复，但这些依赖对象的名字却保留了回收站中的“代号”。

有趣的是，虽然索引名变了，但Oracle的优化器足够智能，依然能识别出你Hint中旧的索引名，并正确地使用那个“变形”后的索引。

-- Hint依然有效，但执行计划中显示的是新索引名
select /*+index(t idx_object_id)*/ * from t where object_id=19;
执行计划
------------------------------------------------------------------------------------------
| Id |Operation                   |Name                          |Rows|Bytes |Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT            |                              |   1|  207 |    2   (0)|
|   1| TABLE ACCESS BY INDEX ROWID|T                             |   1|  207 |    2   (0)|
|*  2|  INDEX RANGE SCAN          |BIN$NyJOneU9S6K0fDw02M90Lg==$0|   1|      |    1   (0)|
------------------------------------------------------------------------------------------

侦探笔记：

从回收站恢复表后，索引和约束等对象会被重命名。虽然不影响功能，但为了维护的清晰和规范，最佳实践是手动将它们RENAME回原来的名字。例如：alter index "BIN$9VPINfhiSnqqKgiI9qMgmw==$0" rename to IDX_OBJECT_ID;

在这里插入图片描述

悬案七：回收站的“健忘症”——被遗忘的约束

案情简介：

这是上一个案件的“并发症”。管理员从回收站恢复了表，以为万事大吉，却没发现一个更致命的问题：表的约束关系丢失了，这直接导致了后续的数据错乱。

现场勘查与证据收集：

-- 准备环境，T_C上有外键约束FK_T_C
... (省略建表语句，与原文一致) ...
-- 检查初始约束状态
SELECT TABLE_NAME, CONSTRAINT_NAME, STATUS, CONSTRAINT_TYPE, R_CONSTRAINT_NAME
  FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_C';
TABLE_NAME  CONSTRAINT_NAME  STATUS   C R_CONSTRAINT_NAME
----------- ---------------- -------- - -----------------
T_C         FK_T_C           ENABLED  R T_P_ID_PK
-- 模拟误操作与恢复
DROP TABLE T_C ;
FLASHBACK TABLE T_C TO BEFORE  DROP;
-- 再次检查约束，发现空空如也
SELECT TABLE_NAME, CONSTRAINT_NAME, STATUS, CONSTRAINT_TYPE, R_CONSTRAINT_NAME
  FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_C';
未选定行

破案分析：

回收站患上了“健忘症”！与索引不同，非NOT NULL的约束（如主键、外键、唯一键、检查约束）在表被DROP进回收站时，其定义会被一并丢弃，FLASHBACK时并不会自动恢复。

这是一个非常隐蔽且危险的“坑”。因为业务逻辑往往严重依赖于这些约束来保证数据的完整性和一致性。约束的丢失，意味着数据保护网出现了一个大洞。

侦探笔记：

从回收站恢复表，绝非“一键搞定”那么简单。它是一个需要细致检查和手动修复的过程。恢复后的标准操作流程应包括：检查并重命名索引、检查并重新创建所有丢失的约束。

悬案八：时间查询的“函数陷阱”

案情简介：

这是开发人员代码中最常见的“通病”之一。一个看似正常的日期范围查询，却死活不用created列上的索引，导致了不必要的全表扫描。

现场勘查与证据收集：

1. 低效的写法：对索引列使用函数

-- 对列进行了运算(TRUNC)，所以用不到索引
select * from t where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD');
执行计划
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |  2484 |   296   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    12 |  2484 |   296   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))>=TO_DATE(' 2013-12-14
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              TRUNC(INTERNAL_FUNCTION("CREATED"))<=TO_DATE(' 2013-12-15 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

2. 高效的写法：保持索引列“纯净”

-- 改为对常量进行运算，立即用上索引
select * from t where created>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and created<TO_DATE('2013-12-15', 'YYYY-MM-DD')+1;
执行计划
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |   207 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |   207 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CREATED">=TO_DATE(' 2013-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "CREATED"<TO_DATE(' 2013-12-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

破案分析：

铁律：不要对索引列进行任何函数运算或计算！

当你写trunc(created)时，Oracle看到的是一个函数运算的结果，而不是created列本身。它无法利用created列上B树索引的有序性。这就好比你想在电话簿里找姓“张”的人，却跟管理员说：“请帮我找所有姓氏的第一个拼音字母是‘Z’的人。”管理员只能把每个人的姓都拿去算一遍拼音，而无法直接翻到“Z”部。

正确的做法是，保持索引列的“纯净”，将所有的计算和转换都移动到WHERE子句的另一侧，即常量值或绑定变量上。

侦探笔记：

WHERE f(indexed_column) = value 会导致索引失效。

WHERE indexed_column = g(value) 则可以正常使用索引。

这不仅仅是时间查询的通病，适用于所有类型的列。例如，WHERE object_id / 2 = :id 会全表扫描，而 WHERE object_id = :id * 2 则能走索引。这看似是小事，实则天壤之别。

全案总结：索引的“失踪者宣言”

至此，我们对索引失踪之谜的上下两集调查已全部完成。回顾所有案件，我们可以听到那些“失踪”的索引仿佛在向我们宣告：

“我依赖秩序，请不要用前导%或反向键打乱我的队列。”

“我的地址（ROWID）是我的生命，请不要用**MOVE轻易改变它，或在改变后忘了为我REBUILD**新家。”

“我是高并发的守护神，请务必在外键列上部署我，否则我只能用粗暴的表锁来保护数据。”

“我的健康需要关注，请不要让**大量DELETE和SHRINK让我变得臃肿不堪，记得定期为我REBUILD**瘦身。”

“请尊重我的本来面目，不要在我的身上施加任何函数或运算，把计算的活儿留给另一边。”

“我不是万能的，请在需要我之前，用虚拟索引这样的工具，先问问我是否真的能帮上忙。”

最终结论：

索引，是科学，更是艺术。它不是一个创建后便可高枕无忧的“银弹”，而是一个需要我们深入理解其习性、持续关注其状态、并根据业务场景巧妙利用的精密工具。

作为数据库的守护者，我们最重要的职责，不仅仅是创建索引，更是要成为一名合格的“索引侦探”。在性能下降的“案发现场”，能够凭借扎实的知识和敏锐的直觉，迅速定位问题，找到那个“失踪的索引”，并让它重新为我们高效地服务。这，才是我们与立于不败之地的核心价值所在。

全文完

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

论索引影响性能的一面⑤（全文完）：索引失踪之谜【下】

前情提要：未结束的追查

悬案五：反向键索引的“范围恐惧症”

1. 场景一：等值查询

2. 场景二：范围查询

悬案六：回收站里的“变形记”

悬案七：回收站的“健忘症”——被遗忘的约束

1. 低效的写法：对索引列使用函数

2. 高效的写法：保持索引列“纯净”

全案总结：索引的“失踪者宣言”

最终结论：

最新文章