mysql的5.7版本与8.0版本的差异与兼容性

MySQL 5.7 和 8.0 是两个重要的版本，它们在性能、功能、安全性等方面都有显著的改进，同时也存在一些兼容性问题。以下是具体的改进点和兼容性问题：

---

一、MySQL 8.0 的改进点

1. 性能提升

   • 优化器改进：MySQL 8.0 对查询优化器进行了重大改进，支持更复杂的查询计划，性能显著提升。
   • 降序索引：支持降序索引（Descending Indexes），优化了 ORDER BY ... DESC 查询的性能。
   • 隐藏索引：支持隐藏索引（Invisible Indexes），可以在不删除索引的情况下测试其对查询性能的影响。
   • 资源组：引入资源组（Resource Groups），可以对不同线程分配不同的 CPU 资源，优化多租户环境下的性能。

2. JSON 增强

   • JSON 函数扩展：新增了多个 JSON 函数，如 JSON_TABLE()、JSON_UNQUOTE(JSON_CONTAINS_PATH()) 等，简化了 JSON 数据的处理。
   • JSON 聚合：支持对 JSON 数据进行聚合操作，例如使用 JSON_ARRAYAGG() 和 JSON_OBJECTAGG()。

3. 窗口函数

   • 支持窗口函数（Window Functions），如 ROW_NUMBER()、RANK()、NTILE() 等，简化了复杂查询的编写。

4. 公用表表达式（CTE）

   • 支持递归和非递归的公用表表达式（Common Table Expressions, CTE），提高了查询的可读性和可维护性。

5. 持久化设置

   • 支持将系统变量持久化到配置文件中，重启后无需重新设置。

6. 安全性增强

   • 默认认证插件：将默认认证插件从 mysql_native_password 改为 caching_sha2_password，提供了更强的安全性。
   • 密码策略：增加了密码过期策略、密码强度检查等功能。
   • 角色管理：支持基于角色的访问控制（Role-Based Access Control, RBAC）。

7. InnoDB 改进

   • 原子 DDL：支持原子数据定义语言（Atomic DDL），确保 DDL 操作要么完全成功，要么完全回滚。
   • 即时列添加：支持即时添加列（Instant ADD COLUMN），无需重建表。
   • 下推过滤：改进了下推过滤（Pushdown Filters），减少了从存储引擎返回的数据量。

8. 其他改进

   • 默认字符集：将默认字符集从 latin1 改为 utf8mb4，更好地支持多语言和表情符号。
   • 崩溃安全复制：改进了复制的崩溃安全性，减少了主从复制延迟。
   • 数据字典：引入了新的数据字典架构，提升了元数据的管理效率。

---

二、MySQL 5.7 的改进点

1. 性能提升

   • JSON 支持：首次引入了对原生 JSON 数据类型的支持，提供了丰富的 JSON 函数。
   • 生成列：支持生成列（Generated Columns），可以在查询时自动计算列值。
   • 并行复制：改进了复制功能，支持基于组提交的并行复制，减少了主从延迟。

2. 安全性增强

   • 密码过期：支持密码过期策略，强制用户定期更改密码。
   • SSL/TLS 加密：改进了 SSL/TLS 支持，提供了更安全的连接。

3. InnoDB 改进

   • 在线 DDL：支持更多的在线 DDL 操作，减少了表锁定时间。
   • 临时表空间：改进了临时表空间的管理，支持更大的临时表。

4. 其他改进

   • GIS 支持：增强了地理空间（GIS）功能的支持。
   • 性能模式：扩展了性能模式（Performance Schema），提供了更多的监控指标。

---

三、核心改进点比较

1. 性能优化

• 查询性能提升：
• 支持窗口函数（如 ROW_NUMBER(), RANK()），简化复杂分析查询，避免多层嵌套子查询。
• 引入并行查询（需手动开启），加速大数据量下的聚合操作。
• 优化器改进：支持隐藏索引（Invisible Indexes），可临时禁用索引测试性能影响；新增降序索引优化 ORDER BY 性能。

• 并发与事务处理：
• 原子DDL：DDL操作（如 ALTER TABLE）支持事务原子性，失败自动回滚。
• 改进InnoDB的锁机制，支持 NOWAIT 和 SKIP LOCKED 选项，减少锁冲突。

2. 功能增强

• JSON支持：
• 新增 JSON_TABLE() 函数将JSON转为关系表，增强 ->> 操作符简化路径查询。
• 支持 JSON_MERGE_PATCH() 合并文档，提升非结构化数据处理能力。

• SQL扩展：
• 支持通用表表达式（CTE），包括递归查询，提升复杂查询可读性。
• 新增 GROUP BY 默认排序取消，需显式使用 ORDER BY。

3. 安全性提升

