資料庫索引原理

qq_547026179發表於2020-11-04

一、什麼時資料庫索引

資料庫索引是資料庫管理系統中一個排序的資料結構，將原本無規則一行一行排列的原始資料按照特定的資料結構排列起來而形成一個新的排序+原始資料的結構。這種資料結構主要以「平衡樹」(非二叉)，也就是b tree或者 b+ tree為主，當然有的資料庫也使用雜湊桶作用索引的資料結構。

二、資料庫索引的作用

快速的查詢資料。如果沒有索引，通常會全表掃描資料，如果表的資料非常大的話，一條一條的去匹配的話，最壞的情況下需要匹配O(n)最壞時間複雜度；而通過索引的話，不需要全表掃描，一般只需要O(logn)次就可以定位到具體的資料，大大減少了查詢速度。
管理資料庫約束。索引通常還會用於資料庫約束，例如：UNIQUE，PRIMARY KEY，FOREIG KEY，當一個索引被定義成UNIQUE時，資料庫同時建立一個隱式的約束。

三、索引的優缺點

優點：

減少I/O次數，加快檢索速度；
根據索引分組和排序，可以加快分組和排序。

缺點：

建立索引和維護索引耗時，時間隨著資料的增加而增加，成正比；
索引需要佔物理空間，除了資料表佔資料空間外，每一個索引還要佔一定的物理空間，如果建立聚簇索引，佔得物理空間會更大；
索引會降低資料表的修改操作（刪除，新增，修改）的效率，因為在修改資料表的同時還需要修改索引表；

四、索引的建立與分類

索引可以按照與資料之間的物理儲存順序的關係分為兩位類：

聚集索引/聚簇索引：聚簇索引的資料的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那麼對應的資料一定也是相鄰地存放在磁碟上的。一個表只能有一個聚簇索引。
非聚集索引/非聚簇索引：非聚簇索引的順序與資料物理排列順序無關，他們是分開的，非聚簇索引結構的葉子節點都不存放具體的物理資料而是存放的指向具體物理資料對應行的指標。

索引的建立方式:

--1、主鍵索引：即根據主鍵pk_clolum（length）建立索引，不允許重複；
ALTER TABLE table_name ADD PRIMARY KEY index_name(id);

--2、唯一索引：用來建立索引的列的值必須是唯一的，允許空值；
ALTER TABLE table_name ADD UNIQUE index_name(col1(10));

--3、普通索引：用表中的普通列構建的索引，沒有任何限制；
ALTER TABLE table_name ADD INDEX index_name(col2(20);

--4、全文索引：用大文字物件的列構建的索引；
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(col3(255));

--5、組合索引：用多個列組合構建的索引；
ALTER TABLE table_name ADD INDEX index_name(col1,col2,col3)；

--刪除索引
ALTER  TABLE  company  DROP  INDEX  index_name;

聯合索引最左側原則：
例如: ALTER TABLE person ADD INDEX idx_age_name_sex(age,name,sex);

A:select * from person where age = 16 and sex= '男'
B:select * from person where name = '小明' and age = 16
C:select * from person where name = '小明' and sex= '男'
D:select * from person where age = 18 and name = '小明' and sex = '男' 
D:select * from person where age > 20 and name = '小明'
E:select * from person where age != 15 and name = '小明'
F:select * from person where age = 15 and name != '小明'

上次這些查詢除了C不走索引，其他都會走索引，也就是說只要查詢條件中包含建立索引時最左邊的那個欄位，並且全部使用and連線起來，那麼這個聯合索引就生效。如果條件中包含or時，這個聯合索引也不生效。

五、索引的底層資料結構

索引常用的資料結構主要有B-tree（平衡樹）、B+tree、Hashes（雜湊）。

B+tree與B-tree的主要區別在於：

B+Tree中的非葉子結點不儲存資料，只儲存鍵值；
B+Tree的葉子結點沒有指標，所有鍵值都會出現在葉子結點上，且key儲存的鍵值對應data資料的實體地址；
B+Tree的每個非葉子節點由n個鍵值key和n個指標point組成；

B+Tree對比BTree的優點：

磁碟讀寫代價更低：因為B+Tree的非葉節點中不儲存data，就可以儲存更多的key，每個節點中的key個數越多，那麼樹的高度越小，需要I/O的次數越少，而提升查詢速度的關鍵就在於儘可能少的磁碟I/O，因此一般來說B+Tree比BTree更快。
查詢速度更穩定：由於B+Tree非葉子節點不儲存資料（data），因此所有的資料都要查詢至葉子節點，而葉子節點的高度都是相同的，因此所有資料的查詢速度都是一樣的。

InnoDB索引實現：

InnoDB引擎使用B+Tree作為索引實現，在InnoDB引擎中主鍵索引為聚集索引，其他輔助索引都為非聚集索引。在InnoDB引擎中通過主鍵索引也就是聚集索引可以直接獲取到具體的資料；而通過輔助索引在葉子節點只能拿到獲取具體資料的主鍵id，然後再通過主鍵去查詢主鍵索引，最終找到具體的資料。下面借用網上的圖片作為參考：
InnoDB的主鍵索引圖：在這裡插入圖片描述
InnoDB的輔助索引圖：