前言

數(shù)據(jù)庫索引是mysql數(shù)據(jù)庫中重要的組成部分，是數(shù)據(jù)庫查詢數(shù)據(jù)速度提升的關(guān)鍵，本文將介紹數(shù)據(jù)庫索引的一些內(nèi)容。

數(shù)據(jù)庫索引的數(shù)據(jù)結(jié)構(gòu)

在數(shù)據(jù)庫中的索引方法中，有TREE和HASH兩種方法，HASH是經(jīng)常使用的，本文中主要介紹TREE的數(shù)據(jù)結(jié)構(gòu)。B+Tree 的高度一般是2-4層，也就是說查找一條數(shù)據(jù)記錄，最多使用 2-4 次 IO，當(dāng)前一般的機(jī)械磁盤每秒至少可以做 100 次 IO， 2-4 詞的 IO 操作時(shí)間大概耗時(shí) 20-40ms。

數(shù)據(jù)庫中的B+Tree索引分為聚集索引（clustered index）和輔助索引（secondary index），聚集索引和還是非聚集索引其內(nèi)部的數(shù)據(jù)結(jié)構(gòu)為平衡二叉樹，所有的數(shù)據(jù)都存放在葉子節(jié)點(diǎn)，聚集索引存放的是一整行的數(shù)據(jù)，而輔助索引的葉子節(jié)點(diǎn)存放的是主鍵 id。 需要提一下的是B+Tree是一個(gè)自底向上生長的樹，在數(shù)據(jù)庫表中的數(shù)據(jù)進(jìn)行增長時(shí)，B+Tree也會進(jìn)行增長和分裂。

聚集索引

聚集索引就是按照每張表的主鍵 id 構(gòu)造出的一個(gè)B+Tree，同時(shí)葉子節(jié)點(diǎn)存放的是整行表的數(shù)據(jù)記錄，所以聚集索引的葉子節(jié)點(diǎn)就是數(shù)據(jù)頁，每個(gè)葉子節(jié)點(diǎn)所在的數(shù)據(jù)頁通過雙向鏈表來進(jìn)行鏈接。

數(shù)據(jù)頁智能按照一個(gè)B+Tree進(jìn)行排序，因此每張表智能由一個(gè)聚集索引。大多數(shù)情況下，查詢優(yōu)化器首先采用聚集索引，因?yàn)榫奂饕系娜~子結(jié)點(diǎn)能夠直接找到數(shù)據(jù)記錄。由于數(shù)據(jù)是按照順序排列的，索引聚集索引對于主鍵的排序查找和范圍值的查詢速度非?？?。

輔助索引

輔助索引也是非聚集索引，葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)。葉子節(jié)點(diǎn)除了包含的主鍵值外，每個(gè)葉子結(jié)點(diǎn)中的索引行還包含一個(gè)書簽（bookmark）。借助這個(gè)書簽InnoDB能夠很快找到索引對應(yīng)的行記錄。InnoDB 存儲引擎是索引組織表，因此輔助索引的書簽就是相應(yīng)行數(shù)據(jù)的聚集索引。

數(shù)據(jù)庫表中的聚集索引只有一個(gè)，輔助索引可以有多個(gè)，當(dāng)通過輔助索引來查找數(shù)據(jù)時(shí)，InnoDB 存儲引擎會遍歷輔助索引并通過葉級別的指針獲得指向主鍵索引的主鍵，然后通過主鍵索引來找到完整的行記錄。如果輔助索引樹的高度為 3，聚集索引樹的高度同樣為 3，那么如果需要找到一個(gè)完整的行記錄數(shù)據(jù)，一共需要 6 次邏輯 IO 訪問才能拿到數(shù)據(jù)頁。

索引管理

索引的創(chuàng)建和刪除方法如下：

# 創(chuàng)建和刪除方法如下
alter table table_name  index_name
create table table_name index_name
drop table table_name index_name
# 查詢數(shù)據(jù)庫表的索引
show index from table_name

如下圖所示，查詢數(shù)據(jù)庫表中的索引情況： 

 表中的實(shí)際索引如下：

