MySQL索引結(jié)構(gòu)實(shí)例分析

178次閱讀

共計(jì) 4042 個(gè)字符，預(yù)計(jì)需要花費(fèi) 11 分鐘才能閱讀完成。

這篇文章主要講解了“MySQL 索引結(jié)構(gòu)實(shí)例分析”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著丸趣 TV 小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL 索引結(jié)構(gòu)實(shí)例分析”吧！

簡(jiǎn)介

在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。

一般來(lái)說(shuō)索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤上。

優(yōu)點(diǎn)：

1、類似大學(xué)圖書館建書目索引，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的 IO 成本。

2、通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了 CPU 的消耗。

缺點(diǎn)：

1、雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行 INSERT、UPDATE 和 DELETE。因?yàn)楦卤頃r(shí)，MySQL 不僅要保存數(shù)據(jù)，還要保存一下索引文件。每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息。

2、實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的

索引舉例：（用樹(shù)結(jié)構(gòu)做索引）

左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址。

為了加快 Col2 的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹(shù)，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找在一定的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)，從而快速的檢索出符合條件的記錄。

索引結(jié)構(gòu)（樹(shù)）

如何通過(guò)索引加快數(shù)據(jù)庫(kù)表的查詢速度呢？為了方便講解，我們限定于數(shù)據(jù)庫(kù)表只包含下面這樣兩個(gè)查詢需求：

1、select* from user where id=1234；

2、select *from user where id 1234 and id 2345;（按區(qū)間）

為什么用樹(shù)，而不用哈希表

哈希表按值查詢的性能很好，時(shí)間復(fù)雜度是 O(1)，但它不能支持按照區(qū)間快速查找數(shù)據(jù)，因此無(wú)法滿足要求。同理，盡管平衡二叉查找樹(shù)查詢性能很高，時(shí)間復(fù)雜度為 O(logn)，而且對(duì)樹(shù)進(jìn)行中序遍歷，可以輸出有序的數(shù)據(jù)序列，但也無(wú)法滿足按照區(qū)間快速查找數(shù)據(jù)的需求。

為了支持按照區(qū)間快速查找數(shù)據(jù)，我們對(duì)二叉查找樹(shù)進(jìn)行改造，將二叉查找樹(shù)的葉子節(jié)點(diǎn)用鏈表串起來(lái)，如果要查找某個(gè)區(qū)間的數(shù)據(jù)，只需要用區(qū)間的起始值，在樹(shù)中進(jìn)行查找，當(dāng)定位到有序鏈表中的某個(gè)節(jié)點(diǎn)之后，再?gòu)倪@個(gè)節(jié)點(diǎn)開(kāi)始順著有序鏈表往后遍歷，直到有序鏈表中的節(jié)點(diǎn)數(shù)據(jù)值大于區(qū)間終止值為止。

又因?yàn)闃?shù)上的很多操作的時(shí)間復(fù)雜程度與樹(shù)的高度成正比，降低的樹(shù)的高度，就能減少磁盤 IO 操作。因此我們把索引構(gòu)建成 m 叉樹(shù)（m 2），詳細(xì)介紹可看后文。

BTree 索引

在介紹 B + 樹(shù)之前，先來(lái)了解一下 B 樹(shù)。

1、初始化介紹

一顆 b 樹(shù)，淺藍(lán)色的塊我們稱之為一個(gè)磁盤塊，可以看到每個(gè)磁盤塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤塊 1 包含數(shù)據(jù)項(xiàng) 17 和 35，包含指針 P1、P2、P3。P1 表示小于 17 的磁盤塊，P2 表示在 17 和 35 之間的磁盤塊，P3 表示大于 35 的磁盤塊。

注意：

真實(shí)的數(shù)據(jù)只存在于葉子節(jié)點(diǎn)，即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。（而且是多條數(shù)據(jù)組成的數(shù)據(jù)區(qū)間：3~ 5，… …，90~ 99）

非葉子節(jié)點(diǎn)不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如 17、35 并不真實(shí)存在于數(shù)據(jù)表中。

2、查找過(guò)程

如果要查找數(shù)據(jù)項(xiàng) 29，那么首先會(huì)把磁盤塊 1 由磁盤加載到內(nèi)存，此時(shí)發(fā)生一次 IO，在內(nèi)存中用二分查找確定 29 在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P的 IO）可以忽略不計(jì)，通過(guò)磁盤塊 1 的 P2 指針的磁盤地址把磁盤塊 3 由磁盤加載到內(nèi)存，發(fā)生第二次 IO，29 在 26 和 30 之間，鎖定磁盤塊 3 的 P2 指針，通過(guò)指針加載磁盤塊 8 到內(nèi)存，發(fā)生第三次 IO，同時(shí)內(nèi)存中做二分查找找到 29，結(jié)束查詢，總計(jì)三次 IO。

