我在以下查询中遇到性能问题:
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
模式,请告诉我。
我在这里错过了什么?
依赖子查询在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'
你看到我们是如何将依赖摘要子查询更改为它自己的虚拟表的吗?这使得查询规划器只运行该查询一次,而不是针对主查询中的每一行运行一次。这帮助很大。
对于缺少任何相应customer_payment_options
行的transaction
行,IFNULL()
为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/这是一个良好的开端。
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
。