終於有人把MYSQL索引講清楚了

一什麼是索引

索引在MYSQL中也可以稱為鍵，其是存儲引擎用於快速查找記錄的一種數據結構；這樣聽起來有點生澀，你可能難以理解；如果給你一本書，你如何能夠精確的查找到書中某個章節的具體位置呢？我們肯定是先看目錄，再找內容。你可以理解索引就像書的目錄一樣；當資料庫的數據量大的時候，索引的性能對資料庫非常重要，索引分為很多種，所以要學習好索引的相關知識，甚至比查詢優化更重要。

二B-Tree與 B+Tree

2.1B-tree樹

學習B-樹之前讀者肯定要有二叉樹的基礎知識，（沒學過的看這篇

//blog.csdn.net/youku1327/article/details/105159762）

MYSQL中的數據結構實際上是B+tree，而非Btree；所以我們先要了解一下什麼是Btree，再了解下一下什麼是B+tree; 要得出的結論是為什麼MYSQL要使用B+tree, 而非 Btree；

M階B-tree的特徵如下

  • 根節點至少有兩個子女.
  • 每個節點包含k-1個元素和k個孩子,其中m/2 <= k <= m.
  • 每一個葉子節點都包含k-1個元素,其中m/2 <= k <= m.
  • 所有的葉子節點位於同一層.
  • 每個節點中的元素從小到大排列,那麼k-1個元素正好是k個孩子包含的值域的劃分

如果沒學過數據結構的讀者看到這邊肯定一頭霧水，知識追尋者還是做個簡單的說明；如下圖3階B-樹所示；

  • 根節點9包含兩個子節點，（3，7）一個節點，12 一個節點；
  • (m/2=1) <= k <= (m=3); 所以節點包含1至3個都正確，故 （3，7）和 12 滿足要求；
  • 再看 2 小於 3 位於 3左側，（4，5）大於3小於7位於（3，7）中間，8大於7位於7右側；

樹的高度決定了磁碟的IO能力，一棵3階的B-磁碟IO能力為3，與二叉樹IO能力相同；資料庫載入索引的時候是載入磁碟頁（默認4K大小），而非整個索引，每個磁碟頁都對應索引的記錄，故B-樹並不能帶來高效磁碟IO；

從樹的形態上B-樹比二叉樹更加的胖，原因也很簡單，B-樹的節點可能包含多個元素；

註：B樹就是B-樹，面試的時候別說B減樹；

2.2B+tree樹

B+樹是B-樹的基礎上進行升級，B+樹的特徵如下

  • 每個中間節點不保存數據，用來保存索引，葉子節點存放數據；
  • 中間節點都存在於葉子節點，為最大值或者最小值，所以會出現重複的現象；
  • 葉子節點之間根據自身的順序進行了鏈接；

對B+樹也做個簡單說明：

  • 中間節點 （2，5，8）和（11，16）都沒有存放數據，並且都是葉子節點存放數據；
  • 8，16同時存在於中間節點，葉子節點；同理（2，5，8）這個節點的元素都存在於葉子節點；

2.3B+比B-優勢

B+比B-的優勢在哪裡，面試經常問道；

  • B+樹的中間節點不存放數據，磁碟頁可以存放更多的節點元素；
  • B+Tree非葉子節點不存儲數據，所有的數據都要查詢至葉子節點，而葉子節點的高度都是相同的，因此所有數據的查詢速度都一樣；而B-樹根據精確的匹配查找，查找數據不穩定；

三 索引

3.1 非聚集索引和聚集索引

MySQL中最常見的兩種存儲引擎分別是MyISAM和InnoDB，分別實現了非聚集索引（普通索引）和聚集索引；

聚集索引:聚集索引的順序就決定了數據行的物理存儲順序；所以我們創建的主鍵索引其實就是聚集索引，如果未定義主鍵，MYSQL會默認選擇非空的唯一索引當作主鍵，否則會默認生成一個主鍵

非聚集索引：索引順序與數據行物理排列順序無關；

看下普通索引如何創建，其作用就是加快查詢速度；

語法格式如下

alter table 表名 add index 索引名稱(索引欄位)

如果創建表的時候語法格式如下

CREATE INDEX 索引名稱 ON 表名 (索引欄位)

知識追尋者手頭有一張用戶表，模擬10萬數據；

未創建索引查詢速度

select * from sys_user where first_name = 'ijklmnopqrs'
> OK
> 時間: 0.059s

創建索引

alter table sys_user add index select_username(first_name)

創建索引後查詢速度

select * from sys_user where first_name = 'ijklmnopqrs'
> OK
> 時間: 0.049s

刪除索引

DROP INDEX [索引名稱] ON 表名;

查看索引

SHOW INDEX FROM 表名;

3.2索引的分類

Mysql中索引的種類也不是很多，不同類型的索引有不同的作用，索引的作用相互之間也存在交叉關係，Mysql中索引主要分為以下幾類：

  1. 主鍵索引（PRIMARY KEY）：主鍵索引一般都是在創建表的時候進行指定，一個表只有一個主鍵索引，特點是唯一、非空。MYSQL常用就是 自增主鍵；
  2. 唯一索引（UNIQUE）：唯一索引具有的特點就是唯一性，即指定列不能出現重複數據；
  3. 前綴索引（prefix INDEX）：前綴索引建立的基礎就指定列數據有很多的共同前綴；
  4. 聯合索引：聯合索引又稱符合索引，是在表中兩個或者兩個列以上的基礎上創建索引；
  5. 覆蓋索引：當一個索引包含(或者說是覆蓋)需要查詢的所有欄位的值時,我們稱之為覆蓋索引；

