索引失效的情況有哪些？索引何時(shí)會(huì)失效？（全面總結(jié)）

雖然你這列上建了索引，查詢(xún)條件也是索引列，但最終執(zhí)行計(jì)劃沒(méi)有走它的索引。

下面是引起這種問(wèn)題的幾個(gè)關(guān)鍵點(diǎn)。

列與列對(duì)比

某個(gè)表中，有兩列（id和c_id）都建了單獨(dú)索引，下面這種查詢(xún)條件不會(huì)走索引

select * from test where id=c_id;

這種情況會(huì)被認(rèn)為還不如走全表掃描。

存在NULL值條件

我們?cè)谠O(shè)計(jì)數(shù)據(jù)庫(kù)表時(shí)，應(yīng)該盡力避免NULL值出現(xiàn)，如果非要不可避免的要出現(xiàn)NULL值，也要給一個(gè)DEFAULT值，數(shù)值型可以給0、-1之類(lèi)的， 字符串有時(shí)候給空串有問(wèn)題，就給一個(gè)空格或其他。如果索引列是可空的，是不會(huì)給其建索引的，索引值是少于表的count(*)值的，所以這種情況下，執(zhí)行計(jì)劃自然就去掃描全表了。

select * from test where id is not null;

NOT條件

我們知道建立索引時(shí)，給每一個(gè)索引列建立一個(gè)條目，如果查詢(xún)條件為等值或范圍查詢(xún)時(shí)，索引可以根據(jù)查詢(xún)條件去找對(duì)應(yīng)的條目。反過(guò)來(lái)當(dāng)查詢(xún)條件為非時(shí)，索引定位就困難了，執(zhí)行計(jì)劃此時(shí)可能更傾向于全表掃描，這類(lèi)的查詢(xún)條件有：<>、NOT、in、not exists

select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where not in (6,7,8,9,0);
select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

LIKE通配符

當(dāng)使用模糊搜索時(shí)，盡量采用后置的通配符，例如：name||’%’，因?yàn)樽咚饕龝r(shí)，其會(huì)從前去匹配索引列，這時(shí)候是可以找到的，如果采用前匹配，那么查索引就會(huì)很麻煩，比如查詢(xún)所有姓張的人，就可以去搜索’張%’。

相反如果你查詢(xún)所有叫‘明’的人，那么只能是%明。這時(shí)候索引如何定位呢？前匹配的情況下，執(zhí)行計(jì)劃會(huì)更傾向于選擇全表掃描。后匹配可以走INDEX RANGE SCAN。

所以業(yè)務(wù)設(shè)計(jì)的時(shí)候，盡量考慮到模糊搜索的問(wèn)題，要更多的使用后置通配符。

select * from test where name like 張||'%';

條件上包括函數(shù)

查詢(xún)條件上盡量不要對(duì)索引列使用函數(shù)，比如下面這個(gè)SQL

select * from test where upper(name)='SUNYANG';

這樣是不會(huì)走索引的，因?yàn)樗饕诮r(shí)會(huì)和計(jì)算后可能不同，無(wú)法定位到索引。但如果查詢(xún)條件不是對(duì)索引列進(jìn)行計(jì)算，那么依然可以走索引。比如

select * from test where name=upper('sunyang');
--INDEX RANGE SCAN

這樣的函數(shù)還有：to_char、to_date、to_number、trunc等

復(fù)合索引前導(dǎo)列區(qū)分大

當(dāng)復(fù)合索引前導(dǎo)列區(qū)分小的時(shí)候，我們有INDEX SKIP SCAN，當(dāng)前導(dǎo)列區(qū)分度大，且查后導(dǎo)列的時(shí)候，前導(dǎo)列的分裂會(huì)非常耗資源，執(zhí)行計(jì)劃想，還不如全表掃描來(lái)的快，然后就索引失效了。

select * from test where owner='sunyang';

數(shù)據(jù)類(lèi)型的轉(zhuǎn)換

當(dāng)查詢(xún)條件存在隱式轉(zhuǎn)換時(shí)，索引會(huì)失效。

比如在數(shù)據(jù)庫(kù)里id存的number類(lèi)型，但是在查詢(xún)時(shí)，卻用了下面的形式：

select * from sunyang where id='123';

Connect By Level

使用connect by level時(shí)，不會(huì)走索引。

