Mysql8.0新特性01

转载

mob604756eb6938 2021-07-25 17:09:00

文章标签 mysql json 持久化表空间重启 文章分类 MySQL 数据库

1.前言

Mysql8.0自从2016年就开始迭代了，不管从Mysql8.0.0到Mysql8.0.5都不是GA版本，直到Mysql8.0.11(这里直接是从Mysql8.0.5调到Mysl8.0.11)才开始发布GA版本(稳定版)，首先说明一点，Mysql8.0版本在官方介绍中它是一个里程碑式的一个版本，它是直接从Mysql5.7版本跳到Mysql8.0的，因此未来Mysql数据库当属于Mysql8.0版本的天下了。

2.新版本的一些新的特性

　　首先说明一下，由于本人真正接触Mysql比较晚(Mysql8.0都8.0.20了)，因此，这里我就没有对Mysql8.0新版本进行追踪，因此这里就借用别人的文章，打开地记录一下Mysql8.0版本到底有了那些新的变化。

3.新特性(部分)

　　3.1 默认字符集有Latin1变为utf8mb4

　　　在8.0版本之前，默认字符集为latin1，utf8指向的utfmb3，8.0版本默认字符集为utf8mb4，utf默认指向的也是utf8mb4

　　3.2 MyISM系统表全部都换成了innodb表

　　　　系统表全部换成事务型的innodb表，默认的MySQL实例将不包含任何MyISAM表，除非手动创建MyISAM表　　　　

# MySQL 5.7
mysql>select distinct(ENGINE) from information_schema.tables;
+--------------------+
|ENGINE  |
+--------------------+
|MEMORY  |
|InnoDB  |
|MyISAM  |
|CSV  |
|PERFORMANCE_SCHEMA|
|NULL  |
+--------------------+
6rows inset (0.00sec)

# MySQL 8.0
mysql>select distinct(ENGINE) from information_schema.tables;
+--------------------+
|ENGINE  |
+--------------------+
|NULL  |
|InnoDB  |
|CSV  |
|PERFORMANCE_SCHEMA|
+--------------------+
4rows inset (0.00se

　　3.3 自增变量持久化

　　在8.0之前的版本，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id=199)，一直到8.0才被解决，8.0版本将会对AUTO_INCREMENT值进行持久化，MySQL重启后，该值将不会改变

　　3.4 DDL原子化

　　InnoDB表的DDL支持事务完整性，要么成功要么回滚，将DDL操作回滚日志写入到data dictionary 数据字典表mysql.innodb_ddl_log 中用于回滚操作，该表是隐藏的表，通过show tables无法看到。通过设置参数

　　3.5参数修改持久化

　　MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。

　　例如执行：

　　set PERSIST expire_logs_days=10 ;

　　系统会在数据目录下生成一个包含json格式的mysqld-auto.cnf 的文件，格式化后如下所示，当my.cnf 和mysqld-auto.cnf 同时存在时，后者具有更高优先级　　

{
  "Version":1,
  "mysql_server":{
  "expire_logs_days":{
  "Value":"10",
  "Metadata":{
  "Timestamp":1529657078851627,
  "User":"root",
  "Host":"localhost"
  }
  }
  }
}

　　3.6 新增降序索引

　　3.7 group by 不再隐式排序

　　　　mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句　　　

# 表结构
mysql>show create table tb1\G
***************************1.row ***************************
  Table: tb1
CreateTable: CREATETABLE`tb1` (
  `id` int(11) NOTNULLAUTO_INCREMENT,
  `name` varchar(50) DEFAULTNULL,
  `group_own` int(11) DEFAULT'0',
  PRIMARYKEY(`id`)
) ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciROW_FORMAT=DYNAMIC
1row inset (0.00sec)

# 表数据
mysql>select *from tb1;
+----+------+-----------+
|id |name |group_own|
+----+------+-----------+
|  1|1  |  0|
|  2|2  |  0|
|  3|3  |  0|
|  4|4  |  0|
|  5|5  |  5|
|  8|8  |  1|
|10|10  | 5|
+----+------+-----------+
7rows inset (0.00sec)

# MySQL 5.7
mysql>select count(id), group_own from tb1 group by group_own;
+-----------+-----------+
|count(id)|group_own|
+-----------+-----------+
|  4|  0|
|  1|  1|
|  2|  5|
+-----------+-----------+
3rows inset (0.00sec)

# MySQL 8.0.11
mysql>select count(id), group_own from tb1 group by group_own;
+-----------+-----------+
|count(id)|group_own|
+-----------+-----------+
|  4|  0|
|  2|  5|
|  1|  1|
+-----------+-----------+
3rows inset (0.00sec)

# MySQL 8.0.11显式地加上order by进行排序
mysql>select count(id), group_own from tb1 group by group_own order by group_own;
+-----------+-----------+
|count(id)|group_own|
+-----------+-----------+
|  4|  0|
|  1|  1|
|  2|  5|
+-----------+-----------+
3rows inset (0.00sec)

　　3.8 JSON特性增强　

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

　　　　在主从复制中，新增参数 binlog_row_value_options，控制JSON数据的传输方式，允许对于Json类型部分修改，在binlog中只记录修改的部分，减少json大数据在只有少量修改的情况下，对资源的占用。

　　3.9 redo & undo 日志加密

增加以下两个参数，用于控制redo、undo日志的加密。
innodb_undo_log_encrypt
innodb_undo_log_encrypt

　　3.10 innodb select for update跳过锁等待

　　　　select ... for update，select ... for share(8.0新增语法) 添加NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳过锁定。

在5.7及之前的版本，select...for update，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。

　　　　在8.0版本，通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。

　　3.11 增加SET_VAR语法

　　在sql语法中增加SET_VAR语法，动态调整部分参数，有利于提升语句性能。

· select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ;
· insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

　　3.12 支持不可见索引

　　使用INVISIBLE关键字在创建表或者进行表变更中设置索引是否可见。索引不可见只是在查询时优化器不使用该索引，即使使用force index，优化器也不会使用该索引，同时优化器也不会报索引不存在的错误，因为索引仍然真实存在，在必要时，也可以快速的恢复成可见。

# 创建不可见索引
create table t2(c1 int,c2 int,index idx_c1_c2(c1,c2 desc) invisible );
# 索引可见
alter table t2 alter index idx_c1_c2 visible;
# 索引不可见
alter table t2 alter index idx_c1_c2 invisible;

　　3.13 undo空间自动回收　

· innodb_undo_log_truncate参数在8.0.2版本默认值由OFF变为ON，默认开启undo日志表空间自动回收。

· innodb_undo_tablespaces参数在8.0.2版本默认为2，当一个undo表空间被回收时，还有另外一个提供正常服务。

· innodb_max_undo_log_size参数定义了undo表空间回收的最大值，当undo表空间超过这个值，该表空间被标记为可回收。

　　3.14 增加角色管理

　　角色可以认为是一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无需为每个用户单独授权。

# 创建角色
mysql>create role role_test;
QueryOK, 0rows affected (0.03sec)

# 给角色授予权限
mysql>grant select on db.*to 'role_test';
QueryOK, 0rows affected (0.10sec)

# 创建用户
mysql>create user 'read_user'@'%'identified by '123456';
QueryOK, 0rows affected (0.09sec)

# 给用户赋予角色
mysql>grant 'role_test'to 'read_user'@'%';
QueryOK, 0rows affected (0.02sec)

# 给角色role_test增加insert权限
mysql>grant insert on db.*to 'role_test';
QueryOK, 0rows affected (0.08sec)

# 给角色role_test删除insert权限
mysql>revoke insert on db.*from 'role_test';
QueryOK, 0rows affected (0.10sec)

# 查看默认角色信息
mysql>select *from mysql.default_roles;
+------+-----------+-------------------+-------------------+
|HOST|USER  |DEFAULT_ROLE_HOST|DEFAULT_ROLE_USER|
+------+-----------+-------------------+-------------------+
|%  |read_user |%  |role_test  |
+------+-----------+-------------------+-------------------+
1row inset (0.00sec)

# 查看角色与用户关系
mysql>select *from mysql.role_edges;
+-----------+-----------+---------+-----------+-------------------+
|FROM_HOST|FROM_USER|TO_HOST|TO_USER  |WITH_ADMIN_OPTION|
+-----------+-----------+---------+-----------+-------------------+
|%  |role_test |%  |read_user |N  |
+-----------+-----------+---------+-----------+-------------------+
1row inset (0.00sec)

# 删除角色
mysql>drop role role_test;
QueryOK, 0rows affected (0.06sec)