嗦一嗦MySQL 8.0的新特性（三）

 

MySQL8.0 GA版本发布了，展现了众多新特性，本系列译文将整理为3篇，本文为第三篇（完结篇），为大家介绍升级的部分新特性。

第一篇：MySQL 8.0的新特性（一）

第二篇：MySQL 8.0的新特性（二）

一、可观测性

• 提高信息视图库的性能 

8.0版本重新实现了信息视图库，在新的实现中，信息视图库的表都是基于InnoDB存储的数据字典表的简单视图。这比之前有了百倍的性能提升。让信息视图库可以更加实用性的被外部工具引用。

MySQL 8.0 reimplements Information Schema. In the new implementation the Information Schema tables are simple views on data dictionary tables stored in InnoDB. This is by far more efficient than the old implementation with up to 100 times speedup. This makes Information Schema practically usable by external tooling. See blog posts by Gopal Shankar here and here , and the blog post by Ståle Deraas here.

• 提高性能信息库的速度 

8.0版本通过在性能信息库上增加了100多个索引完成了其查询性能的提升。这些索引是预设，且无法被删除，修改，增加。相对于在单独的数据结构上进行便利，其索引是通过在既存数据表上过滤扫描来实现的。不需要管理B树，或者散列表的重建，更新及其他操作。

性能信息库的索引从行为上更像散列索引：

1、可以快速返回数据；

2、不支持排序操作（推到服务层处理）。

根据查询，索引会避免全表扫描的需求，而且会返回一个相当精巧的数据集。对show indexes来说，性能信息库的索引是不可见的，但是会出现在explain的结果中和其有关的部分。

MySQL 8.0 speeds up performance schema queries by adding more than 100 indexes on performance schema tables. The indexes on performance schema tables are predefined. They cannot be deleted,added or altered. A performance schema index is implemented as a filtered scan across the existing table data, rather than a traversal through a separate data structure. There are no B-trees or hash tables to be constructed, updated or otherwise managed. 

Performance Schema tables indexes behave like hash indexes in that a) they quickly retrieve the desired rows, and b) do not provide row ordering, leaving the server to sort the result set if necessary. 

However, depending on the query, indexes obviate the need for a full table scan and will return a considerably smaller result set. Performance schema indexes are visible with SHOW INDEXES and are represented in the EXPLAIN output for queries that reference indexed columns. See comment from Simon Mudd. See blog post by Marc Alff here.

• 参数配置

8.0版本增加了关于配置参数的有用信息，比如变量名，最大最小值，当前值的来源，更改用户，更改时间等等。可以在一个新增的性能信息表variables_info表中进行查询。

MySQL 8.0 adds useful information about configuration variables, such as the variable name, min/max values, where the current value came from, who made the change and when it was made. This information is found in a new performance schema table called variables_info. See blog post by Satish Bharathy here.

• 客户端错误报告信息统计

8.0版本使得查看服务端曝出的客户端错误信息汇总统计成为可能。用户可以在五个不同的表中查看统计信息：Global count, summary per thread, summary per user, summary per host, 和summary per account。

用户可以查看单个错误信息的次数，被SQL exception句柄处理过的数量，第一次发生的时间戳，最近一次的时间戳。给予用户适当的权限后，用户既可以用select从中查询，也可以使用truncate进行重置统计数据。

MySQL 8.0 makes it possible to look at aggregated counts of client error messagesreported by the server.The user can look at statistics from 5 different tables: Global count, summary per thread, summary per user, summary per host, or summary per account. 

For each error message the user can see the number of errors raised, the number of errors handled by the SQL exception handler, “first seen” timestamp, and “last seen” timestamp. Given the right privileges the user can either SELECTfrom these tables or TRUNCATE to reset statistics. See blog post by Mayank Prasad here.

• 语句延迟直方图 

为了更高的观察查询相应时间，8.0版本在性能信息库中提供了语句延迟的直方图，同时会从直方图中计算95%，99%，9999%比例的信息。

这些百分比用来作为服务质量的指示器。

MySQL 8.0 provides performance schema histograms of statements latency, for the purpose of better visibility of query response times. This work also computes “P95”, “P99” and “P999” percentiles from collected histograms.

 These percentiles can be used as indicators of quality of service. See blog post by Frédéric Descamps here.

• 图形化数据依赖关系锁 

8.0版本在性能信息库中增加了数据锁生产者身份。当事务A锁住行R时，事务B正在等待一行被A锁住的行。新增的生产者身份将会那些数据被锁住（本例中为行R），谁拥有锁（本例中为事务A），谁在等待被锁住的事务（本例中为事务B）

