MySQL的優(yōu)化主要包括三個(gè)方面，首先是SQL語(yǔ)句的優(yōu)化，其次是表結(jié)構(gòu)的優(yōu)化，這里主要指索引的優(yōu)化，最后是服務(wù)器配置的優(yōu)化。

1． SQL語(yǔ)句的優(yōu)化

1） 查詢語(yǔ)句應(yīng)該盡量避免全表掃描，首先應(yīng)該考慮在Where子句以及OrderBy子句上建立索引，但是每一條SQL語(yǔ)句最多只會(huì)走一條索引，而建立過(guò)多的索引會(huì)帶來(lái)插入和更新時(shí)的開銷，同時(shí)對(duì)于區(qū)分度不大的字段，應(yīng)該盡量避免建立索引，可以在查詢語(yǔ)句前使用explain關(guān)鍵字，查看SQL語(yǔ)句的執(zhí)行計(jì)劃，判斷該查詢語(yǔ)句是否使用了索引；

2）應(yīng)盡量使用EXIST和NOT EXIST代替 IN和NOT IN，因?yàn)楹笳吆苡锌赡軐?dǎo)致全表掃描放棄使用索引；

3）應(yīng)盡量避免在Where子句中對(duì)字段進(jìn)行NULL判斷，因?yàn)镹ULL判斷會(huì)導(dǎo)致全表掃描；

4）應(yīng)盡量避免在Where子句中使用or作為連接條件，因?yàn)橥瑯訒?huì)導(dǎo)致全表掃描；

5）應(yīng)盡量避免在Where子句中使用！=或者<>操作符，同樣會(huì)導(dǎo)致全表掃描；

6）使用like “%abc%” 或者like “%abc” 同樣也會(huì)導(dǎo)致全表掃描，而like “abc%”會(huì)使用索引。

7）在使用Union操作符時(shí)，應(yīng)該考慮是否可以使用Union ALL來(lái)代替，因?yàn)閁nion操作符在進(jìn)行結(jié)果合并時(shí)，會(huì)對(duì)產(chǎn)生的結(jié)果進(jìn)行排序運(yùn)算，刪除重復(fù)記錄，對(duì)于沒有該需求的應(yīng)用應(yīng)使用Union ALL，后者僅僅只是將結(jié)果合并返回，能大幅度提高性能；

8）應(yīng)盡量避免在Where子句中使用表達(dá)式操作符，因?yàn)闀?huì)導(dǎo)致全表掃描；

9）應(yīng)盡量避免在Where子句中對(duì)字段使用函數(shù)，因?yàn)橥瑯訒?huì)導(dǎo)致全表掃描

10）Select語(yǔ)句中盡量 避免使用“*”，因?yàn)樵赟QL語(yǔ)句在解析的過(guò)程中，會(huì)將“*”轉(zhuǎn)換成所有列的列名，而這個(gè)工作是通過(guò)查詢數(shù)據(jù)字典完成的，有一定的開銷；

11）Where子句中，表連接條件應(yīng)該寫在其他條件之前，因?yàn)閃here子句的解析是從后向前的，所以盡量把能夠過(guò)濾到多數(shù)記錄的限制條件放在Where子句的末尾；

12）若數(shù)據(jù)庫(kù)表上存在諸如index(a,b,c)之類的聯(lián)合索引，則Where子句中條件字段的出現(xiàn)順序應(yīng)該與索引字段的出現(xiàn)順序一致，否則將無(wú)法使用該聯(lián)合索引；

13）From子句中表的出現(xiàn)順序同樣會(huì)對(duì)SQL語(yǔ)句的執(zhí)行性能造成影響，F(xiàn)rom子句在解析時(shí)是從后向前的，即寫在末尾的表將被優(yōu)先處理，應(yīng)該選擇記錄較少的表作為基表放在后面，同時(shí)如果出現(xiàn)3個(gè)及3個(gè)以上的表連接查詢時(shí)，應(yīng)該將交叉表作為基表；

14）盡量使用>=操作符代替>操作符，例如，如下SQL語(yǔ)句，select dbInstanceIdentifier from DBInstance where id > 3，該語(yǔ)句應(yīng)該替換成 select dbInstanceIdentifier from DBInstance where id >=4 ，兩個(gè)語(yǔ)句的執(zhí)行結(jié)果是一樣的，但是性能卻不同，后者更加 高效，因?yàn)榍罢咴趫?zhí)行時(shí)，首先會(huì)去找等于3的記錄，然后向前掃描，而后者直接定位到等于4的記錄。

2． 表結(jié)構(gòu)的優(yōu)化

這里主要指如何正確的建立索引，因?yàn)椴缓侠淼乃饕龝?huì)導(dǎo)致查詢?nèi)頀呙瑁瑫r(shí)過(guò)多的索引會(huì)帶來(lái)插入和更新的性能開銷；

