升级你的MySQL吧，感受下8.0.30 or Higher新特性

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

前言

MySQL 8.0.30 or Higher发布后，总结的一些新特性分享给大家

---

📣 1.安装MySQL 8.0.31

本次采用yum在线安装部署

📢 1.1 repo下载

[root@jeames ~]# rpm -Uvh https://repo.mysql.com//mysql80-community-release-el8.rpm
Retrieving https://repo.mysql.com//mysql80-community-release-el8.rpm
warning: /var/tmp/rpm-tmp.SiEZMj: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql80-community-release-el8-4  ################################# [100%]
1
2
3
4
5
6
7

📢 1.2 查询yum里的MySQL版本

[root@jeames ~]# yum repolist all | grep mysql

Failed to set locale, defaulting to C.UTF-8
mysql-cluster-8.0-community           MySQL Cluster 8.0 Community       disabled
mysql-cluster-8.0-community-debuginfo MySQL Cluster 8.0 Community - Deb disabled
mysql-cluster-8.0-community-source    MySQL Cluster 8.0 Community - Sou disabled
mysql-connectors-community            MySQL Connectors Community        enabled
mysql-connectors-community-debuginfo  MySQL Connectors Community - Debu disabled
mysql-connectors-community-source     MySQL Connectors Community - Sour disabled
mysql-tools-community                 MySQL Tools Community             enabled
mysql-tools-community-debuginfo       MySQL Tools Community - Debuginfo disabled
mysql-tools-community-source          MySQL Tools Community - Source    disabled
mysql-tools-preview                   MySQL Tools Preview               disabled
mysql-tools-preview-source            MySQL Tools Preview - Source      disabled
mysql80-community                     MySQL 8.0 Community Server        enabled
mysql80-community-debuginfo           MySQL 8.0 Community Server - Debu disabled
mysql80-community-source              MySQL 8.0 Community Server - Sour disabled
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

📢 1.3 配置安装的MySQL的版本

[root@jeames ~]# yum -y install yum-utils
[root@jeames ~]# yum-config-manager --enable mysql80-community

#查询安装的MySQL的版本
[root@jeames ~]# yum repolist enabled | grep mysql
mysql-connectors-community MySQL Connectors Community
mysql-tools-community      MySQL Tools Community
mysql80-community          MySQL 8.0 Community Server
1
2
3
4
5
6
7
8

📢 1.4 安装 MySQL

先执行：yum module disable mysql
再执行：yum -y install mysql-community-server

#初始化MySQL
[root@jeames ~]# systemctl start mysqld
#查看MySQL状态
[root@jeames ~]# systemctl status mysqld
1
2
3
4
5
6
7

📢 1.5 登陆MySQL

#查看临时密码
[root@jeames ~]# grep 'temporary password' /var/log/mysqld.log

#登陆MySQL后修改密码,记得一定要用identified with mysql_native_password
[root@jeames ~]# mysql -uroot -p
mysql> ALTER USER root@'localhost' identified with mysql_native_password BY '********';
mysql> ALTER USER root@'%' identified with mysql_native_password BY '********';
mysql> grant all on *.* to root@'%' with grant option;
mysql> flush privileges;
mysql> status
1
2
3
4
5
6
7
8
9
10

📣 2.8.0.30 or Higher新特性

📢 2.1 Redo Log

✨✨ innodb_redo_log_capacity参数

在MySQL 8.0.30中，innodb_redo_log_capacity系统变量控制重做日志文件占用的磁盘空间量。
可以在启动或运行时使用set GLOBAL语句在选项文件中设置此变量；
例如，以下语句将重做日志容量设置为8GB：
SET GLOBAL innodb_redo_log_capacity = 8589934592;

说明：
innodb_redo_log_capacity变量取代了已弃用的innodb_ log_files_in_group和innodb _log_file_size变量。
定义innodb_redo_log_capacity设置时，将忽略innodb _log_files_in_group和innodb_ log_file_size设置，
否则，这些设置将用于计算innodb_redo_log_capacity设置

innodb.log_files_in_group*innodblog_file_size=innodb_do_log_capacity

如果没有设置这些变量，则重做日志容量将设置为innodb_redo_log_capacity默认值，即104857600字节（100MB）。
最大重做日志容量为128GB
1
2
3
4
5
6
7
8
9
10
11
12
13
14

✨✨ 重做日志文件

在MySQL 8.0.30之前，InnoDB默认在数据目录中创建两个重做日志文件，
分别名为ib_logfile0和ib_logfile1，并以循环方式写入这些文件。

重做日志文件使用#ib_redoN命名约定，其中N是重做日志的文件号。备用重做日志文件由_tmp后缀表示。
下面的示例显示了#innodb_redo目录中的重做日志文件，其中有1个活动重做日志和31个备用重做日志，按顺序编号。

除非innodb_log_group_home_dir变量指定了不同的目录，否则重做日志文件位于数据目录的#innodb_ Redo目录中。
如果定义了innodb_log_group_home_dir，则重做日志文件位于该目录中的#innodb_ redo目录中。
有两种类型的重做日志文件，普通和备用。普通的重做日志文件就是正在使用的那些文件。
备用重做日志文件是那些等待使用的文件。
InnoDB尝试维护总共32个重做日志文件，每个文件的大小等于1/32*InnoDB_redo_log_capacity；