MySQL 8.0 instruments data locks in the performance schema. When transaction A is locking row R, and transaction B is waiting on this very same row, B is effectively blocked by A. The added instrumentation exposes which data is locked (R), who owns the lock (A), and who is waiting for the data (B). See blog post by Frédéric Descamps here.

• 查询样例摘要

8.0版本为了捕获完成的查询样例和此查询案例的一些关键信息，针对性能信息库中的events_statements_summary_by_digest表做了一些改动。

为了捕获一个真实的查询，并让用户进行explain，并获取查询计划，现增加了一列QUERY_SAMPLE_TEXT 。

为了捕获查询样例时间戳，增加了一列QUERY_SAMPLE_SEEN 。

为了捕获查询执行时间，增加了一列 QUERY_SAMPLE_TIMER_WAIT 。

同时FIRST_SEEN 和 LAST_SEEN 列被修改为可以使用带小数的秒。

MySQL 8.0 makes some changes to the events_statements_summary_by_digestperformance schema table to capture a full example query and some key information about this query example. The column QUERY_SAMPLE_TEXT is added to capture a query sample so that users can run EXPLAIN on a real query and to get a query plan. 

The column QUERY_SAMPLE_SEEN is added to capture the query sample timestamp. 

The column QUERY_SAMPLE_TIMER_WAIT is added to capture the query sample execution time. 

The columns FIRST_SEEN and LAST_SEEN have been modified to use fractional seconds. See blog post by Frédéric Descamps here.

• 生产者的元信息 

8.0版本在性能信息库的 setup_instruments表上增加了诸如属性，易变的，文档等元信息。这些只读信息作为生产者的在线文档被用户或者工具查阅。

MySQL 8.0 adds meta-data such as properties, volatility, and documentation to the performance schema tablesetup_instruments. This read only meta-data act as online documentation for instruments, to be looked at by users or tools. See blog post by Frédéric Descamps here.

• 错误记录

8.0版本 带来了对错误日志的重要的改革。从软件架构的角度来说，错误日志成为了新的服务架构的一部分。这意味着高级用户可以根据需要写出自己的错误日志实现。虽然大多数用户并不想这么做，但是或许会在如何写，写在何处上要求有些变通的空间。

因此8.0版本为用户提供了sinks和filters来实现。8.0版本实行了一个过滤服务（API），和一个默认的过滤服务实现（组件）。

这里的过滤器是指抑制某些特定错误信息的数据和或给定错误信息的某部分的输出。8.0版本实行了一个日志撰写（API），和一个默认的日志撰写服务实现（组件）。

日志撰写器可以接收日志信息，并将其写入到日志中，这里的日志可以指典型的文件日志，syslog日志，或者JSON日志。

MySQL 8.0 delivers a major overhaul of the MySQL error log. From a software architecture perspective the error log is made a component in the new service infrastructure. This means that advanced users can write their own error log implementation if desired. Most users will not want to write their own error log implementation but still want some flexibility in what to write and where to write it. 

Hence, 8.0 offers users facilities to add sinks (where) and filters (what). MySQL 8.0 implements a filtering service (API) and a default filtering service implementation (component). 

Filtering here means to suppress certain log messages (selection) and/or fields within a given log message (projection). MySQL 8.0 implements a log writer service (API) and a default log writer service implementation (component).

 Log writers accept a log event and write it to a log. This log can be a classic file, syslog, EventLog and a new JSON log writer.

不做任何设置默认的话，8.0版本带来了开箱即用的错误日志改进。比如：

By default, without any configuration, MySQL 8.0 delivers many out-of-the-box error log improvements such as:

• 错误编码： 编码格式现在为MY开头的10000系列数据。比如： “MY-10001”。错误编号在GA版本中将不会变化，但是其代表的含义可能在之后的维护性版本发布中做一些改变。

Error numbering: The format is a number in the 10000 series preceded by “MY-“, for example “MY-10001”. Error numbers will be stable in a GA release, but the corresponding error texts are allowed to change (i.e. improve) in maintenance releases.

• 系统信息：系统性的信息[System]替换了之前的 [Error]（指之前错误日志是系统性相关，但是前缀为[Error]的错误信息）,增加到错误日志中。

System messages: System messages are written to the error log as [System] instead of [Error], [Warning], [Note]. [System] and [Error] messages are printed regardless of verbosity and cannot be suppressed. [System] messages are only used in a few places, mainly associated with major state transitions such as starting or stopping the server.

