如何正確合理的建立MYSQL數(shù)據(jù)庫索引

張生工作室 2011-08-04

展開全文

如何正確合理的建立MYSQL數(shù)據(jù)庫索引

普通MySQL運行，數(shù)據(jù)量和訪問量不大的話，是足夠快的，但是當(dāng)數(shù)據(jù)量和訪問量劇增的時候，那么就會明顯發(fā)現(xiàn)MySQL很慢，甚至down掉，那么就要考慮優(yōu)化我們的MySQL了。其中優(yōu)化MYSQL的一個重要環(huán)節(jié)就是為數(shù)據(jù)庫建立正確合理的索引。

如果沒有索引，執(zhí)行查詢時MySQL必須從第一個記錄開始掃描整個表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個操作的代價就越高。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無需掃描任何記錄即可迅速得到目標記錄所在的位置。也就是說索引可以大大減少數(shù)據(jù)庫管理系統(tǒng)查找數(shù)據(jù)的時間。

索引有哪些優(yōu)點？

1、通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

2、可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要原因。

3、可以加速表和表之間的連接，這在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。

4、在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。

索引有哪些缺點？

1、創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。

2、除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，需要的空間就會更大。

3、當(dāng)對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。

索引有哪些類型？

1、 普通索引

這是最基本的索引類型，而且它沒有唯一性之類的限制。

2、 唯一性索引

這種索引和前面的“普通索引”基本相同，但有一個區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須唯一。

3、主鍵

它是一種特殊的唯一索引，不允許有空值。

4、全文索引

MySQL從3.23.23版開始支持全文索引和全文檢索。

單列索引和組合索引：

單列索引就是把索引單獨建立在一個字段上。

組合索引復(fù)合索引就是一個索引創(chuàng)建在兩個列或者多個列上。在搜索時，當(dāng)兩個或者多個列作為一個關(guān)鍵值時，最好在這些列上創(chuàng)建復(fù)合索引。

建立和使用索引有哪些注意事項：

1、索引要建立在經(jīng)常進行select操作的字段上。這是因為，如果這些列很少用到，那么有無索引并不能明顯改變查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求。

2、索引要建立在值比較唯一的字段上。這樣做才是發(fā)揮索引的最大效果。，比如主鍵的id字段，唯一的名字name字段等等。如果索引建立在唯一值比較少的字段，比如性別gender字段，寥寥無幾的類別字段等，剛索引幾乎沒有任何意義。

3、對于那些定義為text、 image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。因為這些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。

4、當(dāng)修改性能遠遠大于檢索性能時，不應(yīng)該創(chuàng)建索引。修改性能和檢索性能是互相矛盾的。當(dāng)增加索引時，會提高檢索性能，但是會降低修改性能。當(dāng)減少索引時，會提高修改性能，降低檢索性能。因此，當(dāng)修改性能遠遠大于檢索性能時，不應(yīng)該創(chuàng)建索引。

5、在WHERE和JOIN中出現(xiàn)的列需要建立索引。

6、在以通配符 % 和 _ 開頭作查詢時，MySQL索引是無效的。但是這樣索引是有效的：select * from tbl1 where name like 'xxx%'，所以謹慎的寫你的SQL是很重要的。

用一個例子詳細說明單列索引與組合索引的區(qū)別及索引使用中的一些細節(jié)

建一個表：
CREATE TABLE myIndex (

i_testID INT NOT NULL AUTO_INCREMENT,

vc_Name VARCHAR(50) NOT NULL,

vc_City VARCHAR(50) NOT NULL,

i_Age INT NOT NULL,

i_SchoolID INT NOT NULL,

PRIMARY KEY (i_testID)

);

在這10000條記錄里面7上8下地分布了5條vc_Name="erquan"的記錄，只不過city,age,school的組合各不相同。
來看這條T-SQL：
SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='鄭州' AND i_Age=25;

首先考慮建單列索引：
在vc_Name列上建立了索引。執(zhí)行T-SQL時，MYSQL很快將目標鎖定在了 vc_Name=erquan的5條記錄上，取出來放到一中間結(jié)果集。在這個結(jié)果集里，先排除掉vc_City不等于"鄭州"的記錄，再排除i_Age不等于25的記錄，最后篩選出唯一的符合條件的記錄。

雖然在vc_Name上建立了索引，查詢時MYSQL不用掃描整張表，效率有所提高，但離我們的要求還有一定的距離。同樣的，在vc_City和i_Age分別建立的單列索引的效率相似。

為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是將vc_Name,vc_City,i_Age建到一個索引里：
ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);--注意了，建表時，vc_Name長度為50，這里為什么用10呢？因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高插入的更新速度。

執(zhí)行T-SQL時，MySQL無須掃描任何記錄就到找到唯一的記錄??！

肯定有人要問了，如果分別在vc_Name,vc_City,i_Age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率一樣吧？嘿嘿，大不一樣，遠遠低于我們的組合索引~~雖然此時有了三個索引，但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引。

建立這樣的組合索引，其實是相當(dāng)于分別建立了
vc_Name,vc_City,i_Age
vc_Name,vc_City
vc_Name
這樣的三個組合索引！為什么沒有vc_City,i_Age等這樣的組合索引呢？這是因為mysql組合索引"最左前綴"的結(jié)果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引，下面的幾個T-SQL會用到：
SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="鄭州"
SELECT * FROM myIndex WHREE vc_Name="erquan"
而下面幾個則不會用到：
SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州"
SELECT * FROM myIndex WHREE vc_City="鄭州"