单机 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 列数据库,但抓取数据的成本太高,且也需要开发,所以先请教一下大佬们,看有没有更好的方案.

优化结果反馈: https://v2ex.com/t/837742

[outdoorlife]

本质上就是拆表

你把这些数据想象成放在文本数据里，用 Grep 去查需要多久。数据库比较重查询效率低，文本直接搜索效率最高，但是他的效率也在于磁盘 IO 和 CPU 自身上。

所以你的问题优化本质就在于拆表。

找到可以拆的维度比如时间，然后缩小范围，然后再去查询，速度会好很多，毕竟不用从 1-20 亿这样排下去，可能是 1-1000 万，但是有 200 个。

我们称为百库百表。

另外单机程序上限受 IO 影响

多机拆了才更好（ HADOOP 原理）

可能你去买 I5 CPU SSD ，16G 内存的台式机来 20 台，每台放 1 亿数据， 这效率你就可以接受了。

[none]

一般建议单表数据记录不超过 500W 条，应该考虑分库分表。

[outdoorlife]

我们以前线上用户有 1000 万。

用户中心含积分，历史记录，上亿数据。

百库百表上了 15 台数据库 DELL 高配，

结果 MYCAT 成为了我们的瓶颈。中间件扛不住高并发。

C10K 经常出现。后来在 DNS 层面弄了好几个登陆中心，每个再连接一个 MYCAT

[meeop]

根据[ transaction 的 hash , from addr, to addr ]这三个字段简单加 3 个索引不行吗? 看起来就是 o1 级别查询,亿级别应该是百毫秒左右出结果吧

[cxh116]

@outdoorlife 目前是三机械硬盘 raid 0,速度太勉强了.

主要是 tx 表不像 block 表, 使用自增 number 做主键,而是使用 hex hash 做主键,单机分库分表 IO 瓶颈感觉意义好像不大,多机的话三件成本高,没赚钱先亏个几百块一个月还是撑不住.

[ch2]

只能上 clickhouse 了

[dayeye2006199]

试试这几个：

1. 你的查询基本上都是 = 条件，所以考虑用 hash index ，不用 btree index ，大概有 10-20%的性能提升；
2. 使用 CLUSTER- 基本就是重排数据记录，让相近的数据在同一个 cluster 里面；这样应对 比如查询所有 from_addr = xxxx 的记录的时候有奇效，因为这些记录都是邻居；
3. 使用 PARTITION - 思路和 sharding 接近，但是不需要引入多机；方便 query plan 并行化执行

[outdoorlife]

@meeop

亿级数据查询百毫秒出结果。。。。

我有点被吓到了，请问你用的是 Oracle 的大型机柜服务器么？天玑那种？一套近 50 万人民币的那种。

[dexterzzz]

sql server 列存储索引表

[outdoorlife]

@cxh116

Mycat 数据库中间件就是干这个活的。

当然如果想的话，最新的 SQL SERVER 集群也支持的。

其实就是你的数据写进数据库前先进行一次取模去处，然后再落盘，到时候查的时候他是派任务下去查，查到有的就吐回来，没有的就不吐回来。

所以可以充分把硬件用起来。

至于那位说 “亿级别数据库查询只要几毫秒的”

我知道的话，可能要用到 Oracle 近 50 万的天玑数据库。

你还是别考虑了。

[shyrock]

不是很能理解 hash 这种几乎不重复的主键，加了索引后为什么不是秒查到 value 。
你把你的查询语句用 explain 跑一下呢，看一下耗时在哪里？

[dreamramon]

上 clickhouse 秒出

[GM]

TDEngine 试试，我看他们吹牛逼吹得很厉害，几亿数据毫秒级时间查到

[tanhui2333]

@none mysql 都这么弱了吗

[superchijinpeng]

ck

[cxh116]

@outdoorlife

