V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
sunmoon1983
V2EX  ›  MySQL

求一个数据表设计的思路!

  •  
  •   sunmoon1983 · 2022-08-09 19:52:59 +08:00 · 4064 次点击
    这是一个创建于 598 天前的主题,其中的信息可能已经有所发展或是发生改变。

    数据库为 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 可以是多选的! 我要怎么设计数据表才能够方便搜索呢?求大神解惑

    38 条回复    2022-08-17 16:41:26 +08:00
    rrfeng
        1
    rrfeng  
       2022-08-09 19:55:49 +08:00   ❤️ 1
    拆成两张表,或者直接拍扁。
    sunmoon1983
        2
    sunmoon1983  
    OP
       2022-08-09 20:05:49 +08:00
    @rrfeng 没太懂,是要搞一张 city county 和数据的关系表吗?
    Maboroshii
        3
    Maboroshii  
       2022-08-09 20:23:51 +08:00 via Android
    for 循环分别搜吧
    wxf666
        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))
    leonme
        5
    leonme  
       2022-08-09 20:32:46 +08:00 via iPhone
    方法 1 、先根据 province 过滤数据,然后在内存中过滤 city 和 country 。 方法 2 、city 和 country 重新设计,拍平存,不要聚合后再存,不然查询效率低。 方法 3 、采用 ES 查询
    sunmoon1983
        6
    sunmoon1983  
    OP
       2022-08-09 20:32:59 +08:00
    @wxf666 运营会经常搜索,肯定会有速度要求的,大佬
    sunmoon1983
        7
    sunmoon1983  
    OP
       2022-08-09 20:34:12 +08:00
    @leonme 拍平存是啥意思?没太懂
    leonme
        8
    leonme  
       2022-08-09 20:36:54 +08:00 via iPhone
    @sunmoon1983 单独的 city 字段,然后记录 province 和 city 的关联关系
    wxf666
        9
    wxf666  
       2022-08-09 20:55:25 +08:00
    @sunmoon1983 我很好奇,不是 省 一对多 市 一对多 县 吗? 为啥存了 县,还要存 市 和 省 呢?
    kran
        10
    kran  
       2022-08-09 21:27:23 +08:00 via Android
    json_search/json_contains
    copper20
        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%" 之类的,用得上索引,而且代码上会方便许多吧
    wxf666
        12
    wxf666  
       2022-08-09 23:42:37 +08:00
    @copper20 这个 SQL ,如何检索多个值呢?

    city=["234567","345678"]

    county=["7345678","6234567"]
    LeeReamond
        13
    LeeReamond  
       2022-08-10 03:02:46 +08:00
    有多字段需求要拆表,你现在这种每行里存列表的形式没法多段索引的。比如需求上 city 需要 in 搜索,那 city 就必须是单独一列,每行只储存一个 city 和它对应的对象 id ,这样才可以 in a,b,c city 这么搜索,其他列同理
    hoopan
        14
    hoopan  
       2022-08-10 08:36:32 +08:00
    这个表好奇怪,是存省市区的表? city 跟 county 怎么对应?
    xaplux
        15
    xaplux  
       2022-08-10 08:43:52 +08:00
    如果是精确匹配,将 JSON 拆分成关系表
    如果是模糊匹配,那上 ES 吧
    copper20
        16
    copper20  
       2022-08-10 09:17:24 +08:00
    @wxf666 把等于运算符换成 IN 运算符吧 比如就 county IN (1, 3)
    panda1079
        17
    panda1079  
       2022-08-10 09:39:34 +08:00
    对于这种可扩展又参差不齐的数据我推荐你用 mongo
    wxf666
        18
    wxf666  
       2022-08-10 09:50:10 +08:00
    @copper20 『 county IN (1, 3)』没有表现出『 1 、3 必须同时存在』的意思吧,而是『 1 、3 有其一出现即可』?
    Saxton
        19
    Saxton  
       2022-08-10 10:03:27 +08:00
    难为 mysql 了
    pannanxu
        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)
    ```
    sanestays
        21
    sanestays  
       2022-08-10 13:02:53 +08:00
    可以尝试一下虚拟列
    yjhatfdu2
        22
    yjhatfdu2  
       2022-08-10 13:39:37 +08:00
    使用 postgresql ,直接 where city@>'["123456"]'::jsonb and county @>'["23456","34567"]'::jsonb 还可以索引,基本上是最好的方案了
    yjhatfdu2
        23
    yjhatfdu2  
       2022-08-10 13:42:27 +08:00
    见文档 http://www.postgres.cn/docs/12/datatype-json.html#JSON-INDEXING ,是时候换掉落后的 mysql 了
    encro
        24
    encro  
       2022-08-10 13:48:07 +08:00
    从 MySQL 8.0.17 开始,InnoDB 支持多值索引
    b2byco
        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 表里加名称字段以及启用日期和结束日期,查询的时候再根据时间过滤下就好了
    JinyAa
        26
    JinyAa  
       2022-08-10 14:12:50 +08:00
    直接一个新字段行政区划内部编码,这种东西不是国家固定的吗?为什么要设计这么复杂,比如苏州市高新区双凤镇的行政区划内部编码就是 1.320000.320500.320585.320585105 ,往前推 1.320000.320500.320585 就是苏州市高新区。你要查哪些地域直接前缀匹配啊,多个地域没有从属关系的直接 in
    wxf666
        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;
    wxf666
        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
    wxf666
        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))
    sunmoon1983
        30
    sunmoon1983  
    OP
       2022-08-10 17:22:36 +08:00
    @wxf666 我可以更换版本的,现在只是开发阶段,在我的开发环境中是 5.7 ^_^
    wxf666
        31
    wxf666  
       2022-08-10 17:26:33 +08:00
    @sunmoon1983 可以问下,MySQL 8 出来也有六年了,为啥还优先选用旧版本吗?
    fzzff
        32
    fzzff  
       2022-08-10 17:30:49 +08:00
    这个类似的需求我也遇到过, 因为涉及的逻辑比较多所以没有动原表存的 json 字段, 增加了个一对多的表专门用来查询
    RangerWolf
        33
    RangerWolf  
       2022-08-10 17:32:10 +08:00
    虚拟列试试看
    wxf666
        34
    wxf666  
       2022-08-10 17:50:30 +08:00
    @fzzff 你是如何写『获取所有「同时满足多个条件」的行记录』的 SQL 语句的?
    sunmoon1983
        35
    sunmoon1983  
    OP
       2022-08-10 20:24:12 +08:00
    @wxf666 懒,哈哈哈哈,以前的系统还有 5.7 的,换了怕有问题呀
    wxf666
        36
    wxf666  
       2022-08-10 20:39:06 +08:00
    @sunmoon1983 我还好奇,你的原表这些字段是啥含义?

    不是 province 一对多 city 一对多 county 吗?为啥存了 county 还要存前面两个?
    joslin1215
        37
    joslin1215  
       2022-08-11 17:09:05 +08:00
    这不是常见的一对多设计思路么?
    需要用作查询,就关系表,仅用于展示,直接赛主表
    sy20030260
        38
    sy20030260  
       2022-08-17 16:41:26 +08:00
    如果是 RT 敏感且高 QPS 的业务场景,还是多建一张表才是王道,直白简单易排查易维护,Keep It Simple Stupid
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2808 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 15:02 · PVG 23:02 · LAX 08:02 · JFK 11:02
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.