引用
美圖公司資料庫高階 DBA，負責美圖後端資料儲存平臺建設和架構設計。前新浪高階資料庫工程師，負責新浪微博核心資料庫架構改造優化，以及資料庫相關的伺服器儲存選型設計。之前在「高可用架構」發表的《單表 60 億記錄等大資料場景的 MySQL 優化和運維之道》廣受好評。
2015 年最重磅的當屬 MySQL 5.7 GA 的釋出，號稱 160 萬隻讀 QPS，大有趕超 NoSQL 趨勢。
上面這個圖是 Oracle 在只讀場景下官方測試的結果，看上去 QPS 確實提升很大。不過官方的硬體測試環境是很高的，所以這個 160 萬 QPS 對於大家測試來說，可能還比較遙遠，所以實際測試的結果可能會失望。但是，至少我們看到了基於同樣測試環境，MySQL 5.7 在效能上的改進，對於多核利用的改善。
提高運維效率的特性
MySQL 5.7 動態修改 Buffer Pool
從 MySQL 5.7.5 開始可以線上動態調整，對運維更友好。很多人都經歷過 Buffer Pool 過大或過小調整需要重啟例項，運維成本非常高，尤其是主庫或其他核心業務。
MySQL redo log 大小
5.5 <= 4G， 5.6 +<= 512G
當然這個也不是越大越好，但是提供了可以 嘗試的機會，越大的 redo log 理論會有更穩定的效能。當然帶來的風險就是故障恢復時間會更長。
下圖就是不同 redo 大小的效能對比，主要是看效能抖動情況
innodb_file_per_table
預設值 Off <= 5.6.5 <=On，獨立表空間優點很明顯。尤其可以使用 InnoDB Transportable tablespaces，可以像 MyISAM 一樣快速遷移表。
query cache
1 <＝ 5.6.8 <= 0，預設關閉。整體來說關閉 query cache 是利遠大於弊，官方最終也選擇了關閉。
SQL_Mode 變為Strict mode
SQL要求更嚴格，version < 5.6.6 sql_mode 為空，最為寬鬆，不夠嚴謹。
5.6.6 < version < 5.7.4
- NO_ENGINE_SUBSTITUTION
version > 5.7.9
- ONLY_FULL_GROUP_BY
- STRICT_TRANS_TABLES
- NO_ZERO_IN_DATE
- NO_ZERO_DATE
- ERROR_FOR_DIVISION_BY_ZERO
- NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
以 NO_ZERO_DATE 為例，如果你原表裡有 0000-00-00 這種資料，在 MySQL 5.7 使用預設 SQL_Mode 時候改表時候就會報錯了。
binlog_rows_query_log_events
預設關閉 ，可選開啟，建議開啟，還是比較有用的。可以看到row格式下的sql語句，方便排查問題和恢復資料。
以 NO_ZERO_DATE 為例，如果你原表裡有 0000-00-00 這種資料，在 MySQL 5.7 使用預設 SQL_Mode 時候改表時候就會報錯了。
binlog_rows_query_log_events
預設關閉 ，可選開啟，建議開啟，還是比較有用的。可以看到row格式下的sql語句，方便排查問題和恢復資料。
上圖就是開啟之後 binlog 解析出來的內容，可以看到正常 SQL。
max_execution_time
5.7.4 剛引入名字是 max_statement_time，後來改成 max_execution_time
單位是毫秒，SQL 語句的超時中斷，自我保護的一種方案。
只針對 select，也可以在 sql 裡指定。如果 percona 版本 的話，這個引數更暴力，對所有請求生效慎用。
replication info in tables
crash-safe slave 方案，最早 Google 做的方案是儲存在事務日誌裡。單獨儲存更靈活，可以解決很多從庫當機後 duplicate key 問題。
innodb_numa_interleave
建議關掉 numa。最早 Twitter 開源分支裡有提供，也可以啟動例項時候設定 numactl –interleave all，不過實際線上使用系統預設 numa 策略，並沒有遇到過因為 numa 導致的 swap 問題。
動態修改 replication filter
方便做拆分或做級連複製時候使用，可以通過 change 動態修改。如果用過 replication filter 應該清楚 這個還是比較有用的。
優化器 Server 層改進
優化器主要還是基於 cost model 層面和給使用者更多自主優化
可配置 cost based optimizer，mysql.server_cost 和 mysql.engine_cost。
New JSON 資料型別和函式支援。當然 JSON 也可以存在 Text 或 VARCHAR 裡用內建 json，更容易訪問，方便修改。
支援生成列（虛擬列），以及虛擬列上索引。
CREATE TABLE order_lines (orderno integer,
lineno integer,
price decimal(10,2),
qty integer,
sum_price decimal(10,2) GENERATED ALWAYS AS (qty * price) STORED );
簡化查詢，有 virtual 和 stored 兩種情況，感覺這個功能還是比較小眾。
下圖是二者對比
5.7 還對 explain 做了增強，對於當前正在執行查詢 explain。
EXPLAIN [FORMAT=(JSON|TRADITIONAL)] FOR CONNECTION <id>;
InnoDB 層優化
InnoDB 層核心還是拆分各種鎖，提高併發。只讀事務優化就是其中一個例子
不再使用只讀事務 list，重構 MVCC 程式碼，不為只讀事務分配事務 id，降低記憶體開銷。
如何使用只讀事務：
start transaction read only
開啟 autocommit 下的不加鎖的 select 語句
原生支援分割槽 Native Partitioning，之前版本分割槽表是放在 server 層管理的，現在是在引擎層面支援，更節省記憶體，分割槽越多，效果越明顯。
atomic write, disable double write，MySQL 5.7 開始支援 atomic write，成本高，價效比不算高，需要底層儲存硬體支援，感覺比較雞肋。
支援 spatial index 空間索引
基於 R tree 實現
目前只支援 2D 資料型別
支援 GeoHash 和 GeoJson ，提高資料查詢效率
Transparent page compression
需要檔案系統支援 PUNCH HOLE，ext4 和 xfs 都可以支援，測試效果比目前壓縮效果好一些。適配更多壓縮演算法，支援 lz4 zlib，功能上還不夠穩定和成熟。
performance_schema 改進，增加了很多統計資訊表，metadata_locks，status_by_host ，status_by_user，status_by_thread，獲取當前執行的慢查詢 top10，可以獲取到更多狀態資訊。
上圖是對單個 thread_id 吞吐量統計資訊
新增加的 sys 資料庫，相當於對 performance_schema 的資料整合。IO 資訊和索引資訊，相當於 Oracle 裡的 V$ Catalog，基於 ps_helper 實現。基於 performace schema 畫的 SQL 執行時間分佈圖。
replication改進
上圖是 MySQLreplication 的發展歷史
最大的亮點 GTID 增強，支援線上調整 GTID。當然也不是簡單的 SET @@GLOBAL.GTID_MODE = ON，步驟也是很複雜，不過至少不用停機，也是進步
從庫可以不開啟 log_slave_updates，通過引入 gtid_executed 表實現，對效能優一定幫助，大大簡化切換流程。增強半同步複製，確保從庫先收到，設定半同步從庫個數。使用 mysqlbinlog 作為偽 slave 是個不錯方案。
上圖就是 loss-less 半同步和之前的區別
並行複製優化 ，Database 5.6 預設並行複製。logical-clock 5.7 引入，一個組提交內事務都可以並行，可以達到接近主庫併發效果。
不同複製方案的可用性級別
5.7 引入的 group replication 也是為了提高可用性。多主複製，多點寫入，內部檢測衝突，保證一致性，自動探測。支援 GTID，共享 UUID，只支援 InnoDB，不支援併發 DDL。
安全方面密碼自動過期，這個要注意，建議關閉。default_password_lifetime 控制過期預設一年。鎖定使用者，支援 SSL 訪問，server 端利用 OpenSSL 加密。
工具支援 mysqlpump，並行版 mysqldump，也是替換原生 mysqldump 和 mydumper 的。--watch-progress 檢視 dump 進度，--compress-ouptut 壓縮。mysqldump 可以做為一個偽 slave 接受 binlog，做 binlog 備份的匪巢的方案，也支援 SSL。
從整體來說，MySQL 5.7 做的改進還是非常有吸引力的，不論是從運維角度還是效能優化上，當然真正在生產環境上遇到問題時在所難免的，要做好踩坑的準備。
未來發展
RDS服務
應該大家很多人都用過 RDS 服務，確實降低了使用成本。下圖是各種架構區別，普通物理伺服器，EC2，RDS，優勢很明顯。
阿里雲 RDS MySQL，支援讀寫分離，多 zone，支援異地容災，壓縮（支援 TokuDB），阿里雲宣稱的一大亮點，程式碼控制能力強。
老牌 RDS 服務 Amazon RDS，目前有 MySQL Aurora 和 MariaDB 這三種。Aurora 雖然目前來說，會有一些問題，但是方向還是不錯的。
HA 架構，從可用性來說 Galera > Aurora > MHA
關於 RDS 服務的一些建議，資料庫經驗積累還是很重要的，面臨過或解決的問題越多，提供的服務也相對越穩定。RDS 提供便利的同時，也存在資料安全的風險和 RDS 服務本身的 SLA 保證，是使用者更關心的。
如何降低雲服務商故障對業務影響，其實從 RDS 提供的效能指標考量，如果使用同等效能配置的物理伺服器，RDS 成本還是偏高一些的。從功能上來說大部分 RDS 還算完備，具體哪些坑實際用的不多不好評判。
儲存層的優化
LSM Tree : LevelDB, RocksDB，適配高效能儲存 SSD，更高的壓縮比,，更低的寫入放大比例，缺點讀效能差，適合寫多讀少場景。
MyRocks: MySQL + RocksDB
統層優化
系統優化主要還是 IO 方面的，blk-mq、scsi-mq、IO 中斷多佇列、3D Xpoint 接近記憶體的訪問速度和非易失儲存，說不定以後整個資料庫例項都可以放在這種介質上面，也是一場新的變革。
運維經驗總結
資料恢復
備份 xtrabackup 物理為主，mydumper/mysqlpump 為輔，binlog 備份也是很重要的。恢復匯出 SQL 檔案正常恢復。
myloader
xtrabackup
InnoDB transportable space
online ddl
5.6 和 5.7 雖然一直在改善，但是在主從同步問題上依然有問題，下圖是目前主流的 online DDL 方案。
總體使用 pt-osc 更通用一些，pt-osc 注意的一些坑，新增唯一鍵，導致資料丟失延時備份。行格式下，只在從庫使用 OSC，丟資料。
MySQL 慢日誌系統
基於 pt-query-digest logstash 和 Anemometer 實現，可以定期跟蹤線上業務慢查詢優化。
系統狀態收集
基於某些特定條件觸發，比如 MySQL 連線數增長到一定閾值，收集當前系統狀態，方便後續問題排查。比如系統 top mpstat strace tcpdump 等，MySQL 的 processlist show engine innodb status 等。
Q & A
1、請問 query cache 關閉的原因，MySQL 是否支援全同步？
query cache 訪問需要獲取一個全域性鎖，高併發時候爭用很嚴重。更主要的是 query cache 快取的效果並不好。原生 MySQL 的話，5.7 裡的 group replication 是支援全同步的，還有目前的基於 galera 實現的 percona xtradb cluster 也是支援全同步的。
2、有沒有 MySQL 的讀寫分離中介軟體？最好沒有語言限制的，謝謝！
目前開源的中介軟體很多了，比如 mycat、atlas、vitess等，你指的語言限制是指的對開發語言的相容吧，我覺得每種都相容的很好的 不多，畢竟現在的中介軟體都是基於開發者本公司的現狀開發的，在相容性上不太能做到很完美。現在官方做了個MySQL Fabric，現在應該也 GA 了，後面可以關注一下。
3、你們備份的策略(比如完整多久一次,增量多久一次,備份恢復測試多久一次)， 備份對線上系統的影響如何控制都是選一個slave備份的麼？
我們目前的策略是以周備+日備，結合 binlog 備份，理論可以恢復到一週內任意時間點。全部是全量備份，沒有做增量。備份恢復測試，我們目前還沒有做，基於xtrabackup備份資料可靠性還是很高的 ，之前在新浪是有實現備份測試的，大概2-3天能對線上備份埠做一輪測試。目前備份是選擇一個線上從庫來做的，控制影響的話主要是通過對備份工具 xtrabackup 的並行度和 IO 來進行限制。
4、請問關於 DB 管理的問題，線上資料庫是否可開放給業務方技術人員查詢？開放到什麼程度？有沒有必要做 WEB 查詢平臺？
開發人員還是有必要開放的，如果完全禁止，很多業務資料查詢的事情可能就需要 DBA 介入，其實效率是比較低的。當然在許可權上可以限制，比如只開放讀許可權，禁止 dump 這種。對核心庫限制要更嚴格一些。如果能做 Web 平臺 當然更好，可以在入口層做限制，後端控制資料訪問頻度和策略等。
5、當插入一條資料，非唯一索引是通過 change buffer 更新提高併發，那麼唯一索引或者主鍵如何更新呢？保證高併發？
唯一索引或主鍵需要看更新或插入的資料在不在 Buffer Pool，沒有的話就需要去磁碟讀取資料做檢測，需要維持約束。
6、" 聚簇索引的資料的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那麼對應的資料一定也是相鄰地存放在磁碟上的 "，有個疑問，某條資料的更新，是新生成一條，老本的打上版本號，然後定期刪除。這樣有個問題，新資料應該在新的實體地址。聚簇索引是不是失效了？
聚簇索引在實際磁碟儲存也不是嚴格順序的，並且老的版本是儲存在 undo裡，也就是 ibdata 共享表空間裡 ，和實際資料不衝突。
7、MySQL 5.7 有沒有對複合索引做優化，在違背 left most prefixing 時也能使用複合索引？
最左字首的原則比較難突破，當然在 5.6 引入了 index condition pushdown 機制，可以在儲存引擎層面做一些過濾，減少過濾行數，會有一定優化。