MySQL管理之道_ 效能調優、高可用與監控（第2版）-讀書筆記

MariaDB架構與歷史

• MariaDB：用Percona XtraDB代替InnoDB（前者是後者的增強版，完全相容後者）
  • 新功能：多源複製、基於表的並行複製
  • 整合更多儲存引擎：Aria（增強版MyISAM）、SphinxSE、TokuDB（可看作ARCHIVE的升級版）、Cassandra、CONNECT、SEQUENCE及Spider（分庫分表）
  • 5.5: 執行緒池技術，減少連線建立的開銷，適合於高併發短連線應用場景，如秒殺（？）
• 執行緒池、審計日誌等功能在MySQL企業版中，需要付費

MySQL 5.7與MariaDB 10.1新特性

• p14 OLTP只讀模式下，MySQL 5.7有近100w的QPS，比5.6效能高3倍；OLTP讀寫模式下，近60w TPS
  • 注：官方測試的硬體配置：E7-8890 v3（72核）、512GB記憶體（靠）
• 5.7 InnoDB儲存引擎的提升：
  • 更改索引名字不鎖表
  • 線上DDL修改VARCHAR屬性不鎖表（不過還是建議使用pt-online-schema-change？）
  • 支援中文全文索引
  • Buffer Pool預熱改進
  • undo log回滾日誌支援線上收縮
  • share.ibd通用表空間
  • innodb_print_all_deadlocks = 1
  • 支援InnoDB只讀事務
  • 支援InnoDB表空間資料碎片整理（Facebook contrib）
  • 支援虛擬列（函式索引）：mod_id int(11) generated always as (id % 10) virtual, 插入時使用字面量default
  • 支援一張表多個INSERT／UPDATE／DELETE觸發器
  • 引入執行緒池（阿里巴巴開源的druid連線池？？？）
  • 支援explain update／delete（資料更新語句裡面也可以使用join？）
  • p57 MariaDB／TokuDB：為什麼要關閉Transparent Huge Page？？
  • 優化器改進（SQL查詢優化與編譯器後端優化技術是有共通之處的）
    • 子查詢採用半連線優化（MySQL的子查詢一向支援不好，這方面沒有Oracle做的好，應用開發人員更喜歡寫子查詢？？？）
      • update／delete仍然不行
    • 優化派生子查詢（謂詞過濾條件下推）
    • 派生表索引優化（略）
    • 優化排序limit
    • 優化IN條件表示式
    • 優化union all（不建立臨時表）
    • 支援索引下推
    • 支援Multi Range Read（MRR）優化，收集主鍵並排序，把磁碟隨機IO改成順序IO
    • 支援Batched Key Access（BKA）索引優化，同樣是把隨機磁碟IO轉換為順序IO
    • MariaDB 10.1: 支援Hash Join索引優化（這個地方很容易讓我想起Impala裡的2種分散式join處理了）
      • Block Nested Loop Hash
      • Block Index Hash Join Batch Key Access Hash
  • 半同步複製改進
    • 《MySQL運維內參》裡有講到，從略
  • GTID複製改進
    • p96 從庫上執行操作時，切記先關閉binlog，再執行DML／DDL
  • 5.7從庫多執行緒複製：基於binlog組提交（《MySQL運維內參》裡有講到，多個處於prepare的事務可同時提交，略）
  • slave支援多源（多個主庫）複製

故障診斷

• innodb_buffer_pool_size：可設定為60%~80%的記憶體，甚至可將資料庫全部放入記憶體（要是機房突然掉電怎麼辦？）
• p107 磁碟技術：比較火的Fusion-io？？？
• p109 Linux伺服器效能監控：dstat？
• p125 如果是RR預設隔離級別，建議設定binlog_format=ROW。如果是Read-Commited，則ROW與MIXED效果是一樣的（...）
• p130 誤刪除ibdata資料檔案，怎麼辦？（資料庫仍然開著，不要殺死mysqld程式，檔案內容可從/proc//fd恢復）
• p132 update忘加where過濾條件誤操作恢復（模擬Oracle閃回）：實質就是從binlog中解析出原來的資料，再人工undo... 矬
  • 為什麼不能直接rollback呢？？見鬼

