淺談Mysql主鍵索引與非主鍵索引區別

Posted on by

什麼是索引

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主鍵索引與非主鍵索引內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：