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行获得一个数值，而不是NULL。

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

你是如何自学的呢？http://use-the-index-luke.com/是一个很好的开始。

Answer 2

WHERE t.local_branch_id='31'
AND DATE(t.date_added) < '2016-04-07'
AND source_country_id='40'

将日期测试更改为简单的t.date_added < '2016-04-07'！否则，下面的索引建议将无法工作。

source_country_id在哪张桌子上？？如果它在t中，那么您需要INDEX(local_branch_id, source_country_id, date_added)。如果它不在t中，那么INDEX(local_branch_id, date_added)。

如果您需要进一步讨论，请提供SHOW CREATE TABLE。