关于mysql-8.0:技术分享-可能是目前最全的-MySQL-80-新特性解读下

作者：马文斌

MySQL 爱好者, 任职于蓝月亮 (中国) 有限公司。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

上一篇次要讲了第一局部：性能加强，感兴趣的亲请点击【可能是史上最全的 MySQL 8.0 新个性解读(上)】，这一篇咱们持续：

MySQL 在 8.0.3 版本引入了新的事务调度算法，基于竞争感知的事务调度，Contention-Aware Transaction Scheduling，简称 CATS。在 CATS 算法之前，MySQL 应用 FIFO 算法，先到的事务先取得锁，如果产生锁期待，则依照 FIFO 算法进行排队。CATS 相比 FIFO 更加简单，也更加聪慧，在高负载、高争用的场景下，性能晋升显著。

总的来说 MySQL 对于并行复制到目前为止经验过三个比拟要害的工夫结点“库间并发”，“组提交”，“写汇合”；真堪称是江山代有人才出，前浪死在沙滩上；总的来说就前面的比后面的不晓得高到哪里去了！

MySQL 8.0 版本引入了一个新的机制 WriteSet，来追踪事务之间的依赖性，这个个性被用于优化从库利用 binlog 的速度，在主库并发较低的场景下，可能显著进步从库回放 binlog 的速度，基于 WriteSet 的并行复制计划，彻底解决了 MySQL 复制提早问题。只须要设置这 2 个参数即可

binlog_transaction_dependency_tracking  = WRITESET                 #    COMMIT_ORDER     
transaction_write_set_extraction        = XXHASH64

MySQL 8 大幅改良了对 JSON 的反对，增加了基于门路查问参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据别离组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

在主从复制中，新增参数 binlog_row_value_options，管制 JSON 数据的传输方式，容许对于 Json 类型局部批改，在 binlog 中只记录批改的局部，缩小 json 大数据在只有大量批改的状况下，对资源的占用。

MySQL 8 大幅改良了空间数据类型和函数，反对更多的空间剖析函数和空间类型对象，空间剖析性能和性能失去大幅晋升。

在 MySQL 8.0.20 版本之前，doublewrite 存储区位于零碎表空间，从 8.0.20 版本开始，doublewrite 有本人独立的表空间文件，这种变更，可能升高 doublewrite 的写入提早，减少吞吐量，为设置 doublewrite 文件的寄存地位提供了更高的灵活性。

MySQL 8.0.18 版本引入 hash join 性能，对于没有走索引的等值 join 连贯能够应用 hash join 进行优化。8.0.20 版本对 hash join 进行了增强，即便 join 连贯没有应用等值条件也能够应用 hash join 优化，原来应用 BNL 算法的 join 连贯将全副由 hash join 代替。

简略来说，就是双重循环，遍历表面(驱动表)，对于表面的每一行记录，而后遍历内表，而后判断 join 条件是否合乎，进而确定是否将记录吐出给上一个执行节点。

从算法角度来说，这是一个 M * N 的复杂度。

是针对 equal-join 场景的优化，根本思维是，将表面数据 load 到内存，并建设 hash 表，这样只须要遍历一遍内表，就能够实现 join 操作，输入匹配的记录。

如果数据能全副 load 到内存当然好，逻辑也简略，个别称这种 join 为 CHJ(Classic Hash Join)，之前 MariaDB 就曾经实现了这种 HashJoin 算法。

如果数据不能全副 load 到内存，就须要分批 load 进内存，而后分批 join，上面具体介绍这几种 join 算法的实现。

MySQL 8.0.17 版本引入了一个 anti join 的优化，这个优化可能将 where 条件中的 not in(subquery)，not exists(subquery)，in(subquery) is not true，exists(subquery) is not true，在外部转化成一个 anti join，以便移除外面的子查问 subquery，这个优化在某些场景下，可能将性能晋升 20% 左右。

anti join 实用的场景案例通常如下：

找出在汇合 A 且不在汇合 B 中的数据
找出在以后季度里没有购买商品的客户
找出往年没有通过考试的学生
找出过来 3 年，某个医生的病人中没有进行医学查看的局部

mysql8.0 一个新个性就是 redo log 提交的无锁化。在 8.0 以前，各个用户线程都是通过互斥量竞争，串行的写 log buffer，因而能保障 lsn 的程序无距离增长。

mysql8.0 通过 redo log 无锁化，解决了用户线程写 redo log 时竞争锁带来的性能影响。同时将 redo log 写文件、redo log 刷盘从用户线程中剥离进去，抽成独自的线程，用户线程只负责将 redo log 写入到 log buffer，不再关怀 redo log 的落盘细节，只需期待 log_writer 线程或 log_flusher 线程的告诉。