• 身份验证：默认改用 caching_sha2_password 插件，旧客户端需升级或切换为 mysql_native_password。
• 密码策略：支持密码过期、历史记录和复杂度检查。
• 数据加密：InnoDB表空间加密功能增强。

4. 存储引擎与数据字典

• 数据字典重构：元数据从文件存储改为InnoDB表存储，提升崩溃恢复能力。
• 自增列持久化：重启后自增值不会重置，避免数据不一致。

---

四、兼容性问题

1. 认证插件

   • 默认认证插件变更：MySQL 8.0 默认使用 caching_sha2_password 认证插件，而 MySQL 5.7 使用的是 mysql_native_password。如果客户端不支持 caching_sha2_password，可能会导致连接失败。
     • 解决方案：可以将 MySQL 8.0 的默认认证插件改回 mysql_native_password，或者在客户端升级支持 caching_sha2_password。

2. 字符集和排序规则

   • 默认字符集变更：MySQL 8.0 默认字符集为 utf8mb4，而 MySQL 5.7 默认是 latin1。如果数据库或表使用了不同的字符集，可能会导致字符集不兼容的问题。
     • 解决方案：在升级前，确保所有数据库和表使用兼容的字符集和排序规则。

3. SQL 模式

   • ONLY_FULL_GROUP_BY 模式：MySQL 8.0 默认启用了 ONLY_FULL_GROUP_BY 模式，而 MySQL 5.7 默认是关闭的。如果查询中使用了非法的 GROUP BY 子句，可能会导致错误。
     • 解决方案：可以在 MySQL 8.0 中禁用 ONLY_FULL_GROUP_BY 模式，或者修改查询以符合该模式的要求。

4. 数据字典

   • 数据字典架构变更：MySQL 8.0 引入了新的数据字典架构，可能会导致一些与元数据相关的操作不兼容。
     • 解决方案：在升级前，确保所有工具和脚本都兼容新的数据字典架构。

5. JSON 函数

   • JSON 函数变更：MySQL 8.0 中的一些 JSON 函数与 MySQL 5.7 不兼容，例如 JSON_MERGE() 在 MySQL 8.0 中被弃用，改用 JSON_MERGE_PATCH() 或 JSON_MERGE_PRESERVE()。
     • 解决方案：在升级前，检查并修改所有使用 JSON 函数的查询。

6. 窗口函数和 CTE

   • 不支持窗口函数和 CTE：MySQL 5.7 不支持窗口函数和 CTE，如果查询中使用了这些功能，在 MySQL 5.7 中会报错。
     • 解决方案：在升级到 MySQL 8.0 之前，避免使用这些功能，或者使用替代方案。

7. 降序索引和隐藏索引

   • 不支持降序索引和隐藏索引：MySQL 5.7 不支持降序索引和隐藏索引，如果表定义中使用了这些索引类型，在 MySQL 5.7 中会报错。
     • 解决方案：在升级前，移除或修改这些索引。

8. 资源组

   • 不支持资源组：MySQL 5.7 不支持资源组功能，如果配置中使用了资源组，在 MySQL 5.7 中会报错。
     • 解决方案：在升级前，移除或修改资源组配置。

---

五、升级建议

1. 预升级检查：
   • 使用 mysql_upgrade 工具检测数据类型、孤立的 .frm 文件等问题。
   • 检查应用程序中是否使用过时函数或语法。

2. 测试与回滚方案：
   • 在测试环境验证性能关键查询（如窗口函数、CTE）的兼容性。
   • 备份数据并规划回滚流程，避免生产环境故障。

3. 配置调优：
   • 调整 innodb_buffer_pool_size（建议物理内存的60-70%）。
   • 启用并行查询优化（如 SET optimizer_switch='parallel_query=on'）。

4. 备份数据

   • 在升级前，务必对数据库进行完整备份，包括数据和配置文件。

5. 测试环境

   • 在测试环境中模拟升级过程，确保所有应用程序和脚本都能在 MySQL 8.0 中正常运行。

6. 逐步升级

   • 如果数据库规模较大，可以考虑逐步升级，例如先升级从库，再升级主库。

7. 检查兼容性

   • 使用工具（如 mysql_upgrade）检查数据库的兼容性，并修复发现的问题。

8. 更新客户端

   • 确保所有客户端应用程序和库都支持 MySQL 8.0，特别是认证插件和字符集相关的功能。

9. 监控性能

   • 在升级后，密切监控数据库的性能，及时发现并解决潜在的问题。

---

五、总结

MySQL 8.0 在性能、功能扩展和安全性方面显著优于 5.7，但升级需重点解决身份验证、字符集、严格模式等兼容性问题。建议结合业务场景评估，优先在新项目中采用 8.0，现有系统升级前需充分测试并调整代码与配置。

---