MySQL最佳化包含的三個面向

資料庫已成為網路應用必不可少的底層依賴，其中MySQL作為開源資料庫得到了更廣泛的應用。最近一直專注於專案工程的開發，對開發過程中使用到的一些關於資料庫的最佳化原則進行了總結，希望能夠幫助更多的應用開發人員更好的使用MySQL資料庫。

       MySQL的最佳化主要包括三個方面，首先是SQL語句的最佳化，其次是表結構的最佳化，這裡主要指索引的最佳化，最後是伺服器配置的最佳化。  

     1．   SQL語句的最佳化

1） 查詢語句應該盡量避免全表掃描，首先應該考慮在Where子句以及OrderBy子句上建立索引，但是每一個SQL語句最多只會走一條索引，而建立過多的索引會帶來插入和更新時的開銷，同時對於區分度不大的字段，應該盡量避免建立索引，可以在查詢語句前使用explain關鍵字，查看SQL語句的執行計劃，判斷該查詢語句是否使用了索引；

2）應盡量使用EXIST和NOT EXIST取代 IN和NOT IN，因為後者很有可能會導致全表掃描放棄使用索引；

3） 應盡量避免在Where子句中對欄位進行NULL判斷，因為NULL判斷會導致全表掃描；

4）應盡量避免在Where子句中使用or作為連接條件，因為同樣會導致全表掃描；

5）應盡量避免在Where子句中使用！ =或<>運算子，同樣會導致全表掃描；

6）使用like “%abc%” 或like “%abc” 同樣也會導致全表掃描，而like “ abc%”會使用索引。

7）在使用Union運算子時，應該考慮是否可以使用Union ALL來代替，因為Union運算子在進行結果合併時，會對產生的結果進行排序運算，刪除重複記錄，對於沒有該需求的應用應使用Union ALL，後者僅只是將結果合併返回，能大幅提高效能；

8）應盡量避免在Where子句中使用表達式運算子，因為會導致全表掃描；

9）應盡量避免在Where子句中對欄位使用函數，因為同樣會導致全表掃描

10）Select語句中盡量 避免使用“*” ，因為在SQL語句在解析的過程中，會將「*」轉換成所有列的列名，而這個工作是透過查詢資料字典完成的，有一定的開銷；

11）Where子句中，表連接條件應該寫在其他條件之前，因為Where子句的解析是從後向前的，所以盡量把能夠過濾到多數記錄的限制條件放在Where子句的末尾；

#12）若資料庫表上存在諸如index(a,b,c)之類的聯合索引，則Where子句中條件欄位的出現順序應該與索引欄位的出現順序一致，否則將無法使用該聯合索引；

13）From子句中表的出現順序同樣會對SQL語句的執行效能造成影響，From子句在解析時是從後向前的，即寫在末尾的表將被優先處理，應該選擇記錄較少的表作為基表放在後面，同時如果出現3個及3個以上的表連接查詢時，應該將交叉表作為基底表；

14）盡量使用>=運算子取代>運算子，例如，如下##SQL語句，select dbInstanceIdentifier  from DBInstance where id > 3，語句應該替換成 select dbInstanceIdentifier from DBInstance where id >=4 ，兩個語句的執行結果是一樣的，但效能卻，後者更有效率，因為前者在執行時，首先會去找等於3的記錄，然後向前掃描，而後者直接定位到等於4的記錄。

2．   表格結構的最佳化

#    這裡主要指如何正確的索引，因為不合理的索引會導致查詢全表掃描，同時過多的索引會帶來插入和更新的效能開銷；#​​

1）首先要明確每一條SQL語句最多只可能使用一個索引，如果出現多個可以使用的索引，系統會根據執行代價，選擇一個索引執行；

2） 對於Innodb表，雖然如果使用者不指定主鍵，系統會自動產生一個主鍵列，但是自動產生的主鍵列有多個問題1. 效能不足，無法使用cache讀取；2. 並發不足，系統所有無主鍵表，共用一個全域的Auto_Increment欄位。因此，InnoDB的所有表，在建表同時必須指定主鍵。

3）對於區分度不大的字段，不要建立索引；

##4）一個欄位只需建立一種索引即可，無需建立了唯一索引，又建立INDEX#索引。

######5）###對於大的文字字段或###BLOB###字段，不要建立索引；###### ###

6）連接查詢的連接欄位應該建立索引；

#7）排序欄位一般要建立索引；

8）分組統計欄位一般要建立索引;

9）正確使用聯合索引，聯合索引的第一個欄位是可以被單獨使用的，例如有如下聯合索引index(userID,dbInstanceID),一下查詢語句是可以使用該索引的，select dbInstanceIdentifier from DBInstance where userID=? ，但是語句select dbInstanceIdentifier from DBInstance where dbInstanceID=?就不可以使用該索引；

10）索引一般用於記錄比較多的表，假如有表DBInstance，所有查詢都有userID條件字段，目前已知該字段已經能夠很好的區分記錄，即每一個userID下記錄數量不多，所以該表只需在userID上建立索引即可，即使有使用其他條件字段，由於每一個userID對應的記錄資料不多，所以其他欄位使用不用索引基本上無影響，同時也可以避免建立過多的索引帶來的插入和更新的效能開銷；#​​

##       3．   MySQL伺服器設定最佳化

              MySQL伺服器設定#MySQL##參數的最佳化；

           1）MySQL

伺服器有一個慢速連線日誌，可將超過一定時間間隔和不使用索引的查詢語句記錄下來方便開發人員追蹤，可以透過設定slow_query_log=ON/OFF開啟和關閉慢連線日誌功能，slow_query_log_file設定慢連線日誌的檔名，long_query_time設定逾時時間，單位是ms,注意慢連線日誌MySQL預設是關閉的;

           2）MySQL有查詢快取的功能，伺服器會儲存查詢語句和對應的回傳結果來減少相同的查詢造成的伺服器開銷，可以透過設定query_cache_size##設定設定快取的大小，0表示關閉查詢緩存，但值得注意的是，一旦該表有更新，則所有的查詢快取都會失效，預設情況下，MySQL是關閉查詢快取的；

           3）可透過設定max_connections設定資料庫的最大連線數，wait_timeout##設定連接最為長保留時間，該時間單位是s, MySQL預設是8個小時，一旦超過8個小時，資料庫會自動斷開該連接，這點在使用資料庫連接池時由為需要注意，因為連接池中的連接可能已經被伺服器斷開了，到那時連接池不知道，應用在從連接池中獲取到該連接使用時就會出錯， max_connect_errors配置如果應用程式出現多次異常，則會終止主機連線資料庫；

【相關推薦】

1. 

免費mysql線上影片教學

#2. 

MySQL最新手冊教學

##3 .資料庫設計那些事

#

以上是MySQL最佳化包含的三個面向的詳細內容。更多資訊請關注PHP中文網其他相關文章！