資料庫索引（Oracle和Mysql）學習總結

索引概念：

    索引是關系資料庫中用于存放每一條記錄的一種對象，主要目的是加快資料的讀取速度和完整性檢查。建立索引是一項技術性要求高的工作。一般在資料庫設計階段的與資料庫結構一道考慮。應用系統的性能直接與索引的合理直接有關。

一、Oracle索引

1、索引類型

列出常見常用的幾種索引類型，如下：

  非唯一索引（最常用） NonUnique

　　唯一索引  Unique

　　位圖索引  Bitmap

　　分區索引  Partitioned

　　非分區索引  NonPartitioned  

　　正常型B樹 Normal

　　基于函數的索引  Function-based

2、索引結構

B-tree：

适合與大量的增、删、改（OLTP）； 

不能用包含OR操作符的查詢； 

适合高基數的列（唯一值多） 

典型的樹狀結構； 

每個結點都是資料塊； 

大多都是實體上一層、兩層或三層不定，邏輯上三層； 

葉子塊資料是排序的，從左向右遞增； 

在分支塊和根塊中放的是索引的範圍；

Bitmap:

适合與決策支援系統； 

做UPDATE代價非常高； 

非常适合OR操作符的查詢； 

基數比較少的時候才能建位圖索引；

樹型結構：

索引頭 

開始ROWID，結束ROWID（先列出索引的最大範圍）

BITMAP

每一個BIT對應着一個ROWID，它的值是1還是0，如果是1，表示着BIT對應的ROWID有值

1. b-tree索引

Oracle資料庫中最常見的索引類型是b-tree索引，也就是B-樹索引，以其同名的計算科學結構命名。CREATE INDEX語句時，預設就是在建立b-tree索引。沒有特别規定可用于任何情況。

2. 位圖索引(bitmap index)

位圖索引特定于該列隻有幾個枚舉值的情況，比如性别字段，标示字段比如隻有0和1的情況。

3. 基于函數的索引

比如經常對某個字段做查詢的時候是帶函數操作的，那麼此時建一個函數索引就有價值了。

4. 分區索引和全局索引

這2個是用于分區表的時候。前者是分區内索引，後者是全表索引

5. 反向索引（REVERSE）

這個索引不常見，但是特定情況特别有效，比如一個varchar(5)位字段(員工編号)含值（10001,10002,10033,10005,10016..） 

這種情況預設索引分布過于密集，不能利用好伺服器的并行 

但是反向之後10001,20001,33001,50001,61001就有了一個很好的分布，能高效的利用好并行運算。

6.HASH索引

HASH索引可能是通路資料庫中資料的最快方法，但它也有自身的缺點。叢集鍵上不同值的數目必須在建立HASH叢集之前就要知道。需要在建立HASH叢集的時候指定這個值。使用HASH索引必須要使用HASH叢集。

3、索引原理

若沒有索引，搜尋某個記錄時（例如查找name='wish'）需要搜尋所有的記錄，因為不能保證隻有一個wish，必須全部搜尋一遍

若在name上建立索引，oracle會對全表進行一次搜尋，将每條記錄的name值哪找升序排列，然後建構索引條目（name和rowid），存儲到索引段中，查詢name為wish時即可直接查找對應地方

建立了索引并不一定就會使用，oracle自動統計表的資訊後，決定是否使用索引，表中資料很少時使用全表掃描速度已經很快，沒有必要使用索引

4、建立索引的原則

1.如果有兩個或者以上的索引，其中有一個唯一性索引，而其他是非唯一，這種情況下oracle将使用唯一性索引而完全忽略非唯一性索引

　　2. 至少要包含組合索引的第一列（即如果索引建立在多個列上，隻有它的第一個列被where子句引用時，優化器才會使用該索引）

　　3. 小表不要履歷索引

　　4. 對于基數大的列适合建立B樹索引，對于基數小的列适合履歷位圖索引

　　5. 列中有很多空值，但經常查詢該列上非空記錄時應該建立索引

　　6. 經常進行連接配接查詢的列應該建立索引

　　7. 使用create index時要将最常查詢的列放在最前面

　　8. LONG（可變長字元串資料，最長2G）和LONG RAW（可變長二進制資料，最長2G）列不能建立索引

　　9.限制表中索引的數量（建立索引耗費時間，并且随資料量的增大而增大；索引會占用實體空間；當對表中的資料進行增加、删除和修改的時候，索引也要動态的維護，降低了資料的維護速度）

5、索引使用原則

經常檢索排序大表中40%或非排序表7%的行，建議建索引；

為了改善多表關聯，索引列用于聯結；

列中的值相對比較唯一；

取值範圍（大：B*樹索引，小：位圖索引）；

Date型列一般适合基于函數的索引；

列中有許多空值，不适合建立索引

二、MySQL索引

1、索引類型

單列索引(普通索引，唯一索引，主鍵索引)、組合索引、全文索引、空間索引

單列索引：一個索引隻包含單個列，但一個表中可以有多個單列索引。

普通索引：MySQL中基本索引類型，沒有什麼限制，允許在定義索引的列中插入重複值和空值，純粹為了查詢資料更快一點。

唯一索引：索引列中的值必須是唯一的，但是允許為空值。

　  主鍵索引：是一種特殊的唯一索引，不允許有空值。

　　組合索引：在表中的多個字段組合上建立的索引，隻有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，使用組合索引時遵循最左字首集合。這個如果還不明白，等後面舉例講解時在細說　

　　全文索引，隻有在MyISAM引擎上才能使用，隻能在CHAR,VARCHAR,TEXT類型字段上使用全文索引，介紹了要求，說說什麼是全文索引，就是在一堆文字中，通過其中的某個關鍵字等，就能找到該字段所屬的記錄行，比如有"你是個大煞筆，二貨 ..." 通過大煞筆，可能就可以找到該條記錄。這裡說的是可能，因為全文索引的使用涉及了很多細節，我們隻需要知道這個大概意思，如果感興趣進一步深入使用它，那麼看下面測試該索引時，會給出一個博文，供大家參考。

　　空間索引：空間索引是對空間資料類型的字段建立的索引，MySQL中的空間資料類型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。在建立空間索引時，使用SPATIAL關鍵字。要求，引擎為MyISAM，建立空間索引的列，必須将其聲明為NOT NULL。

2、索引原理

在MySQL中，存儲引擎用類似的方法使用索引，其先在索引中查找對應的值，然後根據比對的索引記錄找到對應的資料行，最後将資料結果集傳回給用戶端。

3、索引使用原則

最左字首比對原則

mysql會一直向右比對直到遇到範圍查詢（>、<、between、like）就停止比對。比如a = 1 and b = 2 and c > 3 and d = 4，如果建立（a,b,c,d）順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

=和in可以亂序

比如a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識别的形式。

盡量選擇區分度高的列作為索引

區分度的公式是count(distinct col)/count(*)，表示字段不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀态、性别字段可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難确定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄。

索引列不能參與計算，保持列“幹淨”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。是以語句應該寫成create_time = unix_timestamp(’2014-05-29’);

盡量的擴充索引，不要建立索引

比如表中已經有a的索引，現在要加(a,b)的索引，那麼隻需要修改原來的索引即可。

4、高性能索引列

獨立的列

字首索引和索引的選擇性

多列索引

選擇合适的索引列順序

聚簇索引

覆寫索引

使用索引掃描來做排序

備援和重複索引

未使用的索引

同名原創公衆号： 程式大視界