时间戳

首页 > 解决方案 > 查询MySQL中至少一个孩子满足约束1且所有孩子满足约束2的所有行

mysql - 查询MySQL中至少一个孩子满足约束1且所有孩子满足约束2的所有行

问题描述

我有一个 MySQL 数据库，其中的表格代表可能的拼车路线。三个相关表是拼车表（基数约为 200 万）、carpool_stop 表（基数约为 1100 万）和行程表（基数约为 300K）。旅行代表从位置 A 移动到位置 B 的请求。拼车代表汽车通过在多个位置接载用户并在多个位置下车来一次完成多次旅行的可能路线。以下是示例：拼车：

+------------+-----------+
| 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 将不会返回，因为行程 3 已经是拼车的一部分并且是“IN_PROGRESS”。

我有一个有效的查询，但由于 carpool_stop 表中的行数，现在需要 10 分钟才能完成指定的pickup_date，这只是过去一天。

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)

我希望有一种方法可以更快地编写此查询，例如大约一分钟或更短的时间。

标签： mysqlsqldatabasequery-optimization

解决方案

仅根据标题：

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如果您在将其应用于您的数据时需要帮助，请提供。

推荐阅读