為什么使用索引

索引是存儲(chǔ)引擎用于快速找到數(shù)據(jù)記錄的一種數(shù)據(jù)結(jié)構(gòu)，就好比一本教科書(shū)的目錄部分，通過(guò)目錄中找到對(duì)應(yīng)文章的頁(yè)碼，便可快速定位到需要的文章. MySQL中也是一樣的道理，進(jìn)行數(shù)據(jù)查找時(shí)，首先查看查詢(xún)條件是否命中某條索引，符合則通過(guò)索引查找相關(guān)數(shù)據(jù)，如果不符合則需要全表掃描，即需要一條一條地查找記錄，直到找到與條件符合的記錄.

什么是索引

MySQL官方對(duì)索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。
索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu). 可以簡(jiǎn)單理解為“排好序的快速查找數(shù)據(jù)結(jié)構(gòu)”，滿(mǎn)足特定查找算法. 這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上實(shí)現(xiàn)高級(jí)查找算法.
索引是在存儲(chǔ)引擎中實(shí)現(xiàn)的, 因此每種存儲(chǔ)引擎的索引不一定完全相同，并且每種存儲(chǔ)引擎不一定支持所有索引類(lèi)型. 同時(shí)，存儲(chǔ)引擎可以定義每個(gè)表的最大索引數(shù)和最大索引長(zhǎng)度. 所有存儲(chǔ)引擎支持每個(gè)表至少16個(gè)索引，總索引長(zhǎng)度至少為256字節(jié). 有些存儲(chǔ)引擎支持更多的索引數(shù)和更大的索引長(zhǎng)度.

索引的優(yōu)缺點(diǎn)

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

1. 提高數(shù)據(jù)檢索的效率，降低 數(shù)據(jù)庫(kù)的IO成本 ，這也是創(chuàng)建索引最主要的原因。
2. 通過(guò)創(chuàng)建唯一索引，可以保證數(shù)據(jù)庫(kù)表中每一行 數(shù)據(jù)的唯一性 。
3. 在實(shí)現(xiàn)數(shù)據(jù)的 參考完整性方面，可以 加速表和表之間的連接 。換句話(huà)說(shuō)，對(duì)于有依賴(lài)關(guān)系的子表和父表聯(lián)合查詢(xún)時(shí)， 可以提高查詢(xún)速度。
4. 在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢(xún)時(shí)，可以顯著 減少查詢(xún)中分組和排序的時(shí)間 ，降低了CPU的消耗。

缺點(diǎn)

1. 創(chuàng)建索引和維護(hù)索引要 耗費(fèi)時(shí)間 ，并 且隨著數(shù)據(jù)量的增加，所耗費(fèi)的時(shí)間也會(huì)增加。
2. 索引需要占 磁盤(pán)空間 ，除了數(shù)據(jù)表占數(shù)據(jù)空間之 外，每一個(gè)索引還要占一定的物理空間， 存儲(chǔ)在磁盤(pán)上 ，如果有大量的索引，索引文件就可能比數(shù)據(jù)文 件更快達(dá)到最大文件尺寸。
3. 雖然索引大大提高了查詢(xún)速度，同時(shí)卻會(huì) 降低更新表的速度 。當(dāng)對(duì)表 中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)地維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。 因此，選擇使用索引時(shí)，需要綜合考慮索引的優(yōu)點(diǎn)和缺點(diǎn)。

因此，選擇使用索引時(shí)，需要綜合考慮索引的優(yōu)點(diǎn)和缺點(diǎn)
索引可以提高查詢(xún)的速度，但是會(huì)影響插入記錄的速度。這種情況下，最好的辦法是先刪除表中的索引，然后插入數(shù)據(jù)，插入完成后再創(chuàng)建索引。

常見(jiàn)索引概念

索引按照物理實(shí)現(xiàn)方式，索引可以分為 2 種：聚簇（聚集）和非聚簇（非聚集）索引。我們也把非聚集索引稱(chēng)為二級(jí)索引或者輔助索引。

聚簇索引

聚簇索引并不是一種單獨(dú)的索引類(lèi)型，而是一種數(shù)據(jù)存儲(chǔ)方式（所有的用戶(hù)記錄都存儲(chǔ)在了葉子結(jié)點(diǎn)），也就是所謂的索引即數(shù)據(jù), 數(shù)據(jù)即索引.
術(shù)語(yǔ)"聚簇"表示當(dāng)前數(shù)據(jù)行和相鄰的鍵值聚簇的存儲(chǔ)在一起
特點(diǎn):

