文/温国兵

这是「区块链技术指北」的第 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
  • 微信打赏二维码

Wechat

–EOF–

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