淺談Mysql主鍵索引與非主鍵索引區(qū)別

什么是索引

MySql官方索引的定義：索引(Index)是幫助MySql高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，索引的目的在于提高查詢(xún)效率，類(lèi)比字典；實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，索引列也是要占用空間。

主鍵索引和普通索引的區(qū)別

1.主鍵索引索引著數(shù)據(jù)，然而普通索引索引著主鍵ID值(這是在innodb中，但是如果是myisam中，主鍵索引和普通索引是沒(méi)有區(qū)別的都是直接索引著數(shù)據(jù))
2.當(dāng)你查詢(xún)用的是where id=x 時(shí)，那只需要掃描一遍主鍵索引，然后拿到相應(yīng)數(shù)據(jù)，但是如果是查詢(xún)的普通索引的話(huà)，那么會(huì)先掃描一次普通索引，拿到主鍵值，然后再去掃主鍵索引，拿到所需要的數(shù)據(jù)，這個(gè)過(guò)程叫做回表

索引具體采用的哪種數(shù)據(jù)結(jié)構(gòu)

常見(jiàn)的MySQL主要有兩種結(jié)構(gòu)：Hash索引和B+ Tree索引，我們使用的是InnoDB引擎，默認(rèn)的是B+樹(shù)

InnoDB使用的B+ Tree的索引模型，那么為什么采用B+ 樹(shù)？這和Hash索引比較起來(lái)有什么優(yōu)缺點(diǎn)？

B+ Tree索引和Hash索引區(qū)別 哈希索引適合等值查詢(xún)，但是不無(wú)法進(jìn)行范圍查詢(xún) 哈希索引沒(méi)辦法利用索引完成排序 哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則 如果有大量重復(fù)鍵值得情況下，哈希索引的效率會(huì)很低，因?yàn)榇嬖诠Ｅ鲎矄?wèn)題

B+ Tree的葉子節(jié)點(diǎn)都可以存哪些東西?

在 InnoDB 里，索引B+ Tree的葉子節(jié)點(diǎn)存儲(chǔ)了整行數(shù)據(jù)的是主鍵索引，也被稱(chēng)之為聚簇索引。而索引B+ Tree的葉子節(jié)點(diǎn)存儲(chǔ)了主鍵的值的是非主鍵索引，也被稱(chēng)之為非聚簇索引。

聚簇索引和非聚簇索引，在查詢(xún)數(shù)據(jù)的時(shí)候有區(qū)別？

聚簇索引查詢(xún)會(huì)更快
因?yàn)橹麈I索引樹(shù)的葉子節(jié)點(diǎn)直接就是我們要查詢(xún)的整行數(shù)據(jù)了。而非主鍵索引的葉子節(jié)點(diǎn)是主鍵的值，查到主鍵的值以后，還需要再通過(guò)主鍵的值再進(jìn)行一次查詢(xún),這個(gè)過(guò)程稱(chēng)作回表。
非主鍵索引一定會(huì)查詢(xún)多次嗎？
覆蓋索引也可以只查詢(xún)一次，覆蓋索引（covering index）指一個(gè)查詢(xún)語(yǔ)句的執(zhí)行只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取。也可以稱(chēng)之為實(shí)現(xiàn)了索引覆蓋。 當(dāng)一條查詢(xún)語(yǔ)句符合覆蓋索引條件時(shí)，MySQL只需要通過(guò)索引就可以返回查詢(xún)所需要的數(shù)據(jù)，這樣避免了查到索引后再返回表操作，減少I(mǎi)/O提高效率。 如，表covering_index_sample中有一個(gè)普通索引 idx_key1_key2(key1,key2)。當(dāng)我們通過(guò)SQL語(yǔ)句：select key2 from covering_index_sample where key1 = ‘keytest';的時(shí)候，就可以通過(guò)覆蓋索引查詢(xún)，無(wú)需回表。