PRIMARY KEY (`id`),
UNIQUE KEY `uk_tb_user_2` (`username`,`id_card`) USING BTREE,
UNIQUE KEY `uk_tb_user_1` (`username`,`cellphone`) USING BTREE

在索引的展示項(xiàng)中，索引的值cardinality是非常重要的選項(xiàng)，表示的是數(shù)據(jù)基數(shù)即是數(shù)據(jù)中不同元素的個(gè)數(shù)，如果數(shù)據(jù)內(nèi)容為枚舉，則再該列上建立索引就沒有多大意義，因?yàn)槿シ植坎淮蟆５沁@個(gè)值不是實(shí)時(shí)更新的，只能作為參考。

如果需要更新，則執(zhí)行以下命令：

analyze table table_name;

上面我們講述了數(shù)據(jù)庫的索引類型，包括聚集索引和輔助索引，一般情況下這兩種索引的索引列都是單列，接下來我們來介紹另外兩種索引：聯(lián)合索引和覆蓋索引。

聯(lián)合索引

聯(lián)合索引是指對表上的多個(gè)列建立索引，聯(lián)合索引的創(chuàng)建方法與單個(gè)索引的創(chuàng)建方法一樣，不同之處僅在于有多個(gè)索引列。聯(lián)合索引的數(shù)據(jù)結(jié)構(gòu)也是一個(gè)B+Tree。對于聯(lián)合索引，其鍵值對的數(shù)量必定是大于 1，多個(gè)鍵值對的排序和單個(gè)鍵值對的排序都是一樣的，通過葉子節(jié)點(diǎn)可以邏輯上順序讀取所有的數(shù)據(jù)。

多個(gè)鍵值的B+樹：

如有有數(shù)據(jù)庫表的索引建立為 （a，b），那么對于數(shù)據(jù)庫的查詢：

# 這兩種情況下都可以查詢到數(shù)據(jù)，而且可以用得到這個(gè)聯(lián)合索引
select * from table_name where a = XX and b = XX;
select * from table_name where a = XX;
# 如果查詢條件使用 b 列，那么就不會使用索引，因?yàn)?b 列位于索引的第二位
select * from table_name where b = XX;
# 這個(gè)查詢條件的順序和索引的順序不同，但是依然可以使用索引，
# 這是因?yàn)樵跀?shù)據(jù)庫查詢時(shí)會進(jìn)行sql 的優(yōu)化
select * from table_name where b = XX and a = XX;

在使用聯(lián)合索引時(shí)，選用性能最高也是區(qū)分度最高的列放在索引的前面，這樣就可以最大限度的提高查詢的新能。

覆蓋索引

InnoDB 存儲引擎支持覆蓋索引（covering index，或稱索引覆蓋），即從輔助索引中就可以查詢到數(shù)據(jù)記錄。使用覆蓋索引的一個(gè)好處是輔助索引不包含整行記錄的所有記錄，故其大小要遠(yuǎn)小于聚集索引，因此可以減少大量的 IO 操作。

使用覆蓋索引，就是查詢的條件和查詢的內(nèi)容是一致的，通過覆蓋索引則可以避免回表的情況發(fā)生，減少了一次 IO 操作。

回表： 通過輔助索引查找數(shù)據(jù)時(shí)，在其的葉子節(jié)點(diǎn)上存放的是該表的主鍵id，通過主鍵id 在聚集索引上查找數(shù)據(jù)記錄在進(jìn)行返回，這個(gè)操作就稱之為回表。

總結(jié)

本文介紹了mysql數(shù)據(jù)庫的數(shù)據(jù)結(jié)構(gòu)以及索引類型，了解了數(shù)據(jù)庫的重要參數(shù)項(xiàng)cardinality。聯(lián)合索引和覆蓋索引，介紹了基本概念和使用方法，在實(shí)際的項(xiàng)目開發(fā)中這兩中類型的索引使用十分廣泛，在小編看來，這兩個(gè)索引是沒什么區(qū)別的，只是查詢返回的內(nèi)容是否包含索引所在的列，僅此而已。

到此這篇關(guān)于Mysql 數(shù)據(jù)庫結(jié)構(gòu)及索引類型的文章就介紹到這了,更多相關(guān)Mysql 索引類型內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論