> 您可以先尝试 innodb 的两种压缩方式：tablespace 层面（`ALTER TABLE ... ROW_FORMAT=COMPRESSED`）和 page 层面的压缩（`ALTER TABLE ... COMPRESSION="zlib"`）

我做了一丶丶测试： https://github.com/n0099/TiebaMonitor/issues/33

> 昨天我在本站使用指南节点看到站长说骂人也属于言论自由

您是说 https://www.v2ex.com/t/36991 吗，这都 12 年的主题帖了

> 怎么记住那么多页面的？感觉某些好像不是搜索就行的，有什么方法吗

建议立即安装浏览器扩展之 https://chrome.google.com/webstore/detail/better-history/egehpkpgpgooebopjihjmnpejnjafefi https://chrome.google.com/webstore/detail/session-buddy/edacconmaakjimmfgnblocblbcdcpbko

> 你说的那个 bit array 的位数是 tag 的数量吧

是，比如您现在总共有 10 个 tags 那么 bitmask 的长度就是 10 位（但注意大多数环境下处理 bitmask 时都会被视作 unsigned int 处理并对齐到最近的 int32/64 上因此即便您只需要 10 位他内部也还是 padding 了一堆 0 的 32/64 位）
如果使用 mysql 对 bitmask 的封装类型之 https://dev.mysql.com/doc/refman/8.0/en/set.html 那就是`SET(a,b,c,d,e,f,g,h,i,j)`
请注意由于 mysql 的 set 类型只不过是对不同长度的 int 类型的封装所以他也受到最长的 bigint 也只有 64 位的限制，所以当您的 tags 超过 64 个时您应该增加第二个 tags 列来存储，这样也会稍微增加查询时的复杂度（多复制粘贴约束一个字段），如果以后 mysql 自带了 bignumber 那样的可以进行数学运算的长度“无限”（实际上一般是 2^32-1 ）的 int 类型那您就不需要按 64 个来拆分
不要使用字符串类型（定长 varchar/变长 text ）来存储二进制位的字符串表达（比如 0101010 ），因为他们实际上是 ASCII 的 0x30 和 0x31 字节，也就是说阁下花了 8 倍的空间（ 1byte=8bits ）来存储他们，而且还成功实现了无法直接进行位运算（除非先把二进制字符串转二进制 int ）以实现 CRUD tags 集合或 xor 计算 hamming 距离

> 为了防止你有什么更高明的方法问一下

v2 人们会直接推荐您上各种现成的开源或商业的推荐算法封装实现产品，这样您就只需要做调包侠把 mysql 已有的数据灌进去就能获得“实时”计算出的相关推荐流，但这同样也带来了您去学习如何调包的成本（当然总比折腾 mysql 简单）

> 存储方面我只能想到数据库压缩存储

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html 进一步指出
> SET('value1','value2',...) 1 、2 、3 、4 或 8 个字节，具体取决于集合成员的数量（最多 64 个成员）

实际上阁下并不需要担心存储这一堆纯 int 有多大，您之前也看到了阁下生成的 10m 行的 content_tag_rel 表才几百 m 大
您可以先尝试 innodb 的两种压缩方式：tablespace 层面（`ALTER TABLE ... ROW_FORMAT=COMPRESSED`）和 page 层面的压缩（`ALTER TABLE ... COMPRESSION="zlib"`）： https://blog.koehntopp.info/2021/09/09/mysql-two-kinds-of-compression.html https://dev.mysql.com/doc/refman/8.0/en/innodb-compression.html
如果阁下真要十亿级别的行并空间尽可能小建议直接换列存储（如 clickhouse tidb ）或时序（如 influxdb ）数据库，但这也同样陷入了上述`v2 人们会直接推荐您`

> 计算方面怎么处理我就想不出来了

