主頁 > 知識庫 > Mysql中的索引精講

Mysql中的索引精講

熱門標簽:電話機器人的價格多少錢一個月 400電話申請廠家現(xiàn)貨 自己做地圖標注需要些什么 徐涇鎮(zhèn)騰訊地圖標注 百度地圖標注要什么軟件 天津公司外呼系統(tǒng)軟件 中國地圖標注公司 福建外呼電銷機器人加盟 昌德訊外呼系統(tǒng)

前言

開門見山,直接上圖,下面的思維導圖即是現(xiàn)在要講的內容,可以先有個印象~


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

1.常見索引類型(實現(xiàn)層面)

首先不談Mysql怎么實現(xiàn)索引的,先馬后炮一下,如果讓我們來設計數(shù)據(jù)庫的索引,該怎么設計?

我們首先思考一下索引到底想達到什么效果?其實就是想能夠實現(xiàn)快速查找數(shù)據(jù)的策略,所以索引的實現(xiàn)本質上就是一個查找算法。

但是跟普通的查找有所不同,因為我們的數(shù)據(jù)有一下特征:

1.存儲的數(shù)據(jù)是非常非常多的

2.并且還不斷的動態(tài)變化

所以實現(xiàn)索引時需要考慮到這兩個特點。我們需要找一個最合適的數(shù)據(jù)結構算法來實現(xiàn)查找功能。

下面一起看下常見的查找策略,如下圖:

由于前面說的兩個特點我們首先排除靜態(tài)查找的算法。

至于查找樹,我們有二叉樹和多叉樹兩種選擇:

二叉樹:如果選擇二叉樹的話,由于我們的數(shù)據(jù)量龐大,二叉樹的深度會變得非常大,我們的索引樹會變成參天大樹,每次查詢會導致很多磁盤IO。

多叉樹:多叉樹解決了了樹的深度大的問題,那么我們到底選擇B樹還是B+樹呢?

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

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


從上面圖可知B+樹的葉子節(jié)點存放了所有的索引值,并且葉子結點之間以鏈表的形式相互關聯(lián),所以我們只需從最左的鏈表遍歷的話即可查找所有的值,最常見的用途就是范圍查找,而B樹則不滿足這范圍查找,又或者說實現(xiàn)特別復雜,所以Mysql最終選擇了使用B+樹實現(xiàn)這一功能。

1.1 B-Tree 索引(B+樹)

先說明一下,雖然叫在Mysql官方叫做B-Tree索引,但采用的是B+樹數(shù)據(jù)結構。

B-tree索引能夠加快訪問數(shù)據(jù)的速度,不需要進行全表掃描,而是從索引樹的根節(jié)點層層往下搜索,在根節(jié)點存放了索引值和指向下一個節(jié)點的指針。

下面看下單列索引的數(shù)據(jù)怎么組織的。

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

上面User 表給uid列創(chuàng)建了一個索引,那么往表里插入uid(96~102)的時候存儲引擎是怎么管理索引的呢?看下面的索引樹

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

2.葉子節(jié)點的值是有序的

3.葉子節(jié)點之間以鏈表形式關聯(lián)

下面在看一下多列(聯(lián)合)索引的數(shù)據(jù)怎么組織的。

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

給User 表創(chuàng)建了聯(lián)合索引 key(uid,name) 這種情況下他的索引樹是如下圖所示。

特點跟單列索引一樣,不同之處在于他的排序,如果第一個字段相同時會按第二個索引字段排序

如何通過B-tree快速查找數(shù)據(jù)?

對于InnoDb 存儲引擎的B-tree索引,會按一下步驟通過索引找到行數(shù)據(jù)

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

對于MyISAM 存儲引擎的B-tree索引,會按一下步驟通過索引找到行數(shù)據(jù)

  • 在MyISAM 的索引樹的葉子節(jié)點上除了索引值之外即沒存儲主鍵,也沒存儲行數(shù)據(jù),而是存了指向行數(shù)據(jù)的指針,根據(jù)這個指針在從表文件查詢數(shù)據(jù)。

1.2 Hash 索引(哈希表)

哈希索引是基于哈希表來實現(xiàn)的,只有精確匹配所有的所有列才能生效。

也就是說假設有個hash索引 key (col1,col2) 那么每次只有 col1和col2兩個字段都用才能夠生效。因為生成hash索引的時候是根據(jù)一個hash函數(shù)對所有的索引列取hash值來實現(xiàn)的。

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

當我們執(zhí)行 mysql> select * from User where name='張三'; 時怎么利用hash索引快速查找的?

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

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

主鍵索引

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

主鍵索引是唯一的,通常以表的ID設置為主鍵索引,一個表只能有一個主鍵索引,這是他跟唯一索引的區(qū)別。

唯一索引

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

唯一索引主要用于業(yè)務上的唯一約束,他跟主鍵索引的區(qū)別是,一個表可以有多個唯一索引

單列索引

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

以某一個字段為索引

聯(lián)合索引

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

兩個或兩個以上字段聯(lián)合組成一個索引。使用時需要注意滿足最左匹配原則!

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

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

什么是聚簇索引?

聚簇索引指的是他的 索引和行數(shù)據(jù) 在一起存儲。也就是在一顆B+樹的葉子結點上存儲的不僅是他的索引值,還有對應的某一行的數(shù)據(jù)。待會兒看圖便知。