• 使用記錄主鍵值的大小進(jìn)行記錄和頁(yè)的排序，這包括三個(gè)方面的含義：
  • 頁(yè)內(nèi)的記錄是按照主鍵的大小順序排成一個(gè)單向鏈表。
  • 各個(gè)存放用戶(hù)記錄的頁(yè)也是根據(jù)頁(yè)中用戶(hù)記錄的主鍵大小順序排成一個(gè)雙向鏈表。
  • 存放目錄項(xiàng)記錄的頁(yè)分為不同的層次，在同一層次中的頁(yè)也是根據(jù)頁(yè)中目錄項(xiàng)記錄的主鍵大小順序排成一個(gè)雙向鏈表。
• B+樹(shù)的 葉子節(jié)點(diǎn) 存儲(chǔ)的是完整的用戶(hù)記錄。
  所謂完整的用戶(hù)記錄，就是指這個(gè)記錄中存儲(chǔ)了所有列的值（包括隱藏列）。
• 我們把具有這兩種特性的B+樹(shù)稱(chēng)為聚簇索引，所有完整的用戶(hù)記錄都存放在這聚簇索引的葉子節(jié)點(diǎn)處。這種聚簇索引并不需要我們?cè)贛ySQL語(yǔ)句中顯式的使用INDEX 語(yǔ)句去創(chuàng)建，InnDB存儲(chǔ)引擎會(huì)自動(dòng)的為我們創(chuàng)建聚簇索引。

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

• 數(shù)據(jù)訪(fǎng)問(wèn)更快，因?yàn)榫鄞厮饕龑⑺饕蛿?shù)據(jù)保存在同一個(gè)B+樹(shù)中，因此從聚簇索引中獲取數(shù)據(jù)比非聚簇索引更快
• 聚簇索引對(duì)于主鍵的排序查找和范圍查找速度非?？?/li>
• 按照聚簇索引排列順序，查詢(xún)顯示一定范圍數(shù)據(jù)的時(shí)候，由于數(shù)據(jù)都是緊密相連，數(shù)據(jù)庫(kù)不用從多 個(gè)數(shù)據(jù)塊中提取數(shù)據(jù)，所以節(jié)省了大量IO操作。

缺點(diǎn)：

• 插入速度嚴(yán)重依賴(lài)于插入順序，按照主鍵的順序插入是最快的方式，否則將會(huì)出現(xiàn)頁(yè)分裂，嚴(yán)重影響性能。因此，對(duì)于InnoDB表，我們一般都會(huì)定義一個(gè)自增的ID列為主鍵
• 更新主鍵的代價(jià)很高，因?yàn)閷?huì)導(dǎo)致被更新的行移動(dòng)。因此，對(duì)于InnoDB表，我們一般定義主鍵為不可更新
• 二級(jí)索引訪(fǎng)問(wèn)需要兩次索引查找，第一次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)

二級(jí)索引(輔助索引, 非聚簇索引)

如果我們想以別的列作為搜索條件該怎么辦？肯定不能是從頭到尾沿著鏈表依次遍歷記錄一遍。
答案：我們可以多建幾顆B+樹(shù)，不同的B+樹(shù)中的數(shù)據(jù)采用不同的排列規(guī)則。比方說(shuō)我們用c2列的大小作為數(shù)據(jù)頁(yè)、頁(yè)中記錄的排序規(guī)則，再建一課B+樹(shù)，效果如下圖所示：

這個(gè)B+樹(shù)與上邊介紹的聚簇索引有幾處不同：

• 使用記錄c2列的大小進(jìn)行記錄和頁(yè)的排序,這包括三個(gè)方面的含義

  1. 頁(yè)內(nèi)的記錄是按照c2列的大小順序排成一個(gè)單向鏈表。
  2. 各個(gè)存放用戶(hù)記錄的頁(yè)也是根據(jù)頁(yè)中記錄的c2列大小順序排成一個(gè)雙向鏈表存放目錄項(xiàng)記錄的頁(yè)分為不同的層次,在同一層次中的頁(yè)也是根據(jù)頁(yè)中目錄項(xiàng)記錄的c2列大小順序排成一個(gè)雙向鏈表。
  

• B+樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)的并不是完整的用戶(hù)記錄,而只是c2列+主鍵這兩兩個(gè)列的值。