建议复习 https://en.wikipedia.org/wiki/Mask_(computing)
假设您现在每个 content 行中的 tags 字段类型是`SET(a,b,c,d)`，因此您总共有着 4 个可能的 tags ，按经典的 CRUD 来看：
C：给某个 content 增加`a`tag：`tag = tag | 0b0001`（注意是小端序，下同）
R：tags 中是否有`a`：`tag & 0b0001 = 0b0001`
U：反转 tag （如果已经有`a`就删除，反之亦然）：`tag ^ 0b0001`，其中^是 XOR 异或运算符 https://en.wikipedia.org/wiki/Exclusive_or https://en.wikipedia.org/wiki/XOR_gate
D：删除`a`：`tag & 0b1110`，注意这的操作数跟 R 是完全相反的，但运算符都是 AND ，也就是说~0b0001=0b1110 ，其中~是 NOT 运算符 https://en.wikipedia.org/wiki/Negation
以上所有运算都可以同时对多个 tag 执行并在单个位运算之内完成
也就是说如果您想查询同时有着 tag`a`和`c`，那您只需要写`WHERE tags & 0b0101 = 0b0101`，而不需要分开写两个 AND 约束
这被称作 https://en.wikipedia.org/wiki/Bit-level_parallelism 或者说 https://en.wikipedia.org/wiki/Single_instruction,_multiple_data

最后回到阁下的计算 tags 集合相似度上：
如果使用对所有 content 两两配对计算之间的 hamming 编辑距离法找出距离<=5 的 tags 集合（ 5 是指两个 tags bitmask 之间最多相差 5 个位 /tags ）
最朴素的方式是`SELECT * FROM table WHERE BIT_COUNT(tags ^ 当前 content 的 tags) <= 5`，然后对所有 content 的 tags 循环执行这个 sql ，也就是`O(2n)`的时间复杂度
但由于 hamming 距离运算是符合交换律的也就是说 a 与 b 之间（`BIT_COUNT(a 的 tags ^ b 的 tags)`）跟 b 与 a 之间（`BIT_COUNT(b 的 tags ^ a 的 tags)`）是相同的距离，因此您就可以像乘法表裁剪掉了斜向的一半重复值那样只计算一半配对的结果，也就是`O((n*n+1)/2)`的时间复杂度
进一步的阁下还可以利用 https://github.com/Starry-OvO/aiotieba/pull/63#issuecomment-1364693204 中的思路将 32/64 位长的 bitmask 给分组拆成更小的多个 int8/16 字段，然后通过多个 WHERE 约束来 pre-filter 以进一步减少最终需要计算 hamming 距离的行数

> 我认为"进程锁"应当能解决你提出的"幻读"的问题

我遇到的不是幻读而是“滞后”读，您可以从图 1 中看出来线程 2 读取进程锁时线程 1 已经释放了线程 1 此前插入并锁的行 a ，而线程 2 此前从数据库中读时还不存在行 a ，所以导致线程 2 从两处取得（时机上一个太早一个太晚）的事实都是不存在行 a

> 它存的是真正写入的数据

准确地说进程锁缓存着的行在数据库中有 3 种状态：即将 INSERT （还没 COMMIT ），已经 INSERT （ COMMIT 了，只有这个状态下是`真正写入的数据`），INSERT 失败（ ROLLBACK 了）

> 只能说目前只能保证不会重复插入导致整个事务回滚,这个应该更重要?

是

> 提早去识别"垃圾数据": 非当前所有活跃"写事务"需要的数据都是"垃圾数据"

的确可以提前不去生成已经被进程锁占据着的行的数据

> 在 `"线程 1"向"进程锁"插入数据->处理失败"进程锁"数据` 的中间会有`"线程 2"读取"进程锁"`

所以阁下的意思还是建议使用您最初提出的`"进程锁"清理数据的时机可以调整下,等到没有任何活跃事务的情况下再把它清掉.`
但问题并不在线程 2 在线程 1 还占据着进程锁中的行 a 时去读取进程锁中的行 a ，因为进程锁的目的不就是让线程 2 （或更多后续任务）知道现在行 a 被线程 1 占据着吗？

> 还要考虑间隔锁、表锁之类的问题

根据 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
> 对于使用唯一索引锁定行以搜索唯一行的语句，不需要间隙锁定。（这不包括搜索条件只包括多列唯一索引的某些列的情况；在这种情况下，确实会发生间隙锁定。）例如，如果该 id 列具有唯一索引，则以下语句仅使用一个索引记录锁定 id 值为 100 的行，其他会话是否在前面的间隙中插入行并不重要：
> SELECT * FROM child WHERE id = 100;
> 如果 id 未索引或具有非唯一索引，则该语句会锁定前面的间隙。

