阿里面试题：MySQL 5.x 和 8.0 有什么区别？

一、MySQL 5.x与8.0核心区别

1. 性能优化

• 提升幅度：MySQL 8.0性能较5.7提升2倍。
• 优化方向：
• 并发控制：改进InnoDB并发处理，减少锁竞争。
• IO负载：优化Buffer Pool，降低磁盘IO消耗。
• 热点竞争：减少全局锁争用，提升高并发场景下的事务处理能力。
• 适用场景：高并发、高IO负载的业务场景优化显著。

2. NoSQL支持（JSON增强）

• 5.7限制：仅引入JSON数据类型，但查询功能有限。
• 8.0特性：
• 解析与聚合：支持JSON_TABLE()（解析JSON为表结构）、JSON_ARRAYAGG()/JSON_OBJECTAGG()（JSON聚合运算）。
• 数据提取：通过JSON_EXTRACT()从JSON中提取指定字段。
• 应用价值：完善JSON处理能力，适配NoSQL与关系型混合存储场景。

3. SQL语法增强

（1）窗口函数（Window Functions）

• 支持函数：RANK()、DENSE_RANK()、ROW_NUMBER()等。
• 示例：

  SELECT id, name, salary, 
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
  FROM employees;

• 对比：5.7需通过子查询或GROUP BY实现类似逻辑，8.0更简洁高效。

（2）公用表表达式（CTE）

• 语法：通过WITH cte AS (...)定义临时结果集，避免嵌套子查询。
• 示例：

  WITH cte AS (
    SELECT id, name, department FROM employees WHERE salary > 5000
  )
  SELECT * FROM cte WHERE department = 'IT';

• 优势：提升SQL可读性，简化复杂查询逻辑。

（3）行锁优化（SELECT FOR UPDATE）

• 5.7行为：锁定行时查询会阻塞等待。
• 8.0新增：
• NOWAIT：若行被锁定，立即报错不等待。
• SKIP LOCKED：跳过已锁定的行，继续执行。
• 场景：适用于高并发下需要快速处理锁冲突的业务。

4. 索引优化

（1）隐藏索引（Invisible Indexes）

• 语法：

  ALTER TABLE employees ALTER INDEX emp_name_idx INVISIBLE;

• 作用：测试索引对查询性能的影响，避免误删索引导致性能下降。

（2）降序索引（Descending Index）

• 语法：

  CREATE INDEX emp_salary_desc_idx ON employees (salary DESC);

• 优势：直接支持降序索引，避免5.7中ORDER BY DESC的额外排序开销。

5. 可靠性增强（DDL事务化）

• 5.7缺陷：DDL操作失败可能导致数据不完整。
• 8.0特性：InnoDB表支持DDL事务，失败时自动回滚，保证数据完整性。

6. 高可用性（InnoDB集群）

• 5.7方案：依赖传统主从复制，需手动配置故障切换。
• 8.0特性：原生支持InnoDB Group Replication，实现自动主从切换、多主模式，简化高可用部署。

7. 安全性增强

• 默认认证插件：
• 5.7：默认使用mysql_native_password，安全性较低。
• 8.0：默认使用caching_sha2_password，基于SHA-256加密，提升认证安全性。
• 创建用户示例：

  CREATE USER 'user'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

8. 默认字符集

• 5.7：默认字符集为latin1，不支持Emoji等Unicode字符，需手动设置utf8mb4。
• 8.0：默认字符集为utf8mb4，完整支持Unicode，避免字符集兼容问题。

9. 查询缓存调整

• 5.7：内置查询缓存，但存在严重锁争用问题，影响并发性能。
• 8.0：移除查询缓存，推荐使用Redis、Memcached等外部缓存方案，提升并发查询效率。

二、对比总结

对比项	MySQL 5.7及以下	MySQL 8.0
性能	低	优化并发&IO，性能提升2倍
NoSQL支持（JSON）	支持有限	增强JSON查询，支持JSON_TABLE()
窗口函数	❌ 不支持	✅ 支持RANK()、ROW_NUMBER()
CTE（公用表表达式）	❌ 不支持	✅ 支持WITH cte AS (...)语法
行锁机制（SELECT FOR UPDATE）	阻塞等待	✅ 支持NOWAIT和SKIP LOCKED
索引优化	无隐藏索引	✅ 支持INVISIBLE INDEX
降序索引	ORDER BY DESC需额外排序	✅ 支持CREATE INDEX ... DESC
事务性DDL	❌ 不支持	✅ 支持InnoDB事务化DDL
高可用性	传统主从复制	✅ 原生InnoDB Group Replication
安全性	明文密码认证	✅ caching_sha2_password加密
默认字符集	latin1	✅ utf8mb4
查询缓存	存在（性能瓶颈）	❌ 已移除，推荐外部缓存

总结：MySQL 8.0在性能、功能、可靠性和安全性等方面全面优于5.x版本，尤其适合高并发、大数据量的企业级场景。升级时需注意查询缓存移除、认证插件兼容性等问题，建议通过测试验证后逐步迁移。