MySQL优化复杂的查询而不是IN (2选择和连接)
我有以下查询：
SELECT driver_id, first_name, last_name
FROM drivers
WHERE driver_id NOT IN
(SELECT DISTINCT w.driver_id from waybills w
JOIN drivers d ON d.driver_id = w.driver_id
WHERE w.waybill_owner = 1
AND w.waybill_status = 'dispatched'
AND w.delivery_date = '2014-10-28')
AND driver_id NOT IN
(SELECT DISTINCT wm.driver_id from waybill_movements wm
JOIN drivers d ON d.driver_id = wm.driver_id
WHERE wm.movement_owner = 1
AND wm.delivery_date = '2014-10-28')
AND status = 'active'
AND driver_owner = 1
ORDER BY last_name ASC如何优化此查询？
查询工作良好，并返回预期的结果，但我的问题是，查询是否可以优化。
非常感谢您的时间和帮助。
更新：
而且，是的，我有这些索引：
运单(waybill_owner，waybill_status，w.delivery_date) waybill_movements (wm.movement_owner，delivery_date)驱动程序(driver_id a主键和驱动程序(status，driver_owner)
优化时不需要表结构。
，我没想到会有这么多答案。谢谢大家，
回答 4
Stack Overflow用户
发布于 2014-10-28 13:55:55
我通常会发现，做左联接和查找表中的NULL会更容易、更好地利用可以生成的索引。
The drivers table I would have an index ON (driver_owner, status, driver_id)
Your waybill table, index ON(waybill_owner, driver_id, delivery_date, waybill_status)
waybill_movements, index ON(movement_owner, driver_id, delivery_date )
SELECT
d1.driver_id,
d1.first_name,
d1.last_name
FROM
drivers d1
LEFT JOIN waybills w
ON d1.driver_id = w.driver_id
AND d1.driver_owner = w.waybill_owner
AND w.waybill_status = 'dispatched'
AND w.delivery_date = '2014-10-28'
LEFT JOIN waybill_movements wm
ON d1.driver_id = wm.driver_id
AND d1.driver_owner = wm.movement_owner
AND wm.delivery_date = '2014-10-28'
where
d1.driver_owner = 1
AND d1.status = 'active'
AND w.driver_ID IS NULL
AND wm.driver_id IS NULL
ORDER BY
d1.last_name ASC通过包含运单和waybill_movements的"IS“，您只会得到那些没有匹配记录的。
同时，看到你的waybill_owner =1.这和driver_owner =1也是巧合吗？如果运单所有者应该始终与司机所有者相同，那么我将首先将运单表上的索引更改为waybill_owner，然后是driver_id，然后将其余的连接更改为基于drivers.driver_owner = waybills.waybill_owner (类似于waybill_movements)的联接。
每反馈修正
根据相同的drivers.driver_owner表将索引和查询修改为运单表，以防止交叉所有者匹配的错误结果出现任何奇怪的可能性。
Stack Overflow用户
发布于 2014-10-28 13:44:20
这取决于is的定义是什么。您可能希望将这些WHERE IN子查询重写到FROM子句中，然后对其运行现有的查询，从头到头，查看MySQL解释是否给出了不同的执行路径和状态。
毫无疑问，为了获得所需的结果，您几乎必须在现有查询中三次访问drivers表。这完全是不必要的开销。
SELECT d.driver_id, d.first_name, d.last_name
FROM drivers d
LEFT OUTER JOIN waybills w ON
d.driver_id = w.driver_id AND
w.waybill_owner = 1 AND
w.waybill_status = 'dispatched' AND
w.delivery_date = '2014-10-28'
LEFT OUTER JOIN waybill_movements wm ON
d.driver_id = wm.driver_id AND
wm.movement_owner = 1 AND
wm.delivery_date = '2014-10-28')
WHERE
w.driver_id IS NULL AND
wm.driver_id IS NULL AND
d.status = 'active' AND
d.driver_owner = 1
ORDER BY last_name ASC 您的ORDER BY也很昂贵，因为它们总是如此。如果不需要，那么删除它可能是个好主意。
您可能想运行@StuartLC的建议SQL，而这个通过解释和查看MySQL更喜欢它们中的任何一个。你可能会发现更好的结果，但有时这是一个硬币翻转。对于派生表，MySQL通常不太快，因此尽可能地将其填入传统的连接中可以获得更好的结果。但这完全取决于表的大小，它们是否有适当的索引和所有有趣的东西。
Stack Overflow用户
发布于 2014-10-28 13:43:47
假设您已经看过索引
waybills(waybill_owner, waybill_status, w.delivery_date)waybill_movements (wm.movement_owner, delivery_date)drivers(driver_id)-大概是主键？也有可能是drivers(status, driver_owner)
另外两项改进也浮现在脑海中。
- 这两个子查询实际上都没有使用联合驱动程序表。假设这是有意的，您可以删除联接(如果目的不是过滤根本没有任何驱动程序的运单/移动)，或者只是在
where driver_id IS NOT NULL上过滤(如果这是一个可空的外键) - 如果将这两个子查询合并，则可以执行单个
NOT IN。
SELECT driver_id, first_name, last_name
FROM drivers
WHERE driver_id NOT IN
(
SELECT DISTINCT w.driver_id
from waybills w
WHERE w.waybill_owner = 1
AND w.waybill_status = 'dispatched'
AND w.delivery_date = '2014-10-28'
UNION
SELECT DISTINCT wm.driver_id
from waybill_movements wm
WHERE wm.movement_owner = 1
AND wm.delivery_date = '2014-10-28'
)
AND status = 'active'
AND driver_owner = 1
ORDER BY last_name ASChttps://stackoverflow.com/questions/26609449
复制相似问题
腾讯云开发者
