MySQL中的SELECT DISTINCT语句耗时10分钟
我是MySQL的新手，我正在尝试使用下面的语句选择一组不同的行：
SELECT DISTINCT sp.atcoCode, sp.name, sp.longitude, sp.latitude
FROM `transportdata`.stoppoints as sp
INNER JOIN `vehicledata`.gtfsstop_times as st ON sp.atcoCode = st.fk_atco_code
INNER JOIN `vehicledata`.gtfstrips as trip ON st.trip_id = trip.trip_id
INNER JOIN `vehicledata`.gtfsroutes as route ON trip.route_id = route.route_id
INNER JOIN `vehicledata`.gtfsagencys as agency ON route.agency_id = agency.agency_id
WHERE agency.agency_id IN (1,2,3,4);但是，select语句大约需要10分钟，所以显然有些事情正在进行中。
一个重要的因素是表gtfsstop_times很大。(约2.5亿条记录)
索引似乎设置正确；上面的所有连接都使用索引列。表的大小大致如下：
gtfsagencys - 4 rows
gtfsroutes - 56,000 rows
gtfstrips - 5,500,000 rows
gtfsstop_times - 250,000,000 rows
`transportdata`.stoppoints - 400,000 rows服务器有22‘m的内存，我将InnoDB缓冲池设置为8G，并且我使用的是MySQL 5.6。
有没有人能找到一种让它运行得更快的方法？或者实际上，根本就是这样！
停止点表在不同的模式中有关系吗？
编辑:解释SELECT...返回以下内容：
回答 4
Stack Overflow用户
发布于 2013-04-15 23:40:13
看起来您正在尝试根据特定的标准查找一组停靠点。而且，您可以使用SELECT DISTINCT来避免重复的停止点。是那么回事吗？
看起来atcoCode是您的停止点表的唯一键。是那么回事吗？
如果是这样的话，试试这个：
SELECT sp.name, sp.longitude, sp.latitude, sp.atcoCode
FROM `transportdata`.stoppoints` AS sp
JOIN (
SELECT DISTINCT st.fk_atco_code AS atcoCode
FROM `vehicledata`.gtfsroutes AS route
JOIN `vehicledata`.gtfstrips AS trip ON trip.route_id = route.route_id
JOIN `vehicledata`.gtfsstop_times AS st ON trip.trip_id = st.trip_id
WHERE route.agency_id BETWEEN 1 AND 4
) ids ON sp.atcoCode = ids.atcoCode这样做了几件事:它消除了您似乎不需要的表(代理)。它将agency_id上的搜索从IN(a,b,c)更改为范围搜索，这可能会有帮助，也可能没有帮助。最后，它将DISTINCT处理从必须处理大量数据的情况重新定位为只需要处理ID值的子查询情况。
(JOIN和INNER JOIN是一样的。我使用JOIN使查询更易于阅读。)
这应该会让你的速度更快一点。但是，不得不说，四分之一千兆位的桌子是一张很大的桌子。
Stack Overflow用户
发布于 2013-04-15 23:30:45
如果有250M条记录，我将在一列上分割gtfsstop_times表。然后，每个分片的表可以连接到一个单独的查询中，该查询可以在单独的线程中并行运行，您只需要合并结果集。
Stack Overflow用户
发布于 2013-04-15 23:43:46
诀窍是减少必须计算的gtfsstop_times SQL的行数。在这种情况下，SQL首先计算gtfsstop_times和transportdata**.stoppoints**，的内连接中的每一行，对吗？transportdata.stoppoints有多少行？然后SQL计算WHERE子句，然后计算DISTINCT。它是如何做DISTINCT的？通过多次查看每一行来确定是否有其他行与之类似。那会花很长时间的，对吧？
但是，GROUP BY快速将所有匹配的行压缩在一起，而不计算每个行。我通常使用连接来快速减少查询需要计算的行数，然后查看我的分组。
在这种情况下，您希望将DISTINCT替换为grouping。
试试这个；
SELECT sp.name, sp.longitude, sp.latitude, sp.atcoCode
FROM `transportdata`.stoppoints as sp
INNER JOIN `vehicledata`.gtfsstop_times as st ON sp.atcoCode = st.fk_atco_code
INNER JOIN `vehicledata`.gtfstrips as trip ON st.trip_id = trip.trip_id
INNER JOIN `vehicledata`.gtfsroutes as route ON trip.route_id = route.route_id
INNER JOIN `vehicledata`.gtfsagencys as agency ON route.agency_id = agency.agency_id
WHERE agency.agency_id IN (1,2,3,4)
GROUP BY sp.name
, sp.longitude
, sp.latitude
, sp.atcoCodehttps://stackoverflow.com/questions/16018839
复制相似问题
腾讯云开发者
