從索引技術談資料庫查詢索引建立和查詢條件書寫

索引的優勢當然是提高檢索速度,但並不是說資料庫建立了索引就真的會提高檢索速度.為什麼呢?

我們知道,索引本身是有序的,索引尋找的時候一般是多分尋找,(當然在記憶體用數組實現的索引則可以做到隨機尋找,但資料庫一般很少會採用這種方式組織,一般都是利用B+樹),所以索引的尋找一般不會是常數級,由於索引本身資料量問題,也不是一次就能將所有索引資料載入在記憶體裡,所以也可能會引起多次磁碟讀,加上定位到目標索引後還需要常數級的具體資料區塊磁碟讀寫,因此一次索引定位需要的磁碟讀寫可以控制在常數層級.因此索引尋找的速度會在對數層級.但這並不等同於資料庫查詢時具體的查詢速度,下面來分析一下:

1)只有建立索引的欄位作為條件才會啟用索引查詢,提高速度;
2)如果索引欄位的條件和其它停用字詞段的條件是or關係,也會啟用索引,但不會提高速度,因為這是的檢索速度取決於慢條件;
3)索引欄位在等值查詢時效率最高(等條件),大於,小於等帶範圍的查詢條件的速度是否能提高要看資料庫索引本身的實現技術,因此資料庫一般也不會採用B樹而採用類似B+樹的原因,因為B+樹的衛星資料都在葉子節點上,可以實現範圍讀,提高範圍查詢的效率;
4)對於模糊查詢,要看具體的資料庫,一般是不會啟用索引(Oracle 會做一定的最佳化,會用索引,具體可看後面的測試資料);
5)IS NULL,IS NOT NUL等條件也是一樣.

因此,在資料庫索引實際應用時要根據實際需要進行:
1)如果某個欄位,或者某幾個欄位頻繁單獨作為條件查詢時,可以建立索引;
2)如果一般欄位多使用模糊查詢,則不要建立索引;
3)索引欄位條件和停用字詞段只有在邏輯關係為與的情況下,索引欄位條件才會真正有意義,否則還是全表掃描;

下面是Oracle的索引是否有效一些條件:
1) 一般比較都會啟用索引,In,between也會啟用索引.
2)Like比較特殊,實際上Oracle會對第1個模糊比對符號前面的部分串作索引定位匹配,具體的可參見後面的測試資料;
3) is null,is not null不會啟用索引;
4)索引欄位與常量運算式時Or關係時,常量運算式不會影響結果,但變數和參數化就會全表掃描;

下面是我測試的結果:資料量進2億,伺服器就是普通的PC機,Cards欄位是索引欄位,batchno欄位是停用字詞段,下面是結果:

select count(*) from cards;-- >52s
select count(1) from cards;-- >52s
select * from cards where cards='1';--不存在,毫秒
select * from cards where cards='994595942';--存在,毫秒級
select * from cards where cards>='999999999';--毫秒級,但也與返回資料量大小有關.索引有效.
select * from cards where cards>='9999' and  cards<='9999';--毫秒級,但也與返回資料量大小有關.索引有效.
select * from cards where cards in('994595942','999906236');--毫秒秒,索引有效.
select * from cards where cards in( select '994595942' from dual);--毫秒級索引有效.
select * from cards where cards in('999906236');--0.156秒,索引有效.

select * from cards where cards > '999999999';--毫秒級,索引有效.
select * from cards where cards like '999999%';-->毫秒級,索引有效.但也與返回資料量大小有關
select * from cards where cards like '_999999_';--分鐘級,索引無效
select * from cards where cards like '999999_';--毫秒級,索引有效.但也與返回資料量大小有關
select * from cards where cards like '_999999';--分鐘級索引無效
select * from cards where cards like '999_999';--分鐘級索引無效

select * from cards where cards is null;--分鐘級索引無效
select * from cards where cards='994595942' or 1>1;--毫秒級,索引有效
update cards set batchno=batchno  where cards='994595942';--毫秒級,索引有效.
update cards set batchno=batchno  where cards='994595942' or i>1;--分鐘級索引無效 i是變數
update cards set batchno=batchno  where cards='994595942' or :V >1;--分鐘級索引無效 v是預留位置號,其實就是一般參數化查詢.

select * from cards where cards between '994595942' and '994595942';--毫秒級索引有效.
select * from cards where cards='994595942' or batchno='222';--索引有效,但沒意義,還是全表掃描;
select * from cards where cards='994595942' and batchno='222';--索引有效,毫秒級
select * from cards where  batchno='222' and cards='994595942';--索引有效,毫秒級,說明Oracle有先進行索引欄位處理的最佳化.

select * from cards where (batchno,cards) in (select '994595942','994595942' from dual);--毫秒級,索引有效.

PS:從上面的測試來看,Oracle的最佳化,特別是Like的最佳化非常到位,因為我原來還認為資料庫不會對模糊查詢利用索引.當然從上述測試也可以反推出一些Oracle索引存放的一些技術。