[转]MySQL 5.7 新特性大全和未来展望

迷南。 2022-08-19 04:53 137阅读 0赞

本文转自微信公众号： [高可用架构][Link 1]

作者：杨尚刚

引用

美图公司数据库高级 DBA，负责美图后端数据存储平台建设和架构设计。前新浪高级数据库工程师，负责新浪微博核心数据库架构改造优化，以及数据库相关的服务器存储选型设计。之前在「高可用架构」发表的《单表 60 亿记录等大数据场景的 MySQL 优化和运维之道》广受好评。

2015 年最重磅的当属 MySQL 5.7 GA 的发布，号称 160 万只读 QPS，大有赶超 NoSQL 趋势。

![c94d7fc8-8192-3d7b-9e6c-50bab6a62fee.png][]

上面这个图是 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 大小的性能对比，主要是看性能抖动情况

![点击查看原始大小图片][49338847-a4f5-3381-b856-03e8d7fe863d.png]

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语句，方便排查问题和恢复数据。

![6e947c0f-dc1d-3596-b390-bea667b039f8.png][]

上图就是开启之后 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 问题。

![830ad8c9-2846-30a9-ac66-be09252a647a.png][]

innodb\_numa\_interleave   
建议关掉 numa。最早 Twitter 开源分支里有提供，也可以启动实例时候设置 numactl –interleave all，不过实际线上使用系统默认 numa 策略，并没有遇到过因为 numa 导致的 swap 问题。   
  
动态修改 replication filter   
方便做拆分或做级连复制时候使用，可以通过 change 动态修改。如果用过 replication filter 应该清楚 这个还是比较有用的。

![60eb1fe1-fca5-3a3a-8f41-98d735d8f06f.png][]

**优化器 Server 层改进**   
  
优化器主要还是基于 cost model 层面和给用户更多自主优化

![9c20901d-298f-3ccb-9c5a-56582bc24763.png][]

可配置 cost based optimizer，mysql.server\_cost 和 mysql.engine\_cost。

![0edd4c0b-5230-34d2-b9ca-a5981cfeda76.png][]

New JSON 数据类型和函数支持。当然 JSON 也可以存在 Text 或 VARCHAR 里用内置 json，更容易访问，方便修改。

![d4d5d9ba-4b3b-3375-8490-2377c2d2d389.png][]

支持生成列（虚拟列），以及虚拟列上索引。   
  
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 两种情况，感觉这个功能还是比较小众。   
  
下图是二者对比

![31b2a2ff-95e8-37c1-a1bb-935c1c0aa7ac.png][]

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，功能上还不够稳定和成熟。

![c2e07af0-8e09-394b-ac1c-a4d4e112e65c.png][]

performance\_schema 改进，增加了很多统计信息表，metadata\_locks，status\_by\_host ，status\_by\_user，status\_by\_thread，获取当前执行的慢查询 top10，可以获取到更多状态信息。

![f0cbb0cf-4e09-3209-ab2a-43afd3fe6994.png][]

上图是对单个 thread\_id 吞吐量统计信息   
  
新增加的 sys 数据库，相当于对 performance\_schema 的数据整合。IO 信息和索引信息，相当于 Oracle 里的 V$ Catalog，基于 ps\_helper 实现。基于 performace schema 画的 SQL 执行时间分布图。

![70fa7461-fc61-3172-95fe-45777c76f799.png][]

replication改进

![4724443d-fcef-3b3a-8bae-7154a3ba6d24.png][]

上图是 MySQLreplication 的发展历史   
  
最大的亮点 GTID 增强，支持在线调整 GTID。当然也不是简单的 SET @@GLOBAL.GTID\_MODE = ON，步骤也是很复杂，不过至少不用停机，也是进步   
从库可以不开启 log\_slave\_updates，通过引入 gtid\_executed 表实现，对性能优一定帮助，大大简化切换流程。增强半同步复制，确保从库先收到，设置半同步从库个数。使用 mysqlbinlog 作为伪 slave 是个不错方案。

![02c3562f-f780-3fa2-bc54-cefd4be3b7a1.png][]

上图就是 loss-less 半同步和之前的区别   
  
并行复制优化 ，Database 5.6 默认并行复制。logical-clock 5.7 引入，一个组提交内事务都可以并行，可以达到接近主库并发效果。

![99029359-f52d-3785-a5f7-3457af4ada73.png][]

不同复制方案的可用性级别

![8aa6cd58-362f-310f-bb87-a6ef7cad7c72.png][]

5.7 引入的 group replication 也是为了提高可用性。多主复制，多点写入，内部检测冲突，保证一致性，自动探测。支持 GTID，共享 UUID，只支持 InnoDB，不支持并发 DDL。

![58dbf280-dcdd-3afa-a926-19d49e853522.png][]

安全方面密码自动过期，这个要注意，建议关闭。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，优势很明显。

![7e20f11a-fab6-3195-adee-7ce50dad7975.png][]

阿里云 RDS MySQL，支持读写分离，多 zone，支持异地容灾，压缩（支持 TokuDB），阿里云宣称的一大亮点，代码控制能力强。   
  
老牌 RDS 服务 Amazon RDS，目前有 MySQL Aurora 和 MariaDB 这三种。Aurora 虽然目前来说，会有一些问题，但是方向还是不错的。   
  
HA 架构，从可用性来说 Galera > Aurora > MHA

![b28f2f27-36cc-390e-8749-3317f316be90.png][]

关于 RDS 服务的一些建议，数据库经验积累还是很重要的，面临过或解决的问题越多，提供的服务也相对越稳定。RDS 提供便利的同时，也存在数据安全的风险和 RDS 服务本身的 SLA 保证，是用户更关心的。   
  
