如何优化带子查询的SQL性能 避免外层查询全表扫描

现有两张业务数据表，结构如下：

• transactions（交易表）

• tags（标签表）

当前使用行转列+分组聚合的方式统计按日期、账户、SKU维度的交易总金额，SQL语句如下：

SELECT
    transaction_date,
    SUM(amount),
    account,
    sku
FROM 
    (
        SELECT
            id,
            transaction_date,
            amount,
            MAX(CASE WHEN tags.name = 'account' THEN tags.value END) AS account,
            MAX(CASE WHEN tags.name = 'sku' THEN tags.value END) AS sku
        FROM 
            transactions 
        LEFT JOIN
            tags on transactions.id = tags.transaction_id
            AND tags.name IN ('account', 'sku') 
        GROUP BY 
            id
    )
GROUP BY
    transaction_date,
    account,
    sku;

当前执行EXPLAIN QUERY PLAN输出的执行计划如下：

|--CO-ROUTINE SUBQUERY 1
|  |--SCAN transactions USING INDEX idx_tid
|  `--SEARCH tags USING INDEX idx_txn_id (transaction_id=?)
|--SCAN SUBQUERY 1
`--USE TEMP B-TREE FOR GROUP BY

执行计划显示外层查询会对子查询结果做全表扫描，且分组操作需要使用临时B树，当库内存在数千条交易记录时查询速度很慢，需要优化方案避免外层查询对子查询结果的全表扫描，提升查询效率。

简化性能对比 #

该性能问题可以简化为两个查询的效率差异：

1. 执行速度快的单表聚合查询

SELECT SUM(amount) FROM transactions

对应执行计划：

`--SCAN transactions

2. 带子查询的慢查询

SELECT SUM(amount) FROM transactions WHERE id IN (SELECT transaction_id FROM tags)

对应执行计划：

|--SEARCH transactions USING INDEX idx_tid (id=?)
`--LIST SUBQUERY 1
   `--SCAN tags USING COVERING INDEX idx_txn_id

核心待解决问题：如何优化外层查询针对子查询结果集的查询性能？

内容的提问来源于stack exchange，提问作者poundifdef