MySQL 8.0 新特性全览

一、窗口函数（Window Functions）

最实用的新特性之一，告别自连接和子查询。

-- 排名：每个部门的工资排名
SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num,
       SUM(salary) OVER (PARTITION BY dept) AS dept_total,
       LAG(salary, 1) OVER (PARTITION BY dept ORDER BY salary) AS prev_salary
FROM employees;

-- 累计求和
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- 前 N 名
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) t WHERE rn <= 3;

常用函数：ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG()、SUM() OVER、FIRST_VALUE()、LAST_VALUE()

二、通用表表达式 CTE（Common Table Expressions）

非递归 CTE

-- 替代子查询，可读性更好
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

递归 CTE（层次查询）

-- 查组织树：从CEO往下查所有下属
WITH RECURSIVE org_tree AS (
    -- 锚点：顶级领导
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- 递归：逐级展开
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

三、SKIP LOCKED 和 NOWAIT

-- 跳过被锁的行（并发任务队列）
SELECT * FROM task_queue WHERE status = 'PENDING'
LIMIT 1 FOR UPDATE SKIP LOCKED;

-- 遇到锁立即报错，不等待
SELECT * FROM inventory WHERE sku = 'SKU001'
FOR UPDATE NOWAIT;

方式	行为	遇到锁时的表现
`SELECT ... FOR UPDATE`	默认	阻塞等待，直到锁释放或超时
`SELECT ... FOR UPDATE NOWAIT`	MySQL 8.0+	立即报错，不等待
`SELECT ... FOR UPDATE SKIP LOCKED`	MySQL 8.0+	直接跳过被锁的行，返回未被锁的行

通过捕获PessimisticLockingFailureException类型的异常，判断是否获取锁异常

四、函数索引（Functional Indexes）

8.0 之前无法对 JSON 路径或表达式建索引，8.0 支持：

-- 对 JSON 字段的某个 key 建索引
CREATE INDEX idx_user_name
ON users ((CAST(info->>'$.name' AS CHAR(50))));

-- 对表达式建索引
CREATE INDEX idx_lower_email
ON users ((LOWER(email)));

-- 对日期函数建索引
CREATE INDEX idx_order_month
ON orders ((DATE_FORMAT(order_time, '%Y-%m')));

五、降序索引（Descending Indexes）

8.0 之前 DESC 索引只是语法糖，实际还是升序存储。8.0 真正支持：

-- 8.0 真正的降序索引
CREATE INDEX idx_time_amount ON orders (create_time DESC, amount ASC);

-- 场景：查最近的大额订单，索引直接命中
SELECT * FROM ORDER BY create_time DESC, amount ASC LIMIT 10;

六、Invisible Index（不可见索引）

调试利器：索引对优化器不可见，但不删除。可以安全验证"删了这个索引会不会出问题"。

-- 设为不可见，优化器不再使用
ALTER INDEX idx_old ON orders INVISIBLE;

-- 观察几天，没有慢查询 → 安全删除
DROP INDEX idx_old ON orders;

-- 如果出问题了，秒级恢复
ALTER INDEX idx_old ON orders VISIBLE;

七、角色（Roles）

权限管理更方便，不用逐个用户授权。

-- 创建角色
CREATE ROLE 'app_read', 'app_write';

-- 给角色授权
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

-- 给用户赋予角色
GRANT 'app_read' TO 'user1'@'%';
GRANT 'app_read', 'app_write' TO 'admin1'@'%';

-- 用户激活角色
SET ROLE 'app_read';

八、更安全的 DDL（Instant ALTER TABLE）

8.0 之前 ALTER TABLE 可能锁表很久。8.0 引入 ALGORITHM=INSTANT，只修改元数据，不重建表：

-- 秒级完成，不锁表
ALTER TABLE users ADD COLUMN nickname VARCHAR(50), ALGORITHM=INSTANT;

版本	INSTANT 支持的操作
8.0.12	ADD COLUMN（末尾）
8.0.29	ADD/DROP COLUMN、修改列默认值、修改 VARCHAR 长度等

九、JSON 增强

-- JSON_TABLE：把 JSON 数组展开成关系表
SELECT *
FROM orders,
     JSON_TABLE(items, '$[*]' COLUMNS (
         sku VARCHAR(50) PATH '$.sku',
         qty INT PATH '$.qty',
         price DECIMAL(10,2) PATH '$.price'
     )) AS t;

-- JSON_MERGE_PATCH / JSON_MERGE_PRESERVE
SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"b":3,"c":4}');
-- 结果: {"a": 1, "b": 3, "c": 4}

-- ->> 操作符（等价于 JSON_UNQUOTE(JSON_EXTRACT())）
SELECT info->>'$.name' FROM users;

十、其他重要特性

特性	说明
默认认证插件改为 caching_sha2_password	更安全，但旧客户端可能连不上，需加 `default_authentication_plugin=mysql_native_password`
DDL 日志化	DDL 操作写入日志，崩溃后可恢复
通用表空间	多张表共享一个 .ibd 文件，减少文件数
自增列持久化	重启后自增值不再重置，不会产生主键冲突
资源组（Resource Groups）	按线程分配 CPU 资源，OLTP/OLAP 隔离
Undo 表空间独立	不再放在系统表空间，可在线回收空间
更细粒度的权限控制	新增 30+ 权限项，如 `BACKUP_ADMIN`、`CONNECTION_ADMIN`
binlog 日志加密	支持对 binlog 加密存储
持久化全局变量	`SET PERSIST` 重启不丢失
正则增强	支持 ICU 正则，新增 `REGEXP_LIKE()`、`REGEXP_INSTR()`、`REGEXP_SUBSTR()`

-- 自增持久化（8.0 之前重启可能主键冲突）
CREATE TABLE t (id BIGINT AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t VALUES (NULL);  -- id=100
DELETE FROM t WHERE id=100;
-- 8.0 之前：重启后下一条 id=1（回退到最大值+1）
-- 8.0：下一条 id=101（持久化）

-- SET PERSIST：修改全局变量并持久化
SET PERSIST max_connections = 500;  -- 重启后仍然生效

实际开发中最常用的 TOP 5

排名	特性	使用频率	场景
1	窗口函数	极高	排名、累计、分组TopN
2	SKIP LOCKED	高	并发任务消费、防止锁等待
3	CTE 递归	高	组织树、菜单树、层级查询
4	Instant DDL	高	线上加字段不锁表
5	函数索引	中	JSON 字段查询优化

总结：MySQL 8.0 最核心的提升是分析能力（窗口函数/CTE）和并发能力（SKIP LOCKED/NOWAIT），直接减少了需要用应用层代码或 Redis 弥补的场景。

posted @ 2026-05-29 17:10 Nervermore10086 阅读(47) 评论(0) 收藏举报

刷新页面返回顶部

Nervermore10086

MySQL 8.0 新特性全览

MySQL 8.0 新特性全览

一、窗口函数（Window Functions）

二、通用表表达式 CTE（Common Table Expressions）

非递归 CTE

递归 CTE（层次查询）

三、SKIP LOCKED 和 NOWAIT

四、函数索引（Functional Indexes）

五、降序索引（Descending Indexes）

六、Invisible Index（不可见索引）

七、角色（Roles）

八、更安全的 DDL（Instant ALTER TABLE）

九、JSON 增强

十、其他重要特性

实际开发中最常用的 TOP 5

公告