mysql索引實例,mysql索引介紹

本文目錄一覽：

• 1、MySQL——關於索引的總結
• 2、MySQL前綴索引
• 3、「Mysql索引原理（七）」覆蓋索引

MySQL——關於索引的總結

首先說說索引的 優點 ：最大的好處無疑就是提高查詢效率。有的索引還能保證數據的唯一性，比如唯一索引。

而它的 壞處 也很明顯：索引也是文件，我們在創建索引時，也會創建額外的文件，所以會佔用一些硬盤空間。其次，索引也需要維護，我們在增加刪除數據的時候，索引也需要去變化維護。當一個表的索引多了以後，資源消耗是很大的，所以必須結合實際業務再去確定給哪些列加索引。

再說說索引的基本結構。一說到這裡肯定會脫口而出：B+樹！了解B+樹前先要了解二叉查找樹和二叉平衡樹。 二叉查找樹 ：左節點比父節點小，右節點比父節點大，所以二叉查找樹的中序遍歷就是樹的各個節點從小到大的排序。 二叉平衡樹 ：左右子樹高度差不能大於1。B+樹就是結合了它們的特點，當然，不一定是二叉樹。

為什麼要有二叉查找樹的特點？？ 因為查找效率快，二分查找在這種結構下，查找效率是很快的。 那為什麼要有平衡樹的特點呢？ 試想，如果不維護一顆樹的平衡性，當插入一些數據後，樹的形態有可能變得很極端，比如左子樹一個數據沒有，而全在右子樹上，這種情況下，二分查找和遍歷有什麼區別呢？而就是因為這些特點需要去維護，所以就有了上面提到的缺點，當索引很多後，反而增加了系統的負擔。

接著說B+樹。 它的結構如下 ：

可以發現，葉子節點其實是一個 雙向循環鏈表 ，這種結構的好處就是，在範圍查詢的時候，我只用找到一個數據，就可以直接返回剩餘的數據了。比如找小於30的，只用找到30，其餘的直接通過葉子節點間的指針就可以找到。再說說其他特點： 數據只存在於葉子節點 。當葉子節點滿了，如果再添加數據，就會拆分葉子節點，父節點就多了個子節點。如果父節點的位置也滿了，就會擴充高度，就是拆分父節點，如25 50 75拆分成：25為左子樹，75為右子樹，50變成新的頭節點，此時B+樹的高度變成了3。它們的擴充的規律如下表，Leaf Page是葉子節點，index Page是非葉子節點。

再說說B樹 ，B樹相比較B+樹，它所有節點都存放數據，所以在查找數據時，B樹有可能沒到達葉子節點就結束了。再者，B樹的葉子節點間不存在指針。

最後說說Hash索引 ，相較於B+樹，Hash索引最大的優點就是查找數據快。但是Hash索引最大的問題就是不支持範圍查詢。試想，如果查詢小於30的數據，hash函數是根據數據的值找到其對應的位置，誰又知道小於30的有哪幾個數據。而B+樹正好相反，範圍查詢是它的強項。

附錄： Hash到底是啥？？ 哈希中文名散列，哈希只是它的音譯。 為啥都說Hash快？？ 首先有一塊哈希表（散列表），它的數據結構是個數組，一個任意長度的數據通過hash函數都可以變成一個固定長度的數據，叫hash值。然後通過hash值確定在數組中的位置，相同數據的hash值是相同的，所以我們存儲一個數據以後，只需O(1)的時間複雜度就可以找到數據。 那hash函數又是啥？？ 算術運算或位運算，很多應用里都有hash函數，但實際運算過程大不一樣。這是Java里String的hashCode方法：

publicint hashCode() {

}

還有一個問題，hash函數計算出來的hash值有可能存在碰撞，即兩個不同的數據可能存在相同的hash值，在MySQL或其他的應用中，如Java的HashMap等，如果存在碰撞就會以當前數組位置為頭節點，轉變成一個鏈表。

說到這裡也清楚了為啥Java中引用類型要同時重寫hashCode和equals了。兩個對象，實例就算一模一樣，它們的hash值也不相等， 為啥不相等？？ 默認的Object的hashCode方法會根據對象來計算hash值的，實例相同，但它們還是兩個不同的對象啊，所以我們重寫hashCode時，最簡單的方法就是調用Object的hashCode方法，然後傳入該引用類型的屬性，讓hashCode方法只根據這幾個屬性來計算，那麼實例相同的話，它們的hash值也會相等。等hashCode比較完後，如果相等再比較實例內容，也就是equals，確保不是hash碰撞。

