数据库为 MySQL5.7
一张表table1
吧里面有 30W+数据,字段为id bigint(20),full_name varchar(66) province bigint(20),city JSON, county JSON
要展示符合下面这些条件的记录:
注意:city 和 county 储存的数据为地区的编码,如 city=["123456","234567","345678"],county=["5123456","6234567","7345678"]这种,我可以修改数据表结构
现在有一个搜索的需求,想要按照 city 和 county 搜索对应的数据,比如,搜索的条件为前端发送过来的,province=12345,city=["234567","345678"],county=["7345678","6234567"]
就是说,搜索的 city 和 county 可以是多选的!
我要怎么设计数据表才能够方便搜索呢?求大神解惑
1
rrfeng 2022-08-09 19:55:49 +08:00 1
拆成两张表,或者直接拍扁。
|
2
sunmoon1983 OP @rrfeng 没太懂,是要搞一张 city county 和数据的关系表吗?
|
3
Maboroshii 2022-08-09 20:23:51 +08:00 via Android
for 循环分别搜吧
|
4
wxf666 2022-08-09 20:30:10 +08:00
@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)) |
5
leonme 2022-08-09 20:32:46 +08:00 via iPhone
方法 1 、先根据 province 过滤数据,然后在内存中过滤 city 和 country 。 方法 2 、city 和 country 重新设计,拍平存,不要聚合后再存,不然查询效率低。 方法 3 、采用 ES 查询
|
6
sunmoon1983 OP @wxf666 运营会经常搜索,肯定会有速度要求的,大佬
|
7
sunmoon1983 OP @leonme 拍平存是啥意思?没太懂
|
8
leonme 2022-08-09 20:36:54 +08:00 via iPhone
@sunmoon1983 单独的 city 字段,然后记录 province 和 city 的关联关系
|
9
wxf666 2022-08-09 20:55:25 +08:00
@sunmoon1983 我很好奇,不是 省 一对多 市 一对多 县 吗? 为啥存了 县,还要存 市 和 省 呢?
|
10
kran 2022-08-09 21:27:23 +08:00 via Android
json_search/json_contains
|
11
copper20 2022-08-09 23:24:39 +08:00
如果单独考虑这个需求的话,或许可以把 city 和 county 挪到另外的表里存。设计 foo_city(id, city) 表和 foo_county(id, county) 表,id 和 city / id 和 county 都设为主键(就是说直接设计两个关系表),然后在这两个表上分别建 city 字段和 county 字段上的索引,另外在现在的 table1 的 province 上也建立索引
捞数据的时候可以直接: (SELECT table1.idtable1 FROM table1 INNER JOIN (SELECT idtable1 FROM foo_county WHERE county = <county code>) co INNER JOIN (SELECT idtable1 FROM foo_city WHERE city = <city code>) ci WHERE province = <procince code>) 不过话说回来,地区代码这种事情用 varchar 存国标的地区代码,检索的时候直接用 LIKE "110101%" 之类的,用得上索引,而且代码上会方便许多吧 |
12
wxf666 2022-08-09 23:42:37 +08:00
|
13
LeeReamond 2022-08-10 03:02:46 +08:00
有多字段需求要拆表,你现在这种每行里存列表的形式没法多段索引的。比如需求上 city 需要 in 搜索,那 city 就必须是单独一列,每行只储存一个 city 和它对应的对象 id ,这样才可以 in a,b,c city 这么搜索,其他列同理
|
14
hoopan 2022-08-10 08:36:32 +08:00
这个表好奇怪,是存省市区的表? city 跟 county 怎么对应?
|
15
xaplux 2022-08-10 08:43:52 +08:00
如果是精确匹配,将 JSON 拆分成关系表
如果是模糊匹配,那上 ES 吧 |
17
panda1079 2022-08-10 09:39:34 +08:00
对于这种可扩展又参差不齐的数据我推荐你用 mongo
|
18
wxf666 2022-08-10 09:50:10 +08:00
@copper20 『 county IN (1, 3)』没有表现出『 1 、3 必须同时存在』的意思吧,而是『 1 、3 有其一出现即可』?
|
19
Saxton 2022-08-10 10:03:27 +08:00
难为 mysql 了
|
20
pannanxu 2022-08-10 10:23:00 +08:00
```sql
create table table1 ( id bigint primary key, full_name varchar(66) ); create table tab1_mapping ( id bigint primary key, table1_id bigint, province bigint, city int, county int, index (table1_id), index (province, city, county) ); select * from table1 t1 inner join tab1_mapping t1m on t1.id = t1m.table1_id where province = 1 and city in (1, 2, 3) and county in (1, 2, 3) ``` |
21
sanestays 2022-08-10 13:02:53 +08:00
可以尝试一下虚拟列
|
22
yjhatfdu2 2022-08-10 13:39:37 +08:00
使用 postgresql ,直接 where city@>'["123456"]'::jsonb and county @>'["23456","34567"]'::jsonb 还可以索引,基本上是最好的方案了
|
23
yjhatfdu2 2022-08-10 13:42:27 +08:00
见文档 http://www.postgres.cn/docs/12/datatype-json.html#JSON-INDEXING ,是时候换掉落后的 mysql 了
|
24
encro 2022-08-10 13:48:07 +08:00
从 MySQL 8.0.17 开始,InnoDB 支持多值索引
|
25
b2byco 2022-08-10 13:58:17 +08:00
看起来是 table1 每一行对应多个地区(区域),每个地区有省市县三个维度。
table1( id,full_name ) 原来的 table1 area( id , province , city ,county ) 地区表 index1 province , city ,county ;index 2 city,county ;index 3 county relation( id , table1id , areaid ) 关系表 index 1 table1id , areaid ;index 2 areaid, table1id select t1.* from area a inner join relation r on a.id = r.areaid and a.province = xxx and a.city = xxx and a.county = xxx -- 或者别的过滤语句 inner join table1 t1 on r.table1id = t1.id 不知道地区名字你们怎么处理的,如果出现同样的编码改名,且要保留旧数据用旧名字的话,可以直接在 area 表里加名称字段以及启用日期和结束日期,查询的时候再根据时间过滤下就好了 |
26
JinyAa 2022-08-10 14:12:50 +08:00
直接一个新字段行政区划内部编码,这种东西不是国家固定的吗?为什么要设计这么复杂,比如苏州市高新区双凤镇的行政区划内部编码就是 1.320000.320500.320585.320585105 ,往前推 1.320000.320500.320585 就是苏州市高新区。你要查哪些地域直接前缀匹配啊,多个地域没有从属关系的直接 in
|
27
wxf666 2022-08-10 14:19:04 +08:00
@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; |
28
wxf666 2022-08-10 14:22:48 +08:00
@sunmoon1983 上面有处地方忘改了:
divided_by_prov_county(id) AS ( SELECT DISTINCT id FROM idx_prov_『改成这样:county 』 main |
29
wxf666 2022-08-10 14:31:27 +08:00
@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)) |
30
sunmoon1983 OP @wxf666 我可以更换版本的,现在只是开发阶段,在我的开发环境中是 5.7 ^_^
|
31
wxf666 2022-08-10 17:26:33 +08:00
@sunmoon1983 可以问下,MySQL 8 出来也有六年了,为啥还优先选用旧版本吗?
|
32
fzzff 2022-08-10 17:30:49 +08:00
这个类似的需求我也遇到过, 因为涉及的逻辑比较多所以没有动原表存的 json 字段, 增加了个一对多的表专门用来查询
|
33
RangerWolf 2022-08-10 17:32:10 +08:00
虚拟列试试看
|
35
sunmoon1983 OP @wxf666 懒,哈哈哈哈,以前的系统还有 5.7 的,换了怕有问题呀
|
36
wxf666 2022-08-10 20:39:06 +08:00
|
37
joslin1215 2022-08-11 17:09:05 +08:00
这不是常见的一对多设计思路么?
需要用作查询,就关系表,仅用于展示,直接赛主表 |
38
sy20030260 2022-08-17 16:41:26 +08:00
如果是 RT 敏感且高 QPS 的业务场景,还是多建一张表才是王道,直白简单易排查易维护,Keep It Simple Stupid
|