- 索引的優(yōu)點
- 索引的缺點
- 建索引的幾大原則
索引的優(yōu)點
通過創(chuàng)建唯一性索引，可以保證數(shù)據庫表中每一行數(shù)據的唯一性。
可以大大加快數(shù)據的檢索速度，這也是創(chuàng)建索引的最主要的原因。
可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據的參考完整性方面特別有意義。
在使用分組和排序子句進行數(shù)據檢索時，同樣可以顯著減少查詢中分組和排序的時間。
通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能
索引的缺點
創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據量的增加而增加。
索引需要占物理空間，除了數(shù)據表占數(shù)據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。
當對表中的數(shù)據進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據的維護速度。
建索引的幾大原則
1.最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式
3.盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據面前區(qū)分度就是0，那可能有人會問，這個比例有什么經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄
4.索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據表中的字段值，但進行檢索時，需要把所有元素都應用函數(shù)才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);
5.盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可
- Where 子句中經常使用的字段應該創(chuàng)建索引，分組字段或者排序字段應該創(chuàng)建索引，兩個表的連接字段應該創(chuàng)建索引。
7.like 模糊查詢中，右模糊查詢（321%）會使用索引，而%321 和%321%會放棄索引而使用全局掃描
應該建立索引的情況
- 在經常需要搜索的列上，可以加快搜索的速度；
- 在作為主鍵的列上，強制該列的唯一性和組織表中數(shù)據的排列結構；
- 在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
- 在經常需要根據范圍進行搜索的列上創(chuàng)建索引，因為索引已經排序，其指定的范圍是連續(xù)的；
- 在經常需要排序的列上創(chuàng)建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
- 在經常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。
不應該建立索引的情況
對于那些在查詢中很少使用或者參考的列不應該創(chuàng)建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求。
對于那些只有很少數(shù)據值的列也不應該增加索引。這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的數(shù)據行占了表中數(shù)據行的很大比例，即需要在表中搜索的數(shù)據行的比例很大。增加索引，并不能明顯加快檢索速度。
對于那些定義為text, image和bit數(shù)據類型的列不應該增加索引。這是因為，這些列的數(shù)據量要么相當大，要么取值很少。
當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。
