PG 库，查询优化。

[allenwuli]

大佬们，我现在有一张表。表结构如下，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 秒左右！ 我在大数据量方面没有经验，请问各位大佬们有没有什么好的优化方法，提高我取数的效率。 谢过各位大佬了！

[limboMu]

按你的想法想要多久能取出数据啊？

[allenwuli]

@limboMu 越快越好啊，秒出结果最好了！

[limboMu]

@allenwuli 尽量把信息描述完全一些吧，比如执行环境是单机还是网络，查询计划是什么样子的。

[reus]

这还嫌慢？你取 100 条不就一秒以下了吗？
换 hash 索引可能会快一丁点

[allenwuli]

@limboMu 需要将整张表都读一次，所以我一次取一千。一轮下来还是很久的！有专门的数据库服务器。

[allenwuli]

@reus 要将表轮一遍，太耗时了。

[reus]

@allenwuli 多线程分片取

[limboMu]

@allenwuli 看一下数据库服务器的带宽吧，感觉瓶颈应该是在哪里

[allenwuli]

@limboMu 好的，谢了大佬。

[allenwuli]

@reus 谢了大佬，我试试

[mahone3297]

你说了 logo_array 很大，所以，就是要很多磁盘 io 去取，应该无法优化吧
where 条件你都已经加了索引了

[allenwuli]

@mahone3297 是啊，感觉很烦。

[pmispig]

你这个慢，要么是带宽问题，要么是 IO 问题，好像没啥优化的余地吧 0.0
你看看你取 1000 条的数据量是多大

[shenjixiang]

图片存数据库这做法不太合理啊，logo_array 建索引太浪费磁盘。如果小于 4kb 的话可以尝试一下 tm_id 和 logo_array 建立联合索引。
如果你的 tm_id 查询范围过大，可能还走不了索引

[allenwuli]

@shenjixiang 是很不合理，原始图片数据还是 base64 编码后存数据库的。我们现在做图片算法模型比对的，就将那个图片原始表同步过来，经过筛洗，不需要的 tm_id 被剔除。base64 的字段，自己解码再 numpy 转换的。用原始的更费事，更慢。现在的表少了不少数据，还不要 base64 转换 numpy 。其他没想到好的办法，这方面经验不足。

[JDog]

看表结构似乎从 SQL 方面优化已经没啥效果了，因为 logo_array 这个字段数据量太大...响应时间慢应该是卡在 IO 这块。
1.PG 缓存优化(需要加内存)
2.更换更好的 SSD(也需要花钱)
3.主从复制,读写分离(加机器 == 花钱)

[shenjixiang]

@allenwuli #7 说的多线程分片取是个好办法，可以试试，图片这种大数据本身就没有特别大的优化方式，只能在每个代码环节看看执行效率

[MoYi123]

1. 用 copy 应该比 select 性能更好。
2. 可以考虑传输或者储存的时候压缩一下
3. 根据我的经验，in (...) 在大于 200 个左右的时候可能会出现 recheck index 的现象，最好能 explain 确定一下。