mysql的20個最佳化總結

這篇文章帶給大家的內容是關於mysql的最佳化總結，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

前言

現如今，資料庫的運作越來越成為整個應用程式的效能瓶頸了，這點對於Web應用尤其明顯。所以，我整理了MySQL優化的幾點建議，希望這些優化技巧對您有用，總結不到的，歡迎大家來補充。

SQL執行慢的原因

1. 網路速度慢，記憶體不足，I/O吞吐量小，磁碟空間滿了等硬體問題

2. #沒有索引或索引失效

3. 資料表裡的資料記錄過多

4. 伺服器調優及各個參數設定也可能影響

5. 開發者所寫的SQL效率

#1、EXPLAIN分析你的SELECT查詢

很多情況下，使用EXPLAIN關鍵字可以讓你知道MySQL是如何處理你的SQL語句的，這可以幫你分析你的查詢語句，或許能盡快找到的優化方法以及潛在的性能問題。具體EXPLAIN的使用以及各參數的意義，請查閱相關文件即可。

2、SELECT查詢必須指明欄位名稱

SELECT * 的查詢會加很多不必要的消耗（例如CPU、I/O等），同時，也有可能增加了使用覆蓋索引。所以SELECT查詢時，要求直接在後面指明需要查詢的對應欄位名。

3、查詢一筆資料的時候，使用LIMIT 1

減少多餘的查詢，因為指定limit 1後，查詢到一條資料就不再繼續查詢了，使得EXPLAIN中type列達到const類型，查詢語句更優。

4、為搜尋的WHERE欄位建立索引

一般，每個表我們都會設定一個主鍵，而索引不一定就是給主鍵。如果在你的表中，有某個字段你總要會經常用來做WHERE查詢搜索，而且是讀大於寫的，那麼，請為其建立索引吧，有興趣了解更多建立索引的的原則，可查閱相關資料。

5、千萬不要使用ORDER BY RAND()

如果你想隨機取數據，也許第一個直接會告訴你，用隨機數取，切記，這個時候你必須控制你的大腦在這個方向繼續想下去，趕緊停止這種可怕的想法。因為這種查詢，對資料庫的效能毫無益處（消耗CPU）。較好的方案之一是先找到資料所在的條數N，然後再用LIMIT N, 1這樣查詢。

6、保證每張表都有一個主鍵ID

我們應該養成一種習慣，每設計新建一張表的時候，都應該為其設計一個ID字段，並讓其成為主鍵，而且最好是INT型（也有使用UUID的），同時設定這個ID欄位為自增（AUTO_INCREMENT）的標誌。

8、盡可能的使用NOT NULL

不要以為NULL不需要空間，事實是NULL也需要額外的空間，也許，很多有沒注意但是遇到過，NULL欄位在進行查詢比較的時候，是比較麻煩的。當然了，如果你實在是必須需要NULL的話，那沒轍，就使用吧，否則的話，就建議使用NOT NULL吧。

8、選擇合適的儲存引擎

在MySQL中有MyISAM和InnoDB兩種儲存引擎，兩者各有利弊，所以我們需要了解兩者的差異然後來做最合適的選擇，例如InnoDB支援事務而MyISAM不支持，MyISAM查詢比InnoDB快等等；總之，如果你不知道選擇什麼的話，那就用InnoDB吧。

9、把IP位址存為UNSIGNED INT

在遇到需要儲存IP位址的時候，很多人的第一想法都會是儲存VARCHAR(15)字串類型的，而不會想到要用INT整型來存儲；如果你用整型來存儲，只需要4個字節，並且你可以有定長的字段，而且這會為你帶來查詢上的優勢。

10、盡量不要在WHERE查詢時對字段進行null值判斷

我們都知道，檔我們對一個字段進行null的判斷時候，會比較慢的，這是因為這個判斷會導致引擎放棄使用所有現有的索引而進行全表掃描搜尋。

11、盡量不要使用%前綴的LIKE模糊查詢

模糊查詢，在日常開發中，我們都會經常遇到，但是我相信很多人都是直接LIKE ' %key_word%' 或LIKE '%key_word' 這樣搜尋的，這兩種搜尋方式，都會導致索引失效從而進行全表掃描搜尋。如果解決上面的這種模糊查詢呢，答案就是使用“使用全文索引”，具體的用法有興趣的可以自己查資料一波。

12、避免在WHERE查詢時對欄位進行表達式操作

例如查詢語句SELECT id FROM table WHERE num * 2 = 50;，這樣的查詢，對字段num做了一個乘2的算數操作，就會導致索引失效。

14、減少不必要的排序

排序操作會消耗較多的CPU資源，所以減少不必要的排序可以在快取命中率高等I/O足夠的情況下，會降低SQL的響應時間。

14、建議用JOIN代替子查詢

有的人會說，JOIN的性能其實也並不是很好呀，但是和子查詢比起來還是有很大的性能優勢的。具體的，可以了解一下子查詢的執行計劃相關的問題。

15、避免發生隱式類型轉換

類型轉換主要是指在WHERE子句中出現欄位的類型和傳入的參數類型不一致的時候發生的型別轉換；這是指在WHERE子句中出現欄位的型別和傳入的參數型別不一致的時候發生的型別轉換；這是因為如果我們傳入的資料類型和欄位類型不一致，MySQL可能會對我們傳的資料進行類型轉換操作，也可能不進行處理而直接交由儲存引擎去處理，這樣一來，就可能會出現索引無法使用的情況而造成執行計劃問題。

16、避免多表查詢欄位類型不一致

在遇到需要多表聯合查詢的時候，我們設計表結構的時候，盡量保持表與表的關聯欄位一致，並且都要設定索引。同時，多表連接查詢時，盡量把結果集小的表當作驅動表。

17、建議開啟查詢快取

大多數的MySQL伺服器都開啟了查詢緩存，這是提高效能最有效的方法之一，因為查詢快取由MySQL資料庫引擎自動處理，當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個快取中，這樣，後續的相同的查詢就不用操作表，而直接存取快取結果了。

18、使用UNION取代暫存資料表

UNION查詢可以把兩個或更多的SELECT查詢結果合併到一個查詢中，所以不再需要建立暫存表來完成。需要注意的是，使用UNION的所有SELECT語句中的字段數目要相同。

19、慎用IN查詢

IN以及NOT IN查詢都要慎重，因為可能會導致全表掃描，而對於連續的數值，能用BETWEEN就不要用IN了。

20、歡迎補充

結束語

這主要是從查詢角度去考慮優化，還有一些分錶、分區技術以及讀寫分離等；以上優化之處，如果說的不到位的地方，請大家諒解，MySQL優化的地方可以有很多處，歡迎提出其他優化建議，謝謝。

以上是mysql的20個最佳化總結的詳細內容。更多資訊請關注PHP中文網其他相關文章！