MySQL系列之MySQL8.0新特性

原创

johnny233 2022-10-30 09:50:05 博主文章分类：数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者johnny233的原创作品，请联系作者获取转载授权，否则将追究法律责任

概述

本文非原创，整理网络资源学习备用。

MySQL 8.0 正式版 8.0.11 已发布，官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍，还带来了大量的改进和更快的性能！

注意：从 MySQL 5.7 升级到 MySQL 8.0 仅支持通过使用 in-place 方式进行升级，不支持从 MySQL 8.0 降级到 MySQL 5.7（或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本）。唯一受支持的替代方案是在升级之前对数据进行备份。

新特性

隐藏索引

隐藏索引的特性对于性能调试非常有用。在 8.0 中，索引可以被隐藏和显示。当一个索引隐藏时，它不会被查询优化器所使用。
即，可以隐藏一个索引然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其恢复显示即可；如果数据库性能看不出变化，说明索引是多余的，可删除。
隐藏索引的语法：ALTER TABLE t ALTER INDEX i INVISIBLE; 恢复显示该索引的语法：ALTER TABLE t ALTER INDEX i VISIBLE;

当一个索引被隐藏时，可从show index命令输出中看到，该索引Visible 属性值为 NO。当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的，这个特性本身是专门为优化调试使用。如果你长期隐藏一个索引，那还不如干脆删掉，因为索引的存在会影响插入、更新和删除的性能。

CTE

通用表表达式，Common Table Expressions，复杂的查询会使用嵌入式表：

SELECT t1.*, t2.* FROM 
  (SELECT col1 FROM table1) t1,
  (SELECT col2 FROM table2) t2;

基于CTE的简化写法：

WITH
  t1 AS (SELECT col1 FROM table1),
  t2 AS (SELECT col2 FROM table2)SELECT t1.*, t2.* 
FROM t1, t2;

这样看上去层次和区域都更加分明，改起来也更清晰的知道要改哪一部分。
CTE官方文档

窗口函数

Window Functions，也叫开窗函数，MySQL 8 以前缺少rank()等开窗函数，当需要在查询当中实现排名时，必须手写 @ 变量。

推荐阅读：
SQL开窗函数Hive系列之开窗函数

窗口函数有点像是 SUM()、COUNT() 那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。即，窗口函数是不需要 GROUP BY 的。

窗口函数官方文档

Instant Add Column

ALGORITHM=INSTANT 目前对6种ddl有效：

Adding a column. This feature is referred to as Instant Add Column . 添加列
Adding or dropping a virtual column. 添加或删除virtual 列
Adding or dropping a column default value. 添加或删除列默认值
Modifying the definition of an ENUM. 修改 ENUM 定义
Changing the index type. 修改索引类型
Renaming a table. 重命名表

调整

UTF-8 编码

从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4，包含所有 emoji 字符。

设置持久化

MySQL设置可以在运行时通过SET GLOBAL命令来来时生效，下次启动时数据库又会从配置文件中读取。
新增SET PERSIST 命令：SET PERSIST max_connections = 500; MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。

优化

JSON

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html

MySQL 5.7开始提供NoSQL存储的功能。
MySQL 8 大幅改进对 JSON 的支持，添加基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

概述

不允许为null
JSON格式定义与LONGBLOB or LONGTEXT类似，最大长度受max_allowed_packet控制
查看JSON字段所占用空间大小的函数JSON_STORAGE_SIZE(xxx)
除普通的JSON操作，额外支持GeoJSON (基于几何图形的针对地理空间数据交换格式)一些相关操作
对JSON栏位支持索引（结合Mysql8.0新特性，函数index）
一个可以支持部分的，原地更新Json Column 的可选优化项加入MySql8.0
可以使用的函数有JSON_SET(), JSON_REPLACE() ,JSON_REMOVE()
使用时，有一些约束，但是会有更加的性能

新增

JSON_PRETTY：接受JSON数据类型或JSON的字符串表示形式，并以新的行和缩进方式以人类可读的方式返回JSON格式的字符串。
JSON_STORAGE_SIZE：

实例

-- JSON_ARRAY定义JSON数组
SELECT JSON_ARRAY(1, 'abc', NULL, TRUE, CURTIME());
-- [1, "abc", null, true, "11:30:24.000000"]

-- JSON_OBJECT定义JSON对象
SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
-- {"id": 87, "name": "carrot"}

-- 数组与对象嵌套的场景
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]] {"k1": "value", "k2": [10, 20]}

-- 日期/时间类型定义
["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

-- JSON_QUOTE 将JSON对象转义成String, 将内部的符号进行转义，并整体包裹上双引号；
JSON_QUOTE(' "null" ');
-- "\"null\""

-- 将JSON内容美化并输出
JSON_PRETTY();

-- 可以将JSON/JSON内部的元素转化为其他数据类型;
-- 将JSON jdoc 中的id元素转化为 unsigned int;
ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED);