请教多标签查询怎么做效率高?

[freewind]

主表大概 100 万条数据，标签有 4000 个左右，有层级划分，每条数据有几十个标签。

按标签查询的时候要把打了当前标签和它下级标签的数据全部查出来

一次要查询多个标签, 还会和其它多个条件组合查询

标签列做了索引, 用 a,b,c,c01 这样保存

现在是用 find_in_set 和 like 查询有些慢

请问要怎么做才能提高查询速度?

数据库是 mysql 5.7

[yjhatfdu2]

你这样完全没用上索引，你这个场景，最好就使用 postgresql ，使用 array 或者 json 存 tags ，然后建立 GIN 索引，然后每次查询把标签和子标签组成目标 tags ，然后用 select * from xxx where tags @> ARRAY[tag1,tag2,tag3]，就可以用上 GIN 索引，一个亿数据也没啥

[freewind]

@yjhatfdu2 #1 谢谢, 我去找找这方面的资料

[wanniwa]

@freewind #2 我们就是用的这个

[Morriaty]

100 万条数据，感觉直接弄个内存级的搜索引擎就行了，就是内存和数据库的一致性要写点逻辑维护下。

预算够的话，直接上 es 省事，弄个 nested array 字段就能解决

[yidinghe]

提示词：我正在做一个关系数据库的表设计。要保存的数据分为主表记录和标签两种，其中一条主表记录可关联多个标签，而标签存在层级结构。表设计需要满足下面的查询要求：以标签 ID 为条件，查询其本身和所有下级标签关联的主表记录。

下面是豆包的生成内容：
https://www.doubao.com/thread/w38665315320066

你也可以用其他 AI 尝试。

[freewind]

@wanniwa #3 是用了#1 的方法吗

@Morriaty #4 小公司，预算不足

@yidinghe #5 多了一个关系表，不知道查询起来速度怎么样

[hnliuzesen]

如果打算用 pg ，也可以试试 [recursive cte]( https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE) + [lateral]( https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL) 一起使用，可以和上面的建议配合

[yjhatfdu2]

实测来了，
创建测试表，并插入 1 亿条测试数据，每一条包含 50 个随机标签，每个标签有 4000 种可能
create table tags_test(id serial primary key,tags array[int]);
create function random_array() returns int[] as $$ select array_agg((4000*random())::int) from generate_series(1,50)) $$;
insert into tags_test(tags) select random_array() from generate_series(1,100000000);
创建 GIN 索引
//最好临时增加 maintenance_work_mem ，会加快索引构建
//set maintenance_work_mem to 1GB;
create index ON tags_test using gin(tags);
简单查个包含几个 tags 的数据
postgres=# select count(*) from tags_test where tags @>array[1,2,3,4];
count
-------
2
(1 row)

Time: 135.185 ms
postgres=# select count(*) from tags_test where tags @>array[1,2,3];
count
-------
190
(1 row)

Time: 123.327 ms
当然，实际情况下标签肯定不是随机分布的，可能会更快或者更慢，也可以试试用 jsonb 来存和查，结果应该差不多

[freewind]

@hnliuzesen #7 没用过，学习一下

@yjhatfdu2 #8 👍 按这种方法，是不是要把标签平铺，先把所有下级标签存进去

[yjhatfdu2]

@freewind 可以这样，也可以像#7 说的一样，每次用递归 CTE 把所有子层级标签查出来作为条件，这样标签层级可以更新，存储容量也可以小点，你们标签少可以考虑用更小的 int 做标签，也能省

[freewind]

@yjhatfdu2 #10 要是递归速度还行就打算先用这个, 数据的标签经常改, 标签数量也在不断增加

[wxf666]

@yjhatfdu2 #8 这个索引，实际有多大呢？

我怀疑它是按照 (array_value, id) 来存的，即存了 50 亿条。

每条 10 字节的话，也要快 50 GB 了。。

[jeesk]

自己写倒排序, 维护索引. 关键字: 广告系统布尔表达式, 倒排序

[GeekGao]

最简单的方案：标签字段合并、全文索引标签集合字段。
只是信息有限不知道符不符合你们的场景。

[yjhatfdu2]

@wxf666 这个索引占用 9G 左右，pg 的 GIN 是通用反向索引，并不是直接按照这样展开存的，原理类似 es 的反向索引

[yjhatfdu2]

@freewind 你这几千条标签递归查估计也就 1ms 之内的事情

[guochenglong]

还有一种 bitmap 存储，用位运算查

[wxf666]

@yjhatfdu2 #15 是 (tag_id, main_ids_array) 这样存的吗？

即，有 4000 条记录，总计 50 亿个主表 ID （平均每条记录有 125W 个主表 ID ）？

每个 ID 有 4 字节，算下来也要 (5e9 * 4) / (1 << 20) = 18.6 GB 呀。。

[yjhatfdu2]

@wxf666 你算的很对，表大概 20G 多一点，但是索引不是这么存的，你可以理解为反向索引存了 tag 和这个 tag 在哪些行中出现，这里面我怀疑是用 bitmap+压缩来实现的，有兴趣可以看下 pg 的 gin 相关的代码，所以实际上索引体积只有 9G 左右

[tedzhou1221]

好奇请教一下，“标签列做了索引, 用 a,b,c,c01 这样保存”