mysql8新特性二

四、窗口函数

1、基本概念
mysql8支持窗口函数（window function），也称分析函数
窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果
聚合窗口函数：sum/avg/count/max/min等等

create table sales (year year, country varchar(32),product varchar(32),profit int);
Insert into sales values
('2000','Finland','Computer',1500),
('2001','USA','Computer',1200),
('2001','Finland','Phone',10),
('2000','India','Calculator',75),
('2001','USA','TV',150),
('2000','India','Computer',1200),
('2000','USA','Calculator',5),
('2000','USA','Computer',1500),
('2000','Finland','Phone',100),
('2001','USA','Calculator',50),
('2001','USA','Computer',1500),
('2000','India','Calculator',75),
('2001','USA','TV',100);

传统聚合函数

> select country ,sum(profit) from sales group by country ;

窗口分析函数

> select year, country product, profit,
sum(profit) over (partition by country) as country_profit
From sales;

> select year, country product, profit,
avg(profit) over (partition by country) as avg_profit
From sales;

2、专用聚合函数

2.1序号函数：row_number()，rank()，dense_rank()

序号函数的作用是显示分区中每行的行号。三者的区别在于，当排序字段相同时，三个函数的处理结果不同：

row_number()：排序字段相同时，行号随机排，下一行行号正常加1。显然此函数得到的行号是连续的。

rank()：排序字段相同时，行号相同，下一行行号按照排名确定。这个逻辑比较像是现实中的排名规则，当出现并列冠军时，第三个人排名第三，没有亚军。显然此函数得到的行号是不连续的。

dense_rank()：排序字段相同时，行号相同，下一行行号顺序加1。也就是出现冠军时，第三个人排名是第二。显然此函数得到的行号是连续的。

2.2、分布函数：percent_rank() / cume_dist()

分布函数得到的是一个百分比，计算公式用到rank()函数

percent_rank()

实际计算公式：(rank()-1)/(rows-1)

其中rank()是rank()函数得到的结果，rows是分区行数。

 

cume_dist()

实际计算公式：rank()/rows

这个函数的含义比较好理解一点，就是小于等于自己排名的行占总行数的比例。
 

2.3、前后函数：lead() / lag()

lead (expr,n)代表当前行前面第n行记录的expr表达式（或字段值）。

lag(expr,n)代表当前行后面的第n行记录的expr表达式（或字段值）。

比如lead (add_time,1)就代表当前行前面一行的add_time字段。这两个函数是基于排序的

 

2.4、头尾函数：first_value() / last_value()

first_value(expr)代表分区第一行记录的expr表达式（或字段值）。

last_value (expr)代表分区最后一行记录的expr表达式（或字段值）。

这两个函数是基于分组的。

 

2.5、其他函数：nth_value() / nfile()

nth_value(expr,n)代表区间第n条记录的expr表达式（或字段值）。

比如：nth_value(amount,2)代表区间第二行的amount字段。

nfile(n)代表把区间列分为n个组，返回组号。

注意：这个分组只能是尽量平均，但有时候不能完全平均，比如分区内有7条数据，用nfile(3)分三组，记录的组号就会是：1,1,1,2,2,3,3。
 

 

create table numbers(val int);
Insert into numbers values(1),(1),(2),(3),(3),(3),(3),(4),(4),(5);

>Select val,row_number() over (partition by val order by val) as 'row_number' from numbers;

>Select val,row_number() over (order by val) as 'row_number' from numbers;

>Select val,dense_rank() over (order by val) as 'row_number'  from numbers;

>Select val,first_value(val) over (order by val) as 'first',lead(val,1) over(order by val) as 'lead'  from numbers;

>Select val,first_value(val) over (order by val) as 'first',lead(val,2) over(order by val) as 'lead'  from numbers;

3、窗口定义

window_function(expr)
Over (
partition by ...
Order by ...
frame_clause ...
)

 

select year,country,product,profit,
sum(profit) over(partition by country order by profit rows unbounded preceding) as running_total
From sales
Order by country, profit;

select year,country,product,profit,
avg(profit) over(partition by country order by profit rows between 1 preceding and 1 following) as running_avg
From sales
Order by country, profit;

select year,country,product,profit,
first_value(profit) over w as 'first',
last_value(profit) over w as 'last'
From sales
Window w as (partition by country order by profit rows unbounded preceding)
Order by country, profit;

 

五、innodb增强

1、集成数据字典

mysql8删除了之前版本的元数据文件，如.frm,.opt等；

将系统表（mysql）和数据字典表全部改为innodb存储引擎

支持原子ddl语句

简化了infomation_schema的实现，提高了访问性能

提供了序列化字典信息（sdi）的支持，以及ibd2sdi工具

数据字典使用上的差异，例如innodb_read_only影响所有的存储引擎；数据字典表不可见，不能直接查询和修改。

2、原子操作ddl

