mysql效能優化

PinXiong發表於2020-08-01

在網際網路公司MySQL的使用非常廣泛,大家經常會有MySQL效能優化方面的需求。整理了一些在MySQL優化方面的實用技巧。

Schema與資料型別優化

  1. 整數通常是標識列最好的選擇,因為它們很快並且可以使用AUTO_INCREMENT

  2. 完全“隨機”的字串(如:MD5()SHA1()或者UUID()等產生的字串)會任意分佈在很大的空間內,會導致INSERT以及一些SELECT語句變的很慢

  3. 如果希望查詢執行得快速且併發性好,單個查詢最好不要做太多的關聯查詢(網際網路公司非常忌諱關聯查詢),利用程式來完成關聯操作

  4. 如果需要對一張比較大的表做表結構變更(ALTER TABLE操作增加一列),建議先拷貝一張與原表結構一樣的表,再將資料複製進去,最後通過重新命名將新表的表名稱修改為原表的表名稱。因為在變更表結構的時候很有可能會鎖住整個表,並且可能會有長時間的不可用

  5. 避免多表關聯的時候可以適當考慮一些反正規化的建表方案,增加一些冗餘欄位

InnoDB索引優化

  1. 如果不是按照索引的最左列開始查詢,則無法使用索引

  2. 所有的非聚簇索引都需要先通過索引定位到對應的主鍵,然後在到聚簇索引查詢資料,所以在定義主鍵索引的時候一定要謹慎

  3. 只有當索引的列順序和ORDER BY子句的順序完全一致,並且所有列的排序方向(倒序或者正序)都一樣時,MySQL才能夠使用索引來對結果做排序。有一種情況下ORDER BY子句可以不滿足索引的最左字首的要求,就是前導列為常量的時候。

  4. 在使用like來匹配字串型別的欄位的值時,儘可能的使用字首匹配like ‘XX%’,避免使用 like ‘%XX’

  5. 雜湊索引是基於雜湊表實現的,只有精確匹配索引所有列的查詢才有效,也不遵循索引的最左匹配原則

  6. 當伺服器需要對多個索引做聯合操作時(通常有多個OR條件),建議修改成UNION的方式,這樣方便命中索引

  7. 對於如何選擇索引的列順序有一個經驗法則:將選擇性最高的列放到索引最前列

  8. 儘可能多的使用覆蓋索引(如果一個索引包含或者說覆蓋所有需要查詢的欄位的值,我們就稱之為覆蓋索引),通過EXPLAINExtra列可以看到“Using index”資訊

  9. 當ID為主鍵時,建立索引(A),相當於建立了(A)和(A, ID)兩個索引

  10. 表中的索引越多對SELECTUPDATEDELETE操作速度變慢,同時佔用的記憶體也會比較多

  11. InnoDB在二級索引上使用共享鎖,但是訪問主鍵索引需要排他鎖

  12. 儘可能的使用WHERE INWHERE BETWEEN AND的方式來進行範圍查詢

  13. LIMIT的偏移量越大效能越慢

  14. 編寫查詢語句時應該避免單行查詢、儘可能的使用資料原生順序從而避免額外的排序操作,並儘可能使用索引覆蓋查詢

查詢效能優化

  1. 對於低效的查詢,通常從兩個方面來分析:

    • 確認應用程式是否在檢索大量超過需要的資料。這通常意味著訪問了太多的行,但有時候可能是訪問了太多的列
    • 確認MySQL伺服器層是否在分析大量超過需要的資料行
  2. 一般MySQL能夠使用以下三種方式應用WHERE條件,從好到壞依次為:

    • 在索引中使用WHERE條件倆過濾不匹配的記錄
    • 使用索引覆蓋掃描來返回記錄
    • 從資料表中返回資料,然後過濾不滿足條件的記錄
  3. MySQL從設計上讓連線和斷開連線都很輕量級,在返回一個小的查詢結果方面很高效。在一個通用伺服器上,也能夠執行每秒超過10萬的查詢,一個千兆網路卡也能輕鬆滿足每秒超過2000次的查詢,MySQL內部每秒能夠掃描記憶體中上百萬行資料

  4. 在刪除大量資料時,建議每次刪除一小批量資料後,暫停一會兒再做下一次的刪除

  5. 無論如何排序都是一個成本很高的操作,所以從效能角度考慮,應儘可能避免排序或者儘可能避免對大量資料進行排序

  6. COUNT()函式有兩種不同的作用:它可以統計某個列值的數量,也可以統計行數。最簡單的就是通過COUNT(*)來統計行數

  7. 關聯查詢的時候要確保關聯的欄位上有索引

  8. 在資料量很大並且歷史資料需要定期刪除的情況下,可以考慮使用分割槽表

  9. 如果定了的索引列和分割槽列不匹配,會導致查詢無法進行分割槽過濾

  10. 外來鍵約束儘可能避免,通常通過程式來實現,心中要有外來鍵

  11. 觸發器、儲存過程、自定義函式等最好不要使用

  12. 儘可能的利用查詢快取,如果在寫查詢語句的時候有一些不確定的資料(NOW()或者CURRENT_DATE()等)時,則不會被快取

  13. 用多個小表代替一個大表對查詢快取有好處

  14. 批量寫入時只需要做一次快取失效,所以相比單條寫入(每寫入一次,快取就失效)效率更好,對於寫密集型的應用,直接禁用查詢快取

  15. 如果快取的空間太大,在過期操作的時候可能會導致伺服器僵死

以上是個人在工作中的經驗總結,如果有描述錯誤的地方希望大家可以幫忙指出,一起交流學習!

相關文章