清晨的一缕阳光
返回

MySQL 8.0 新特性

MySQL 8.0 新特性

MySQL 8.0 是 MySQL 发展史上的里程碑版本，引入了窗口函数、CTE、JSON 增强等众多企业级特性。本文将全面解析 MySQL 8.0 的核心新特性和最佳实践。

一、窗口函数（Window Functions）

1.1 什么是窗口函数

窗口函数对一组相关行进行计算，同时保留每行的详细信息：

-- 传统 GROUP BY（丢失明细）
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

-- 窗口函数（保留明细）
SELECT 
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

1.2 常用窗口函数

聚合窗口函数

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE
);

INSERT INTO employees VALUES
(1, '张三', '技术部', 15000, '2020-01-01'),
(2, '李四', '技术部', 18000, '2019-01-01'),
(3, '王五', '技术部', 12000, '2021-01-01'),
(4, '赵六', '销售部', 10000, '2020-01-01'),
(5, '钱七', '销售部', 13000, '2019-01-01');

-- 部门内工资排名
SELECT 
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

-- 结果：
-- name | department | salary | rank | dense_rank | row_num
-- 李四 | 技术部     | 18000  | 1    | 1          | 1
-- 张三 | 技术部     | 15000  | 2    | 2          | 2
-- 王五 | 技术部     | 12000  | 3    | 3          | 3
-- 钱七 | 销售部     | 13000  | 1    | 1          | 1
-- 赵六 | 销售部     | 10000  | 2    | 2          | 2

分布窗口函数

-- 计算工资百分比
SELECT 
  name,
  department,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank,
  CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;

-- 计算前/后行
SELECT 
  name,
  salary,
  LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
  LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary,
  FIRST_VALUE(salary) OVER (ORDER BY salary) AS min_salary,
  LAST_VALUE(salary) OVER (
    ORDER BY salary 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS max_salary
FROM employees;

1.3 窗口帧（Window Frame）

-- 计算累计工资
SELECT 
  name,
  salary,
  SUM(salary) OVER (
    ORDER BY hire_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM employees;

-- 计算移动平均（前后各一行）
SELECT 
  name,
  salary,
  AVG(salary) OVER (
    ORDER BY salary 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg
FROM employees;

-- 窗口帧子句：
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 从开始到当前行
-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING         -- 前 1 行到后 1 行
-- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- 从当前行到结束
-- RANGE BETWEEN ...                                -- 按值范围而非行数

1.4 实战案例

计算部门工资排名

-- 查询每个部门工资前三的员工
SELECT * FROM (
  SELECT 
    name,
    department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department 
      ORDER BY salary DESC
    ) AS rank
  FROM employees
) t
WHERE rank <= 3;

计算同比环比

CREATE TABLE monthly_sales (
  year_month CHAR(6),
  sales DECIMAL(12,2)
);

-- 计算环比（与上月相比）
SELECT 
  year_month,
  sales,
  LAG(sales, 1) OVER (ORDER BY year_month) AS prev_sales,
  ROUND(
    (sales - LAG(sales, 1) OVER (ORDER BY year_month)) / 
    LAG(sales, 1) OVER (ORDER BY year_month) * 100, 
    2
  ) AS mom_growth  -- Month over Month
FROM monthly_sales;

-- 计算同比（与去年同月相比）
SELECT 
  year_month,
  sales,
  LAG(sales, 12) OVER (ORDER BY year_month) AS prev_year_sales,
  ROUND(
    (sales - LAG(sales, 12) OVER (ORDER BY year_month)) / 
    LAG(sales, 12) OVER (ORDER BY year_month) * 100, 
    2
  ) AS yoy_growth  -- Year over Year
FROM monthly_sales;

二、公用表表达式（CTE）

2.1 基础 CTE

-- 传统子查询（难以阅读）
SELECT * FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) t
WHERE avg_salary > 15000;

-- CTE（清晰易读）
WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT * FROM dept_avg
WHERE avg_salary > 15000;

2.2 多个 CTE

WITH 
dept_stats AS (
  SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
),
high_pay_depts AS (
  SELECT department
  FROM dept_stats
  WHERE avg_salary > 15000
)
SELECT e.*
FROM employees e
INNER JOIN high_pay_depts h ON e.department = h.department;

2.3 递归 CTE

-- 生成序列 1-10
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- 组织架构查询（员工 - 经理层级）
CREATE TABLE org_chart (
  employee_id INT,
  employee_name VARCHAR(50),
  manager_id INT
);

INSERT INTO org_chart VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, '技术经理', 2),
(5, '开发工程师', 4);

