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

知識追尋者發表於2020-09-15
MySql索引

一什麼是索引

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

二B-Tree與 B+Tree

2.1B-tree樹

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

https://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執行計劃看這篇 https://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%不會走優化器；

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

相關文章