• 错误日志详细度减弱： 默认的错误日志详细信息级别log_error_verbosity 从3(输出)改成了2（输出警告级别及以上）

Reduced verbosity: The default of log_error_verbosity changes from 3 (Notes) to 2 (Warning). This makes MySQL 8.0 error log less verbose by default.

• 信息源部分：每个信息前面都加了[Server], [InnoDB], [Replic] 三个其中之一的注释，以显示信息是从哪个子系统中输出的。

Source Component: Each message is annotated with one of three values [Server], [InnoDB], [Replic] showing which sub-system the message is coming from.

8.0GA版本错误日志中的启动信息：

This is what is written to the error log in 8.0 GA after startup :

 

1234

2018-03-08T10:14:29.289863Z 0 [System][MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.5) starting as process 8063 2018-03-08T10:14:29.745356Z 0 [Warning][MY-010068] [Server] CA certificate ca.pem is self signed. 2018-03-08T10:14:29.765159Z 0 [System][MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.5' socket: '/tmp/mysql.sock' port: 3306 Source distribution. 2018-03-08T10:16:51.343979Z 0 [System][MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.5) Source distribution.

新引入的错误编码方式允许 MySQL在需要的情况下在以后的维护性版本发布中，不改变错误编号的情况下改进错误详细信息。同时错误编号还可以用于在客制化中作为过滤/屏蔽错误信息的实施基础.

The introduction of error numbering in the error log allows MySQL to improve an error text in upcoming maintenance releases (if needed) while keeping the error number (ID) unchanged. Error numbers also act as the basis for filtering/suppression and internationalization/localization.

二、可管理性 Manageability

• 不可见索引

8.0版本将索引的可见性管理变为可能。一个不可见索引在优化器指定查询执行计划时不会被纳入考虑。但是这个索引仍会在后台维护，因此让其变得可见比删除再增加索引代价更小。

设计不可见索引的目的时为了帮助DBA来判断索引能否被删除。如果你假设索引已经不会被用到，可以先将其设为不可见，然后观察查询性能，如果相关的查询性能没有下降的话，最后可以将其删除。

这个功能也是万众期盼的。

MySQL 8.0 adds the capability of toggling the visibility of an index (visible/invisible). An invisible index is not considered by the optimizer when it makes the query execution plan. However, the index is still maintained in the background so it is cheap to make it visible again. 

The purpose of this is for a DBA / DevOp to determine whether an index can be dropped or not. If you suspect an index of not being used you first make it invisible, then monitor query performance, and finally remove the index if no query slow down is experienced. 

This feature has been asked for by many users, for example through Bug#70299. See blog post by Martin Hansson here.

• 灵活的Undo表空间管理 

8.0版本让用户可以全权管理Undo表空间，比如表空间的数量，存放位置，每个undo表空间有多少回滚段。

MySQL 8.0 gives the user full control over Undo tablespaces, i.e. how manytablespaces, where are they placed, and how many rollback segments in each.

1、Undo日志不在存放于系统表空间中：在版本升级的过程中，Undo日志被从系统表空间中分离出来，并放到Undo表空间中。这为现存的非独立Undo表空间的升级留了后路。

No more Undo log in the System tablespace. Undo log is migrated out of the System tablespace and into Undo tablespaces during upgrade. This gives an upgrade path for existing 5.7 installation using the system tablespace for undo logs.

2、Undo表空间可以单独管理：比如放到更快的磁盘存储上。

*Undo tablespaces can be managed separately from the System tablespace.*For example, Undo tablespaces can be put on fast storage.

3、在线Undo表空间回收：为了Undo表空间清理的需要，生成了了两个小的Undo表空间，这样可以让InnoDB在一个活跃一个清理的情况下在线收缩Undo表空间。

Reclaim space taken by unusually large transactions (online). A minimum of two Undo tablespaces are created to allow for tablespace truncation. This allows InnoDB to shrink the undo tablespace because one Undo tablespace can be active while the other is truncated.

4、增多回滚段，减少争用：现在用户可以选择使用最多127个Undo表空间，每个表空间都有最多可达128个回滚段。更多的回滚段可以让并行的事务更可能地为其undo日志使用独立的回滚段，这样可以减少对同个资源的争用。

More rollback segments results in less contention. The user might choose to have up to 127 Undo tablespaces, each one having up to 128 rollback segments. More rollback segments mean that concurrent transactions are more likely to use separate rollback segments for their undo logs which results in less contention for the same resources.

See blog post by Kevin Lewis here.

• 将全局参数设置持久化

在正常的情况下，全局的动态的参数可以在线更改，但是实例重启后，这些没有写入配置文件或者与配置文件冲突的参数设定值有可能会丢失。8.0版本中可以将全局的动态参数的更改持久化。

MySQL 8.0 makes it possible to persist global, dynamic server variables. Many server variables are both GLOBAL and DYNAMIC and can be reconfigured while the server is running. For example: SET GLOBAL sql_mode='STRICT_TRANS_TABLES';However, such settings are lost upon a server restart.

这就让 SET PERSIST sql_mode='STRICT_TRANS_TABLES';的写法成为可能。这样就可以在重启后，参数设定值仍会留存。这个功能的使用场景很多，但是最重要的是给出了一个在更改配置文件不方便或者根本无法做到的情况下的选择。

比如某些托管的场景下，你根本没有文件系统的访问权限，仅有连入数据库服务的权限。和 SET GLOBAL 相同，SET PERSIST也需要super权限。

This work makes it possible to write SET PERSIST sql_mode='STRICT_TRANS_TABLES';The effect is that the setting will survive a server restart. There are many usage scenarios for this functionality but most importantly it gives a way to manage server settings when editing the configuration files is inconvenient or not an option. 

For example in some hosted environments you don’t have file system access, all that you have is the ability to connect to one or more servers. As for SET GLOBAL you need the super privilege for SET PERSIST.

除此之外还有 RESET PERSIST 命令，可以将之前使用SET PERSIST命令更改后参数值的持久化特性取消掉，让这个参数值和 SET GLOBAL设置的一样，下次启动可能会丢失。

There is also the RESET PERSIST command. The RESET PERSIST command has the semantic of removing the configuration variable from the persist configuration, thus converting it to have similar behavior as SET GLOBAL.

8.0版本也允许对大部分当前启动环境下的只读参数进行 SET PERSIST 更改，在下次启动时会生效。当然了部分只读参数是无法被设置更改的。

MySQL 8.0 allows SET PERSIST to set most read-only variables as well, the new values will here take effect at the next server restart. Note that a small subset of read-only variables are left intentionally not settable. See blog post by Satish Bharathy here.

• 远程管理 

8.0版本增加了RESTART命令。其用途是用来允许通过SQL连接来允许远程管理MySQL实例。比如通过 SET PERSIST 命令更改了只读参数后，可以用这个命令来重启MySQL实例。

当然，需要shutdown权限（译者注：同时mysqld进程需要被supervisor进程管理才可以。）

MySQL 8.0 implements an SQL RESTART command. The purpose is to enable remote management of a MySQL server over an SQL connection, for example to set a non-dynamic configuration variable by SET PERSIST followed by a RESTART. 

See blog post MySQL 8.0: changing configuration easily and cloud friendly ! by Frédéric Descamps.

• 重命名表空间 

8.0版本增加了 ALTER TABLESPACE s1 RENAME TO s2;功能，通用表空间或者共享表空间都可以被用户创建，更改，删除。

MySQL 8.0 implements ALTER TABLESPACE s1 RENAME TO s2; A shared/general tablespace is a user-visible entity which users can CREATE, ALTER, and DROP. See also Bug#26949, Bug#32497, and Bug#58006.

• 列重命名

8.0版本支持 ALTER TABLE ... RENAME COLUMN old_name TO new_name;，这相对于现有的ALTER TABLE <table_name> CHANGE …语法（需要注明当前列所有属性）来说是个改进。

应用程序可能由于某些原因并不能获取所该列的所有信息，因此，这是之前语法的弊端。同时之前的语法也可能会偶然导致数据丢失（rename应该是在线DDL，不需要重建表）

MySQL 8.0 implements ALTER TABLE ... RENAME COLUMN old_name TO new_name;This is an improvement over existing syntax ALTER TABLE <table_name> CHANGE … which requires re-specification of all the attributes of the column. 

The old/existing syntax has the disadvantage that all the column information might not be available to the application trying to do the rename. There is also a risk of accidental data type change in the old/existing syntax which might result in data loss.

三、安全特性

• 新的默认验证插件 

8.0版本将默认的验证插件从mysql_native_password改成了caching_sha2_password。与此对应的，libmysqlclient也会采用caching_sha2_password验证机制。

新的caching_sha2_password结合了更高的安全特性（SHA2算法）和高性能（缓存）。我们目前建议所有用户在网络通信上使用TLS/SSL。

MySQL 8.0 changes the default authentication plugin from mysql_native_passwordto caching_sha2_password. Correspondingly, libmysqlclient will use caching_sha2_password as the default authentication mechanism, too. 

The new caching_sha2_password combines better security (SHA2 algorithm) with high performance (caching). The general direction is that we recommend all users to use TLS/SSL for all their network communication. See blog post by Harin Vadodaria here.

• 社区版本默认使用

8.0版本正在把OpenSSL作为企业版本和社区版本的统一默认TLS/SSL库。之前社区版本使用的是YaSSL。支持OpenSSL也是社区呼声比较多的请求了。

MySQL 8.0 is unifying on OpenSSL as the default TLS/SSL library for both MySQL Enterprise Edition and MySQL Community Edition. Previously, MySQL Community Edition used YaSSL. Supporting OpenSSL in the MySQL Community Edition has been one of the most frequently requested features. See blog post by Frédéric Descamps here.

• 动态链接的OpenSSL 

8.0版本动态的和OpenSSL链接在一起。从MySQL软件源的使用者角度来看，现在MySQL包依赖于系统提供的OpenSSL文件。动态的链接之后，OpenSSL的更新就不需要要求MySQL也一同更新或者打补丁。

MySQL 8.0 is linked dynamically with OpenSSL. Seen from the MySQL Repositoryusers perspective , the MySQL packages depends on the OpenSSL files provided by the Linux system at hand. By dynamically linking, OpenSSL updates can be applied upon availability without requiring a MySQL upgrade or patch. See blog post by Frédéric Descamps here.

• 对Undo和Redo日志的加密 

8.0版本加入了对Undo和Redo日志的静态加密。在5.7版本中，我们引入了对使用了独立表空间的InnoDB表的加密。

其可为物理的表空间数据文件提供静态加密。在8.0版本中我们将这个贴行扩展到了Undo和Redo日志上。

MySQL 8.0 implements data-at-rest encryption of UNDO and REDO logs. In 5.7 we introduced Tablespace Encryption for InnoDB tables stored in file-per-table tablespaces.

 This feature provides at-rest encryption for physical tablespace data files. In 8.0 we extend this to include UNDO and REDO logs. See documentation here.

• SQL 角色

8.0版本引入了SQL角色。角色是一组权限的集合。其目的是为了简化用户权限管理系统。可以角色赋给用户的身上，给角色授权，创建角色，删除角色，定义哪些角色对于当前会话是合适的。

MySQL 8.0 implements SQL Roles. A role is a named collection of privileges. The purpose is to simplify the user access right management. One can grant roles to users, grant privileges to roles, create roles, drop roles, and decide what roles are applicable during a session. See blog post by Frédéric Descamps here.

• 允许为公用角色授权或收回权限 

8.0版本引入了可配置的参数 mandatory-roles，用于自动给新创建的用户加上角色。授予给所有用户指定的角色的权限不可以被再次分发。但是这些角色除非被设置为默认角色，否则还是需要进行激活操作。

当然也可以把新引入的 activate-all-roles-on-login参数设为ON，这样在用户验证通过连接进来后，所有授权角色都会自动激活。

MySQL 8.0 introduces the configuration variable mandatory-roles which can be used for automatic assignment and granting of default roles when new users are created. Example: role1@%,role2,role3,role4@localhost. All the specified roles are always considered granted to every user and they can’t be revoked. These roles still require activation unless they are made into default roles. When the new server configuration variable activate-all-roles-on-login is set to “ON”, all granted roles are always activated after the user has authenticated.

• 分解super权限

8.0版本定义了在很多方面上一批新粒度的权限用以代替之前版本使用的SUPER权限。其本意是用于限制用户仅获得和自己工作相关的权限。

比如 BINLOG_ADMIN, CONNECTION_ADMIN, and ROLE_ADMIN.

MySQL 8.0 defines a set of new granular privileges for various aspects of what SUPER is used for in previous releases. The purpose is to limit user access rights to what is needed for the job at hand and nothing more. 

For example BINLOG_ADMIN, CONNECTION_ADMIN, and ROLE_ADMIN.

• 用于管理XA事务的授权模型 

8.0版本引入了一个新的系统权限 XA_RECOVER_ADMIN ，用于控制执行 XA RECOVER语句的权限。所有尝试执行 XA RECOVER 语句的非授权用户会引起报错。

MySQL 8.0 introduces a new system privilege XA_RECOVER_ADMIN which controls the capability to execute the statement XA RECOVER. An attempt to do XA RECOVER by a user who wasn’t granted the new system privilege XA_RECOVER_ADMIN will cause an error.

• 密码轮换策略 

8.0版本引入了对密码重新使用的限制，既可以在全局层级也可以在单独的用户等级上配置。过往的历史密码由于安全的原因（会泄露密习惯，或者词组）会被加密保存。

密码轮换策略对于其他策略来说是叠加的。可以和现有的机制（如，密码过期和密码安全策略等）共存。

MySQL 8.0 introduces restrictions on password reuse. Restrictions can be configured at global level as well as individual user level. Password history is kept secure because it may give clues about habits or patterns used by individual users when they change their password. The password rotation policy comes in addition to other, existing mechanisms such as the password expiration policy and allowed password policy. See Password Management.

• 减缓对用户密码的暴力破解 

8.0版本引入了在连续的错误登陆尝试后的等待验证过程。其设计目的用于减缓哦对用户密码的暴力破解。密码连续错误次数和连续错误之后的等待时间是可以配置的。

MySQL 8.0 introduces a delay in the authentication process based on consecutive unsuccessful login attempts. The purpose is to slow down brute force attacks on user passwords. It is possible to configure the number of consecutive unsuccessful attempts before the delay is introduced and the maximum amount of delay introduced.

• 撤销skip-grant-tables（指远程连接情况下） 

8.0版本禁止当实例以–skip-grant-tables参数启动时的远程用户连接

MySQL 8.0 disallows remote connections when the server is started with –skip-grant-tables. See also Bug#79027 reported by Omar Bourja.

• 为实例增加mysqld_safe的部分功能

8.0版本在实例中引入了部分之前mysqld_safe中的逻辑。可以改善当使用 --daemonize 启动参数时在某些情况下的可用性。

这也减轻了用户对我们即将移除的mysqld-safe脚本的依赖。

MySQL 8.0 implement parts of the logic currently found in the mysqld_safe script inside the server. The work improves server usability in some scenarios for example when using the --daemonize startup option. 

The work also make users less dependent upon the mysqld_safe script, which we hope to remove in the future. It also fixes Bug#75343 reported by Peter Laursen.

四、性能

8.0 版本带来更好的读写负载，IO依赖性工作负载，和业务热数据集中的负载。另外新增的资源组特性给用户带来在特定硬件特定负载下将用户线程分配给指定CPU的选项。

MySQL 8.0 comes with better performance for Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads. In addition, the new Resource Group feature gives users an option to optimize for specific workloads on specific hardware by mapping user threads to CPUs.

• 可伸缩的读写负载

8.0版本对于读写皆有和高写负载的拿捏恰到好处。在集中的读写均有的负载情况下，我们观测到在4个用户并发的情况下，对于高负载，和5.7版本相比有着两倍性能的提高。在5.7上我们显著了提高了只读情况下的性能，8.0则显著提高了读写负载的可扩展性。

为MySQL提升了硬件性能的利用率，其改进是基于重新设计了InnoDB写入Redo日志的方法。

对比之前用户线程之前互相争抢着写入其数据变更，在新的Redo日志解决方案中，现在Re'do日志由于其写入和刷缓存的操作都有专用的线程来处理。用户线程之间不在持有Redo写入相关的锁，整个Redo处理过程都是时间驱动。

MySQL 8.0 scales well on RW and heavy write workloads. On intensive RW workloads we observe better performance already from 4 concurrent users and more than 2 times better performance on high loads comparing to MySQL 5.7. We can say that while 5.7 significantly improved scalability for Read Only workloads, 8.0 significantly improves scalability for Read/Write workloads. 

The effect is that MySQL improves hardware utilization (efficiency) for standard server side hardware (like systems with 2 CPU sockets).

 This improvement is due to re-designing how InnoDB writes to the REDO log. In contrast to the historical implementation where user threads were constantly fighting to log their data changes, in the new REDO log solution user threads are now lock-free, REDO writing and flushing is managed by dedicated background threads, and the whole REDO processing becomes event-driven. See blog post by Dimitri Kravtchuk here.

• 榨干IO能力（在更快速的存储设备上） 

8.0版本允许马力全开的使用存储设备，比如使用英特尔奥腾闪存盘的时候，我们可以在IO敏感的负载情况下获得1百万的采样 QPS（这里说的IO敏感是指不在IBP中，且必须从二级存储设备中获取）。

这个改观是由于我们摆脱了 file_system_mutex全局锁的争用。

MySQL 8.0 allows users to use every storage device to its full power. For example, testing with Intel Optane flash devices we were able to out-pass 1M Point-Select QPS in a fully IO-bound workload. (IO bound means that data are not cached in buffer pool but must be retrieved from secondary storage). This improvement is due to getting rid of the fil_system_mutexglobal lock.

• 在高争用（热点数据）负载情况下的更优性能

8.0版本显著地提升了高争用负载下的性能。高争用负载通常发生在许多事务争用同一行数据的锁，导致了事务等待队列的产生。在实际情景中，负载并不是平稳的，负载可能在特定的时间内爆发（80/20法则）。

8.0版本针对短时间的爆发负载无论在每秒处理的事务数（换句话，延迟）还是95%延迟上都处理的更好。对于终端用户来说体现在更好的硬件资源利用率（效率）上。因为系统需要尽量使用榨尽硬件性能，才可以提供更高的平均负载。

MySQL 8.0 significantly improves the performance for high contention workloads. A high contention workload occurs when multiple transactions are waiting for a lock on the same row in a table, causing queues of waiting transactions. Many real world workloads are not smooth over for example a day but might have bursts at certain hours (Pareto distributed). MySQL 8.0 deals much better with such bursts both in terms of transactions per second, mean latency, and 95th percentile latency. The benefit to the end user is better hardware utilization (efficiency) because the system needs less spare capacity and can thus run with a higher average load. The original patch was contributed by Jiamin Huang (Bug#84266). Please study the Contention-Aware Transaction Scheduling (CATS) algorithm and read the MySQL blog post by Jiamin Huang and Sunny Bains here.

• 资源组

8.0版本为MySQL引入了全局资源组。有了资源组的概念后，管理人员可以管理用户线程和系统线程对CPU的分配。这个功能可以被用来按CPU分割负载以在某些使用情景下获取更高的效率和性能。

因此DBA的工具箱中又多了一把可以帮助自己提升硬件使用率或者增加查询性能的工具。比如在英特尔志强E7-4860 2.27GHz,40核超线程处理器上运行Sysbench读写负载时，我们可以通过将写负载限制在10个核心上从而将总体请求输入量提升一倍。

资源组是相当先进的工具，但由于效果会因为负载的类型和手头的硬件的千差万别，这就要求经验经验丰富的管理人员因地制宜地进行使用。

MySQL 8.0 introduces global Resource Groups to MySQL. With Resource Groups, DevOps/DBAs can manage the mapping between user/system threads and CPUs. This can be used to split workloads across CPUs to obtain better efficiency and/or performance in some use cases. 

Thus, Resource Groups adds a tool to the DBA toolbox, a tool which can help the DBA to increase hardware utilization or to increase query stability. As an example, with a Sysbench RW workload running on a Intel(R) Xeon (R) CPU E7-4860 2.27 GHz 40 cores-HT box we doubled the overall throughput by limiting the Write load to 10 cores.

 Resource Groups is a fairly advanced tool which requires skilled DevOps/DBA to be used effectively as effects will vary with type of load and with the hardware at hand.

 

五、其他特性

• 更优的默认值 

在MySQL了团队中，为了更可能地让用户体验开箱即用，我们对MySQL的默认值保持着紧密的关注。我们已经更改了8.0版本中30多处参数为我们认为更合适的默认参数值。

In the MySQL team we pay close attention to the default configuration of MySQL, and aim for users to have the best out of the box experience possible. MySQL 8.0 has changed more than 30 default values to what we think are better values. See blog post New Defaults in MySQL 8.0. The motivation for this is outlined in a blog post by Mogan Tocker here.

• 协议

8.0版本增加了关闭元数据产生并转化成结果集的选项。构建，解析，发送，接收元数据结果集都会消耗实例，客户端和网络的资源。在某些场景下，元数据大小可能比实际的结果集更大，且不必要。

因此在我们关闭了元数据产生和存储后，显著了地提升了查询结果集的转化。客户端如果不想接收于数据一同传回的元数据信息，可以设置 CLIENT_OPTIONAL_RESULTSET_METADATA

MySQL 8.0 adds an option to turn off metadata generation and transfer for resultsets. Constructing/parsing and sending/receiving resultset metadata consumes server, client and network resources. In some cases the metadata size can be much bigger than actual result data size and the metadata is just not needed. 

We can significantly speed up the query result transfer by completely disabling the generation and storage of these data. Clients can set the CLIENT_OPTIONAL_RESULTSET_METADATA flag if they do not want meta-data back with the resultset.

• C语言客户端 

8.0版本扩展了 libmysql的C语言API，使其更加稳定地从服务器流式获取复制事务。其设计目的在于为了实现基于binlog的程序（类似Hadoop接收MySQL数据的工具） 进而需要避免对非正式API的调用和对内部头文件的打包

MySQL 8.0 extends libmysql’s C API with a stable interface for getting replication events from the server as a stream of packets. The purpose is to avoid having to call undocumented APIs and package internal header files in order to implement binlog based programs like the MySQL Applier for Hadoop.

• 内存缓存

8.0版本利用多样的的get操作和对范围查询的支持，提升了InnoDB的内存缓存技能。我们通过对多种get操作的支持还提升了读性能，如用户可以在单次内存缓存查询中获取多个键值对。

对范围查询的支持是应脸书的Yoshinori 所请求（译者注：MHA作者）。利用范围查询，用户可以指定一个特定的范围，并获取此区间所有合乎需要的键值。这两个特性对于减少客户端和服务端的来回交互来说都是至关重要的。

MySQL 8.0 enhances the InnoDB Memcached functionalities with multiple getoperations and support for range queries. We added support for the multiple getoperation to further improve the read performance, i.e. the user can fetch multiple key value pairs in a single memcached query. 

Support for range queries has been requested by Yoshinori @ Facebook. With range queries, the user can specify a particular range, and fetch all the qualified values in this range. Both features can significantly reduce the number of roundtrips between the client and the server.

• 持久化的自增计数器

8.0版本通过写入redo日志，持久化了自增计数器。持久化的自增计数器解决了一个年代悠久的BUG（译者注：实例在重启后，自增计数器重复利用了之前被使用后删除的自增值）。

MySQL恢复进程会重放redo日志，以确保自增计数器的准确数值。不再会出现自增值计数器数值的回滚。这意味着数据库实例在故障恢复时会将redo日志中最后一次发放的自增值作为重建计数器的起点。

确保了自增计数器的值不会重复发放，计数器的数值是单调递增的。但要注意可能会有空洞（即发放但是未使用的自增值）。未持久化的自增值在过去一直被认为是一个的有麻烦的故障点。

MySQL 8.0 persists the AUTOINC counters by writing them to the redo log. This is a fix for the very old Bug#199. The MySQL recovery process will replay the redo log and ensure correct values of the AUTOINC counters. There won’t be any rollback of AUTOINCcounters. This means that database recovery will reestablish the last known counter value after a crash. It comes with the guarantee that the AUTOINC counter cannot get the same value twice. The counter is monotonically increasing, but note that there can be gaps (unused values). The lack of persistent AUTOINC has been seen as troublesome in the past, e.g. see Bug#21641 reported by Stephen Dewey in 2006 or this blog post .

六、总结 Summary

综上所述，8.0版本带来了一大堆新特性和性能提升，马上从dev.mysql.com上下载并尝试一下吧(￣▽￣)"。

As shown above, MySQL 8.0 comes with a large set of new features and performance improvements. Download it from dev.mysql.com and try it out !

当然你也可以从既存的5.7实例升级到8.0版本。在此过程中，你可能需要试试我们随着shell工具包一同下发的新的升级检查工具。

这个工具可以帮你检查既存5.7版本的8.0版本升级兼容性（译者附：8.0.3到8.0.11不可以直接升级，或许我用到了旧的工具，改天再试）。可以试试Frédéric Descamps写的t Migrating to MySQL 8.0 without breaking old application 博文。

You can also upgrade an existing MySQL 5.7 to MySQL 8.0. In the process you might want to try our new Upgrade Checker that comes with the new MySQL Shell (mysqlsh). 

This utility will analyze your existing 5.7 server and tell you about potential 8.0 incompatibilities. Another good resource is the blog post Migrating to MySQL 8.0 without breaking old application by Frédéric Descamps.

在这篇文章中我们主要覆盖了服务端的新特性。不仅如此，我们还写了很多关于其他方面的文章，如复制，组复制，InnoDB集群，MySQL Shell,开发API及其相关的连接组件（(Connector/Node.js, Connector/Python, PHP, Connector/NET, Connector/ODBC,Connector/C++, and Connector/J）

In this blog post we have covered Server features. There is much more! We will also publish blog posts for other features such as Replication, Group Replication, InnoDB Cluster, Document Store, MySQL Shell, DevAPI, and DevAPI based Connectors 

(Connector/Node.js, Connector/Python, PHP, Connector/NET, Connector/ODBC,Connector/C++, and Connector/J).

就这么多，感谢您使用MySQL！

That’s it for now, and thank you for using MySQL !