3.3 主鍵索引和唯一索引

主鍵索引我們通常不默認，經常使用，一張表中僅允許有一個主鍵，可以由一個或者多欄位組成；主鍵索引滿足如下特徵：

  • 主鍵必須唯一；
  • 主鍵不能包含NULL值；
  • 主鍵必須自增；

創建主鍵語法格式

alter table 表名 add primary key (欄位名稱)

創建唯一索引語法格式：

alter table 表名 add unique (欄位名稱)

如果是創建表時添加約束語法格式

CREATE UNIQUE INDEX 索引名稱 ON 表名(欄位(欄位長度));

3.4 前綴索引

前綴索引: 當對字元串進行索引時,如果資料庫中該欄位有許多的前綴重複就可以使用前綴索引，,這樣可以大大的節約索引空間,從而提高索引效率；但其缺點也很明顯，不能在 order by 和 group by 中使用；

前綴索引經常使用在地名，比如 xx省xx市xx縣這種情形，有一個統一的前綴 xx省xx市；

創建語法

alter table 表名 add key (欄位名稱(前綴長度))

示例

alter table sys_user add key (first_name(8))

查詢的時候使用指定前綴的長度性能更加

select * from sys_user where first_name = 'ijklmnop'

3.5 覆蓋索引

回表查詢：

MYSQL 如果只通過索引就可以返回查詢所需要的數據，就是不是回表查詢，否則查到索引數據後還需要回到表中查詢數據就是回表查詢；

我們來看個簡單的示例

先去除前綴索引

drop index first_name on sys_user

然後加上普通索引

alter table sys_user add index select_username(first_name)

實行MYSQL執行計劃

（沒學過MYSQL執行計劃看這篇 //blog.csdn.net/youku1327/article/details/107336500）

explain select id from sys_user where first_name = 'ijklmnop'

輸出結果表示 使用using index ， 由於 id 和 first_name 都是索引；所以不需要回表查詢就是覆蓋索引；

如果我們使用如下語句則需要回表查詢，原因是查詢到欄位id, first_name後還需要回表查詢其它欄位，這就是為什麼 select * 如此慢的原因；

explain select * from sys_user where first_name = 'ijklmnop'

輸出結果如下

3.6 聯合索引

聯合索引是在表中用2個或者2個以上的欄位創建索引，其創建索引方式與普通索引相同；其能減小檢索範圍；

語法格式

alter table 表名 add index 索引名稱(欄位1，欄位2...)

最左前綴匹配原則

使用聯合索引有一個非常重要的因素就是所有的索引列只可以進行最左前綴匹配原則；

比如

聯合索引 first_name和 last_name

alter table sys_user add index select_username(first_name,last_name)

根據最左匹配原則情形如下會命中索引

  • first_name,last_name
  • first_name

轉換為查詢語句命中索引示例如下

select * from sys_user where first_name = 'ijklmnop';

select * from sys_user where first_name ='ijklmnop' and last_name ='ijklmnop';

select * from sys_user where first_name ='ijklmnop' and last_name in (ijklmnop');

select * from sys_user order by first_name,last_name

select * from sys_userwhere first_name ='ijklmnop'order by last_name

如下情形不會命中索引

select * from sys_user where last_name = 'ijklmnop';
select * from sys_userwhere last_name ='ijklmnop'order by first_name

索引下推

Mysql5.6版本發布了索引下推的原則，主要用於like關鍵字的查詢優化 ；

比如聯合索引（last_name,age）

select * from sys_user where last_name = 'ijklmnop' and age>'20';

命中可能性如下

  • 命中last_name聯合索引，查詢所有滿足last_name以”ijklmnop”開頭的數據， 然後回表查詢所有滿足的行。
  • 命中last_name聯合索引，查詢所有滿足last_name以”ijklmnop”開頭的數據，然後再篩出age>20的索引，再回表查詢全行數據。

第二種方式的磁碟IO會更少，查詢效率會更高，這就是下推索引；

除此之外還有全文索引和hash索引，簡單了解一下即可；

四 索引總結

4.1索引的優點

  • 提高查詢效率
  • 提高聚合函數查詢效率
  • 提高排序查詢效率
  • 使用覆蓋索引避免回表

4.2創建索引的策略

  • 不要在NULL值列上使用索引，盡量使用NOT NULL約束列上使用索引
  • 很少查詢的欄位不要使用索引
  • 大數據類型欄位不創建索引

4.3 使用索引時的注意事項

  • 不要在條件NOT IN、<>、!= 等範圍查詢中使用索引
  • 模糊查詢時不要使用 %開頭（ 如 ‘%xxx’ , ‘%xxx%’）
  • 查詢索引的欄位不要函數計算
  • 聯合索引查詢時遵循最左原則
  • 全部掃描超過30%不會走優化器；

聽說關注公眾號 知識追尋者 的男孩子都找到了漂亮的女朋友，女孩子都找到了白馬王子；當然索引並非查詢優化的最佳原則，但在大多數情況下就已經足夠使用；在大數據情況下通常要考慮分庫分表；

Tags:

VirMach 便宜 VPS

QNews

QNews