mysql - 如何让我的 GTFS 查询运行得更快？

Question

我正在尝试使用 GTFS 数据库，即 RATP 为巴黎及其郊区提供的数据库。

数据集庞大。 stop_times 表有 1400 万行。

这是表模式:https://github.com/mauryquijada/gtfs-mysql/blob/master/gtfs-sql.sql

我正在尝试以最有效的方式获取特定位置的可用路线。据我了解 GTFS 规范，以下是表格及其从我的数据(纬度/经度)到路线的链接:

stops      | stop_times     | trips      | routes
-----------+----------------+------------+--------------
lat        | stop_id        | trip_id    | route_id
lon        | trip_id        | route_id   |
stop_id    |                |            |

我已经通过三个步骤(实际上是上面四个表之间的三个链接)编译了我想要的内容，为了清楚起见，发布在这个要点下:https://gist.github.com/BenoitDuffez/4eba85e3598ebe6ece5f

---

以下是我创建此脚本的方式。

我已经能够在不到一秒的时间内快速找到步行距离(比如 200 米)内的所有站点。我使用:

$ . mysql.ini && time mysql -h $host -N -B -u $user -p${pass} $name -e "SELECT stop_id, (6371000*acos(cos(radians(48.824699))*cos(radians(s.stop_lat))*cos(radians(2.3243)-radians(s.stop_lon))+sin(radians(48.824699))*sin(radians(s.stop_lat)))) AS distance
FROM stops s
GROUP BY s.stop_id
HAVING distance < 200
ORDER BY distance ASC" | awk '{print $1}'
3705271
4472979
4036891
4036566
3908953
3908755
3900765
3900693
3900607
4473141
3705272
4472978
4036892
4036472
4035057
3908952
3705288
3908814
3900832
3900672
3900752
3781623
3781622

real    0m0.797s
user    0m0.000s
sys     0m0.000s

然后，获取今天晚些时候的所有停止时间(使用 stop_times.departure_time > '``date +%T``')会花费很多时间:

"SELECT trip_id
 FROM stop_times
 WHERE
     stop_id IN ($stops) AND departure_time >= '$now'

 GROUP BY trip_id"

$stops 包含从第一步获得的停靠点列表。这是一个例子:

$ . mysql.ini && time mysql -h $host -N -B -u $user -p${pass} $name -e "SELECT stop_id, (6371000*acos(cos(radians(
FROM stops s
GROUP BY s.stop_id
HAVING distance < 200
ORDER BY distance ASC" | awk '{print $1}'
3705271
4472979
4036891
4036566
3908953
...
9916360850964321
9916360920964320
9916360920964321

real    1m21.399s
user    0m0.000s
sys     0m0.000s

这个结果有2000多行。

我的最后一步是选择与这些 trip_id 匹配的所有路线。这很容易，而且相当快:

$ . mysql.ini && time mysql -h $host -u $user -p${pass} $name -e "SELECT r.id, r.route_long_name FROM trips t, routes r WHERE t.trip_id IN (`cat trip_ids | tr '\n' '#' | sed -e 's/##$//' -e 's/#/,/g'`) AND r.route_id = t.route_id GROUP BY t.route_id"
+------+-------------------------------------------------------------------------+
| id   | route_long_name                                                         |
+------+-------------------------------------------------------------------------+
|  290 | (PLACE DE CLICHY <-> CHATILLON METRO) - Aller                           |
|  291 | (PLACE DE CLICHY <-> CHATILLON METRO) - Retour                          |
|  404 | (PORTE D'ORLEANS-METRO <-> ECOLE VETERINAIRE DE MAISON-ALFORT) - Aller  |
|  405 | (PORTE D'ORLEANS-METRO <-> ECOLE VETERINAIRE DE MAISON-ALFORT) - Retour |
|  453 | (PORTE D'ORLEANS-METRO <-> LYCEE POLYVALENT) - Retour                   |
|  457 | (PORTE D'ORLEANS-METRO <-> LYCEE POLYVALENT) - Retour                   |
|  479 | (PORTE D'ORLEANS-METRO <-> VELIZY 2) - Retour                           |
|  810 | (PLACE DE LA LIBERATION <-> GARE MONTPARNASSE) - Aller                  |
|  989 | (PORTE D'ORLEANS-METRO) - Retour                                        |
| 1034 | (PLACE DE LA LIBERATION <-> HOTEL DE VILLE DE PARIS_4E__AR) - Aller     |
+------+-------------------------------------------------------------------------+

real    0m1.070s
user    0m0.000s
sys     0m0.000s

这里是包含 2k 行程 ID 的文件 trip_ids。

我怎样才能更快地得到这个结果？是否有比我采用的 stops>stop_times>trips>routes 路径更好的方式来爬取数据？

实际上一个“查询”的总时间约为 30 秒:“距此位置 200 米有哪些可用路线？”。太过分了……

Answer 1

简短的回答是:使用表连接和索引。

这是更长的答案:

您的想法是正确的，并且您对表格之间的关系的理解是正确的。但是，通过要求 DBMS 匹配列表中的字段值(使用 WHERE...IN)而不是将表连接在一起，您需要它做比它需要做的更多的工作。

您真正想要做的是将所有这些作为单个查询执行，使用 JOIN 子句将表链接在一起。试试这个，它另外加入了 calendars 和 calendar_dates 表，以将结果限制为仅今天实际运营的路线:

SELECT DISTINCT r.id, r.route_long_name
  FROM (SELECT s.stop_id, (6371000 *
          acos(cos(radians(48.824699)) * cos(radians(s.stop_lat)) *
          cos(radians(2.3243) - radians(s.stop_lon)) +
          sin(radians(48.824699)) * sin(radians(s.stop_lat)))) AS distance
          FROM stops AS s) AS i_s
  INNER JOIN stop_times AS st ON st.stop_id = i_s.stop_id
  INNER JOIN (SELECT trip_id, route_id FROM trips AS t
                INNER JOIN (SELECT service_id FROM calendars
                              WHERE start_date <= '2014-09-09'
                                AND end_date >= '2014-09-09'
                                AND tuesday = 1
                              UNION
                                SELECT service_id FROM calendar_dates
                                  WHERE date = '2014-09-09'
                                    AND exception_type = 1
                              EXCEPT
                                SELECT service_id FROM calendar_dates
                                  WHERE date = '2014-09-09'
                                    AND exception_type = 2) AS c
                   ON c.service_id = t.service_id) AS t_r
    ON t_r.trip_id = st.trip_id
  INNER JOIN routes AS r ON r.route_id = t_r.route_id
  WHERE st.departure_time > '$now'
    AND i_s.distance < 200;

这里 INNER JOIN 用于“添加”另一个表的列，仅包括那些与 ON 子句中的条件匹配的行。这比使用一个查询生成结果列表然后将其输入到下一个查询要快很多。

但是，为了获得更好的性能，您需要创建索引以防止 DBMS 必须线性扫描表。经验法则是为 JOIN 或 WHERE 子句中使用的每一列定义一个索引。以下是我定义的索引，您应该会发现这些索引使上述查询执行得很好:

CREATE INDEX calendar_dates_date_exception_type_service_id_index
  ON calendar_dates (date, exception_type, service_id);

CREATE INDEX trips_service_id_trip_id_route_id_index
  ON trips (service_id, trip_id, route_id);

CREATE INDEX stop_times_trip_id_departure_time_stop_id_index
  ON stop_times (trip_id, departure_time, stop_id);

CREATE INDEX routes_route_id_index ON routes (route_id);

CREATE INDEX stops_stop_id_index ON stops (stop_id);