关键词：MySQL8, 新特性, 隐藏索引, 窗口函数, CTE, 降序索引, 函数索引, 原子DDL
MySQL 5.7将于2023年10月31日停止支持，官方不再进行代码维护。MySQL 8.0全内存访问可轻易跑到200W QPS，I/O极端高负载场景跑到16W QPS，性能提升显著。除此之外，MySQL 8还新增了大量实用功能。本文将从账户安全、索引增强、通用表表达式、窗口函数等多个维度，全面解析MySQL 8.0的新特性，帮助你顺利升级到MySQL 8。
目录
- MySQL 8.0简介
- 账户与安全增强
- 用户创建和授权分离
- 认证插件更新
- 密码管理
- 索引增强
- 隐藏索引
- 降序索引
- 函数索引
- 通用表表达式（CTE）
- 窗口函数
- 原子DDL操作
- JSON增强
- InnoDB其他改进
1. MySQL 8.0简介
MySQL 5.7生命周期结束
- MySQL 5.7将于2023年10月31日停止支持
- 官方不再进行后续代码维护
- 建议升级到MySQL 8.0以获得持续支持
MySQL 8.0性能提升
| 场景 | QPS性能 |
|---|---|
| 全内存访问 | 轻易跑到200W QPS |
| I/O极端高负载场景 | 跑到16W QPS |
2. 账户与安全增强
2.1 用户创建和授权分离
MySQL 5.7及之前版本：
-- 用户创建和授权可以一起执行GRANTALLPRIVILEGESON*.*TO'lijin'@'%'IDENTIFIEDBY'Lijin@2022';MySQL 8.0版本：
-- 用户创建和授权必须分开执行-- 步骤1：创建用户CREATEUSER'lijin'@'%'IDENTIFIEDBY'Lijin@2022';-- 步骤2：授权GRANTALLPRIVILEGESON*.*TO'lijin'@'%';注意：MySQL 8.0中不能将用户创建和授权语句合并执行。
2.2 认证插件更新
MySQL 8.0默认认证插件变更：
| 版本 | 默认认证插件 |
|---|---|
| MySQL 5.7 | mysql_native_password |
| MySQL 8.0 | caching_sha2_password |
查看当前认证插件：
SHOWVARIABLESLIKE'default_authentication%';-- 查看所有用户的认证插件SELECTuser,host,pluginFROMmysql.user;兼容性问题：
- 如果客户端没有更新，可能连接不上MySQL 8.0
- 老版本的Navicat等工具可能无法正常连接
解决方案1：修改配置文件（需要重启）
# my.cnf [mysqld] default_authentication_plugin=mysql_native_password解决方案2：动态修改用户认证方式（无需重启）
-- 修改指定用户的认证插件ALTERUSER'lijin'@'%'IDENTIFIEDWITHmysql_native_passwordBY'Lijin@2022';2.3 密码管理
MySQL 8.0开始允许限制重复使用以前的密码，并加入了密码修改管理功能。
查看密码相关变量：
SHOWVARIABLESLIKE'password%';密码历史设置：
| 变量名 | 说明 |
|---|---|
password_history | 不能和最近N次密码一致 |
password_reuse_interval | 按照天数限制密码重复使用 |
password_require_current | 是否需要校验旧密码（OFF不校验，ON校验） |
全局级设置：
-- 修改密码不能和最近3次一致SETPERSIST password_history=3;用户级设置：
-- 为指定用户设置密码历史策略ALTERUSER'lijin'@'%'PASSWORD HISTORY3;-- 查看用户的密码历史设置SELECTuser,host,Password_reuse_historyFROMmysql.user;要求校验旧密码（针对非root用户）：
SETPERSIST password_require_current=ON;3. 索引增强
3.1 隐藏索引
概念：MySQL 8.0开始支持隐藏索引（invisible index），隐藏索引不会被优化器使用，但仍然需要进行维护。
应用场景：
软删除
- 线上经常删除和创建索引，如果删除了发现删错了，又需要重新创建
- 先把索引变成隐藏索引（查询优化器用不上）
- 最后确定要删除时再进行删除操作
灰度发布
- 想在线上进行测试，先创建一个隐藏索引，不影响当前生产环境
- 通过测试发现索引没问题，直接把隐藏索引改成正式索引
创建隐藏索引：
-- 创建表CREATETABLEt1(iINT,jINT);-- 创建正常索引CREATEINDEXi_idxONt1(i);-- 创建隐藏索引CREATEINDEXj_idxONt1(j)INVISIBLE;查看索引信息：
SHOWINDEXFROMt1\G测试隐藏索引：
-- 查看查询是否使用索引EXPLAINSELECT*FROMt1WHEREi=1;-- 会使用i_idxEXPLAINSELECT*FROMt1WHEREj=1;-- 不会使用j_idx（隐藏索引）让优化器看到隐藏索引（会话级别）：
-- 查看优化器参数SELECT@@optimizer_switch\G;-- 开启隐藏索引可见SETSESSIONoptimizer_switch='use_invisible_indexes=on';切换索引可见性：
-- 隐藏索引变为可见ALTERTABLEt1ALTERINDEXj_idx VISIBLE;-- 正常索引变为隐藏ALTERTABLEt1ALTERINDEXj_idx INVISIBLE;限制：不能把主键设置成隐藏索引（MySQL做了限制）。
3.2 降序索引
MySQL 8.0开始真正支持降序索引（descending index）。
特点：
- 只有InnoDB存储引擎支持降序索引
- 只支持BTREE降序索引
- MySQL 8.0不再对GROUP BY操作进行隐式排序
创建降序索引：
CREATETABLEt2(c1INT,c2INT,INDEXidx1(c1ASC,c2DESC)-- c1升序，c2降序);查看表结构：
SHOWCREATETABLEt2\GMySQL 8.0会显示升序/降序信息，而5.7不会显示。
插入测试数据：
INSERTINTOt2(c1,c2)VALUES(1,100),(2,200),(3,150),(4,50);使用降序索引查询：
-- 会使用索引（不需要额外排序）EXPLAINSELECT*FROMt2ORDERBYc1,c2DESC;-- MySQL 5.7需要额外的排序操作-- MySQL 8.0可以直接使用索引GROUP BY不再隐式排序：
MySQL 8.0中GROUP BY不再默认排序：
-- 8.0版本：不会自动排序SELECTCOUNT(*),c2FROMt2GROUPBYc2;-- 需要手动添加ORDER BYSELECTCOUNT(*),c2FROMt2GROUPBYc2ORDERBYc2;3.3 函数索引
问题背景：如果在查询中加入了函数，普通索引不会生效。
MySQL 8.0.13开始支持在索引中使用函数（表达式）的值，支持JSON数据的索引。
函数索引基于虚拟列功能实现。
创建函数索引（表达式）：
-- 创建表CREATETABLEt3(c1VARCHAR(10),c2VARCHAR(10));-- 创建普通索引CREATEINDEXidx_c1ONt3(c1);-- 创建函数索引（大写转换）CREATEINDEXfunc_idxONt3((UPPER(c2)));测试函数索引：
-- 普通索引不会生效（使用了函数）EXPLAINSELECT*FROMt3WHEREUPPER(c1)='ABC';-- 函数索引会生效EXPLAINSELECT*FROMt3WHEREUPPER(c2)='ABC';创建函数索引（JSON）：
-- 创建JSON函数索引CREATETABLEt4(dataJSON,INDEX((CAST(data->>'$.name'ASCHAR(25)))));-- 查询使用函数索引EXPLAINSELECT*FROMt4WHERECAST(data->>'$.name'ASCHAR(25))='lijin';原理：函数索引相当于新增了一个列，这个列根据函数进行计算，然后使用计算后的列作为索引。
4. 通用表表达式（CTE）
MySQL 8.0开始支持通用表表达式（Common Table Expression，CTE），即WITH子句。
简单入门示例
WITHRECURSIVE cte(n)AS(SELECT1UNIONALLSELECTn+1FROMcteWHEREn<10)SELECT*FROMcte;结果：返回1到10的数字。
执行过程：
- 首先执行
SELECT 1，得到结果n=1 - 把n=1送入UNION ALL下面的
SELECT n+1 FROM cte WHERE n < 10 - 递归调用，直到n >= 10
递归CTE实际案例
场景：查询员工的上下级关系
表结构：
CREATETABLEstaff(idINT,nameVARCHAR(50),m_idINT-- 上级ID);递归CTE查询上下级关系：
WITHRECURSIVE staff_view(id,name,m_id)AS(-- 递归起始：查询顶级员工（m_id=0）SELECTid,name,CAST(idASCHAR(200))FROMstaffWHEREm_id=0UNIONALL-- 递归部分：查询下级员工SELECTs2.id,s2.name,CONCAT(s1.m_id,'-',s2.id)FROMstaff_viewASs1JOINstaffASs2ONs1.id=s2.m_id)SELECT*FROMstaff_viewORDERBYid;优势：
- 上下级层级有4、5、6甚至更多层，都可以帮助遍历出来
- 老的方式需要写复杂的SQL，递归CTE更加简洁
CTE总结
- CTE类似于派生表，就像语句级别的临时表或视图
- CTE可以在查询中多次引用
- CTE可以引用其他CTE
- CTE支持递归
- CTE支持SELECT/INSERT/UPDATE/DELETE等语句
5. 窗口函数
MySQL 8.0支持窗口函数（Window Function），也称分析函数。
窗口函数与分组聚合函数类似，但每一行数据都生成一个结果。
聚合窗口函数
普通分组聚合（以国家统计）：
SELECTcountry,SUM(sum)FROMsalesGROUPBYcountryORDERBYcountry;窗口函数聚合（以国家汇总，保留所有行）：
SELECTyear,country,product,sum,SUM(sum)OVER(PARTITIONBYcountry)AScountry_sumFROMsalesORDERBYcountry,year,product,sum;计算平均值：
SELECTyear,country,product,sum,SUM(sum)OVER(PARTITIONBYcountry)AScountry_sum,AVG(sum)OVER(PARTITIONBYcountry)AScountry_avgFROMsalesORDERBYcountry,year,product,sum;专用窗口函数
| 类型 | 函数 |
|---|---|
| 序号函数 | ROW_NUMBER()、RANK()、DENSE_RANK() |
| 分布函数 | PERCENT_RANK()、CUME_DIST() |
| 前后函数 | LAG()、LEAD() |
| 头尾函数 | FIRST_VALUE()、LAST_VALUE() |
| 其他函数 | NTH_VALUE()、NTILE() |
排名示例
SELECTYEAR,country,product,sum,ROW_NUMBER()OVER(ORDERBYsum)AS'rank',RANK()OVER(ORDERBYsum)AS'rank_1'FROMsales;ROW_NUMBER() vs RANK()区别：
- ROW_NUMBER()：连续排名，1、2、3、4…
- RANK()：跳跃排名，1、1、3、4…（相同值排名相同，跳过后续序号）
累计求和示例
SELECTYEAR,country,product,sum,SUM(sum)OVER(PARTITIONBYcountryORDERBYsumROWSUNBOUNDEDPRECEDING)ASsum_1FROMsalesORDERBYcountry,sum;6. 原子DDL操作
MySQL 8.0开始支持原子DDL操作，与表相关的原子DDL只支持InnoDB存储引擎。
原子DDL操作内容：
- 更新数据字典
- 存储引擎层的操作
- 在binlog中记录DDL操作
支持的DDL：
| 对象 | 操作 |
|---|---|
| 数据库 | CREATE、ALTER、DROP |
| 表空间 | CREATE、ALTER、DROP |
| 表 | CREATE、ALTER、DROP、TRUNCATE TABLE |
| 索引 | CREATE、ALTER、DROP |
| 存储程序 | CREATE、ALTER、DROP |
| 触发器 | CREATE、ALTER、DROP |
| 视图 | CREATE、ALTER、DROP |
| UDF | CREATE、ALTER、DROP |
| 用户和角色 | CREATE、ALTER、DROP、RENAME |
| 权限 | GRANT、REVOKE |
原子DDL示例
DROPTABLEt1,t2;场景：只有t1表，没有t2表
| 版本 | 表现 |
|---|---|
| MySQL 5.7 | 删除t1表，报错（非原子操作） |
| MySQL 8.0 | 报错，不会删除t1表（原子操作） |
原子性保证：要么全部成功，要么全部失败。
7. JSON增强
MySQL 8.0对JSON数据类型进行了大量增强。
官方文档：MySQL 8.0 JSON Data Type
主要增强：
- JSON数据类型的性能优化
- 新增的JSON函数
- JSON路径语法增强
- 排序和比较改进
8. InnoDB其他改进
8.1 自增列持久化
问题：MySQL 5.7及早期版本，InnoDB自增列计数器（AUTO_INCREMENT）的值只存储在内存中。
MySQL 8.0改进：
- 每次变化时将自增计数器的最大值写入redo log
- 每次检查点时将其写入引擎私有的系统表
- 解决了长期以来的自增字段值可能重复的bug
8.2 死锁检查控制
MySQL 8.0（MySQL 5.7.15）增加了新的动态变量，用于控制系统是否执行InnoDB死锁检查。
-- 查看死锁检查设置SHOWVARIABLESLIKE'innodb_deadlock_detect';适用场景：对于高并发的系统，禁用死锁检查可能带来性能提高。
8.3 锁定语句选项
SELECT … FOR SHARE 和 SELECT … FOR UPDATE 中支持 NOWAIT、SKIP LOCKED 选项。
| 选项 | 说明 |
|---|---|
| NOWAIT | 如果请求的行被其他事务锁定，语句立即返回 |
| SKIP LOCKED | 从返回的结果集中移除被锁定的行 |
示例：
-- 立即返回，不等待锁SELECT*FROMt1WHEREid=1FORUPDATENOWAIT;-- 跳过被锁定的行SELECT*FROMt1WHEREstatus='pending'FORUPDATESKIP LOCKED;8.4 其他改进
| 特性 | 说明 |
|---|---|
| 部分快速DDL | ALTER TABLE ALGORITHM=INSTANT |
| 临时表空间 | InnoDB临时表使用共享的临时表空间ibtmp1 |
| 自动配置 | innodb_dedicated_server自动配置InnoDB内存参数 |
| UNDO表空间 | 默认创建2个UNDO表空间，不再使用系统表空间 |
| 重命名表空间 | 支持ALTER TABLESPACE … RENAME TO |
总结
本文全面介绍了MySQL 8.0的新特性，帮助你顺利升级：
核心新特性：
账户与安全：
- 用户创建和授权必须分开执行
- 默认认证插件变为caching_sha2_password
- 支持密码历史管理和重复使用限制
索引增强：
- 隐藏索引：支持灰度发布和软删除
- 降序索引：真正支持DESC索引，优化排序性能
- 函数索引：支持表达式和JSON路径索引
CTE通用表表达式：
- 支持递归CTE
- 简化层级查询（如组织架构、树形结构）
窗口函数：
- 聚合窗口函数：SUM、AVG、COUNT等
- 专用窗口函数：ROW_NUMBER、RANK、LAG、LEAD等
原子DDL：
- DDL操作要么全部成功，要么全部失败
- 避免部分执行导致的数据不一致
InnoDB改进：
- 自增列持久化，解决重复问题
- 死锁检查可控
- 锁定语句支持NOWAIT和SKIP LOCKED
升级建议：
- MySQL 5.7已停止支持，建议尽快升级到8.0
- 升级前测试应用兼容性（特别是认证插件）
- 利用新特性优化查询性能（窗口函数、CTE、函数索引）
- 使用隐藏索引进行灰度测试
面试高频问题：
- MySQL 8.0有哪些新特性？（隐藏索引、窗口函数、CTE、原子DDL等）
- 隐藏索引有什么作用？（灰度发布、软删除）
- 什么是窗口函数？和普通聚合函数有什么区别？（每行都返回结果）
- 什么是原子DDL？有什么好处？（DDL操作原子性）
- MySQL 8.0默认认证插件是什么？（caching_sha2_password）
希望这篇文章能帮助你全面了解MySQL 8.0新特性！如果觉得有帮助，欢迎点赞、收藏、关注～
推荐标签：
- MySQL8
- 新特性
- 隐藏索引
- 窗口函数
- CTE
- 降序索引
- 原子DDL
- 面试