-- 查询某员工的所有上级
WITH RECURSIVE hierarchy AS (
  -- 锚点成员：起始员工
  SELECT employee_id, employee_name, manager_id, 0 AS level
  FROM org_chart
  WHERE employee_id = 5
  
  UNION ALL
  
  -- 递归成员：上级经理
  SELECT o.employee_id, o.employee_name, o.manager_id, h.level + 1
  FROM org_chart o
  INNER JOIN hierarchy h ON o.employee_id = h.manager_id
)
SELECT * FROM hierarchy;

-- 结果：
-- employee_id | employee_name | manager_id | level
-- 5           | 开发工程师    | 4          | 0
-- 4           | 技术经理      | 2          | 1
-- 2           | CTO           | 1          | 2
-- 1           | CEO           | NULL       | 3

三、JSON 增强

3.1 JSON 聚合函数

CREATE TABLE products (
  id INT,
  category VARCHAR(50),
  name VARCHAR(100),
  price DECIMAL(10,2)
);

-- JSON_ARRAYAGG：聚合成数组
SELECT 
  category,
  JSON_ARRAYAGG(name) AS products
FROM products
GROUP BY category;

-- JSON_OBJECTAGG：聚合成对象
SELECT 
  category,
  JSON_OBJECTAGG(id, name) AS product_map
FROM products
GROUP BY category;

3.2 JSON 路径增强

CREATE TABLE user_profile (
  user_id INT,
  profile JSON
);

INSERT INTO user_profile VALUES
(1, '{"name": "张三", "skills": ["Java", "Python", "Go"], "projects": [{"name": "项目 A", "role": "开发"}, {"name": "项目 B", "role": "架构"}]}');

-- 提取嵌套值
SELECT profile->'$.name' AS name FROM user_profile;
SELECT profile->'$.skills[0]' AS first_skill FROM user_profile;

-- 路径存在检查
SELECT JSON_CONTAINS_PATH(profile, 'one', '$.skills') AS has_skills 
FROM user_profile;

-- 搜索值
SELECT JSON_SEARCH(profile, 'one', 'Java') AS java_path 
FROM user_profile;
-- 结果：$.skills[0]

3.3 JSON 表函数

-- JSON_TABLE：将 JSON 转换为关系表
SELECT jt.*
FROM user_profile,
JSON_TABLE(
  profile,
  '$.projects[*]' COLUMNS(
    project_name VARCHAR(50) PATH '$.name',
    project_role VARCHAR(50) PATH '$.role'
  )
) AS jt;

-- 结果：
-- project_name | project_role
-- 项目 A        | 开发
-- 项目 B        | 架构

四、隐藏索引

4.1 什么是隐藏索引

隐藏索引对优化器不可见，但仍然存在和维护：

-- 创建隐藏索引
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(100),
  INDEX idx_email (email) INVISIBLE
);

-- 或者修改现有索引为隐藏
ALTER TABLE users 
ALTER INDEX idx_email INVISIBLE;

-- 恢复索引可见性
ALTER TABLE users 
ALTER INDEX idx_email VISIBLE;

4.2 使用场景

-- 场景 1：测试删除索引的影响
-- 先隐藏索引，观察性能变化
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

-- 运行性能测试
SELECT * FROM orders WHERE status = 1;

-- 如果性能下降，恢复索引
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

-- 如果性能无影响，可以删除索引
ALTER TABLE orders DROP INDEX idx_status;

-- 场景 2：A/B 测试
-- 对不同查询强制使用/不使用隐藏索引
SELECT * FROM orders 
WHERE status = 1 
  AND created_at > '2024-01-01'
  /*+ NO_INDEX(orders idx_status) */;

4.3 注意事项

-- ⚠️ 主键不能隐藏
ALTER TABLE users ALTER INDEX PRIMARY INVISIBLE;  -- ❌ 错误

-- ⚠️ 唯一索引如果允许 NULL 可以隐藏
CREATE TABLE t (
  id INT,
  unique_id INT UNIQUE,
  INDEX idx_unique (unique_id) INVISIBLE
);

-- ✅ 优化器默认忽略隐藏索引
-- 但可以使用 USE INDEX 强制使用
SELECT * FROM users 
USE INDEX (idx_email)
WHERE email = 'test@example.com';

五、降序索引

5.1 降序索引语法

-- MySQL 8.0 支持真正的降序索引
CREATE TABLE logs (
  id BIGINT PRIMARY KEY,
  created_at DATETIME,
  INDEX idx_created_desc (created_at DESC)
);

-- 联合索引可以混合排序
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  created_at DATETIME,
  INDEX idx_user_created (user_id ASC, created_at DESC)
);

5.2 性能优势

-- 场景：查询最新订单（降序排序 + LIMIT）
SELECT * FROM orders 
WHERE user_id = 1 
ORDER BY created_at DESC 
LIMIT 10;

