數(shù)據(jù)庫已成為互聯(lián)網(wǎng)應(yīng)用必不可少的底層依賴，其中mysql作為開源數(shù)據(jù)庫得到了更加廣泛的應(yīng)用。最近一直專注于項目工程的開發(fā)，對開發(fā)過程中使用到的一些關(guān)于數(shù)據(jù)庫的優(yōu)化原則進行了總結(jié)，希望能夠幫助更多的應(yīng)用開發(fā)人員更好的使用mysql數(shù)據(jù)庫。
???????MySQL的優(yōu)化主要包括三個方面，首先是SQL語句的優(yōu)化，其次是表結(jié)構(gòu)的優(yōu)化，這里主要指索引的優(yōu)化，最后是服務(wù)器配置的優(yōu)化。?
???? 1．??SQL語句的優(yōu)化
1）?查詢語句應(yīng)該盡量避免全表掃描，首先應(yīng)該考慮在Where子句以及OrderBy子句上建立索引，但是每一條SQL語句最多只會走一條索引，而建立過多的索引會帶來插入和更新時的開銷，同時對于區(qū)分度不大的字段，應(yīng)該盡量避免建立索引，可以在查詢語句前使用explain關(guān)鍵字，查看SQL語句的執(zhí)行計劃，判斷該查詢語句是否使用了索引；
2）應(yīng)盡量使用EXIST和NOT EXIST代替?IN和NOT IN，因為后者很有可能導(dǎo)致全表掃描放棄使用索引；
3）應(yīng)盡量避免在Where子句中對字段進行NULL判斷，因為NULL判斷會導(dǎo)致全表掃描；
4）應(yīng)盡量避免在Where子句中使用or作為連接條件，因為同樣會導(dǎo)致全表掃描；
5）應(yīng)盡量避免在Where子句中使用！=或者操作符，同樣會導(dǎo)致全表掃描；
6）使用like “%abc%”?或者like “%abc”?同樣也會導(dǎo)致全表掃描，而like “abc%”會使用索引。
7）在使用Union操作符時，應(yīng)該考慮是否可以使用Union ALL來代替，因為Union操作符在進行結(jié)果合并時，會對產(chǎn)生的結(jié)果進行排序運算，刪除重復(fù)記錄，對于沒有該需求的應(yīng)用應(yīng)使用Union ALL，后者僅僅只是將結(jié)果合并返回，能大幅度提高性能；
8）應(yīng)盡量避免在Where子句中使用表達式操作符，因為會導(dǎo)致全表掃描；
9）應(yīng)盡量避免在Where子句中對字段使用函數(shù)，因為同樣會導(dǎo)致全表掃描
10）Select語句中盡量?避免使用“*”，因為在SQL語句在解析的過程中，會將“*”轉(zhuǎn)換成所有列的列名，而這個工作是通過查詢數(shù)據(jù)字典完成的，有一定的開銷；
11）Where子句中，表連接條件應(yīng)該寫在其他條件之前，因為Where子句的解析是從后向前的，所以盡量把能夠過濾到多數(shù)記錄的限制條件放在Where子句的末尾；
12）若數(shù)據(jù)庫表上存在諸如index(a,b,c)之類的聯(lián)合索引，則Where子句中條件字段的出現(xiàn)順序應(yīng)該與索引字段的出現(xiàn)順序一致，否則將無法使用該聯(lián)合索引；
13）From子句中表的出現(xiàn)順序同樣會對SQL語句的執(zhí)行性能造成影響，From子句在解析時是從后向前的，即寫在末尾的表將被優(yōu)先處理，應(yīng)該選擇記錄較少的表作為基表放在后面，同時如果出現(xiàn)3個及3個以上的表連接查詢時，應(yīng)該將交叉表作為基表；
14）盡量使用>=操作符代替>操作符，例如，如下SQL語句，select dbInstanceIdentifier??from DBInstance where id > 3，該語句應(yīng)該替換成?select dbInstanceIdentifier from DBInstance where id >=4?，兩個語句的執(zhí)行結(jié)果是一樣的，但是性能卻不同，后者更加 高效，因為前者在執(zhí)行時，首先會去找等于3的記錄，然后向前掃描，而后者直接定位到等于4的記錄。
2．??表結(jié)構(gòu)的優(yōu)化
??? 這里主要指如何正確的建立索引，因為不合理的索引會導(dǎo)致查詢?nèi)頀呙瑁瑫r過多的索引會帶來插入和更新的性能開銷；
1）首先要明確每一條SQL語句最多只可能使用一個索引，如果出現(xiàn)多個可以使用的索引，系統(tǒng)會根據(jù)執(zhí)行代價，選擇一個索引執(zhí)行；
2）對于Innodb表，雖然如果用戶不指定主鍵，系統(tǒng)會自動生成一個主鍵列，但是自動產(chǎn)生的主鍵列有多個問題1.?性能不足，無法使用cache讀??；2.?并發(fā)不足，系統(tǒng)所有無主鍵表，共用一個全局的Auto_Increment列。因此，InnoDB的所有表，在建表同時必須指定主鍵。
3）對于區(qū)分度不大的字段，不要建立索引；
4）一個字段只需建一種索引即可，無需建立了唯一索引，又建立INDEX索引。
5）對于大的文本字段或者BLOB字段，不要建立索引；
6）連接查詢的連接字段應(yīng)該建立索引；
7）排序字段一般要建立索引；
8）分組統(tǒng)計字段一般要建立索引；
9）正確使用聯(lián)合索引，聯(lián)合索引的第一個字段是可以被單獨使用的，例如有如下聯(lián)合索引index(userID,dbInstanceID),一下查詢語句是可以使用該索引的，select dbInstanceIdentifier from DBInstance where userID=??，但是語句select dbInstanceIdentifier from DBInstance where dbInstanceID=?就不可以使用該索引；
10）索引一般用于記錄比較多的表，假如有表DBInstance，所有查詢都有userID條件字段，目前已知該字段已經(jīng)能夠很好的區(qū)分記錄，即每一個userID下記錄數(shù)量不多，所以該表只需在userID上建立一個索引即可，即使有使用其他條件字段，由于每一個userID對應(yīng)的記錄數(shù)據(jù)不多，所以其他字段使用不用索引基本無影響，同時也可以避免建立過多的索引帶來的插入和更新的性能開銷；
?????? 3．??MySQL服務(wù)器配置優(yōu)化
????????????MySQL服務(wù)器配置優(yōu)化主要是指MySQL參數(shù)的優(yōu)化；
?????????? 1）MySQL服務(wù)器有慢連接日志，可以將超過一定時間間隔和不使用索引的查詢語句記錄下來方便開發(fā)人員跟蹤，可以通過設(shè)置slow_query_log=ON/OFF打開和關(guān)閉慢連接日志功能，slow_query_log_file設(shè)置慢連接日志的文件名，long_query_time設(shè)置超時時間，單位是ms,注意慢連接日志MySQL默認是關(guān)閉的；
?????????? 2）MySQL有查詢緩存的功能，服務(wù)器會保存查詢語句和相應(yīng)的返回結(jié)果來減少相同的查詢造成的服務(wù)器開銷，可以通過設(shè)置query_cache_size設(shè)置查詢緩存的大小，0表示關(guān)閉查詢緩存，但是值得注意的是，一旦該表有更新，則所有的查詢緩存都會失效，默認情況下，MySQL是關(guān)閉查詢緩存的；
?????????? 3）可以通過配置max_connections設(shè)置數(shù)據(jù)庫的最大連接數(shù)，wait_timeout設(shè)置連接最長保留時間，該時間單位是s, MySQL默認是8個小時，一旦超過8個小時，數(shù)據(jù)庫會自動斷開該連接，這點在使用數(shù)據(jù)庫連接池時由為需要注意，因為連接池中的連接可能已經(jīng)被服務(wù)器斷開了，到那時連接池不知道，應(yīng)用在從連接池中獲取到該連接使用時就會出錯，max_connect_errors配置如果應(yīng)用出現(xiàn)多次異常，則會終止主機連接數(shù)據(jù)庫；
【相關(guān)推薦】