索引的分類

如果我們指定了一個主鍵，那麼這個主鍵就是主鍵索引。如果我們沒有指定，Mysql就會自動找一個非空的唯一索引當主鍵。如果沒有這種字段，Mysql就會創建一個大小為6字節的自增主鍵。如果有多個非空的唯一索引，那麼就讓第一個定義為唯一索引的字段當主鍵，注意，是第一個定義，而不是建表時出現在前面的。

對於輔助索引來說，它們的B+樹結構稍微有點特殊，它們的葉子節點存儲的是主鍵，而不是整個數據。所以在大部分情況下，使用輔助索引查找數據，需要二次查找。但並不是所有情況都需要二次查找。比如查找的數據正好就是當前索引字段的值，那麼直接返回就行。這裡提一句，B+樹的key就是對應索引字段的內容。

而輔助索引又有一些分類：唯一索引：不能出現重複的值，也算一種約束。普通索引：可以重複、可以為空，一般就是查詢時用到。前綴索引：只適用於字符串類型數據，對字符串前幾個字符創建索引。全文索引：作用是檢測大文本數據中某個關鍵字，這也是搜索引擎的一種技術。

注意，聚集索引、非聚集索引和前面幾個索引的分類並不是一個層面上的。上面的幾個分類是從索引的作用來分析的。聚集、非聚集索引是從索引文件上區分的。主鍵索引就屬於聚集索引，即索引和數據存放在一起，葉子節點存放的就是數據。數據表的.idb文件就是存放該表的索引和數據。

輔助索引屬於非聚集索引，說到這也就明白了。索引和數據不存放在一起的就是非聚集索引。在MYISAM引擎中，數據表的.MYI文件包含了表的索引， 該表的 葉子節點存儲索引和索引對應數據的指針，指向.MYD文件的數據。

索引的幾點使用經驗

經常被查詢的字段；經常作為條件查詢的字段；經常用於外鍵連接或普通的連表查詢時進行相等比較字段；不為null的字段；如果是多條件查詢，最好創建聯合索引，因為聯合索引只有一個索引文件。

經常被更新的字段、不經常被查詢的字段、存在相同功能的字段

MySQL前綴索引

前綴索引顧名思義，定義字符串的一部分當做索引，而不是把整個字符串當做索引。默認地，如果你創建索引的語句不指定前綴長度，那麼索引就會包含整個字符串。

假設一張表有 id,name,email 2個字段

1.創建email列的普通索引應該是： alter table T add index idx_email1( email )

2.前綴索引的創建規則為： alter table table T add index idx_email2( email(6) )

當然第一索引包含是的整個字符串，第二個是該字段前6個字節（注意是字節）

對於這2中索引，B+樹怎麼存儲呢？

INSERT INTO T (email) VALUES (‘瞎子’,’zhangsh1234@163.com’), (‘劍聖’,’lisi1998883@163.com’), (‘露娜’,’zhangssxyz@163.com’), (‘李白’,’zhangsy1998@163.com’), (‘韓信’,’zhaq5481993@163.com’), (‘百里玄策’,’hhaq5481993@163.com’);

【誰還不是個野王啊】

普通索引存儲為：

是的你沒看錯，前綴索引那顆樹上的存儲的是email的前6位字節，也就是你創建前綴索引時指定的前綴字節長度。2種樹相比，前綴索引存儲了更少的數據，那麼他所耗費的空間也就相比較少，這正是他的一個優點。同樣的也就相對的增加了掃描行數。

什麼增加了掃描行數？？？？ 這是為什麼呢？

那麼小朋友咱們一起來看下吧。

假設SQL如此這般： select id,name,email from T where email = ‘zhangsh1234@163.com’

那麼這2個SQL，應該怎麼操作呢。

idx_email1:

2.到主鍵上查到主鍵為ID1的，判斷email值是否正確【為什麼判斷呢，其實我理解是為了二次判斷保證數據一致性吧，比較官方的解釋尚未找到】，正確放入結果集

3.取 idx_email1 索引樹上剛剛查到的位置的下一條記錄，如此往複。

循環過程中，需要回主鍵取1次數據，所以系統可以認為只掃描了一行【1次是數第一棵樹數出來的】

idx_email2:

1.從 索引數上找到滿足索引值為 ‘zhangs’的該記錄，取得 ID1的值

2.到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值是’ zhangsh1234@xxx.com ’，這行記錄放入結果集【不是要的值，丟棄，進行下一步】

3.取 idx_email2 上剛剛查到的位置的下一條記錄,重複以上步驟

在這個過程中，要回主鍵索引取 3 次數據，也就是掃描了 3 行。通過這個對比，你很容易就可以發現，使用前綴索引後，可能會導致查詢語句讀數據的次數變多。

但是，對於這個查詢語句來說，如果你定義的 idx_email2 不是 email(6) 而是 email(8），也就是說取 email 字段的前 8 個字節來構建索引的話，即滿足前綴’zhangsh’的記錄只有一個，也能夠直接查到 ID1，只掃描一行就結束了。也就是說使用前綴索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。

那麼問題來了，到底定義多長才算是合理呢？

一般的定義原則是 count(distinct(columnName))/count(*) ,當前綴索引【count(distinct(columnName(length)))，length是你想要創建列的前綴字節長度】越接近此值越好，當有多個前綴字節都一樣且都等於這個值時怎麼選擇呢，當然是 字節越少越好了哈，字節越少越省空間。索引選取的越長，佔用的磁盤空間就越大，相同的數據頁能放下的索引值就越少，搜索的效率也就會越低。

count(distinct(columnName(length))) 翻譯到SQL 為： count(dictinct(left(colunmName, length)))

前面我們說了使用前綴索引可能會增加掃描行數，這會影響到性能。其實，前綴索引的影響不止如此，我們再看一下另外一個場景。

來呀，上SQL： select id,email from T where email=’zhangsh1234@163.com’

如果按照email全字段索引，那麼此SQL 是不需要回表的【為什麼不需要回表？兄嘚，這個相當於覆蓋索引了哈】

那麼如果按照前綴索引是否需要回表呢？答案是的。

因為當判斷前6個字節相等後，需要拿到id 回表拿到email的全部內容進行比較，如果不相同，丟棄這行，否則加入結果集。

那麼有人會問了，我把長度放大點，包含所有字節不就好了嗎？

那麼此時會有如下問題。

1.當你此時的長度是囊括了全字段，但是系統是不知道的，他還是需要回表再次判斷的，去確定前綴索引的定義是否截斷了完整信息。

2.此時長度是夠了，那麼能肯定因為業務日後不會增加長度嗎？

3.儘可能的加長長度，還不如直接建立全字段索引呢

綜上，使用前綴索引就用不上覆蓋索引對查詢性能的優化了，這也是你在選擇是否使用前綴索引時需要考慮的一個因素。

前面說到的是，可以根據字段前面幾個字節進行查詢的，那麼對於身份證這種，一共 18 位，其中前 6 位是地址碼，所以同一個縣的人的身份證號前 6 位一般會是相同的。

或許你會說，多弄幾個字節不就好嗎？那麼請問下自己為什麼使用前綴索引呢，不就是為了節省空間嗎？

那麼這麼做合適嗎？ 不合適對嗎？ 乖~，快去反省下吧

那麼採用前綴索引顯示是不行的，那麼如果用前綴索引怎麼辦呢，聰明的你應該已經猜到了，採用倒敘存儲，然後建立前綴索引。

放到SQL 中就應該是這樣的： select field_list from t where id_card = reverse(‘id_card_string’);

當然了，這種邏輯建議放到業務邏輯中實現，而不是放到SQL 中。

按照上述第4節的內容，有人或許會有另一個想法，還倒敘建立前綴索引複雜不，hash索引或者hash字段不香嗎？

有人會問了，為什麼要在創建一個值來存儲hash值呢，如果不存儲你知道原值是什麼嗎？ 同時hash算法是有一定重複可能的（hash值碰撞）

【可以了解下partition算法哦:[ 】。如果重複了，不存儲原值，你是無法判斷出正確數據的。

註：【hash字段不代表hash索引，hash索引原理正在快馬加鞭】，簡單說下hash索引，hash索引不需要創建一個值來存儲hash值，而是有hasn表來存儲【hash值碰撞時，由一個鏈表來搞定了】，存儲的內容為 hash值和每行的行指針 。

說回來啊，跑題了

查詢時： select field_list from t where id_card_crc=crc32(‘id_card_string’) and id_card=’id_card_string’

不過有個問題相信你也想到了，不管是hash存儲值還是hash索引都是不支持範圍查詢的。

來總結下這2個優缺點吧

1.從佔用空間來看呢，倒敘索引不需要額外開闢存儲空間，而hash字段需要額外的一個字段，所以從這點上看倒敘索引更勝一籌，NO!並不準確,如果前綴長度過長，那麼這2個情況額外的空間也就相差無幾了

3.從查詢效率上看，使用 hash 字段方式的查詢性能相對更穩定一些。因為 crc32 算出來的值雖然有衝突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數接近 1。而倒序存儲方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數

1.全字段完整索引比較占空間，但是而走覆蓋索引

2.前綴索引，節省空間，但會增加掃描 次數 並且不能使用覆蓋索引【每次都需回表校驗】

3.倒序存儲，再創建前綴索引，用於繞過字符串本身前綴的區分度不夠的問題。【倒敘方法建立放到業務邏輯中】

4.hash字段索引，相比前綴索引性能較為穩定，但是有額外的存儲空間和計算消耗，同時也 不 支持範圍查詢

「Mysql索引原理（七）」覆蓋索引

       通常大家都會根據查詢的WHERE條件來創建合適的索引，不過這只是索引優化的一個方面。設計優秀的索引應該考慮到整個查詢，而不單單是WHERE條件部分。索引確實是一種查找數據的高效方式，但是MySQL也可以使用索引來直接獲取列的數據，這樣就不再需要讀取數據行。如果索引的葉子節點中已經包含要查詢的數據，那麼還有什麼必要再回到表中查詢呢？ 如果一個索引覆蓋所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。

覆蓋索引是非常有用的工具，能夠極大地提高性能：

       在所有這些場景中，在索引中滿足查詢的成本一般比查詢行要小得多。

       不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引都不存儲索引列的值，所以MySQL只能使用B+Tree索引所覆蓋索引。另外，不同的存儲引擎實現覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。

       當發起一個唄索引覆蓋的查詢是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

       索引覆蓋查詢還有很多陷阱可能會導致無法實現優化。MySQL查詢優化器會在執行查詢前判斷是否有一個索引能進行覆蓋。假設索引覆蓋了wehre條件中的字段，但不是整個查詢涉及的字段。mysql5.5和更早的版本也總是會回表獲取數據行，儘管並不需要這一行且最終會被過濾掉。

如： EXPLAIN select * from people where last_name=’Allen’ and first_name like ‘%Kim%’

這裡索引無法覆蓋該查詢，有兩個原因：

這條語句只檢索1行，而之前的 like ‘%Kim%’要檢索3行。

也有辦法解決上面所說的兩個問題，需要重寫查詢並巧妙設計索引。

       這種方式叫做延遲關聯，因為延遲了對列的訪問。在查詢第一個階段MySQL可以使用覆蓋索引，因為索引包含了主鍵id的值，不需要做二次查找。

       在FROM子句的子查詢中找到匹配的id，然後根據這些id值在外層查詢匹配獲取需要的所有列值。雖然無法使用索引覆蓋整個查詢，但總算比完全無法利用索引覆蓋的好吧。

數據量大了怎麼辦？

       這樣優化的效果取決於WHERE條件匹配返回的行數。假設這個people表有100萬行，我們看一下上面兩個查詢在三個不同的數據集上的表現，每個數據集都包含100萬行。

實例1中 ，查詢返回了一個很大的結果集，因此看不到優化的效果。大部分時間都花在讀取和發送數據上了。

實例2中 ，經過索引過濾，尤其是第二個條件過濾後只返回了很少的結果集，優化的效果非常明顯：在這個數據及上性能提高了很多，優化後的查詢效率主要得益於只需讀取40行完整數據行，而不是原查詢中需要的30000行。

實例3中 ，子查詢效率反而下降。因為索引過濾時符合第一個條件的結果集已經很小了，所以子查詢帶來的成本反而比從表中直接提取完整行更高。

       在大多數存儲引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以更進一步優化InnoDB。回想一下，InnoDB的二級索引的葉子節點都包含了主鍵的值，這意味着InnoDB的二級索引可以有效地利用這些額外的主鍵列來覆蓋查詢。

       例如，people表中last_name字段有一個二級索引，雖然該索引的列不包括主鍵id，但也能夠用於對id做覆蓋查詢：

select id,last_name from people where last_name=’hua’