我有一个可以连接到自身的表。我想连接它两次。这是架构:

CREATE TABLE `route_connections` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `from_route_iid` int(11) NOT NULL,
    `from_service_id` varchar(100) NOT NULL,
    `to_route_iid` int(11) NOT NULL,
    `to_service_id` varchar(100) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `to_route` (`to_route_iid`),
    KEY `from_route` (`from_route_iid`),
    KEY `to_service` (`to_service_id`),
    KEY `from_service` (`from_service_id`),
    KEY `from_to_route` (`from_route_iid`,`to_route_iid`)
) ENGINE=InnoDB AUTO_INCREMENT=6798783 DEFAULT CHARSET=utf8

它有大约 370 万行。

我的主要目标是找到一条使用 3 条路线(2 条路线连接)的路径，知道允许的出发和到达路线列表(连接路线必须由查询确定)。

路径:A路→B路→C路:

• 出发路线(已知列表，A)
• route_connection c1 (A → B)
• 连接路线(未知，B)
• route_connection c2 (B → C)
• 到达路线(已知列表，C)

所以我需要选择三个route_iid:c1.from, c1.to or c2.from(相同)和 c2.to。

此外，我需要使用以下过滤器过滤每个 service_id:

service_id in (
    select service_id from (
        select service_id from calendar c
            where c.start_date <= 20141109 and end_date >= 20141109 

        union

        select service_id from calendar_dates cd 
            where cd.date = 20141109 and exception_type = 1 
    ) x 
    where x.service_id not in (
        select service_id from calendar_dates cd 
        where cd.date = 20141109 and exception_type = 2
    )
)

首先，我在处理连接路由时没有处理 service_id 过滤。

仅搜索一个连接时，查询耗时 <1>

select c.*
from route_connections c
where c.from_route_iid in (864, 865, 495, 494, 459, 54, 458)
    and c.to_route_iid in (745, 744, 1096, 1093, 743, 317, 742, 13, 316)

但我的目标是找到 2 个连接，所以我正在使用这个查询，这需要很多时间(结果也是零):

select c1.*, c2.*
from
route_connections c1
inner join route_connections c2 on c2.from_route_iid = c1.to_route_iid
    and c2.to_route_iid in (745, 744, 1096, 1093, 743, 317, 742, 13, 316)
where c1.from_route_iid in (864, 865, 495, 494, 459, 54, 458)

过去需要 50 秒，但我添加了 from_to_route 索引，这将查询速度加快到 18-20 秒。

我也试过不使用连接:

SELECT ...
FROM route_connections c1, route_connections c2
WHERE ...

但它产生完全相同的性能(我猜它在内部与连接完全相同)。

我尝试将内部连接更改为左连接 + 一个 HAVING 子句，但情况要糟糕得多(正如预期的那样)。

我试图删除除这两个之外的所有索引:

• 主键(id)，
• KEY from_route_iid (from_route_iid,to_route_iid)

结果是一样的，都在 18-20 秒左右。

这里是解释:

+----+-------------+-------+-------+------------------------------------+----------------+---------+----------------------------------+-------+----------------------------------+
| id | select_type | table | type  | possible_keys                      | key            | key_len | ref                              | rows  | Extra                            |
+----+-------------+-------+-------+------------------------------------+----------------+---------+----------------------------------+-------+----------------------------------+
|  1 | SIMPLE      | c1    | range | to_route,from_route,from_route_iid | from_route     | 4       | NULL                             | 15464 | Using index condition; Using MRR |
|  1 | SIMPLE      | c2    | ref   | to_route,from_route,from_route_iid | from_route_iid | 4       | bicou_gtfs_paris.c1.to_route_iid |  1746 | Using index condition            |
+----+-------------+-------+-------+------------------------------------+----------------+---------+----------------------------------+-------+----------------------------------+

将表连接到自身的正确方法是什么？我是否遗漏了索引或其他内容？

硬件是 2014 款 macbook air，配备 1.7GHz Core i7、8GB RAM 和 256GB SSD。
软件为Mac OS X 10.10 Yosemite，MySQL 5.6.21

最佳答案

好的，这是我找到解决方案的方式:

select to_route_iid
from route_connections
where from_route_iid in (864, 865, 495, 494, 459, 54, 458)

=> 15471 行

select to_route_iid
from route_connections
where from_route_iid in (864, 865, 495, 494, 459, 54, 458)
group by to_route_iid

=> 97 行!

到达路线也是一样，131 行 vs 25427。

所以这个查询:

select c1.from_route_iid, c2.from_route_iid, c2.to_route_iid
from (
    select from_route_iid, to_route_iid
    from route_connections
    where from_route_iid in (864, 865, 495, 494, 459, 54, 458)
    group by to_route_iid
) c1, route_connections c2
where c2.from_route_iid = c1.to_route_iid
and c2.to_route_iid in (745, 744, 1096, 1093, 743, 317, 742, 13, 316)
group by c2.from_route_iid, c2.to_route_iid

运行时间为 145 毫秒。太好了，今天早上我从 2 分钟开始:)

关于mysql - 将表连接到自身非常慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/26827462/