Index Condition Pushdown（索引下推）

MySQL 5.6引入了索引下推優(yōu)化，默認(rèn)開(kāi)啟，使用SET optimizer_switch = ‘index_condition_pushdown=off';可以將其關(guān)閉。官方文檔中給的例子和解釋如下： people表中（zipcode，lastname，firstname）構(gòu)成一個(gè)索引

WHERE zipcode=‘95054' AND lastname LIKE ‘%etrunia%' AND address LIKE ‘%Main Street%';

如果沒(méi)有使用索引下推技術(shù)，則MySQL會(huì)通過(guò)zipcode='95054'從存儲(chǔ)引擎中查詢(xún)對(duì)應(yīng)的數(shù)據(jù)，返回到MySQL服務(wù)端，然后MySQL服務(wù)端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來(lái)判斷數(shù)據(jù)是否符合條件。 如果使用了索引下推技術(shù)，則MYSQL首先會(huì)返回符合zipcode='95054'的索引，然后根據(jù)lastname LIKE '%etrunia%'篩選出符合條件的索引后再返回到MySQL服務(wù)端，然后MySQL服務(wù)端基于address LIKE '%Main Street%'來(lái)判斷數(shù)據(jù)是否符合條件，這樣返回給MySQL服務(wù)端的索引數(shù)又會(huì)減少。有了索引下推優(yōu)化，可以在有l(wèi)ike條件查詢(xún)的情況下，減少回表次數(shù)。

查詢(xún)優(yōu)化器

一條SQL語(yǔ)句的查詢(xún)，可以有不同的執(zhí)行方案，至于最終選擇哪種方案，需要通過(guò)優(yōu)化器進(jìn)行選擇，選擇執(zhí)行成本最低的方案。 在一條單表查詢(xún)語(yǔ)句真正執(zhí)行之前，MySQL的查詢(xún)優(yōu)化器會(huì)找出執(zhí)行該語(yǔ)句所有可能使用的方案，對(duì)比之后找出成本最低的方案。這個(gè)成本最低的方案就是所謂的執(zhí)行計(jì)劃。 優(yōu)化過(guò)程大致如下： 1、根據(jù)搜索條件，找出所有可能使用的索引 2、計(jì)算全表掃描的代價(jià) 3、計(jì)算使用不同索引執(zhí)行查詢(xún)的代價(jià) 4、對(duì)比各種執(zhí)行方案的代價(jià)，找出成本最低的那一個(gè)

關(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;

由于歷史原因，這個(gè)表需要 a、b 做聯(lián)合主鍵。
那意味著，單獨(dú)在字段 c 上創(chuàng)建一個(gè)索引，就已經(jīng)包含了三個(gè)字段，為什么要?jiǎng)?chuàng)建“ca”“cb”這兩個(gè)索引？

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

問(wèn)題：這位同事的解釋對(duì)嗎? 哪些索引沒(méi)有必要，可以刪除？

答案：

（1） 主鍵 a，b 的聚簇索引組織順序相當(dāng)于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 無(wú)序。

–a--|–b--|–c--|–d--
1 2 3 d
1 3 2 d
1 4 3 d
2 1 3 d
2 2 2 d
2 3 4 d

（2） 索引 ca 的組織是先按 c 排序，再按 a 排序，同時(shí)記錄主鍵。

–c--|–a--|–主鍵部分b-- （注意，這里不是 ab，而是只有 b）
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

上面的這個(gè)索引ca 與索引c 的數(shù)據(jù)是一模一樣的。

（3） 索引 cb 的組織是先按 c 排序，在按 b 排序，同時(shí)記錄主鍵

–c--|–b--|–主鍵部分a-- （同上）
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2

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

到此這篇關(guān)于淺談Mysql主鍵索引與非主鍵索引區(qū)別的文章就介紹到這了,更多相關(guān)Mysql主鍵索引與非主鍵索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！