V2EX › wxf666 的所有回复 › 第 21 页 / 共 27 页

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

1 ... 13 14 15 16 17 18 19 20 21 22 ... 27

❮

❯

2022-08-10 17:26:33 +08:00

回复了 sunmoon1983 创建的主题 › MySQL › 求一个数据表设计的思路！

@sunmoon1983 可以问下，MySQL 8 出来也有六年了，为啥还优先选用旧版本吗？

2022-08-10 14:31:27 +08:00

回复了 sunmoon1983 创建的主题 › MySQL › 求一个数据表设计的思路！

@yjhatfdu2 三年前的 MySQL 8.0.17 ，也能很好地完成楼主的任务呀。。只是楼主不换新版本而已

SELECT *
FROM DATA
WHERE province = 12345
　 AND JSON_CONTAINS(city, CAST('["234567","345678"]' AS JSON))
　 AND JSON_CONTAINS(county, CAST('["7345678","6234567"]' AS JSON))

2022-08-10 14:22:48 +08:00

回复了 sunmoon1983 创建的主题 › MySQL › 求一个数据表设计的思路！

@sunmoon1983 上面有处地方忘改了：

　 divided_by_prov_county(id) AS (
　　 SELECT DISTINCT id
　　　 FROM idx_prov_『改成这样：county 』 main

2022-08-10 14:19:04 +08:00

回复了 sunmoon1983 创建的主题 › MySQL › 求一个数据表设计的思路！

@LeeReamond MySQL 8.0.17 以上都支持多值索引了（索引一个数组，也就是你说的多段索引？）

用上多值索引，4 楼的 SQL 就不是全表扫描了。但楼主 @sunmoon1983 用的还是旧版 MySQL……

没办法，只能自己模拟一下了。如楼上几位所说，拍平存。

之后如何取数据呢？像 @copper20 #11 和 @pannanxu #20 那样用 in ，表现不出『同时满足』的意思

翻了翻课本，这不就是『关系除法』干的活儿吗。。

站内另一个帖子（ https://www.v2ex.com/t/772870 ）也有类似描述：

《给定一「技能表」，根据「员工技能表」，求会「技能表」中『所有技能』的员工》

但我不会同时『除以两张表』，只能分开除，再求交集了（然而还要自己模拟 INTERSECT ……）

在此抛砖引玉，求大佬合并这两个除法

『查询条件』

prov 　　 city 　　 county
——　—————　————
123 　 [30, 20] 　 [80, 70]

『结果』

id 　 prov 　　　 city 　　　 county
—　———　——————　——————
1 　 123 　 [10, 20, 30] 　 [70, 80, 90]

『 MySQL 语法（排版原因，记得去掉每行开头的全角空格）』

（简化了建表建索引）

WITH

　-- 要查询的数据
　 query(prov, city, county) AS (
　　 SELECT 123, '[30, 20]', '[80, 70]'
　),

　-- 原始数据
　 data(id, prov, city, county) AS (
　　 VALUES
　　　 ROW(1, 123, '[10, 20, 30]', '[70, 80, 90]'),
　　　 ROW(2, 123, '[10, 21, 30]', '[70, 80, 90]')
　),

　-- 对原始数据的 (id, prov, city) 建多值索引，即：
　-- (1, 123, 10), (1, 123, 20), (1, 123, 30)
　-- (2, 123, 10), (2, 123, 21), (2, 123, 30)
　 idx_prov_city(id, prov, city) AS (
　　 SELECT data.id, prov, arr.id
　　 FROM data, json_table(data.city, '$[*]' COLUMNS(id INT PATH '$')) arr
　),

　-- 对原始数据的 (id, prov, county) 建多值索引，即
　-- (1, 123, 70), (1, 123, 80), (1, 123, 90)
　-- (2, 123, 70), (2, 123, 80), (2, 123, 90)
　 idx_prov_county(id, prov, county) AS (
　　 SELECT data.id, prov, arr.id
　　 FROM data, json_table(data.county, '$[*]' COLUMNS(id INT PATH '$')) arr
　),

　-- 除以 (prov, city)
　 divided_by_prov_city(id) AS (
　　 SELECT DISTINCT id
　　　 FROM idx_prov_city main
　　 WHERE NOT EXISTS (
　　　　　　　 SELECT *
　　　　　　　　 FROM query
　　　　　　　　 JOIN json_table(query.city, '$[*]' COLUMNS(city INT PATH '$')) arr
　　　　　　　　 WHERE NOT EXISTS (
　　　　　　　　　　　　　 SELECT *
　　　　　　　　　　　　　　 FROM idx_prov_city self
　　　　　　　　　　　　　 WHERE self.id = main.id
　　　　　　　　　　　　　　 AND self.prov = query.prov
　　　　　　　　　　　　　　 AND self.city = arr.city))
　),
　
　-- 除以 (prov, county)
　 divided_by_prov_county(id) AS (
　　 SELECT DISTINCT id
　　　 FROM idx_prov_city main
　　 WHERE NOT EXISTS (
　　　　　　　 SELECT *
　　　　　　　　 FROM query
　　　　　　　　 JOIN json_table(query.county, '$[*]' COLUMNS(county INT PATH '$')) arr
　　　　　　　　 WHERE NOT EXISTS (
　　　　　　　　　　　　　 SELECT *
　　　　　　　　　　　　　　 FROM idx_prov_county self
　　　　　　　　　　　　　 WHERE self.id = main.id
　　　　　　　　　　　　　　 AND self.prov = query.prov
　　　　　　　　　　　　　　 AND self.county = arr.county))
　)

-- 两个除法的商求交集，再 JOIN 原数据表，获取行记录
SELECT data.*
　 FROM divided_by_prov_city
　 JOIN divided_by_prov_county USING(id)
　 JOIN data USING(id)
GROUP BY id;

2022-08-10 09:50:10 +08:00

回复了 sunmoon1983 创建的主题 › MySQL › 求一个数据表设计的思路！

@copper20 『 county IN (1, 3)』没有表现出『 1 、3 必须同时存在』的意思吧，而是『 1 、3 有其一出现即可』？

2022-08-10 00:59:00 +08:00

回复了 Moris 创建的主题 › 正则表达式 › 请教一下，如何利用正则表达将 html 中在<p></p>之间的/筛选出来

所以，这种情况咋办？

<html>
　<p>
　　 Have a/ good/ day! 祝你 /开心！
　</p>
　<script>
　　 const s = `
　　　<html>
　　　　<p>
　　　　　 Have a/ good/ day! 祝你 /开心！
　　　　</p>
　　　</html>
　　`;
　</script>
</html>

2022-08-09 23:42:37 +08:00

回复了 sunmoon1983 创建的主题 › MySQL › 求一个数据表设计的思路！

@copper20 这个 SQL ，如何检索多个值呢？

city=["234567","345678"]

county=["7345678","6234567"]

2022-08-09 20:55:25 +08:00

回复了 sunmoon1983 创建的主题 › MySQL › 求一个数据表设计的思路！

@sunmoon1983 我很好奇，不是省一对多市一对多县吗？为啥存了县，还要存市和省呢？

2022-08-09 20:30:10 +08:00

回复了 sunmoon1983 创建的主题 › MySQL › 求一个数据表设计的思路！

@sunmoon1983 你要求速度吗？还是直接全表扫描？

可以接受全表扫描的话，直接 JSON_CONTAINS 呗

WITH
　 DATA(province, city, county) AS (
　　 VALUES
　　　 ROW(12345, '["123456","234567","345678"]', '["5123456","6234567","7345678"]'),
　　　 ROW(12345, '["123456","2345678","345678"]', '["5123456","6234567","7345678"]')
　)

SELECT *
FROM DATA
WHERE province = 12345
　 AND JSON_CONTAINS(city, CAST('["234567","345678"]' AS JSON))
　 AND JSON_CONTAINS(county, CAST('["7345678","6234567"]' AS JSON))

2022-08-09 11:42:31 +08:00

回复了 simonlu9 创建的主题 › 程序员 › 社交权限类似于朋友圈权限，遇到以下场景你会怎样设计

@simonlu9 再修正一下，10 楼 3.2 计算，这个表更应该是顺序插入，那预留 1/16 后，叶节点可存约 590 行，

若用 3.2：75~79 + (ceil(20000 / 590) ~ 20000) * 1~2 = 109 ~ 40079 次 IO

如果固态 16KB IOPS 有 13W ，那每秒最多可查询 3 ~ 1200 个类似这样的人的所有动态 ID 列表

2022-08-09 11:28:39 +08:00

回复了 simonlu9 创建的主题 › 程序员 › 社交权限类似于朋友圈权限，遇到以下场景你会怎样设计

@simonlu9 不对，「权限类型，作者 ID 」咋能成唯一索引呢。。只能是索引

另外，如果是这样设计，或许你的会比我 3 楼的设计快：

动态表『主键「动态 ID 」，索引「权限类型，作者 ID ，允许用户 IDs JSON 」，动态内容 TEXT ，…… 』

因为每条动态不用回『动态表』查权限，也不用查『其他表』是否存在该权限了

「允许用户 IDs 」字符串操作，应该比一次 IO ，快几个数量级吧

2022-08-09 11:18:25 +08:00

回复了 simonlu9 创建的主题 › 程序员 › 社交权限类似于朋友圈权限，遇到以下场景你会怎样设计

@simonlu9

> 主键如果设那么长考虑插入时候分裂情况

我 3 楼的设计，直接按照『最差情况』，全部『随机插入』，叶节点『全裂成一半』考虑的

> 允许用户（ at_user_id ）最好是建另外一个表，字符串查找不建议

我打完下面这堆，才看到你的回复。最后计算，也大体验证你所说，『单独表』更可能比『字符串查找』快

如果是我 8 楼所认为的那样设计，那么：

唯一索引『主键「权限类型，作者 ID 」，动态 ID 』，叶节点每行记录 14 字节。

随机插入情况下，一个叶节点一半空间可用，可存约 585 行。

再拿 3 楼的 3W 条动态+1W 条<指定><他人 ID>可见动态（这对 3 楼的设计无影响，且合情合理），计算下硬盘 IO：

1. 花 1 ~ 2 次 IO ，查询其他表，来确定 <他人和自己是好友>

2. 查询 <公开>、<朋友可见> 每种类型的前 585 条记录，各花费 1 ~ 2 次 IO 。往后每 585 条，再花费 1 次 IO 。

3. 查询 <指定> 类型，和第 2 步类似，但还要确定自己是否在权限内：

3.1 「允许用户 IDs 」 JSON

每条动态，都要花 1 ~ 2 次 IO （偏向 2 ~ 3 次，此表很容易变深）回『动态表』，获取「允许用户 IDs 」，然后 find_in_set 或 json 函数确定。

假设『动态表』每行记录 0.5KB （不大吧），顺序插入情况下，叶子节点预留 1/16 空间，每个叶子节点可存 30 行

运气好，用户连发 30 条动态，全在一个叶子节点里，能命中缓存 29 次。运气差，无法命中缓存

3.2 『用户动态指定可见表』

每条动态，都要花 1 ~ 2 次 IO ，exists

此表叶节点每行记录 26 字节，随机插入情况下，一半空间可用，可存约 315 行。

运气好，用户连发 315 条动态，且每条动态都只指定一人可见，则可全在一个叶节点里。反之分散各处，无法命中缓存

final. 总计：1~2 + 2 * (1~2 + ceil(10000 / 585)) + (1~2 + ceil(20000 / 585) + …) = 75 ~ 79 + …

若用 3.1：75~79 + (ceil(20000 / 30) ~ 20000) * 1~2 = 742 ~ 40079 次 IO

若用 3.2：75~79 + (ceil(20000 / 315) ~ 20000) * 1~2 = 139 ~ 40079 次 IO

如果固态 16KB IOPS 有 13W ，那每秒最多可查询 3 ~ 1000 个类似这样的人的所有动态 ID 列表

看起来，是因为每条动态，都要验证权限，导致的回表 /查表次数太多

当然，指定某人可见的动态，应该不会这么多。可以算出个阈值，超过此值这种设计不划算

我 3 楼那样的设计，是将同类动态的行记录（某人公开、某人私有、某人朋友可见、某人指定他人 1 ，某人指定他人 2 ，……），尽可能凑在一起，实现：

1. 能迅速跳过某些类别的所有行记录（如，跳过某人私有、某人指定非自己的其他所有人）
2. 能迅速定位到某一类的第一行
3. 能利用上 B+ 树的叶子节点双向链表的特性，迅速遍历完某一类的余下行

这样的表，用文件系统类比，就是（如下），要啥类别，就直接读那个文件（叶子节点），直接跳过其他所有：

用户动态权限文件夹 /
　用户 A/
　　公开动态 ID 列表.txt
　　私有….txt
　　朋友可见….txt
　　指定可见 /
　　　用户 B 可见….txt
　　　用户 C 可见….txt
　用户 B/
　　……

2022-08-09 10:03:41 +08:00

回复了 simonlu9 创建的主题 › 程序员 › 社交权限类似于朋友圈权限，遇到以下场景你会怎样设计

@simonlu9 这样设计，会有何问题呢？

另外，你是在动态表上，建唯一索引吗？

这样：动态表『主键「动态 ID 」，唯一索引「权限类型，作者 ID 」，允许用户 IDs JSON ，动态内容 TEXT ，…… 』？

或「允许用户 IDs 」独立成一个「用户动态指定可见表」『主键「动态 ID ，允许用户 ID 」』？

2022-08-09 01:20:38 +08:00

回复了 shadow1949 创建的主题 › 程序员 › SQL 苦手来请教各位大佬了。

@shadow1949 用不了窗口函数，强行自己模拟，代码看起来会很臭。。

换个思路，如果你能自己维护个『第几个工作日表』，也能很舒服。比如：

　　日期　　第几个工作日
—————　———————
09-09 周五　 1
09-10 中秋　（不要这行）
09-11 周日　（不要这行）
09-12 周一　（不要这行）
09-13 周二　 2
09-14 周三　 3
09-15 周四　 4
09-16 周五　 5
09-17 周六　（不要这行）

『大致 SQL （排版原因，记得去掉每行开头的全角空格）』

WITH

　 workday_data(date, nth, num) AS (
　　 SELECT date, nth, num
　　 FROM nth_workday LEFT JOIN orig_data USING(date)
　)

SELECT date, num,
　　　　(SELECT avg(COALESCE(num, 0)) FROM workday_data WHERE nth BETWEEN today.nth - 4 AND today.nth) avg_5,
　　　　(SELECT avg(COALESCE(num, 0)) FROM workday_data WHERE nth BETWEEN today.nth - 9 AND today.nth) avg_10
FROM workday_data today
WHERE num IS NOT NULL;

2022-08-08 23:27:55 +08:00

回复了 huzhikuizainali 创建的主题 › C++ › 为什么要用递归而不用循环？

@huzhikuizainali 我在想，会不会是你不明白，

同样是“重复”，为何有的用递归，有的用循环？

递归还有一系列入栈出栈操作，既占内存，又耗性能，这货存在意义是什么？！

2022-08-08 23:04:39 +08:00

回复了 huzhikuizainali 创建的主题 › C++ › 为什么要用递归而不用循环？

@huzhikuizainali 走迷宫用深度优先遍历啊

2022-08-08 23:01:21 +08:00

回复了 huzhikuizainali 创建的主题 › C++ › 为什么要用递归而不用循环？

@huzhikuizainali 单纯的循环不会进栈

或许，你可以试着，用循环去解决一些，原本用递归干的活儿

比如，写个走迷宫。
再如，用递归下降去解析个 json

可能写多几个，你就不爱用循环+自己维护的栈，去模拟语言实现好的递归了

2022-08-08 22:18:39 +08:00

回复了 simonlu9 创建的主题 › 程序员 › 社交权限类似于朋友圈权限，遇到以下场景你会怎样设计

@simonlu9 诶，突然发现，主键设成那样，不就一堆重复的了。。脑子瓦特了

应该是『主键：「作者 ID ，权限类型 TINYINT ，允许用户 ID ，动态 ID 」，时间』

但叶子节点的结论不变（因为还是 35 字节 / 行）

你说的『 showWith ，user_id 』索引，是『权限类型，作者 ID 』索引的意思吗？

2022-08-08 18:55:37 +08:00

回复了 huzhikuizainali 创建的主题 › C++ › 为什么要用递归而不用循环？

你不用语言的栈来实现递归，那就用自己的栈来实现循环呗（除非可优化的尾递归）

2022-08-08 18:22:09 +08:00

回复了 simonlu9 创建的主题 › 程序员 › 社交权限类似于朋友圈权限，遇到以下场景你会怎样设计

关系数据库新手求问，这样设计，性能会很差吗？

用户动态权限信息表『主键：「作者 ID ，权限类型 TINYINT ，允许用户 ID 」，时间，动态 ID 』

（假设使用 MySQL 的 Innodb 引擎 Dynamic 或 Compact 行格式，则该表的叶节点中，每行记录占 35 字节）

场景一，按时间倒序，获取访问他人主页时，应该能看到的「动态 ID 」列表

select 动态 ID
from 用户动态权限信息表
where 作者 ID = <他人 ID>
　 and (权限类型 = <公开> or
　　　(权限类型 = <指定> and 允许用户 ID = <自己 ID>) or
　　　(exists <他人和自己是好友> and 权限类型 = <朋友>))
order by 时间 desc;

假设此表 B+ 树有 3 ~ 4 层高，前两层容易被缓存，且都是随机插入，即每个叶节点只有一半可用（能存约 234 行）

如果应能看到他人主页公开、指定自己、朋友可见『各』 1W 条动态，对于此条查询，我设想会发生的硬盘 IO 次数：

1. 花 1 ~ 2 次 IO ，查询其他表，来确定 <他人和自己是好友>

2. 查询 <公开>、<指定><自己 ID>、<朋友可见> 每种类型的前 234 条记录，各花费 1 ~ 2 次 IO （从树根向叶子查）

往后每查询 234 条，再花费 1 次 IO （叶子是双向链表）。则总计 3 * ( 1~2 + ceil(10000 / 234)) = 132 ~ 135 次 IO

总结：若某人有（除自己可见外）各种类型『共』 3W 条动态，为获得这些动态 ID 列表，需读取硬盘 130 多次

如果固态 16KB IOPS 有 13W ，那每秒最多可查询 1000 个类似这样的人的所有动态 ID 列表

不知算得对不对

1 ... 13 14 15 16 17 18 19 20 21 22 ... 27

❮

❯