代码之家 › 专栏 › 技术社区 › user223304

服务器同步数据库应用程序、有关主键的问题、多个表和节省存储空间

primary-key database-design database mysql

user223304 · 技术社区 · 6 年前

我计划开发一个应用程序,可以选择性地将数据项存储在mysql中央服务器(云)上,并将该服务器用作备份数据项或在不同设备之间同步数据项的方法。

现在,我非常粗糙的表格布局如下所示:

服务器的用户表

用户ID(int主键自动递增)
last_update-上次更新任何数据项的时间戳
电子邮件
密码-加密和哈希

服务器的数据输入表

服务器入口ID(int主键自动递增)-在所有设备上都是唯一的
用户id(int)-来自服务器的用户表
上次更新-上次更新此数据项的时间戳
实际数据的列…

客户端应用数据输入表

row_id(int primary key autoincrement)-仅用于本地app db操作,不用于共享
服务器条目id(int)-这是添加到服务器时从服务器检索到的唯一服务器数据条目id
last_update-此数据项上次在应用程序中本地出现的时间戳
实际数据的列…

问题

我的问题实际上不是关于同步逻辑。如果我有很多用户和数据条目,我希望尽可能提前计划,以避免潜在的问题。

使用intautoincrementprimarykey(pk)获取有用信息(比如用户id或数据输入id)可以吗?我听说这不是很好的实践,但是我也看到了一些例子,其中pk用于获取有用的信息。如果需要,是否担心将行迁移到不同的表或数据库会很困难?服务器上的服务器条目id与与与该服务器同步的所有设备上的相同。另一种方法是使用uuid作为服务器入口,但它们不是自动递增的,所以我认为搜索速度会慢一些,而且会占用更多的数据库空间(16字节blob)。
如果数据输入表在服务器上可能变得非常大,那么改为创建多个数据输入表是不是一个好主意?例如,用户id 0-9999可以使用table1,用户id 10000-19999可以使用table2等。此外,将用户的数据全部保存在一个表中会使为单个用户检索所有数据的速度快于单独的表。
假设我有一个数据输入表,其中有5个int列和3个double列,用于存储实际数据。我们还可以说,这8列只需要放在一起,而不会在带有mysql查询的where子句中搜索。假设这8列的值通常为0。由于mysql使用相同的存储空间来存储0和2147483647的int,所以最好使用类似的存储模式将这8列存储为字节blob https://www.sqlite.org/fileformat2.html#varint ?

1 回复 | 直到 6 年前

Rick James 6 年前

一个表中的行数没有截断。随着一张桌子越来越大,越来越多的问题出现了。百行表在没有规范化和索引的情况下工作得很好;十亿行表需要两者,加上收缩的数据类型。等等(在一次调查中,50米的行大约是第96个百分点。我在一张桌子上看到了超过十亿行。)

uuid对于大型表来说性能很差;请尽量避免它们。(是的,把它们装进 BINARY(16) 比 VARCHAR(36) .

2/3的我的表不使用 AUTO_INCREMENT 相反,他们有一个“自然”的PK。所以,很明显,我说“这取决于”什么时候涉及PKS。用户id可能是最好的 SMALLINT UNSIGNED 或 MEDIUMINT UNSIGNED ,这取决于您期望的用户数是不超过64k还是1600万。很少有人需要 INT ,更不用说 BIGINT . (它们的大小分别为2、3、4、8字节。)

非- 自动递增 pk是将pk的副本连接到每个次密钥,从而添加到它们的大容量中。不过,粗略地说:

大PK,但没有副键:好
大PK和一辅助键:使用或不使用ai时占用大约相同的磁盘空间。
大pk和多个副键:ai开始发光(空间方面)。

做不把一张大桌子分成多张小桌子。麻烦是巨大的,好处是微乎其微的。偶数 PARTITIONing 不太可能提供任何好处。

“规范化”具有重复值的大型ish列是通常好主意。(节省空间,但 JOIN 把事情放在一起并不坏。)

50万行加上这8列可能会达到大约4GB。这不是很大。不值得玩 BLOB 游戏(除非应用程序代码 真的? 同时,考虑比4字节更小的整数类型 国际的 ;考虑 UNSIGNED ;考虑 FLOAT 对“双倍”。

mysql中没有等价的“varint”。

评论 http://www.agiledata.org/essays/keys.html :

“钥匙”不一定 独特地 确定一行。“Secondary Key”的同上。关键字 UNIQUE 是必要的。
在mysql中, PRIMARY KEY 其特殊之处在于它唯一地标识行,并与数据“群集”。
在mysql中,几乎总是首选使用 VARCHAR 而不是 CHAR .
在mysql中, 自动递增 通常是“代理”键的首选技术。