0x00 背景

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

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

image

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 ?, ?