MySQL8.0新特性 – 老迟笔记

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

在5.6、5.7版本中，默认字符集均为latin1，在8.0版本中，默认字符集已经变为utf8mb4

2、系统表不再使用MyISAM和MEMORY

执行select distinct(ENGINE) from information_schema.tables;获取当前系统表使用到的存储引擎。

在5.6、5.7版本中查询结果为：MEMORY、InnoDB、MyISAM、CSV、PERFORMANCE_SCHEMA

在8.0版本中查询结果为：InnoDB、PERFORMANCE_SCHEMA、CSV

可以看到，MySQL8.0版本中，系统表存储引擎已经取消了使用MyISAM和MEMORY。

3、支持在线修改全局参数并持久化

通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。

例如执行：

# 8.0版本中，设置binlog日志过期时间为7天
set PERSIST binlog_expire_logs_seconds=604800;

系统会在数据目录下生成mysqld-auto.cnf 文件，该文件内容是以json格式存储的。当my.cnf 和mysqld-auto.cnf 同时存在时，后者优先级更高。

# mysqld-auto.cnf 文件
{
    "binlog_expire_logs_seconds": {
        "Value": "604800",
        "Metadata": {
            "Timestamp": 1629252257908278,
            "User": "root",
            "Host": ""
        }
    }
}

4、binlog日志过期时间精确到秒，之前是天，并且参数名称发生变化。

在8.0版本之前，binlog日志过期时间设置都是设置expire_logs_days参数，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。

5、新增降序索引

MySQL在语法上很早就已经支持降序索引，但实际上创建的仍然是升序索引，但在8.0版本中，这种情况得到消除，创建的是降序索引，实际上就是降序索引。

不过值得注意的是，创建降序索引需要严格考虑业务场景，否则创建降序索引可能效率还不如正常的升序索引。

# MySQL 5.7
# 创建表
create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
# 查看表结构
CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# MySQL 5.7
# 创建表
create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
CREATE TABLE `t1` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

6、支持隐式索引

在 MySQL 8.0 中，索引可以被“隐藏”和“显示”。

当对索引进行隐藏时，它不会被查询优化器所使用。我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉。

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

7、JSON特性增强

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

8、窗口函数(Window Functions)

从 MySQL 8.0 开始，新增了一个叫窗口函数的概念。

它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种聚合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中。即窗口函数不需要 GROUP BY

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

10、支持直方图

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

可以使用ANALYZE TABLE table_name [UPDATE HISTOGRAM on colume_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 32buckets;
+--------+-----------+----------+-----------------------------------------------+
|Table  |Op |Msg_type|Msg_text  |
+--------+-----------+----------+-----------------------------------------------+
|db.t1 |histogram|status  |Histogramstatistics created forcolumn 'c1'. |
|db.t1 |histogram|status  |Histogramstatistics created forcolumn 'c2'. |
+--------+-----------+----------+-----------------------------------------------+
2rows inset (2.57sec)

# 删除直方图
mysql>analyze table t1 drop histogram on c1, c2;
+--------+-----------+----------+-----------------------------------------------+
|Table  |Op |Msg_type|Msg_text  |
+--------+-----------+----------+-----------------------------------------------+
|db.t1 |histogram|status  |Histogramstatistics removed forcolumn 'c1'. |
|db.t1 |histogram|status  |Histogramstatistics removed forcolumn 'c2'. |
+--------+-----------+----------+-----------------------------------------------+
2rows inset (0.13sec)

11、新增innodb_dedicated_server参数

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

12、新增角色管理

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

# 创建角色
mysql> create role 'test_role';
Query OK, 0 rows affected (0.00 sec)

# 给角色授予权限
mysql> grant select on *.* to test_role;
Query OK, 0 rows affected (0.01 sec)

# 创建用户
mysql> create user 'read_user'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

# 给用户赋予角色
mysql> grant 'test_role' to 'read_user';
Query OK, 0 rows affected (0.00 sec)

# 给角色增加insert权限
mysql> grant insert on *.* to 'test_role';
Query OK, 0 rows affected (0.00 sec)

# 移除角色insert权限
mysql> revoke insert on *.* from 'test_role';
Query OK, 0 rows affected (0.01 sec)

# 查看角色与用户关系
mysql>select *from mysql.role_edges;
+-----------+-----------+---------+-----------+-------------------+
|FROM_HOST|FROM_USER|TO_HOST|TO_USER  |WITH_ADMIN_OPTION|
+-----------+-----------+---------+-----------+-------------------+
|%  |role_test |%  |read_user |N  |
+-----------+-----------+---------+-----------+-------------------+
1row inset (0.00sec)

# 查看用户当前拥有的角色
mysql> show grants for 'read_user';
+------------------------------------------------------------+
| Grants for read_user@%                                     |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user`@`%`                      |
| GRANT `insert_role`@`%`,`test_role`@`%` TO `read_user`@`%` |
+------------------------------------------------------------+

# 删除角色
mysql>drop role test_role;
QueryOK, 0rows affected (0.06sec)

注：在8.0版本之前，比如5.6和5.7版本，仅支持针对单个用户设置权限，而没有角色的概念。

13、默认认证插件更改为caching_sha2_password

5.7版本中默认认证插件是mysql_native_password，在8.0版本中默认的认证插件变为了caching_sha2_password。

可以使用命令show variables like 'default_authentication_plugin%';来查询MySQL默认的认证插件。

对于8.0之前的版本，只要用户的密码一样，加密出的密文串也是一样的。但在8.0版本中，即使两个用户的密码是相同的，但加密出的密文串也不会相同。

在这点上，降低了用户密码被破解的可能性。

14、新增密码记录功能

该功能可以记录用户使用该密码的次数，控制相同密码的使用次数。借此可以强制用户定期修改密码

15、InnoDB DDl语句支持原子操作

MySQL5.7执行drop命令 drop table t1,t2; 如果t1存在，t2不存在，会提示t2表不存在，但是t1表仍然会被删除。

MySQL8.0执行同样的drop命令，会提示t2表不存在，而且t1表不会被删除，保证了原子性。

打赏赞