单机 8 核 32G, 用什么技术才可以做单表 20 亿数据的过滤查询?

[cxh116]

需求是抓取 eth 的 block 与 transaction 来分析, transaction 数据 10 亿以上了.
数据存在 pg 数据,大小在 2TB 以上.

目前是使用 postgresql + timescale, transaction 表的 hash 字段加了索引,1 小时返回不了根据 hash 单字段的过滤查询结果.

3 秒左右一个块,一个 block 里有 300 左右个 tx ,也就是每秒写入差不多在 100 条记录.

需要根据 transaction 的 hash , from addr, to addr 查询,简单的 key value.

期望简单查询能在 1 分钟左右出结果.

目前准备尝试的方案是使用 clickhouse 列数据库,但抓取数据的成本太高,且也需要开发,所以先请教一下大佬们,看有没有更好的方案.

[meeop]

@outdoorlife 我刚才还试了下,2 亿行数据,普通 long 字段加索隐,查询大致 25ms 左右,mysql
不过机器配置确实比较好

[0ZXYDDu796nVCFxq]

单表 500 万这个的来源是什么
数千万的记录并没什么问题啊

[Immortal]

楼主根据上面说的优化后能在这里补充下优化后的过程和结果么 想学习

[buddyy]

@meeop 没毛病，我感觉就是索引问题。以 hash 为主键索引的情况下，32 Byte 加上指针占用的话，一个叶可以容纳 400 左右的，BTree 树高度也就 5 层左右吧，也就 5 次叶访问+一次二分查找，应该不会这么长时间也查不出来。

[winglight2016]

这么大表，不是分库分表，就是同步到 nosql 库，没有其他方案了，也许 newsql 可以，不过我不太了解。

查询慢，是因为主键采用了 varchar 而不是 int ，使用有序整型做主键是大表的基本设计原则。

[zhoujinjing09]

你用机械盘不是搞自己嘛

[cxh116]

@zhoujinjing09 Kimsufi 的洋垃圾独服。

[kiwi95]

机器配置好的话，搞过单表 6 亿的索引查询还是很快的，甚至再 join 一张亿级别的表也还过得去

[0ZXYDDu796nVCFxq]

单表 500 万这个是不是十几年前的文章写的
再涨两个量级到亿其实也没问题

[outdoorlife]

@gstqc 一般单单五百万问题，你首先要知道他的瓶颈在哪里。

你知道么，就算有索引，只要超过一定阀值，就会进入全局扫描。上亿的表要不进入全局扫描，至少在 2018 年的时候我们还没解决

不然要 MYCAT 分库分表我们干百库百表干吗

[outdoorlife]

@kiwi95

MYSQL 在进行索引太多行的时候会自动转入全局扫描的

你这么豪狠的数据是哪里来的。

我们为了解决这用户中心的问题，还不是几亿，就大几千万。用了百库百表，十来台服务器，再魔改 MYCAT 。最后还想了其它办法。

如果可以也请你放一下你的优化方式，我们学习一下，也让整个互联网学习一下。

顺便说回来，我们后来是使用阿里云的 MYSQL 集群才去解决的。那费用之猛，光是集群中间件就贵的要死。

[outdoorlife]

@meeop

mysql 单表 2 亿，查询几十毫秒。

你这表有几列，你的索引用了多少内存，据我浅薄的知识，除非你的数据表特别特别特别特别简单，简单到可能只有二列，而且是自增长纯数字，还需要配合非常大的内存，非常快的 SSD 。并且这个数据库上还要没有其它业务在。你要几十毫秒都很难。

打个比方吧，就像 redis ，你要有上亿数据在里边，查一下，这性能和寻址会好到哪里去。不然大家上 redis cluster 干吗。redis 官方也不推荐太大内存

你先说说你说的机器非常好是有多好。

我看看和我们一台 7 万用了这么多台服务器还要魔改 mycat 的服务器才解决的业务问题上有多少的进步，可以让 mysql 有这么大效能的提升。

我也学习一下。

[kiwi95]

@outdoorlife 保证 where 条件能走到较好的索引，5 亿记录的表查询没问题的，配置大概是 128G 内存多少核忘了

[kiwi95]

曾经我也以为千万级别是瓶颈，直到看到了这个亿级量也能跑

[outdoorlife]

@kiwi95

5 亿单表，你先看看最简单的 Redis 吃的下么。

我们用的是 至强 3.0 的 CPU ，核少的，配合 512G 内存，SSD 加万兆硬盘，RAID10.

你 128G 内存可以走 5 亿索引，还 ms 级查询。是不是真的只有二列呀。

二列也不能呀，内存寻址都寻不过来。

我见过真的亿级数据库单表查询的

天玑机柜一体机，Oralce 数据库，优化到极致。 纯硬件 50 万人民币，不含实施费和备份。单机柜。他们上三个。

[hooopo]

你点查为啥要用列存数据库

[hooopo]

别用 btree 用 hash index 或者 brin

[lifanxi]

可以上云的话，试试 Hologres 。

[kiwi95]

@outdoorlife 肯定不止两列啊，不过我们底层不是 InnoDB 是 rocksdb 做存储引擎。也有用 InnoDB 的，量级小一点，千万级还是可以的

[kaichen]

1. 从需求出发会很好做优化
- 如果你只是分析数据不去做验证可以把里面签名和共识层相关参数拿掉，数据量能压缩很多
- 如果你只是要分析特定 topic ，那么很多垃圾交易可以直接丢掉，transaction 里的 input 只切前面的 4bytes 存剩下扔掉，eventlog 只保存 topic0 ，剩下 topic 和 data 扔掉

---
2. 从现有基础设施出发，
- 可以把 hash/address 改成 bytes ，amount 类都用 bigint 存 存储和搜索效率都能提升
- 直接用 google bigquery 里已经弄好的 eth etl 数据，问题是数据 T+1 ，并且费用按照查询源数据扫描量计算

---
上述除 gcp 产品外用单个 mysql 都能轻松处理，没必要过早优化