MySQL 升级：MySQL Server 变更详解 (8.0.36 ~ 8.0.37)

2024 年上半年，MySQL Server 共发布了两个版本：

1. MySQL 8.0.36, 2024-01-16
2. MySQL 8.0.37, 2024-04-30

下面探讨这些版本的重要变更，并以 MySQL 8.0.37 为例做演示。

优化器

1. (MySQL 8.0.36) The hashing algorithm employed yielded poor performance when using a HASH field to check for uniqueness. (Bug #109548, Bug #34959356)

示例：

问题复现案例中的执行计划，新版本执行很快，问题版本则要若干秒。

mysql> EXPLAIN ANALYZE SELECT COUNT(*) FROM ( SELECT value FROM product GROUP BY code, name, comment, platform ) derived\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)  (cost=8600..8600 rows=1) (actual time=40.5..40.5 rows=1 loops=1)
    -> Table scan on derived  (cost=6329..6584 rows=20167) (actual time=37.6..39.6 rows=20000 loops=1)
        -> Materialize  (cost=6329..6329 rows=20167) (actual time=37.6..37.6 rows=20000 loops=1)
            -> Table scan on <temporary>  (cost=4058..4312 rows=20167) (actual time=32.9..36 rows=20000 loops=1)
                -> Temporary table with deduplication  (cost=4058..4058 rows=20167) (actual time=32.9..32.9 rows=20000 loops=1)
                    -> Table scan on product  (cost=2041 rows=20167) (actual time=0.0257..14.6 rows=20000 loops=1)

1 row in set (0.04 sec)

2. (MySQL 8.0.37) The multi-range read (MRR) optimization did not perform as well as in previous releases. (Bug #113711, Bug #36220640)

解析：

MySQL 8.0.36 开启 MRR 后，主键排序性能下降。

MRR 可用于表的索引范围扫描和等值连接操作。当使用 MRR 时，EXPLAIN 输出中的 Extra 列显示 Using MRR。

Performance Schema

1. (MySQL 8.0.36) When executing a stored program, the Performance Schema instrumentation caused some unnecessary overhead. As of this release, all stored procedure micro instructions (statement/sp/%), except statement/sp/stmt, are disabled by default. (Bug #27934653)

示例：

mysql> SELECT name,enabled,timed
    -> FROM performance_schema.setup_instruments
    -> WHERE name LIKE 'statement/sp/%';
+--------------------------------+---------+-------+
| name                           | enabled | timed |
+--------------------------------+---------+-------+
| statement/sp/stmt              | YES     | YES   |
| statement/sp/set               | NO      | NO    |
| statement/sp/set_trigger_field | NO      | NO    |
| statement/sp/jump              | NO      | NO    |
| statement/sp/jump_if_not       | NO      | NO    |
| statement/sp/freturn           | NO      | NO    |
| statement/sp/hpush_jump        | NO      | NO    |
| statement/sp/hpop              | NO      | NO    |
| statement/sp/hreturn           | NO      | NO    |
| statement/sp/cpush             | NO      | NO    |
| statement/sp/cpop              | NO      | NO    |
| statement/sp/copen             | NO      | NO    |
| statement/sp/cclose            | NO      | NO    |
| statement/sp/cfetch            | NO      | NO    |
| statement/sp/error             | NO      | NO    |
| statement/sp/set_case_expr     | NO      | NO    |
+--------------------------------+---------+-------+
16 rows in set (0.00 sec)

功能

1. (MySQL 8.0.36) For platforms on which OpenSSL libraries are bundled, the linked OpenSSL library for MySQL Server has been updated to version 3.0.12. Issues fixed in OpenSSL version 3.0.12 are described at https://www.openssl.org/news/cl30.txt. (Bug #36033684)

2. (MySQL 8.0.37) For platforms on which OpenSSL libraries are bundled, the linked OpenSSL library for MySQL Server has been updated to version 3.0.13. Issues fixed in OpenSSL version 3.0.13 are described at https://www.openssl.org/news/cl30.txt. (Bug #36261675)

解析：

在 Oracle Linux 7 上仍可使用 openssl 1.1.1，但在 Oracle Linux 8/9 系统，则应使用 openssl 3。

3. (MySQL 8.0.37) Group Replication: When issued with group_replication_consistency set to BEFORE_ON_PRIMARY_FAILOVER, the MySQL KILL statement now ignores any consistency guarantees, with any interrupted transactions now being rolled back.

4. (MySQL 8.0.37) Packaging: Added support for Fedora 40 and Ubuntu 24.04.

解析：

Fedora 40 将作为 CentOS Stream 10 和 RHEL 10 的上游，意味着 MySQL 已经在为新版本的企业级操作系统做准备。

5. (MySQL 8.0.37) The clone plugin version requirements were relaxed to allow cloning between different point releases in the same series. In other words, only the major and minor version numbers must match when previously the point release number also had to match.

问题修复

1. (MySQL 8.0.37) The Robin Hood hashing library has been replaced with unordered_dense. (Bug #36158022)

解析：

从 MySQL 8.0.37 开始，引入 ankerl::unordered_dense v4.4.0 类库，替换掉之前的 robin-hood-hashing 。

unordered_dense 是一个高性能哈希表实现，基于 robin-hood 后移删除算法，适用于 C++17 及更高版本。