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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 2028 天前的主题，其中的信息可能已经有所发展或是发生改变。

数据库：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 时，得到文件路径
子查询？视图？

文件

上传

主键

fid

6 条回复 • 2019-05-05 14:55:05 +08:00

season8

2019-05-05 13:33:10 +08:00

感觉业务挺简单啊，也没说大量数据的问题:
首先，file_match 表的 uid 需要设置索引
然后，直接关联查询,都不用考虑效率问题

xivisi

2019-05-05 13:53:57 +08:00

@season8
为了说明问题，目前是将业务是简化的。理论上来说，随着上线时间、用户数增加，file_match 表的增长将会远超 file_path，使用 join 语句，是否会导致性能问题？

xivisi

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

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

2019-05-05 14:38:47 +08:00

@xivisi 如果表大了 join 效率低可以考虑在业务层分两次查询
不过看情况应该还好，file_path 的 hash 应该是唯一的吧，优化器应该会选择 file_path 作为驱动表，那样的话 join 效率还是很高的

xivisi

2019-05-05 14:55:05 +08:00

@season8
@littlewing

感谢 2 位指导，目前先这样用，后续遇到问题再优化。