具有相关子查询的 MySQL 查询 - 我怎样才能加快它的速度？

Question

我有一个查询要加快速度。我确信有一种方法可以提高效率，但我缺乏相关知识。

订单 - 每个收到的订单显示一行(当前为 800,000 行)

orders_financials - 这显示与订单相关的每笔金融交易一行(当前为 2,000,000 行)

orders_financials 表将包含每个订单的多行，交易的类别显示在 financialType 中

因此，例如，对于一个订单，orders_financials 中可能有 5 行

financialType = 14: value = 10.00
financialType = 12: value = 7.00
financialType = 18: value = 2.50
financialType = 15: value = 0.30
financialType = 17: value = 7.50

对于每个订单，都会有不同数量的数据。随着时间的推移，更多的数据被添加到系统中，新的行被添加到 orders_financials。

我想做的是运行一个查询，允许我为每个订单获取一行，并为这一行计算几类金融交易的总和。

下面的查询应该给我 customerID 279 下的所有订单:

select 
orders.orderID, 
customers.username, 
(select group_concat(financialRef) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (17) ) ref,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (17) ) costs_cost,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (12,13) ) costs_exp,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (14) ) costs_sell,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (15) ) costs_sell_out,                                       
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (1,2,3,9,11) ) extras_in,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (4,5) ) extras_out,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (20) ) extras_back,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (18) ) insurance_in,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (10,6) ) insurance_out,
(select sum(amount) from orders_financials where orders_financials.orderID=orders.orderID and financialType IN (19) ) insurance_back                                            
from orders 
left join customers on orders.customerID=customers.customerID 
where customers.customerID='279'
order by orderID

(仅供引用，在 customerID=279 的订单表中有 18,263 条记录)

您会看到 financialType 的 12 或 13 可以相加得到 costs_exp。同样 1,2 3,9,11 都是 extras_in。

最终结果是一个包含大量数据的漂亮查询，但它提供了每个订单的详细概览。在 where 子句中，我可以按客户、按服务、按日期范围等进行搜索。

我的问题是这个查询需要 ages 才能运行。我一定是以一种非常低效的方式来做这件事，因为查询的每个元素都很快。

从慢日志我可以看到它正在迭代 422,000,000 行

Query_time: 528.107584  Lock_time: 0.000652 Rows_sent: 1388  Rows_examined: 422442417

必须有更有效的方法，但我很难看到。

编辑 - 这是上述查询的解释:

+----+--------------------+-------------------+-------+-----------------------+---------------+---------+---------------------------+--------+--------------------------+
| id | select_type        | table             | type  | possible_keys         | key           | key_len | ref                       | rows   | Extra                    |
+----+--------------------+-------------------+-------+-----------------------+---------------+---------+---------------------------+--------+--------------------------+
|  1 | PRIMARY            | customers         | const | PRIMARY               | PRIMARY       | 4       | const                     |      1 |                          |
|  1 | PRIMARY            | orders            | ref   | customerID            | customerID    | 5       | const                     |  24802 | Using where; Using index |
| 12 | DEPENDENT SUBQUERY | orders_financials | ref   | orderID,financialType | financialType | 4       | const                     |      1 | Using where              |
| 11 | DEPENDENT SUBQUERY | orders_financials | range | orderID,financialType | financialType | 4       | NULL                      |      2 | Using where              |
| 10 | DEPENDENT SUBQUERY | orders_financials | ref   | orderID,financialType | financialType | 4       | const                     |  49717 | Using where              |
|  9 | DEPENDENT SUBQUERY | orders_financials | ref   | orderID,financialType | financialType | 4       | const                     |      1 | Using where              |
|  8 | DEPENDENT SUBQUERY | orders_financials | range | orderID,financialType | financialType | 4       | NULL                      |      2 | Using where              |
|  7 | DEPENDENT SUBQUERY | orders_financials | range | orderID,financialType | financialType | 4       | NULL                      |      5 | Using where              |
|  6 | DEPENDENT SUBQUERY | orders_financials | ref   | orderID,financialType | financialType | 4       | const                     |    139 | Using where              |
|  5 | DEPENDENT SUBQUERY | orders_financials | ref   | orderID,financialType | orderID       | 4       | p4db_admin.orders.orderID | 236338 | Using where              |
|  4 | DEPENDENT SUBQUERY | orders_financials | ref   | orderID,financialType | orderID       | 4       | p4db_admin.orders.orderID | 236338 | Using where              |
|  3 | DEPENDENT SUBQUERY | orders_financials | ref   | orderID,financialType | financialType | 4       | const                     |  99966 | Using where              |
|  2 | DEPENDENT SUBQUERY | orders_financials | ref   | orderID,financialType | financialType | 4       | const                     |  99966 | Using where              |
+----+--------------------+-------------------+-------+-----------------------+---------------+---------+---------------------------+--------+--------------------------+
13 rows in set (0.00 sec)

Answer 1

SELECT  a.orderID, 
        b.username,
        GROUP_CONCAT(CASE WHEN c.financialType = 17 THEN c.financialRef END) ref,
        SUM(CASE WHEN c.financialType IN (12,13) THEN c.amount END) costs_cost,
        SUM(CASE WHEN c.financialType = 14 THEN c.amount END) costs_exp,
        SUM(CASE WHEN c.financialType = 15 THEN c.amount END) costs_sell,
        SUM(CASE WHEN c.financialType IN (1,2,3,9,11) THEN c.amount END) extras_in,
        SUM(CASE WHEN c.financialType IN (4,5) THEN c.amount END) extras_out,
        SUM(CASE WHEN c.financialType = 20 THEN c.amount END) extras_back,
        SUM(CASE WHEN c.financialType = 18 THEN c.amount END) insurance_in,
        SUM(CASE WHEN c.financialType IN (10,6) THEN c.amount END) insurance_out,
        SUM(CASE WHEN c.financialType = 19 THEN c.amount END) insurance_back
FROM    orders a
        LEFT JOIN customers b
            ON a.customerID = b.customerID 
        LEFT JOIN orders_financials c
            ON a.orderID = c.orderID
GROUP   BY a.orderID, b.username

在 orders_financials.financialType 上添加索引

ALTER TABLE orders_financials ADD INDEX (financialType)