MySQL 5.7 新特性大全和未來展望

本文轉自微信公眾號： 高可用架構

作者：楊尚剛

引用

美圖公司數據庫高級 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。最早 推ter 開源分支里有提供，也可以啟動實例時候設置 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 機制，可以在存儲引擎層面做一些過濾，減少過濾行數，會有一定優化。