Mysql 索引精講

小小木發表於2019-05-08
MySql索引

開門見山，直接上圖，下面的思維導圖即是現在要講的內容，可以先有個印象～

Mysql 索引精講

  • 常見索引型別(實現層面)
  • 索引種類(應用層面)
  • 聚簇索引與非聚簇索引
  • 覆蓋索引
  • 最佳索引使用策略

1.常見索引型別(實現層面)

首先不談Mysql怎麼實現索引的,先馬後炮一下,如果讓我們來設計資料庫的索引，該怎麼設計？

我們首先思考一下索引到底想達到什麼效果？其實就是想能夠實現快速查詢資料的策略，所以索引的實現本質上就是一個查詢演算法。

但是跟普通的查詢有所不同，因為我們的資料有一下特徵：

1.儲存的資料是非常非常多的
2.並且還不斷的動態變化

所以實現索引時需要考慮到這兩個特點。我們需要找一個最合適的資料結構演算法來實現查詢功能。

下面一起看下常見的查詢策略，如下圖：

Mysql 索引精講

由於前面說的兩個特點我們首先排除靜態查詢的演算法。

至於查詢樹，我們有二叉樹和多叉樹兩種選擇：

二叉樹：如果先澤二叉樹的話，由於我們的資料量龐大，二叉樹的深度會變得非常大，我們的索引樹會變成參天大樹，每次查詢會導致很多磁碟IO。

多叉樹：多叉樹解決了了樹的深度大的問題，那麼我們到底選擇B樹還是B+樹呢？

B樹 摘自維基百科 zh.wikipedia.org/wiki/B%2B樹

Mysql 索引精講

B+樹 摘自維基百科 zh.wikipedia.org/wiki/B%2B樹

Mysql 索引精講

從上面圖可知B+樹的葉子節點存放了所有的索引值，並且葉子結點之間以連結串列的形式相互關聯，所以我們只需從最左的連結串列遍歷的話即可查詢所有的值，最常見的用途就是範圍查詢，而B樹則不滿足這範圍查詢，又或者說實現特別複雜,所以Mysql最終選擇了使用B+樹實現這一功能。

1.1 B-Tree 索引(B+樹)

先說明一下，雖然叫在Mysql官方叫做B-Tree索引，但採用的是B+樹資料結構。

B-tree索引能夠加快訪問資料的速度，不需要進行全表掃描，而是從索引樹的根節點層層往下搜尋，在根節點存放了索引值和指向下一個節點的指標。

下面看下單列索引的資料怎麼組織的。

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`)
);

複製程式碼

上面User 表給uid列建立了一個索引，那麼往表裡插入uid(96~102)的時候儲存引擎是怎麼管理索引的呢？看下面的索引樹

Mysql 索引精講

1.在葉子節點存放所有的索引值，非葉子節點值是為了更快定位包含目標值的葉子節點

2.葉子節點的值是有序的

3.葉子節點之間以連結串列形式關聯

下面在看一下多列(聯合)索引的資料怎麼組織的。

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`,`name`)
);

複製程式碼

給User 表建立了聯合索引 key(uid,name) 這種情況下他的索引樹是如下圖所示。

Mysql 索引精講

特點跟單列索引一樣，不同之處在於他的排序，如果第一個欄位相同時會按第二個索引欄位排序

如何通過B-tree快速查詢資料？

Mysql 索引精講

對於InnoDb 儲存引擎的B-tree索引，會按一下步驟通過索引找到行資料

  • 如果使用了聚簇索引(主鍵)，則葉子節點上就包含行資料，可直接返回
  • 如果使用了非聚簇索引(普通索引),則在葉子節點存了主鍵，再根據主鍵查詢一次上面 的聚簇索引，最後返回資料

對於MyISAM 儲存引擎的B-tree索引，會按一下步驟通過索引找到行資料

  • 在MyISAM 的索引樹的葉子節點上除了索引值之外即沒儲存主鍵，也沒儲存行資料，而是存了指向行資料的指標，根據這個指標在從表檔案查詢資料。

1.2 Hash 索引(雜湊表)

雜湊索引是基於雜湊表來實現的，只有精確匹配所有的所有列才能生效。

也就是說假設有個hash索引 key (col1,col2) 那麼每次只有 col1和col2兩個欄位都用才能夠生效。因為生成hash索引的時候是根據一個hash函式對所有的索引列取hash值來實現的。

如下方圖，有個hash索引key(name)

Mysql 索引精講

當我們執行 mysql> select * from User where name='張三'； 時怎麼利用hash索引快速查詢的？

  1. 第一步，計算出hash值，hash(張三) = 1287
  2. 第二步，定位行號，比如key=1287 對應的行號為3
  3. 第三步，找到指定行並且比較name列值是否為張三做個校驗

Mysql 索引精講

2.常見索引種類(應用層面)

主鍵索引

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 primary key(`uid`)
);
複製程式碼

主鍵索引是唯一的，通常以表的ID設定為主鍵索引,一個表只能有一個主鍵索引，這是他跟唯一索引的區別。

唯一索引

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 unique key(`name`)
);
複製程式碼

唯一索引主要用於業務上的唯一約束，他跟主鍵索引的區別是，一個表可以有多個唯一索引

單列索引

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`name`)
);
複製程式碼

以某一個欄位為索引

聯合索引

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`name`,`uid`)
);
複製程式碼

兩個或兩個以上欄位聯合組成一個索引。使用時需要注意滿足最左匹配原則！

