查询MySQL中至少有一个子级满足约束1且所有子级满足约束2的所有行
我有一个MySQL数据库，其中包含表示可能的拼车路线的表。这三个相关的表是一个拼车表(基数~200万)、一个carpool_stop表(基数~1100万)和一个trip表(基数~300K)。Trips表示从位置A移动到位置B的请求。Carpool表示一辆汽车一次完成多次旅行的可能路线，方法是在多个位置接送用户并在多个位置将他们放下。以下是示例:拼车：
+------------+-----------+
| carpool_id | completed |
+------------+-----------+
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
+------------+-----------+carpool_stop：
+------------+---------+---------+
| carpool_id | trip_id | type |
+------------+---------+---------+
| 1 | 1 | pickup |
| 1 | 2 | pickup |
| 1 | 2 | dropoff |
| 1 | 1 | dropoff |
| 2 | 2 | pickup |
| 2 | 3 | pickup |
| 2 | 3 | dropoff |
| 2 | 2 | dropoff |
| 3 | 3 | pickup |
| 3 | 4 | pickup |
| 3 | 4 | dropoff |
| 3 | 3 | dropoff |
+------------+---------+---------+行程：
+---------+------------+---------------+--------------+
| trip_id | carpool_id | status | pickup_date |
+---------+------------+---------------+--------------+
| 1 | NULL | 'INITIAL' | '2019-04-01' |
| 2 | NULL | 'INITIAL' | '2019-04-02' |
| 3 | 3 | 'IN_PROGRESS' | '2019-04-03' |
| 4 | 3 | 'INITIAL' | '2019-04-03' |
+---------+------------+---------------+--------------+在trip.pickup_date上有一个索引。我们的目标是获得所有满足这些条件的拼车：
at least one trip has a pickup_date later than a specified date
AND
(the carpool is completed OR
(all trips have status in ('INITIAL', 'WAITING') AND have a NULL carpool_id))在上面的例子中，如果指定的pickup_date是'2019-04-02'，那么它将是拼车1和拼车3。拼车2不会被返回，因为trip 3已经是拼车的一部分，并且是' In _PROGRESS‘。
我有一个有效的查询，但是由于carpool_stop表中的行数，指定的pickup_date只需要一天就需要10分钟才能完成。
SELECT carpool.*
FROM (
SELECT carpool_stop.carpool_id
FROM trip
JOIN carpool_stop ON carpool_stop.trip_id = trip.trip_id
JOIN carpool ON carpool.carpool_id = carpool_stop.carpool_id
WHERE trip.pickup_date >= '2019-04-02'
GROUP BY carpool.carpool_id
) AS inner_query
JOIN carpool ON carpool.carpool_id = inner_query.carpool_id
JOIN carpool_stop ON carpool_stop.carpool_id = carpool.carpool_id
JOIN trip ON trip.trip_id = carpool_stop.trip_id
GROUP BY carpool.carpool_id
HAVING (sum(CASE WHEN (trip.status NOT IN ('INITIAL', 'WAITING') OR trip.carpool_id IS NOT NULL)
THEN 1
ELSE 0
END) = 0
OR carpool.completed = 1)我希望有一种更快的方法来编写这个查询，例如一分钟或更短的时间。
回答 2
Stack Overflow用户
发布于 2019-04-05 03:35:59
我假设对pickup_date列进行了索引。如果不是，那么无论你做什么，查询都会很慢。
要记住的主要事情是，大多数行都是历史记录(trip.pickup_date < '2019-04-02')。因此，您需要的是一个查询(或子查询)，它只选择最近的行程，然后围绕此构建查询的其余部分。
你用你内心的疑问做到了这一点，所以我会说你有正确的想法。那么为什么它是慢的呢？要么是pickup_date没有索引，要么是您的查询编写方式使MySQL无法使用该索引。(MySQL的EXPLAIN command可以显示是否发生了这种情况。)
有一些方法可以简化查询。只有几个：
- 我不认为内部查询需要连接到carpool表--不过我并不期望这会带来很大的加速。您可以尝试将整个代码编写为两个
语句，然后使用
- 。(这也会去掉OR，这有时会有帮助。)
- 有一些方法可以去掉GROUP BY ..这可能会有帮助，也可能没有帮助。
或者:在我看来，查询返回的是已完成的拼车，加上尚未开始的拼车。相反，测试介于两者之间的所有拼车可能会更简单(即拼车未完成；但至少有一次行程的状态为已接或更晚)。如果您尝试这样做，请将结果与缓慢的查询进行比较，以确保它们返回相同的结果。可能有一些模糊的状态需要处理。
Stack Overflow用户
发布于 2019-04-22 04:30:12
仅根据标题：
SELECT ...
FROM ...
WHERE EXISTS( SELECT 1 FROM ... WHERE ... ) -- at least 1 child
AND NOT EXISTS( SELECT 1 FROM ... WHERE NOT ... ) -- all (ie, none fail)如果在将SHOW CREATE TABLE应用于您的数据时需要帮助，请提供它。
https://stackoverflow.com/questions/55520804
复制相似问题
腾讯云开发者
