MySQL8.0新特征的总结（附代码）【MySQL教程】,mysql

本篇文章给人人带来的内容是关于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新特征的总结（附代码）的细致内容，更多请关注ki4网别的相干文章！

正文