MySQL調優

yetangjian發表於2022-05-21
MySql

調優的目的？讓吞吐量更大，響應速度更快。

關於資料庫優化，我們從以下5個維度進行。

一.優化表結構

表結構儘量遵循三正規化的原則，在進行多表查詢的時候，必要時可以採用反正規化化進行優化。

什麼叫正規化？

  • 在關係型資料庫中，關於資料表設計的基本原則，規則就稱為正規化。

一共有6大正規化，知道前5個就行。滿足了高階正規化,就一定滿足低階正規化。比如滿足第三正規化,就一定滿足第1,2正規化。

  • 第一正規化：確保每一個欄位保證"原子性",不能被拆分。比如有一個欄位叫"個人資訊",它就可以拆分為地址,年齡,姓名等。就不滿足第一正規化

  • 第二正規化：確保表中的每一條記錄,都有唯一的標識(主鍵)。所有的非主鍵欄位,必須完全依賴主鍵。不能部分依賴。
    注意：這裡的主鍵是聯合主鍵。比如下圖：姓名，年齡依賴球員編號；比賽時間，比賽場地依賴比賽編號；只有得分全部依賴

  • 第三正規化：確保非主鍵之間是相互獨立的,不能產生依賴。下圖就不滿足

  • 巴斯正規化(BCNF)：3NF的增強版，在3NF的基礎上消除了主屬性對候選鍵的部分依賴或者傳遞依賴的關係。相當於主鍵中也產生了依賴關係,就不滿足巴斯正規化。比如下圖

  • 第四正規化：一個表中只有一對1:多的關係。如果一個表中有多個1：多的關係就不滿足第四正規化。

  •  第五正規化：也叫完美正規化，瞭解有這麼個東西就行

正規化的優缺點？

  • 優點：消除資料冗餘
  • 缺點：降低查詢效率，因為正規化越高，設計出來的資料表就越多，就需要很多的關聯查詢。

反正規化化？

  • 是一種空間換時間的優化手段。因為我們遵循業務優先原則，可以通過在表中增加冗餘欄位來提高資料庫的讀效能。
  • 當冗餘資訊有價值或者能大幅度提高查詢效率的時候，我們才會採用反正規化化進行優化。

 

資料庫的設計原則？三少一多

  • 資料表的個數越少越好
  • 資料表中的欄位個數越少越好
  • 資料表中聯合主鍵的欄位個數越少越好
  • 使用主鍵和外來鍵越多越好。這裡是指外來鍵關係越多，就可以重複的利用資料，而不是指在表中建立好多外來鍵。

資料庫表建模的工具?

  • PowerDesigner
二.優化邏輯查詢

關聯查詢優化：最好"被驅動表加索引"

  • 外連線：一般驅動表是全表查詢(就算新增索引也是index),被驅動表是索引查詢。(也就是說最好給被驅動表新增索引，驅動表加不加都行)
    • 如：student是驅動表，book是被驅動表   ：EXPLAIN SELECT * FROM student LEFT JOIN book ON student.card = book.card；
  • 內連線：誰是驅動表誰是被驅動表由優化器決定,優化器滿足"小表驅動大表"。(2個都加索引,讓優化器自己決定;如果只加 一個索引,優化器肯定選擇加索引的作為被驅動表)

優化器中join 的原理？

  • 不加索引
    • Simple Nested-Loop Join(簡單巢狀迴圈連線) 

    • Block Nested-Loop Join(塊巢狀迴圈連線)：不再逐條獲取驅動表的資料，而是一塊一塊的獲取，引入join buffer快取區

　　

    • Hash Join：MySQL8.0之後預設使用hash join。可以做大資料集連線。
  • 加索引
    • Index Nested-Loop Join(索引巢狀迴圈連線)

 

 子查詢優化：

  • 子查詢執行效率不高，使用關聯查詢(join)代替子查詢。
  • 效率不高的原因：
    • 查詢的過程中需要建立一個臨時表，查詢完畢，再撤銷臨時表。消耗效能
    • 臨時表都不會使用索引

 排序優化：

  • 前提知識：MySQL支援2中排序方式，所以優化也是從這倆方面考慮
    • index排序：b+樹的葉子節點就是按照排序進行的，使用索引直接就可以保證有序性
    • FileSort排序：將需要排序的資料載入到記憶體中，然後進行排序。
  • 儘量使用索引完成order by排序。如果where 和order by 後面的列相同就用單索引，不同就用聯合索引。
  • 對FileSort進行調優
    • 前提知識：FileSort有2種演算法
      • 雙路排序(慢)：進行倆次磁碟掃描，第一次只載入需要排序的列到sort_buffer，進行排序。然後根據排序好的列，第二次從磁碟讀取其他的列。
      • 單路排序(快)：一次性將所有列的資料載入到sort_buffer中，進行排序。
    • 提高sort_buffer_size：不管哪種演算法，提高這個記憶體值肯定加大效率。
    • 提高max_length_for_sort_data：這個引數就是一個界限，需要返回的列總長度大於這個值就使用雙路，小於這個值就使用單路。

group by優化：

  • group by 優化的方法和order by一樣。

分頁查詢優化：

  • 如果像下圖查詢的情況極端，儘量的使用表中其他欄位的索引。

 

其他的優化

  • exists和in：小表驅動大表

  • count(*)和count(1)和count(具體欄位)的效率
    • count(*)和count(1)沒有本質區別，執行時間基本一樣
    • count(具體欄位)的時候，儘量使用佔用空間少的二級索引。因為二級索引儲存的資訊相比聚簇索引要少很多。count(*)和count(1) 系統會自動選擇佔用空間少的二級索引進行統計。　　
    • innodb的count()是O(n)級別的，MyISAM是O(1)級別的。
  • 關於select *
    • 儘量使用什麼欄位就指出來，不要使用select *。因為會載入很多沒用的列。
    • 無法使用覆蓋索引
  • 關於Limit 1：
    • 如果是全表掃描，加上Limit 1。找到資料就不會再繼續查詢了，加快查詢效率
    • 如果是唯一索引，找到資料也不會繼續查詢了，Limit 1 就不管用了。

 

 三.優化物理查詢(索引)
  • 選擇適合做索引的欄位(MySQL索引講)
  • 哪些情況導致索引失效(MySQL索引講)
  • 使用覆蓋索引：一個索引包含了滿足查詢結果的資料就叫做覆蓋索引。(也就是需要的列恰好都在索引的葉子節點上儲存，不需要回表)
    • 好處：無需回表；可以把隨機IO變成順序IO加快查詢效率(利用到索引都是順序IO，因為索引就是有順序的)。
  • 使用索引下推：如圖

    • 索引下推就用在一些and查詢語句中，本來通過非聚集索引zipcode查詢出來資料，要進行回表，但是如果查出來100條，分別對這100條進行回表就很浪費效能，icp就是先不進行回表，使用後面的條件進行過濾，過濾完畢之後比如剩下10條，對這10條進行回表就行了。
    • set optimizer_switch = 'index_condition_pushdown=on'     //開啟索引下推
四.使用快取

對於熱點資料可以使用redis或者Memcached作為快取，減少資料庫的壓力

五.庫級優化

 後期補上

 

 

今天是除夕，祝大家新年快樂！

 寄語：放棄可以找到一萬個理由,堅持只需要一個信念! 

相關文章