論索引影響性能的一面④ 索引失蹤之謎【上】

梁敬彬梁敬弘兄弟出品

往期回顧
論索引影響性能的一面①索引的各種開銷
論索引影響性能的一面②索引的使用失效
論索引影響性能的一面③ 索引開銷與經典案例

開篇：DBA的深夜“尋人啟事”

作為數據庫的守護者，我們最信賴的伙伴莫過于“索引”。它如同一位效率超群的圖書管理員，能于浩如煙海的數據中，精準地為我們取出所需的那一頁。但你是否也曾遇到過這樣的“靈異事件”：明明為它精心創建了索引，性能卻依舊慘不忍睹？執行計劃里，那個熟悉的身影消失不見，取而代之的是令人絕望的“全表掃描”（TABLE ACCESS FULL）。

索引失蹤了，到底去哪兒了？

這并非靈異故事，而是一系列潛藏在日常操作中的“性能懸案”。今天，我們將化身偵探，深入四個經典的“案發現場”，揭開索引失蹤的秘密。

在這里插入圖片描述

懸案一：like與 %間一波三折的故事

案情簡介：
江湖傳言，“LIKE一出，索引必廢”。這個說法流傳甚廣，讓許多開發者談%色變。但這究竟是真相，還是誤解？讓我們用證據說話。

現場勘查與證據收集：
首先，我們準備“案發現場”——一張T表，并在OBJECT_NAME列上建立索引。

drop table t purge;
create table t as select * from dba_objects where object_id is not null;
set autotrace off
update t set object_id=rownum;
update t set object_name='AAALJB' where object_id=8;
update t set object_name='LJBAAA' where object_id=10;
commit;
create index idx_object_name on t(object_name);
SET AUTOTRACE ON
SET LINESIZE 1000

1. 場景一：前綴匹配查詢 (‘LJB%’)

select object_name,object_id from t where object_name like 'LJB%';OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------
LJBAAA 10
LJB_TMP_SESSION 72521
LJB_TMP_SESSION 72910
LJB_TMP_TRANSACTION 72522
LJB_TMP_TRANSACTION 72911
已選擇5行。執行計劃
--------------------------------------------------------------------------------------
| Id |Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
| 0 |SELECT STATEMENT | | 5| 395 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5| 395 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_NAME | 5| | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
統計信息
----------------------------------------------------------0  recursive calls0  db block gets9  consistent gets0  physical reads0  redo size602  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)5  rows processed

破案分析：
索引健在，且表現優異！ INDEX RANGE SCAN（索引范圍掃描）赫然在列。like 'LJB%'這種前綴匹配，Oracle可以精準定位到索引樹中“LJB”的位置開始掃描，當然能用到索引。

2. 場景二：模糊匹配查詢 (‘%LJB%’)

select object_name,object_id from t where object_name like '%LJB%';執行計劃
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 12 | 948 | 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 948 | 292 (1)| 00:00:04 |
--------------------------------------------------------------------------------------統計信息
----------------------------------------------------------0  recursive calls0  db block gets1049  consistent gets0  physical reads0  redo size653  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)6  rows processed

破案分析：
索引“人間蒸發”！ TABLE ACCESS FULL出現了。因為前導%的存在，Oracle不知道檢索何時能停下來，只能放棄索引，選擇全表掃描。

3. 場景三：后綴匹配的“曲線救國” (‘%LJB’)

正常情況下，后綴匹配與模糊匹配原理相似，無法使用常規索引。但我們可以通過reverse函數和函數索引，巧妙破局。

create index idx_reverse_objname on t(reverse(object_name));
set autotrace on
select object_name,object_id from t where reverse(object_name) like reverse('%LJB');OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------
AAALJB 8執行計劃
--------------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes |Cost (%CPU)| Time |
| 0|SELECT STATEMENT | | 3596| 509K| 290 (0)| 00:00:04 |
| 1| TABLE ACCESS BY INDEX ROWID|T | 3596| 509K| 290 (0)| 00:00:04 |
|* 2| INDEX RANGE SCAN |IDX_REVERSE_OBJNAME| 647| | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
統計信息
----------------------------------------------------------0  recursive calls0  db block gets5  consistent gets0  physical reads0  redo size496  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1  rows processed

