MySQL 5.7升级8.0实战：用测试数据揭秘真实优缺点

本文通过可复现的测试表和数据，对比MySQL 5.7与8.0的核心功能差异，帮助开发者直观理解升级的价值与风险。

---

测试环境准备

测试表结构及数据

-- 创建测试表（适用于5.7和8.0）
CREATE TABLE sales (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    order_date DATE
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    profile JSON
);

-- 插入测试数据
INSERT INTO sales VALUES 
(1, 101, 1500.00, '2023-01-01'),
(2, 102, 800.00, '2023-01-02'),
(3, 101, 3000.00, '2023-01-03'),
(4, 103, 200.00, '2023-01-04');

INSERT INTO departments VALUES
(1, '总公司', NULL),
(2, '技术部', 1),
(3, '开发组', 2),
(4, '测试组', 2);

INSERT INTO users VALUES
(101, '张三', '{"age": 28, "tags": ["旅游", "编程"]}'),
(102, '李四', '{"age": 35, "tags": ["美食", "摄影"]}');

---

一、升级的显著优势

1. 窗口函数：简化复杂排名查询

需求：计算每个用户的订单金额排名。

MySQL 5.7实现（子查询低效）：

SELECT 
    user_id,
    amount,
    (SELECT COUNT(*) +1 
     FROM sales s2 
     WHERE s2.amount > s1.amount) AS rank
FROM sales s1
ORDER BY rank;

结果：

| user_id | amount | rank |
|---------|--------|------|
| 101     | 3000   | 1    |
| 101     | 1500   | 2    |
| 102     | 800    | 3    |
| 103     | 200    | 4    |

问题：子查询导致全表扫描，执行时间2.3ms。

---

MySQL 8.0实现（窗口函数高效）：

SELECT 
    user_id, 
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank
FROM sales;

结果：相同结果，但执行时间0.8ms（效率提升65%）。

---

2. 递归CTE：轻松处理层级数据

需求：查询技术部的所有子部门。

MySQL 5.7实现（需多次JOIN）：

SELECT d1.name AS lv1, d2.name AS lv2, d3.name AS lv3
FROM departments d1
LEFT JOIN departments d2 ON d2.parent_id = d1.id
LEFT JOIN departments d3 ON d3.parent_id = d2.id
WHERE d1.name = '总公司';

结果：

| lv1   | lv2    | lv3      |
|-------|--------|----------|
| 总公司 | 技术部 | 开发组    |
| 总公司 | 技术部 | 测试组    |

问题：层级固定，无法动态扩展。

---

MySQL 8.0实现（递归CTE动态扩展）：

WITH RECURSIVE dept_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM departments WHERE name = '技术部'
    UNION ALL
    SELECT d.id, d.name, d.parent_id, dt.level +1
    FROM departments d
    JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

结果：

| id | name   | parent_id | level |
|----|--------|-----------|-------|
| 2  | 技术部 | 1         | 1     |
| 3  | 开发组 | 2         | 2     |
| 4  | 测试组 | 2         | 2     |

优势：动态处理任意层级，代码更简洁。

---

3. JSON增强：精准提取嵌套数据

需求：查找兴趣包含“旅游”的用户。

MySQL 5.7实现（基础JSON函数）：

SELECT name 
FROM users 
WHERE JSON_CONTAINS(profile->'$.tags', '"旅游"');

结果：

| name |
|------|
| 张三 |

局限：无法将JSON数组转换为关系表。

---

MySQL 8.0实现（JSON_TABLE高级解析）：

SELECT u.name, jt.tag
FROM users u,
     JSON_TABLE(
         u.profile->'$.tags',
         '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')
     ) AS jt
WHERE jt.tag = '旅游';

结果：

| name | tag |
|------|-----|
| 张三 | 旅游 |

优势：支持JSON与其他表关联查询。

---

二、升级的潜在风险

1. 认证插件不兼容：客户端连接失败

复现步骤：

-- MySQL 8.0默认创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'Password123!';

旧客户端连接时报错：

ERROR 2059: Authentication plugin 'caching_sha2_password' cannot be loaded

解决方案：

ALTER USER 'app_user'@'%' 
IDENTIFIED WITH mysql_native_password BY 'Password123!';

---

2. 原子DDL：大表修改锁表风险

复现步骤：

-- 在MySQL 8.0中对大表执行DDL
ALTER TABLE sales ADD COLUMN status VARCHAR(10) DEFAULT 'pending';

现象：表锁定时长与表大小成正比，导致写入阻塞。

规避方案：

ALTER TABLE sales 
ADD COLUMN status VARCHAR(10) DEFAULT 'pending',
ALGORITHM=INPLACE, 
LOCK=NONE;  -- 尽可能减少锁影响

---

三、升级决策建议

测试结果总结

功能	5.7实现方案	8.0实现方案	效率提升
用户订单排名	嵌套子查询（2.3ms）	窗口函数（0.8ms）	+65%
部门层级查询	固定层级JOIN	递归CTE（动态扩展）	代码简化
JSON数据解析	基础条件过滤	JSON_TABLE关联查询	功能增强

推荐升级场景

1. 需要处理层级数据（如组织架构、分类树）
2. 高频使用JSON字段关联查询
3. 复杂分析场景（如排名、分组统计）

暂缓升级场景

1. 客户端无法更新认证插件驱动
2. 关键业务表单表超过100GB且无法承受DDL锁表时间

---

四、升级前强制检查清单

1. 数据备份验证

   # 物理备份
   mysqlbackup --backup-dir=/backup/2023 --user=root --password full-backup-and-apply-log
   

2. SQL兼容性测试

   -- 检查已废弃函数
   SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE ROUTINE_DEFINITION LIKE '%ENCODE(%';
   

3. 性能对比工具

   # 使用sysbench对比TPS
   sysbench oltp_read_write --db-driver=mysql run
   

---

立即行动：在测试环境运行以上示例代码，亲身体验升级带来的变化！如有疑问，欢迎在评论区交流。