MySQL-08 MySQL8.0新特性

2022-04-08 09:51:22

性能

MySQL 8.0 在一定的用户访问条件下，速度要比 MySQL 5.7 快 2 倍。MySQL 8.0 在以下方面带来了更好的性能：读/写工作负载、IO 密集型工作负载、以及高竞争（"hot spot"热点竞争问题）工作负载。

NoSQL的支持

MySQL 从 5.7 版本开始提供 NoSQL 存储功能，在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。

MySQL-08 MySQL8.0新特性 '

X Protocol访问：支持常规的TCP协议以及unix sockets访问数据库，提高了用户安全性。

默认字符集：utf8mb4

说明

8.0之前的版本，默认字符集为latin1，通常为了避免客户端和数据交互造成字符乱码，需要设置客户端和mysql数据的字符集为utf8。实际上utf8能够存储3个字节的字符，即utf8指向的是utf8mb3。
8.0版本默认字符集为utf8mb4，utf8默认指向的是utf8mb4。
utf8mb3和utf8mb4的区别：utf8mb3只支持BMP字符集；utf8mb4除了支持BMP字符集，还支持BMP之外的字符集，例如emoji编码符号。

查看字符集设置

查看全局字符集设置：

SHOW global variables LIKE '%character_set%';

MySQL-08 MySQL8.0新特性

使用默认配置创建的数据库字符集：

USE myschool;

SHOW variables LIKE '%character_set%';

MySQL-08 MySQL8.0新特性

工程中应用

utf8mb4 存储会消耗存储空间。
如果有使用到emoji的表可以设置为utf8mb4字符集。
表字段排序：tf8_unicode_ci比较准确，utf8_general_ci速度较快。通常创建数据库的时候使用utf8mb4_general_ci或者 utf8mb4_unicode_ci。

MySQL8默认数据表引擎为：InnoDB

说明

MySQL8创建数据库默认的数据表引擎为InnoDB类型，之前的版本默认为MyISAM类型。

MyISAM查询速度快，如果需要在mysql8中创建MyISAM表需要手动指定。

查看某表的数据表引擎命令：

show create table 表名

例如：

MySQL-08 MySQL8.0新特性

mysql8支持的数据表引擎

MySQL-08 MySQL8.0新特性

自增变量持久化

自增主键重启重置问题

8.0之前的mysql数据库重启或者断电后，innodb表的自增字段的值会被重置。原因：auto_increment计数值存储在内存中，断电导致计数值丢失。当重启mysql服务，mysql会读取表中最大的值并加1放入auto_increment的计数值中。

如果存在同时操作删除数据和添加数据的情况：例如删除数据的主键id为1000，插入数据的id应该为1001；如果删除成功后故障导致自增id计数值丢失，插入数据的id将会变为1000，若该id是表记录的业务主键，就会导致冲突。

mysql8.0的解决方案

mysql8对AUTO_INCREMENT值进行持久化（例如存储到硬盘），MySQL重启后，该值将不会改变。

DDL事务支持

MySQL8之前的版本只有ninnodb表支持DML语句的事务处理。

MySQL8支持数据表级别的DDL事务完处理。例如，在8.0之前的版本中，加入数据库中只有一张表table1，如果执行以下语句：

drop table table1, table2;

执行报错，但是table1还是会被删除；在mysql8.0中执行报错，但是table1不会被删除。

参数修改持久化

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

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

文件目录：

D:\MySQL8\data\Data\mysqld-auto.cnf

生成内容：

{ "Version" : 1 , "mysql_server" : { "binlog_expire_logs_seconds" : { "Value" : "8640000" , "Metadata" : { "Timestamp" : 1546569025389875 , "User" : "root" , "Host" : "localhost" } } } }

新增降序索引

MySQL在语法上很早就已经支持降序索引，但实际上创建的仍然是升序索引，如下MySQL 5.7 所示，c2字段降序，但是从show create table看c2仍然是升序。8.0可以看到，c2字段降序。