-- 升序索引：需要 filesort
-- 降序索引：直接按索引顺序读取，无需排序

-- EXPLAIN 对比
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1 
ORDER BY created_at DESC 
LIMIT 10;

-- Extra 字段：
-- 升序索引：Using index condition; Using filesort
-- 降序索引：Using index condition

5.3 注意事项

-- ⚠️ 降序索引只适用于 InnoDB
CREATE TABLE t_myisam (
  id INT,
  created_at DATETIME,
  INDEX idx_created_desc (created_at DESC)
) ENGINE = MyISAM;  -- 实际仍按升序存储

-- ✅ 检查索引是否真正降序
SHOW INDEX FROM orders WHERE Key_name = 'idx_user_created';

-- Collation 字段：A=升序，D=降序

六、其他重要特性

6.1 通用表表达式优化

-- 物化 CTE（自动优化）
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 1000000
)
SELECT COUNT(*) FROM numbers WHERE n % 2 = 0;

-- MySQL 8.0 自动物化 CTE，避免重复计算

6.2 组复制增强

-- MySQL 8.0 支持多主模式
-- MGR（MySQL Group Replication）

-- 查看组复制状态
SELECT * FROM performance_schema.replication_group_members;

-- 查看成员状态
SELECT * FROM performance_schema.replication_group_member_stats;

6.3 性能 schema 增强

-- 新增更多监控指标
SELECT * FROM performance_schema.accounts;
SELECT * FROM performance_schema.users;
SELECT * FROM performance_schema.hosts;

-- 慢查询日志增强
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

6.4 字符集默认值

-- MySQL 8.0 默认字符集改为 utf8mb4
-- 默认排序规则改为 utf8mb4_0900_ai_ci

SHOW VARIABLES LIKE 'character_set_server';
-- 结果：utf8mb4

SHOW VARIABLES LIKE 'collation_server';
-- 结果：utf8mb4_0900_ai_ci

七、升级指南

7.1 升级前检查

-- 1. 检查兼容性
mysql_upgrade -u root -p

-- 2. 检查保留字冲突
-- MySQL 8.0 新增保留字：RANK, OVER, ROWS, RECURSIVE 等
SHOW RESERVED WORDS;

-- 3. 备份所有数据库
mysqldump -u root -p --all-databases > backup.sql

7.2 升级步骤

# 1. 备份数据
mysqldump -u root -p --all-databases > full_backup.sql

# 2. 停止 MySQL 服务
sudo systemctl stop mysql

# 3. 升级 MySQL（以 Ubuntu 为例）
sudo apt-get update
sudo apt-get install mysql-server

# 4. 启动 MySQL 服务
sudo systemctl start mysql

# 5. 执行升级脚本
mysql_upgrade -u root -p

# 6. 验证版本
mysql --version

7.3 升级后优化

-- 1. 重建系统表
mysql_upgrade -u root -p

-- 2. 分析所有表
mysqlcheck -u root -p --analyze --all-databases

-- 3. 优化所有表
mysqlcheck -u root -p --optimize --all-databases

-- 4. 更新统计信息
ANALYZE TABLE table_name;

-- 5. 检查字符集
SELECT 
  table_schema,
  table_name,
  table_collation
FROM information_schema.tables
WHERE table_collation NOT LIKE 'utf8mb4%';

八、性能对比

8.1 窗口函数性能

-- 传统方式（多次扫描）
SELECT 
  e1.name,
  e1.salary,
  (SELECT AVG(salary) FROM employees WHERE department = e1.department) AS dept_avg
FROM employees e1;

-- 窗口函数（单次扫描）
SELECT 
  name,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- 性能提升：5-10 倍

8.2 CTE vs 子查询

-- 子查询（重复执行）
SELECT * FROM (
  SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department
) t1
JOIN (
  SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department
) t2 USING (department);

-- CTE（执行一次）
WITH dept_stats AS (
  SELECT department, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
  FROM employees GROUP BY department
)
SELECT * FROM dept_stats;

-- 性能提升：2-5 倍

总结

MySQL 8.0 核心新特性：

  1. 窗口函数：RANK、ROW_NUMBER、LAG/LEAD、聚合函数
  2. CTE：WITH 子句、递归查询
  3. JSON 增强：聚合函数、表函数、路径查询
  4. 隐藏索引：测试索引影响，无需删除
  5. 降序索引：真正的 DESC 索引，优化排序查询
  6. 字符集升级：默认 utf8mb4

升级建议：

基础架构篇完结。下一篇：B+ 树索引原理 - 深入解析索引核心数据结构、聚簇索引和二级索引的实现原理

分享这篇文章到:
分享到QQ 分享到微博 分享文章到Email
上一篇文章
MySQL 内存参数配置与调优
下一篇文章
Prompt 版本管理与测试实战