MySQL性能優(yōu)化技巧分享

MySQL性能優(yōu)化

在互聯(lián)網(wǎng)公司MySQL的使用非常廣泛，大家經(jīng)常會(huì)有MySQL性能優(yōu)化方面的需求。整理了一些在MySQL優(yōu)化方面的實(shí)用技巧。

Schema與數(shù)據(jù)類(lèi)型優(yōu)化

1. 整數(shù)通常是標(biāo)識(shí)列最好的選擇，因?yàn)樗鼈兒芸觳⑶铱梢允褂?code>AUTO_INCREMENT
2. 完全“隨機(jī)”的字符串（如：MD5()、SHA1()或者UUID()等產(chǎn)生的字符串）會(huì)任意分布在很大的空間內(nèi)，會(huì)導(dǎo)致INSERT以及一些SELECT語(yǔ)句變的很慢
3. 如果希望查詢執(zhí)行得快速且并發(fā)性好，單個(gè)查詢最好不要做太多的關(guān)聯(lián)查詢（互聯(lián)網(wǎng)公司非常忌諱關(guān)聯(lián)查詢），利用程序來(lái)完成關(guān)聯(lián)操作
4. 如果需要對(duì)一張比較大的表做表結(jié)構(gòu)變更（ALTER TABLE操作增加一列），建議先拷貝一張與原表結(jié)構(gòu)一樣的表，再將數(shù)據(jù)復(fù)制進(jìn)去，最后通過(guò)重命名將新表的表名稱修改為原表的表名稱。因?yàn)樵谧兏斫Y(jié)構(gòu)的時(shí)候很有可能會(huì)鎖住整個(gè)表，并且可能會(huì)有長(zhǎng)時(shí)間的不可用
5. 避免多表關(guān)聯(lián)的時(shí)候可以適當(dāng)考慮一些反范式的建表方案，增加一些冗余字段

InnoDB索引優(yōu)化

1. 如果不是按照索引的最左列開(kāi)始查找，則無(wú)法使用索引
2. 所有的非聚簇索引都需要先通過(guò)索引定位到對(duì)應(yīng)的主鍵，然后在到聚簇索引查找數(shù)據(jù)，所以在定義主鍵索引的時(shí)候一定要謹(jǐn)慎
3. 只有當(dāng)索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向（倒序或者正序）都一樣時(shí)，MySQL才能夠使用索引來(lái)對(duì)結(jié)果做排序。有一種情況下ORDER BY子句可以不滿足索引的最左前綴的要求，就是前導(dǎo)列為常量的時(shí)候。
4. 在使用like來(lái)匹配字符串類(lèi)型的字段的值時(shí)，盡可能的使用前綴匹配like ‘XX%'，避免使用 like ‘%XX'
5. 哈希索引是基于哈希表實(shí)現(xiàn)的，只有精確匹配索引所有列的查詢才有效，也不遵循索引的最左匹配原則
6. 當(dāng)服務(wù)器需要對(duì)多個(gè)索引做聯(lián)合操作時(shí)（通常有多個(gè)OR條件），建議修改成UNION的方式，這樣方便命中索引
7. 對(duì)于如何選擇索引的列順序有一個(gè)經(jīng)驗(yàn)法則：將選擇性最高的列放到索引最前列
8. 盡可能多的使用覆蓋索引（如果一個(gè)索引包含或者說(shuō)覆蓋所有需要查詢的字段的值，我們就稱之為覆蓋索引），通過(guò)EXPLAIN的Extra列可以看到“Using index”信息
9. 當(dāng)ID為主鍵時(shí)，創(chuàng)建索引(A)，相當(dāng)于創(chuàng)建了(A)和(A, ID)兩個(gè)索引
10. 表中的索引越多對(duì)SELECT、UPDATE和DELETE操作速度變慢，同時(shí)占用的內(nèi)存也會(huì)比較多
11. InnoDB在二級(jí)索引上使用共享鎖，但是訪問(wèn)主鍵索引需要排他鎖
12. 盡可能的使用WHERE IN和WHERE BETWEEN AND的方式來(lái)進(jìn)行范圍查詢
13. LIMIT的偏移量越大性能越慢
14. 編寫(xiě)查詢語(yǔ)句時(shí)應(yīng)該避免單行查找、盡可能的使用數(shù)據(jù)原生順序從而避免額外的排序操作，并盡可能使用索引覆蓋查詢