而这里的 a 所在的字段是具有 UNIQUE 约束的并且 SELECT 的 WHERE 子句使用=而不是< > BETWEEN 等 range operator ，所以只有 row lock 而没有 gap lock

> 这里还值得注意的是，不同事务可以在间隙上持有冲突锁。例如，事务 A 可以在一个间隙上持有一个共享间隙锁（间隙 S 锁），而事务 B 在同一间隙上持有一个独占间隙锁（间隙 X 锁）。允许冲突间隙锁的原因是，如果从索引中清除记录，则必须合并不同事务在记录上持有的间隙锁。
> 间隙锁 InnoDB 是“纯粹抑制性的”，这意味着它们的唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务获取的间隙锁不会阻止另一个事务在同一间隙上获取间隙锁。共享和排他间隙锁之间没有区别。它们彼此不冲突，并且它们执行相同的功能。

这应该暗示了`SELECT ... WHERE uniq > 1 FOR SHARE`（ gap IS ）可以与`SELECT ... WHERE uniq > 1 FOR UPDATE`（ gap IX ）同时执行

> 可以明确禁用间隙锁定。如果您将事务隔离级别更改为 READ COMMITTED ，则会发生这种情况。在这种情况下，间隙锁定在搜索和索引扫描中被禁用，并且仅用于外键约束检查和重复键检查。
> 使用隔离级别 READ COMMITTED 还有其他影响 。在 MySQL 评估 WHERE 条件后，释放不匹配行的记录锁。对于 UPDATE 语句，InnoDB 进行“半一致”读取，这样它将最新提交的版本返回给 MySQL ，以便 MySQL 可以确定该行是否匹配 UPDATE 的 WHERE 条件

这可能也就是我在生产中只要使用 mysql 默认的`REPEATABLE READ`隔离级别就会疯狂死锁的原因

> 除非死锁的时候我会去 DEBUG 数据库的锁状态,其它情况我想操这个心 :)

建议无脑降低事务隔离级别至`READ COMMITTED`

乐观并发控制是要求每个客户端的后续读写都得依赖于此前查询所获得的的`ROW_VERSION`
比如事务 1`SELECT yi, ver FROM t`获得`(a,0)`
那么事务 1 后续的所有对该行的读写（ SELECT/UPDATE ）都得依赖于`ver=0`这个此前获得的事实
也就是对于读：事务 1 期望重新`SELECT yi, ver FROM t`获得的还是`(a,0)`，这叫做`REPEATABLE READ`（避免了幻读`phantom read`），注意 mysql 默认的事务隔离级别就是`REPEATABLE READ`所以默认是已经保证了在同一事务内不断重新执行`SELECT yi, ver FROM t`返回的永远都是`(a,0)`
对于写：事务 1 期望`UPDATE t SET 某个其他字段 = 某值 WHERE yi = a AND ver = 0`所返回的`affected rows`是 1 行，而如果不是 1 行而是 0 行就意味着表 t 中已经不存在符合约束`WHERE yi = a AND ver = 0`的行，也就是说行`yi=a`已经被其他事务修改了

建议参考以某企业级 orm EFCore 为背景的 MSDN 微软谜语： https://learn.microsoft.com/en-us/ef/core/saving/concurrency
以及我之前对于类似的场景（但是是 INSERT-only 而不是 UPDATE ）使用了数据库层提供的悲观并发控制，毕竟在 SQL 末尾加`FOR UPDATE`可比在 mysql 里用 TRIGGER 模拟单调递增的自增 sql server 的 ROW_VERSION 类型然后在程序业务逻辑里写乐观控制所带来的一大堆 if 简单多了： https://www.v2ex.com/t/908047

1. 那会在有许多线程并行执行事务时造成“饥饿”（ starvation ），因为靠后创建的线程获取不到任何没被锁的行导致其无事可做
2. 如果事务执行失败或者发生其他异常了也会释放锁（ try finally ）。而且为了保险我还加了锁超时，其他线程获取锁时会忽略已经连续锁了 5 分钟的行（数据库不会真卡到 INSERT 一些行都卡上几分钟吧）

> 这些是基于单体服务的设计

