MySQL面试宝典-8.0新特性

IT小Chen 2022-04-02

504

请简述几个熟悉的MySQL 8.0新特性？

一：系统表全部换成innodb表
二：自增变量持久化
三：原子DDL
四：参数修改持久化
五：新增降序索引
六：group by 不再隐式排序
七：redo & undo 日志加密
八：innodb select for update跳过锁等待
九：增加SET_VAR语法
十：支持不可见索引
十一：支持直方图
十二：日志分类更详细
十三：undo空间自动回收
十四：资源管理
十五：增加角色管理
十六：身份认证
十七：新增innodb_dedicated_server参数
十八：字符集
十九：窗口函数
二十：with语句
二十一：连接管理
二十二：HASH JOIN
二十三：加强EXPLAIN，定位sql性能更直观
二十四：账户与安全
二十五：重置root密码

一：系统表全部换成innodb表

系统表全部换成事务型的innodb表，默认的MySQL实例将不包含任何MyISAM表，除非手动创建MyISAM表。

# MySQL 5.7
mysql>select distinct(ENGINE) from information_schema.tables;
+--------------------+
|ENGINE              |
+--------------------+
|MEMORY              |
|InnoDB              |
|MyISAM              |
|CSV                 |
|PERFORMANCE_SCHEMA  |
|NULL                |
+--------------------+
6rows inset (0.00sec)
# MySQL 8.0
mysql>select distinct(ENGINE) from information_schema.tables;
+--------------------+
|ENGINE              |
+--------------------+
|NULL                |
|InnoDB              |
|CSV                 |
|PERFORMANCE_SCHEMA  |
+--------------------+
4rows inset (0.00sec)

二：自增变量持久化

在8.0之前的版本，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1。

这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。

现在自增列计数器会在每次值修改时，将值写到REDO LOG中，并且在CHECKPOINT时写到存储引擎私有的系统表中。这就消除了以往重启实例自增列不连续的问题。

自增主键重启重置的问题很早就被发现，一直到8.0才被解决，8.0版本将会对AUTO_INCREMENT值进行持久化，MySQL重启后，该值将不会改变。

三：原子DDL

InnoDB表的DDL支持事务完整性，要么成功要么回滚，将DDL操作回滚日志写入到data dictionary 数据字典表mysql.innodb_ddl_log 中用于回滚操作，该表是隐藏的表，通过show tables无法看到。通过设置参数，可将ddl操作日志打印输出到mysql错误日志中。

来看另外一个例子，库里只有一个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             |
+---------------+
1row inset(0.00sec)

mysql>drop table t1, t2;
ERROR 1051(42S02): Unknown table 'db.t2'

mysql>show tables;
Empty set(0.00sec)
# MySQL 8.0
mysql>show tables;
+---------------+
|Tables_in_db   |
+---------------+
|t1             |
+---------------+
1row inset(0.00sec)

mysql>drop table t1, t2;
ERROR 1051(42S02): Unknown table 'db.t2'

mysql>show tables;
+---------------+
|Tables_in_db   |
+---------------+
|t1             |
+---------------+
1row inset(0.00sec)

四：参数修改持久化

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"
  }
  }
  }
}

五：新增降序索引

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, 0rows affected (0.03sec)
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
1row in set (0.00sec)

# MySQL 8.0
mysql>create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0rows affected (0.06sec)
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
1row in set (0.00sec)

六：group by 不再隐式排序

mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。

# 表结构

mysql>show create table tb1\G
***************************1.row ***************************
  Table: tb1
CreateTable: CREATETABLE`tb1` (
  `id` int(11) NOTNULLAUTO_INCREMENT,
  `name` varchar(50) DEFAULTNULL,
  `group_own` int(11) DEFAULT'0',
  PRIMARYKEY(`id`)
) ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciROW_FORMAT=DYNAMIC
1row inset (0.00sec)

# 表数据

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             |
+----+------+-----------+
7rows inset (0.00sec)
 
# 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                |
+-----------+-----------+
3rows inset (0.00sec)
 
# 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                |
+-----------+-----------+
3rows inset (0.00sec)
 
# 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|
+-----------+-----------+
3rows inset (0.00sec)

七：redo和undo 日志加密

增加以下两个参数，用于控制redo、undo日志的加密。

innodb_undo_log_encrypt

innodb_redo_log_encrypt

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

九：增加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);

十：支持不可见索引

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

十一：支持直方图

优化器会利用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 32buckets;
# 删除直方图
mysql>analyze table t1 drop histogram on c1, c2;

十二：日志分类更详细

在错误信息中添加了错误信息编号[MY-010311]和错误所属子系统[Server]