聚簇索引不是一種索引,而是一種數(shù)據(jù)存儲組織方式 !??!

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

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

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

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

下面看下聚簇索引和非聚簇索引在索引樹上數(shù)據(jù)是怎么分布的,圖片摘自《高性能Nysql》

下圖是聚簇索引的數(shù)據(jù)組織方式。 col1為主鍵索引的聚簇索引樹

索引列是主鍵 col1

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

下圖是非聚簇索引(二級索引)的數(shù)據(jù)組織方式。

索引列是 col2

與聚簇索引不同的是非聚簇索引在索引樹葉子節(jié)點上除了索引值之外只存了主鍵值。而聚簇索引則存了一行數(shù)據(jù)。

假如有一條sql 語句 select * from test where col2=93;

上面這條語句會經歷兩次從索引樹查找過程

1.第一步從非聚簇索引的索引樹上找到包含col2=93的葉子節(jié)點,并定位到行的主鍵 3

2.第二步 根據(jù)主鍵 3 在從聚簇索引定位包含 主鍵=3的葉子節(jié)點并返回全部行數(shù)據(jù)。

以上說的都是基于InnoDb存儲引擎的,MyISAM是不支持聚簇索引的,因為他的數(shù)據(jù)文件和索引文件是相互獨立存儲的  MyISAM存儲引擎的索引樹的葉子節(jié)點不會寸主鍵值,而存一個指向對應行的地址或者說是指針,然后再從表數(shù)據(jù)文件里去找,如下面圖所示。

 結論:

  • 聚簇索引:

通常由主鍵或者非空唯一索引實現(xiàn)的,葉子節(jié)點存儲了一整行數(shù)據(jù)

  • 非聚簇索引:

又稱二級索引,就是我們常用的普通索引,葉子節(jié)點存了索引值和主鍵值,在根據(jù)主鍵從聚簇索引查

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),且有個聯(lián)合索引 key(name,uid)那么

執(zhí)行如下面這條sql查詢時就用到了 覆蓋索引。

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

上面這條sql語句使用了聯(lián)合索引 key(name,uid),并且只需查找 name,uid兩個字段,所以使用了覆蓋索引。覆蓋索引有什么好處呢?先看一下下面這個圖

上面這個圖就是 聯(lián)合索引key(name,uid) 所對應的索引樹,從圖中可以看出,如果我們只需查詢(name,uid)兩個字段的話,從索引樹就能得到我們需要查的數(shù)據(jù)。不需要找到索引值之后再從表數(shù)據(jù)文件定位對應的行數(shù)據(jù)了。

覆蓋索引好處

1.避免了對主鍵索引(聚簇)的二次查詢

2.由于不需要回表查詢(從表數(shù)據(jù)文件)所以大大提升了Mysql緩存的負載

總之大大提升了讀取數(shù)據(jù)的性能

5.最佳索引使用策略

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

獨立的列

獨立的列不是指單列索引,而是指索引列不能是表達式的一部分或者是函數(shù)的一部分。

select * FROM test where col1 + 1 =100; // 不能是表達式一部分
select * FROM test where ABS(col1) =100; // 不能是函數(shù)一部分

最左匹配原則

假如有個聯(lián)合索引 key (col1,col2)。那么以下查詢是索引無效的

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

對于最左匹配原則,大家想一下B+樹的葉子節(jié)點的關聯(lián)就差不多知道為啥需要最左匹配原則了,因為B+的葉子結點,從左到右以鏈表的形式關聯(lián)的,索引我們查詢的時候要么范圍查詢,要么有明確的左邊一個開始的索引值,不能跳過或者不明確如 like '%XYZ'這種查詢。

索引值不能是null值

單列索引有null值會導致索引無效

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

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

因為聚簇索引和覆蓋索引的索引樹上就有了需要的字段,所以不需要回表文件查詢,所以提升了查詢速度

使用短索引

如果很長的字符串進行查詢,只需匹配一個前綴長度,這樣能夠節(jié)省大量索引空間

總結

以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,謝謝大家對腳本之家的支持。

您可能感興趣的文章:
  • 新手學習MySQL索引
  • 由不同的索引更新解決MySQL死鎖套路
  • 通過唯一索引S鎖與X鎖來了解MySQL死鎖套路
  • 分享幾道關于MySQL索引的重點面試題
  • 簡單談談Mysql索引與redis跳表
  • MySQL學習(七):Innodb存儲引擎索引的實現(xiàn)原理詳解
  • 使用shell腳本來給mysql加索引的方法
  • MySQL批量插入和唯一索引問題的解決方法
  • 高效利用mysql索引指南

標簽:荊門 鄂爾多斯 昌都 北京 黔西 梅河口 陜西 駐馬店

巨人網(wǎng)絡通訊聲明:本文標題《Mysql中的索引精講》,本文關鍵詞  Mysql,中的,索引,精講,Mysql,;如發(fā)現(xiàn)本文內容存在版權問題,煩請?zhí)峁┫嚓P信息告之我們,我們將及時溝通與處理。本站內容系統(tǒng)采集于網(wǎng)絡,涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《Mysql中的索引精講》相關的同類信息!
  • 本頁收集關于Mysql中的索引精講的相關信息資訊供網(wǎng)民參考!
  • 推薦文章