mysql连接性能问题或条件

fcwjkofz  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(263)

当我运行这个查询时,它总是超时,有人能想出更有效的方法来编写它吗?

SELECT 
orders.OrderKey,
shipout.primary_subcategory
FROM
orders
    INNER JOIN
orderitems ON orderitems.OrderID = orders.OrderID
    INNER JOIN
subjects ON subjects.SubjectID = orderitems.SubjectID
    INNER JOIN
subjectdetails ON subjectdetails.SubjectID = subjects.SubjectID
    INNER JOIN
shipout ON shipout.id_invoice = subjects.SubjectKey
        OR shipout.id_invoice = orders.OrderKey
GROUP BY orders.orderkey

如果我删除或条件,它会在.2秒内完成。即使我将最后一个连接切换到orders表而不是subjects表,它也会在.1秒内完成。

shipout ON shipout.id_invoice = orders.OrderKey
GROUP BY orders.orderkey

但是当我尝试同时加入两者时,或者它超时了。下面是解释:

+----+-------------+----------------+--------+-----------------------------------------+----------------------+---------+--------------------------+--------+----------------------------------------------------+
| id | select_type |     table      |  type  |              possible_keys              |         key          | key_len |           ref            |  rows  |                       Extra                        |
+----+-------------+----------------+--------+-----------------------------------------+----------------------+---------+--------------------------+--------+----------------------------------------------------+
|  1 | SIMPLE      | shipout        | ALL    |                                         |                      |         |                          |  10658 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | subjectdetails | index  | PRIMARY                                 | SubjectDetails_ID    |     768 |                          | 455446 | Using index; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | subjects       | eq_ref | PRIMARY,Subjects_SubjectKey             | PRIMARY              |       4 | subjectdetails.SubjectID |      1 |                                                    |
|  1 | SIMPLE      | orderitems     | ref    | OrderItems_OrderID,OrderItems_SubjectID | OrderItems_SubjectID |       5 | subjectdetails.SubjectID |      1 |                                                    |
|  1 | SIMPLE      | orders         | eq_ref | PRIMARY,Orders_OrderKey                 | PRIMARY              |       4 | orderitems.OrderID       |      1 | Using where                                        |
+----+-------------+----------------+--------+-----------------------------------------+----------------------+---------+--------------------------+--------+----------------------------------------------------+

需要注意的是,我不是数据库管理员,所以我不能索引表。看起来主题细节是,但我不确定是否要发货。

q8l4jmvw

q8l4jmvw1#

您可以尝试重写最后一个连接,如下所示:

shipout ON shipout.id_invoice = COALESCE(subjects.SubjectKey, orders.OrderKey)

这样就消除了or,但是将相同的行连接起来。这仅在subjects.subjectkey/orders.orderkey为null时有效,如果另一个不是null,则在进行连接时有效,但如果不知道确切的数据结构,则很难判断。

6l7fqoea

6l7fqoea2#

当前查询的联合重写。
还可以通过正确的索引获得性能。
查询

SELECT 
orders.OrderKey,
shipout.primary_subcategory
FROM
orders
    INNER JOIN
orderitems ON orderitems.OrderID = orders.OrderID
    INNER JOIN
subjects ON subjects.SubjectID = orderitems.SubjectID
    INNER JOIN
subjectdetails ON subjectdetails.SubjectID = subjects.SubjectID
    INNER JOIN
shipout ON shipout.id_invoice = subjects.SubjectKey
GROUP BY orders.orderkey

UNION ALL

SELECT 
orders.OrderKey,
shipout.primary_subcategory
FROM
orders
    INNER JOIN
orderitems ON orderitems.OrderID = orders.OrderID
    INNER JOIN
subjects ON subjects.SubjectID = orderitems.SubjectID
    INNER JOIN
subjectdetails ON subjectdetails.SubjectID = subjects.SubjectID
    INNER JOIN
shipout ON shipout.id_invoice = orders.OrderKey
GROUP BY orders.orderkey

需要注意的是,我不是数据库管理员,所以我不能索引表
但仍然要向数据库管理员征求建议和索引。

相关问题