Mysql8开始支持原子ddl操作，其中与表相关的原子ddl只支持innodb存储引擎

一个原子ddl操作内容包括：更新数据字典，存储引擎层的操作，在binlog中记录ddl操作

支持的原子ddl操作

支持与表相关的ddl：数据库、表空间、表、索引的create\alter\drop\以及truncate table

支持的其他的ddl：存储过程、触发器、视图、udf的create、drop以及alter语句

支持的账户管理相关的ddl：用户和角色的create\alter\drop以及适用的rename，以及grant和revoke语句

3、自增列的持久化

mysql5.7以及早期版本，innodb自增列计数器（auto_increment）的值只存储在内存中

mysql8每次变化时将自增计数器的最大值写入redo log,同时在每次检查点将其写入引擎私有的系统表

解决了长期以来的自增字段可能重复的bug

show variables like 'innodb_autoinc%';

4、死锁检查控制

mysql8增加了一个新的动态变量，用于控制系统是否执行innodb死锁检查

innodb_deadlock_detect

对于高并发的系统，禁用死锁检查可能带来性能的提高

5、锁定语句选项

select ... for share 和 select ... for update 中支持nowait、skip locked选项；select ... for update [nowait | skip locked]

对于nowait，如果请求的行被其他事务锁定时，语句立即返回。
对于skip locked，从返回的结果集中移除被锁定的行。

6、其他功能改进

支持部分快速ddl,alter table ... algorithm=instant;

Innodb 临时表使用共享的临时表空间ibtmp1

新增静态变量innodb_dedicated_server,自动配置innodb内存参数：innodb_buffer_pool_size/innodb_log_file_size等。

新增表INFORMATION_SCHEMA.INNODB_CACHED_INDEXES,显示没个索引缓存在innodb缓存池中的索引页数

新增视图INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF,为innodb表空间提供相关元数据信息。

默认创建2个undo表空间，不再使用系统表空间

支持alter tablespace ... rename to 重命名通用表空间

支持使用innodb_directories选项在服务器停止时将表空间文件移动到新的位置

innodb表空间加密特性支持重做日志和撤销日志

 

六、json增强

1、内联路径操作符

主要用于获取json对象在某些节点或某些路径上的数据值

mysql8增加了json操作符column->>path，等价于 
json_unquote(column->path)
json_unquote(json_extract(column, path))

mysql5.7

With doc(data) as 
(Select json_object('id','3','name','Barney'))
Select json_unquote(data->'$.name') from doc;

With doc(data) as 
(Select json_object('id','3','name','Barney'))
Select json_unquote(json_extract(data,'$.name')) from doc;

Select json_extract('["a","b","c"]', '$[1]');

 

mysql8

With doc(data) as 
(Select json_object('id','3','name','Barney'))
Select data->>'$.name' from doc;

支持范围查找

Select json_extract('["a","b","c","d","3"]', '$[1 to 3]');

Select json_extract('["a","b","c","d","3"]', '$[last-2 to last]');

 

2、json聚合函数

Create table t(o_id int, attribute varchar(32),value varchar(32));
Insert into t values
(2,'color','red'),
(2,'fabric','silk'),
(3,'color','green'),
(3,'shape','square');

 

mysql8（mysql5.7.22）增加了2个聚合函数：

json_arrayagg(),用于生成json数组

Select o_id,json_arrayagg(attribute) as attributes from t group by o_id;

json_objectagg(),用于生成json对象

Select o_id,json_objectagg(attribute,value) as attributes from t group by o_id;

insert into t values (3,'color','yellow');

Select o_id,json_objectagg(attribute,value) as attributes from t group by o_id;//相同属性值，后边的覆盖前边的值

 

3、json实用函数

mysql8（mysql5.7.22）增加了json_pretty()

Select json_pretty(json_object("id","3",'name','Barney'));

mysql8（mysql5.7.22）增加了json_storage_size()

mysql8增加了json_storage_free()

 

4、json合并函数

mysql8（mysql5.7.22）增加了json_merge_patch()

Select json_merge_patch('{"a":1,"b":2}', '{"a":3,"c":4}');//相同节点，后边的覆盖前边的

mysql8（mysql5.7.22）增加了json_merge_preserve()

Select json_merge_preserve('{"a":1,"b":2}', '{"a":3,"c":4}');//相同节点都保留

msyql8废弃了json_merge()

 

5、json表函数

mysql8增加了json_table(),将json数据转化为关系表，可以将该函数返回结果当作一个普通的表，使用sql进行查询

Select * from 
json_table(
'[{"a":3}, {"a":2}, {"b":1}, {"a":0}, {"a":[1,2]}]',
"$[*]" columns(
Rowed for ordinality,
ac varchar(100) path "$.a" default '999' on error default '111' on empty,
aj json path "$.a" default '{"x":333}' on empty,
bx int exists path "$.b"
)
) as tt;