摘要MySQL 8.0 是 MySQL 历史上最重要的版本升级之一带来了性能、安全、开发效率的全方位提升。本文精选 10 大核心新特性窗口函数、CTE 公用表表达式、降序索引、不可见索引、原子 DDL、角色权限管理、JSON 增强、直方图统计、默认字符集升级、以及全新的 MySQL Shell。每个特性都配有实战示例帮你快速上手 MySQL 8.0。一、MySQL 8.0 概览为什么值得升级MySQL 8.0 于 2018 年 4 月发布 GA 版本是时隔 4 年的重大升级上一版 5.7 发布于 2015 年。官方宣称性能提升2 倍并引入了 100 项新特性。上图展示了 MySQL 8.0 的核心架构不仅支持传统的关系型表Relational Tables还通过 X Protocol 原生支持 JSON 文档存储JSON Collections实现 SQL NoSQL 双模能力。升级前的注意事项MySQL 8.0 要求数据字典升级不支持直接从 5.5/5.6 升级必须先升级到 5.7默认认证插件从mysql_native_password改为caching_sha2_password旧客户端可能连不上部分废弃特性已移除如query_cache、mysql_install_db二、特性 1窗口函数Window Functions2.1 什么是窗口函数窗口函数是 SQL 2003 标准的一部分MySQL 8.0 终于补齐了这个短板。它可以在不改变行数的情况下对一组相关的行进行计算。与传统聚合函数的区别特性聚合函数GROUP BY窗口函数输出行数每组一行压缩每行都保留计算范围整组数据滑动窗口可自定义范围典型用途汇总统计排名、累计、移动平均2.2 常用窗口函数-- 假设表结构CREATETABLEsales(idINTPRIMARYKEY,productVARCHAR(50),regionVARCHAR(50),amountDECIMAL(18,2),sale_dateDATE);-- 1. ROW_NUMBER()为每组内的行分配唯一序号SELECTproduct,region,amount,ROW_NUMBER()OVER(PARTITIONBYregionORDERBYamountDESC)ASrank_in_regionFROMsales;-- 结果每个 region 内按 amount 降序排名1, 2, 3...-- 2. RANK() / DENSE_RANK()排名允许并列SELECTproduct,amount,RANK()OVER(ORDERBYamountDESC)ASrank,-- 并列跳号1, 1, 3DENSE_RANK()OVER(ORDERBYamountDESC)ASdense_rank-- 并列不跳号1, 1, 2FROMsales;-- 3. LAG() / LEAD()访问前/后行的数据SELECTproduct,amount,LAG(amount,1)OVER(ORDERBYsale_date)ASprev_amount,-- 上一行的 amountLEAD(amount,1)OVER(ORDERBYsale_date)ASnext_amount-- 下一行的 amountFROMsales;-- 4. SUM() OVER累计求和SELECTsale_date,amount,SUM(amount)OVER(ORDERBYsale_date)AScumulative_sum,-- 从第一行累加到当前行SUM(amount)OVER(ORDERBYsale_dateROWSBETWEEN6PRECEDINGANDCURRENTROW)ASmoving_7dFROMsales;-- 5. NTILE()将数据分为 N 组分位数SELECTproduct,amount,NTILE(4)OVER(ORDERBYamountDESC)ASquartile-- 按 amount 分为 4 组FROMsales;2.3 窗口函数实战TOP N 问题-- 问题找出每个区域销售额 TOP 3 的产品-- MySQL 5.7 的笨办法子查询 变量-- MySQL 8.0 的优雅写法SELECT*FROM(SELECTproduct,region,amount,ROW_NUMBER()OVER(PARTITIONBYregionORDERBYamountDESC)ASrnFROMsales)tWHERErn3;三、特性 2CTE 公用表表达式Common Table Expressions3.1 普通 CTECTE 允许定义临时结果集在后续 SELECT 中多次引用使复杂查询更易读。-- 定义 CTEWITHregional_salesAS(SELECTregion,SUM(amount)AStotal_salesFROMsalesGROUPBYregion),top_regionsAS(SELECTregionFROMregional_salesORDERBYtotal_salesDESCLIMIT3)-- 使用 CTESELECTs.product,s.region,s.amountFROMsales sJOINtop_regions trONs.regiontr.region;3.2 递归 CTE递归 CTE 可以处理树形结构数据如组织架构、分类层级。-- 员工表id, name, manager_idCREATETABLEemployees(idINTPRIMARYKEY,nameVARCHAR(50),manager_idINT,FOREIGNKEY(manager_id)REFERENCESemployees(id));-- 查询某个员工的所有下属递归WITHRECURSIVE subordinatesAS(-- 锚成员找到起始员工SELECTid,name,manager_id,0ASlevelFROMemployeesWHEREid1-- CEOUNIONALL-- 递归成员找到下属的下属SELECTe.id,e.name,e.manager_id,s.level1FROMemployees eJOINsubordinates sONe.manager_ids.id)SELECT*FROMsubordinatesORDERBYlevel,id;四、特性 3降序索引Descending Indexes4.1 为什么需要降序索引在 MySQL 5.7 中虽然可以创建DESC索引但内部实际还是升序存储只是反向扫描。当查询同时包含升序和降序排序时优化器无法完全利用索引。上图展示了 MySQL 8.0 降序索引在 ORDER BY 混合排序场景下的性能提升对于(a DESC, b ASC)这类混合排序查询8.0 的耗时显著低于 5.7。4.2 创建降序索引-- MySQL 8.0 真正支持降序存储CREATEINDEXidx_amount_dateONsales(amountDESC,sale_dateASC);-- 查询可以直接利用索引无需 filesortSELECT*FROMsalesORDERBYamountDESC,sale_dateASC;-- Extra: Using index而不是 Using filesort五、特性 4不可见索引Invisible Indexes5.1 用途DBA 可以临时将索引设为不可见优化器会忽略它但索引仍在后台维护。用于安全地测试删除索引的影响。-- 创建不可见索引CREATEINDEXidx_testONsales(product)INVISIBLE;-- 或将现有索引设为不可见ALTERTABLEsalesALTERINDEXidx_product INVISIBLE;-- 测试查询性能优化器不会使用该索引EXPLAINSELECT*FROMsalesWHEREproductiPhone;-- 如果性能下降随时恢复可见ALTERTABLEsalesALTERINDEXidx_product VISIBLE;5.2 强制使用不可见索引-- 会话级别强制优化器考虑不可见索引SETSESSIONoptimizer_switchuse_invisible_indexeson;六、特性 5原子 DDLAtomic DDL6.1 什么是原子 DDLMySQL 8.0 将 DDL 操作如 CREATE、ALTER、DROP变为原子性操作要么完全成功要么完全回滚不会出现操作一半失败导致数据字典不一致的情况。-- MySQL 5.7 的问题DROP TABLE 中途崩溃可能表文件删了但数据字典记录还在-- MySQL 8.0 的解决DROP TABLE 是原子操作失败会自动回滚-- 示例安全地重命名表RENAMETABLEold_tableTOnew_table;-- 原子操作不会半成功6.2 INSTANT ADD COLUMNMySQL 8.0.12 支持瞬间加列无需复制整张表数据。-- 传统加列MySQL 5.7复制整张表大表可能需要数小时ALTERTABLEbig_tableADDCOLUMNnew_colVARCHAR(50);-- 耗时取决于表大小-- MySQL 8.0 INSTANT 加列修改元数据毫秒级完成ALTERTABLEbig_tableADDCOLUMNnew_colVARCHAR(50),ALGORITHMINSTANT;-- 限制只能加在表尾不能加在中间8.0.29 支持加在中间-- 查看表是否支持 INSTANTSELECTNAME,TOTAL_ROW_VERSIONS,CASEWHENTOTAL_ROW_VERSIONS0THEN支持 INSTANTELSE不支持ENDASinstant_supportFROMinformation_schema.INNODB_TABLESWHERENAMEdb1/big_table;七、特性 6角色权限管理Roles7.1 创建和使用角色MySQL 8.0 引入了角色Role概念可以批量管理权限类似 Linux 的用户组。-- 创建角色CREATEROLEapp_read,app_write,app_admin;-- 给角色授权GRANTSELECTONmydb.*TOapp_read;GRANTSELECT,INSERT,UPDATE,DELETEONmydb.*TOapp_write;GRANTALLPRIVILEGESONmydb.*TOapp_admin;-- 创建用户并赋予角色CREATEUSERdeveloper%IDENTIFIEDBYpassword;GRANTapp_writeTOdeveloper%;-- 创建用户并赋予多个角色CREATEUSERdba%IDENTIFIEDBYpassword;GRANTapp_read,app_write,app_adminTOdba%;-- 用户激活角色默认不自动激活SETDEFAULTROLEapp_writeTOdeveloper%;-- 或用户登录后手动激活SETROLEapp_write;-- 查看当前用户的角色和权限SHOWGRANTS;SHOWGRANTSFORCURRENT_ROLE();7.2 强制角色Mandatory Roles# my.cnf所有用户自动拥有某些角色 [mysqld] mandatory_roles app_read八、特性 7JSON 增强8.1 JSON 数据类型改进MySQL 5.7 已支持 JSON8.0 进一步增强了 JSON 操作能力。-- 创建 JSON 列CREATETABLEusers(idINTPRIMARYKEY,profile JSON,INDEXidx_city((CAST(profile-$.cityASCHAR(50))))-- 函数索引8.0.13);-- 插入 JSON 数据INSERTINTOusersVALUES(1,{name: 张三, age: 25, city: 北京, tags: [程序员, 篮球]});-- JSON 路径查询SELECTid,JSON_EXTRACT(profile,$.name)ASname,profile-$.cityAScity,-- - 是 JSON_EXTRACT UNQUOTE 的简写JSON_CONTAINS(profile,篮球,$.tags)ASlikes_basketballFROMusers;-- JSON 聚合函数SELECTid,JSON_ARRAYAGG(tag)ASall_tags-- 将多行聚合成 JSON 数组FROMuser_tagsGROUPBYid;-- JSON 表函数将 JSON 数组展开为多行SELECTu.id,jt.tagFROMusers u,JSON_TABLE(u.profile,$.tags[*]COLUMNS(tagVARCHAR(50)PATH$))ASjt;8.2 多值索引Multi-Valued IndexesMySQL 8.0.17 支持对 JSON 数组创建索引。-- 为 JSON 数组创建多值索引CREATEINDEXidx_tagsONusers((CAST(profile-$.tagsASCHAR(50)ARRAY)));-- 查询可以利用索引SELECT*FROMusersWHEREJSON_CONTAINS(profile,篮球,$.tags);九、特性 8直方图统计Histograms9.1 为什么需要直方图优化器依赖索引统计信息如 cardinality来估算查询成本。但对于数据分布不均的列如 80% 是 “completed”20% 是 “pending”简单的 cardinality 无法准确反映分布情况导致优化器选择错误的执行计划。上图展示了直方图如何将数据分布划分为多个桶bucket每个桶记录值的频率帮助优化器更准确地估算查询成本。9.2 创建和使用直方图-- 为 status 列创建直方图默认 100 个桶ANALYZETABLEordersUPDATEHISTOGRAMONstatusWITH100BUCKETS;-- 查看直方图信息SELECTCOLUMN_NAME,HISTOGRAM-$.number-of-buckets-specifiedASbuckets,HISTOGRAM-$.data-typeASdata_typeFROMinformation_schema.COLUMN_STATISTICSWHERETABLE_NAMEordersANDCOLUMN_NAMEstatus;-- 删除直方图ANALYZETABLEordersDROPHISTOGRAMONstatus;-- 查看优化器是否使用了直方图EXPLAINANALYZESELECT*FROMordersWHEREstatuspending;-- 如果 rows 估算更准确说明直方图生效十、特性 9默认字符集升级10.1 utf8mb4 成为默认字符集MySQL 8.0 将默认字符集从latin1升级为utf8mb4排序规则升级为utf8mb4_0900_ai_ci。版本默认字符集默认排序规则说明MySQL 5.7latin1latin1_swedish_ci不支持 EmojiMySQL 8.0utf8mb4utf8mb4_0900_ai_ci支持 Emoji、中文、多语言-- 查看默认字符集SHOWVARIABLESLIKEcharacter_set%;-- 创建表时显式指定推荐CREATETABLEcomments(idINTPRIMARYKEY,contentVARCHAR(500))CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;-- 旧表升级到 utf8mb4ALTERTABLEold_tableCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;注意utf8mb4_0900_ai_ci是 MySQL 8.0 新增的排序规则基于 Unicode 9.0比utf8mb4_general_ci更准确但部分旧系统可能不兼容。十一、特性 10MySQL Shell 与 X Protocol11.1 MySQL Shell新一代客户端MySQL Shell 是官方推出的高级客户端支持 JavaScript、Python 和 SQL 三种模式。# 启动 MySQL Shellmysqlsh rootlocalhost:3306# JavaScript 模式默认\js# 执行 SQL\sql SELECT * FROMusersWHEREid1;# 执行 JavaScript 脚本dbsession.getSchema(mydb);tabledb.getTable(users);resulttable.select([id,name]).where(age 18).execute();print(result.fetchAll());# 使用 AdminAPI 管理 MGR 集群\js clusterdba.createCluster(myCluster);cluster.addInstance(rootdb2:3306);cluster.addInstance(rootdb3:3306);cluster.status();11.2 X Protocol 与 Document StoreMySQL 8.0 通过 X Plugin 支持 X Protocol可以直接操作 JSON 文档无需 SQL。// MySQL Shell 中使用 Document Store\js sessionmysqlx.getSession(root:passwordlocalhost:33060);dbsession.getSchema(mydb);// 创建集合类似 MongoDB 的 Collectioncollectiondb.createCollection(products);// 插入文档collection.add({name:iPhone 15,price:5999,tags:[手机,苹果]}).execute();// 查询文档resultcollection.find(price 5000).execute();print(result.fetchAll());// 修改文档collection.modify(name iPhone 15).set(price,5799).execute();十二、其他值得关注的特性特性版本说明持久化配置8.0SET PERSIST将变量修改持久化到mysqld-auto.cnf重启后仍生效资源组Resource Groups8.0限制线程的 CPU 和 IO 使用隔离不同业务的资源倒排索引8.0.13InnoDB 表支持全文倒排索引提升全文检索性能克隆插件Clone Plugin8.0.17快速物理克隆实例用于搭建从库或备份备份锁Backup Lock8.0LOCK INSTANCE FOR BACKUP允许在线备份时阻塞 DDL不阻塞 DMLTempTable 引擎8.0替代 Memory 引擎处理内部临时表支持变长数据类型十三、升级 checklist□ 确认当前版本必须是 5.7不支持 5.5/5.6 直接升级 □ 备份数据mysqldump 或物理备份XtraBackup □ 检查废弃特性移除 query_cache、mysql_install_db 等 □ 检查认证插件旧客户端需升级或改为 mysql_native_password □ 检查 SQL 模式ONLY_FULL_GROUP_BY 等模式更严格 □ 测试环境验证先在测试库跑一遍升级流程 □ 检查性能关键查询用 EXPLAIN 对比执行计划 □ 检查字符集确认 utf8mb4 兼容性 □ 监控升级过程观察错误日志和性能指标十四、面试高频考点速记Q1MySQL 8.0 相比 5.7 有哪些重要改进窗口函数和 CTE 补齐 SQL 标准降序索引和不可见索引优化查询性能原子 DDL 避免数据字典不一致角色权限管理简化权限控制JSON 增强和多值索引支持文档存储直方图统计优化查询计划默认 utf8mb4 支持 EmojiMySQL Shell 和 X Protocol 提供 NoSQL 能力。Q2窗口函数和 GROUP BY 有什么区别GROUP BY 将多行聚合成一行压缩窗口函数保留所有行在每行上计算聚合值。窗口函数通过 OVER 子句定义计算范围PARTITION BY 分组 ORDER BY 排序 ROWS/RANGE 滑动窗口。Q3什么是原子 DDL有什么好处MySQL 8.0 将 DDL 操作变为原子性要么完全成功要么完全回滚。好处是避免操作中途崩溃导致数据字典和文件系统不一致如表文件删了但字典记录还在。同时支持 INSTANT ADD COLUMN大表加列从小时级降到毫秒级。Q4MySQL 8.0 的默认认证插件是什么旧客户端连不上怎么办默认改为caching_sha2_password比mysql_native_password更安全但兼容性差。解决方法1. 升级客户端驱动2. 创建用户时显式指定WITH mysql_native_password3. 修改全局变量default_authentication_plugin。Q5不可见索引有什么用途允许 DBA 临时禁用索引而不删除它用于安全地测试删除索引对查询性能的影响。如果性能下降随时恢复可见无需重新创建索引。Q6MySQL 8.0 的 JSON 多值索引是什么允许对 JSON 数组类型的字段创建索引。例如用户标签是 JSON 数组[程序员, 篮球]可以创建多值索引让JSON_CONTAINS查询走索引而不是全表扫描。结语MySQL 8.0 是一次全面的现代化升级开发效率窗口函数、CTE、JSON 增强让复杂查询更简洁运维安全原子 DDL、不可见索引、角色管理降低运维风险性能优化降序索引、直方图、TempTable 引擎提升查询效率架构扩展MGR、Clone Plugin、MySQL Shell 支持云原生和分布式场景如果你还在用 MySQL 5.7强烈建议制定升级计划。MySQL 5.7 已于2023 年 10 月停止官方支持不再接收安全补丁。如果本文对你有帮助欢迎点赞收藏关注
MySQL 8.0 新特性详解：从窗口函数到原子 DDL，这些功能你必须知道
摘要MySQL 8.0 是 MySQL 历史上最重要的版本升级之一带来了性能、安全、开发效率的全方位提升。本文精选 10 大核心新特性窗口函数、CTE 公用表表达式、降序索引、不可见索引、原子 DDL、角色权限管理、JSON 增强、直方图统计、默认字符集升级、以及全新的 MySQL Shell。每个特性都配有实战示例帮你快速上手 MySQL 8.0。一、MySQL 8.0 概览为什么值得升级MySQL 8.0 于 2018 年 4 月发布 GA 版本是时隔 4 年的重大升级上一版 5.7 发布于 2015 年。官方宣称性能提升2 倍并引入了 100 项新特性。上图展示了 MySQL 8.0 的核心架构不仅支持传统的关系型表Relational Tables还通过 X Protocol 原生支持 JSON 文档存储JSON Collections实现 SQL NoSQL 双模能力。升级前的注意事项MySQL 8.0 要求数据字典升级不支持直接从 5.5/5.6 升级必须先升级到 5.7默认认证插件从mysql_native_password改为caching_sha2_password旧客户端可能连不上部分废弃特性已移除如query_cache、mysql_install_db二、特性 1窗口函数Window Functions2.1 什么是窗口函数窗口函数是 SQL 2003 标准的一部分MySQL 8.0 终于补齐了这个短板。它可以在不改变行数的情况下对一组相关的行进行计算。与传统聚合函数的区别特性聚合函数GROUP BY窗口函数输出行数每组一行压缩每行都保留计算范围整组数据滑动窗口可自定义范围典型用途汇总统计排名、累计、移动平均2.2 常用窗口函数-- 假设表结构CREATETABLEsales(idINTPRIMARYKEY,productVARCHAR(50),regionVARCHAR(50),amountDECIMAL(18,2),sale_dateDATE);-- 1. ROW_NUMBER()为每组内的行分配唯一序号SELECTproduct,region,amount,ROW_NUMBER()OVER(PARTITIONBYregionORDERBYamountDESC)ASrank_in_regionFROMsales;-- 结果每个 region 内按 amount 降序排名1, 2, 3...-- 2. RANK() / DENSE_RANK()排名允许并列SELECTproduct,amount,RANK()OVER(ORDERBYamountDESC)ASrank,-- 并列跳号1, 1, 3DENSE_RANK()OVER(ORDERBYamountDESC)ASdense_rank-- 并列不跳号1, 1, 2FROMsales;-- 3. LAG() / LEAD()访问前/后行的数据SELECTproduct,amount,LAG(amount,1)OVER(ORDERBYsale_date)ASprev_amount,-- 上一行的 amountLEAD(amount,1)OVER(ORDERBYsale_date)ASnext_amount-- 下一行的 amountFROMsales;-- 4. SUM() OVER累计求和SELECTsale_date,amount,SUM(amount)OVER(ORDERBYsale_date)AScumulative_sum,-- 从第一行累加到当前行SUM(amount)OVER(ORDERBYsale_dateROWSBETWEEN6PRECEDINGANDCURRENTROW)ASmoving_7dFROMsales;-- 5. NTILE()将数据分为 N 组分位数SELECTproduct,amount,NTILE(4)OVER(ORDERBYamountDESC)ASquartile-- 按 amount 分为 4 组FROMsales;2.3 窗口函数实战TOP N 问题-- 问题找出每个区域销售额 TOP 3 的产品-- MySQL 5.7 的笨办法子查询 变量-- MySQL 8.0 的优雅写法SELECT*FROM(SELECTproduct,region,amount,ROW_NUMBER()OVER(PARTITIONBYregionORDERBYamountDESC)ASrnFROMsales)tWHERErn3;三、特性 2CTE 公用表表达式Common Table Expressions3.1 普通 CTECTE 允许定义临时结果集在后续 SELECT 中多次引用使复杂查询更易读。-- 定义 CTEWITHregional_salesAS(SELECTregion,SUM(amount)AStotal_salesFROMsalesGROUPBYregion),top_regionsAS(SELECTregionFROMregional_salesORDERBYtotal_salesDESCLIMIT3)-- 使用 CTESELECTs.product,s.region,s.amountFROMsales sJOINtop_regions trONs.regiontr.region;3.2 递归 CTE递归 CTE 可以处理树形结构数据如组织架构、分类层级。-- 员工表id, name, manager_idCREATETABLEemployees(idINTPRIMARYKEY,nameVARCHAR(50),manager_idINT,FOREIGNKEY(manager_id)REFERENCESemployees(id));-- 查询某个员工的所有下属递归WITHRECURSIVE subordinatesAS(-- 锚成员找到起始员工SELECTid,name,manager_id,0ASlevelFROMemployeesWHEREid1-- CEOUNIONALL-- 递归成员找到下属的下属SELECTe.id,e.name,e.manager_id,s.level1FROMemployees eJOINsubordinates sONe.manager_ids.id)SELECT*FROMsubordinatesORDERBYlevel,id;四、特性 3降序索引Descending Indexes4.1 为什么需要降序索引在 MySQL 5.7 中虽然可以创建DESC索引但内部实际还是升序存储只是反向扫描。当查询同时包含升序和降序排序时优化器无法完全利用索引。上图展示了 MySQL 8.0 降序索引在 ORDER BY 混合排序场景下的性能提升对于(a DESC, b ASC)这类混合排序查询8.0 的耗时显著低于 5.7。4.2 创建降序索引-- MySQL 8.0 真正支持降序存储CREATEINDEXidx_amount_dateONsales(amountDESC,sale_dateASC);-- 查询可以直接利用索引无需 filesortSELECT*FROMsalesORDERBYamountDESC,sale_dateASC;-- Extra: Using index而不是 Using filesort五、特性 4不可见索引Invisible Indexes5.1 用途DBA 可以临时将索引设为不可见优化器会忽略它但索引仍在后台维护。用于安全地测试删除索引的影响。-- 创建不可见索引CREATEINDEXidx_testONsales(product)INVISIBLE;-- 或将现有索引设为不可见ALTERTABLEsalesALTERINDEXidx_product INVISIBLE;-- 测试查询性能优化器不会使用该索引EXPLAINSELECT*FROMsalesWHEREproductiPhone;-- 如果性能下降随时恢复可见ALTERTABLEsalesALTERINDEXidx_product VISIBLE;5.2 强制使用不可见索引-- 会话级别强制优化器考虑不可见索引SETSESSIONoptimizer_switchuse_invisible_indexeson;六、特性 5原子 DDLAtomic DDL6.1 什么是原子 DDLMySQL 8.0 将 DDL 操作如 CREATE、ALTER、DROP变为原子性操作要么完全成功要么完全回滚不会出现操作一半失败导致数据字典不一致的情况。-- MySQL 5.7 的问题DROP TABLE 中途崩溃可能表文件删了但数据字典记录还在-- MySQL 8.0 的解决DROP TABLE 是原子操作失败会自动回滚-- 示例安全地重命名表RENAMETABLEold_tableTOnew_table;-- 原子操作不会半成功6.2 INSTANT ADD COLUMNMySQL 8.0.12 支持瞬间加列无需复制整张表数据。-- 传统加列MySQL 5.7复制整张表大表可能需要数小时ALTERTABLEbig_tableADDCOLUMNnew_colVARCHAR(50);-- 耗时取决于表大小-- MySQL 8.0 INSTANT 加列修改元数据毫秒级完成ALTERTABLEbig_tableADDCOLUMNnew_colVARCHAR(50),ALGORITHMINSTANT;-- 限制只能加在表尾不能加在中间8.0.29 支持加在中间-- 查看表是否支持 INSTANTSELECTNAME,TOTAL_ROW_VERSIONS,CASEWHENTOTAL_ROW_VERSIONS0THEN支持 INSTANTELSE不支持ENDASinstant_supportFROMinformation_schema.INNODB_TABLESWHERENAMEdb1/big_table;七、特性 6角色权限管理Roles7.1 创建和使用角色MySQL 8.0 引入了角色Role概念可以批量管理权限类似 Linux 的用户组。-- 创建角色CREATEROLEapp_read,app_write,app_admin;-- 给角色授权GRANTSELECTONmydb.*TOapp_read;GRANTSELECT,INSERT,UPDATE,DELETEONmydb.*TOapp_write;GRANTALLPRIVILEGESONmydb.*TOapp_admin;-- 创建用户并赋予角色CREATEUSERdeveloper%IDENTIFIEDBYpassword;GRANTapp_writeTOdeveloper%;-- 创建用户并赋予多个角色CREATEUSERdba%IDENTIFIEDBYpassword;GRANTapp_read,app_write,app_adminTOdba%;-- 用户激活角色默认不自动激活SETDEFAULTROLEapp_writeTOdeveloper%;-- 或用户登录后手动激活SETROLEapp_write;-- 查看当前用户的角色和权限SHOWGRANTS;SHOWGRANTSFORCURRENT_ROLE();7.2 强制角色Mandatory Roles# my.cnf所有用户自动拥有某些角色 [mysqld] mandatory_roles app_read八、特性 7JSON 增强8.1 JSON 数据类型改进MySQL 5.7 已支持 JSON8.0 进一步增强了 JSON 操作能力。-- 创建 JSON 列CREATETABLEusers(idINTPRIMARYKEY,profile JSON,INDEXidx_city((CAST(profile-$.cityASCHAR(50))))-- 函数索引8.0.13);-- 插入 JSON 数据INSERTINTOusersVALUES(1,{name: 张三, age: 25, city: 北京, tags: [程序员, 篮球]});-- JSON 路径查询SELECTid,JSON_EXTRACT(profile,$.name)ASname,profile-$.cityAScity,-- - 是 JSON_EXTRACT UNQUOTE 的简写JSON_CONTAINS(profile,篮球,$.tags)ASlikes_basketballFROMusers;-- JSON 聚合函数SELECTid,JSON_ARRAYAGG(tag)ASall_tags-- 将多行聚合成 JSON 数组FROMuser_tagsGROUPBYid;-- JSON 表函数将 JSON 数组展开为多行SELECTu.id,jt.tagFROMusers u,JSON_TABLE(u.profile,$.tags[*]COLUMNS(tagVARCHAR(50)PATH$))ASjt;8.2 多值索引Multi-Valued IndexesMySQL 8.0.17 支持对 JSON 数组创建索引。-- 为 JSON 数组创建多值索引CREATEINDEXidx_tagsONusers((CAST(profile-$.tagsASCHAR(50)ARRAY)));-- 查询可以利用索引SELECT*FROMusersWHEREJSON_CONTAINS(profile,篮球,$.tags);九、特性 8直方图统计Histograms9.1 为什么需要直方图优化器依赖索引统计信息如 cardinality来估算查询成本。但对于数据分布不均的列如 80% 是 “completed”20% 是 “pending”简单的 cardinality 无法准确反映分布情况导致优化器选择错误的执行计划。上图展示了直方图如何将数据分布划分为多个桶bucket每个桶记录值的频率帮助优化器更准确地估算查询成本。9.2 创建和使用直方图-- 为 status 列创建直方图默认 100 个桶ANALYZETABLEordersUPDATEHISTOGRAMONstatusWITH100BUCKETS;-- 查看直方图信息SELECTCOLUMN_NAME,HISTOGRAM-$.number-of-buckets-specifiedASbuckets,HISTOGRAM-$.data-typeASdata_typeFROMinformation_schema.COLUMN_STATISTICSWHERETABLE_NAMEordersANDCOLUMN_NAMEstatus;-- 删除直方图ANALYZETABLEordersDROPHISTOGRAMONstatus;-- 查看优化器是否使用了直方图EXPLAINANALYZESELECT*FROMordersWHEREstatuspending;-- 如果 rows 估算更准确说明直方图生效十、特性 9默认字符集升级10.1 utf8mb4 成为默认字符集MySQL 8.0 将默认字符集从latin1升级为utf8mb4排序规则升级为utf8mb4_0900_ai_ci。版本默认字符集默认排序规则说明MySQL 5.7latin1latin1_swedish_ci不支持 EmojiMySQL 8.0utf8mb4utf8mb4_0900_ai_ci支持 Emoji、中文、多语言-- 查看默认字符集SHOWVARIABLESLIKEcharacter_set%;-- 创建表时显式指定推荐CREATETABLEcomments(idINTPRIMARYKEY,contentVARCHAR(500))CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;-- 旧表升级到 utf8mb4ALTERTABLEold_tableCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;注意utf8mb4_0900_ai_ci是 MySQL 8.0 新增的排序规则基于 Unicode 9.0比utf8mb4_general_ci更准确但部分旧系统可能不兼容。十一、特性 10MySQL Shell 与 X Protocol11.1 MySQL Shell新一代客户端MySQL Shell 是官方推出的高级客户端支持 JavaScript、Python 和 SQL 三种模式。# 启动 MySQL Shellmysqlsh rootlocalhost:3306# JavaScript 模式默认\js# 执行 SQL\sql SELECT * FROMusersWHEREid1;# 执行 JavaScript 脚本dbsession.getSchema(mydb);tabledb.getTable(users);resulttable.select([id,name]).where(age 18).execute();print(result.fetchAll());# 使用 AdminAPI 管理 MGR 集群\js clusterdba.createCluster(myCluster);cluster.addInstance(rootdb2:3306);cluster.addInstance(rootdb3:3306);cluster.status();11.2 X Protocol 与 Document StoreMySQL 8.0 通过 X Plugin 支持 X Protocol可以直接操作 JSON 文档无需 SQL。// MySQL Shell 中使用 Document Store\js sessionmysqlx.getSession(root:passwordlocalhost:33060);dbsession.getSchema(mydb);// 创建集合类似 MongoDB 的 Collectioncollectiondb.createCollection(products);// 插入文档collection.add({name:iPhone 15,price:5999,tags:[手机,苹果]}).execute();// 查询文档resultcollection.find(price 5000).execute();print(result.fetchAll());// 修改文档collection.modify(name iPhone 15).set(price,5799).execute();十二、其他值得关注的特性特性版本说明持久化配置8.0SET PERSIST将变量修改持久化到mysqld-auto.cnf重启后仍生效资源组Resource Groups8.0限制线程的 CPU 和 IO 使用隔离不同业务的资源倒排索引8.0.13InnoDB 表支持全文倒排索引提升全文检索性能克隆插件Clone Plugin8.0.17快速物理克隆实例用于搭建从库或备份备份锁Backup Lock8.0LOCK INSTANCE FOR BACKUP允许在线备份时阻塞 DDL不阻塞 DMLTempTable 引擎8.0替代 Memory 引擎处理内部临时表支持变长数据类型十三、升级 checklist□ 确认当前版本必须是 5.7不支持 5.5/5.6 直接升级 □ 备份数据mysqldump 或物理备份XtraBackup □ 检查废弃特性移除 query_cache、mysql_install_db 等 □ 检查认证插件旧客户端需升级或改为 mysql_native_password □ 检查 SQL 模式ONLY_FULL_GROUP_BY 等模式更严格 □ 测试环境验证先在测试库跑一遍升级流程 □ 检查性能关键查询用 EXPLAIN 对比执行计划 □ 检查字符集确认 utf8mb4 兼容性 □ 监控升级过程观察错误日志和性能指标十四、面试高频考点速记Q1MySQL 8.0 相比 5.7 有哪些重要改进窗口函数和 CTE 补齐 SQL 标准降序索引和不可见索引优化查询性能原子 DDL 避免数据字典不一致角色权限管理简化权限控制JSON 增强和多值索引支持文档存储直方图统计优化查询计划默认 utf8mb4 支持 EmojiMySQL Shell 和 X Protocol 提供 NoSQL 能力。Q2窗口函数和 GROUP BY 有什么区别GROUP BY 将多行聚合成一行压缩窗口函数保留所有行在每行上计算聚合值。窗口函数通过 OVER 子句定义计算范围PARTITION BY 分组 ORDER BY 排序 ROWS/RANGE 滑动窗口。Q3什么是原子 DDL有什么好处MySQL 8.0 将 DDL 操作变为原子性要么完全成功要么完全回滚。好处是避免操作中途崩溃导致数据字典和文件系统不一致如表文件删了但字典记录还在。同时支持 INSTANT ADD COLUMN大表加列从小时级降到毫秒级。Q4MySQL 8.0 的默认认证插件是什么旧客户端连不上怎么办默认改为caching_sha2_password比mysql_native_password更安全但兼容性差。解决方法1. 升级客户端驱动2. 创建用户时显式指定WITH mysql_native_password3. 修改全局变量default_authentication_plugin。Q5不可见索引有什么用途允许 DBA 临时禁用索引而不删除它用于安全地测试删除索引对查询性能的影响。如果性能下降随时恢复可见无需重新创建索引。Q6MySQL 8.0 的 JSON 多值索引是什么允许对 JSON 数组类型的字段创建索引。例如用户标签是 JSON 数组[程序员, 篮球]可以创建多值索引让JSON_CONTAINS查询走索引而不是全表扫描。结语MySQL 8.0 是一次全面的现代化升级开发效率窗口函数、CTE、JSON 增强让复杂查询更简洁运维安全原子 DDL、不可见索引、角色管理降低运维风险性能优化降序索引、直方图、TempTable 引擎提升查询效率架构扩展MGR、Clone Plugin、MySQL Shell 支持云原生和分布式场景如果你还在用 MySQL 5.7强烈建议制定升级计划。MySQL 5.7 已于2023 年 10 月停止官方支持不再接收安全补丁。如果本文对你有帮助欢迎点赞收藏关注