mysql> select @@innodb_log_group_home_dir;
+-----------------------------+
| @@innodb_log_group_home_dir |
+-----------------------------+
| ./                          |
+-----------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mysql> SELECT FILE_NAME, START_LSN, END_LSN FROM performance_schema.innodb_redo_log_files;
+--------------------------+-----------+----------+
| FILE_NAME                | START_LSN | END_LSN  |
+--------------------------+-----------+----------+
| ./#innodb_redo/#ib_redo6 |  19656704 | 22931456 |
+--------------------------+-----------+----------+

说明：
1.每个普通重做日志文件都与特定范围的LSN值相关联，以上查询显示了列出的活动重做日志文件的START_LSN和END_LSN值
2.执行检查点时，InnoDB将检查点LSN存储在包含该LSN的文件的头中，
在恢复期间，将检查所有重做日志文件，并从最新的检查点LSN开始恢复。
1
2
3
4
5
6
7
8
9
10
11

📢 2.2 GIPK

从MySQL 8.0.30开始，MySQL支持在GIPK模式下运行时生成的不可见主键。
在这种模式下运行时，对于任何没有显式主键创建的InnoDB表，MySQL服务器会自动向表中添加生成的不可见主键（GIPK）

新版本为我们提供了一个令人惊喜的特性 -（Generated Invisible Primary Keys）简称GIPK 。
一句概况就是: 当开启GIPK模式后，MySQL 会在没有显示定义主键的InnoDB表上自动生成不可见的主键。

如果没有主键，遇到load data，大事务，ddl 等有大量表数据行扫描的行为时，
会带来严重的主从延迟，给数据库稳定性和数据一致性带来隐患，那么GIPK解决了这个问题。

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+

说明：
GIPK模式由sql_generate_invisible_primary_key服务器系统变量控制。
默认情况下，该变量的值为OFF，这意味着禁用了GIPK模式；要启用GIPK模式，请将变量设置为ON
1
2
3
4
5
6
7
8
9
10

接下来就演示下GIPK的特性

##GIPK模式默认关闭
mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> use jeames
mysql> CREATE TABLE auto_n1 (c1 VARCHAR(50), c2 INT);

##开启GIPK模式
mysql> SET sql_generate_invisible_primary_key=ON;

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

mysql> CREATE TABLE auto_n2 (c1 VARCHAR(50), c2 INT);

## 使用SHOW CREATE TABLE查看表实际创建方式的差异

mysql> SHOW CREATE TABLE auto_n1\G
*************************** 1. row ***************************
       Table: auto_n1
Create Table: CREATE TABLE `auto_n1` (
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE auto_n2\G
*************************** 1. row ***************************
       Table: auto_n2
Create Table: CREATE TABLE `auto_n2` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

说明：
1.由于auto_n2没有用于创建它的CREATE TABLE语句指定的主键，因此GIPK模式使MySQL将不可见的列my_row_id和该列上的主键添加到此表中。
由于在创建auto_n1时禁用了GIPK模式，因此未在该表上执行此类添加。

2.当服务器以GIPK模式将主键添加到表中时，列和键名称始终为my_row_id。
因此，当启用GIPK模式时，除非表创建语句还指定了显式主键，否则不能创建具有名为my_row_id的列的表。
（在这种情况下，不需要为列或键命名my_row_id。）

## VISIBLE和INVISIBLE之间切换
当GIPK模式生效时，生成的主键不能更改，只能在VISIBLE和INVISIBLE之间切换。
要使auto_n2上生成的不可见主键可见，请执行以下ALTER TABLE语句：

mysql> ALTER TABLE auto_n2 ALTER COLUMN my_row_id SET VISIBLE;

mysql>  SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "auto_n2";

要使生成的主键再次不可见，请执行
ALTER TABLE auto_1 ALTER COLUMN my_row_id SET invisible。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

创建或导入使用GIPK模式的安装备份时，可以排除生成的不可见PK列和值。
mysqldump的–skip生成的不可见主键选项会导致GIPK信息被排除在程序的输出中。
如果要导入包含GIPK键和值的转储文件，还可以使用mysqlpump中的–skip生成的不可见主键来抑制这些键和值（从而不导入）。

小伙伴们是不是感觉这个特新很棒

📢 2.3 多级别的 ORDER BY or LIMIT

在 MySQL 8.0.31 之前，带括号的查询表达式不允许多个级别的 ORDER BY or LIMIT 操作，查询会被拒绝。
在 MySQL 8.0.31 及更高版本中，取消了此限制，并允许嵌套的带括号的查询表达式。
支持的最大嵌套级别为 63；这是在解析器执行任何简化或合并之后。

以下是示例:

mysql> (SELECT 'a' UNION SELECT 'b' LIMIT 2) LIMIT 3;

( 
  (SELECT a, b, c FROM t ORDER BY a LIMIT 3) ORDER BY b LIMIT 2
) ORDER BY c LIMIT 1;
1
2
3
4
5

📢 2.4 innodb_doublewrite

系统变量支持 DETECT_ONLY 和 DETECT_AND_RECOVER 设置。
使用该 DETECT_ONLY设置，数据库页面内容不会写入双写缓冲区，并且恢复不会使用双写缓冲区来修复不完整的页面写入。
此轻量级设置仅用于检测不完整的页面写入。
该 DETECT_AND_RECOVER 设置等同于现有ON 设置。

📢 2.5 mysqldump

mysqldump 执行全表扫描，这意味着它的查询通常会超过 long_query_time
对常规查询有用的设置。从 MySQL 8.0.30 开始，如果要从慢查询日志中排除大部分或全部 mysqldump 产生的慢查询，
可以设置 mysqldump 的 --mysqld-long-query-time
命令行选项，将系统变量的 session 值更改为更高的值。