同步複製報錯故障處理

• p148 master上更新了一條記錄，slave上卻找不到（仍然是手工分析處理binlog，瘋了）
• slave意外當機，可能損壞relay-log：找到binlog和POS點，重新同步（5.5 my.cnf：relay_log_recovery=1）
• ? 多臺slave上server-id重複（由於直接複製master點my.cnf到slave導致）
• 避免master上執行大事務（老生常談了）
  • 利用儲存過程，把大事務轉換為小批量操作....
• p156 binlog_ignore_db引起的同步複製故障：使用replicate-ignore-db=代替

效能調優

• 資料型別：選擇夠用的就行
  • timestamp：預設隨行更新而更新？？？（這個特性我怎麼之前從來沒注意過？不過我之前實際專案開發也只用到4.1/5.0）
  • 5.6: year(2)自動轉換為year(4)
  • varchar(5)升級到varchar(10)的底層磁碟儲存不變，但decimal(10,1)升級到decimal(10,2)就不行了——後者應該是底層位儲存模式不一致
• p192 5.6 online DDL
• 採用合適的鎖機制
  • 表鎖
  • 行鎖
    • p197 只有通過索引條件檢索資料，InnoDB才會使用行鎖，否則使用表鎖
  • 頁面鎖（粒度介於前兩者之間，NDB？）
• 選擇合適的事務隔離級別
  • innodb_flush_log_at_trx_commit=0(每隔1秒刷盤)/1（每次事務提交刷盤）/2（僅刷到日誌檔案）
    • p204 事務提交後，先刷binlog，再刷到redo log,... => 中間發生當機，可導致主從資料不一致
  • p208 間隙鎖：主要是防止幻讀。RR隔離級別下，當對資料進行條件／範圍檢索時，對其範圍內也許並不存在的值加鎖。RC級別下，沒有間隙鎖。
• SQL優化與合理利用索引
  • p212 error 1093: 通過子查詢刪除已查詢的記錄會報錯，可更改子查詢引入臨時表（更像是MySQL底層實現的bug？？？）
  • p216 類似select count(*)千萬不要在主庫上執行，因為InnoDB無表級計數器，需要全表掃描一次才能得到彙總
  • p219 避免使用having子句，用where限制記錄的數目
  • p222 聯合索引要遵循最左原則（這個太不智慧了！）
  • p225 當取出資料超過全表的20%，優化器就不會使用索引了；
    • 一條SQL只能有一個索引，如果有多個，優化器選擇最優的（！）
    • order by後如果有多個欄位排序，順序要一致，如果一個生序一個降序，會出現Using filesort（效能差）

備份與恢復

• p259 取代mysqldump的新工具：mydumper
• p263 熱備份與恢復
  • xtrabackuo／innobackupex（後者是前者的perl指令碼封裝）

高可用MHA架構叢集管理

• MHA與MMM都是採用Perl編寫的（？）

MySQL架構演進：“一主多從、讀寫分離”

• p294 HAProxy提供了高可用、負載均衡以及基於TCP／HTTP的應用代理。
• p295 MySQL Proxy一直沒有釋出GA版本，不能用在生產系統裡？？？？
  • MaxScale的GA版本？
  • 作者這裡推薦OneProxy，但是OneProxy為什麼不支援預編譯語句？怎麼防止SQL拼接的注入攻擊？？見鬼
    • CSDN上曾經有篇文章談到基於proxy的LB解決方案，技術核心在於SQL語句的語法解析... 另，淘寶也有過類似應用

Codership Galera Cluster叢集架構搭建與管理

• HAProxy結合Galera Cluster實現無單點秒級故障轉移*

OneProxy分庫分表的搭建與管理

• 資料訪問層TDDL／ZDAL是個什麼鬼？
• 注意：分庫分表不支援：跨庫join、分散式事務XA、儲存過程

Lepus慢日誌分析平臺搭建與維護