還有其他不常用的就不介紹了～

3.聚簇索引與非聚簇索引

什麼是聚簇索引？
聚簇索引指的是他的 索引和行資料 在一起儲存。也就是在一顆B+樹的葉子結點上儲存的不僅是他的索引值，還有對應的某一行的資料。待會兒看圖便知。

聚簇索引不是一種索引，而是一種資料儲存組織方式 ！！！

crreate table test(
  col1 int not null,
  col2 int not null,
  PRIMARY KEY(col1),
  KEY(col2)
);

複製程式碼

如上所示，表test 由兩個索引，分別是主鍵 col1 和 普通索引 col2。那麼這倆索引跟聚簇非聚簇有啥關係呢？

會生成一個聚簇索引和一個非聚簇索引(二級索引)，也就是說會組織兩個索引樹。主鍵索引會生成聚簇索引的樹 以及以col2為索引的非聚簇索引的樹。

InnoDb 將通過主鍵來實現聚簇索引 ，如果沒有主鍵則會選選一個唯一非空索引來實現。如果沒有唯一非空索引則會隱式生成一個主鍵。

下面看下聚簇索引和非聚簇索引在索引樹上資料是怎麼分佈的，圖片摘自《高效能Nysql》

下圖是聚簇索引的資料組織方式。 col1為主鍵索引的聚簇索引樹

索引列是主鍵 col1

Mysql 索引精講
可以看出葉子結點除了儲存索引值 列col1 (3~99~4700)值 之外還儲存了其他列的值，如列col2 (92~8~13),如果還有別的列的話也會儲存，或者換句話說聚簇索引樹 在葉子節點上儲存某個索引值對應的一行資料。

下圖是非聚簇索引(二級索引)的資料組織方式。

索引列是 col2

Mysql 索引精講

與聚簇索引不同的是非聚簇索引在索引樹葉子節點上除了索引值之外只存了主鍵值。而聚簇索引則存了一行資料。

假如有一條sql 語句 select * from test where col2=93;
上面這條語句會經歷兩次從索引樹查詢過程

1.第一步從非聚簇索引的索引樹上找到包含col2=93的葉子節點，並定位到行的主鍵 3
2.第二步 根據主鍵 3 在從聚簇索引定位包含 主鍵=3的葉子節點並返回全部行資料。

以上說的都是基於InnoDb儲存引擎的,MyISAM是不支援聚簇索引的，因為他的資料檔案和索引檔案是相互獨立儲存的 MyISAM儲存引擎的索引樹的葉子節點不會寸主鍵值，而存一個指向對應行的地址或者說是指標，然後再從表資料檔案裡去找，如下面圖所示。

Mysql 索引精講

結論：

  • 聚簇索引:
    通常由主鍵或者非空唯一索引實現的，葉子節點儲存了一整行資料

  • 非聚簇索引：
    又稱二級索引，就是我們常用的普通索引，葉子節點存了索引值和主鍵值，在根據主鍵從聚簇索引查

4.覆蓋索引

覆蓋索引就是指索引包含了所有需要查詢的欄位。

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`,`name`)
);

複製程式碼

假如表 User有三個欄位 User (name,uid,gender),且有個聯合索引 key(name,uid)那麼 執行如下面這條sql查詢時就用到了 覆蓋索引。

select name,uid from User where name in ('a','b') and uid >= 98 and uid <=100 ;

上面這條sql語句使用了聯合索引 key(name,uid),並且只需查詢 name,uid兩個欄位，所以使用了覆蓋索引。覆蓋索引有什麼好處呢？先看一下下面這個圖

Mysql 索引精講

上面這個圖就是 聯合索引key(name,uid) 所對應的索引樹，從圖中可以看出，如果我們只需查詢(name,uid)兩個欄位的話，從索引樹就能得到我們需要查的資料。不需要找到索引值之後再從表資料檔案定位對應的行資料了。

覆蓋索引好處
1.避免了對主鍵索引(聚簇)的二次查詢
2.由於不需要回表查詢(從表資料檔案)所以大大提升了Mysql快取的負載

總之大大提升了讀取資料的效能

5.最佳索引使用策略

最後在講講使用索引過程中的避坑指南

獨立的列

獨立的列不是指單列索引，而是指索引列不能是表示式的一部分或者是函式的一部分。

select * FROM test where col1 + 1 =100; // 不能是表示式一部分

select * FROM test where ABS(col1) =100; // 不能是函式一部分

最左匹配原則

假如有個聯合索引 key (col1,col2)。那麼一下查詢是索引無效的

select * from test where col2 = 3;

select * from test where col1 like '%3';

對於最左匹配原則，大家想一下B+樹的葉子節點的關聯就差不多知道為啥需要最左匹配原則了，因為B+的葉子結點，從左到右以連結串列的形式關聯的，索引我們查詢的時候要麼範圍查詢，要麼有明確的左邊一個開始的索引值，不能跳過或者不明確如 like '%XYZ'這種查詢。

索引值不能是null值

單列索引有null值會導致索引無效
多列索引只要有個列有null值會導致索引無效

使用聚簇索引和覆蓋索引大大提升讀取效能

因為聚簇索引和覆蓋索引的索引樹上就有了需要的欄位，所以不需要回表檔案查詢，所以提升了查詢速度

使用短索引
如果很長的字串進行查詢，只需匹配一個字首長度，這樣能夠節省大量索引空間

相關文章