MySQL8使用不可见的列(invisible column)来避免复制延迟
2022-01-21 22:29 abce 阅读(223) 评论(0) 编辑 收藏 举报MySQL基于行的复制经常遇到的问题之一:因为没有主键导致的复制延迟。问题原因是DML操作修改任何一行,复制的时候都会执行全表扫描。
比如,在下表执行一个delete操作:
1 2 3 4 5 6 | 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 |
一共有这么多行:
1 2 3 4 5 6 | mysql> select count (*) from joinit; + ----------+ | count (*) | + ----------+ | 1048576 | + ----------+ |
执行delete操作:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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中。如:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | #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")作为主键:
1 | ALTER TABLE joinit ADD COLUMN newc INT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY FIRST ; |
增加主键是个昂贵的操作,因为会重构表。
增加了主键后的表:
1 2 3 4 5 6 7 8 | 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 |
删除一行记录后,日志会类似下面的信息:
1 2 3 4 5 6 7 8 9 10 | ### 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是第一个列,这里是主键。复制的使用该列就不需要执行全表扫描了。
在从库也类似,一次扫描找出匹配的行:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 | … |
这里可以看到,不可见的列不会被显示,在表上执行操作的时候也不会被引用:
1 2 3 4 5 6 7 8 9 10 11 | 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) |
如果需要,可以显式的查询不可见列。
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 6 7 | 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 |
最后会被转换成:
1 2 3 4 5 6 7 8 | 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 |
可以通过
1 | ALTER TABLE joint ALTER COLUMN newc SET VISIBLE; |
将主键置为可见。
缺少主键是扩展数据库时的一个问题,因为复制需要对每个更新/删除的行进行全表扫描,并且数据越多延迟越多。
由于 3rd 方工具或限制,添加主键可能并不总是可行,但添加不可见的主键可以解决问题,并且具有添加主键的好处,而不会影响来自3rd方客户端/工具的语法和操作。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 我与微信审核的“相爱相杀”看个人小程序副业
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~
2021-01-21 PostgreSQL auto_explain模块
2016-01-21 SQL Access Advisor
2016-01-21 SQL Tuning Advisor
2016-01-21 emergency monitoring和real-time ADDM