# MySQL 5.7
2019-06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2019-06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-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
2019-06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv'  entry '@ root@localhost' ignored in --skip-name-resolve mode.
2019-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.
2019-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.

十三：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表空间超过这个值，该表空间被标记为可回收。

十四：资源管理

MySQL 8.0新增了一个资源组功能，用于调控线程优先级以及绑定CPU核。

MySQL用户需要有RESOURCE_GROUP_ADMIN权限才能创建、修改、删除资源组。

在Linux环境下，MySQL进程需要有 CAP_SYS_NICE 权限才能使用资源组完整功能。

十五：增加角色管理

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

# 创建角色
mysql>create role role_test;
QueryOK, 0rows affected (0.03sec)
 
# 给角色授予权限
mysql>grant select on db.*to 'role_test';
QueryOK, 0rows affected (0.10sec)
 
# 创建用户
mysql>create user 'read_user'@'%'identified by '123456';
QueryOK, 0rows affected (0.09sec)
 
# 给用户赋予角色
mysql>grant 'role_test'to 'read_user'@'%';
QueryOK, 0rows affected (0.02sec)
 
# 给角色role_test增加insert权限
mysql>grant insert on db.*to 'role_test';
QueryOK, 0rows affected (0.08sec)
 
# 给角色role_test删除insert权限
mysql>revoke insert on db.*from 'role_test';
QueryOK, 0rows affected (0.10sec)
 
# 查看默认角色信息
mysql>select *from mysql.default_roles;

# 查看角色与用户关系
mysql>select *from mysql.role_edges;

# 删除角色
mysql>drop role role_test;

十六：身份认证

从MySQL-8.0.3开始，引入了一个新的身份验证插件 caching_sha2_password。

从 MySQL-8.0.4开始，此插件成为MySQL服务器的新默认身份验证插件。

新的caching_sha2_password认证插件作为默认的认证插件，比mysql_native_password更安全，性能更好。但可能在现网会出现一个连接相关的问题。

为了提供更安全的密码加密，

MySQL8.0的首选默认认证插件是caching_sha2_password，而不是mysql_native_password

对于新的MySQL 8.0安装，还想使用之前的密码认证方式请使用：

ALTER USER 'root'@'localhost'
  IDENTIFIED WITH mysql_native_password
  BY 'password';

十七：新增innodb_dedicated_server参数

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

MySQL 8 中可以设置参数 innodb_dedicated_server=ON来让MySQL自动探测服务器的内存大小，根据内存大小设置innodb_buffer_pool_size, innodb_log_file_size 和 innodb_flush_method 三个参数。具体策略如下:

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。
innodb_dedicated_server=为OFF和ON时分别检查：
mysqladmin variables|grep -E 'innodb_buffer_pool_size|innodb_flush_method|innodb_log_file_size'
注意事项
innodb_dedicated_server=ON的情况下，如果还显式设置了 innodb_buffer_pool_size  innodb_log_file_size  innodb_flush_method 参数，显式设置的这些参数会优先生效。

十八：字符集

默认字符集从latin1变成了utf8mb4. 对于utf8mb4字符集增加了新的比较规则，比如utf8mb4_ja_0900_as_cs。

在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。

注：在Percona Server 8.0.15版本上测试，utf8仍然指向的是utf8mb3，与官方文档有出入。

十九：窗口函数

MySQL现在支持窗口函数，在一个查询中对每行进行计算。

这些函数包括RANK(), LAG(), NTILE()。另外，有几个聚合函数也能用作窗口函数。比如SUM(),AVG()。

二十：with语句

对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性

MySQL 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。

例如：

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;

二十一：连接管理

允许配置一个专门端口用于管理连接，当连接数打满时可以用于连接数据库进行管理。

需要设置admin_address，默认该值为空。

使用的端口默认为33062, 由admin_port来设置。

该端口的没有连接数的限制。

二十二：HASH JOIN

MySQL一直被人诟病没有实现Hash Join，直到8.0.18才带上了这个功能。

在8.0.18之前，MySQL只支持NestLoopJoin算法，最简单的就是Simple NestLoop Join，MySQL针对这个算法做了若干优化，实现了Block NestLoop Join，Index NestLoop Join和Batched Key Access等，有了这些优化，在一定程度上能缓解对HashJoin的迫切程度。

Hash Join算法

NestLoopJoin算法简单来说，就是双重循环，遍历外表(驱动表)，对于外表的每一行记录，然后遍历内表，然后判断join条件是否符合，进而确定是否将记录吐出给上一个执行节点。

从算法角度来说，这是一个M*N的复杂度。

HashJoin是针对equal-join场景的优化，基本思想是，将外表数据load到内存，并建立hash表，这样只需要遍历一遍内表，就可以完成join操作，输出匹配的记录。

