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表不会被删除,保证了原子性。