前言
索引可以加快資料庫查詢速度,減少資料庫I/O,提高資料庫性能。建立有效的索引,對于系統來說是非常重要的。一般來說,我們面臨的資料搜尋場景主要包括兩類:表的每條記錄都具有一個特征字段,與其他記錄進行區分,一次查詢即能定位到較少記錄;表字段選擇性差,基數(Cardinality)較小,一次查詢可能會命中大量記錄。
B-tree索引
B-tree(多路搜尋樹)類型索引是比較常見的。
适用場合:
- 選擇性高的字段,如:時間戳字段、sequence字段、實體主鍵、業務主鍵。
- 若字段的選擇性很差,則考慮隻有當該字段的值分布非常傾斜(這些字段的值相比其他值出現的幾率小得多)的情況下才建索引。可以事先對表字段的取值進行一個統計分析,判斷各種取值的情況的記錄數分别是多少,進而判斷是否值得為該字段建立索引。
- 如果多個字段組合起來,能改善選擇性,則可考慮建立複合索引。
以下情況不适合建b-tree索引
- 基數小、選擇性差的字段,如:員工表的 性别字段,不适合建立索引。
- 經常會發生修改的字段,不要建立索引。因為這時候資料庫不光要修改表的資料,還要更新索引。
- 大字段,如:備注性質的文本資訊,不适合建立索引。隻能在小字段上建索引。
- 小表,即資料量較少(一般<1000)的表,不适合建索引。在這種情況下,資料庫采用全表掃描甚至可能比索引更快。
實務要點:
注意平衡更新速度與查詢速度
通常來說,表的索引越多,其查詢的速度也就越快。但是,表的更新速度則會降低。這主要是因為表的更新(如往表中插入一條記錄)速度,反而随着索引的增加而增加。這主要是因為,在更新記錄的同時需要更新相關的索引資訊。為此,到底在表中建立多少索引合适,就需要在這個更新速度與查詢速度之間取得一個均衡點。
注意索引的定期清理
資料庫管理者,需要隔一段時間,如一年,對資料庫的索引進行優化。該去掉的去掉,該調整的調整,以提高資料庫的性能。
關于複合索引
- 複合索引的建立需要進行仔細分析,盡量考慮用單字段索引代替;
- 正确選擇複合索引中的主列字段,一般是選擇性較好的字段,即確定在WHERE子句中使用到的字段是複合索引的上司字段;
- 複合索引的幾個字段是否經常同時以AND方式出現在Where子句中?單字段查詢是否極少甚至沒有?如果是,則可以建立複合索引;否則考慮單字段索引;
- 如果複合索引中包含的字段經常單獨出現在Where子句中,則分解為多個單字段索引;
- 如果複合索引所包含的字段超過3個,那麼仔細考慮其必要性,考慮減少複合的字段;
- 如果既有單字段索引,又有這幾個字段上的複合索引,一般可以删除複合索引;
- 過多的複合索引,在有單字段索引的情況下,一般都是沒有存在價值的;相反,還會降低資料增加删除時的性能,特别是對頻繁更新的表來說,負面影響更大。
注意OLAP與OLTP的差異
對于一些資料倉庫或者決策型資料庫系統,其主要用來進行查詢。相關的記錄往往是在資料庫初始化的時候導入。此時,設定的索引多一點,可以提高資料庫的查詢性能。同時因為記錄不怎麼更新,是以索引比較多的情況下,也不會影響到更新的速度。即使在起初的時候需要導入大量的資料,此時也可以先将索引禁用掉。等到資料導入完畢後,再啟用索引。可以通過這種方式來減少索引對資料更新的影響。相反,如果那些表中經常需要更新記錄,如一些事務型的應用系統,資料更新操作是家常便飯的事情。此時如果在一張表中建立過多的索引,則會影響到更新的速度。
位圖索引
位圖索引适用于靜态資料,即OLAP,資料倉庫環境,不适于OLTP環境。如果一個列經常發生更新,則不适合。
主要特性
提高低基數字段查詢性能
如果對于基數較小的字段進行查詢,因為無法精确命中少數記錄,Oracle很可能會進行全表掃描,進而導緻查詢性能受到影響。而這正是位圖索引可以解決的問題。
并發性較差,不适合OLTP
位圖索引往往涉及多個字段,當位圖索引發生更新時,鎖的最小粒度是一個位圖段,可能牽涉到整張表的大部分記錄;這與B-tree索引的更新時隻鎖定單條記錄是不同的。是以,位圖索引存在并發性問題。這也決定了位圖索引更适合于不經常變化的靜态資料。
原理
對表的某幾個字段的所有記錄,縱橫轉換後,可表示為多元數組。因這些數值的基數都比較小,其中一個字段的所有值可視為一個向量。
例如下圖,人員資訊表的性别字段,組成兩個向量,男:101000……女:01011……
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISMwIzN1QjM4EzNxITM1EDMy8CX0Vmbu4GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.jpg)
婚姻狀況字段,組成三個向量,分别為:已婚:11000……未婚:00101……離婚:00010……
在查詢“未婚男性”時,實際上是對男向量與未婚向量進行與運算。
根據結果向量中為1的字段即可定位到相應的查詢結果記錄集。
索引相關DDL語句
建立索引
--建立一般索引
create index IDX_TABLENAME_01 on emp(ename); --采用預設表空間
create index IDX_TABLENAME_02 on emp(ename) tablespace idx_tbs; --指定表空間
--建立基于函數的索引
create index IDX_TABLENAME_03 on emp(UPPER(ename)) tablespace idx_tbs;
--建立唯一索引
create unique index IDX_TABLENAME_05 on emp(ename) tablespace idx_tbs;
--建立位圖索引
create bitmap index IDX_TABLENAME_06 on emp(ename) tablespace idx_tbs;
--建立主鍵索引
alter table emp add constraint PK_TABLENAME_1 primary key (col_name)
using index
tablespace idx_tbs;
删除及重建索引
drop index idx_name;
alter index idx_name rebuild nologging;
--分區索引删除及重建
drop index idx_name partition partition_name;
alter index idx_name rebuild partition partition_name nologging;
索引分區
--全局索引,通過global字句指定索引的範圍值:
create index IDX_TABLENAME_X1 on emp(part_date)
global partition by range(part_date)(
partition p01_idx vlaues less than ('0106')
partition p01_idx vlaues less than ('0111')
...
partition p01_idx vlaues less than ('0401')
);
--局部索引,隻适用于分區表,且依賴于分區表的分區鍵來建立。
create index IDX_PARTY_TAB_1 on PARTY_TAB(part_dec)
local(
partition idx_1 tablespace tbs1,
partition idx_2 tablespace tbs2
);
參考:全局分區索引與局部分區索引