哪怕再怎么分布式（实际上同一个进程里的多线程也已经是分布式了所以才会有这些并行竞争问题），不论是无主还是有主的分布式网络，最终都得往一个单点（接受 INSERT 写操作的单主数据库网络）上竞争资源（插入什么行），那这就需要协调来避免竟态（同时插入导致表里存了重复行，但这会被数据库本身的 UNIQUE 约束给拦截所以导致各个线程收到 duplicate entry 错误，但我又不希望某个线程只是因为插入的一大批行中有一些已经存在了就导致整个事务回滚），除非能让分布式数据库也变成多主网络（ apache hadoop 那样），那每个任务节点就可以只向固定的数据库节点 INSERT （但仍然需要在接受 INSERT 写操作的所有数据库节点之间不断同步数据从而保证不会有相同 key 但 value 却不同的记录，也就是保证数据一致性）

> 我不喜欢用`SELECT ... FOR UPDATE`,它通常会让我其它功能查询也被无辜的阻塞

根据 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks 的那张表格
事务 A 中的`SELECT ... FOR UPDATE`所产生的 IX 锁（有意排他锁）的确会导致事务 B 的普通的`SELECT`所产生的 S 锁（非有意共享锁）被挂起并一直等待 IX 锁释放（直到`innodb_lock_wait_timeout`）
但这表格也暗示了事务 A 的 IX 锁不会导致另一个事务 C 的`SELECT ... FOR SHARE`所产生的 IS 锁被挂起（`Compatible`），尽管我也不知道这会在哪个事务隔离级别下成立（可能是`READ COMMITTED`），但阁下的确可以亲自试试看

#12 @shade 用 bitmask https://dev.mysql.com/doc/refman/8.0/en/set.html
正好 /t/909074#r_12585697 里讨论过了

typo：最小复习->最小复现 https://en.wikipedia.org/wiki/Minimal_reproducible_example

让我们回到阁下的问题本身：

> 可能是我脑子出问题了。不知道为什么会说错。也可能是我写的时候脑子里想的是另外一种计算所有内容两两之间的相似度的方法吧。其实用我上面的 SQL 执行一次就能获得某个 content 的按某种相似度排序的所有的 content ，所以用不着 content 两两之间计算相似度，对所有 content 执行一遍那个带 IN 的语句就行了。感觉这个错误实在是太神奇了。想不到什么避免的方法。

这的相似度是指某两个 content 所属的 tags 集合之间的相似度吗？也就是朴素的推荐算法实现中使用的聚类推荐？
那么阁下可以违反一下 1NF （此前 https://www.v2ex.com/t/908231#r_12571811 提到的）将每 content 的 tags 集合变成一个 https://en.wikipedia.org/wiki/Bit_array 然后两两配对对所有 content 的这个转变为了 bitmask 的 tags 集合计算编辑距离（如 hamming/levenshtein 距离） https://github.com/Starry-OvO/aiotieba/pull/63 ，
那么对于每 content 的 tags bitmask ，存在其他 content 的 tags bitmask ，如果他们之间的编辑距离足够小（如<10 ），就认定这两个 content 具有相似的 tags ，就可以放进推荐 feed 流中

> 我感觉你这个说法很像嘲讽了，应该不只是我过于敏感。

https://www.v2ex.com/t/908083#r_12566585 对此早有预言：
建议深入学习贯彻泛银河系格雷科技分部邪恶组织四叶重工炼铜本部叶独头子叶独群组联合体陈意志第三帝国元首炼铜傻狗橙猫领导下的四叶 TG 本部（ https://t.me/n0099_tg https://t.me/n0099official ）话语体系文风：
https://sora.ink/archives/1574
https://github.com/n0099/TiebaMonitor/issues/24
https://github.com/Starry-OvO/aiotieba/issues/64

> 很有启发性，刷新我的认知了

一个 现实生活中十分常见的 xyproblem 场景 就能再一次刷新阁下的`认知`了？
那如果阁下去复习哲学基础之形式逻辑呢： https://sora.ink/archives/1502
https://n0099.net/v/d/1509
https://lasm.dev/2022/11/15/2022-11-15/
https://lasm.dev/2022/10/18/2022-10-18/
再来丶左右互搏：
https://n0099.net/v/d/3236
https://lasm.dev/2021/11/22/self/

