作者：马文斌

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

本文起源：原创投稿

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

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

二、性能晋升

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

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

2.2-基于WriteSet的并行复制

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

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

binlog_transaction_dependency_tracking  = WRITESET                 #    COMMIT_ORDER     transaction_write_set_extraction        = XXHASH64

2.3-JSON个性加强

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

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

2.4-空间数据类型加强

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

2.5-doublewrite改良

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

2.6-hash join

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

2.6.1-NestLoopJoin算法

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

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

2.6.2-Hash Join

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

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

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

2.7-anti 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年，某个医生的病人中没有进行医学查看的局部

2.8-redo优化

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线程的告诉。

2.9-直方图（统计信息）

优化器会利用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的性能。

2.10-敞开QC（Query Cache ）

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

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

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

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

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

三、安全性加强

3.1-死锁检测

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

3.2-默认明码认证插件

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

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

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

  • password_history
  • password_reuse_interval

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

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

3.4-认值加密插件

老版本：认证形式为sha256_password

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

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

3.5-用户明码加强

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

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

password_history = 5 ;

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

password_reuse_interval = 90 ;

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

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

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

（3）双明码

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

3.6-角色性能

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

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

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

3.7-redo & undo 日志加密

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

innodb_redo_log_encryptinnodb_undo_log_encrypt

四、优化器加强

4.1-Cost Model改良

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

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

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全局锁的争用。

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

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

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

五、其余加强

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

通过加上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 文件（如果存在则替换任何现有变量设置）。

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

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

5.3-undo空间主动回收

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表空间超过这个值，该表空间被标记为可回收。

5.4-地理信息系统 GIS

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

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

5.5-参数开关表

select @@optimizer_switch \Gmysql> 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=onsession 开关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";