MySQL学习---17、MySQL8其它新特性

1、MySQL新增特性

1.1 更简便的NoSQL支持

NoSQL泛指非关系型数据库和数据存储。随着互联网平台的规模飞速发展，传统的关系型数据库已经越来越不能瞒住需求。从5.6版本开始，MySQL就开始支持简单的NoSQL存储功能。MySQL 8对这一功能做了优化，以更灵活的方式实现NoSQL功能，不再依赖模式（schema）。

1.2 更好的索引

在查询中，正确地使用索引可以提高查询的效率。MySQL8中新增了隐藏索引和降序索引。隐藏索引可以用来测试去掉索引对查询性能的影响。在查询中混合多列索引时，使用降序索引可以提高查询的性能。

1.3 更完善的JSON支持

MySQL从5.7开始支持原生JSON数据的存储，MySQL8对这一功能做了优化，增加了聚合函数JSON_ARRAYAGG()和JSON_OBJECTAGG()，将参数聚合为JSON数组或对象，新增了行内操作符–>，是列路径运算符->的增强，对JSON排序做了提示，并优化了JSON的更新操作。

1.4 安全和账号管理

MySQL8新增caching_sha2_password授权插件、角色、密码历史记录和FIPS模式支持，这些特性提高了数据的安全性和性能，使数据库管理员能够更加灵活地进行账户管理工作。

1.5 InnoDB的变化

InnoDB是MySQL默认的存储引擎，是事务性数据库的首选引擎，支持事务安全表（ACID）,支持行锁定和外键。在MySQL8版本中，InnoDB在自增、索引、加密、死锁、共享锁等方面做了大量的改进和优化，并且支持原子数据定义语言（DDL），提高了数据安全性，对事务提供更好的支持。

1.6 数据字典

在之前的MySQL版本中，字典数据都存储在元数据文件和非事务表中。从MySQL8开始新增了数据数据字典，在这个字典中存储着数据库对象的信息，这些数据字典存储在内部事务表中。

1.7 原子数据定义语句

MySQL8开始支持原子数据定义语句（Automic DDL），即原子DDL。目前。只有InnoDB存储引擎支持原子DDL。原子数据定义语句（DDL）将与DDL操作相关的数据字典更新、存储引擎操作、二进制日志写入结合到一个单独的原子事务中，这使得即使服务器崩溃，事务也会提交或回滚。使用支持原子操作的存储引擎所创建的表，在执行Drop table、Create table、alter table、rename table、truncate table、等操作时，都支持原子操作，即事务要么完全操作成功，要么失败回滚，不再进行部分提交。

1.8 资源管理

MySQL8开始支持创建和管理资源组，允许将服务器内运行的线程分配给特定的组，以便线程根据组内可用资源执行。组属性能够控制组内资源，启用或限制组内资源消耗。数据库管理员能够根据不同的工作负载适当地更改这些属性。目前，cpu时间是可控资源，由‘虚拟cpu’这个概念来表示，此术语包含cpu的核心数，超线程，硬件线程等。服务器在启动时确定可用的虚拟cpu数量。拥有对应权限的数据库管理员可以将这些CPU与资源组相关联，并且为资源组分配线程。

1.9 字符集支持

MySQL8中默认的字符集由latin1更改为utf8mb4，并首次增加了日语所特点使用的集合。

1.10 优化器增强

MySQL优化器开始支持隐藏索引和降序索引。隐藏索引不会被优化器使用，验证索引的必要性时不需要删除索引，先将索引隐藏，如果优化器性能无影响就可以真正的删除索引。降序索引允许优化器对多个列进行排序，并且运行排序顺序不一致。

1.11 公用表表达式

公用表表达式（Common Table Expression）简称CTE，MySQL现在支持递归和非递归两种形式的CTE。CTE通过在select语句或其他特点语句前使用With语句对临时结果集进行命名。
基本语法：

With cte_name(col_name1,col_name2,...) as (Subquery)
select * from cte_name

说明：
Subquery代表子查询，子查询前使用With语句将结果集命名为cte_name,在后续的查询中即可使用cte_name进行查询

1.12 窗口函数

MySQL开始支持窗口函数。在之前的版本已经存在大部分的聚合函数，在MySQL8中也可以作为窗口函数来使用。

函数名称	描述
Cume_Dist ()	累计的分布值
Dense_Rank()	对当前记录不间断排序
First_Value()	返回窗口首行记录的对应字段值
Lag()	返回对象字段的前n行记录
Last_Value()	返回窗口尾行记录的对应字段值
Lead()	返回对应字段的后n行记录
Nth_Value()	返回地n条记录对应的字段值
Ntile()	将区划分为n组，并返回组的数量
Percent_Rank()	返回0到1之间的小数，表示某个字段值在数据分区中的排名
Rank()	返回分区内每条记录对应的排名
Row_Number	返回每一条记录对应的序号，且不重复

