MySQL 8.0 新特性集锦
一、默认字符集由latin1变为utf8mb4
在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。
show variables like '%char%';
+--------------------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------------------+--------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------------------------------+
9 rows in set (0.01 sec)
二、参数修改持久化
MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。
例如执行：
mysql> set PERSIST expire_logs_days=10;
ERROR 3683 (HY000): The option expire_logs_days and binlog_expire_logs_seconds cannot be used together. Please use binlog_expire_logs_seconds to set the expire time (expire_logs_days is deprecated)
expire_logs_days参数已经不存在了，被
binlog_expire_logs_seconds取代，单位是秒
mysql> set PERSIST binlog_expire_logs_seconds=360000;
Query OK, 0 rows affected (0.06 sec)
系统会在数据目录下生成一个包含json格式的 mysqld-auto.cnf 的文件，格式化后如下所示，当 my.cnf 和 mysqld-auto.cnf 同时存在时，后者具有更高优先级。
[root@mysql8 mysql]# cat mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "binlog_expire_logs_seconds" : { "Value" : "360000" , "Metadata" : { "Timestamp" : 1532352224772238 , "User" : "root" , "Host" : "localhost" } } } }
将参数改回默认值：
mysql> set PERSIST binlog_expire_logs_seconds= DEFAULT;
Query OK, 0 rows affected (0.01 sec)
[root@mysql8 mysql]# cat mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "binlog_expire_logs_seconds" : { "Value" : "2592000" , "Metadata" : { "Timestamp" : 1532352748424756 , "User" : "root" , "Host" : "localhost" } } } }
如果不想重启时mysqld-auto.cnf生效，可以：
1、手动删除mysqld-auto.cnf
2、启动时指定persisted_globals_load=off
三、新增innodb_dedicated_server参数,默认off
能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method三个参数。
如果该参数开启，mysqld服务进程每次重启后都会自动调整上述三个参数值
如果三个参数中其一设为固定值，innodb_dedicated_server=on时，不影响其他两个参数自适应
取值策略：
innodb_buffer_pool_size:
<1G: 128M(innodb_dedicated_server=为OFF时的默认取值)
<=4G: 探测到的物理内存 * 0.5
>4G: 探测到的物理内存 * 0.75
innodb_log_file_size:
<1G: 48M(innodb_dedicated_server=为OFF时的默认取值)
<=4G: 128M
<=8G: 512M
<=16G: 1024M
>16G: 2G
innodb_flush_method:
如果系统允许设置为O_DIRECT_NO_FSYNC。
如果系统不允许，则设置为InnoDB默认的Flush method
mysql> show variables like 'innodb_flush_method';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_flush_method | fsync |
+---------------------+-------+
1 row in set (0.01 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> set default role all to 'read_user'@'localhost';
Query OK, 0 rows affected (0.05 sec)
# 查看默认角色信息mysql> select * from mysql.default_roles;
+-----------+-----------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+-----------+-----------+-------------------+-------------------+
| localhost | read_user | % | app_read |
+-----------+-----------+-------------------+-------------------+
1 row in set (0.00 sec)
# 查看角色与用户关系mysql> select * from mysql.role_edges;
+-----------+-----------+-----------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+-----------+-----------+-------------------+
| % | app_read | localhost | read_user | N |
| % | role_test | % | read_user | N |
+-----------+-----------+-----------+-----------+-------------------+
2 rows in set (0.00 sec)
# 查看某用户权限
mysql> show grants for read_user@localhost;
+-------------------------------------------------+
| Grants for read_user@localhost |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user`@`localhost` |
| GRANT `app_read`@`%` TO `read_user`@`localhost` |
+-------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for read_user@localhost using app_read;
+--------------------------------------------------------+
| Grants for read_user@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user`@`localhost` |
| GRANT SELECT ON `test`.`t1` TO `read_user`@`localhost` |
| GRANT `app_read`@`%` TO `read_user`@`localhost` |
+--------------------------------------------------------+
3 rows in set (0.00 sec)
mysql库新增管理表：default_roles，role_edges
五、 MyISAM系统表全部换成InnoDB表
系统表全部换成事务型的innodb表，默认的MySQL实例将不包含任何MyISAM表，除非手动创建MyISAM表。
mysql> select distinct(ENGINE) from information_schema.tables;
+--------------------+
| ENGINE |
+--------------------+
| NULL |
| InnoDB |
| CSV |
| PERFORMANCE_SCHEMA |
+--------------------+
4 rows in set (0.03 sec)
六、数据字典
mysql 8.0之前：
server层：.frm文件，存放表结构信息，.opt文件，记录了每个库的一些基本信息，包括库的字符集等信息，.TRN，.TRG文件用于存放触发器的信息内容。
引擎层：information_schema，存放数据库表名，表字段，访问权限等信息
mysql8.0：
1、将所有原先存放于数据字典文件中的信息，全部存放到数据库系统表中mysql.ibd，将之前版本的.frm,.opt,.par,.TRN,.TRG,.isl文件都移除了，不再通过文件的方式存储数据字典信息。
2、对INFORMATION_SCHEM，mysql，sys系统库中的存储引擎做了改进，原先使用MyISAM存储引擎的数据字典表都改为使用InnoDB存储引擎存放。从不支持事务的MyISAM存储引擎转变到支持事务的InnoDB存储引擎，为原子DDL的实现，提供了可能性。
新数据字典带来的影响：
1、INFORMATION_SCHEMA性能提升
数据库在查询INFORMATION_SCHEMA的表时，不再需要创建一张临时表，可以直接查询数据字典表
没有了frm文件
如果数据字典表中有索引也会合理利用索引
INFORMATION_SCHEMA下的STATISTICS表和TABLES表中的信息，8.0中通过缓存的方式，以提高查询的性能，先在缓存中找，没有就从存储引擎获取最新数据
information_schema_stats_expiry设置缓存数据的过期时间
2、原子ddl
原子DDL 操作步骤
1.准备：创建所需的对象并将DDL日志写入 mysql.innodb_ddl_log表中。DDL日志定义了如何前滚和回滚DDL操作。
2.执行：执行DDL操作。例如，为CREATE TABLE操作执行创建。
3.提交：更新数据字典并提交数据字典事务。
4.Post-DDL：重播并从mysql.innodb_ddl_log表格中删除DDL日志。为确保回滚可以安全执行而不引入不一致性，在此最后阶段执行文件操作（如重命名或删除数据文件）。这一阶段还从
mysql.innodb_dynamic_metadata的数据字典表删除的动态元数据为了DROP TABLE，TRUNCATE和其它重建表的DDL操作。
mysql> select * from mysql.innodb_ddl_log;
ERROR 3554 (HY000): Access to system table 'mysql.innodb_ddl_log' is rejected.
innodb_ddl_log是隐藏在mysql.ibd 数据字典表空间里的数据字典表，用于回滚操作，无法看到，通过设置参数，可将ddl操作日志打印输出到mysql错误日志中
mysql> set global log_error_verbosity=3;(MySQL 8.0 默认为2，error log 记录Errors and warnings，不记录notes)
mysql> set global innodb_print_ddl_logs=1;
mysql> drop table t1;
tail -f /var/log/mysqld.log
2018-07-23T16:17:10.286883Z 10 [Note] [MY-012475] [InnoDB] InnoDB: DDL log insert : [DDL record: DROP, id=8, thread_id=10, table_id=1059]
2018-07-23T16:17:10.286997Z 10 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=9, thread_id=10, space_id=2, old_file_path=./test/t1.ibd]
2018-07-23T16:17:10.297422Z 10 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 10
2018-07-23T16:17:10.297671Z 10 [Note] [MY-012479] [InnoDB] InnoDB: DDL log replay : [DDL record: DELETE SPACE, id=9, thread_id=10, space_id=2, old_file_path=./test/t1.ibd]
2018-07-23T16:17:10.323414Z 10 [Note] [MY-012479] [InnoDB] InnoDB: DDL log replay : [DDL record: DROP, id=8, thread_id=10, table_id=1059]
2018-07-23T16:17:10.326485Z 10 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 10
对比：InnoDB表的DDL支持事务完整性，要么成功要么回滚
mysql 5.7
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> drop table t1 ,t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;
Empty set (0.00 sec)
mysql 8.0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> drop table t1 ,t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
3、innodb_read_only对所有存储引擎生效
在8.0之前，innodb_read_only参数可以阻止对InnoDB存储引擎表的create和drop等更新操作。
但是在MySQL8.0中，开启innodb_read_only参数阻止了所有存储引擎的这些操作。create或者drop表的操作都需要更新数据字典表，8.0中数据字典表都改为了InnoDB存储引擎，所以对于数据字典表的更新会失败，从而导致各存储引擎create和drop表失败，像ANALYZE TABLE和ALTER TABLE tbl_name ENGINE=engine_name这种操作也会失败
4、mysqldump mysqlpump导出的内容影响
数据字典中proc表和event表都不再使用，并且定义触发器、存储过程的数据字典表不会被导出。所以在8.0中使用mysqldump、mysqlpump(5.7)导出数据的时候，如果需要导出触发器、存储过程等内容，一定需要加上--routines和--events选项。
5、手动mkdir的方式在数据目录下创建库目录，这种方式是不会被数据库识别到。
[root@mysql8 mysql]# mkdir app
[root@mysql8 mysql]# ll
drwxr-xr-x 2 mysql mysql 4096 Jul 24 01:45 app
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.01 sec)
6、DDL操作会花费更长的时间，因为之前的DDL操作是直接对.frm文件进行更改操作，只要写一个文件，现在是需要更新数据字典表，代表着需要将数据写到存储引擎、read log、undo log中。
七、自增变量持久化
在8.0之前的版本，InnoDB初始化AUTO_INCREMENT的方式，在每次重启时，总是算出表上最大的自增值作为最大值，下一次分配从该值开始。这意味着如果在btree右侧叶节点大量删除记录，重启后，自增值可能被重用。
例：5.7.14
mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
+----+
3 rows in set (0.00 sec)
删除id=4 的行数据，重启MySQL：
mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
[root@mha114 ~]# service mysqld restart
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql8.0将自增主键的计数器持久化到redo log，每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响，计数器写入到redo log中，并不会马上刷新
例：8.0
mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.08 sec)
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
+----+
3 rows in set (0.00 sec)
删除并重启
mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.08 sec)
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
[root@mysql8 ~]# service mysqld restart
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 5 |
+----+
3 rows in set (0.00 sec)
八、 索引
新增降序索引：
MySQL在语法上很早就已经支持降序索引，但实际上创建的仍然是升序索引，如下MySQL 5.7 所示，c2字段降序，但是从show create table看c2仍然是升序。8.0可以看到，c2字段降序。
MySQL 5.7
mysql> create table t2(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.04 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
KEY `idx_c1_c2` (`c1`,`c2`) --实际上还是按升序排列
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MySQL 8.0
mysql> create table t2(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.10 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`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
1 row in set (0.01 sec)
降序索引的效果：
在t2表插入10万条随机数据，
DELIMITER ;;
CREATE PROCEDURE test_insert () BEGIN DECLARE i INT DEFAULT 1;
WHILE i<100001 DO
insert into t2 select rand()*100000, rand()*100000;
SET i=i+1;END WHILE ;
commit;END;;
DELIMITER ;
CALL test_insert();
查看select * from t2 order by c1 , c2 desc;的执行计划。
MySQL 5.7
mysql> explain select * from t2 order by c1 , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | t2 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 100104 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql 8.0
mysql> explain select * from t2 order by c1 , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.15 sec)
从执行计划上可以看出，5.7的扫描数100104远远大于8.0的5行，并且使用了filesort。
降序索引只是对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低，比如上述查询排序条件改为 order by c1 desc, c2 desc，这种情况下，5.7的执行计划要明显好于8.0的，如下：
mysql 5.7
mysql> explain select * from t2 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 | t2 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql 8.0
mysql> explain select * from t2 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 | t2 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 100183 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
不可见索引（Invisible Indexes）：
MySQL8.0开始支持看不见的索引。不可见索引不会被优化器使用，但是通常是保持正常的。默认情况下索引是可见的。不可见索引可以测试删除某索引的效果，而不需要真的删除该索引。
将index设置为invisible，会导致优化器在选择执行计划时，自动忽略该索引，即便使用了FORCE INDEX，主键索引是不能被设为不可见索引的
mysql> CREATE TABLE t4 (i INT NOT NULL,j INT NOT NULL,UNIQUE j_idx (j)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.15 sec)
mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | NO | | NULL | |
| j | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.07 sec)
mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't4';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| j_idx | YES |
+------------+------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE t4 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible
虽然这个表没有明确的主键，但是在NOT NULL列j上的索引在行上放置了相同的约束，作为主键，不能被忽略
增加一个主键，如下：
mysql> ALTER TABLE t4 ADD PRIMARY KEY (i);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't4';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| j_idx | YES |
| PRIMARY | YES |
+------------+------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE t4 ALTER INDEX j_idx INVISIBLE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't4';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| j_idx | NO |
| PRIMARY | YES |
+------------+------------+
2 rows in set (0.00 sec)
mysql> explain select * from t4 order by j;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t4 | NULL | index | NULL | j_idx | 4 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> ALTER TABLE t4 ALTER INDEX j_idx INVISIBLE;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t4 order by j;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
九、 group by 不再隐式排序
mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。
mysql> create table t3(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t3 values(2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t3 values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 2 |
| 3 |
| 1 |
+------+
3 rows in set (0.00 sec)
mysql 5.7
mysql> select * from t3 group by id;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> explain select * from t3 group by id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql 8.0
mysql> select * from t3 group by id;
+------+
| id |
+------+
| 2 |
| 3 |
| 1 |
+------+
3 rows in set (0.02 sec)
mysql> explain select * from t3 group by id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
想要排序，需要加上order by：
mysql> select * from t3 group by id order by id;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> explain select * from t3 group by id order by id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
十、undo表空间
5.7开始,可以在线truncate undo tablespace
8.0：
独立undo tablespace被默认打开
参数innodb_undo_tablespaces，允许动态修改，调大会新建undo tablespace，设小就不使用多出来的undo tablespace但不会主动删除
undo表空间的命名：undoNNN变为undo_NNN，例：
-rw-r----- 1 mysql mysql 10485760 Jul 24 19:26 undo_001
-rw-r----- 1 mysql mysql 10485760 Jul 24 19:22 undo_002
回滚段的数量:8.0之前是128个，8.0开始每个undo表空间有128个，总共有innodb_rollback_segments*innodb_undo_tablespaces个
Innodb_undo_truncate参数默认打开，这意味着默认情况下，undo tablespace超过1GB（参数innodb_max_undo_log_size来控制）时，就会触发online truncate
支持undo tablespace加密，redo日志加密，innodb_redo_log_encrypt，innodb_redo_log_encrypt，默认关闭
十一、select for update、select for share
在5.7及之前的版本，select...for update，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时
MySQL8.0 InnoDB针对SELECT ... FOR SHARE(SELECT ... LOCK IN SHARE MODE)以及SELECT ... FOR UPDATE锁定读取语句，支持 NOWAIT和SKIP LOCKED选项。
NOWAIT如果请求的行被另一个事务锁定，则会立即报错返回。
SKIP LOCKED从结果集中删除锁定的行。
例：
session1：
mysql> select * from t3;
+------+
| id |
+------+
| 2 |
| 3 |
| 1 |
+------+
3 rows in set (0.01 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t3 WHERE id = 2 FOR UPDATE;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
session2：
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t3 WHERE id = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
mysql>
session3：
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t3 WHERE id = 2 FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)
mysql>
十二、日志分类更详细
在错误信息中添加了错误信息编号[MY-010116]和错误所属子系统[Server]
2018-07-24T10:19:35.626766Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 2495
2018-07-24T10:19:36.106177Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-07-24T10:19:36.131675Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.11' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2018-07-24T13:58:52.291528Z 0 [ERROR] [MY-012661] [InnoDB] InnoDB: Encryption can't find master key, please check the keyring plugin is loaded.
2018-07-24T13:58:52.298989Z 0 [ERROR] [MY-012877] [InnoDB] InnoDB: Can't set undo tablespace number 1 to be encrypted.
十三、JSON特性增强
MySQL 8.0 大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。
在主从复制中，新增参数 binlog_row_value_options，控制JSON数据的传输方式，允许对于Json类型部分修改，在binlog中只记录修改的部分，减少json大数据在只有少量修改的情况下，对资源的占用。
十四、增加SET_VAR语法
在sql语法中增加SET_VAR语法，动态调整部分参数，有利于提升语句性能。
select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order by id ;
insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);
十五、mysql 8.0支持窗口函数（分析函数）和CTE
mysql 8.0 中窗口函数的语法和表达逻辑和MSSQL、oracle、pgsql基本上完全一致
例子：
drop table if exists order_info
create table order_info
(
order_id int primary key,
user_no varchar(10),
amount int,
create_date datetime
);
insert into order_info values (1,'u0001',100,'2018-1-1');
insert into order_info values (2,'u0001',300,'2018-1-2');
insert into order_info values (3,'u0001',300,'2018-1-2');
insert into order_info values (4,'u0001',800,'2018-1-10');
insert into order_info values (5,'u0001',900,'2018-1-20');
insert into order_info values (6,'u0002',500,'2018-1-5');
insert into order_info values (7,'u0002',600,'2018-1-6');
insert into order_info values (8,'u0002',300,'2018-1-10');
insert into order_info values (9,'u0002',800,'2018-1-16');
insert into order_info values (10,'u0002',800,'2018-1-22');
mysql> select * from order_info;
+----------+---------+--------+---------------------+
| order_id | user_no | amount | create_date |
+----------+---------+--------+---------------------+
| 1 | u0001 | 100 | 2018-01-01 00:00:00 |
| 2 | u0001 | 300 | 2018-01-02 00:00:00 |
| 3 | u0001 | 300 | 2018-01-02 00:00:00 |
| 4 | u0001 | 800 | 2018-01-10 00:00:00 |
| 5 | u0001 | 900 | 2018-01-20 00:00:00 |
| 6 | u0002 | 500 | 2018-01-05 00:00:00 |
| 7 | u0002 | 600 | 2018-01-06 00:00:00 |
| 8 | u0002 | 300 | 2018-01-10 00:00:00 |
| 9 | u0002 | 800 | 2018-01-16 00:00:00 |
| 10 | u0002 | 800 | 2018-01-22 00:00:00 |
+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)
练习：查询求每个用户的最新的一个订单
SELECT * FROM
(
SELECT IF(@y=a.user_no, @x:=@x+1, @x:=1) X ,
IF(@y=a.user_no, @y, @y:=a.user_no) Y,
a.*
FROM order_info a, (SELECT @x:=0, @y:=NULL) b
ORDER BY a.user_no, a.create_date desc
) c
WHERE X <= 1;
+------+-------+----------+---------+--------+---------------------+
| X | Y | order_id | user_no | amount | create_date |
+------+-------+----------+---------+--------+---------------------+
| 1 | u0001 | 5 | u0001 | 900 | 2018-01-20 00:00:00 |
| 1 | u0002 | 10 | u0002 | 800 | 2018-01-22 00:00:00 |
+------+-------+----------+---------+--------+---------------------+
2 rows in set (0.00 sec)
使用窗口函数：
select * from
(
select row_number()over(partition by user_no order by create_date desc) as row_num,
order_id,user_no,amount,create_date
from order_info
)t where row_num=1;
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date |
+---------+----------+---------+--------+---------------------+
| 1 | 5 | u0001 | 900 | 2018-01-20 00:00:00 |
| 1 | 10 | u0002 | 800 | 2018-01-22 00:00:00 |
+---------+----------+---------+--------+---------------------+
2 rows in set (0.02 sec)
CTE：
CTE有两种用法，非递归的CTE和递归的CTE。
非递归的CTE可以用来增加代码的可读性，增加逻辑的结构化表达。
例：
with cte as
(
select row_number()over(partition by user_no order by create_date desc) as row_num,
order_id,user_no,amount,create_date
from order_info
)
select * from cte where row_num=1;
递归的CTE:比如查询大部门下的子部门，每一个子部门下面的子部门等等，就需要使用递归的方式。
例：
WITH RECURSIVE cte_count (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1
FROM cte_count
WHERE n < 3
)
SELECT n FROM cte_count;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
十六、支持直方图
可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 来收集或者删除直方图信息。
直方图统计了表中某些字段的数据分布情况，计算字段选择性,为优化选择高效的执行计划提供参考，直方图与索引有着本质的区别，维护一个索引有代价。每一次的insert、update、delete都会需要更新索引，会对性能有一定的影响。而直方图一次创建永不更新，除非明确去更新它。所以不会影响insert、update、delete的性能。
相关参数：
histogram_generation_max_mem_size创建直方图时允许使用的最大内存数
# 添加/更新直方图mysql> analyze table t2 update histogram on c1, c2 with 32 buckets;
+---------+-----------+----------+-----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-----------+----------+-----------------------------------------------+
| test.t2 | histogram | status | Histogram statistics created for column 'c1'. |
| test.t2 | histogram | status | Histogram statistics created for column 'c2'. |
+---------+-----------+----------+-----------------------------------------------+
2 rows in set (2.29 sec)
# 删除直方图
mysql> analyze table t2 drop histogram on c1, c2;
+---------+-----------+----------+-----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-----------+----------+-----------------------------------------------+
| test.t2 | histogram | status | Histogram statistics removed for column 'c1'. |
| test.t2 | histogram | status | Histogram statistics removed for column 'c2'. |
+---------+-----------+----------+-----------------------------------------------+
2 rows in set (0.04 sec)
mysql> desc COLUMN_STATISTICS;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| SCHEMA_NAME | varchar(64) | NO | | NULL | |
| TABLE_NAME | varchar(64) | NO | | NULL | |
| COLUMN_NAME | varchar(64) | NO | | NULL | |
| HISTOGRAM | json | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
十七、增加资源组
MySQL 8.0新增了一个资源组功能，用于调控线程优先级以及绑定CPU核。
MySQL用户需要有 RESOURCE_GROUP_ADMIN权限才能创建、修改、删除资源组。
在Linux环境下，MySQL进程需要有 CAP_SYS_NICE 权限才能使用资源组完整功能。
在MySQL 8.0之前，所有的线程(Master Thread、IO Thread， Purge Thread等)都是同等优先级的，官方的版本是不可以修改线程的优先级的，也没法将线程绑定到特定的cpu核上。
8.0 可以改变线程的优先级，绑定vcpu(逻辑cpu)
(在某些业务场景下，我们希望可以来做干涉，例如，白天业务高峰的时候，让用户线程的优先级更高，而到晚上做批量处理的时候，让负责批量处理的线程优先级更高，等等，另外，也希望可以给特定的线程来绑定cpu核，来保证服务质量等…)
优先级：-20..0 for SYSTEM 类型线程, 0..19 for USER类型线程，数字越小优先级越高
查看vcpu编号：
[root@mysql8 mysql]# cat /proc/cpuinfo
processor: 0
资源组使用方法：
创建一个叫sql_thread的资源组，将编号0的逻辑CPU指定到这个组，并将这个组的优先级设为1
mysql> CREATE RESOURCE GROUP sql_thread TYPE = USER VCPU = 0 THREAD_PRIORITY = 1;
Query OK, 0 rows affected (0.04 sec)
将mysql中threadId=46的线程加入到sql_thread资源组(Thread的id值需要查询
Performance_Schema.threads的Thread_Id字段)
mysql> SET RESOURCE GROUP sql_thread FOR 46;
Query OK, 0 rows affected (0.02 sec)
修改资源组sql_thread绑定的逻辑CPU为编号5，6，并降低优先级到-5
ALTER RESOURCE GROUP sql_thread VCPU = 5, 6 THREAD_PRIORITY = -5;
查看资源组里有哪些线程：
mysql> select * from performance_schema.threads where RESOURCE_GROUP='sql_thread';
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+----------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP |
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+----------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
| 46 | thread/sql/one_connection | FOREGROUND | 9 | root | localhost | information_schema | Query | 0 | Sending data | select * from performance_schema.threads where RESOURCE_GROUP='sql_thread' | NULL | NULL | YES | YES | Socket | 2538 | sql_thread |
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+----------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
1 row in set (0.01 sec)
删除资源组 ：
mysql> drop resource group sql_thread;
ERROR 3656 (HY000): Resource group sql_thread is busy.
如果删除资源组时该资源组里有线程，就会报错
先将线程移入默认资源组：
mysql> SET RESOURCE GROUP USR_default FOR 46;
Query OK, 0 rows affected (0.00 sec)
mysql> drop resource group sql_thread;
Query OK, 0 rows affected (0.04 sec)
查看资源组信息：
mysql> select * from performance_schema.RESOURCE_GROUPS;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default | USER | 1 | 0-0 | 0 |
| SYS_default | SYSTEM | 1 | 0-0 | 0 |
| sql_thread | USER | 1 | 0 | 1 |
+---------------------+---------------------+------------------------+----------+-----------------+
3 rows in set (0.02 sec)
mysql 8.0 默认提供两个资源组，分别是USR_default，SYS_default
SYSTEM类型的Threads默认使用的是SYS_default资源组，USER类型的Threads默认使用的是USER_default资源组
十八、复制
5.6 基于schema的并行复制
5.7 基于Group Commit 的并行复制：必须是在主上并行提交的事务才能在从上并行回放
8.0 writeset 通过检测事务在运行过程中是否存在写冲突来决定从机上的回放顺序，这使得从机上的并发程度不再依赖于主机。
writeset在5.7.20的group commit中已经应用，应用在certify阶段：
在一个certify阶段，通过writeset的方式验证事务之间是否存在冲突，同时，在写relaylog时将没有冲突的事务的last_committed值设为相同的值
mysql复制参数：
binlog_transaction_depandency_tracking：
COMMIT_ORDERE：默认的，表示继续使用5.7中的基于组提交的方式决定事务的依赖关系;
WRITESET:表示使用写集合来决定事务的依赖关系;
WRITESET_SESSION:表示使用 WriteSet 来决定事务的依赖关系，但是同一个Session内的事务不会有相同的 last_committed 值。
Binlog_format=row
如果两个事务修改了不同行的数据，由于主键不同，就可以并发执行。在此时执行就会有一个明显的特点。当master上只有一个线程的时候，在slave上的并发执行性能会比较高，比master上会高很多
writeset 依赖于主键和唯一索引