• 目錄項(xiàng)記錄中不再是主鍵+頁(yè)號(hào)的搭配,而變成了c2列+頁(yè)號(hào)的搭配。

所以如果我們現(xiàn)在想通過(guò)c2列的值查找某些記錄的話(huà)就可以使用我們網(wǎng)剛剛建好的這個(gè)B+樹(shù)了。以查找c2列的值為4的記錄為例,查找過(guò)程如下:

1. 確定 目錄項(xiàng)記錄頁(yè)根據(jù)根頁(yè)面,也就是頁(yè)44,可以快速定位到目錄項(xiàng)記錄所在的頁(yè)為頁(yè)42(因?yàn)?<4<9)。
2. 通過(guò)目錄項(xiàng)記錄頁(yè)確定用戶(hù)記錄真實(shí)所在的頁(yè)。在頁(yè)42中可以快速定位到實(shí)際存儲(chǔ)用戶(hù)記錄的頁(yè),但是由于c2列并沒(méi)有唯一性約束,所以c2列值為4的記錄可能分布在多個(gè)數(shù)據(jù)頁(yè)中,又因?yàn)?<4=4,所以確定實(shí)際存存儲(chǔ)用戶(hù)記錄的頁(yè)在頁(yè)34和頁(yè)35中。
3. 在真實(shí)存儲(chǔ)用戶(hù)記錄的頁(yè)中定位到具體的記錄。到頁(yè)34和頁(yè)35中定位到具體的記錄。
4. 但是這個(gè)B+樹(shù)的葉子節(jié)點(diǎn)中的記錄只存儲(chǔ)了c2和c1(也就就是主鍵)兩個(gè)列,所以我們必須再根據(jù)主鍵值去聚簇索引中再查找一遍完整的用戶(hù)記錄。

聚簇索引與非聚簇索引的原理不同，在使用上也有一些區(qū)別：

1. 聚簇索引的葉子結(jié)點(diǎn)存儲(chǔ)的就是我們的數(shù)據(jù)記錄, 非聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)位置。非聚簇索引不會(huì)影響數(shù)據(jù)表的物理存儲(chǔ)順序。
2. 一個(gè)表只能有一個(gè)聚簇索引，因?yàn)橹荒苡幸环N排序存儲(chǔ)的方式，但可以有多個(gè)非聚簇索引，也就是多個(gè)索引目錄提供數(shù)據(jù)檢索。
3. 使用聚簇索引的時(shí)候，數(shù)據(jù)的查詢(xún)效率高，但如果對(duì)數(shù)據(jù)進(jìn)行插入，刪除，更新等操作，效率會(huì)比非聚簇索引低。

InnoDB的B+樹(shù)索引的注意事項(xiàng)

1. 根頁(yè)面位置萬(wàn)年不動(dòng)

實(shí)際上B+樹(shù)的形成過(guò)程是這樣的：

• 每當(dāng)為某個(gè)表創(chuàng)建一個(gè)B+樹(shù)索引（聚簇索引不是人為創(chuàng)建的，默認(rèn)就有）的時(shí)候，都會(huì)為這個(gè)索引創(chuàng)建一個(gè)根節(jié)點(diǎn)頁(yè)面。最開(kāi)始表中沒(méi)有數(shù)據(jù)的時(shí)候，每個(gè)B+樹(shù)索引對(duì)應(yīng)的根節(jié)點(diǎn)中即沒(méi)有用戶(hù)記錄，也沒(méi)有目錄項(xiàng)記錄。
• 隨后向表中插入用戶(hù)記錄時(shí)，先把用戶(hù)記錄存儲(chǔ)到這個(gè)根節(jié)點(diǎn)中。
• 當(dāng)根節(jié)點(diǎn)中的可用 空間用完時(shí)繼續(xù)插入記錄，此時(shí)會(huì)將根節(jié)點(diǎn)中的所有記錄復(fù)制到一個(gè)新分配的頁(yè)，比如 頁(yè)a中，然后對(duì)這個(gè)新頁(yè)進(jìn)行頁(yè)分裂的操作，得到另一個(gè)新頁(yè)，比如頁(yè)b。這時(shí)新插入的記錄根據(jù)鍵值（也就是聚簇索引中的主鍵值，二級(jí)索引中對(duì)應(yīng)的索引列的值）的大小就會(huì)被分配到頁(yè)a或者頁(yè)b中，而根節(jié)點(diǎn)便升級(jí)為存儲(chǔ)目錄項(xiàng)記錄的頁(yè)。

