mysql - 加速 MySQL 查询/290 万行/多个连接

Question

由于表包含 290 万行以及与其他表的联接，我的查询运行速度非常慢。我已经成功地将执行时间减少了一半，但仍然不够快。

有人知道我还能做什么吗？ amount、entityId、merchant.name、channel.uuid 和channel.sender 列均已编入索引。

这是原始查询，需要 11 秒...

SELECT 
    SUM(t.amount) AS amount, 
    m.name 
FROM 
    transactionsV2 t
    JOIN channels c ON t.entityId = c.uuid
    JOIN merchants m ON c.sender = m.uuid
WHERE 
    t.paymentType = "DB"
    AND t.status = 1
    AND t.processing_time >= "2019-01-19 00:00:00"
    AND t.processing_time <= "2019-01-20 23:59:59"
GROUP BY m.uuid
ORDER BY m.name

这是重新设计的查询，需要 4.5 秒...

SELECT 
    SUM(t1.amount) AS amount, 
    m.name 
FROM
    (
        SELECT t.amount, t.entityId 
        FROM transactionsV2 t
        WHERE 
            t.paymentType = "DB"
            AND t.status = 1
            AND t.processing_time >= "2019-01-19 00:00:00"
            AND t.processing_time <= "2019-01-20 23:59:59"
    ) t1
    JOIN channels c ON t1.entityId = c.uuid
    JOIN merchants m ON c.sender = m.uuid
GROUP BY m.name

下面是所有 3 个表的表结构。

Answer 1

查看您的代码，确保您有复合索引

transactionsV2 (paymentType, status, processing_time, entityId, amount)

channels (sender, uuid)

merchants ( uuid, name)