在這里插入圖片描述

懸案二：move命令引發的“血案”

案情簡介：
這是一起發生在某大型制造業系統的真實“血案”。一個看似無害的ALTER TABLE … MOVE操作，竟導致系統核心查詢性能驟降，業務近乎癱瘓。

現場勘查與證據收集：

drop table t_p cascade constraints purge;
drop table t_c cascade constraints purge;
CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);
CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);
INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;
INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;
COMMIT;
CREATE INDEX IND_T_C_FID ON T_C (FID);-- 檢查索引初始狀態
SELECT TABLE_NAME,INDEX_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND_T_C_FID';
TABLE_NAME           INDEX_NAME           STATUS
-------------------- -------------------- --------
T_C                  IND_T_C_FID          VALID-- 執行一個“不小心”的操作
ALTER TABLE T_C MOVE;-- 再次檢查索引狀態
SELECT TABLE_NAME,INDEX_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND_T_C_FID';
TABLE_NAME           INDEX_NAME           STATUS
-------------------- -------------------- --------
T_C                  IND_T_C_FID          UNUSABLE

破案分析：
兇手正是MOVE命令！ MOVE操作改變了表中所有行的ROWID，而索引中存儲的恰恰是舊的ROWID。Oracle為了避免數據錯亂，將該索引標記為UNUSABLE。這個“暗殺”是無聲的，操作本身不報錯，但索引已悄然“死亡”。

受害者（索引失效后的查詢）：

SET LINESIZE 1000
SET AUTOTRACE TRACEONLY
SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;執行計劃
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 25 | 1500 | 111 (1)| 00:00:02 |
| 1 | NESTED LOOPS | | 25 | 1500 | 111 (1)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T_C | 25 | 750 | 111 (1)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------3 - access("A"."ID"=880)4 - filter("B"."FID"=880)統計信息
----------------------------------------------------------0  recursive calls0  db block gets394  consistent gets0  physical reads0  redo size3602  bytes sent via SQL*Net to client459  bytes received via SQL*Net from client6  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)72  rows processed

對T_C表執行了TABLE ACCESS FULL，邏輯讀高達394。

“救治”方案與效果：

ALTER INDEX IND_T_C_FID REBUILD;-- 再次執行查詢
SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;執行計劃
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 72 | 4320 | 87 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 72 | 4320 | 87 (0)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_C | 72 | 2160 | 87 (0)| 00:00:02 |
|* 5 | INDEX RANGE SCAN | IND_T_C_FID | 72 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------3 - access("A"."ID"=880)5 - access("B"."FID"=880)統計信息
----------------------------------------------------------0  recursive calls0  db block gets81  consistent gets0  physical reads0  redo size3602  bytes sent via SQL*Net to client459  bytes received via SQL*Net from client6  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)72  rows processed

偵探筆記：
ALTER TABLE … MOVE是一個極其危險的操作。操作規范：在執行MOVE后，必須立即REBUILD該表上的所有索引。
在這里插入圖片描述

懸案三：外鍵索引失效引發的“幽靈鎖”

案情簡介：
如果說上一個案例是MOVE命令造成的直接性能傷害，那么這個案例，則是它更隱蔽、更陰險的“并發癥”——鎖等待。
現場勘查與證據收集：

-- 準備環境
drop table t_p cascade constraints purge;
drop table t_c cascade constraints purge;
CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);
CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);
INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;
INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;
COMMIT;
-- 注意，這里沒有給外鍵列T_C(FID)創建索引-- 以下操作導致外鍵相關的索引失效（此處原文為move，但實際場景是未創建索引）
-- ALTER TABLE T_C MOVE;

并發測試：

