V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
xivisi
V2EX  ›  程序员

数据库新手求指导,多表查询的优化

  •  
  •   xivisi · 2019-05-05 11:18:28 +08:00 · 1324 次点击
    这是一个创建于 1147 天前的主题,其中的信息可能已经有所发展或是发生改变。
    数据库:PostgreSQL
    目的功能:主要用于管理用户上传文件处理
    1、对于文件 hash ( SHA256 )相同的文件只保留一份,在文件系统中根据上传时间多级目录存储;
    2、假如用户 A 上传了文件 A,但是如果用户 B 并没有上传过 A,则不能访问文件 A,如果用户 B 上传了文件 A(此时秒上传)就可以访问文件 A。

    根据此需求设计了 2 个表(隐含 user 表,此处省略):
    1、file_path (其余省略)
    ① id:自增唯一主键,整数
    ② hash: 主键非空,固定长度字符串,同时上传的文件将会被重名为该值+原文件名后缀
    ③ path:非空字符串,磁盘相对路径
    2、file_match (简化)
    ① id:自增唯一主键,整数
    ② fid: 等效于 file_path.id
    ③ uid:等效于 user.id

    在设计查询语句时,怎么写比较好?

    1、给出 uid、hash 时,得到文件路径
    子查询?视图?
    6 条回复    2019-05-05 14:55:05 +08:00
    season8
        1
    season8  
       2019-05-05 13:33:10 +08:00
    感觉业务挺简单啊,也没说大量数据的问题:
    首先,file_match 表的 uid 需要设置索引
    然后,直接关联查询,都不用考虑效率问题
    xivisi
        2
    xivisi  
    OP
       2019-05-05 13:53:57 +08:00
    @season8
    为了说明问题,目前是将业务是简化的。理论上来说,随着上线时间、用户数增加,file_match 表的增长将会远超 file_path,使用 join 语句,是否会导致性能问题?
    xivisi
        3
    xivisi  
    OP
       2019-05-05 14:17:31 +08:00
    @season8 比如我写一条这样的查询语句:
    SELECT file_match.uid uid, file_path.hash hash, file_path.path path
    FROM file_match JOIN file_path ON file_path.id=file_match.fid WHERE hash='A7FCFC6B5269BDCCE571798D618EA219A68B96CB87A0E21080C2E758D23E4CE9' AND file_match.uid=12345;

    总感觉当表大了之后,查询效率会很低
    season8
        4
    season8  
       2019-05-05 14:38:44 +08:00
    @xivisi 应该不存在
    1. file_path 表可以说是根据 unique key 查询的,很快,量也不大
    2. file_match 虽然量累计起来大,但 uid 有索引,根据 uid 过滤到的记录时很少的,所以也可以用 子查询 in 来实现,除非这个表中某几个用户的上传量超过了 1/5(我在 mysql 中发现数据量比较大时,目标数据量超过大概 1/5 就会变成全表扫描)

    不放心的话可以看下执行计划
    littlewing
        5
    littlewing  
       2019-05-05 14:38:47 +08:00
    @xivisi 如果表大了 join 效率低可以考虑在业务层分两次查询
    不过看情况应该还好,file_path 的 hash 应该是唯一的吧,优化器应该会选择 file_path 作为驱动表,那样的话 join 效率还是很高的
    xivisi
        6
    xivisi  
    OP
       2019-05-05 14:55:05 +08:00
    @season8
    @littlewing

    感谢 2 位指导,目前先这样用,后续遇到问题再优化。
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1515 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 17:40 · PVG 01:40 · LAX 10:40 · JFK 13:40
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.