MYSQL 索引相关
為什么需要索引（Why is it needed）?
當數(shù)據(jù)保存在磁盤類存儲介質(zhì)上時，它是作為數(shù)據(jù)塊存放。這些數(shù)據(jù)塊是被當作一個整體來訪問的，這樣可以保證操作的原子性（原子性是指一個完整的事務程序，要么成功，要么失敗回滾）。硬盤數(shù)據(jù)塊存儲結構類似于鏈表，都包含數(shù)據(jù)部分，以及一個指向下一個節(jié)點（或數(shù)據(jù)塊）的指針，不需要連續(xù)存儲。
記錄集只能在某個關鍵字段上進行排序，所以如果需要在一個無序字段上進行搜索，就要執(zhí)行一個線性搜索（Linear Search）的過程，平均需要訪問N/2的數(shù)據(jù)塊，N是表所占據(jù)的數(shù)據(jù)塊數(shù)目。如果這個字段是一個非主鍵字段（也就是說，不包含唯一的訪問入口），那么需要在N個數(shù)據(jù)塊上搜索整個表格空間。
但是對于一個有序字段，可以運用二分查找（Binary Search），這樣只要訪問log2 (N)的數(shù)據(jù)塊。這就是為什么性能能得到本質(zhì)上的提高。
?
什么是索引（What is indexing）?
索引是對記錄集的多個字段進行排序的方法。在一張表中為一個字段創(chuàng)建一個索引，將創(chuàng)建另外一個數(shù)據(jù)結構，包含字段數(shù)值以及指向相關記錄的指針，然后對這個索引結構進行排序，允許在該數(shù)據(jù)上進行二分法查找。
二分法排序詳解可查看此連接http://blog.csdn.net/zimuxin/article/details/78475719
副作用是索引需要額外的磁盤空間，對于MyISAM引擎而言，這些索引是被統(tǒng)一保存在一張表中的，如果很多字段都建立了索引的話，這個文件將很快到達底層文件系統(tǒng)所能夠支持的大小限制。
?
索引如何工作（How does it work?）
首先，我們建立一個示范數(shù)據(jù)庫表：
字段名 ? ? ? 數(shù)據(jù)類型 ? ??大小
id (Primary key) Unsigned INT ? 4 bytes
firstName ? ? ? ?Char(50)? ? ? 50 bytes
lastName ? ? ? ? Char(50)? ? ? 50 bytes
emailAddress ? ? Char(100) ?? ?100 bytes
注意：使用char是為了指定準確的磁盤占用大小。這個示范數(shù)據(jù)庫包含500萬行，而且沒有索引。我們將分析一些查詢語句的性能，一個是使用主鍵id（有序）查詢，一個是使用firstName（非關鍵無序字段）。
例1
我們的示范數(shù)據(jù)庫有r=5,000,000條記錄，每條記錄長度R=204字節(jié)而且使用MyISAM引擎存儲（默認數(shù)據(jù)塊大小為B=1024字節(jié)），這張表的塊因子（blocking factor）會是bfr = (B/R) = 1024/204 = 5條記錄每磁盤數(shù)據(jù)塊。保存這張表所需要的磁盤塊為N = (r/bfr) = 5000000/5 =1,000,000 blocks。
在id字段上的線性搜索平均需要N/2 = 500,000塊訪問來找到一條記錄假設id字段是查詢關鍵值，不過既然id字段是有序的，可以執(zhí)行一個二分查詢，這樣平均只需要訪問log2 (1000000)= 19.93 = 20 個數(shù)據(jù)塊。我們馬上就看到了極大的提高。
現(xiàn)在firstName字段既不是有序的，無法執(zhí)行二分搜索，數(shù)值也不具有唯一性，所以對這張表的查找必須到最后一個記錄即全表掃描N = 1,000,000個數(shù)據(jù)塊訪問。這就是索引用來改進的地方。
假如索引記錄只包含一個索引列以及一個指向原記錄數(shù)據(jù)的指針，那么它顯而易見會比原記錄（多列）要小。所以索引本身所需要的磁盤塊要更少，掃描數(shù)目也少。firstName索引表結構如下：
Field name ? ? ? Data type ?? ?Size on disk
firstName ? ? ? ?Char(50)? ? ? 50 bytes
(record pointer) Special ? ? ??4 bytes
注意:MySQL里的指針按表大小的不同分別可能是 2, 3, 4 或5 個字節(jié)。
例2
假設我們的數(shù)據(jù)庫有r = 5,000,000 條記錄，建立了一個長R = 54字節(jié)的索引，并且使用默認磁盤塊大小為1,024字節(jié)。那么該索引的塊因子為bfr = (B/R) = 1024/54 = 18條記錄每磁盤塊。容納這個索引表總共需要的磁盤塊為N = (r/bfr) = 5000000/18 =277,778 塊。
現(xiàn)在使用FirstName字段來進行搜索就可以利用索引來提高性能。這允許使用一個二分查找，平均log2(277778) = 18.08 -> 19次數(shù)據(jù)塊訪問。找到實際記錄的地址，這需要進一步的塊讀取，這樣總數(shù)達到19 + 1 = 20次數(shù)據(jù)塊訪問，這和非索引表的數(shù)據(jù)塊訪問次數(shù)有天壤之別。
?
什么時候使用索引（When should it be used?）
鑒于創(chuàng)建索引需要額外的磁盤空間（上面的例子需要額外的277778個磁盤塊），以及太多的索引會導致文件系統(tǒng)大小限制所產(chǎn)生的問題，所以對哪些字段建立索引，什么情況下使用索引，需要審慎考慮。
比如唯一性太差的字段不用加，比如SEX
還有字段經(jīng)常改變的，也不太建議加
由于索引只是用來加速數(shù)據(jù)查詢，那么顯然對只是用來輸出的字段建立索引會浪費磁盤空間以及發(fā)生插入、刪除操作時的處理時間，所以這種情況下應該盡量避免。此外鑒于二分搜索的特性，數(shù)據(jù)的基數(shù)或獨立性是很重要的。在基數(shù)為2的字段上建立索引，將把數(shù)據(jù)分割一半，而基數(shù)為1000則將返回大約1000條記錄。低基數(shù)的二分查找效率將降低為一個線性排序，而且查詢優(yōu)化器可能會在基數(shù)小于記錄數(shù)某個比例時（如30%）的情況下將避免使用索引而直接查詢原表，所以這種情況下的索引浪費了空間。
索引方式區(qū)別
目前索引有兩種方法
btree索引和hash索引
1.HASH 索引
Hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree索引需要從根節(jié)點到枝節(jié)點，最后才能訪問到頁節(jié)點這樣多次的IO訪問，所以 Hash索引的查詢效率要遠高于 B-Tree索引。可能很多人又有疑問了，既然 Hash索引的效率要比 B-Tree高很多，為什么大家不都用 Hash索引而還要使用 B-Tree索引呢？任何事物都是有兩面性的，Hash索引也一樣，雖然 Hash索引效率高，但是 Hash索引本身由于其特殊性也帶來了很多限制和弊端，主要有以下這些。
（1）Hash索引僅僅能滿足'=','IN'和'<=>'查詢，不能使用范圍查詢。
（<=>解釋?
和=號的相同點
像常規(guī)的=運算符一樣，兩個值進行比較，結果是0（不等于）或1（相等）;換句話說：'A'<=>'B'得0和'a'<=>'a‘得1。
2.和=號的不同點
和=運算符不同的是，NULL的值是沒有任何意義的。所以=號運算符不能把NULL作為有效的結果。所以：請使用<=>,
'a' <=> NULL 得0??NULL<=> NULL得出1。和=運算符正相反，=號運算符規(guī)則是 'a'=NULL 結果是NULL 甚至NULL = NULL 結果也是NULL。順便說一句，mysql上幾乎所有的操作符和函數(shù)都是這樣工作的，因為和NULL比較基本上都沒有意義。
除了 <=> ，還有兩個其他的操作符用來處理某個值和NULL做比較，也就是IS NULL and IS NOT NULL。他們是ANSI標準中的一部分，因此也可以用在其他數(shù)據(jù)庫中。而<=>只能在mysql中使用。
你可以把<=>當作mysql中的方言。
?
?
| 1 2 | 'a' IS NULL?? ==> 'a' <=> NULL 'a' IS NOT NULL ==> NOT('a' <=> NULL) |
據(jù)此，你可以把這個查詢語句段改的更具移植性一點：
WHERE p.name ISNULL）
由于 Hash索引比較的是進行 Hash運算之后的 Hash值，所以它只能用于等值的過濾，不能用于基于范圍的過濾，因為經(jīng)過相應的Hash算法處理之后的Hash值的大小關系，并不能保證和Hash運算前完全一樣。
（2）Hash索引無法被用來避免數(shù)據(jù)的排序操作。
由于 Hash索引中存放的是經(jīng)過 Hash計算之后的 Hash值，而且Hash值的大小關系并不一定和 Hash運算前的鍵值完全一樣，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算；
（3）Hash索引不能利用部分索引鍵查詢。
對于組合索引，Hash索引在計算 Hash值的時候是組合索引鍵合并后再一起計算 Hash值，而不是單獨計算 Hash值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash索引也無法被利用。
（4）Hash索引在任何時候都不能避免表掃描。
前面已經(jīng)知道，Hash索引是將索引鍵通過 Hash運算之后，將 Hash運算結果的 Hash值和所對應的行指針信息存放于一個 Hash表中，由于不同索引鍵存在相同 Hash值，所以即使取滿足某個 Hash鍵值的數(shù)據(jù)的記錄條數(shù)，也無法從 Hash索引中直接完成查詢，還是要通過訪問表中的實際數(shù)據(jù)進行相應的比較，并得到相應的結果。
（5）Hash索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高。
對于選擇性比較低的索引鍵，如果創(chuàng)建 Hash索引，那么將會存在大量記錄指針信息存于同一個 Hash值相關聯(lián)。這樣要定位?某一條記錄時就會非常麻煩，會浪費多次表數(shù)據(jù)的訪問，而造成整體性能低下，這里就是我上面說的SEX字段
2. B-Tree索引
B-Tree 索引是 MySQL數(shù)據(jù)庫中使用最為頻繁的索引類型，除了 Archive（?從archive單詞的解釋我們大概可以明白這個存儲引擎的用途，這個存儲引擎基本上用于數(shù)據(jù)歸檔；它的壓縮比非常的高，存儲空間大概是innodb的10-15分之一所以它用來存儲歷史數(shù)據(jù)非常的適合，由于它不支持索引同時也不能緩存索引和數(shù)據(jù)，所以它不適合作為并發(fā)訪問表的存儲引擎。Archivec存儲引擎使用行鎖來實現(xiàn)高并發(fā)插入操作，但是它不支持事務，其設計目標只是提供高速的插入和壓縮功能。）存儲引擎之外的其他所有的存儲引擎都支持 B-Tree 索引。不僅僅在 MySQL中是如此，實際上在其他的很多數(shù)據(jù)庫管理系統(tǒng)中B-Tree索引也同樣是作為最主要的索引類型，這主要是因為 B-Tree索引的存儲結構在數(shù)據(jù)庫的數(shù)據(jù)檢索中有非常優(yōu)異的表現(xiàn)。一般來說， MySQL中的 B-Tree索引的物理文件大多都是以 Balance Tree（平衡樹）的結構來存儲的，也就是所有實際需要的數(shù)據(jù)都存放于 Tree的 Leaf Node（葉節(jié)點），而且到任何一個 Leaf Node的最短路徑的長度都是完全相同的，所以我們大家都稱之為 B-Tree索引當然，可能各種數(shù)據(jù)庫（或 MySQL的各種存儲引擎）在存放自己的 B-Tree索引的時候會對存儲結構稍作改造。如 Innodb存儲引擎的 B-Tree索引實際使用的存儲結構實際上是B+Tree，也就是在B-Tree數(shù)據(jù)結構的基礎上做了很小的改造，在每一個
Leaf Node 上面出了存放索引鍵的相關信息之外，還存儲了指向與該Leaf Node相鄰的后一個LeafNode的指針信息，這主要是為了加快檢索多個相鄰Leaf Node的效率考慮。
在 Innodb存儲引擎中，存在兩種不同形式的索引，一種是 Cluster（集群）形式的主鍵索引（ Primary Key ），另外一種則是和其他存儲引擎（如 MyISAM存儲引擎）存放形式基本相同的普通 B-Tree索引，這種索引在 Innodb存儲引擎中被稱為 Secondary Index（輔助索引）。下面我們通過圖示來針對這兩種索引的存放
形式做一個比較。
X
圖示中左邊為 Clustered形式存放的 Primary Key，右側(cè)則為普通的 B-Tree索引。兩種 Root Node（根節(jié)點）和 Branch Nodes（分支節(jié)點）方面都還是完全一樣的。而 Leaf Nodes就出現(xiàn)差異了。在 Prim中， Leaf Nodes存放的是表的實際數(shù)據(jù)，不僅僅包括主鍵字段的數(shù)據(jù)，還包括其他字段的數(shù)據(jù)據(jù)以主鍵值有序的排列。而 Secondary Index則和其他普通的 B-Tree索引沒有太大的差異，Leaf Nodes出了存放索引鍵的相關信息外，還存放了 Innodb的主鍵值。
所以，在 Innodb中如果通過主鍵來訪問數(shù)據(jù)效率是非常高的，而如果是通過 Secondary Index來訪問數(shù)據(jù)的話，Innodb首先通過 Secondary Index的相關信息，通過相應的索引鍵檢索到Leaf Node之后，需要再通過Leaf Node中存放的主鍵值再通過主鍵索引來獲取相應的數(shù)據(jù)行。MyISAM存儲引擎的主鍵索引和非主鍵索引差別很小，只不過是主鍵索引的索引鍵是一個唯一且非空的鍵而已。而且 MyISAM存儲引擎的索引和 Innodb的 Secondary Index的存儲結構也基本相同，主要的區(qū)別只是 MyISAM存儲引擎在 Leaf Nodes上面出了存放索引鍵信息之外，再存放能直接定位到 MyISAM數(shù)據(jù)文件中相應的數(shù)據(jù)行的信息（如 Row Number（行數(shù)）），但并不會存放主鍵的鍵值信息
mysql 索引左向
以該表的(name,cid)復合索引為例,它內(nèi)部結構簡單說就是下面這樣排列的：
mysql創(chuàng)建復合索引的規(guī)則是首先會對復合索引的最左邊的，也就是第一個name字段的數(shù)據(jù)進行排序，在第一個字段的排序基礎上，然后再對后面第二個的cid字段進行排序。
其實就相當于實現(xiàn)了類似 order by name cid這樣一種排序規(guī)則。
所以：第一個name字段是絕對有序的，而第二字段就是無序的了。
? ? ? ? 所以通常情況下，直接使用第二個cid字段進行條件判斷是用不到索引的，當然，可能會出現(xiàn)使用index類型的索引。
? ? ? ? 這就是所謂的mysql為什么要強調(diào)最左前綴原則的原因
總結
以上是生活随笔為你收集整理的MYSQL 索引相关的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 。。。。找不到题目
- 下一篇： 面向对象的设计模式及魔术函数