php - mysql查询性能提升

Question

我在以下查询中遇到性能问题:

SELECT t.local_branch_revenue, t.total_payment,

    (SELECT SUM(IF(cpo.real_account_type = 'HQ', 0, cpo.payment_amount)) AS cpo_payment_amount
        FROM customer_payment_options cpo
        WHERE tran_id=t.id
        AND cpo.payment_type != 'WALLET' AND cpo.payment_type != 'REWARD_CREDIT'
        GROUP BY cpo.tran_id)
    as cpo_payment_amount,

    b.ben_firstname, b.ben_lastname
    FROM transaction t
    LEFT JOIN beneficiary b
    ON b.id=t.ben_id
    WHERE t.local_branch_id='31'
    AND DATE(t.date_added) < '2016-04-07'
    AND source_country_id='40'
    AND t.transaction_status != 'CANCELLED'

解释

 +----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+
| id | select_type        | table | type   | possible_keys                          | key                                    | key_len | ref             | rows | Extra       |
+----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+
| 1  | PRIMARY            | t     | ref    | local_branch_id,source_country_id      | local_branch_id                        | 5       | const           | 2    | Using where |
+----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+
| 1  | PRIMARY            | b     | eq_ref | PRIMARY                                | PRIMARY                                | 8       | mtesdb.t.ben_id | 1    |             |
+----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+
| 2  | DEPENDENT SUBQUERY | cpo   | ref    | tran_id_payment_type_real_account_type | tran_id_payment_type_real_account_type | 9       | mtesdb.t.id     | 1    | Using where |
+----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+

如您所见，它正在使用来自可能键的索引。但查询仍然需要大约 13 秒。

我还有 transaction 表的索引:(ben_id, company_id, source_country_id, date_added, tran_owner)。但是，它甚至没有出现在可能的键部分。

如果您需要 table 架构，请告诉我。

我在这里错过了什么？

Answer 1

依赖子查询在 MySQL 中执行得不是很好......查询规划器不会有效地将它们转换为 JOINed 子查询。 (它们在 Oracle 和 SQL Server 中没问题，但谁有钱买这些？)因此，重构查询以消除依赖子查询对您来说是个不错的选择。

这是您的子查询。让我们将其重构为一个独立的子查询。我们将去掉 WHERE tran_id=t.id 并将其移至 ON 子句。

             SELECT tran_id,
                    SUM(IF(real_account_type = 'HQ',
                           0, 
                           payment_amount)) AS cpo_payment_amount
               FROM customer_payment_options
              WHERE payment_type != 'WALLET'
                AND payment_type != 'REWARD_CREDIT'
             GROUP BY tran_id

请注意，您可以按如下方式简化这一点——您的 IF() 子句排除了带有 real_account_type = 'HQ' 的行。您可以改为在 WHERE 子句中执行此操作。

             SELECT tran_id,
                    SUM(payment_amount) AS cpo_payment_amount
               FROM customer_payment_options
              WHERE payment_type != 'WALLET'
                AND payment_type != 'REWARD_CREDIT'
                AND real_account_type != 'HQ'
             GROUP BY tran_id

(tran_id, payment_type, real_account_type, payment_amount) 上的复合索引可能有助于此子查询运行得更快。但是这三个 != 子句的存在保证了完整的索引扫描；没有办法随机访问那些索引。

这会生成一个虚拟表，其中每个 tran_id 一行包含您需要的总和。

接下来我们需要将其加入到您的主查询中。

SELECT t.local_branch_revenue, 
       t.total_payment,
       IFNULL(cposum.cpo_payment_amount,0) cpo_payment_amount,
       b.ben_firstname, b.ben_lastname
  FROM transaction t
  LEFT JOIN beneficiary b ON b.id=t.ben_id
  LEFT JOIN (
             SELECT tran_id,
                    SUM(payment_amount) AS cpo_payment_amount
               FROM customer_payment_options
              WHERE payment_type != 'WALLET'
                AND payment_type != 'REWARD_CREDIT'
                AND real_account_type != 'HQ'
             GROUP BY tran_id
       ) cposum ON t.id = cposum.tran_id
 WHERE t.local_branch_id='31'
   AND DATE(t.date_added) < '2016-04-07'
   AND source_country_id='40'
   AND t.transaction_status != 'CANCELLED'

您看到我们如何将依赖汇总子查询更改为它自己的虚拟表了吗？这让查询规划器只运行一次该查询，而不是对主查询中的每一行运行一次。这很有帮助。

IFNULL() 为缺少任何相应 customer_payment_options 行的 transaction 行获取 cpo_payment_amount 的数值，而不是 NULL。

(local_branch_id, source_country_id, date_added) 上的transaction 表的复合索引将有助于此查询；查询引擎可以随机访问 local_branch_id 和 source_country_id 值，然后范围扫描 date_added 值。

你是如何学会自己做这件事的？ http://use-the-index-luke.com/是一个好的开始。