MySQL索引的一些問題

注意：本文基于MySQL的InnoDB引擎說明。

一、什么是最左前綴原則

對于該表，如果按照name字段來建立索引的話，采用B+樹結(jié)構(gòu)，大概的索引如下：

如果要進(jìn)行模糊查找，查找name 以“張"開頭的所有人的ID，即 sql 語句為：

select ID from table where name like '張%'

由于在B+樹結(jié)構(gòu)的索引中，葉子節(jié)點是一個有序的鏈表，當(dāng)我們快速定位到 ID 為 100的張一后，可以直接向右遍歷所有張開頭的人，直到條件不滿足為止。這種定位到最左邊，然后向右遍歷尋找的方式，就是我們所說的最左前綴原則。

只是當(dāng)個索引，在組合索引中更能感受到：

示例：一個(a,b,c)的組合索引。

通過a,b條件查詢能不能使用或命中這個索引？-----能

通過b,c條件查詢能不能使用或命中這個索引？-----不能

原因：索引文件具有B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。

二、為什么用B+樹作索引二不用哈希表作索引

1. 不支持模糊查詢：哈希表是把索引字段映射成對應(yīng)的哈希碼然后再存放在對應(yīng)的位置，這樣的話，如果我們要進(jìn)行模糊查找的話，顯然哈希表這種結(jié)構(gòu)是不支持的，只能遍歷這個表。而B+樹則可以通過最左前綴原則快速找到對應(yīng)的數(shù)據(jù)。

2. 不支持范圍查詢：如果我們要進(jìn)行范圍查找，例如查找ID為100 ~ 400的人，哈希表同樣不支持，只能遍歷全表

3. 哈希沖突，影響查詢效率：索引字段通過哈希映射成哈希碼，如果很多字段都剛好映射到相同值的哈希碼的話，那么形成的索引結(jié)構(gòu)將會是一條很長的鏈表，這樣的話，查找的時間就會大大增加

三、主鍵索引和非主鍵索引的區(qū)別

例如下表(其實就是上面的表中增加了一個k字段)，且ID是主鍵。

主鍵索引和非主鍵索引的示意圖如下：

其中R代表一整行的值，

由圖可以看出，主鍵索引和非主鍵索引的區(qū)別：主鍵索引葉子節(jié)點存放的是整行數(shù)據(jù)，非主鍵索引的葉子節(jié)點存放的是主鍵的值。非主鍵索引也被稱為（二級索引、非聚簇索引），而主鍵索引也被稱為聚簇索引。

1）使用這兩種結(jié)構(gòu)進(jìn)行查詢，看看區(qū)別：

• 如果查詢語句是?select * from table where ID = 100，即主鍵查詢的方式，則只需要搜索 ID 這棵 B+樹。
• 如果查詢語句是?select * from table where k = 1，即非主鍵的查詢方式，則先搜索k索引樹，得到ID=100，再到ID索引樹搜索一次，這個過程也被稱為回表。

四、聚集索引和非聚集索引的區(qū)別

聚集索引和聚簇索引是不同（不知道對不對，我找的資料是這樣說的）

1.?聚集索引：指索引項的排序方式和表中數(shù)據(jù)記錄排序方式一致的索引?

也就是說聚集索引的順序就是數(shù)據(jù)的物理存儲順序。它會根據(jù)聚集索引鍵的順序來存儲表中的數(shù)據(jù)，即對表的數(shù)據(jù)按索引鍵的順序進(jìn)行排序，然后重新存儲到磁盤上。因為數(shù)據(jù)在物理存放時只能有一種排列方式，所以一個表只能有一個聚集索引。

2.?非聚集索引： 索引順序與物理存儲順序不同

五、為什么建議使用自增主鍵作索引

如果主鍵是自增的，每次插入的 ID 都會比前面的大，那么每次只需要在后面插入就行， 不需要移動位置、分裂等操作。從性能和存儲空間方面考量，自增主鍵往往是更合理的選擇。

六、覆蓋索引

select * from T where k between 3 and 5? 這種查詢K的索引搜索到主鍵 然后搜索主鍵的索引 拿到具體的信息有回表
select ID from T where k between 3 and 5 這時只需要查 ID 的值，而 ID 的值已經(jīng)在 k 索引樹上了，因此可以直接提供查詢結(jié)果，不需要回表 由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優(yōu)化手段。
也就是說，你要找的數(shù)據(jù)已經(jīng)在索引上，不需要再回表。

七、一個關(guān)于索引的題

CREATE TABLE `geek` (`a` int(11) NOT NULL,`b` int(11) NOT NULL,`c` int(11) NOT NULL,`d` int(11) NOT NULL,PRIMARY KEY (`a`,`b`),KEY `c` (`c`),KEY `ca` (`c`,`a`),KEY `cb` (`c`,`b`) ) ENGINE=InnoDB;

問題：哪個索引可以去掉？

主鍵 a，b 的聚簇索引組織順序相當(dāng)于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 無序。
索引 ca 的組織是先按 c 排序，再按 a 排序，同時記錄主鍵

所以索引ca 與 索引c 的數(shù)據(jù)是一模一樣的

索引 cb 的組織是先按 c 排序，在按 b 排序，同時記錄主鍵，

所以結(jié)論是ca可以去掉，cb保留。

總結(jié)

以上是生活随笔為你收集整理的MySQL索引的一些问题的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。