MY SQL 知識

zping發表於2016-05-04
SQL

LINUX配置優化

  • 診斷命令
  • 硬體優化
    • raid 10
    • ssd
    • Fusion-io
    • 大記憶體
    • 電池後備SCSI磁碟快取
  • 系統優化
    • 日誌，資料分盤
    • IO排程演算法 deadline適用於磁碟 noop適用於ssd
    • XFS檔案系統
    • 避免使用swap 分割槽
    • fd限制 ulimit

MySQL引數配置

  • 基礎配置
    • 字符集
    • 資料目錄，日誌目錄，臨時目錄
    • sql_mode
  • 快取設定
    • 執行緒快取
    • 表快取
    • 排序快取
    • join快取
    • 查詢快取
    • binlog快取
    • ......
  • 閾值設定
    • max connection
    • timeout
    • slow query time
    • ......
  • innodb 設定
    • instances
    • size
    • io_capacity
    • innodb_flush_method=O_DIRECT 資料直接寫盤，避過OS cache 日誌還是OS flush
    • 雙一設定
    • ......

MySQL開發規範

  • DDL
    • 型別使用要精確
    • not null
    • 避免text/blob
    • 避免外來鍵
    • 自增主鍵
    • 避免冗餘索引
    • 索引數量控制
    • 索引欄位過濾度
  • DML
    • 避免使用觸發器，儲存過程
    • 儲存使用Mysql，不要有運算邏輯
    • 更新操作禁用limit
    • select * 禁用
    • in 優先 or
    • 同表alter操作合併

BIG SQL

  • 索引優化
    • WHERE 條件索引使用率，符合最左原則
    • 聯合索引，注意索引過濾度
    • 表join操作，注意加索引，且join欄位型別必須一致
  • SQL優化
    • 子查詢變成連線操作，優先使用獨立子查詢
    • 利用覆蓋索引，分頁操作
    • 拆分SQL，加快取表或者彙總表
    • 外在條件下推，減少中間結果集
    • 重寫SQL，利用過濾性更好的索引

主從延遲

  • 避免
    • 避免3B
    • 較少從庫壓力，包括伺服器和資料庫本身的
    • 從庫上有觸發操作，控制更新量
  • 改善
    • 升級從庫機器硬體ssd
    • 升級資料庫版本，5.6的庫併發 5.7的binlog執行緒併發
    • 第三方工具，丁奇的transfer外掛
    • inset...select rbr模式，可以先select 快取結果，後insert

排查方法

  • 伺服器
    • top
      • 檢視負載
      • 檢視CPU耗時
    • iostat
    • free
    • ps aux | grep pid
      • 檢視具體程式資訊
  • MySQL
    • SHOW FULL PROCESSLIST / dbaslowing
      • 資料庫目前執行SQL，連線數
      • 慢查詢實時採集，格式話輸出
    • SHOW ENGINE INNODB STATUS
      • 執行事務資訊
      • 死鎖資訊 / pt-deadlock-logger 週期採集死鎖資訊
      • buffer pool 資訊檢視
    • dbaprocess.sh
      • 檢視連線數排序，顯示連線服務

緊急恢復

  • 切換
    • 主從切換
      • 雙主模式，設定自增間隔，不停服
      • read_only 切換，保留靜態老主
  • 連線數打滿
    • pt-kill
    • mysqladmin kill

日常指標

  • zabbix
    • 歷史狀態查詢
    • 報警監控
    • 組合定製指令碼監控
  • 運維指令碼
    • 慢查詢指令碼每日郵件
    • 資料庫周健康度郵件
    • 核心表每日備份
    • 擴容從庫指令碼
    • 搭建級聯指令碼
    • 測試庫同步指令碼
    • 試執行環境對比指令碼
  • 備份
    • hotbak，dump
    • nfs掛載磁碟
    • hdfs資料冗餘

自動化運維

  • 自動稽核inception
  • 自動授權系統

MySQL HA

  • 中介軟體

    • dbproxy
      • 讀寫分離
      • 從庫HA，負載均衡
    • mycat
      • 讀寫分離
      • 從庫負載均衡
      • 分庫分表&全域性表
      • HA
      • 不支援分散式事務
      • 業務SQL限制，需要有路由key
      • 效能有所損耗

  • MHA

  • PXC

常用PT

  • pt-config 資料庫替換時，驗證引數一致性
  • pt-deadlock-logger 記錄歷史死鎖資訊
  • pt-duplicate-key-checker 冗餘索引檢測
  • pt-ioprofile IO除錯
  • pt-kill 定製kill連線執行緒
  • pt-online-schema-change OSC
  • pt-query-digest 狀態輸出慢查詢
  • pt-table-checksum 主從一致性驗證
  • pt-slave-find 主從拓撲結構
  • pt-fifo-split 大檔案匯入

 

相關文章