为了简化一下问题的描述,以下统用省、市、县、乡、村来表示可见级别。 比如说发一篇文章,文章可以设置显示区域[多个区域]。访问也可能归属于多个区域[比如即住在北京,有时也住在天津] 1 、比如可以设置兰考县, 那当访客是 兰阳街道、考城镇 都是可以见到的 2 、比如只设置考城镇, 那就只有这样一个镇的访客可见 3 、也可以设置可见为 兰考县、 设置不可见为 兰阳街道,那么这时除了 兰阳街道,兰考县下的其它城镇都可以见到。 4 、也可以单独设置多个区域,比如说只对北京、天津访客可见。也可能只对北京 11 、考城镇 410225103 这两地可见
id | 名称 name | 父 ID pid |
---|---|---|
10 | 中国 | 0 |
11 | 北京 | 10 |
12 | 天津 | 10 |
…… | …… | …… |
41 | 河南省 | 0 |
1101 | 北京市 | 11 |
4101 | 郑州市 | 41 |
4102 | 洛阳市 | 41 |
…… | …… | …… |
410202 | 龙亭区 | 4102 |
410204 | 鼓楼区 | 4102 |
410225 | 兰考县 | 4102 |
410225001 | 兰阳街道 | 410225 |
410225103 | 考城镇 | 410225 |
字段 | 中文 |
---|---|
id | 主键 |
title | 标题 |
content | 内容 |
字段 | 中文 |
---|---|
visible_city_ids | 可见城市 ID 11,410225 |
invisible_city_ids | 不可见城市 ID, 410225103 |
visible_city_ids = 11,410225
invisible_city_ids = 410225103
visible_city_ids = ''|null
invisible_city_ids = 410225103
id | 文章 id | 区域 ID | 是[1]否[0]可见 |
---|---|---|---|
1 | 1 | 11 | 1 |
2 | 1 | 410225 | 1 |
3 | 1 | 410225103 | 0 |
4 | 2 | 410225103 | 0 |
这样基本可以满足需求,但是在查询的时候要对比访客的各种级别、以及归属。比如说访客 A 本身考城镇的,还是河南这个地区的 BOSS ,就算设置了兰考县不可见,A 还是可以看见这篇文章的。
不过这样查询的 sql 会比较复杂,所以想来请教下各位 DBA 大佬,这种场景一般怎么样设计,SQL 怎么样写查询效率会比较高,还是说会放到程序里面去处理? 抱歉,可能我说的有点乱。如果还有没说清楚的地方还请指出,谢谢
1
YVAN7123 276 天前
我理解你的问题是关于如何设计一个数据库模型和编写高效的查询语句,以满足文章可见性在多个区域设置下的需求。你提到了两种设计方案,我会就每种方案分别进行讨论,并提供一些可能的解决方案。
### 方案一:在 t_article 表中加字段 这种方案中,你在文章表 t_article 中加入了两个字段,分别表示可见城市和不可见城市的 ID 列表。这样的设计在简单的情况下是可行的,但在复杂的查询和处理情况下可能会变得繁琐。 **查询的复杂性:** 当涉及多层级的区域关系以及访客的属地归属时,编写查询可能变得复杂。需要处理访客属地与文章可见性的交叉匹配。 **解决方案:** 如果冗余数据量不大,你可以尝试在程序层面进行一些数据预处理,将一些常见的查询结果缓存,以减轻数据库查询压力。 ### 方案二:建立中间表 t_article_to_city 这种方案通过建立一个中间表 t_article_to_city ,记录文章和可见城市/不可见城市的关系。这种做法在处理复杂的多对多关系上更加灵活,但查询可能会涉及多表连接。 **查询的复杂性:** 查询涉及到多个表的连接,可能会增加查询的复杂性和性能开销。 **解决方案:** 使用合适的索引来优化查询性能,尽可能避免全表扫描。合理使用缓存,以及数据库优化技术,如合理的索引设计、查询优化等。 ### 建议的优化方案: 1. **索引优化:** 无论哪种方案,都要确保你的表上有合适的索引。对于 t_article_to_city 表,可以在 `article_id` 和 `city_id` 列上建立复合索引,以加快查询速度。 2. **缓存:** 考虑使用缓存来缓存常用的查询结果,这可以减轻数据库的负担。例如,可以缓存某个访客在某个时间点下有权访问的文章列表。 3. **存储过程和函数:** 在数据库中,你可以使用存储过程或函数来封装复杂的查询逻辑,使得业务逻辑更集中,同时减少网络传输开销。 4. **数据库分区:** 如果数据量较大,可以考虑使用数据库分区技术,将数据按照一定规则分散存储,以提高查询性能。 5. **合理拆分数据:** 根据实际业务情况,合理拆分数据表,避免一张表的数据量过大。 6. **测试和优化:** 在设计数据库结构和编写查询语句后,一定要进行充分的测试和性能优化,确保系统在不同访问情况下都能够正常高效运行。 总的来说,数据库设计和查询性能优化是一个复杂的过程,需要根据实际情况进行权衡和调整。在设计过程中,要考虑数据规模、查询频率、数据变更频率等因素,以及合理使用数据库技术和优化手段来满足业务需求。同时,将一些复杂的业务逻辑尽可能地移到程序层面,以减轻数据库的压力。 |
2
wxf666 276 天前
感觉直接在 t_article 加字段比较可行。
大家也常说,回表查询,速度会变慢嘛。搞个中间表,不就相当于回表查询了。。 但应该也有限度。如果设立的区域过多,还不如直接去有索引的表查了? |
3
whooami OP |
4
v2eb 276 天前
1.可以查看的各个区域都新增一条相同数据,
2. 各个区域新增一条数据, 加个字段指向源数据, 便于修改 |
5
icql 276 天前 via iPhone
感觉这个场景存白名单就行。中间表字段:文章 id/省 id-市 id-区 id-镇 id ,文章 1 河北省石家庄市和湖南省 可见,就存两条记录 文章 1/河北省-石家庄市-ALL-ALL 文章 1/湖南省-ALL-ALL-ALL ,查询时只有一个 poi 地址组合一下 ALL 用 in 去查。如果不加中间表要用可见性过滤分页查文章就可能不行了
|
6
whooami OP |
8
newaccount 276 天前
看起来像是经典的树形菜单存表问题?
如果使用关系表保存,是很灵活,但是查询起来烦的要死 一般还是通过字符串直接级别拼接的方式,查询可以左 like ,方便,效率也还可以 排除列表是不是可以通过第二个查询,然后程序里做个 A - B 来处理呢 |
9
zypy333 276 天前
jeecg 用 like ,每个业务表一个字段 sys_org_code ,上级编码 A01 ,下级编码 A01A02 类似这样
|
10
v2eb 276 天前
@whooami 两个都没有中间表, 只在文章表里操作。1 是在文章表里添加多条记录, 区分地域。2 还是在文章表里添加多条记录, 区分地域, 由于多条数据需要考虑一致性问题, 加个字段指向初始数据。
|
11
Mandelo 276 天前 via iPhone
做过类似的,有个属性叫可视范围,公开,私密,指定可见这些。私密就是筛选创建人自己如果指定范围就会弹出个带 checkbox 的树,中间表存的是根结点 id 和文章 id 。
|
12
vivisidea 276 天前
我会倾向于中间表 t_article_to_city
article 表里面加字段 visible_city_ids 的方式,如果 id 很多呢?上百个怎么办,你的字段长度怎么定? 如果要统计哪些文章在某个城市可见呢?用 like 查询么? |
13
zhazi 276 天前
看下 abac 基于请求者拥有的属性授予对服务的访问权
|
14
cpstar 276 天前
我觉得最复杂的是用关系型表来表述树状结构,也就是 t_city 这个,然后问题就出现在,如果对北京不可见,对海淀可见么?那么这里自然有一个继承,怎么继承,即便是上了 t_article_to_city ,那一个 1-11-0 是否还需要搞 1-1101-0 以及 1-110106-0 ?估计再关系表的区域 ID 上,还需要做点计算文章。
|
15
whooami OP |
16
knightdf 275 天前
看起来就是资源权限问题
|
18
Rever4433 275 天前
你这个需求是用户管理系统里的部门概念的升级版。考虑到行政区的数据量,我认为中间表是最合适的。
|
22
hfywy 275 天前
黑白名单不多的话,建议是用 t_article 加字段方案,可以减少中间表的查询。黑白名单多的话,只用中间表估计也不够,请求量大的情况下建议加一层缓存。
BOSS 角色这个就是判断优先级的,角色级别已经可见,就可以不用往下判断区域可见性。 |