創(chuàng)建索引(關(guān)于mysql建立索引需要注意的幾點(diǎn)事項(xiàng))
1.關(guān)于mysql建立索引需要注意的幾點(diǎn)事項(xiàng)
1.索引不存儲(chǔ)null值。
更準(zhǔn)確的說(shuō)，單列索引不存儲(chǔ)null值，復(fù)合索引不存儲(chǔ)全為null的值。索引不能存儲(chǔ)Null，所以對(duì)這列采用is null條件時(shí)，因?yàn)樗饕细緵](méi)Null值，不能利用到索引，只能全表掃描。
為什么索引列不能存Null值？將索引列值進(jìn)行建樹(shù)，其中必然涉及到諸多的比較操作。Null值的特殊性就在于參與的運(yùn)算大多取值為null。
這樣的話，null值實(shí)際上是不能參與進(jìn)建索引的過(guò)程。也就是說(shuō)，null值不會(huì)像其他取值一樣出現(xiàn)在索引樹(shù)的葉子節(jié)點(diǎn)上。
2.不適合鍵值較少的列（重復(fù)數(shù)據(jù)較多的列）。假如索引列TYPE有5個(gè)鍵值，如果有1萬(wàn)條數(shù)據(jù)，那么 WHERE TYPE = 1將訪問(wèn)表中的2000個(gè)數(shù)據(jù)塊。
再加上訪問(wèn)索引塊，一共要訪問(wèn)大于200個(gè)的數(shù)據(jù)塊。如果全表掃描，假設(shè)10條數(shù)據(jù)一個(gè)數(shù)據(jù)塊，那么只需訪問(wèn)1000個(gè)數(shù)據(jù)塊，既然全表掃描訪問(wèn)的數(shù)據(jù)塊少一些，肯定就不會(huì)利用索引了。
3.前導(dǎo)模糊查詢不能利用索引（like '%XX'或者like '%XX%'）假如有這樣一列code的值為'AAA','AAB','BAA','BAB' ，如果where code like '%AB'條件，由于前面是模糊的，所以不能利用索引的順序，必須一個(gè)個(gè)去找，看是否滿足條件。這樣會(huì)導(dǎo)致全索引掃描或者全表掃描。
如果是這樣的條件where code like 'A % '，就可以查找CODE中A開(kāi)頭的CODE的位置，當(dāng)碰到B開(kāi)頭的數(shù)據(jù)時(shí)，就可以停止查找了，因?yàn)楹竺娴臄?shù)據(jù)一定不滿足要求。這樣就可以利用索引了。
4.MySQL主要提供2種方式的索引：B-Tree索引，Hash索引。B樹(shù)索引具有范圍查找和前綴查找的能力，對(duì)于有N節(jié)點(diǎn)的B樹(shù)，檢索一條記錄的復(fù)雜度為O(LogN)。
相當(dāng)于二分查找。哈希索引只能做等于查找，但是無(wú)論多大的Hash表，查找復(fù)雜度都是O(1)。
顯然，如果值的差異性大，并且以等值查找（=、、in）為主，Hash索引是更高效的選擇，它有O(1)的查找復(fù)雜度。如果值的差異性相對(duì)較差，并且以范圍查找為主，B樹(shù)是更好的選擇，它支持范圍查找。
2.數(shù)據(jù)庫(kù)索引的注意事項(xiàng)
索引是建立在數(shù)據(jù)庫(kù)表中的某些列的上面。在創(chuàng)建索引的時(shí)候，應(yīng)該考慮在哪些列上可以創(chuàng)建索引，在哪些列上不能創(chuàng)建索引。一般來(lái)說(shuō)，應(yīng)該在這些列上創(chuàng)建索引：
在經(jīng)常需要搜索的列上，可以加快搜索的速度；
在作為主鍵的列上，強(qiáng)制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；
在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，其指定的范圍是連續(xù)的；
在經(jīng)常需要排序的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時(shí)間；
在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。
同樣，對(duì)于有些列不應(yīng)該創(chuàng)建索引。一般來(lái)說(shuō)，不應(yīng)該創(chuàng)建索引的這些列具有下列特點(diǎn)：
第一，對(duì)于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引。這是因?yàn)?，既然這些列很少使用到，因此有索引或者無(wú)索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求。
第二，對(duì)于那些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引。這是因?yàn)?，由于這些列的取值很少，例如人事表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。
第三，對(duì)于那些定義為text, image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因?yàn)?，這些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少，不利于使用索引。
第四，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。這是因?yàn)椋薷男阅芎蜋z索性能是互相矛盾的。當(dāng)增加索引時(shí)，會(huì)提高檢索性能，但是會(huì)降低修改性能。當(dāng)減少索引時(shí)，會(huì)提高修改性能，降低檢索性能。因此，當(dāng)修改操作遠(yuǎn)遠(yuǎn)多于檢索操作時(shí)，不應(yīng)該創(chuàng)建索引。
3.關(guān)于mysql建立索引需要注意的幾點(diǎn)事項(xiàng)
1.索引不存儲(chǔ)null值。
更準(zhǔn)確的說(shuō)，單列索引不存儲(chǔ)null值，復(fù)合索引不存儲(chǔ)全為null的值。索引不能存儲(chǔ)Null，所以對(duì)這列采用is null條件時(shí)，因?yàn)樗饕细緵](méi)Null值，不能利用到索引，只能全表掃描。
為什么索引列不能存Null值？
將索引列值進(jìn)行建樹(shù)，其中必然涉及到諸多的比較操作。Null值的特殊性就在于參與的運(yùn)算大多取值為null。這樣的話，null值實(shí)際上是不能參與進(jìn)建索引的過(guò)程。也就是說(shuō)，null值不會(huì)像其他取值一樣出現(xiàn)在索引樹(shù)的葉子節(jié)點(diǎn)上。
2.不適合鍵值較少的列（重復(fù)數(shù)據(jù)較多的列）。
假如索引列TYPE有5個(gè)鍵值，如果有1萬(wàn)條數(shù)據(jù)，那么 WHERE TYPE = 1將訪問(wèn)表中的2000個(gè)數(shù)據(jù)塊。
再加上訪問(wèn)索引塊，一共要訪問(wèn)大于200個(gè)的數(shù)據(jù)塊。
如果全表掃描，假設(shè)10條數(shù)據(jù)一個(gè)數(shù)據(jù)塊，那么只需訪問(wèn)1000個(gè)數(shù)據(jù)塊，既然全表掃描訪問(wèn)的數(shù)據(jù)塊少一些，肯定就不會(huì)利用索引了。
3.前導(dǎo)模糊查詢不能利用索引（like '%XX'或者like '%XX%'）
假如有這樣一列code的值為'AAA','AAB','BAA','BAB' ，如果where code like '%AB'條件，由于前面是模糊的，所以不能利用索引的順序，必須一個(gè)個(gè)去找，看是否滿足條件。這樣會(huì)導(dǎo)致全索引掃描或者全表掃描。如果是這樣的條件where code like 'A % '，就可以查找CODE中A開(kāi)頭的CODE的位置，當(dāng)碰到B開(kāi)頭的數(shù)據(jù)時(shí)，就可以停止查找了，因?yàn)楹竺娴臄?shù)據(jù)一定不滿足要求。這樣就可以利用索引了。
4.MySQL主要提供2種方式的索引：B-Tree索引，Hash索引。
B樹(shù)索引具有范圍查找和前綴查找的能力，對(duì)于有N節(jié)點(diǎn)的B樹(shù)，檢索一條記錄的復(fù)雜度為O(LogN)。相當(dāng)于二分查找。
哈希索引只能做等于查找，但是無(wú)論多大的Hash表，查找復(fù)雜度都是O(1)。
顯然，如果值的差異性大，并且以等值查找（=、<；、>；、in）為主，Hash索引是更高效的選擇，它有O(1)的查找復(fù)雜度。
如果值的差異性相對(duì)較差，并且以范圍查找為主，B樹(shù)是更好的選擇，它支持范圍查找。
4.索引的注意事項(xiàng)
并非所有的數(shù)據(jù)庫(kù)都以相同的方式使用索引。
作為通用規(guī)則，只有當(dāng)經(jīng)常查詢索引列中的數(shù)據(jù)時(shí)，才需要在表上創(chuàng)建索引。索引占用磁盤(pán)空間，并且降低添加、刪除和更新行的速度。
在多數(shù)情況下，索引用于數(shù)據(jù)檢索的速度優(yōu)勢(shì)大大超過(guò)它的不足之處。但是，如果應(yīng)用程序非常頻繁地更新數(shù)據(jù)或磁盤(pán)空間有限，則可能需要限制索引的數(shù)量。
可以基于數(shù)據(jù)庫(kù)表中的單列或多列創(chuàng)建索引。多列索引使您可以區(qū)分其中一列可能有相同值的行。
如果經(jīng)常同時(shí)搜索兩列或多列或按兩列或多列排序時(shí)，索引也很有幫助。例如，如果經(jīng)常在同一查詢中為姓和名兩列設(shè)置判據(jù)，那么在這兩列上創(chuàng)建多列索引將很有意義。
確定索引的有效性：檢查查詢的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以選擇的對(duì)象。
對(duì)新索引進(jìn)行試驗(yàn)以檢查它對(duì)運(yùn)行查詢性能的影響。考慮已在表上創(chuàng)建的索引數(shù)量。
最好避免在單個(gè)表上有很多索引。檢查已在表上創(chuàng)建的索引的定義。
最好避免包含共享列的重疊索引。檢查某列中唯一數(shù)據(jù)值的數(shù)量，并將該數(shù)量與表中的行數(shù)進(jìn)行比較。
比較的結(jié)果就是該列的可選擇性，這有助于確定該列是否適合建立索引，如果適合，確定索引的類型。
