什么是索引

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

主鍵索引和普通索引的區別

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

索引具體采用的哪種數據結構

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

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

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

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

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

聚簇索引和非聚簇索引，在查詢數據的時候有區別？

聚簇索引查詢會更快
因為主鍵索引樹的葉子節點直接就是我們要查詢的整行數據了。而非主鍵索引的葉子節點是主鍵的值，查到主鍵的值以后，還需要再通過主鍵的值再進行一次查詢,這個過程稱作回表。
非主鍵索引一定會查詢多次嗎？
覆蓋索引也可以只查詢一次，覆蓋索引（covering index）指一個查詢語句的執行只用從索引中就能夠取得，不必從數據表中讀取。也可以稱之為實現了索引覆蓋。 當一條查詢語句符合覆蓋索引條件時，MySQL只需要通過索引就可以返回查詢所需要的數據，這樣避免了查到索引后再返回表操作，減少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引入了索引下推優化，默認開啟，使用SET optimizer_switch = ‘index_condition_pushdown=off';可以將其關閉。官方文檔中給的例子和解釋如下： people表中（zipcode，lastname，firstname）構成一個索引

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

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

查詢優化器

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

關于索引的題

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 做聯合主鍵。
那意味著，單獨在字段 c 上創建一個索引，就已經包含了三個字段，為什么要創建“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 的數據是一模一樣的。

（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

所以結論是ca可以去掉，cb保留。

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