嵌套连接的优化
问题描述：
我试图在我的网站上获得一组商家的订单金额。我必须检查两个项目类型，这使查询非常缓慢。这是我现在使用嵌套连接的优化
SELECT User.ID, COUNT(Order.ID) FROM User
INNER JOIN UserGroup ON UserGroup.userID = User.ID AND UserGroup.groupID = 4
LEFT JOIN Meal ON Meal.userID = User.ID
LEFT JOIN Produce ON Produce.userID = User.ID
LEFT JOIN Order ON (type = 'Meal' AND typeID = Meal.ID) OR (type = 'Produce' AND typeID = Produce.ID)
WHERE Order.rating > 50 AND Order.status = 'DELIVERED'
ORDER BY User.ID ORDER BY COUNT(Order.ID) DESC
当然，所有的连接和状态列索引（transscribed的可读性）查询。当我运行查询的EXPLAIN时，我可以看到它作为type ALL加入了Order tabel，我很确定这是问题所在，我无法弄清楚如何使它作为ref加入。它目前需要2.74秒才能执行。
（不检查在此查询错别字，我改表名和字段名的可读性，并有可能错过了一些东西，关于我的问题但是所有的信息都在）。
输出的EXPLAIN
+----+-------------+-----------+--------+-------------------------+---------+---------+------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+-------------------------+---------+---------+------------------+------+---------------------------------+ | 1 | SIMPLE | UserGroup | ref | userID,groupID | groupID | 4 | const | 74 | Using temporary; Using filesort | | 1 | SIMPLE | User | eq_ref | PRIMARY,isDeleted | PRIMARY | 4 | UserGroup.userID | 1 | Using where | | 1 | SIMPLE | Meal | ref | userID,isDeleted,status | userID | 4 | UserGroup.userID | 18 | | | 1 | SIMPLE | Produce | ref | userID,status,isDeleted | userID | 4 | User.ID | 13 | | | 1 | SIMPLE | Order | ALL | status,isDeleted,type | NULL | NULL | NULL | 1960 | | +----+-------------+-----------+--------+-------------------------+---------+---------+------------------+------+---------------------------------+
答
我解决了这个问题。我可以去两种方式：
使用UNION在上面的意见提出（执行时间：〜10.5ms）
SELECT User.ID, SUM(orderCount) AS totalOrders FROM (
(
SELECT User.ID, COUNT(Order.ID) AS orderCount
FROM User
INNER JOIN UserGroup ON User.ID = UserGroup.userID AND UserGroup.groupID = 4
LEFT JOIN Meal ON User.ID = Meal.userID
LEFT JOIN Order ON Order.type = 'Meal' AND Order.typeID = Meal.ID
GROUP BY User.ID
)
UNION ALL
(
SELECT User.ID, COUNT(Order.ID) AS orderCount
FROM User
INNER JOIN UserGroup ON User.ID = UserGroup.userID AND UserGroup.groupID = 4
LEFT JOIN Produce ON User.ID = Produce.userID
LEFT JOIN Order ON Order.type = 'Produce' AND Order.typeID = Produce.ID
GROUP BY User.ID
)
) AS uni
GROUP BY User.ID
ORDER BY totalOrders DESC
或者，我可以让Order表主表，并做了GROUP BY COALESCE()（执行时间：约76毫秒）
SELECT COALESCE(Meal.userID,Produce.userID) AS cook, COUNT(Order.ID) AS totalOrders
FROM Order
LEFT JOIN Meal ON Order.type = 'Meal' AND Order.typeID = Meal.ID
LEFT JOIN Produce ON Order.type = 'Produce' AND Order.typeID = Produce.ID
INNER JOIN UserGroup ON UserGroup.groupID = 4 AND (Meal.userID = UserGroup.userID OR Produce.userID = UserGroup.userID)
GROUP BY cook
HAVING cook IS NOT NULL
ORDER BY TotalOrders DESC
我决定在后者的可读性。如果有人知道如何在原始问题中解决我的问题，请告诉我。我将它作为一个更大脚本的一部分编写，我可以快速加入查询修饰符。如果我必须改变这个查询，这些不再有效。
您能否将'EXPLAIN'的输出与'Order'表上的索引一起发布？ – kastermester
通常，联合ALl查询比具有或加入条件的查询更快。我知道这对于SQL Server来说是正确的，我怀疑我的sqlas也是如此。 OR条件应避免不惜一切代价。 – HLGEM
**您需要向我们展示表格和索引定义**以及每个表格的行数。也许你的表格定义不好。也许索引没有正确创建。也许你没有一个你认为你做过的那个专栏的索引。没有看到表和索引定义，我们不能说。我们还需要行计数，因为这会大大影响查询优化。如果你知道如何做一个'EXPLAIN'或者得到一个执行计划，那就把结果也放在问题中。 –