mysql的20條優(yōu)化總結(jié)

前言

現(xiàn)如今，數(shù)據(jù)庫的操作越來越成為整個(gè)應(yīng)用的性能瓶頸了，這點(diǎn)對于Web應(yīng)用尤其明顯。所以，我整理了MySQL優(yōu)化的幾點(diǎn)建議，希望這些優(yōu)化技巧對您有用，總結(jié)不到的，歡迎大家補(bǔ)充。

SQL執(zhí)行慢的原因

1. 網(wǎng)絡(luò)速度慢，內(nèi)存不足，I/O吞吐量小，磁盤空間滿了等硬件問題

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

3. 數(shù)據(jù)表里的數(shù)據(jù)記錄過多

4. 服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置也可能會影響

5. 開發(fā)者編寫的SQL效率

6. 其他

1、EXPLAIN分析你的SELECT查詢

很多情況下，使用EXPLAIN關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語句的，這可以幫你分析你的查詢語句，從而或許能盡快的找到優(yōu)化方法以及潛在的性能問題。具體EXPLAIN的使用以及各個(gè)參數(shù)的含義，請查閱相關(guān)文檔即可。

2、SELECT查詢必須指明字段名

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

3、查詢一條數(shù)據(jù)的時(shí)候，使用 LIMIT 1

減少多余的查詢，因?yàn)橹付╨imit 1后，查詢到一條數(shù)據(jù)就不再繼續(xù)查詢了，使得EXPLAIN中type列達(dá)到const類型，查詢語句更優(yōu)。

4、為搜索的WHERE字段建立索引

一般，每個(gè)表我們都會設(shè)置一個(gè)主鍵，而索引并不一定就是給主鍵。如果在你的表中，有某個(gè)字段你總要會經(jīng)常用來做WHERE查詢搜索，而且是讀大于寫的，那么，請為其建立索引吧，有興趣了解更多建立索引的的原則，可以查閱相關(guān)資料。

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

如果你想隨機(jī)取數(shù)據(jù)，也許第一個(gè)直接會告訴你，用隨機(jī)數(shù)取，切記，這個(gè)時(shí)候你必須控制你的大腦在這個(gè)方向繼續(xù)想下去，趕緊停止這種可怕的想法。因?yàn)檫@種查詢，對數(shù)據(jù)庫的性能毫無益處（消耗CPU）。更好的方案之一是先找到數(shù)據(jù)所在的條數(shù)N，然后再用LIMIT N, 1這樣查詢。

6、保證每張表都有一個(gè)主鍵ID

我們應(yīng)該養(yǎng)成一種習(xí)慣，每設(shè)計(jì)新建一張表的時(shí)候，都應(yīng)該為其設(shè)計(jì)一個(gè)ID字段，并讓其成為主鍵，而且最好是INT型（也有使用UUID的），同時(shí)設(shè)置這個(gè)ID字段為自增（AUTO_INCREMENT）的標(biāo)志。

8、盡可能的使用 NOT NULL

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

8、選擇合適的存儲引擎

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

9、把IP地址存為UNSIGNED INT

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

10、盡量不要在WHERE查詢時(shí)對字段進(jìn)行null值判斷

我們都知道，檔我們對一個(gè)字段進(jìn)行null的判斷時(shí)候，會比較慢的，這是因?yàn)檫@個(gè)判斷會導(dǎo)致引擎放棄使用所有已有的索引而進(jìn)行全表掃描搜索。

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

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

12、避免在WHERE查詢時(shí)對字段進(jìn)行表達(dá)式操作

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

14、減少不必要的排序

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

14、建議用JOIN代替子查詢

有的人會說，JOIN的性能其實(shí)也并不是很好呀，但是和子查詢比起來還是有很大的性能優(yōu)勢的。具體的，可以了解一下子查詢的執(zhí)行計(jì)劃相關(guān)的問題。

15、避免發(fā)生隱式類型轉(zhuǎn)換

類型轉(zhuǎn)換主要是指在WHERE子句中出現(xiàn)字段的類型和傳入的參數(shù)類型不一致的時(shí)候發(fā)生的類型轉(zhuǎn)換；這是因?yàn)槿绻�我們傳入的�?shù)據(jù)類型和字段類型不一致，MySQL可能會對我們傳的數(shù)據(jù)進(jìn)行類型轉(zhuǎn)換操作，也可能不進(jìn)行處理而直接交由存儲引擎去處理，這樣一來，就可能會出現(xiàn)索引無法使用的情況而造成執(zhí)行計(jì)劃問題。

16、避免多表查詢字段類型不一致

在遇到需要多表聯(lián)合查詢的時(shí)候，我們設(shè)計(jì)表結(jié)構(gòu)的時(shí)候，盡量保持表與表的關(guān)聯(lián)字段一致，并且都要設(shè)置索引。同時(shí)，多表連接查詢時(shí)，盡量把結(jié)果集小的表作為驅(qū)動表。

17、建議開啟查詢緩存

大多數(shù)的MySQL服務(wù)器都開啟了查詢緩存，這是提高性能最有效的方法之一，因?yàn)椴樵兙彺嬗蒑ySQL數(shù)據(jù)庫引擎自動處理，當(dāng)有很多相同的查詢被執(zhí)行了多次的時(shí)候，這些查詢結(jié)果會被放到一個(gè)緩存中，這樣，后續(xù)的相同的查詢就不用操作表，而直接訪問緩存結(jié)果了。

18、使用UNION代替臨時(shí)表

UNION查詢可以把兩條或更多的SELECT查詢結(jié)果合并到一個(gè)查詢中，從而不再需要?jiǎng)?chuàng)建臨時(shí)表來完成。需要注意的是，使用UNION的所有SELECT語句中的字段數(shù)目要相同。

19、慎用IN查詢

IN以及NOT IN查詢都要慎重，因?yàn)榭赡軙��?dǎo)致全表掃描，而對于連續(xù)的數(shù)值，能用BETWEEN就不要用IN了。

20、歡迎補(bǔ)充

結(jié)束語

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

以上就是mysql的20條優(yōu)化總結(jié)的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！