MariaDB 语句只在刚插入测试数据之后比较快， MariaDB 重启之后速度就很慢了

[shendaowu]

SQL： https://pastebin.com/C3zYLe3z

为了节省点积分所以把 SQL 弄到别的网站了。另外注意执行那段代码好像会向硬盘写入接近 1G 的数据，不知道会不会有人嫌浪费。

主要症状是这样的。刚插入随机的测试数据之后那条带 IN 的查询语句比较快，大概 10 毫秒左右。但是重启 MariaDB 之后相同的查询语句需要一两秒，有时候需要一二十秒。重启之后对那条带 IN 的语句改来改去一阵之后又 10 毫秒左右了，但是重新插入随机的测试数据用同样的方法改就无效了。

我发现的异常。刚插入随机的测试数据之后 explain 那条带 IN 的语句 key 是 tag_content_rel_index ，key_len 是 4 ，rows 是一万左右。重启之后 key 是 content_index 。强制用 tag_content_rel_index 之后 key_len 是 8 ，rows 是 1 。我对优化基本是个外行，看不出来什么东西，也不知道这些信息有没有价值。搜过这些症状，搜不到什么东西。

另外 10 毫秒左右的是不是就没法优化了？

[h0099]

https://www.v2ex.com/t/908231
https://www.v2ex.com/t/908246

[h0099]

> 重启 MariaDB 之后

每次 mysqld 重启都会完全清空内存中的 innodb buffer pool ，而您再次在重启后的 mysqld 上首次查询这 sql 就需要从硬盘读大量 innodb page 进 buffer pool ，所以这时吃内存和硬盘 io 拖慢了 sql 耗时

> 重启之后对那条带 IN 的语句改来改去一阵之后又 10 毫秒左右

实际上您啥也不改单纯的在等到这查询所需的 page 都进了 buffer pool 之后再怎么反复执行耗时都是差不多的（除非您的 buffer pool 太小或是其他无关 sql 又进来导致 page 被反复逐出 pool 又从硬盘读取进 pool ）

> 我发现的异常。刚插入随机的测试数据之后 explain 那条带 IN 的语句 key 是 tag_content_rel_index ，key_len 是 4 ，rows 是一万左右。重启之后 key 是 content_index 。强制用 tag_content_rel_index 之后 key_len 是 8 ，rows 是 1

因为您创建表并狂暴轰入大量垃圾数据后没 ANALYZE TABLE 和 /或 OPTIMIZE TABLE 所以查询计划优化器还以为这表里头没有几行所以就选择了可能更慢的计划 stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause
还要看 filtered 是多少

> 另外 10 毫秒左右的是不是就没法优化了？

一条 sql 耗时 10ms 还需要优化？
如果这 sql 返回的行很多那实际上查询结果从 mysqld 到您的业务程序的 dbdriver ，再从 dbdriver 进入您的业务代码被您写的奇妙深刻业务逻辑消费的时间都远超 10ms 了（如果 mysqld 跟您的业务程序不在同一 localhost 而是走网络那延迟更高，那些选择无脑上云的业务环境里就是这样）
还不论从您的业务程序最终输出 html/json/xml 后走网络到最终用户那又要多久

[shendaowu]

#2 @h0099

>还要看 filtered 是多少

filtered 一直都是 100 。

> ANALYZE TABLE

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

太谢谢你了。这下我基本上是安心了。剩下的那点不安心是我的问题，我不习惯因为暂时的有效而确信方法是绝对正确的。另外因为你我也算是对数据库优化稍微有点见识了。

> 一条 sql 耗时 10ms 还需要优化？

如果能优化到更低的水平我就可以加个功能了。比如说每隔一个月所有 content 都两两执行一次那个带 IN 的语句，前提是用户允许。对我想做的网站来说这应该是个很实用的功能，应该能提升用户的体验。当然两个未修改的 content 之间和两个不活跃的用户的 content 之间不执行我是想到了的。其实我的网站不是简单的标签加内容，只是用了标签加内容的形式，“内容”关联的“标签”是可能一直变的。

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

> 您写的奇妙深刻业务逻辑

哈哈，是在嘲讽我吗？或者是玩笑？我几乎不在乎被嘲讽，在知乎上问问题问多了习惯被回答者嘲讽了。不过 V2EX 的站长好像对礼貌问题的容忍度挺低的，如果不想被处罚还是小心点吧。我感觉你这个说法很像嘲讽了，应该不只是我过于敏感。

[h0099]

> 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 中一大堆阴阳怪气的杠精和低级红高级黑反串都没人管，却要封我一个纯路人？

[shendaowu]

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

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

我之前想说的是每个月都在所有的 content 上都运行一次那个带 IN 的语句。说得还是不明确，我一步一步说吧。

SELECT *
FROM tag_content_rel
WHERE content_id = 1;

把获得的结果填入那条带 IN 的语句的 IN 的部分并执行和保存结果。

SELECT *
FROM tag_content_rel
WHERE content_id = 2;

把获得的结果填入那条带 IN 的语句的 IN 的部分并执行和保存结果。

其实两条语句应该是可以合成一条。我之前可能是怕缓存获得不太具有预测性的执行时间把，毕竟有时候搜索的 tag 可能不是某个 content 相关联的。

> 其实阁下就是陷入了 https://xyproblem.info 之中：

很有启发性，刷新我的认知了。已保存到我的笔记里。不过我好像偶尔还是能意识到这个问题的，因为我问问题的时候偶尔会带上自己的目的。另外我也是对自己的目的有点不想公开。不过这些基本都是借口，以后我会多注意的。

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

常在河边走，哪能不湿鞋。可能是我感觉错了吧，我感觉站长处罚人好像有一定随意性和随机性的，还有有时候 @他他才会管。希望别被站长看到。不过尽量还是别 @他，之前好像见过某人因为经常 @他结果被处罚了。另外 V2EX 的处罚不只是封号，还有降权。谁知道还有什么隐藏处罚。

[h0099]

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

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 主机佬们差不多

[h0099]

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 流中

[shendaowu]

@h0099
昨天我在本站使用指南节点看到站长说骂人也属于言论自由。我记性不太好，忘了是哪帖了，搜也没搜出来。不过我怎么记得站长好像因为某人骂人处罚那个人了？可能是我记错了吧。你是怎么记住那么多页面的？感觉某些好像不是搜索就行的，有什么方法吗？还是仅仅是你记性好而已？

你说的那个 bit array 的位数是 tag 的数量吧？我见识少，智商也不高，只能想到这个。为了防止你有什么更高明的方法问一下。存储方面我只能想到数据库压缩存储，计算方面怎么处理我就想不出来了。我之前好像了解过类似的东西，但是听说类似的方法相当耗资源就没详细了解。不过我也没具体了解到底要消耗多少资源，也许应该实际试一下。

[h0099]

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

您是说 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 距离的行数

[h0099]

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

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