优化器会利用 column_statistics 的数据，判断字段的值的散布，失去更精确的执行打算。

能够通过 ANALYZE TABLE table_name [UPDATE HISTOGRAM on colume_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 来收集或者删除直方图信息。

直方图统计了表中某些字段的数据分布状况，为优化抉择高效的执行打算提供参考，直方图与索引有着实质的区别，保护一个索引有代价。每一次的 insert、update、delete 都须要更新索引，会对性能有肯定的影响。而直方图一次创立永不更新，除非明确去更新它，因而不会影响 insert、update、delete 的性能。

从 MySQL 8.0 开始，不再应用查问缓存（Query Cache）。

随着技术的提高，通过工夫的考验，MySQL 的工程团队发现启用缓存的益处并不多。

首先，查问缓存的成果取决于缓存的命中率，只有命中缓存的查问成果能力有改善，因而无奈预测其性能。

其次，查问缓存的另一个大问题是它受到单个互斥锁的爱护。在具备多个内核的服务器上，大量查问会导致大量的互斥锁争用。

MySQL8.0 勾销查问缓存的另外一个起因是，钻研表明，缓存越凑近客户端，取得的益处越大。MySQL8.0 新减少了一些其余对性能干涉的工具来反对。另外，还有像 ProxySQL 这样的第三方工具，也能够充当两头缓存。

能够应用一个新的动静变量 innodb_deadlock_detect 来禁用死锁检测。在高并发零碎上，当多个线程期待同一个锁时，死锁检测会导致速度变慢。有时，禁用死锁检测并在产生死锁时依附 innodb_lock_wait_timeout 设置进行事务回滚可能更无效。

MySQL 8.0.4 版本批改了默认的身份认证插件，从老的 mysql_native_password 插件变为新的 caching_sha2_password，并将其作为默认的身份认证机制，同时客户端对应的 libmysqlclient 也默认应用新的认证插件。

设置历史明码检测规定，避免重复重用旧明码。

password_history
password_reuse_interval

双明码机制，批改明码时，创立新的明码，同时旧的明码也能够应用，保留肯定的缓冲工夫进行查看确认。

当批改一个账户明码时，须要去验证以后的明码，通过参数 password_require_current 来管制，默认敞开，当关上该选项时，如果要批改账户明码，必须要提供以后的明码才容许批改。

老版本：认证形式为 sha256_password

8.0 版本：在老版本的根底上，新增 caching_sha2_password, 能够应用缓存解决连贯时的延时问题。

须要留神的问题是：如果客户端与服务端配置不同，无奈进行连贯，两者的加密认证形式须要一样。

（1）明码的重复使用策略

历史明码反复次数检测：新密码不能与最近最新的 5 个明码雷同。

password_history = 5 ;

工夫距离：新密码不能和过来 90 天内的明码雷同。

password_reuse_interval = 90 ;

（2）批改明码必要的验证策略

批改明码，要输出以后的明码。减少了用户的安全性。

## 默认为 off；为 on 时 批改明码须要用户提供以后明码 (开启后批改明码须要验证旧明码，root 用户不须要)
password_require_current = on ;

（3）双明码

相比于一个用户只有一个明码最大长处就是: 批改明码不会导致利用不可用。那么利用就能够主动应用副明码（副明码和以后明码保持一致）连贯数据库库。确保了业务的不中断。批改明码不会导致利用不可用；利用就能够主动应用副明码连贯数据库。

MySQL 角色是指定权限汇合。像用户账户一样，角色能够领有授予和撤销的权限。

能够授予用户账户角色，授予该账户与每个角色相干的权限。

不便了用户权限治理和保护。很好地解决了多个用户应用雷同的权限集。权限–》角色–》用户。

减少以下两个参数，用于管制 redo、undo 日志的加密。

innodb_redo_log_encrypt
innodb_undo_log_encrypt

优化器可能感知到页是否存在缓冲池中。5.7 其实曾经凋谢接口，然而不对内存中的页进行统计，返回都是 1.0.

8.0 版本对于读写皆有和高写负载的拿捏恰到好处。在集中的读写均有的负载状况下，咱们观测到在 4 个用户并发的状况下，对于高负载，和 5.7 版本相比有着两倍性能的进步。在 5.7 上咱们显著了进步了只读状况下的性能，8.0 则显著进步了读写负载的可扩展性。为 MySQL 晋升了硬件性能的利用率，其改良是基于从新设计了 InnoDB 写入 Redo 日志的办法。比照之前用户线程之前相互争抢着写入其数据变更，在新的 Redo 日志解决方案中，当初 Redo 日志因为其写入和刷缓存的操作都有专用的线程来解决。用户线程之间不在持有 Redo 写入相干的锁，整个 Redo 处理过程都是工夫驱动。

8.0 版本容许马力全开的应用存储设备，比方应用英特尔奥腾闪存盘的时候，咱们能够在 IO 敏感的负载状况下取得 1 百万的采样 QPS（这里说的 IO 敏感是指不在 IBP 中，且必须从二级存储设备中获取）。这个改观是因为咱们解脱了 file_system_mutex全局锁的争用。

Better Performance upon High Contention Loads (“hot rows”)

8.0 版本显著地晋升了高争用负载下的性能。高争用负载通常产生在许多事务争用同一行数据的锁，导致了事务期待队列的产生。在理论情景中，负载并不是安稳的，负载可能在特定的工夫内暴发（80/20 法令）。8.0 版本针对短时间的暴发负载无论在每秒解决的事务数（换句话，提早）还是 95% 提早上都解决的更好。对于终端用户来说体现在更好的硬件资源利用率（效率）上。因为零碎须要尽量应用榨尽硬件性能，才能够提供更高的均匀负载。

通过加上 PERSIST 关键字，能够将批改的参数长久化到新的配置文件（mysqld-auto.cnf）中，重启 MySQL 时，能够从该配置文件获取到最新的配置参数。

零碎会在数据目录下生成 mysqld-auto.cnf 文件，该文件内容是以 json 格局存储的。当 my.cnf 和 mysqld-auto.cnf 同时存在时，后者优先级更高。

例如：

SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;

此 SET 语法使您可能在运行时进行配置更改，这些更改也会在服务器重新启动后继续存在。与 SET GLOBAL 一样，SET PERSIST 设置全局变量运行时值，但也将变量设置写入 mysqld-auto.cnf 文件（如果存在则替换任何现有变量设置）。

之前是天，并且参数名称发生变化. 在 8.0 版本之前，binlog 日志过期工夫设置都是设置 expire_logs_days 参数，而在 8.0 版本中，MySQL 默认应用 binlog_expire_logs_seconds 参数。

innodb_undo_log_truncate 参数在 8.0.2 版本默认值由 OFF 变为 ON，默认开启 undo 日志表空间主动回收。

innodb_undo_tablespaces 参数在 8.0.2 版本默认为 2，当一个 undo 表空间被回收时，还有另外一个提供失常服务。

innodb_max_undo_log_size 参数定义了 undo 表空间回收的最大值，当 undo 表空间超过这个值，该表空间被标记为可回收。

8.0 版本提供对地形的反对，其中包含了对空间参照系的数据源信息的反对，SRS aware spatial 数据类型，空间索引，空间函数。总而言之，8.0 版本能够了解地球表面的经纬度信息，而且能够在任意受反对的 5000 个空间参照系中计算地球上任意两点之间的间隔.

留神: 降级前，肯定要验证 jdbc 驱动是否匹配，是否须要随着降级。

select @@optimizer_switch \G

mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

session 开关
set session optimizer_switch="use_invisible_indexes=off";  
set session optimizer_switch="use_invisible_indexes=on";  

global 开关
set global optimizer_switch="use_invisible_indexes=off";  
set global optimizer_switch="use_invisible_indexes=on";

关于mysql-8.0:技术分享-可能是目前最全的-MySQL-80-新特性解读下

二、性能晋升

2.1- 基于竞争感知的事务调度

2.2- 基于 WriteSet 的并行复制

2.3-JSON 个性加强

2.4- 空间数据类型加强

2.5-doublewrite 改良

2.6-hash join

2.6.1-NestLoopJoin 算法

2.6.2-Hash Join

2.7-anti join（反连贯）

2.8-redo 优化

2.9- 直方图（统计信息）

2.10- 敞开 QC（Query Cache）

三、安全性加强

3.1- 死锁检测

3.2- 默认明码认证插件

3.3- 降级明码过期，历史明码应用规定

3.4- 认值加密插件

3.5- 用户明码加强

3.6- 角色性能

3.7-redo & undo 日志加密

四、优化器加强

4.1-Cost Model 改良

4.2- 可伸缩的读写负载 Scaling Read/Write Workloads

4.3- 在高争用（热点数据）负载状况下的更优性能

五、其余加强

5.1- 反对在线批改全局参数并长久化

5.2-binlog 日志过期工夫准确到秒

5.3-undo 空间主动回收

5.4- 地理信息系统 GIS

5.5- 参数开关表