电子商务网站建设的代码,有祥云网站,网站推广公司转型方向,成都网站建设著名公司梁敬彬梁敬弘兄弟出品
往期回顾 论索引影响性能的一面①索引的各种开销 论索引影响性能的一面②索引的使用失效 论索引影响性能的一面③ 索引开销与经典案例
开篇#xff1a;DBA的深夜“寻人启事”
作为数据库的守护者#xff0c;我们最信赖的伙伴莫过于“索引”。它如同一…梁敬彬梁敬弘兄弟出品
往期回顾 论索引影响性能的一面①索引的各种开销 论索引影响性能的一面②索引的使用失效 论索引影响性能的一面③ 索引开销与经典案例
开篇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_idrownum;
update t set object_nameAAALJB where object_id8;
update t set object_nameLJBAAA where object_id10;
commit;
create index idx_object_name on t(object_name);
SET AUTOTRACE ON
SET LINESIZE 10001. 场景一前缀匹配查询 (‘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_NAMEIND_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_NAMEIND_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.ID880)4 - filter(B.FID880)统计信息
----------------------------------------------------------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.ID880)5 - access(B.FID880)统计信息
----------------------------------------------------------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 rownum1;
DELETE T_C WHERE ID 2;-- 接下来开启会话2也就是开启一个新的连接
select sid from v$mystat where rownum1;
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 rownum292000;
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真相大白强制走索引的成本Cost675逻辑读649远高于走全表扫描Cost5逻辑读15。原因在于SHRINK操作虽然收缩了表但并未有效地收缩索引段的空间。 大量DELETE操作在索引中留下了许多“空洞”导致索引变得稀疏而“臃肿”扫描它反而得不偿失。
侦探笔记 索引的VALID状态仅仅是它的“准入证”并不代表它的“健康证”。一个因大量删除而变得臃肿的索引即便有效也可能成为性能的拖累。在这种情况下ALTER INDEX … REBUILD 才是整理索引碎片、恢复其紧凑结构和高性能的“特效药”。
总结
今天的调查到此告一段落。我们揭开了四个导致索引“失踪”或“失效”的经典悬案
伪装者LIKE 被前导通配符%所迷惑。 刺客MOVE 无声地让索引状态变为UNUSABLE。 帮凶“无索引外键” 在并发操作中制造“幽灵锁”。 “亚健康”的SHRINK后遗症 索引虽在却因臃肿而被优化器嫌弃。 然而索引失踪之谜远未结束。在下一集中我们将继续追踪另外四位“嫌疑人”它们同样狡猾同样致命。敬请期待《索引失踪之谜下》。
未完待续…
系列回顾
“大白话人工智能” 系列 “数据库拍案惊奇” 系列 “世事洞明皆学问” 系列
