Mysql索引：為什麼使用索引，索引方式，索引類型，缺點和注意事項

一.為什麼使用索引:加快查詢的速度

MySQL 資料庫存儲資料最終是以檔案的形式存儲到硬碟的。一般來說，我們在程式中使用的時候肯定要把磁盤檔案中的資料讀到記憶體中。那麼就這個 “讀” 的過程是什麼樣子的呢？磁盤讀取資料靠的是機械運動，每次讀取資料花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分，尋道時間指的是磁臂移動到指定磁道所需要的時間，主流磁盤的尋道時間一般在5ms以下；旋轉延遲就是我們經常聽說的磁盤轉速，比如一個磁盤7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120/2 = 4.17ms（旋轉延遲等于磁盤轉動半圈時間）；傳輸時間指的是從磁盤讀出或将資料寫入磁盤的時間，一般在零點幾毫秒，相對于前兩個時間可以忽略不計。那麼通路一次磁盤的時間，即一次磁盤IO的時間約等于5+4.17 = 9ms左右，聽起來還挺不錯的，但要知道一台 500 - MIPS 的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行40萬條指令（如果以 CPU 的指令執行效率來比較的話），資料庫動辄十萬百萬乃至千萬級資料，每次9毫秒的時間，顯然是個災難.

是以，問題的症結就在于磁盤 IO 是非常高昂的操作。

解決方案：

①：計算機作業系統做了一些優化，當一次IO時，不光把目前磁盤位址的資料，而是把相鄰的資料也都讀取到記憶體緩沖區内，因為局部預讀性原理告訴我們，當計算機通路一個位址的資料的時候，與其相鄰的資料也會很快被通路到。每一次IO讀取的資料我們稱之為一頁(page)。具體一頁有多大資料跟作業系統有關，一般為4k或8k，也就是我們讀取一頁内的資料時候，實際上才發生了一次IO（一次 IO 的資料包括目前要讀取的磁盤位址的資料+與之相鄰的資料），這個理論對于索引的資料結構設計非常有幫助。

②：每次查找資料時把磁盤IO次數控制在一個很小的數量級

很明顯：第①種解決方案是系統已經提供好的。要想實作第二種解決方案就需要一種穩定的資料結構能夠滿足幾乎每次查詢資料進行磁盤的 IO 次數是很少的。這個條件可以解釋為：每次查詢資料進行的 IO 次數都很少，說明這個資料結構不能像紅黑樹一樣樹的高度不可控，于是一個高度可控的多路搜尋樹就産生了，這就是 B + 樹。

那麼B + 樹的每一個節點究竟存的是什麼？以一張表的 id 列為例，也就是主鍵列如果是索引的話，那麼這張表的每一個 id 都會以 B+ 樹的每一個節點存儲到 B+ 樹上，如果資料過多，一個節點就會存儲多個 id

二.索引方式: hash和b+tree

1.B+樹的資料結構

B+樹

B+ 樹是一種樹資料結構，通常用于資料庫和作業系統的檔案系統中。B+ 樹的特點是能夠保持資料穩定有序，其插入與修改擁有較穩定的對數時間複雜度。B+ 樹元素自底向上插入，這與二叉樹恰好相反。

B+ 樹在節點通路時間遠遠超過節點内部通路時間的時候，比可作為替代的實作有着實在的優勢。這通常在多數節點在次級存儲比如硬碟中的時候出現。通過最大化在每個内部節點内的子節點的數目減少樹的高度，平衡操作不經常發生，而且效率增加了。這種價值得以确立通常需要每個節點在次級存儲中占據完整的磁盤塊或近似的大小。

B+ 背後的想法是内部節點可以有在預定範圍内的可變量目的子節點。是以，B+ 樹不需要像其他自平衡二叉查找樹那樣經常的重新平衡。對于特定的實作在子節點數目上的低和高邊界是固定的。例如，在 2-3 B 樹（常簡稱為2-3 樹）中，每個内部節點隻可能有 2 或 3 個子節點。如果節點有無效數目的子節點則被當作處于違規狀态。

B+ 樹的創造者 Rudolf Bayer 沒有解釋B代表什麼。最常見的觀點是B代表平衡(balanced)，因為所有的葉子節點在樹中都在相同的級别上。B也可能代表Bayer，或者是波音（Boeing），因為他曾經工作于波音科學研究實驗室。

2.Hash 索引

Hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree 索引需要從根節點到枝節點，最後才能通路到頁節點這樣多次的IO通路，是以 Hash 索引的查詢效率要遠高于 B-Tree 索引。

可能很多人又有疑問了，既然 Hash 索引的效率要比 B-Tree 高很多，為什麼大家不都用 Hash 索引而還要使用 B-Tree 索引呢？任何事物都是有兩面性的，Hash 索引也一樣，雖然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也帶來了很多限制和弊端，主要有以下這些。

（1）Hash 索引僅僅能滿足"=","IN"和"<=>"查詢，不能使用範圍查詢。

由于 Hash 索引比較的是進行 Hash 運算之後的 Hash 值，是以它隻能用于等值的過濾，不能用于基于範圍的過濾，因為經過相應的 Hash 算法處理之後的 Hash 值的大小關系，并不能保證和Hash運算前完全一樣。

（2）Hash 索引無法被用來避免資料的排序操作。

由于 Hash 索引中存放的是經過 Hash 計算之後的 Hash 值，而且Hash值的大小關系并不一定和 Hash 運算前的鍵值完全一樣，是以資料庫無法利用索引的資料來避免任何排序運算；

（3）Hash 索引不能利用部分索引鍵查詢。

對于組合索引，Hash 索引在計算 Hash 值的時候是組合索引鍵合并後再一起計算 Hash 值，而不是單獨計算 Hash 值，是以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用。

（4）Hash 索引在任何時候都不能避免表掃描。

前面已經知道，Hash 索引是将索引鍵通過 Hash 運算之後，将 Hash運算結果的 Hash 值和所對應的行指針資訊存放于一個 Hash 表中，由于不同索引鍵存在相同 Hash 值，是以即使取滿足某個 Hash 鍵值的資料的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要通過通路表中的實際資料進行相應的比較，并得到相應的結果。

（5）Hash 索引遇到大量Hash值相等的情況後性能并不一定就會比B-Tree索引高。

對于選擇性比較低的索引鍵，如果建立 Hash 索引，那麼将會存在大量記錄指針資訊存于同一個 Hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的通路，而造成整體性能低下

三. MySQL索引類型

1.從功能上分類可以分為：

1).普通索引

是最基本的索引，它沒有任何限制,就是為了加快搜尋速度

2).唯一索引

與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一

3).組合索引

指多個字段上建立的索引，隻有在查詢條件中使用了建立索引時的第一個字段，索引才會被使用。使用組合索引時遵循最左字首集合.

4).全文索引

主要用來查找文本中的關鍵字，而不是直接與索引中的值相比較。fulltext索引跟其它索引大不相同，它更像是一個搜尋引擎，而不是簡單的where語句的參數比對。fulltext索引配合match against操作使用，而不是一般的where語句加like。它可以在create table，alter table ，create index使用，不過目前隻有char、varchar，text 列上可以建立全文索引。值得一提的是，在資料量較大時候，現将資料放入一個沒有全局索引的表中，然後再用CREATE index建立fulltext索引，要比先為一張表建立fulltext然後再将資料寫入的速度快很多。

2.從空間上分類可以分為:

1).聚集索引

資料行的實體順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中隻能擁有一個聚集索引。

2).非聚集索引

該索引中索引的邏輯順序與磁盤上行的實體存儲順序不同，一個表中可以擁有多個非聚集索引。

四、缺點

1.雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行insert、update和delete。因為更新表時，不僅要儲存資料，還要儲存一下索引檔案。

2.建立索引會占用磁盤空間的索引檔案。一般情況這個問題不太嚴重，但如果你在一個大表上建立了多種組合索引，索引檔案的會增長很快。

索引隻是提高效率的一個因素，如果有大資料量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句。

五、注意事項

使用索引時，有以下一些技巧和注意事項：

1.索引不會包含有null值的列

隻要列中包含有null值都将不會被包含在索引中，複合索引中隻要有一列含有null值，那麼這一列對于此複合索引就是無效的。是以我們在資料庫設計時不要讓字段的預設值為null。

2.使用短索引

對串列進行索引，如果可能應該指定一個字首長度。例如，如果有一個char(255)的列，如果在前10個或20個字元内，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

3.索引列排序

查詢隻使用一個索引，是以如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。是以資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

4.like語句操作

一般情況下不推薦使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

5.不要在列上進行運算

這将導緻索引失效而進行全表掃描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;
      

6.不使用not in和<>操作