查詢性能優(yōu)化

1.對(duì)于低效的查詢，通常從兩個(gè)方面來(lái)分析：

1. 確認(rèn)應(yīng)用程序是否在檢索大量超過(guò)需要的數(shù)據(jù)。這通常意味著訪問(wèn)了太多的行，但有時(shí)候可能是訪問(wèn)了太多的列
2. 確認(rèn)MySQL服務(wù)器層是否在分析大量超過(guò)需要的數(shù)據(jù)行
   

2.一般MySQL能夠使用以下三種方式應(yīng)用WHERE條件，從好到壞依次為：

• 在索引中使用WHERE條件倆過(guò)濾不匹配的記錄
• 使用索引覆蓋掃描來(lái)返回記錄
• 從數(shù)據(jù)表中返回?cái)?shù)據(jù)，然后過(guò)濾不滿足條件的記錄
  

3.MySQL從設(shè)計(jì)上讓連接和斷開(kāi)連接都很輕量級(jí)，在返回一個(gè)小的查詢結(jié)果方面很高效。在一個(gè)通用服務(wù)器上，也能夠運(yùn)行每秒超過(guò)10萬(wàn)的查詢，一個(gè)千兆網(wǎng)卡也能輕松滿足每秒超過(guò)2000次的查詢，MySQL內(nèi)部每秒能夠掃描內(nèi)存中上百萬(wàn)行數(shù)據(jù)

4.在刪除大量數(shù)據(jù)時(shí)，建議每次刪除一小批量數(shù)據(jù)后，暫停一會(huì)兒再做下一次的刪除

5.無(wú)論如何排序都是一個(gè)成本很高的操作，所以從性能角度考慮，應(yīng)盡可能避免排序或者盡可能避免對(duì)大量數(shù)據(jù)進(jìn)行排序

6.COUNT()函數(shù)有兩種不同的作用：它可以統(tǒng)計(jì)某個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)。最簡(jiǎn)單的就是通過(guò)COUNT(*)來(lái)統(tǒng)計(jì)行數(shù)

7.關(guān)聯(lián)查詢的時(shí)候要確保關(guān)聯(lián)的字段上有索引

8.在數(shù)據(jù)量很大并且歷史數(shù)據(jù)需要定期刪除的情況下，可以考慮使用分區(qū)表

9.如果定了的索引列和分區(qū)列不匹配，會(huì)導(dǎo)致查詢無(wú)法進(jìn)行分區(qū)過(guò)濾

10.外鍵約束盡可能避免，通常通過(guò)程序來(lái)實(shí)現(xiàn)，心中要有外鍵

11.觸發(fā)器、存儲(chǔ)過(guò)程、自定義函數(shù)等最好不要使用

12.盡可能的利用查詢緩存，如果在寫(xiě)查詢語(yǔ)句的時(shí)候有一些不確定的數(shù)據(jù)（NOW()或者CURRENT_DATE()等）時(shí)，則不會(huì)被緩存

13.用多個(gè)小表代替一個(gè)大表對(duì)查詢緩存有好處

14.批量寫(xiě)入時(shí)只需要做一次緩存失效，所以相比單條寫(xiě)入（每寫(xiě)入一次，緩存就失效）效率更好，對(duì)于寫(xiě)密集型的應(yīng)用，直接禁用查詢緩存

15.如果緩存的空間太大，在過(guò)期操作的時(shí)候可能會(huì)導(dǎo)致服務(wù)器僵死

以上是個(gè)人在工作中的經(jīng)驗(yàn)總結(jié)，如果有描述錯(cuò)誤的地方希望大家可以幫忙指出，一起交流學(xué)習(xí)！

到此這篇關(guān)于MySQL性能優(yōu)化技巧分享的文章就介紹到這了,更多相關(guān)MySQL性能優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！