謂詞運(yùn)算

我們?cè)谏厦嬲f(shuō)，不能對(duì)索引列進(jìn)行函數(shù)運(yùn)算，這也包括加減乘除的謂詞運(yùn)算，這也會(huì)使索引失效。

建立一個(gè)sunyang表，索引為id，看這個(gè)SQL：

select * from sunyang where id/2=:type_id;

這里很明顯對(duì)索引列id進(jìn)行了’/2’除二運(yùn)算，這時(shí)候就會(huì)索引失效，這種情況應(yīng)該改寫(xiě)為：

select * from sunyang where id=:type_id*2;

就可以使用索引了。

Vistual Index

先說(shuō)明一下，虛擬索引的建立是否有用，需要看具體的執(zhí)行計(jì)劃，如果起作用就可以建一個(gè)，如果不起作用就算了。普通索引這么建：

create index idx_test_id on test(id);

虛擬索引Vistual Index這么建：

create index idx_test_id on test(id) nosegment;

做了一個(gè)實(shí)驗(yàn)，首先創(chuàng)建一個(gè)表：

CREATE TABLE test_1116( 
id number, 
a number 
); 

CREATE INDEX idx_test_1116_id on test_1116(id); 
CREATE INDEX idx_test_1116_a on test_1116(a)nosegment; 

其中id為普通索引，a為虛擬索引。

在表中插入十萬(wàn)條數(shù)據(jù)

begin 
for i in 1 .. 100000 loop 
        insert into test_1116 values (i,i); 
end loop; 
commit; 
end; 

接著分別去執(zhí)行下面的SQL看時(shí)間，由于在內(nèi)網(wǎng)機(jī)做實(shí)驗(yàn)，圖貼不出來(lái)，數(shù)據(jù)保證真實(shí)性。

select count(id) from test_1116;
--第一次耗時(shí)：0.061秒
--第二次耗時(shí)：0.016秒

select count(a) from test_1116; 
--第一次耗時(shí)：0.031秒
--第二次耗時(shí)：0.016秒

因?yàn)樵趫?zhí)行過(guò)一次后，oracle對(duì)結(jié)果集緩存了，所以第二次執(zhí)行耗時(shí)不走索引，走內(nèi)存就都一樣了。

可以看到在這種情況下，虛擬索引比普通索引快了一倍。

具體虛擬索引的使用細(xì)節(jié)，這里不再展開(kāi)討論。

Invisible Index

Invisible Index是oracle 11g提供的新功能，對(duì)優(yōu)化器（還接到前面博客里講到的CBO嗎）不可見(jiàn)，MySQL 也有，MySQL 8.0 中的索引可以隱藏了。我感覺(jué)這個(gè)功能更主要的是測(cè)試用，假如一個(gè)表上有那么多索引，一個(gè)一個(gè)去看執(zhí)行計(jì)劃調(diào)試就很慢了，這時(shí)候不如建一個(gè)對(duì)表和查詢(xún)都沒(méi)有影響的Invisible Index來(lái)進(jìn)行調(diào)試，就顯得很好了。

通過(guò)下面的語(yǔ)句來(lái)操作索引

alter index idx_test_id invisible;
alter index idx_test_id visible;

如果想讓CBO看到Invisible Index，需要加入這句：

alter session set optimizer_use_invisible_indexes = true;

原文鏈接：https://blog.csdn.net/bless2015/article/details/84134361

版權(quán)聲明：本文為CSDN博主「番茄發(fā)燒了」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。

1. 道友自訴：入職中軟一個(gè)月（外包華為）就離職了！
2. 發(fā)現(xiàn)個(gè)外賣(mài)漏洞，希望不要被封殺！
3. 漫畫(huà) | Java語(yǔ)言是如何誕生的？
4. 技術(shù)公司與非技術(shù)公司的區(qū)別，太真實(shí)了…
最近面試BAT，整理一份面試資料《Java面試BATJ通關(guān)手冊(cè)》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫(kù)、數(shù)據(jù)結(jié)構(gòu)等等。
獲取方式：點(diǎn)“在看”，關(guān)注公眾號(hào)并回復(fù) Java 領(lǐng)取，更多內(nèi)容陸續(xù)奉上。
文章有幫助的話(huà)，在看，轉(zhuǎn)發(fā)吧。
謝謝支持喲 (*^__^*）