1.13 正则表达式

MySQL在8.0.4以后得版本中采用支持Unicode的国际化组件库实现正则表达式操作，这种方式不仅能提供完全Unicode支持，而且是多字节安全编码。MySQL增加了REGEXP_LIKE()、EGEXP_INSTR()、REGEXP_REPLACE()和 REGEXP_SUBSTR()等函数来提升性能。另外，regexp_stack_limit和 regexp_time_limit 系统变量能够通过匹配引擎来控制资源消耗。

1.14 内部临时表

TempTable存储引擎取代Memory存储引擎成为内部临时表的默认存储引擎。TempTable存储引擎为VarChar和VarBInary列提供高效存储。intermal_tmp_mem_storge_engine会话变量定义了内部临时表的存储引擎，可选的值有两个，TempTable和Memory，其中TempTable为默认的存储引擎。temptable_max_ram系统配置项定义了TempTable存储引擎可使用的最大内存数量。

1.15 日志记录

在MySQL8中错误日志子系统由一系列MySQL组件组成。这些组件的·构成由系统变量log_error_services来配置，能够实现日志时间的过滤和写入。

1.16 增强的MySQL复制

MySQL8复制支持对JSON文档进行部分更新的二进制日志记录，该记录使用紧凑的二进制格式，从而节省完整JSON文档空间。当使用基于语句的日志记录时，这种紧凑的日志记录会自动完成，并且可以通过新的binlog_row_value_options系统变量值设置为PARTIA_JSON来启用。

2、窗口函数

MySQL从8.0版本开始支持窗口函数，窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数式将结果置于每一条数据记录中。

窗口函数可以分为静态窗口函数和动态窗口函数。
（1）静态窗口函数的窗口ed大小是固定的，不会因为记录的不同而不同。
（2）动态窗口函数的窗口大小会随着记录的不同而变化。

函数分类	函数	函数说明
序号函数	Row_Number()	排序函数
序号函数	Rank()	并列排序，会跳过重复的序号，比如序号1,1,3
序号函数	Dense_Rank()	并列排序，不会跳过重复的序号，比如序号1，1,2
分布函数	Percent_Rank()	等级值百分比
分布函数	Cume_Dist()	累计分布值
前后函数	Lag(expr,n)	返回当前行的前n行的expr值
前后函数	LEAD(expr,n)	返回当前行的后n行的expr的值
首尾函数	First_Value()	返回第一个expr值
首尾函数	Last_Value()	返回最后一个expr值
其他函数	Nth_Value(expr,n)	返回第n个expr值
其他函数	Ntile(n)	将分区中的有序数据分为n个桶，记录桶的编号

1、语法结构

函数 over ([partition by 字段名  order by 字段名  asc|desc])

或

函数 over 窗口名 ...Window 窗口名 as ([partition by 字段名 order by 字段名 ASC|DESC])

2、案例实操

CREATE TABLE sales(id INT PRIMARY KEY AUTO_INCREMENT,city VARCHAR(15),county VARCHAR(15),sales_value DECIMAL
);INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',10.00),
('北京','朝阳',20.00),
('上海','黄埔',30.00),
('上海','长宁',10.00);SELECT * FROM sales;#需求：现在计算这个网站在每个城市的销售总额，在全国的销售总额，每个区的销售额占所在城市销售
#中的比率，以及占总销售额的比率#如果用分组和聚合函数计算要分好几步来算
#第一步
CREATE TEMPORARY TABLE a
SELECT SUM(sales_value) AS sales_value
FROM sales;SELECT * FROM a;#计算每个城市的销售总额并存入临时表中
CREATE TEMPORARY TABLE b
SELECT city,SUM(sales_value) AS sales_value
FROM sales
GROUP BY city;DROP TABLE b;SELECT * FROM b;#计算各区销售占所在城市的总金额的比例和占全部销售总计金额的比例SELECT s.city AS 城市 ,s.county AS 区,s.sales_value AS 销售额,
b.sales_value AS 市销售额,s.sales_value /b.sales_value AS 市比率,
a.sales_value AS 总销售额,s.sales_value /a.sales_value AS 总比例
FROM sales s
JOIN b ON (s.city=b.city)
JOIN a 
ORDER BY s.city,s.county;SELECT * 
FROM b JOIN a;#如果用窗口函数
SELECT city AS 城市 ,county AS 区,sales_value AS 销售额,
SUM(sales_value) over(PARTITION BY city) AS 市销售额,
sales_value/SUM(sales_value) over (PARTITION BY city) AS 市比率,
SUM(sales_value) over() AS 总销售额,
sales_value/SUM(sales_value) over() AS 总比率
FROM sales
ORDER BY city,county;