# MySQL 5.7

mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1\G

*************************** 1. row ***************************

       Table: t1

Create Table: CREATE TABLE `t1` (

  `c1` int(11) DEFAULT NULL,

  `c2` int(11) DEFAULT NULL,

  KEY `idx_c1_c2` (`c1`,`c2`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

 # MySQL 8.0

 mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

Query OK, 0 rows affected (0.06 sec)

mysql> show create table t1\G

*************************** 1. row ***************************

Table: t1 Create Table: CREATE TABLE `t1` (

　　`c1` int(11) DEFAULT NULL,

　　`c2` int(11) DEFAULT NULL,

　　KEY `idx_c1_c2` (`c1`,`c2` DESC)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC

1 row in set (0.00 sec)

再来看看降序索引在执行计划中的表现，在t1表插入10万条随机数据，查看select * from t1 order by c1 , c2 desc;的执行计划。从执行计划上可以看出，5.7的扫描数100113远远大于8.0的5行，并且使用了filesort。

DELIMITER ;;

CREATE PROCEDURE test_insert ()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE i<100000

DO

insert into t1 select rand()*100000, rand()*100000;

SET i=i+1;

END WHILE ;

commit;

END;;

DELIMITER ;

CALL test_insert();

# MySQL 5.7

mysql> explain select * from t1 order by c1 , c2 desc limit 5;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL | 100113 |   100.00 | Using index; Using filesort |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

1 row in set, 1 warning (0.00 sec)



# MySQL 8.0

mysql> explain select * from t1 order by c1 , c2 desc limit 5;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    5 |   100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

降序索引只是对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低，比如上述查询排序条件改为 order by c1 desc, c2 desc，这种情况下，5.7的执行计划要明显好于8.0的，如下：

# MySQL 5.7

mysql> explain select * from t1  order by c1 desc , c2 desc limit 5;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    5 |   100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

# MySQL 8.0

mysql> explain select * from t1 order by c1 desc , c2 desc limit 5;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL | 100429 |   100.00 | Using index; Using filesort |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

1 row in set, 1 warning (0.01 sec)

group by 不再隐式排序

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

# 表结构

mysql> show create table tb1\G

*************************** 1. row ***************************

       Table: tb1

Create Table: CREATE TABLE `tb1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) DEFAULT NULL,

  `group_own` int(11) DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC

1 row in set (0.00 sec)

# 表数据

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 |

+----+------+-----------+

7 rows in set (0.00 sec)

# 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 |

+-----------+-----------+

3 rows in set (0.00 sec)

# MySQL 8.0.13

mysql> select count(id), group_own from tb1 group by group_own;

+-----------+-----------+

| count(id) | group_own |

+-----------+-----------+

|         4 |         0 |

|         2 |         5 |

|         1 |         1 |

+-----------+-----------+

3 rows in set (0.00 sec)

# 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 |

+-----------+-----------+

3 rows in set (0.00 sec)

JSON特性增强

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

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

redo & undo 日志加密

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

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也会立即返回，只是返回的结果中不包含被锁定的行。

# session1:

mysql> begin;

mysql> select * from t1 where c1 = 2 for update;

+------+-------+

| c1   | c2    |

+------+-------+

|    2 | 60530 |

|    2 | 24678 |

+------+-------+

2 rows in set (0.00 sec)

# session2:

mysql> select * from t1 where c1 = 2 for update nowait;

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired  immediately and NOWAIT is set.

mysql> select * from t1 where c1 = 2 for update skip locked;

Empty set (0.00 sec)

增加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);

示例代码：

select /*+SET_VAR(sort_buffer_size = 16M)*/  studentno,studentname from student order by studentno desc ;  ##调整缓冲区

INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO student VALUES(80000,'GuoJing','郭靖2','男',5,'02088762106','1987-09-08','天津市河西区','GuoJing@sohu.com','1111112'); ##关闭外键约束

支持不可见索引

使用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;

通过隐藏索引可以了解该索引是否对性能有影响。如果隐藏了某个索引，优化器运行后性能没有索引，该索引即可进行删除或者其他变更操作。

支持直方图

优化器会利用column_statistics的数据，判断字段的值的分布，得到更准确的执行计划。

可以使用一下语句来收集或者删除直方图信息：

ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name]

直方图统计了表中某些字段的数据分布情况，为优化选择高效的执行计划提供参考，直方图与索引有着本质的区别，维护一个索引有代价。

每一次的insert、update、delete都会需要更新索引，会对性能有一定的影响。

而直方图一次创建永不更新，除非明确去更新它。所以不会影响insert、update、delete的性能。

# 添加/更新直方图

mysql> analyze table t1 update histogram on c1, c2 with 32 buckets;

+--------+-----------+----------+-----------------------------------------------+

| Table  | Op        | Msg_type | Msg_text                                      |

+--------+-----------+----------+-----------------------------------------------+

| db.t1 | histogram | status   | Histogram statistics created for column 'c1'. |

| db.t1 | histogram | status   | Histogram statistics created for column 'c2'. |

+--------+-----------+----------+-----------------------------------------------+

2 rows in set (2.57 sec)

# 删除直方图

mysql> analyze table t1 drop histogram on c1, c2;

+--------+-----------+----------+-----------------------------------------------+

| Table  | Op        | Msg_type | Msg_text                                      |

+--------+-----------+----------+-----------------------------------------------+

| db.t1 | histogram | status   | Histogram statistics removed for column 'c1'. |

| db.t1 | histogram | status   | Histogram statistics removed for column 'c2'. |

+--------+-----------+----------+-----------------------------------------------+

2 rows in set (0.13 sec)

新增innodb_dedicated_server参数

能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method三个参数。

日志分类更详细

在错误信息中添加了错误信息编号[MY-010311]和错误所属子系统[Server]

# MySQL 5.7

2018-06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

2018-06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

2018-06-08T09:07:20.117868+08:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

# MySQL 8.0

2018-06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv'  entry '@ root@localhost' ignored in --skip-name-resolve mode.

2018-06-21T17:53:13.040520+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv'  entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

2018-06-21T17:53:13.040542+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv'  entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

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表空间超过这个值，该表空间被标记为可回收。

增加资源组

MySQL 8.0新增了一个资源组功能，用于调控线程优先级以及绑定CPU核。
MySQL用户需要有 RESOURCE_GROUP_ADMIN权限才能创建、修改、删除资源组。
在Linux环境下，MySQL进程需要有 CAP_SYS_NICE 权限才能使用资源组完整功能。

[root@localhost~]# sudo setcap cap_sys_nice+ep /usr/local/mysql8.0/bin/mysqld

[root@localhost~]# getcap /usr/local/mysql8.0/bin/mysqld

/usr/local/mysql8.0/bin/mysqld = cap_sys_nice+ep

默认提供两个资源组，分别是USR_default，SYS_default

创建资源组：

create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;

将当前线程加入资源组：

SET RESOURCE GROUP test_resouce_group;

将某个线程加入资源组：

SET RESOURCE GROUP test_resouce_group FOR thread_id;

查看资源组里有哪些线程：

select * from Performance_Schema.threads where RESOURCE_GROUP='test_resouce_group';

修改资源组：

alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8;

删除资源组：

drop resource group test_resouce_group;

示例代码：

# 创建资源组

mysql>create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from RESOURCE_GROUPS;

+---------------------+---------------------+------------------------+----------+-----------------+

| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS |  THREAD_PRIORITY |

+---------------------+---------------------+------------------------+----------+-----------------+

| USR_default         | USER                |                      1 | 0-3      |                0 |

| SYS_default         | SYSTEM              |                      1 | 0-3      |                0 |

| test_resouce_group  | USER                |                      1 | 0-1      |                5 |

+---------------------+---------------------+------------------------+----------+-----------------+

3 rows in set (0.00 sec)

# 把线程id为60的线程加入到资源组test_resouce_group中，线程id可通过Performance_Schema.threads获取

mysql> SET RESOURCE GROUP test_resouce_group FOR 60;

Query OK, 0 rows affected (0.00 sec)

# 资源组里有线程时，删除资源组报错

mysql> drop resource group test_resouce_group;

ERROR 3656 (HY000): Resource group test_resouce_group is busy.

# 修改资源组

mysql> alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8;

Query OK, 0 rows affected (0.10 sec)

mysql> select * from RESOURCE_GROUPS;

+---------------------+---------------------+------------------------+----------+-----------------+

| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |

+---------------------+---------------------+------------------------+----------+-----------------+

| USR_default         | USER                |                      1 | 0-3      |               0 |

| SYS_default         | SYSTEM              |                      1 | 0-3      |               0 |

| test_resouce_group  | USER                |                      1 | 2-3      |               8 |

+---------------------+---------------------+------------------------+----------+-----------------+

3 rows in set (0.00 sec)

# 把资源组里的线程移出到默认资源组USR_default

mysql> SET RESOURCE GROUP USR_default FOR 60;

Query OK, 0 rows affected (0.00 sec)

# 删除资源组

mysql> drop resource group test_resouce_group;

Query OK, 0 rows affected (0.04 sec)

增加角色管理

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

# 创建角色

mysql> create role role_test;

Query OK, 0 rows affected (0.03 sec)

# 给角色授予权限

mysql> grant select on db.* to 'role_test';

Query OK, 0 rows affected (0.10 sec)

# 创建用户

mysql> create user 'read_user'@'%' identified by '123456';

Query OK, 0 rows affected (0.09 sec)

# 给用户赋予角色

mysql> grant 'role_test' to 'read_user'@'%';

Query OK, 0 rows affected (0.02 sec)

# 给角色role_test增加insert权限

mysql> grant insert on db.* to 'role_test';

Query OK, 0 rows affected (0.08 sec)

# 给角色role_test删除insert权限

mysql> revoke insert on db.* from 'role_test';

Query OK, 0 rows affected (0.10 sec)

# 查看默认角色信息

mysql> select * from mysql.default_roles;

+------+-----------+-------------------+-------------------+

| HOST | USER      | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |

+------+-----------+-------------------+-------------------+

| %    | read_user | %                 | role_test         |

+------+-----------+-------------------+-------------------+

1 row in set (0.00 sec)

# 查看角色与用户关系

mysql> select * from mysql.role_edges;

+-----------+-----------+---------+-----------+-------------------+

| FROM_HOST | FROM_USER | TO_HOST | TO_USER   | WITH_ADMIN_OPTION |

+-----------+-----------+---------+-----------+-------------------+

| %         | role_test | %       | read_user | N                 |

+-----------+-----------+---------+-----------+-------------------+

1 row in set (0.00 sec)

# 删除角色

mysql> drop role role_test;

Query OK, 0 rows affected (0.06 sec)

参考资料

官方开发手册：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

偏执的工匠：https://www.jianshu.com/p/be29467c2b0c

我俗人：https://blog.csdn.net/qq_32448349/article/details/81701355

菜鸟教程：mysql教程

码农公寓

性能

NoSQL的支持

默认字符集：utf8mb4

说明

查看字符集设置

工程中应用

MySQL8默认数据表引擎为：InnoDB

说明

mysql8支持的数据表引擎

自增变量持久化

自增主键重启重置问题

mysql8.0的解决方案

DDL事务支持

参数修改持久化

新增降序索引

group by 不再隐式排序

JSON特性增强

redo & undo 日志加密

innodb select for update跳过锁等待

增加SET_VAR语法

支持不可见索引

支持直方图

新增innodb_dedicated_server参数

日志分类更详细

undo空间自动回收

增加资源组

增加角色管理

参考资料

相关文章