转载

如何找出数据库中被删除记录的主键？

最近几周一直在魔改一个重要的生产系统，给这个系统加上了一个嵌入式的关系数据库，在内存中缓存一些结构化的数据，使得应用在和数据库之间发生网络隔离的时候也能做一些关键操作。既然是缓存就会涉及到缓存失效、同步、一致性等一系列问题，好在我可以充分利用业务特性来实现最终一致性，但是还是在数据同步上遇到了一个有趣的问题：如何将数据库中有记录被删除这件事情通知到应用，使得缓存中的相关记录也被删除？

这是一个典型的缓存失效问题，在大多数的场景下，一个应用同时对数据库和缓存拥有读写能力，缓存失效的策略是在数据库中删除数据之后，应用主动将缓存数据置为失效。这一策略在缓存是 Memcached 或者 Redis 之类的集中式缓存时工作良好，但是在应用规模扩展到多活部署时会出现扩展性问题：应用在多个可用区分别部署了缓存和数据库，但是数据只在单可用区写入（单机数据库事务），事务所在可用区的应用能主动失效当前可用区的缓存，其他可用区的数据库可以通过数据库的远程同步实现异步的数据同步，但是其他可用区的缓存由谁来失效呢？

最朴素的方案还是由事务发生的可用区的应用来失效，但是这会引入扩展性问题，也就是每个可用区里部署的应用，需要感知到总共部署了几个可用区，事务之后依次地或者并发地失效全部可用区的缓存。这就出现了一个反模式，系统需要关心的事情超出了它应该关心的范围，流量上来了临时扩个容还得改失效缓存的代码可不是什么好事情。

这时候常用的手段是引入一个订阅数据库更新的中间件，比如在其他可用区订阅事务所在可用区的 MySQL 的 binlog，根据 binlog 的内容来失效或者更新当前可用区的缓存。因为各个可用区之间的数据库已经在用类似的机制在做同步了，把这个机制再用到缓存失效上，也是十分行之有效的。于是应用服务器就还是只需要去主动失效所在可用区的缓存就可以了，其他可用区的缓存失效交给 binlog 和中间件。

这就是一个通过引入中间层实现解耦的案例。

这种策略在集中式缓存下工作良好，但是我的缓存是一个运行在 JVM 中的内存数据库，缓存不是集中式的，而是分散在每一个应用服务器中的「分布式缓存」，通过网络通信实现数据同步和最终一致性。binlog 订阅这种事情如果要做的话，恐怕就不是交给中间件那么简单了，而是每个进程都得自己订阅自己消费。

真的要用这么复杂的实现去做事情吗？真的要把应用和 MySQL binlog 格式绑定吗？绝对不要。

了解系统的约束是设计出恰如其分的架构的前提。在我魔改的这个系统中，数据库的变更是由别的系统触发的，而这个系统则是定时感知数据库的变化，通过竞争乐观锁的方式来处理数据库变化，其实是把关系数据库当成一个消息队列在用，多少也是有些反模式的。而且两个系统通过一个数据库来交互，则是表结构级别的耦合，这是非常反模式的。

之前是通过竞争乐观锁来处理数据变化，如果记录被删除了，自然就竞争不到了。如今被我加了缓存之后，就不再竞争乐观锁了，好处是即使数据库宕机，应用也能根据当前内存库中的数据干活，代价则是我必须引入一种感知数据库记录删除的手段，使得经过一个确定的时间窗口之后，数据库记录被删除的信息能同步到整个应用集群。

大多数时候，内存库是数据库的一个 View，或者子集，但是当数据库有记录被删除之后，内存库的记录就会比数据库多，只要我能找到这些多出来的记录的主键，就能从内存库中把这些记录删掉。但是问题来了，怎么找到这些主键？

在关系数据库中，我们常用 inner join 去关联两张表，找到两张表都有的数据。如果一张表的数据比另一张表多，那么我们可以用 left join 或者 right join，把数据比较多的那张表放在 left join 的左边，或者放在 right join 的右边，得到的结果集里，数据少的那张表就会有很多行都是 null，我们就能根据这些 null 过滤出不存在于另一张表中的记录。

