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方客户端/工具的语法和操作。