优化 EOS 交易记录查询

文/温国兵

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

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

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

0x00 背景

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

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

(html comment removed: more)

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–

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

区块链技术指北

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

「区块链技术指北」同名知识星球，https://t.xiaomiquan.com/ZRbmaU3
官网，https://chainon.io
官方博客，https://blog.chainon.io
官方社区，https://bbs.chainon.io
Telegram Channel，https://t.me/BlockchainAge
Telegram Group，https://t.me/bcage
Twitter，https://twitter.com/bcageone
Facebook，https://www.facebook.com/chainone.org
新浪微博，https://weibo.com/BlockchainAge

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

「区块链技术指北」同名微信公众号（微信号：BlockchainAge）
个人博客，https://dbarobin.com
知乎，https://zhuanlan.zhihu.com/robinwen
简书，https://www.jianshu.com/c/a37698a12ba9
Steemit，https://steemit.com/@robinwen
Medium，https://medium.com/@robinwan
掘金，[email protected]
EOS LIVE，https://eos.live/user/robin
币乎，https://bihu.com/people/22207

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

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

Wechat

–EOF–

Sort:

Trending

[-]

partiko (66) 5 years ago

Thank you so much for sharing this amazing post with us!

Have you heard about Partiko? It’s a really convenient mobile app for Steem! With Partiko, you can easily see what’s going on in the Steem community, make posts and comments (no beneficiary cut forever!), and always stayed connected with your followers via push notification!

Partiko also rewards you with Partiko Points (3000 Partiko Point bonus when you first use it!), and Partiko Points can be converted into Steem tokens. You can earn Partiko Points easily by making posts and comments using Partiko.

We also noticed that your Steem Power is low. We will be very happy to delegate 15 Steem Power to you once you have made a post using Partiko! With more Steem Power, you can make more posts and comments, and earn more rewards!

If that all sounds interesting, you can:

Download Partiko Android at Google Play
Or Download Partiko iOS on the App Store

Thank you so much for reading this message!

$0.00