关键词MySQL8, 新特性, 隐藏索引, 窗口函数, CTE, 降序索引, 函数索引, 原子DDLMySQL 5.7将于2023年10月31日停止支持官方不再进行代码维护。MySQL 8.0全内存访问可轻易跑到200W QPSI/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 QPSI/O极端高负载场景跑到16W QPS2. 账户与安全增强2.1 用户创建和授权分离MySQL 5.7及之前版本-- 用户创建和授权可以一起执行GRANTALLPRIVILEGESON*.*TOlijin%IDENTIFIEDBYLijin2022;MySQL 8.0版本-- 用户创建和授权必须分开执行-- 步骤1创建用户CREATEUSERlijin%IDENTIFIEDBYLijin2022;-- 步骤2授权GRANTALLPRIVILEGESON*.*TOlijin%;注意MySQL 8.0中不能将用户创建和授权语句合并执行。2.2 认证插件更新MySQL 8.0默认认证插件变更版本默认认证插件MySQL 5.7mysql_native_passwordMySQL 8.0caching_sha2_password查看当前认证插件SHOWVARIABLESLIKEdefault_authentication%;-- 查看所有用户的认证插件SELECTuser,host,pluginFROMmysql.user;兼容性问题如果客户端没有更新可能连接不上MySQL 8.0老版本的Navicat等工具可能无法正常连接解决方案1修改配置文件需要重启# my.cnf [mysqld] default_authentication_pluginmysql_native_password解决方案2动态修改用户认证方式无需重启-- 修改指定用户的认证插件ALTERUSERlijin%IDENTIFIEDWITHmysql_native_passwordBYLijin2022;2.3 密码管理MySQL 8.0开始允许限制重复使用以前的密码并加入了密码修改管理功能。查看密码相关变量SHOWVARIABLESLIKEpassword%;密码历史设置变量名说明password_history不能和最近N次密码一致password_reuse_interval按照天数限制密码重复使用password_require_current是否需要校验旧密码OFF不校验ON校验全局级设置-- 修改密码不能和最近3次一致SETPERSIST password_history3;用户级设置-- 为指定用户设置密码历史策略ALTERUSERlijin%PASSWORD HISTORY3;-- 查看用户的密码历史设置SELECTuser,host,Password_reuse_historyFROMmysql.user;要求校验旧密码针对非root用户SETPERSIST password_require_currentON;3. 索引增强3.1 隐藏索引概念MySQL 8.0开始支持隐藏索引invisible index隐藏索引不会被优化器使用但仍然需要进行维护。应用场景软删除线上经常删除和创建索引如果删除了发现删错了又需要重新创建先把索引变成隐藏索引查询优化器用不上最后确定要删除时再进行删除操作灰度发布想在线上进行测试先创建一个隐藏索引不影响当前生产环境通过测试发现索引没问题直接把隐藏索引改成正式索引创建隐藏索引-- 创建表CREATETABLEt1(iINT,jINT);-- 创建正常索引CREATEINDEXi_idxONt1(i);-- 创建隐藏索引CREATEINDEXj_idxONt1(j)INVISIBLE;查看索引信息SHOWINDEXFROMt1\G测试隐藏索引-- 查看查询是否使用索引EXPLAINSELECT*FROMt1WHEREi1;-- 会使用i_idxEXPLAINSELECT*FROMt1WHEREj1;-- 不会使用j_idx隐藏索引让优化器看到隐藏索引会话级别-- 查看优化器参数SELECToptimizer_switch\G;-- 开启隐藏索引可见SETSESSIONoptimizer_switchuse_invisible_indexeson;切换索引可见性-- 隐藏索引变为可见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-$.nameASCHAR(25)))));-- 查询使用函数索引EXPLAINSELECT*FROMt4WHERECAST(data-$.nameASCHAR(25))lijin;原理函数索引相当于新增了一个列这个列根据函数进行计算然后使用计算后的列作为索引。4. 通用表表达式CTEMySQL 8.0开始支持通用表表达式Common Table ExpressionCTE即WITH子句。简单入门示例WITHRECURSIVE cte(n)AS(SELECT1UNIONALLSELECTn1FROMcteWHEREn10)SELECT*FROMcte;结果返回1到10的数字。执行过程首先执行SELECT 1得到结果n1把n1送入UNION ALL下面的SELECT n1 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_id0SELECTid,name,CAST(idASCHAR(200))FROMstaffWHEREm_id0UNIONALL-- 递归部分查询下级员工SELECTs2.id,s2.name,CONCAT(s1.m_id,-,s2.id)FROMstaff_viewASs1JOINstaffASs2ONs1.ids2.m_id)SELECT*FROMstaff_viewORDERBYid;优势上下级层级有4、5、6甚至更多层都可以帮助遍历出来老的方式需要写复杂的SQL递归CTE更加简洁CTE总结CTE类似于派生表就像语句级别的临时表或视图CTE可以在查询中多次引用CTE可以引用其他CTECTE支持递归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)ASrank,RANK()OVER(ORDERBYsum)ASrank_1FROMsales;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、DROPUDFCREATE、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每次检查点时将其写入引擎私有的系统表解决了长期以来的自增字段值可能重复的bug8.2 死锁检查控制MySQL 8.0MySQL 5.7.15增加了新的动态变量用于控制系统是否执行InnoDB死锁检查。-- 查看死锁检查设置SHOWVARIABLESLIKEinnodb_deadlock_detect;适用场景对于高并发的系统禁用死锁检查可能带来性能提高。8.3 锁定语句选项SELECT … FOR SHARE 和 SELECT … FOR UPDATE 中支持 NOWAIT、SKIP LOCKED 选项。选项说明NOWAIT如果请求的行被其他事务锁定语句立即返回SKIP LOCKED从返回的结果集中移除被锁定的行示例-- 立即返回不等待锁SELECT*FROMt1WHEREid1FORUPDATENOWAIT;-- 跳过被锁定的行SELECT*FROMt1WHEREstatuspendingFORUPDATESKIP LOCKED;8.4 其他改进特性说明部分快速DDLALTER TABLE ALGORITHMINSTANT临时表空间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等原子DDLDDL操作要么全部成功要么全部失败避免部分执行导致的数据不一致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面试
MySQL 8.0新特性详解：从隐藏索引到窗口函数全面解析
关键词MySQL8, 新特性, 隐藏索引, 窗口函数, CTE, 降序索引, 函数索引, 原子DDLMySQL 5.7将于2023年10月31日停止支持官方不再进行代码维护。MySQL 8.0全内存访问可轻易跑到200W QPSI/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 QPSI/O极端高负载场景跑到16W QPS2. 账户与安全增强2.1 用户创建和授权分离MySQL 5.7及之前版本-- 用户创建和授权可以一起执行GRANTALLPRIVILEGESON*.*TOlijin%IDENTIFIEDBYLijin2022;MySQL 8.0版本-- 用户创建和授权必须分开执行-- 步骤1创建用户CREATEUSERlijin%IDENTIFIEDBYLijin2022;-- 步骤2授权GRANTALLPRIVILEGESON*.*TOlijin%;注意MySQL 8.0中不能将用户创建和授权语句合并执行。2.2 认证插件更新MySQL 8.0默认认证插件变更版本默认认证插件MySQL 5.7mysql_native_passwordMySQL 8.0caching_sha2_password查看当前认证插件SHOWVARIABLESLIKEdefault_authentication%;-- 查看所有用户的认证插件SELECTuser,host,pluginFROMmysql.user;兼容性问题如果客户端没有更新可能连接不上MySQL 8.0老版本的Navicat等工具可能无法正常连接解决方案1修改配置文件需要重启# my.cnf [mysqld] default_authentication_pluginmysql_native_password解决方案2动态修改用户认证方式无需重启-- 修改指定用户的认证插件ALTERUSERlijin%IDENTIFIEDWITHmysql_native_passwordBYLijin2022;2.3 密码管理MySQL 8.0开始允许限制重复使用以前的密码并加入了密码修改管理功能。查看密码相关变量SHOWVARIABLESLIKEpassword%;密码历史设置变量名说明password_history不能和最近N次密码一致password_reuse_interval按照天数限制密码重复使用password_require_current是否需要校验旧密码OFF不校验ON校验全局级设置-- 修改密码不能和最近3次一致SETPERSIST password_history3;用户级设置-- 为指定用户设置密码历史策略ALTERUSERlijin%PASSWORD HISTORY3;-- 查看用户的密码历史设置SELECTuser,host,Password_reuse_historyFROMmysql.user;要求校验旧密码针对非root用户SETPERSIST password_require_currentON;3. 索引增强3.1 隐藏索引概念MySQL 8.0开始支持隐藏索引invisible index隐藏索引不会被优化器使用但仍然需要进行维护。应用场景软删除线上经常删除和创建索引如果删除了发现删错了又需要重新创建先把索引变成隐藏索引查询优化器用不上最后确定要删除时再进行删除操作灰度发布想在线上进行测试先创建一个隐藏索引不影响当前生产环境通过测试发现索引没问题直接把隐藏索引改成正式索引创建隐藏索引-- 创建表CREATETABLEt1(iINT,jINT);-- 创建正常索引CREATEINDEXi_idxONt1(i);-- 创建隐藏索引CREATEINDEXj_idxONt1(j)INVISIBLE;查看索引信息SHOWINDEXFROMt1\G测试隐藏索引-- 查看查询是否使用索引EXPLAINSELECT*FROMt1WHEREi1;-- 会使用i_idxEXPLAINSELECT*FROMt1WHEREj1;-- 不会使用j_idx隐藏索引让优化器看到隐藏索引会话级别-- 查看优化器参数SELECToptimizer_switch\G;-- 开启隐藏索引可见SETSESSIONoptimizer_switchuse_invisible_indexeson;切换索引可见性-- 隐藏索引变为可见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-$.nameASCHAR(25)))));-- 查询使用函数索引EXPLAINSELECT*FROMt4WHERECAST(data-$.nameASCHAR(25))lijin;原理函数索引相当于新增了一个列这个列根据函数进行计算然后使用计算后的列作为索引。4. 通用表表达式CTEMySQL 8.0开始支持通用表表达式Common Table ExpressionCTE即WITH子句。简单入门示例WITHRECURSIVE cte(n)AS(SELECT1UNIONALLSELECTn1FROMcteWHEREn10)SELECT*FROMcte;结果返回1到10的数字。执行过程首先执行SELECT 1得到结果n1把n1送入UNION ALL下面的SELECT n1 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_id0SELECTid,name,CAST(idASCHAR(200))FROMstaffWHEREm_id0UNIONALL-- 递归部分查询下级员工SELECTs2.id,s2.name,CONCAT(s1.m_id,-,s2.id)FROMstaff_viewASs1JOINstaffASs2ONs1.ids2.m_id)SELECT*FROMstaff_viewORDERBYid;优势上下级层级有4、5、6甚至更多层都可以帮助遍历出来老的方式需要写复杂的SQL递归CTE更加简洁CTE总结CTE类似于派生表就像语句级别的临时表或视图CTE可以在查询中多次引用CTE可以引用其他CTECTE支持递归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)ASrank,RANK()OVER(ORDERBYsum)ASrank_1FROMsales;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、DROPUDFCREATE、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每次检查点时将其写入引擎私有的系统表解决了长期以来的自增字段值可能重复的bug8.2 死锁检查控制MySQL 8.0MySQL 5.7.15增加了新的动态变量用于控制系统是否执行InnoDB死锁检查。-- 查看死锁检查设置SHOWVARIABLESLIKEinnodb_deadlock_detect;适用场景对于高并发的系统禁用死锁检查可能带来性能提高。8.3 锁定语句选项SELECT … FOR SHARE 和 SELECT … FOR UPDATE 中支持 NOWAIT、SKIP LOCKED 选项。选项说明NOWAIT如果请求的行被其他事务锁定语句立即返回SKIP LOCKED从返回的结果集中移除被锁定的行示例-- 立即返回不等待锁SELECT*FROMt1WHEREid1FORUPDATENOWAIT;-- 跳过被锁定的行SELECT*FROMt1WHEREstatuspendingFORUPDATESKIP LOCKED;8.4 其他改进特性说明部分快速DDLALTER TABLE ALGORITHMINSTANT临时表空间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等原子DDLDDL操作要么全部成功要么全部失败避免部分执行导致的数据不一致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面试