B+Tree 索引

B+ 樹(shù)和 B 樹(shù)類似，B+ 樹(shù)是 B 樹(shù)的改進(jìn)版。即：m 叉查找樹(shù)與有序鏈表構(gòu)建成的樹(shù)就是 B + 樹(shù)，也就是要存儲(chǔ)的樹(shù)索引

如圖：B+ 樹(shù)和 B 樹(shù)的主要區(qū)別有以下兩點(diǎn)：

1、B+ 樹(shù)的葉子節(jié)點(diǎn)用鏈表來(lái)串聯(lián)。查找某個(gè)區(qū)間的數(shù)據(jù)，只需要用區(qū)間的起始值，在樹(shù)中進(jìn)行查找，當(dāng)定位到有序鏈表中的某個(gè)節(jié)點(diǎn)之后，再?gòu)倪@個(gè)節(jié)點(diǎn)開(kāi)始順著有序鏈表往后遍歷，直到有序鏈表中的節(jié)點(diǎn)數(shù)據(jù)值大于區(qū)間終止值為止。

2、B+ 樹(shù)中的任何節(jié)點(diǎn)都不存儲(chǔ)真實(shí)數(shù)據(jù)，只是用來(lái)索引。B 樹(shù)直接通過(guò)葉子節(jié)點(diǎn)獲取到數(shù)據(jù)；而 B + 樹(shù)每個(gè)葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)行的鍵值和地址信息，當(dāng)查詢到某個(gè)葉子節(jié)點(diǎn)時(shí)，通過(guò)葉子節(jié)點(diǎn)的地址找到真實(shí)的數(shù)據(jù)信息。

聚簇索引與非聚簇索引

聚簇索引并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。術(shù)語(yǔ)‘聚簇’表示數(shù)據(jù)行和相鄰的鍵值聚簇的存儲(chǔ)在一起。

聚簇索引的好處：

按照聚簇索引排列順序，查詢顯示一定范圍數(shù)據(jù)的時(shí)候，由于數(shù)據(jù)都是緊密相連，數(shù)據(jù)庫(kù)不不用從多個(gè)數(shù)據(jù)塊中提取數(shù)據(jù)，所以節(jié)省了大量的 io 操作。

聚簇索引的限制：

1、對(duì)于 mysql 數(shù)據(jù)庫(kù)目前只有 innodb 數(shù)據(jù)引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。

2、由于數(shù)據(jù)物理存儲(chǔ)排序方式只能有一種，所以每個(gè) Mysql 的表只能有一個(gè)聚簇索引。一般情況下就是該表的主鍵。

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

如下圖，左側(cè)的索引就是聚簇索引，因?yàn)閿?shù)據(jù)行在磁盤的排列和索引排序保持一致。

索引分類

單值索引

即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引

隨表一起建索引：CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
 
單獨(dú)建單值索引：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)
 
單獨(dú)建唯一索引：CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
刪除索引：DROP INDEX idx_customer_no on customer ;

主鍵索引

設(shè)定為主鍵后數(shù)據(jù)庫(kù)會(huì)自動(dòng)建立索引，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) 
 
  單獨(dú)建主鍵索引：ALTER TABLE customer 
 add PRIMARY KEY customer(customer_no); 
 
刪除建主鍵索引：ALTER TABLE customer 
 drop PRIMARY KEY ; 
 
修改建主鍵索引：必須先刪除掉 (drop) 原索引，再新建 (add) 索引

復(fù)合索引

即一個(gè)索引包含多個(gè)列

隨表一起建索引：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)
 
單獨(dú)建索引：CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
 
刪除索引：DROP INDEX idx_no_name on customer ;

性能分析索引創(chuàng)建場(chǎng)景

哪些情況需要?jiǎng)?chuàng)建索引

1、主鍵自動(dòng)建立唯一索引

2、頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引

3、查詢中與其它表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引

4、單鍵 / 組合索引的選擇問(wèn)題，組合索引性價(jià)比更高

5、查詢中排序的字段，排序字段若通過(guò)索引去訪問(wèn)將大大提高排序速度

6、查詢中統(tǒng)計(jì)或者分組字段

哪些情況不要?jiǎng)?chuàng)建索引

1、表記錄太少

2、經(jīng)常增刪改的表或者字段原因：提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行 INSERT、UPDATE 和 DELETE。因?yàn)楦卤頃r(shí)，MySQL 不僅要保存數(shù)據(jù)，還要保存一下索引文件

3、Where 條件里用不到的字段不創(chuàng)建索引

4、過(guò)濾性不好的不適合建索引

感謝各位的閱讀，以上就是“MySQL 索引結(jié)構(gòu)實(shí)例分析”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì) MySQL 索引結(jié)構(gòu)實(shí)例分析這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

正文完