MySQL調優 優化需要考慮哪些方面

優化目標與方向定位

• 總體目標：使得響應時間更快，吞吐量更大。 （throughout --- 吞吐量：單位時間內處理事務的數量）

• 如何找到需要優化的地方

  • 使用反饋。比如做出一些操作後導致效率降低

  • 分析日誌。

  • 監控伺服器資源。系統，記憶體，I/O

  • 監控資料庫運行狀況

可優化維度

設計優化

• 選擇適合的DBMS
• 對錶恰當的設計
  • 儘量遵循第三範式。減少冗餘的同時減少增刪改時出錯的可能。
  • 適當地"反範式"，以空間換時間，提高多表聯查的效率。
  • 選擇恰當的欄位類型。儘量選擇數據類型，儘量選擇字元長度小的字元類型。

查詢優化

• 對SQL查詢進行邏輯優化 --- 就是使用恰當的SQL語句讓查詢速度更快
  • 比如“小表驅動大表”的EXISTS和IN
  • 比如子查詢優化，簡化查詢條件等等
• 對SQL查詢進行物理優化 --- 就是通過索引或錶鏈接的方式進行優化，本質上是對Server層優化器和執行器進行“人工輔助”，人為地減輕優化器和執行器的壓力。
  • 索引
    • 為表設計精簡且高效的索引 --- 索引不是越多越好，索引需要占據存儲空間，過多的索引也會提高優化器選擇索引的難度。比如欄位內數據重覆度高時不建立索引，如性別
    • 若在where中對索引欄位進行了表達式計算，會造成該欄位索引失效。
    • 設計聯合索引時選擇恰當的順序 --- 最左首碼原則
  • 表連接
    • 單表：全表掃描或局部掃描
    • 兩表：合併連接，HASH連接，嵌套迴圈連接
    • 多表：連接順序

外置緩存

數據都是存放在資料庫（磁碟）中的，在有使用需要的時候就會將磁碟數據調入記憶體。但當用戶量增大時，使用大量數據，頻繁讀取磁碟會消耗大量資源。因此我們可以事先將常用的數據放入記憶體中來提高查詢效率。

• 鍵值存儲資料庫 Redis 和 Memcached 等
• Redis 支持持久化且支持的數據類型和數據結構比Memcached多。Memcached僅進行記憶體存儲且僅支持鍵值對存儲。
• 對於查詢響應要求高的場景可以考慮上述記憶體資料庫，不過增加的開發人員的工作量。

庫級優化

一般來說現在常見的關係型資料庫單表可以存儲億級的數據量。

當數據量達到億級以上時可以採用以下方案進行庫級優化。

• 讀寫分離：使用主從資料庫代替單一資料庫，降低單一資料庫時的負載。主庫完成寫操作，從庫完成讀操作。
• 分庫分表
  • 垂直切分
    • 垂直分庫：數據表過多時，對錶進行劃分，將相關聯的數據表存放在一個庫中
    • 垂直分表：數據表列較多時，對列進行劃分並拆分成多個表，將經常一起使用的列存入一張表中
  • 水平切分
    • 表中數據量達到億級以上時，在保持相同的表結構的情況下，將表按照某一屬性拆分成不同小表。
• 分庫分表也會增加維護和使用成本，要加以平衡。