-- 首先開啟會話1
select sid from v$mystat where rownum=1;
DELETE T_C WHERE ID = 2;-- 接下來開啟會話2，也就是開啟一個新的連接
select sid from v$mystat where rownum=1;
DELETE T_P WHERE ID = 2000;
-- 居然發現卡住半天不動了！

破案分析：
這起“幽靈鎖”的根源，在于外鍵約束的底層工作機制。當你操作主表（如DELETE T_P）時，Oracle必須確保子表中沒有任何記錄引用你將要刪除的主表記錄。

索引有效時： Oracle會通過外鍵列上的索引，快速檢查子表T_C中是否存在FID = 2000的記錄。
索引失效（或不存在）時： Oracle無法快速檢查，只能在子表T_C上施加一個全表鎖，然后慢慢地進行全表掃描。當會話1持有T_C的行級鎖時，會話2想要獲取全表鎖自然會被阻塞。
偵探筆記：
外鍵列上必須建立索引，并保證其永遠有效！這不僅是查詢性能的需要，更是保證高并發環境下系統穩定性的“生命線”。

懸案四：shrink操作后的“偽裝者”

案情簡介：
既然MOVE如此危險，那我們用更現代的SHRINK命令來收縮表空間，總該安全了吧？它確實能避免索引失效，但新的問題又來了：索引明明VALID，優化器為何依然棄之不用？

現場勘查與證據收集：

drop table t purge;
create table t as select * from dba_objects where object_id is not null;
alter table t modify object_id not null;
set autotrace off
insert into t select * from t;
insert into t select * from t;
commit;
create index idx_object_id on t(object_id);
set linesize 1000
set autotrace on
select count(*) from t;
set autotrace off
delete from t where rownum<=292000;
commit;
set autotrace on
alter table t enable row movement;
alter table t shrink space;
select count(*) from t;執行計劃
------------------------------------------------------------------------------------
Plan hash value: 2966233522
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 740 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------統計信息
----------------------------------------------------------0  recursive calls0  db block gets15  consistent gets0  physical reads0  redo size424  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1  rows processed

破案分析：
索引再次“失蹤”！它明明狀態VALID，為何被優化器無情拋棄？我們強制讓它走索引（使用hint）看看。

select /*+index(t)*/ count(*) from t;執行計劃
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 675 (1)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_OBJECT_ID | 740 | 675 (1)| 00:00:09 |
--------------------------------------------------------------------------------------
統計信息
----------------------------------------------------------0  recursive calls0  db block gets649  consistent gets0  physical reads0  redo size424  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1  rows processed

真相大白！強制走索引的成本（Cost=675，邏輯讀=649）遠高于走全表掃描（Cost=5，邏輯讀=15）。原因在于：SHRINK操作雖然收縮了表，但并未有效地收縮索引段的空間。大量DELETE操作在索引中留下了許多“空洞”，導致索引變得稀疏而“臃腫”，掃描它反而得不償失。

偵探筆記：
索引的VALID狀態，僅僅是它的“準入證”，并不代表它的“健康證”。一個因大量刪除而變得臃腫的索引，即便有效，也可能成為性能的拖累。在這種情況下，ALTER INDEX … REBUILD 才是整理索引碎片、恢復其緊湊結構和高性能的“特效藥”。

總結

今天的調查到此告一段落。我們揭開了四個導致索引“失蹤”或“失效”的經典懸案：

偽裝者LIKE：被前導通配符%所迷惑。
刺客MOVE：無聲地讓索引狀態變為UNUSABLE。
幫兇“無索引外鍵”：在并發操作中制造“幽靈鎖”。
“亞健康”的SHRINK后遺癥：索引雖在，卻因臃腫而被優化器嫌棄。
然而，索引失蹤之謎遠未結束。在下一集中，我們將繼續追蹤另外四位“嫌疑人”，它們同樣狡猾，同樣致命。敬請期待《索引失蹤之謎（下）》。

未完待續…
論索引影響性能的一面⑤：索引失蹤之謎（下）