mysql - 连接表性能缓慢

Question

我正在尝试如下优化查询，我研究了如何添加索引以提高性能，但结果仍然很慢。下面的查询运行了 20 秒，Transaction 包含大约 100k 条记录，连接表 TransactionDetail 包含大约 500k 条记录。

    SELECT Transaction.id ....
    FROM Transaction
    INNER JOIN Agent ON Agent.id = Transaction.agent_id
    INNER JOIN Distributor ON Distributor.id = Transaction.distributor_id
    INNER JOIN TransactionDetail ON Transaction.id = TransactionDetail.transaction_id
    WHERE TransactionDetail.type =  'Admin'
    AND Transaction.status IN ('pending',  'processing',  'success',  'rejected')
    ORDER BY issued_date DESC LIMIT 0 , 10

从上面的查询中，我尝试应用我对索引的理解

1. 因为内连接，我添加了 3 个索引，交易(agent_id,distributor_id) 和交易(transaction_id)

2. 从 where 子句我添加了 Transaction(status)

3. 因为 ORDER BY 我添加了 Transaction(issued_date)

但它没有显示任何改进，下面是我从 EXPLAIN 中得到的

这是来自 phpmyadmin 的屏幕截图，显示了表事务的索引

有什么方法可以改进这个查询吗？或者它已经优化，我应该关注 mysql 配置？

Answer 1

您的问题

SELECT Transaction.id ....
FROM Transaction
INNER JOIN Agent ON Agent.id = Transaction.agent_id
INNER JOIN Distributor ON Distributor.id = Transaction.distributor_id
INNER JOIN TransactionDetail ON Transaction.id = TransactionDetail.transaction_id
WHERE TransactionDetail.type =  'Admin'
AND Transaction.status IN ('pending',  'processing',  'success',  'rejected')
ORDER BY issued_date DESC LIMIT 0 , 10

现在您已经在表上应用了索引，这很好，但是 in 子句 更像是 or这会产生一个真正的性能问题。在小数据集的情况下，这无法观察到，但在大数据集中性能将显着下降。

优化它的一种方法是将 in 子句 转换为 union，这比 或 in 效果更好>

(
 SELECT Transaction.id ....
 FROM Transaction
 INNER JOIN Agent ON Agent.id = Transaction.agent_id
 INNER JOIN Distributor ON Distributor.id = Transaction.distributor_id
 INNER JOIN TransactionDetail ON Transaction.id = TransactionDetail.transaction_id
 WHERE TransactionDetail.type =  'Admin'
 AND Transaction.status = 'pending'
)
union
(
 SELECT Transaction.id ....
 FROM Transaction
 INNER JOIN Agent ON Agent.id = Transaction.agent_id
 INNER JOIN Distributor ON Distributor.id = Transaction.distributor_id
 INNER JOIN TransactionDetail ON Transaction.id = TransactionDetail.transaction_id
 WHERE TransactionDetail.type =  'Admin'
 AND Transaction.status = 'processing'
)
union
(
 SELECT Transaction.id ....
 FROM Transaction
 INNER JOIN Agent ON Agent.id = Transaction.agent_id
 INNER JOIN Distributor ON Distributor.id = Transaction.distributor_id
 INNER JOIN TransactionDetail ON Transaction.id = TransactionDetail.transaction_id
 WHERE TransactionDetail.type =  'Admin'
 AND Transaction.status = 'success'
)
union
(
 SELECT Transaction.id ....
 FROM Transaction
 INNER JOIN Agent ON Agent.id = Transaction.agent_id
 INNER JOIN Distributor ON Distributor.id = Transaction.distributor_id
 INNER JOIN TransactionDetail ON Transaction.id = TransactionDetail.transaction_id
 WHERE TransactionDetail.type =  'Admin'
 AND Transaction.status = 'rejected'
)
order by issued_date DESC LIMIT 0 , 10

为了解决订单问题，您可能需要将索引添加为

alter table Transaction add index status_created_idx(status,issued_date);