一起來聊聊MySQL索引結構-mysql教程-PHP中文網

這篇文章為大家帶來了關於mysql的相關知識，MySQL官方對索引的定義為索引（Index）是幫助MySQL高效獲取資料的資料結構，可以得到索引的本質，索引是資料結構，下面一起來看一下，希望對大家有幫助。

一起來聊聊MySQL索引結構

推薦學習：mysql影片教學

#簡介

在資料之外，資料庫系統也維護著滿足特定查找演算法的資料結構，這些資料結構以某種方式引用（指向）數據，這樣就可以在這些資料結構上實作高階查找演算法。這種資料結構，就是索引。

一般來說索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。

優點：

1、類似大學圖書館建書目索引，提高資料檢索的效率，降低資料庫的IO成本。

2、透過索引列對資料進行排序，降低資料排序的成本，降低了CPU的消耗。

缺點：

1、雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存索引檔。每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化後的索引資訊。

2、實際上索引也是一張表，該表保存了主鍵與索引字段，並指向實體表的記錄，所以索引列也是要佔用空間的

索引舉例：（用樹結構做索引）

左邊是資料表，一共有兩列七筆記錄，最左邊的是資料記錄的實體位址。

為了加快Col2的查找，可以維護一個右邊所示的二元查找樹，每個節點分別包含索引鍵值和一個指向對應資料記錄物理位址的指針，這樣就可以運用二元查找在一定的複雜度內獲取到相應數據，從而快速的檢索出符合條件的記錄。

索引結構（樹）

如何透過索引加快資料庫表的查詢速度呢？為了方便講解，我們限定於資料庫表只包含下面這樣兩個查詢需求：

1、select* from user where id=1234;

2、select *from user where id>1234 and id<2345;（依區間）

##為什麼用樹，不用哈希表

#哈希表按值查詢的效能很好，時間複雜度是O(1)，但它不能支援按照區間快速查找數據，因此無法滿足要求。同理，儘管平衡二元查找樹查詢效能很高，時間複雜度為O(logn)，而且對樹進行中序遍歷，可以輸出有序的資料序列，但也無法滿足按照區間快速查找資料的需求。

為了支援按照區間快速查找數據，我們對二元查找樹進行改造，將二元查找樹的葉子節點用鍊錶串起來，如果要查找某個區間的數據，只需要用區間的起始值，在樹中進行查找，當定位到有序鍊錶中的某個節點之後，再從這個節點開始順著有序鍊錶往後遍歷，直到有序鍊錶中的節點資料值大於區間終止值為止。

又因為樹上的許多操作的時間複雜程度與樹的高度成正比，降低的樹的高度，就能減少磁碟IO操作。因此我們把索引建構成m叉樹（m>2），詳細介紹可看後文。

BTree索引

在介紹B 樹之前，先來了解B樹。

1、初始化介紹

一顆b樹，淺藍色的區塊我們稱為一個磁碟區塊，可以看到每個磁碟區塊包含幾個資料項（深藍色所示）和指標（黃色所示），如磁碟區塊1包含資料項17和35，包含指標P1、P2、P3。 P1表示小於17的磁碟區塊，P2表示在17和35之間的磁碟區塊，P3表示大於35的磁碟區塊。

注意：

真實的資料只存在於葉子節點，即3、5、9、10、13、15、28、29、36、60、75、79、90、 99。（而且是多條資料組成的資料區間：3~ 5，… … ，90~ 99）

非葉子節點不儲存真實的數據，只儲存指引搜尋方向的資料項，如17、35並不真實存在於資料表中。

2、尋找過程

如果要查找資料項29，那麼首先會把磁碟區塊1由磁碟載入到內存，此時發生一次IO，在記憶體中用二分查找確定29在17和35之間，鎖定磁碟區塊1的P2指針，內存時間因為非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指針的磁碟地址把磁碟塊3由磁碟加載到內存，發生第二次IO，29在26和30之間，鎖定磁碟區塊3的P2指針，透過指針載入磁碟區塊8到內存，發生第三次IO，同時記憶體中做二分查找找到29，結束查詢，總計三次IO。

B Tree索引

B 樹和B樹類似，B 樹是B樹的改良版。即：m叉尋找樹與有序鍊錶建構成的樹就是B 樹，也就是要儲存的樹索引

如圖：B 樹與B樹的主要差異有以下兩點：

1、B 樹的葉子節點用鍊錶來串聯。查找某個區間的數據，只需要用區間的起始值，在樹中進行查找，當定位到有序鍊錶中的某個節點之後，再從這個節點開始順著有序鍊錶往後遍歷，直到有序鍊錶中的節點資料值大於區間終止值為止。

2、B 樹中的任何節點都不儲存真實數據，只是用來索引。 B樹直接透過葉子節點獲取到資料；而B 樹每個葉子節點儲存資料行的鍵值和位址信息，當查詢到某個葉子節點時，透過葉子節點的位址找到真實的資料資訊。

叢集索引與非叢集索引

叢集索引並不是一種單獨的索引類型，而是一種資料儲存方式。術語‘聚簇’表示資料行和相鄰的鍵值聚集的儲存在一起。

叢集索引的好處：

依照叢集索引排列順序，查詢顯示一定範圍資料的時候，由於資料都是緊密相連，資料庫不不用從多個資料區塊中擷取數據，所以節省了大量的io操作。

叢集索引的限制：

1、對於mysql資料庫目前只有innodb資料引擎支援叢集索引，而Myisam並不支援叢集索引。

2、由於資料實體儲存排序方式只能有一種，所以每個Mysql的表只能有一個叢集索引。一般情況下就是該表的主鍵。

3、為了充分利用聚集索引的叢集的特性，所以innodb表的主鍵列盡量選用有序的順序id，而不建議用無序的id，例如uuid這種。

如下圖，左邊的索引就是叢集索引，因為資料行在磁碟的排列和索引排序保持一致。

索引分類

單一值索引

#即索引只包含單一資料列，一個表格可以有多個單列索引

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
 
单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name  on customer;

登入後複製

唯一索引

索引列的值必須唯一，但允許有空值

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
  
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
删除索引：
DROP INDEX idx_customer_no on customer ;

登入後複製

主鍵索引

設定為主鍵後資料庫會自動建立索引，innodb為叢集索引

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (
id INT(10) UNSIGNED   ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
 单独建主键索引：
ALTER TABLE customer 
 add PRIMARY KEY customer(customer_no);  
 
删除建主键索引：
ALTER TABLE customer 
 drop PRIMARY KEY ;  
 
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

登入後複製

複合索引

即一個索引包含多個欄位

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

登入後複製