1）首先要明確每一條SQL語(yǔ)句最多只可能使用一個(gè)索引，如果出現(xiàn)多個(gè)可以使用的索引，系統(tǒng)會(huì)根據(jù)執(zhí)行代價(jià)，選擇一個(gè)索引執(zhí)行；

2）對(duì)于Innodb表，雖然如果用戶不指定主鍵，系統(tǒng)會(huì)自動(dòng)生成一個(gè)主鍵列，但是自動(dòng)產(chǎn)生的主鍵列有多個(gè)問(wèn)題1. 性能不足，無(wú)法使用cache讀取；2. 并發(fā)不足，系統(tǒng)所有無(wú)主鍵表，共用一個(gè)全局的Auto_Increment列。因此，InnoDB的所有表，在建表同時(shí)必須指定主鍵。

3）對(duì)于區(qū)分度不大的字段，不要建立索引；

4）一個(gè)字段只需建一種索引即可，無(wú)需建立了唯一索引，又建立INDEX索引。

5）對(duì)于大的文本字段或者BLOB字段，不要建立索引；

6）連接查詢的連接字段應(yīng)該建立索引；

7）排序字段一般要建立索引；

8）分組統(tǒng)計(jì)字段一般要建立索引；

9）正確使用聯(lián)合索引，聯(lián)合索引的第一個(gè)字段是可以被單獨(dú)使用的，例如有如下聯(lián)合索引index(userID,dbInstanceID),一下查詢語(yǔ)句是可以使用該索引的，select dbInstanceIdentifier from DBInstance where userID=? ，但是語(yǔ)句select dbInstanceIdentifier from DBInstance where dbInstanceID=?就不可以使用該索引；

10）索引一般用于記錄比較多的表，假如有表DBInstance，所有查詢都有userID條件字段，目前已知該字段已經(jīng)能夠很好的區(qū)分記錄，即每一個(gè)userID下記錄數(shù)量不多，所以該表只需在userID上建立一個(gè)索引即可，即使有使用其他條件字段，由于每一個(gè)userID對(duì)應(yīng)的記錄數(shù)據(jù)不多，所以其他字段使用不用索引基本無(wú)影響，同時(shí)也可以避免建立過(guò)多的索引帶來(lái)的插入和更新的性能開銷；

3． MySQL服務(wù)器配置優(yōu)化

MySQL服務(wù)器配置優(yōu)化主要是指MySQL參數(shù)的優(yōu)化；

1）MySQL服務(wù)器有慢連接日志，可以將超過(guò)一定時(shí)間間隔和不使用索引的查詢語(yǔ)句記錄下來(lái)方便開發(fā)人員跟蹤，可以通過(guò)設(shè)置slow_query_log=ON/OFF打開和關(guān)閉慢連接日志功能，slow_query_log_file設(shè)置慢連接日志的文件名，long_query_time設(shè)置超時(shí)時(shí)間，單位是ms,注意慢連接日志MySQL默認(rèn)是關(guān)閉的；

2）MySQL有查詢緩存的功能，服務(wù)器會(huì)保存查詢語(yǔ)句和相應(yīng)的返回結(jié)果來(lái)減少相同的查詢?cè)斐傻姆?wù)器開銷，可以通過(guò)設(shè)置query_cache_size設(shè)置查詢緩存的大小，0表示關(guān)閉查詢緩存，但是值得注意的是，一旦該表有更新，則所有的查詢緩存都會(huì)失效，默認(rèn)情況下，MySQL是關(guān)閉查詢緩存的；

3）可以通過(guò)配置max_connections設(shè)置數(shù)據(jù)庫(kù)的最大連接數(shù)，wait_timeout設(shè)置連接最長(zhǎng)保留時(shí)間，該時(shí)間單位是s, MySQL默認(rèn)是8個(gè)小時(shí)，一旦超過(guò)8個(gè)小時(shí)，數(shù)據(jù)庫(kù)會(huì)自動(dòng)斷開該連接，這點(diǎn)在使用數(shù)據(jù)庫(kù)連接池時(shí)由為需要注意，因?yàn)檫B接池中的連接可能已經(jīng)被服務(wù)器斷開了，到那時(shí)連接池不知道，應(yīng)用在從連接池中獲取到該連接使用時(shí)就會(huì)出錯(cuò)，max_connect_errors配置如果應(yīng)用出現(xiàn)多次異常，則會(huì)終止主機(jī)連接數(shù)據(jù)庫(kù)；

【相關(guān)推薦】

1. 免費(fèi)mysql在線視頻教程

2. MySQL最新手冊(cè)教程

3.數(shù)據(jù)庫(kù)設(shè)計(jì)那些事

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com