V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
allenwuli
V2EX  ›  数据库

PG 库,查询优化。

  •  
  •   allenwuli · 2020-06-02 14:29:00 +08:00 · 1769 次点击
    这是一个创建于 1630 天前的主题,其中的信息可能已经有所发展或是发生改变。

    大佬们,我现在有一张表。表结构如下,sys_id,tm_id,int_cls,logo_array,date_updated. tm_id,有索引( btree )。logo_array(text 类型,是图片经过 numpy 转换的 np 数组,shape 是 96*96 很大)。 我现在需要根据 tm_id 取 logo_array,一次 1000 条.

    sql: select * from 表名 where tm_id in ('','',....,''); -- (一千个 tm_id)

    表现在有五百多万条数据,以后还有增量数据。 我现在每次取一千条要花 6-9 秒,太耗时了。取一条的话豪秒出,取一百条也要花 1 秒左右! 我在大数据量方面没有经验,请问各位大佬们有没有什么好的优化方法,提高我取数的效率。 谢过各位大佬了!

    18 条回复    2020-06-02 18:08:36 +08:00
    limboMu
        1
    limboMu  
       2020-06-02 14:52:49 +08:00
    按你的想法想要多久能取出数据啊?
    allenwuli
        2
    allenwuli  
    OP
       2020-06-02 14:56:46 +08:00
    @limboMu 越快越好啊,秒出结果最好了!
    limboMu
        3
    limboMu  
       2020-06-02 15:29:52 +08:00
    @allenwuli 尽量把信息描述完全一些吧,比如执行环境是单机还是网络,查询计划是什么样子的。
    reus
        4
    reus  
       2020-06-02 15:32:00 +08:00 via Android
    这还嫌慢?你取 100 条不就一秒以下了吗?
    换 hash 索引可能会快一丁点
    allenwuli
        5
    allenwuli  
    OP
       2020-06-02 15:44:09 +08:00
    @limboMu 需要将整张表都读一次,所以我一次取一千。一轮下来还是很久的!有专门的数据库服务器。
    allenwuli
        6
    allenwuli  
    OP
       2020-06-02 15:44:38 +08:00
    @reus 要将表轮一遍,太耗时了。
    reus
        7
    reus  
       2020-06-02 15:49:20 +08:00
    @allenwuli 多线程分片取
    limboMu
        8
    limboMu  
       2020-06-02 15:50:25 +08:00
    @allenwuli 看一下数据库服务器的带宽吧,感觉瓶颈应该是在哪里
    allenwuli
        9
    allenwuli  
    OP
       2020-06-02 15:53:54 +08:00
    @limboMu 好的,谢了大佬。
    allenwuli
        10
    allenwuli  
    OP
       2020-06-02 15:54:12 +08:00
    @reus 谢了大佬,我试试
    mahone3297
        11
    mahone3297  
       2020-06-02 16:07:07 +08:00
    你说了 logo_array 很大,所以,就是要很多磁盘 io 去取,应该无法优化吧
    where 条件你都已经加了索引了
    allenwuli
        12
    allenwuli  
    OP
       2020-06-02 16:34:40 +08:00
    @mahone3297 是啊,感觉很烦。
    pmispig
        13
    pmispig  
       2020-06-02 17:10:24 +08:00
    你这个慢,要么是带宽问题,要么是 IO 问题,好像没啥优化的余地吧 0.0
    你看看你取 1000 条的数据量是多大
    shenjixiang
        14
    shenjixiang  
       2020-06-02 17:15:28 +08:00
    图片存数据库这做法不太合理啊,logo_array 建索引太浪费磁盘。如果小于 4kb 的话可以尝试一下 tm_id 和 logo_array 建立联合索引。
    如果你的 tm_id 查询范围过大,可能还走不了索引
    allenwuli
        15
    allenwuli  
    OP
       2020-06-02 17:34:52 +08:00
    @shenjixiang 是很不合理,原始图片数据还是 base64 编码后存数据库的。我们现在做图片算法模型比对的,就将那个图片原始表同步过来,经过筛洗,不需要的 tm_id 被剔除。base64 的字段,自己解码再 numpy 转换的。用原始的更费事,更慢。现在的表少了不少数据,还不要 base64 转换 numpy 。其他没想到好的办法,这方面经验不足。
    JDog
        16
    JDog  
       2020-06-02 17:50:56 +08:00
    看表结构似乎从 SQL 方面优化已经没啥效果了,因为 logo_array 这个字段数据量太大...响应时间慢应该是卡在 IO 这块。
    1.PG 缓存优化(需要加内存)
    2.更换更好的 SSD(也需要花钱)
    3.主从复制,读写分离(加机器 == 花钱)
    shenjixiang
        17
    shenjixiang  
       2020-06-02 17:51:08 +08:00
    @allenwuli #7 说的多线程分片取是个好办法,可以试试,图片这种大数据本身就没有特别大的优化方式,只能在每个代码环节看看执行效率
    MoYi123
        18
    MoYi123  
       2020-06-02 18:08:36 +08:00
    1. 用 copy 应该比 select 性能更好。
    2. 可以考虑传输或者储存的时候压缩一下
    3. 根据我的经验,in (...) 在大于 200 个左右的时候可能会出现 recheck index 的现象,最好能 explain 确定一下。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5507 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 07:10 · PVG 15:10 · LAX 23:10 · JFK 02:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.