从 Icalingua 项目谈 SQL 数据库结构的最佳实践

这最早是 Icalingua(下简称 Il)项目下的一个 issue,因比较详细地叙述了一系列 bug 的诞生,具备一定的参考价值,故独立出来发布。

一、万恶之源——不当的数据组织方式

存储消息时,每个房间都建一个表(名为 msg${roomId})。这是 Il 在用 json 存记录时的某种优化,却在随后的 MongoDB 存储方式中也被继承,并影响到了 SQLStorageProvider 和 RedisStorageProvider 的编写。在 MongoDB 和 Redis 这样的 NoSQL 数据库中,这么做并不会增加太多复杂度。然而在 SQLStorageProvider 中,这种“表名与表数量都不固定”的结构却成倍增加了代码量和复杂度。

二、水土不服——SQL 相关代码的编写

NoSQL 数据库中,尝试向表(只有 Mongo 有对应“表”的概念——Collections,但我在 Redis 里也实现了这一层,姑且这么叫吧)中写入数据时,若表不存在,它会被自动创建。然而,SQL 数据库并没有类似 insertOrReplacecreateTableIfNotExist 这样的功能(除了 PostgreSQL,但我用 knex 实现的 SQLStorageProvider 在功能上取三个 SQL 的交集以保证兼容性)。

同时,因为 SQL 的 column 有较为严格的限制,我们需要对数据库版本进行管理,当 columns 有改动时,运行升级函数去 alter 各表,改动它们的 columns。然而,由于表名与表数量都不固定,我只好另建一表(表名为 msgTableNameTable,是不是有屎山的感觉了?),表中存储各 msg 表的表名。

于是我实现了一个 createMsgTable 方法,在每次对表进行操作以前调用该方法检查目标表是否存在,若不存在则先创建,创建时也向 msgTableNameTable 写入 msg 表的表名,以便此后进行升级操作时能修改它们的 columns

由于上述额外操作,以及对各 column 类型的定义,SQLStorageProvider.ts 的行数是 MongoStorageProvider.ts 的两倍以上。众所周知,bug 量与代码行数正相关,因此 SQLStorageProvider 自诞生之日起就 bug 不断。

单是被我记录的 bug 就有 5 条。在修复它们后,SQLStorageProvider 进入了一段稳定时期。SQLStorageProvider 具有庞大的码量,主作者并没有时间理解它,从而无法进行修改。我所做的就是在主作者提出新增/修改 columns 的 issues 时去编写对应的升级函数。此后,我为 SQLStorageProvider 添加了相当详细的 jsDoc 注释,于是我也不再需要自己编写升级函数,只需偶尔 review 一下主作者的修改即可。

对一个从一开始就以 NoSQL 的思路设计的程序,后续增加的 SQL 数据库将水土不服。无论如何,这一系列问题都姑且得到了解决,尽管它们让作者们和用户们都体会到了痛苦(请手动播放《Unravel》),直到这一天的到来。

三、异步灾难——过度优化打开的潘多拉魔盒

当打开空的群聊的时候报错,起初它没有得到足够的重视,因为我有约两个月的时期没有使用 Linux 和 Icalingua。然而一周后的今天,当我注意到该 issues 并尝试解决问题时,这场灾难终于揭开了帷幕。

Uncaught (in promise) Error: Error invoking remote method 'fetchMessage': Error: create table `msg-********` (`_id` varchar(255), ..., primary key (`_id`)) - SQLITE_ERROR: table `msg-1046496784` already exists

这一行错误让我相当震惊——按道理,我的代码不应该出现这个错误,因为在建表前,createMsgTable 会先调用 hasTable 方法来确认这个表不存在。

于是我开始了 debug。对 electron 的 main 进程中的错误进行 debug 是一件比较痛苦的事情。因为我似乎没法正常使用 VS Code 中的调试器,我只好以插入 console.log() 这样的原始方法进行调试。

大约 5 分钟之后,我发现了罪魁祸首:StorageProvider 类(SQLStorageProvider 为对该类的一个实现)中的 addRoomfetchMessages 方法被调用,但后者并非是等待前者异步返回后再被调用,而是同时被调用。