如果数据能全部load到内存当然好，逻辑也简单，一般称这种join为CHJ(Classic Hash Join)，之前MariaDB就已经实现了这种HashJoin算法。

如果数据不能全部load到内存，就需要分批load进内存，然后分批join，下面具体介绍这几种join算法的实现。

二十三：加强EXPLAIN，定位sql性能更直观

MySQL8.0.18加强EXPLAIN，定位sql性能更直观

在mysql8.0.18版本之前，查看一个sql语句的执行计划，基本只能看到，sql是否走索引，使用哪个索引，是否是全部扫描，是否用到文件排序等等。

这样用表格方式展示，不直观，不是很方便去定位sql语句的性能瓶颈点，而且没有sql在每一步需要的cost，和预测执行时间。

经过mysql社区贡献，这些痛点都一一解决，现在新版本支持以tree方式展现，而且在关键步骤都有cost，可以非常方便，直观的看到sql哪一步有性能瓶颈。

例如：

EXPLAIN FORMAT=tree select a.name,a.depno,b.name from t_test1 a inner join t_test2 b on a.depno=b.depno;
EXPLAIN ANALYZE select a.name,a.depno,b.name from t_test1 a inner join t_test2 b on a.depno=b.depno;

二十四：账户与安全

在MySQL5.7的版本：

grant all privileges on *.* to '用户名'@'主机' identified by '密码'；

在MySQL8.0需要分开执行：

create user '用户名'@'主机' identified by '密码'；

grant all privileges on *.* to '用户名'@'主机'；

用以前的一条命令在8.0里面创建用户，会出现sql语法错误

二十五：重置root密码

skip-grant-tables的解法

首先，关闭实例，例如通过kill mysqld进程的方式。

使用--skip-grant-tables参数，重启实例

mysqld_safe --defaults-file=my.cnf --skip-grant-tables  --skip-networking &

设置了该参数，则实例在启动过程中会跳过权限表的加载，这就意味着任何用户都能登录进来，并进行任何操作，相当不安全。

建议同时添加--skip-networking参数。其会让实例关闭监听端口，自然也就无法建立TCP连接，而只能通过本地socket进行连接。

MySQL8.0就是这么做的，在设置了--skip-grant-tables参数的同时会自动开启--skip-networking。

修改密码

###5.7 修改密码方式###

mysql -S usr/local/mysql57/data/mysql.sock
update mysql.user set authentication_string=password('123456') where host='localhost' and user='root';
flush privileges;

###5.6 修改密码方式###

这里的update语句针对的是MySQL 5.7的操作，如果是在5.6版本，修改的应该是password字段，而不是authentication_string。

update mysql.user set password=password('123456') where host='localhost' and user='root';
flush privileges;

###8.0 修改密码方式###

而在MySQL 8.0.11版本中，这种方式基本不可行，因为其已移除了PASSWORD()函数及不再支持SET PASSWORD ... = PASSWORD ('auth_string')语法。

不难发现，这种方式的可移植性实在太差，三个不同的版本，就先后经历了列名的改变，及命令的不可用。

与上面不同的是，其会先通过flush privileges操作触发权限表的加载，再使用alter user语句修改root用户的密码，如：

mysql> alter user 'root'@'localhost' identified by '123';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

免密码登录进来后，直接执行alter user操作是不行的，因为此时的权限表还没加载。可先通过flush privileges操作触发权限表的加载，再执行alter user操作。

需要注意的是，通过alter user修改密码只适用于MySQL5.7和8.0，如果是MySQL 5.6，此处可写成

update mysql.user set password=password('123456') where host='localhost' and user='root';

不建议通过update的方式修改密码，更通用的其实是alter user。

更优雅的解法

相对于skip-grant-tables方案，我们来看看另外一种更优雅的解法，其只会重启一次，且基本上不存在安全隐患。

关闭实例，创建一个sql文件，写上密码修改语句

vim init.sql 
alter user 'root'@'localhost' identified by '123456';

使用--init-file参数，启动实例

mysqld_safe --defaults-file=my.cnf --init-file=/usr/local/mysql57/init.sql &

实例启动成功后，密码即修改完毕~

如果mysql实例是通过服务脚本来管理的，除了创建sql文件，整个操作可简化为一步。

service mysqld restart --init-file=/usr/local/mysql57/init.sql

注意：该操作只适用于/etc/init.d/mysqld这种服务管理方式，不适用于RHEL 7新推出的systemd。

参考：

https://www.cnblogs.com/ivictor/p/9243259.html
https://blog.csdn.net/weixin_43424368/article/details/104856242
https://zhuanlan.zhihu.com/p/136865089

mysql 面试

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL面试宝典-8.0新特性

评论