而 v2 最近的某热门主题帖中也有人对此提出概括 https://www.v2ex.com/t/908787#r_12577024：
> 看到 v 友们的回复，我竟然一时不知道，是 v 友们的世界观太容易被改变了，还是 v 友们根本不知道世界观是个什么东西。。。 （ v 友们个个人中龙凤，怎么可能不知道呢。。。）

> 因为我问问题的时候偶尔会带上自己的目的。另外我也是对自己的目的有点不想公开

阁下并不需要透露阁下到底在做什么项目：
> 网站的具体细节我就不说了。由于网站还没开始做，我想尽量保密。你要是感兴趣的话我可以把原型和计划书单独发给你。其实我这个东西我给很多人都看过了，好像很多人都说从商业角度看不靠谱。所以不出意外我只能用业余时间自己做这个网站了。我对我设想的网站实在是太痴迷了，不做出来难受。

因为您最初的目的就是优化这两个 sql 而已，那您提问时为什么不最小化问题？
去给大型开源项目提 issue 或 stackoverflow 提问如果问题复杂也是直接附带一个最小复习
而不是把您的项目业务逻辑细节事无巨细的告诉纯路人（而且您也不想说），然后纯路人反而被您说的一大堆给整晕了就又陷入了 xyproblem

> 还有有时候 @他他才会管。希望别被站长看到。不过尽量还是别 @他

回顾经典之某位信安带手子纯路人指出了一个 xss 漏洞然后 livid 直接彻底解决了造成问题的功能（而不是尝试修复他）： https://www.v2ex.com/t/908287#r_12567114
我注册 v2 也有 4 年半了（ 2018-06-19 ），但此前都是从 google 检索结果里点击 v2 看看从来没发过贴所以我的确不知道 v2 啥环境，现在看来跟简中互联网平均水平如隔壁 hostloc 主机佬们差不多

> filtered 一直都是 100 。

https://dba.stackexchange.com/questions/164251/what-is-the-meaning-of-filtered-in-mysql-explain

> 狂暴轰入大量垃圾数据 > 执行带 in 查询 10 毫秒左右 > 重启 MariaDB > 执行带 in 查询 1 秒多 > 执行 ANALYZE TABLE tag_content_rel; > 执行带 in 查询 10 毫秒左右 > 之后不管重启多少次执行那条带 in 的都是 10 毫秒左右。

您应该先 OPTIMIZE 再 ANALYZE ，对于 innodb table engine 的表 OPTIMIZE 就是完全重建表，这样可以压缩掉您之前轰入 INSERT 时产生的 page 空洞从而稍微减低 io 这些 page 的耗时

> 比如说每隔一个月所有 content 都两两执行一次那个带 IN 的语句，前提是用户允许

您为啥要两两配对执行？为了缓存某个 content 的所有 tag ？但现查也只要 10ms 那您为什么要去套个缓存？说不定您的缓存比 10ms 还慢
而且为了获知所有 content-tag 关系（而不是需要哪个 content/tag 就去现查）您也只需要去掉 WHERE 子句直接`SELECT * FROM content_tag_rel`，所以哪来的两两配对？

> “内容”关联的“标签”是可能一直变的

content_tag_rel 表当然可以变，没人规定他必须是 immutable 的

> 我对我设想的网站实在是太痴迷了，不做出来难受。

我此前于 https://www.v2ex.com/t/908231#r_12566573 还以为阁下的程序是单纯的直接使用现成的开源 /闭源项目，所以阁下难以 /无法去修改其内部结构，所以才要想到在程序外部从数据库甚至服务器层面隔离 sql 来缓解问题
我错怪您了

> 在知乎上问问题问多了习惯被回答者嘲讽了

您可以先把问题说清楚，最开始您发的 /t/908231 里您不也没说您具体要优化的 sql 和表结构是什么样吗，后来我追问您才发
结果这几天看下来就是 对于 WHERE tag_id 少加了一个包含 tag_id 的索引
以及忘了 OPTIMIZE/ANALYZE TABLE 这两个小问题而已