const hasMsgTable = await this.db.schema.hasTable(`msg${roomId}`)
if (!hasMsgTable) {
  await this.db.schema.createTable(`msg${roomId}`, (table) => {
    ......
  }
}

这是 createMsgTable 方法的片段。addRoomfetchMessages 均在开头调用了 createMsgTable 方法。该方法的作用为判断目标表是否存在,若不存在则建表。然而,当这个方法被以上述方式调用时,灾难出现了。

由于主程序对 addRoomfetchMessages 两个方法异步调用且不等待返回的设计,两次调用中,hasMsgTable 均为 false,因此 createTable 会被调用两次。第二次调用时,由于表已存在,SQLStorageProvider 会抛错,用户报告见到的正是这个抛错。

对“异步调用不等待返回”,主作者的理由是:

那我如果确实不需要等待呢?我希望多线程

实际上这并不是多线程,但却有和多线程相当相似的结果:

你有一个问题
你试图用多线程解决它
现你在两有个题问了有

一个很自然的想法是通过加锁来解决。我在 SQLStorageProvider 中增加了一个私有数组,每次调用 createMsgTable 方法时,都会检查数组中有无目标表名,若有,则跳过后续操作;若没有,则将目标表名 push 进这个数组,并进行后续建表操作。

然而我的锁并没有锁住异步的魔鬼。SQLStorageProvider 报错依旧。进一步的 debug 显示,我加入的的锁毫无作用,因为第二次被调用时,数组依然为空。

我并不知道 SQLStorageProvider 以何种方式被实例化,但如果它在主程序中有大于等于两个实例,那我的锁也将无能为力。即使只有一个实例,以这种异步调用的方式,锁也未必能生效。而即使这个锁得以生效,由于主程序异步调用不等待返回,createMsgTable 方法也将无法如它的设计目标那样,保证目标表在被访问前已被建立。

最终的解决方案是道选择题:

  1. 放弃异步调用不等待返回的过度优化。
  2. 放弃不靠谱的动态表名,将所有消息存入同一张表中,这个表像其它几个表一样,在 StorageProviderconnect 方法中被创建,该方法的异步调用会等待返回。

第二种选择的代码我已经完成,正在进行相关的性能测试。已有的结果(一晚,共收到 3632 条消息)表明:在有索引的情况下,用一个表存储所有消息并不会带来显著的性能损失。但按正常的使用方式,这个表最终会有十万甚至百万级别的行数,在被同等体量的消息测试确认没有问题以前,这个 branch 不会合并到主分支。

四、fill the rabbit hole

上面已经说到,整个项目的作者和用户们终于为动态表名付出了代价。为此,我编写了第二种方案的代码,以及对应的升级函数

将所有同类型数据存到同一个表里,这应该是 SQL 数据库的最佳实践。无论如何,这一系列的问题可算告一段落了。

  • SQL
    115 引用 • 290 回帖 • 3 关注
  • Icalingua
    1 引用
  • JavaScript

    JavaScript 一种动态类型、弱类型、基于原型的直译式脚本语言,内置支持类型。它的解释器被称为 JavaScript 引擎,为浏览器的一部分,广泛用于客户端的脚本语言,最早是在 HTML 网页上使用,用来给 HTML 网页增加动态功能。

    694 引用 • 1142 回帖 • 612 关注
  • MongoDB

    MongoDB(来自于英文单词“Humongous”,中文含义为“庞大”)是一个基于分布式文件存储的数据库,由 C++ 语言编写。旨在为应用提供可扩展的高性能数据存储解决方案。MongoDB 是一个介于关系数据库和非关系数据库之间的产品,是非关系数据库当中功能最丰富,最像关系数据库的。它支持的数据结构非常松散,是类似 JSON 的 BSON 格式,因此可以存储比较复杂的数据类型。

    85 引用 • 58 回帖 • 1 关注
1 操作
lixiang810 在 2021-10-07 14:27:56 更新了该帖

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...