如何降低云服务商故障对业务影响，其实从 RDS 提供的性能指标考量，如果使用同等性能配置的物理服务器，RDS 成本还是偏高一些的。从功能上来说大部分 RDS 还算完备，具体哪些坑实际用的不多不好评判。   
  
**存储层的优化**   
LSM Tree : LevelDB, RocksDB，适配高性能存储 SSD，更高的压缩比,，更低的写入放大比例，缺点读性能差，适合写多读少场景。   
MyRocks: MySQL + RocksDB

![5be89a5f-6343-351c-a431-7d5d52e53212.png][]

统层优化   
系统优化主要还是 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 方案。

![1e5733bf-198f-3d0c-83f8-62f9d01ccc08.png][]

总体使用 pt-osc 更通用一些，pt-osc 注意的一些坑，添加唯一键，导致数据丢失延时备份。行格式下，只在从库使用 OSC，丢数据。   
  
MySQL 慢日志系统   
基于 pt-query-digest logstash 和 Anemometer 实现，可以定期跟踪线上业务慢查询优化。

![点击查看原始大小图片][c91989db-305a-3b6f-ba0c-476337ae723a.png]

系统状态收集   
基于某些特定条件触发，比如 MySQL 连接数增长到一定阈值，收集当前系统状态，方便后续问题排查。比如系统 top mpstat strace tcpdump 等，MySQL 的 processlist show engine innodb status 等。

![6f752ca1-ab39-3b11-8209-9695e5850a7c.png][]

**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 机制，可以在存储引擎层面做一些过滤，减少过滤行数，会有一定优化。

来源：http://www.iteye.com/news/31253?utm\_source=tuicool&utm\_medium=referral

[Link 1]: https://mp.weixin.qq.com/s?__biz=MzAwMDU1MTE1OQ==&mid=403166542&idx=1&sn=3531bc111e28a7d49d4d3b4c58315b9a
[c94d7fc8-8192-3d7b-9e6c-50bab6a62fee.png]: /images/20220731/a712aee9db2d4b40979f317d1a7a6cdb.png
[49338847-a4f5-3381-b856-03e8d7fe863d.png]: /images/20220731/a4134a8a021e4f63a7a0bb55cea94993.png
[6e947c0f-dc1d-3596-b390-bea667b039f8.png]: /images/20220731/85b4febefa704cd3bb80517ac68b2bca.png
[830ad8c9-2846-30a9-ac66-be09252a647a.png]: /images/20220731/40f5be0fc03f488d96ce93f01a77ebec.png
[60eb1fe1-fca5-3a3a-8f41-98d735d8f06f.png]: /images/20220731/3518e5d03a1b4ea39a21074c96e9ce32.png
[9c20901d-298f-3ccb-9c5a-56582bc24763.png]: /images/20220731/e0e755737474408ea367d1abaa3d397c.png
[0edd4c0b-5230-34d2-b9ca-a5981cfeda76.png]: /images/20220731/fc5649f9f3024bfbb8262058b5ae177a.png
[d4d5d9ba-4b3b-3375-8490-2377c2d2d389.png]: /images/20220731/1c05a6bc44604af89d8572df1f1d2c1e.png
[31b2a2ff-95e8-37c1-a1bb-935c1c0aa7ac.png]: /images/20220731/91e856b230384fa58ca83747f66c4629.png
[c2e07af0-8e09-394b-ac1c-a4d4e112e65c.png]: /images/20220731/cb86335acd19457a85541ee3a58f5f79.png
[f0cbb0cf-4e09-3209-ab2a-43afd3fe6994.png]: /images/20220731/c4fd21872fd04d62988da580bb9546c1.png
[70fa7461-fc61-3172-95fe-45777c76f799.png]: /images/20220731/6e758f78f69c4f868feb36d733e64a41.png
[4724443d-fcef-3b3a-8bae-7154a3ba6d24.png]: /images/20220731/9da1a7c899e04122a15a2ae2836fe1d2.png
[02c3562f-f780-3fa2-bc54-cefd4be3b7a1.png]: /images/20220731/c5e85d2799a04274844eff47c5a78466.png
[99029359-f52d-3785-a5f7-3457af4ada73.png]: /images/20220731/fc8984bc68134f22892954c249cc1f9a.png
[8aa6cd58-362f-310f-bb87-a6ef7cad7c72.png]: /images/20220731/a357235521b4488ca74b63a8fdb19b04.png
[58dbf280-dcdd-3afa-a926-19d49e853522.png]: /images/20220731/5a11db5f082d43779e59bf81fd39e0fb.png
[7e20f11a-fab6-3195-adee-7ce50dad7975.png]: /images/20220731/70c9864393794b9d8340e485d07ecd96.png
[b28f2f27-36cc-390e-8749-3317f316be90.png]: /images/20220731/6bcd935394ca4f78accdb9930f4e229d.png
[5be89a5f-6343-351c-a431-7d5d52e53212.png]: /images/20220731/b3df478ca74e4b498efbb410cb3b704d.png
[1e5733bf-198f-3d0c-83f8-62f9d01ccc08.png]: /images/20220731/fba1653a47b949b5a009daad058c4a45.png
[c91989db-305a-3b6f-ba0c-476337ae723a.png]: /images/20220731/a64c6b3affd14d45b9b681d344eecd6a.png
[6f752ca1-ab39-3b11-8209-9695e5850a7c.png]: /images/20220731/aac9e0b9a0714843a910fc977d72b324.png