Table "public.transactions"
Column | Type | Collation | Nullable | Default
-----------------------------+--------------------------+-----------+----------+---------
hash | character varying(66) | | |
nonce | bigint | | |
transaction_index | bigint | | |
from_address | character varying(42) | | |
to_address | character varying(42) | | |
value | numeric(38,0) | | |
gas | bigint | | |
gas_price | bigint | | |
input | text | | |
receipt_cumulative_gas_used | bigint | | |
receipt_gas_used | bigint | | |
receipt_contract_address | character varying(42) | | |
receipt_root | character varying(66) | | |
receipt_status | bigint | | |
block_timestamp | timestamp with time zone | | not null |
block_number | bigint | | |
block_hash | character varying(66) | | |
max_fee_per_gas | bigint | | |
max_priority_fee_per_gas | bigint | | |
transaction_type | bigint | | |
receipt_effective_gas_price | bigint | | |
Indexes:
"transactions_block_number" btree (block_number DESC)
"transactions_block_timestamp_idx" btree (block_timestamp DESC)
"transactions_from_address_block_timestamp_index" btree (from_address, block_timestamp DESC)
"transactions_hash" UNIQUE, btree (block_timestamp DESC, hash)
"transactions_to_address_block_timestamp_index" btree (to_address, block_timestamp DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON transactions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 70 (Use \d+ to list them.)






EXPLAIN select * from transactions where hash='0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8' limit 1;


-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Limit (cost=0.28..853977.96 rows=1 width=886)
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Append (cost=0.28..59778438.09 rows=70 width=886)
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_149_chunk_transactions_hash on _hyper_2_149_chunk (cost=0.28..50.48 rows=1 width=659)
-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 5 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_151_chunk_transactions_hash on _hyper_2_151_chunk (cost=0.42..2496.59 rows=1 width=718)
-[ RECORD 6 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 7 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_153_chunk_transactions_hash on _hyper_2_153_chunk (cost=0.42..28396.85 rows=1 width=774)
-[ RECORD 8 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 9 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_155_chunk_transactions_hash on _hyper_2_155_chunk (cost=0.42..20489.98 rows=1 width=752)
-[ RECORD 10 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 11 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_157_chunk_transactions_hash on _hyper_2_157_chunk (cost=0.42..30703.97 rows=1 width=792)
-[ RECORD 12 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 13 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_159_chunk_transactions_hash on _hyper_2_159_chunk (cost=0.43..34880.92 rows=1 width=800)
-[ RECORD 14 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 15 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_161_chunk_transactions_hash on _hyper_2_161_chunk (cost=0.43..42360.63 rows=1 width=792)
-[ RECORD 16 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 17 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_163_chunk_transactions_hash on _hyper_2_163_chunk (cost=0.43..49846.27 rows=1 width=775)
-[ RECORD 18 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 19 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_165_chunk_transactions_hash on _hyper_2_165_chunk (cost=0.43..41498.32 rows=1 width=815)
-[ RECORD 20 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 21 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_167_chunk_transactions_hash on _hyper_2_167_chunk (cost=0.43..33756.32 rows=1 width=813)
-[ RECORD 22 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)

[buddyy]

我是不是瞎了，没看到有 hash 字段开头的索引呢？
如果没有的话，这肯定很慢啊。

[buddyy]

还有 hash 这个字段长度太长了，可以考虑限制 hash 字段的索引长度，可以让一个 page 上容纳更多的记录。

[buddyy]

如果把 hash 作为主键，这也是不合理的，写入过程中会有大量的叶分裂。

[cxh116]

@dayeye2006199 @buddyy

感谢感谢,针对网友们的建议,做出以下改变尝试.

1. 改成 ClickHouse ,主键默认使用的是稀疏索引.
2. 使用 Table 自带的 Partition ,取 hash 前 8 位做分区.

[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 都能轻松处理，没必要过早优化

[encro]

@outdoorlife

阿里云 RDS 上放了一个 1 核 2G 的 Mysql ，没在怎么优化，1.9 亿数据，每天几百个人用及万次查询，一个页面几个查询，打开速度几十到几百毫秒吧。

字段:
用户书籍编号（ bigint ）,章节编号(bigint),段落编号（ bigint ）,段落内容(text)，状态（是否删除 tinyint ）,创建时间(datetime)，修改时间(datetime)，用户编号(bigint)，段落序号（ smalint ）。

平时查询都是查某一章未删除段落按序号从小到大排列，而且都是 select *。。。

主要影响时间的是某个章节段落超过 1000 以上以上的会返回比较慢，或者段落排序修改时会比较慢。

平时慢查询日志，超过 200ms 都会记录，一般只有上面两种情况会出现需要几百毫秒的满查询，其他都不会。

[dw2693734d]

楼主解决了没，我用的 Nvme 硬盘，5000M/s 读写，按需求存 transaction ， 不是每笔 transaction 都存

[cxh116]

@dw2693734d 还是用 pg 的，根据 v 友的建议，优化的结果放在 append 新帖子链接里。走索引的话，感觉速度也还行。