优化 EOS 交易记录查询

Robin

文/robin

这是「区块链技术指北」的第 43 篇文章。

如果对我感兴趣,想和我交流,我的微信号:Wentasy,加我时简单介绍下自己,并注明来自「区块链技术指北」,同时我会把你拉入微信群。

题图来自: © ChainONE / Original / chainon.io

0x00 背景


钱包获取交易记录,这是一个非常重要的功能。EOS 链上数据转化为关系型数据,然后再从关系型数据获取交易记录,这是比较普遍的做法。

问题在于,EOSIO 并没有提供此插件。所以,本文给出实现思路以及解决方案。

0x01 解决方案


EOSIO MySQL 插件可以参考 这里

笔者在 actions 添加相关字段,然后通过插件把数据解析到对应字段,最后再用 SQL 语句查询即可。

其中 actions 表结构如下:

CREATE TABLE `actions` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`account` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '合约拥有者账号',
`transaction_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '交易号',
`seq` smallint(6) NOT NULL DEFAULT '0' COMMENT '序列号',
`parent` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'action 名称',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`data` json DEFAULT NULL COMMENT 'action 数据',
`authorization` mediumtext DEFAULT NULL COMMENT '执行权限',
`eosto` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '提取 data 的 to 字段',
`eosfrom` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '提取 data 的 from 字段',
`receiver` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '提取 data 的 receiver 字段',
`payer` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '提取 data 的 payer 字段',
`newaccount` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '新建账号名称',
`sellram_account` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '卖内存的用户名',
PRIMARY KEY (`id`),
KEY `idx_actions_account` (`account`),
KEY `idx_actions_name` (`name`),
KEY `idx_actions_tx_id` (`transaction_id`),
KEY `idx_actions_created` (`created_at`),
KEY `idx_actions_eosto` (`eosto`),
KEY `idx_actions_eosfrom` (`eosfrom`),
KEY `idx_actions_receiver` (`receiver`),
KEY `idx_actions_payer` (`payer`),
KEY `idx_actions_newaccount` (`newaccount`),
KEY `idx_actions_sellram_account` (`sellram_account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

0x02 优化


获取交易记录的语句如下:

SELECT created_at, data, name, transaction_id
FROM actions a
LEFT JOIN actions_accounts b ON a.id = b.action_id
WHERE (b.actor = ?
OR a.newaccount = ?
OR a.eosto = ?
OR a.eosfrom = ?
OR a.receiver = ?
OR a.payer = ?)
ORDER BY a.created_at DESC
LIMIT ?, ?

这是初始版本,因为有数个 OR 条件,导致效率极低,查询一个账号的交易记录要数十秒,完全不可用。于是第二版优化如下:

SELECT created_at, data, name, transaction_id
FROM actions a
LEFT JOIN actions_accounts b ON a.id = b.action_id
WHERE b.actor = ?
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
LEFT JOIN actions_accounts b ON a.id = b.action_id
WHERE a.newaccount = ?
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
LEFT JOIN actions_accounts b ON a.id = b.action_id
WHERE a.eosto = ?
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
LEFT JOIN actions_accounts b ON a.id = b.action_id
WHERE a.eosfrom = ?
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
LEFT JOIN actions_accounts b ON a.id = b.action_id
WHERE a.receiver = ?
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
LEFT JOIN actions_accounts b ON a.id = b.action_id
WHERE a.payer = ?
ORDER BY created_at DESC
LIMIT ?, ?;

第二版仍然使用表关联,不过把 WHERE 语句中的 OR 条件改成了 UNION ALL。因为表里有大量的关联,效率还是低,查询一个账号的交易记录要 10 秒左右。

于是我们对 SQL 插件解析数据也进行改写,有了第三版的优化:

SELECT created_at, data, name, transaction_id
FROM actions a
WHERE a.newaccount = ?
AND a.name = 'newaccount'
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
WHERE (a.eosto = ?
OR a.eosfrom = ?)
AND a.name = 'transfer'
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
WHERE (a.eosfrom = ?
OR a.receiver = ?)
AND a.name = 'delegatebw'
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
WHERE (a.eosfrom = ?
OR a.receiver = ?)
AND a.name = 'undelegatebw'
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
WHERE a.sellram_account = ?
AND a.name = 'sellram'
UNION ALL
SELECT created_at, data, name, transaction_id
FROM actions a
WHERE (a.payer = ?
OR a.receiver = ?)
AND (a.name = 'buyrambytes'
OR a.name = 'buyram')
ORDER BY created_at DESC
LIMIT ?, ?

我们可以看到只需要查询 actions 表,name 字段表示动作(目前查询:newaccount、transfer、delegatebw、undelegatebw、sellram 和 buyram)。另外,WHERE 条件全部有索引。查询一个账号的交易记录,1s 以内就可以返回,业务可用性极速提高。

业务运行一段时间后,访问速度又出现了瓶颈,我们做了下一次的优化。从业务入手,既然查询交易记录只查询单表,如果不同的操作只要涉及到查询账号,返回数据不就好了?于是优化结果如下:

SELECT id, created_at, data, name, transaction_id
FROM actions a
WHERE (a.eosto = ?
OR a.eosfrom = ?
OR a.receiver = ?
OR a.payer = ?
OR a.newaccount = ?
OR a.sellram_account = ?)
ORDER BY created_at DESC
LIMIT ?, ?

0x03 小结


SQL 语句优化是一门不断试错,不断否定自己的学问。读者需要对业务逻辑有深刻的认识,才有可能优化出对业务友好的 SQL 语句。

–EOF–

版权声明:自由转载-非商用-非衍生-保持署名(创意共享4.0许可证)

「区块链技术指北」同名 知识星球,二维码如下,欢迎加入。

「区块链技术指北」相关资讯渠道:

同时,本系列文章会在以下渠道同步更新,欢迎关注:

原创不易,读者可以通过如下途径打赏,虚拟货币、美元、法币均支持。

  • BTC: 3QboL2k5HfKjKDrEYtQAKubWCjx9CX7i8f
  • ERC20 Token: 0x8907B2ed72A1E2D283c04613536Fac4270C9F0b3
  • PayPal: https://www.paypal.me/robinwen
  • 微信打赏二维码

–EOF–

版权声明:自由转载-非商用-非衍生-保持署名(创意共享4.0许可证)

Robin

Written by

Robin

Cryptocurrency Specialist, Blockchain Evangelist, Founder of ChainONE, Blogger of dbarobin.com.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade