影響mysql效能的因素都有哪些

一些開發筆記： https://github.com/stayfoo/stayfoo-hub

影響資料庫效能的因素

• 影響資料庫效能的因素
  • sql 查詢速度
  • 伺服器硬體
  • 網路卡流量
  • 磁碟 IO
  • 網路卡流量
  • 大表
  • 大事務

QPS & TPS

QPS：每秒鐘處理的查詢量

• eg：
  • 一個 cpu 10ms 處理 1 個 sql，
  • 1s 處理 100 個 sql，那麼 QPS<=100
  • 一個 cpu 100ms 處理 1 個 sql，QPS<=10

TPS：

• 超高的 QPS 和 TPS 帶來的風險：效率低下的 SQL

資料庫的效能問題大部分都是由於 sql 的慢查詢造成的，大部分問題可以通過對 sql 的優化得到解決。

併發量 & CPU 使用率

併發量：同一時間處理的請求的數量；與同時連線數不同。

空閒的百分比值越高空閒率越高。

• 大量的併發和超高的 CPU 使用率

大量的併發：資料庫連線數被佔滿。（max_connections 預設 100）

超高的 CPU 使用率：因 cpu 資源耗盡而出現當機。

磁碟 IO

資料庫備份遠端同步計劃任務會導致 IO 達到峰值。

• 最好不要在主庫上資料庫備份
• 大型活動前取消這類計劃

風險：

磁碟 IO 效能突然下降（使用更快的磁碟裝置）

其他大量消耗磁碟效能的計劃任務（調整計劃任務，做好磁碟維護）

• 資料庫擴充套件需要完整性和一致性

網路卡流量

網路卡 IO 被佔滿（1000Mb/8 約等於 100MB）

• 如何避免無法連線資料庫的情況：
  • 減少從伺服器的數量
  • 進行分級快取
  • 避免使用 select * 進行查詢
  • 分離業務網路和伺服器網路

大表

• 記錄行數巨大，單標超過千萬行

• 表資料檔案巨大，表資料檔案超過 10G

• 第一、 大表對查詢的影響：

  • 慢查詢：很難再一定的時間內過濾出所需要的資料。
  • 查詢的維度區分度比較低（只有幾個維度，例如三方登入欄位：只有微信、qq 幾個有限的維度，查詢的資料量就比較大），會產生大量磁碟 IO，降低磁碟效率。

• 第二、大表對 DDL 操作的影響：

  • 建立索引需要很長的時間
  • Mysql 版本 < 5.5 建立索引會鎖表
  • Mysql 版本 >= 5.5 雖然不會鎖表，但會引起主從延遲
  • 大表對 DDL 操作的影響：修改表結構需要長時間鎖表。
  • 風險：會造成長時間的主從延遲。影響正常的資料操作。

• 如何處理資料庫中的大表：

  • 1. 分庫分表把一張大表分成多個小表。
    • 難點：
    • 分表主鍵的選擇；分表後跨分割槽資料的查詢和統計。
  • 2. 大表的歷史資料歸檔：
    • 減少對前後端業務的影響
    • 難點：歸檔時間點的選擇。如何進行歸檔操作。

大事務

• 什麼是事務？
  • 事務是資料庫系統區別於其他一切檔案系統的重要特徵之一
  • 事務是一組具有原子性的 sql 語句，或是一個獨立的工作單元

• 特點：原子性、一致性、隔離性、永續性

• 事務原子性 atomicity：

  • 一個事務被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作
  • 銀行例子：1. 取錢 2.存錢

• 事務的一致性 consistency：

  • 一致性是指事務將資料庫從一種一致性狀態轉換到另外一種一致性狀態，在事務開始之前和事務結束後資料庫中資料的完整性沒有被破壞

• 事務的隔離性 isolation：

  • 隔離性要求一個事務對資料庫中資料的修改，在未提交完成前對於其它事務是不可見的
  • SQL 標準中定義的四種隔離級別：
    • 未提交讀（READ uncommited）
    • 已提交讀（READ commited）（不可重複讀）
    • 可重複讀（repeatable READ）
    • 可序列化（serializable）
  • 隔離性由低->高，併發性由高->低

• 事務的永續性 durability：

  • 一旦事務提交，則其所做的修改就會永久儲存到資料庫中。此時即使系統崩潰，已經提交的修改資料也不會丟失

• 大事務：

  • 執行時間比較長，操作的資料比較多的事務
  • 風險：
    • 鎖定太多的資料，造成大量的阻塞和鎖超時
    • 回滾時所需時間比較長
    • 執行時間長，容易造成主從延遲

• 如何處理大事務：

  • 避免一次處理太多的資料
  • 移除不必要在事務中的 select 操作