其实阁下就是陷入了 https://xyproblem.info 之中：
> Q：如何使用 X 来做 Y ？
> A：如果你想做 Y ，你应该问这个问题，不要预先假设使用可能不合适的方法。这种形式的问题通常表明一个人不仅不了解 X ，而且对他们正在解决的问题 Y 感到困惑，并且过于关注他们特定情况的细节。

> 不过 V2EX 的站长好像对礼貌问题的容忍度挺低的，如果不想被处罚还是小心点吧

与此同时隔壁某主题帖 /t/909154 中一大堆阴阳怪气的杠精和低级红高级黑反串都没人管，却要封我一个纯路人？

同样是云厂商扫描硬盘 /静态存储（下称存储）上的文件，建议根据方式做区分：程序化和非程序化的
- 程序化：云厂商写了一些程序来定期 /按事件扫描某些 /全部存储中的文件，当程序遇到某些特征（如文件名 文件内容 hash fs 元数据）时将他的存在记录下来，然后可能会有打点上报 给安全部门作为汇总统计 和 /或 给 end user 发送通知告诉他发现存储了符合特征的文件
- 非程序化：云厂商的人员受到某些命令或是他们闲的打开了存储空间上的某些 /全部文件，人肉操作在其中达成他需要的目的（比如找到什么 log 作为证据提交）

当然直接这样一刀切同样是二极管行径，非程序化的人工法务调查也可以掩盖在 agent 那样的程序化流程之下，反之亦然

所以从（云厂商的）合规性的角度讲应该是尽可能避免非程序化的扫描，而是将这些需求都写成固定程序来执行
还要给用户提供选择性，比如在创建实例时加个默认选择的复选框允许他 optout 不安装 agent
当然即便 不安装 agent 启用硬盘加密 也无法对厂商掩盖什么，只是对厂商而已扫描时更麻烦一些（那他就可能只出于法律目的而扫描了）

据我所知国外云厂商的客服您主动给他 ssh 登录方式请他来解决疑似厂商造成的诡异问题他们都不敢来登录并要求您撤回，因为处理工单的客服的也没那个权限去操作用户托管数据
但即便您订阅了厂商的`高级`支持服务那群工程师同样不敢亲自登录您服进行调查，工程师们也只能在他们熟悉的厂商那一套技术栈上协助调查您遇到的疑似厂商造成的问题

#60 @crazyMan233 如果阁下真的喜欢 cosplay EFF FSF 精神会员隐私人上壬那还是先多读整天黑美帝数字巨头的英国科技媒体 https://www.theregister.com 罢
我搜了 hn 都没找到多少黑料： （ via https://www.google.com/search?q=aws+data+security+site%3Anews.ycombinator.com 当然阁下也可以说 google 同为美帝 FAAMG 科技辛迪加自然要为 aws 辩护，所以应该用依托于 MSFT bing 的欧盟隐私人上壬最爱的 duckduckgo 搜索）
https://news.ycombinator.com/item?id=34276986
https://news.ycombinator.com/item?id=23929044#23930531
https://news.ycombinator.com/item?id=34014429

typo：抽象泛话->抽象泛化后的形式

然后为了那点 v 圈二手屎信息还要以`知识付费`的名义支付数元巨款，或是求爷爷告奶奶地让别人来免费`私人定制`解决他的迫真`私人问题`，殊不知大多数问题抽象泛话都早已在 stackexchange 上甚至各种互联网角落的文档 /部落格里得到了解答 就像 /t/908231 的 OP @shendaowu

看这个帖子。我只能说，中国的开发者配得上机翻 en 互联网然后搬进简中互联网垃圾站的二手信息排泄链如 https://www.v2ex.com/t/909128

#9 @thetbw 这就是 EAV

看这个帖子。我只能说，中国的开发者配得上中国的云服务

#60 @crazyMan233 我只提到 aws 了？
#33 > 我记得阿里云 aws azure 都有提供（阿里云的好像收费）

实际上腾讯云和大量三线云厂商也有硬盘加密服务但我想你们也不敢用

#47 有各种把厂商的技术栈放在自己机房里自部署的政企私有云，严格限制部署区域和用户量 aws/azure govcloud
govcloud 是他们两家的产品名词（ az 叫 for gov ），第一个子句不就囊括了阿里腾讯云所提供的类似的政企云？
https://www.aliyun.com/solution/govcloud
https://cloud.tencent.com/solution/tcx