MySQL8使用不可见的列(invisible column)来避免复制延迟

​MySQL基于行的复制经常遇到的问题之一：因为没有主键导致的复制延迟。问题原因是DML操作修改任何一行，复制的时候都会执行全表扫描。

比如，在下表执行一个delete操作：

CREATE TABLE `joinit` (
  `i` int NOT NULL,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

一共有这么多行：

mysql> select count(*) from joinit;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+

执行delete操作：

mysql> flush status ;

mysql> delete from joinit where i > 5 and i < 150;
Query OK, 88 rows affected (0.04 sec)

mysql> show status like '%handler%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 2       |
| Handler_delete             | 1       |
…
| Handler_read_rnd_next      | 1048577 |
…

可以看到在主库上执行delete操作会执行全表扫描(Handler_read_rnd_next匹配的行数+1)删除了88行记录。

 

此外，每个被删除的行，都会分别被记录到binarylog中。如：

#220112 18:29:05 server id 1  end_log_pos 3248339 CRC32 0xdd9d1cb2 Delete_rows: table id 106 flags: STMT_END_F
### DELETE FROM `test2`.`joinit`
### WHERE
###   @1=6
###   @2='764d302b-73d5-11ec-afc8-00163ef3b519'
###   @3='18:28:39'
###   @4=27
### DELETE FROM `test2`.`joinit`
### WHERE
###   @1=7
###   @2='764d30bc-73d5-11ec-afc8-00163ef3b519'
###   @3='18:28:39'
###   @4=5
…
{88 items}

这就会导致在复制的时候，从库会执行88次全表扫描，因此造成性能下降。

 

 

鉴于这个原因，建议对每个表都增加一个主键，但是有时候，增加主键可能并不容易。比如：

1.可能没有适合当主键的列

2.增加一个列作为主键不现实，比如可能会影响第三方

 

解决方案就是使用MySQL8中的invisible column

增加一个新的不可见列("newc")作为主键：

ALTER TABLE joinit ADD COLUMN newc INT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY FIRST; 

增加主键是个昂贵的操作，因为会重构表。

 

增加了主键后的表：

CREATE TABLE `joinit` (
  `newc` int NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `i` int NOT NULL,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int NOT NULL,
  PRIMARY KEY (`newc`)
) ENGINE=InnoDB AUTO_INCREMENT=1048576 DEFAULT CHARSET=latin1

删除一行记录后，日志会类似下面的信息：

### DELETE FROM `test`.`joinit`
### WHERE
###   @1=1048577
###   @2=1
###   @3='string'
###   @4='17:23:04'
###   @5=5
# at 430
#220112 17:24:56 server id 1  end_log_pos 461 CRC32 0x826f3af6 Xid = 71
COMMIT/*!*/;

@1是第一个列，这里是主键。复制的使用该列就不需要执行全表扫描了。

 

在从库也类似，一次扫描找出匹配的行：

mysql> flush status ;
Query OK, 0 rows affected (0.01 sec)

mysql> delete from joinit where newc = 1048578;
Query OK, 1 row affected (0.00 sec)

mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
…
| Handler_read_key           | 1     |
…
| Handler_read_rnd_next      | 0     |
…

这里可以看到，不可见的列不会被显示，在表上执行操作的时候也不会被引用：

mysql> select * from joinit limit 2; 
+---+--------------------------------------+----------+----+
| i | s                                    | t        | g  |
+---+--------------------------------------+----------+----+
| 2 | ecc6cbed-73c9-11ec-afc8-00163ef3b519 | 17:06:03 | 58 |
| 3 | ecc7d9bb-73c9-11ec-afc8-00163ef3b519 | 17:06:03 | 56 |
+---+--------------------------------------+----------+----+
2 rows in set (0.00 sec)

mysql> insert into joinit values (4, "string", now(), 5);
Query OK, 1 row affected (0.01 sec)

如果需要，可以显式的查询不可见列。

mysql> select newc, i, s, t, g from joinit limit 2; 
+------+---+--------------------------------------+----------+----+
| newc | i | s                                    | t        | g  |
+------+---+--------------------------------------+----------+----+
|    1 | 2 | ecc6cbed-73c9-11ec-afc8-00163ef3b519 | 17:06:03 | 58 |
|    2 | 3 | ecc7d9bb-73c9-11ec-afc8-00163ef3b519 | 17:06:03 | 56 |
+------+---+--------------------------------------+----------+----+
2 rows in set (0.00 sec)

如果MySQL可以自动检测到innodb表缺少主键并自动增加一个不可见的主键呢？

考虑到内部已经有了一个6bytes的主键，将主键创建成不可见主键可能是个不错的想法。

这就表示当你执行create table：

CREATE TABLE `joinit` (
  `i` int NOT NULL,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int NOT NULL,
  PRIMARY KEY (`newc`)
) ENGINE=InnoDB AUTO_INCREMENT=1048576 DEFAULT CHARSET=latin1

最后会被转换成：

CREATE TABLE `joinit` (
  `newc` int NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `i` int NOT NULL,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int NOT NULL,
  PRIMARY KEY (`newc`)
) ENGINE=InnoDB AUTO_INCREMENT=1048576 DEFAULT CHARSET=latin1

可以通过

ALTER TABLE joint ALTER COLUMN newc SET VISIBLE;

将主键置为可见。

 

缺少主键是扩展数据库时的一个问题，因为复制需要对每个更新/删除的行进行全表扫描，并且数据越多延迟越多。

由于 3rd 方工具或限制，添加主键可能并不总是可行，但添加不可见的主键可以解决问题，并且具有添加主键的好处，而不会影响来自3rd方客户端/工具的语法和操作。