什么是索引

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

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

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

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

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

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

B+ Tree索引和Hash索引區(qū)別 哈希索引適合等值查詢，但是不無法進行范圍查詢 哈希索引沒辦法利用索引完成排序 哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則 如果有大量重復鍵值得情況下，哈希索引的效率會很低，因為存在哈希碰撞問題

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

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

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

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

Index Condition Pushdown（索引下推）

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

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

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

查詢優(yōu)化器

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

關(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 做聯(lián)合主鍵。
那意味著，單獨在字段 c 上創(chuàng)建一個索引，就已經(jīng)包含了三個字段，為什么要創(chuàng)建“ca”“cb”這兩個索引？

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

問題：這位同事的解釋對嗎? 哪些索引沒有必要，可以刪除？

答案：

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

–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 排序，同時記錄主鍵。

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

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

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

–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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論