這個(gè)過(guò)程特別注意的是：一個(gè)B+樹(shù)索引的根節(jié)點(diǎn)自誕生之日起，便不會(huì)再移動(dòng)。這樣只要我們對(duì)某個(gè)表建議一個(gè)索引，那么它的根節(jié)點(diǎn)的頁(yè)號(hào)便會(huì)被記錄到某個(gè)地方。然后凡是 InnoDB存儲(chǔ)引擎需要用到這個(gè)索引的時(shí)候，都會(huì)從哪個(gè)固定的地方取出根節(jié)點(diǎn)的頁(yè)號(hào)，從而來(lái)訪(fǎng)問(wèn)這個(gè)索引。

2. 一個(gè)頁(yè)面最少儲(chǔ)存兩條數(shù)據(jù)

MyISAM 與 InnoDB 對(duì)比

MyISAM的索引方式都是“非聚簇”的，與InnoDB包含1個(gè)聚簇索引是不同的。小結(jié)兩種引擎中索引的區(qū)別：
① 在InnoDB存儲(chǔ)引擎中，我們只需要根據(jù)主鍵值對(duì)聚簇索引進(jìn)行一次查找就能找到對(duì)應(yīng)的記錄，而在 MyISAM 中卻需要進(jìn)行一次回表操作，意味著MyISAM中建立的索引相當(dāng)于全部都是 二級(jí)索引 。
② InnoDB的數(shù)據(jù)文件本身就是索引文件，而MyISAM索引文件和數(shù)據(jù)文件是分離的 ，索引文件僅保存數(shù)據(jù)記錄的地址。
③ InnoDB的非聚簇索引data域存儲(chǔ)相應(yīng)記錄**主鍵的值 ，而MyISAM索引記錄的是地址 **。換句話(huà)說(shuō)， InnoDB的所有非聚簇索引都引用主鍵作為data域。
④ MyISAM的回表操作是十分快速的，因?yàn)槭悄弥刂菲屏恐苯拥轿募腥?shù)據(jù)的，反觀(guān)InnoDB是通過(guò)獲取主鍵之后再去聚簇索引里找記錄，雖然說(shuō)也不慢，但還是比不上直接用地址去訪(fǎng)問(wèn)。
⑤ InnoDB要求表 必須有主鍵 （ MyISAM可以沒(méi)有 ）。如果沒(méi)有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè) 可以非空且唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵。如果不存在這種列，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱 含字段作為主鍵，這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)，類(lèi)型為長(zhǎng)整型。

索引的代價(jià)

空間上的代價(jià)
每建立一個(gè)索引都要為它建立一棵B+樹(shù)，每一棵B+樹(shù)的每一個(gè)節(jié)點(diǎn)都是一個(gè)數(shù)據(jù)頁(yè)，一個(gè)頁(yè)默認(rèn)會(huì) 占用 16KB 的存儲(chǔ)空間，一棵很大的B+樹(shù)由許多數(shù)據(jù)頁(yè)組成，那就是很大的一片存儲(chǔ)空間。
時(shí)間上的代價(jià)
每次對(duì)表中的數(shù)據(jù)進(jìn)行 增、刪、改 操作時(shí)，都需要去修改各個(gè)B+樹(shù)索引。而且我們講過(guò)，B+樹(shù)每 層節(jié)點(diǎn)都是按照索引列的值 從小到大的順序排序 而組成了 雙向鏈表 。不論是葉子節(jié)點(diǎn)中的記錄，還 是內(nèi)節(jié)點(diǎn)中的記錄（也就是不論是用戶(hù)記錄還是目錄項(xiàng)記錄）都是按照索引列的值從小到大的順序 而形成了一個(gè)單向鏈表。而增、刪、改操作可能會(huì)對(duì)節(jié)點(diǎn)和記錄的排序造成破壞，所以存儲(chǔ)引擎需 要額外的時(shí)間進(jìn)行一些 記錄移位 ， 頁(yè)面分裂 、 頁(yè)面回收 等操作來(lái)維護(hù)好節(jié)點(diǎn)和記錄的排序。如果 我們建了許多索引，每個(gè)索引對(duì)應(yīng)的B+樹(shù)都要進(jìn)行相關(guān)的維護(hù)操作，會(huì)給性能拖后腿。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-802632.html

到了這里，關(guān)于索引的數(shù)據(jù)結(jié)構(gòu)(MySql高級(jí))的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！