隨着MySQL應用的不斷普及和自身發展，如何更好的優化MySQL和使用MySQL，依然是一個比較有挑戰的問題，尤其是在業務快速增長的場景下。本次分享主要介紹一些通用的運維優化實踐和問題，以及未來的一些方向。
目錄
- MySQL的優勢和劣勢
- 數據庫規範化
- Sharding拆分
- 數據庫備份
- 性能優化
從每個月的db engines排名可以看到，關係數據庫依然占主導地位，nosql的種類和可選擇空間更大，總共283種數據庫，裏面大多數也是NoSQL。
如何選擇數據庫，從以下幾個因素考慮：
- 應用場景:OLTP or OLAP
- 數據量:億級，百億，還是千億？
- 可用性要求：故障時間要求
- 數據安全性要求
- 運維複雜度
- 事務支持
上面的兩張圖介紹了目前幾種主流代表性數據庫的優缺點和典型應用場景。
上圖是之前在微博我們針對不同場景採用的數據庫。
首先，我們羅列幾點MySQL的優勢和劣勢：
1、優勢
- 使用簡單
- 開源免費
- 擴展性”好”，在一定階段擴展性好
- 社區活躍，功能逐步完善
- 性能可以滿足互聯網存儲和性能需求，離不開硬件支持官方支持
2、劣勢：
- 優化器對複雜SQL支持不好
- 對SQL標準支持不好
- 大規模集羣方案不成熟，主要指中間件
- 邏輯複製
- Online DDL
- HA方案不完善
- 備份和恢復方案還是比較複雜，需要依賴外部組件
- 展現給用户信息過少
- 眾多分支
上述可以看到MySQL面臨的問題還有很多,而這些問題是運維中需要化解的，也是DBA實現價值的地方。MySQL的不斷髮展也離不開社區支持，比如Google最早提交的半同patch，後來也合併到官方主線。Facebook Twitter等也都開源了內部使用MySQL分支版本，包含了他們內部使用的patch。
其次，我們看看MySQL DBA的日常需求：
- 滿足各種各樣的開發需求
- 各式各樣的Schema審核
- SQL優化
- 各種救火和處理報警 :主庫故障，緩存“雪崩”
- 各種業務和項目上線
- 業務溝通和需求審核
DBA解放自己和提高效率的前提有：規範化，自動化，平台化。
那麼如何規範化，我們來重點講述一下。
數據庫規範主要包含兩部分：
1.數據庫開發規範：
開發規範是針對內部開發的一系列建議或規則，由DBA制定（如果有DBA的話）。開發規範也包含：基本命名和約束規範，字段設計規範，索引規範，使用規範四個部分部分。
意義：（1）保證線上數據庫schema規範，減少出問題概率，方便自動化管理；（2）需要長期堅持，是一個雙贏的事情。
規範示例：
- 表字符集選擇UTF8 ，如果需要存儲emoj表情，需要使用UTF8mb4(MySQL 5.5.3以後支持)
- 存儲引擎使用InnoDB
- 變長字符串儘量使用varchar 和varbinary
- 不在數據庫中存儲圖片、文件等
- 每張表數據量控制在5億以下
2.數據庫運維規範：
- SQL審核，DDL審核和操作時間，尤其是大表DDL
- 高危操作檢查，Drop做好數據備份
- 權限控制，既包括DBA自身，也包括開發
- 日誌分析，主要是指的MySQL慢日誌
- 高可用方案， 定期做演練和測試
- 數據備份方案
在這裏説一下MySQL DDL問題：
- 原生MySQL執行DDL是需要鎖表的，對服務影響很大。
- 雖然MySQL 5.6和5.7也一直在做，但是對於生產上依然不是那麼完美。
- MySQL在這方面支持的是比較差的，對DBA來説是很痛苦的。
下面是一些方案對比
下圖是實際運維過程中可以採用的DDL方案
從上圖可以看出，MySQL5.6+的Online DDL和pt－osc鎖粒度是最輕的，不過pt-osc更通用一些。
pt-osc的原理 ，還是很巧妙的：
MySQL 5.6和pt-osc的對比，在某些場景5.6還是要好於pt-osc的，畢竟pt-osc 每次都要copy全表數據。
pt-OSC一些坑：
- 添加唯一鍵，導致數據丟失
- 延時備份的問題
- 行格式下，只在從庫使用OSC，丟數據
整體來説pt－osc的可靠性還是很高的。
集羣方案主要是如何組織MySQL實例的方案，主流方案核心依然採用的是MySQL原生的複製方案。原生主從同步肯定存在着性能和安全性問題。
MySQL 半同步複製。
現在也有一些其他選擇，理論上可用性更高的方案：
- Percona XtraDB Cluster(沒有足夠的把控力度，不建議上)
- MySQL Cluster(有官方支持，不過實際用的不多)
- group replication（MySQL 5.7官方支持）
以下是MySQL複製支持的複製拓撲:
不同集羣方案的可靠性：
接下來我們講一下sharding拆分問題：
Sharding is very complex, so itʼs best not to shard until itʼs obvious that you will actually need to!
Sharding是按照一定規則數據重新分佈的方式，拆分是對應用層有損的，主要解決單機寫入壓力過大和容量問題。主要有垂直拆分和水平拆分，拆分要適度，切勿過渡拆分，新浪微博單表最大60億＋，單表數據文件大小1TB＋，DBA有時候就要懶一些。
上圖是兩種拆分的架構。
然後我們講一下很重要的數據庫備份
這個不論是什麼數據庫，數據庫數據安全性是首先要保證的，也是最核心的。平時優化做的再好，一旦需要恢復時候，備份有問題就掛了。備份的意義是什麼呢 。
數據恢復！
我們來看一下當前的各種備份方案：
- 全量備份 VS 增量備份
- 熱備 VS 冷備
- 物理備份 VS 邏輯備份
- 延時備份
- 全量binlog備份
我建議的方式是：
熱備＋物理備份，核心業務：延時備份＋邏輯備份+全量binlog備份
下面説一下性能優化：
1.複製優化
這是MySQL應用最普遍的應用的技術，擴展成本低。為邏輯複製。單線程問題，從庫延時問題。可以做備份或讀複製。問題很多，但是能解決基本問題。
原理圖如下，大家應該都瞭解。
單線程解決方案
1.官方5.6+多線程方案
2. Tungsten和阿里的transfer為代表的第三方工具
3.sharding
4.硬件升級
下圖複製矩陣對大家選擇複製方案可以參考
半同步
更好的數據安全性
可以配置多個從庫
引入loss-less semireplication,,通過 rpl_semi_sync_master_wait_point
可以通過5.6+的mysqlbinlog作為從庫，可以提高半同步複製效率
loss-less改造的原理
以下是複製的一些注意點
- Binlog格式，建議都採用row格式
- Replication filter應用
- 主從數據一致性問題，比如出現不一致如何修復
- row格式下的數據恢復問題
- GTID應用
2.InnoDB優化
開源事務存儲引擎，支持ACID，支持事務四個隔離級別更好的數據安全性，高性能高併發，MVCC，細粒度鎖支持O_DIRECT。
主要優化參數如下：
InnoDB目前的一些特性：
- Bufferpool預熱和動態調整大小
- Page size自定義調整
- InnoDB 壓縮，大大降低數據容量，一般可以壓縮50%
- Transportable tablespaces，遷移ibd文件，用於快速單表恢復
- Memcached API，full text，GIS等
下圖是MySQL5.6和MySQL 5.7的默認參數對比，大家可以感受一下
3.系統優化
以下是系統優化常見的幾個點：
- NUMA問題，建議關閉，其實不關閉也沒發現特別大問題
- 調整swappiness
- 修改IO調度算法為noop/deadline
- 文件系統XFS/Ext4
- 系統limits限制
- 網卡多隊列，當然一般可能遇不到這種場景
- Io中斷多隊列，對於高性能存儲設備是必要的
4.未來可優化:
未來可優化主要有兩個點：
軟硬件結合
軟件優化
5.軟硬件優化案例：
接下來我們來看一個案例：
Amazon Aurora：
Compatible with the open source MySQL
Most of the smarts are in the storage
A data insert in MySQL requires six writes ，Aurora requires only two
軟硬件結合
最重要的地方就是可用性的提升，性能是其次。當然現在aurora的健壯性還需要時間檢驗，據説還是有坑的。
amazon aurora文檔上的架構圖
6.軟件和存儲層的優化
LSM Tree:LevelDB,RocksDB
適配高性能存儲SSD，更高的壓縮比,，更低的寫入放大比例
缺點：讀性能差
適合寫多讀少場景
MyRocks: MySQL + RocksDB
總結
- MySQL是可以用好的
- MySQL可選的方案和可優化的點依然很多
- MySQL 5.7性能和新特性還是很有吸引力的
當然依然會有人都會來吐槽優化器是做的爛，比xxxxx差遠了，應該把MySQL換掉，優化器差這是不爭的事實，但並不影響在互聯網場景的應用，MySQL也是有自己的優勢的，所以不要輕易説誰一定能夠替代誰，場景不一樣，都會有自己的短板。對待技術本身要寬容，比如最好的編程語言 最好的數據庫之類的這種非黑即白的定義，對待技術細節要深究。
講師介紹：楊尚剛
- 【DBA+社羣】聯合發起人
- 美圖高級DBA。數據庫負責人，負責美圖後端數據存儲平台建設和架構設計。
- 前新浪高級數據庫工程師，負責新浪微博核心數據庫架構改造優化，主導了新浪微博核心數據庫的歷次架構變遷,數據庫平台相關的服務器存儲選型設計。
本文來自雲棲社區合作伙伴"DBAplus"，原文發佈時間：2015-12-31