数据库有两张表的场景下，这个问题可以用左/右连接去解决，但是在我只有一堆主键，却没有表的时候，怎么解决呢？

这里就要用到一个叫 dual 表的神奇科技。dual 表是 Oracle 数据库上一个为了保持 select from 语法一致性而专门弄的一个相当于占位符一般存在的表，反正就是能借助类似于 select 1 as id from dual 这样的语句，得到一个和查表一样形式的输出；然后再用 union all 把多个这样的表拼接起来，形成一个包含缓存中所有 id 的临时表；最后把临时表和源表做 left join，就可以得到缓存中还存在，但是数据库中已经删除的记录的主键。

来个例子好了，有一个在线的数据库沙箱，叫做 SQL Fiddle ，可以在里面验证 SQL 而不必在电脑上安装数据库。

CREATE TABLE user(
id BIGINT PRIMARY KEY,
name VARCHAR(64)
);

insert into user values
(1, 'user1'),
(2, 'user2'),
(4, 'user4'),
(5, 'user5');

构造一个包含四条记录的表，其中 id 为 3 的记录被删了。

select t.id from (
  select 1 as id from dual
  union all
  select 2 as id from dual
  union all
  select 3 as id from dual
) t left join user on (t.id = user.id)
where user.id is null;

假设缓存包含主键为 1、2、3 的记录，构造上面这样的 SQL，就能够得到我们想要的 id 为 3 的结果集。

一般来说，每条在生产环境执行的 SQL，我们在上线前都要在备库或者和生产数据在同一规模的测试库上看一下执行计划，以免因为索引没建好或者其他问题写出慢查询，最终拖垮整个系统。

这条 SQL 的执行计划是这样的：

|     id |  select_type |        table |   type | possible_keys |     key | key_len |    ref |   rows |                                Extra |
|--------|--------------|--------------|--------|---------------|---------|---------|--------|--------|--------------------------------------|
|      1 |      PRIMARY |   <derived2> |    ALL |        (null) |  (null) |  (null) | (null) |      3 |                               (null) |
|      1 |      PRIMARY |         user | eq_ref |       PRIMARY | PRIMARY |       8 |   t.id |      1 | Using where; Not exists; Using index |
|      2 |      DERIVED |       (null) | (null) |        (null) |  (null) |  (null) | (null) | (null) |                       No tables used |
|      3 |        UNION |       (null) | (null) |        (null) |  (null) |  (null) | (null) | (null) |                       No tables used |
|      4 |        UNION |       (null) | (null) |        (null) |  (null) |  (null) | (null) | (null) |                       No tables used |
| (null) | UNION RESULT | <union2,3,4> |    ALL |        (null) |  (null) |  (null) | (null) | (null) |                      Using temporary |

关注两个地方，一个是 UNION 的次数，一次传入的 id 越多，SQL 执行层数也就越多；另一个是第一行的全表扫描，一次传入的 id 越多，扫描行数也就越多。从这个角度来看，如果一次性传了几千几万个 id，会组装出一个巨长无比的 SQL 语句不说，还可能会给数据库带来一个比较影响性能的慢查询，所以要限制一下查询的规模，如果内存里的主键比较多，最好分成多次查询，毕竟对于 OLTP 类型的数据库，多次小而快的查询，远好过一次大而慢的查询，虽然从应用上看总耗时增加了，但是出问题的可能性却大大降低了。

这种 SQL 几天之前我是根本不会写的，也不会想到还能这么玩，差点就写出把数据库所有记录的主键分批读出然后和内存库去比对的实现。感觉大学时候学了一学期假的数据库原理，至今我对数据库的知识大多还是来自于在生产环境使用 MySQL 的经验，真是惭愧。

说到 MySQL，就不得不推荐一下我现在所在团队里的几位 MySQL 专家翻译的书，「高性能MySQL 」，物超所值。

原文 https://blog.jamespan.me/posts/how-to-find-primary-keys-of-deleted-row

正文到此结束