MySQL8.0新特性有哪些

25次阅读

没有评论

共计 15755 个字符，预计需要花费 40 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关 MySQL8.0 新特性有哪些的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考。一起跟随丸趣 TV 小编过来看看吧。

1、默认字符集由 latin1 变为 utf8mb4

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

（推荐：MySQL 教程）

2. MyISAM 系统表全部换成 InnoDB 表

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

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

3. 自增变量持久化

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

4. DDL 原子化

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

mysql set global log_error_verbosity=3;
mysql set global innodb_print_ddl_logs=1;
mysql create table t1(c int) engine=innodb;
# MySQL 错误日志:
2018-06-26T11:25:25.817245+08:00 44 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=41, thread_id=44, space_id=6, old_file_path=./db/t1.ibd]
2018-06-26T11:25:25.817369+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 41
2018-06-26T11:25:25.819753+08:00 44 [Note] [MY-012477] [InnoDB] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=42, thread_id=44, table_id=1063, new_file_path=db/t1]
2018-06-26T11:25:25.819796+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 42
2018-06-26T11:25:25.820556+08:00 44 [Note] [MY-012472] [InnoDB] InnoDB: DDL log insert : [DDL record: FREE, id=43, thread_id=44, space_id=6, index_id=140, page_no=4]
2018-06-26T11:25:25.820594+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 43
2018-06-26T11:25:25.825743+08:00 44 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 44
2018-06-26T11:25:25.825784+08:00 44 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 44

来看另外一个例子，库里只有一个 t1 表，drop table t1,t2; 试图删除 t1,t2 两张表, 在 5.7 中，执行报错，但是 t1 表被删除，在 8.0 中执行报错，但是 t1 表没有被删除，证明了 8.0 DDL 操作的原子性，要么全部成功，要么回滚。

# MySQL 5.7
mysql show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql drop table t1, t2;
ERROR 1051 (42S02): Unknown table db.t2 
mysql show tables;
Empty set (0.00 sec)
# MySQL 8.0
mysql show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql drop table t1, t2;
ERROR 1051 (42S02): Unknown table db.t2 
mysql show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)

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 
}

6. 新增降序索引

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

7. 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.11
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)

8. JSON 特性增强

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

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

9. redo undo 日志加密

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

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

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

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

13. 支持直方图

优化器会利用 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)

14. 新增 innodb_dedicated_server 参数

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

15. 日志分类更详细

在错误信息中添加了错误信息编号 [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.

16. 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 表空间超过这个值，该表空间被标记为可回收。17. 增加资源组

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)

18. 增加角色管理

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

# 创建角色
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)

感谢各位的阅读！关于 MySQL8.0 新特性有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！

正文完