前言

數(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也會(huì)進(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ù)是按照順序排列的，索引聚集索引對(duì)于主鍵的排序查找和范圍值的查詢速度非常快。

輔助索引

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

數(shù)據(jù)庫表中的聚集索引只有一個(gè)，輔助索引可以有多個(gè)，當(dāng)通過輔助索引來查找數(shù)據(jù)時(shí)，InnoDB 存儲(chǔ)引擎會(huì)遍歷輔助索引并通過葉級(jí)別的指針獲得指向主鍵索引的主鍵，然后通過主鍵索引來找到完整的行記錄。如果輔助索引樹的高度為 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)合索引是指對(duì)表上的多個(gè)列建立索引，聯(lián)合索引的創(chuàng)建方法與單個(gè)索引的創(chuàng)建方法一樣，不同之處僅在于有多個(gè)索引列。聯(lián)合索引的數(shù)據(jù)結(jié)構(gòu)也是一個(gè)B+Tree。對(duì)于聯(lián)合索引，其鍵值對(duì)的數(shù)量必定是大于 1，多個(gè)鍵值對(duì)的排序和單個(gè)鍵值對(duì)的排序都是一樣的，通過葉子節(jié)點(diǎn)可以邏輯上順序讀取所有的數(shù)據(jù)。

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

如有有數(shù)據(jù)庫表的索引建立為 （a，b），那么對(duì)于數(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 列，那么就不會(huì)使用索引，因?yàn)?b 列位于索引的第二位
select * from table_name where b = XX;
# 這個(gè)查詢條件的順序和索引的順序不同，但是依然可以使用索引，
# 這是因?yàn)樵跀?shù)據(jù)庫查詢時(shí)會(huì)進(jìn)行sql 的優(yōu)化
select * from table_name where b = XX and a = XX;

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

覆蓋索引

InnoDB 存儲(chǔ)引擎支持覆蓋索引（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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論