MySQL8.0新特性的总结（附代码）
发布时间：08/01 来源：未知 浏览：
关键词：
本篇文章给大家带来的内容是对于MySQL8.0新特性的总结（附代码），有一定的参照 价值，有需要的伴侣可以参照 一下，但愿对你有所帮忙。
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 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)
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新特性的总结（附代码）的细致内容，更多请关注 百分百源码网 其它相干文章！
