mysql - 为什么我的多列查询比相应的单列查询慢得多，即使使用多列索引也是如此？

Question

我有以下查询:

SELECT * 
from stop_times 
WHERE (departure_time BETWEEN '02:41' AND '05:41' 
       OR departure_time BETWEEN '26:41' AND '29:41') 
    AND stop_times.stop_id IN(51511,51509,51508,51510,6,53851,51522,51533)

在大约 800 毫秒内返回 134 行。如果我拆分它:

SELECT * 
from stop_times 
WHERE (departure_time BETWEEN '02:41' AND '05:41' 
       OR departure_time BETWEEN '26:41' AND '29:41')

在 ~10ms 内返回 ~110k 行

SELECT * 
from stop_times 
WHERE stop_times.stop_id IN(51511,51509,51508,51510,6,53851,51522,51533)

在 ~100 毫秒内返回 ~5k 行。

我尝试同时使用多列索引(departure_time 和 stop_id)以及 2 个单独的索引，但在任何一种情况下，第一个查询似乎都不会少于 ~800 毫秒。我的 stop_times 表有大约 350 万行。是否有任何我可能遗漏的东西会显着加快第一个查询的速度？

更新 1:显示表创建:

CREATE TABLE `stop_times` (
  `trip_id` varchar(20) DEFAULT NULL,
  `departure_time` time DEFAULT NULL,
  `stop_id` varchar(20) DEFAULT NULL,
  KEY `index_stop_times_on_trip_id` (`trip_id`),
  KEY `index_stop_times_on_departure_time_and_stop_id` (`departure_time`,`stop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不幸的是，stop_id 和 trip_id 是 varchars 而不是整数是我无法控制的......

更新 2: EXPLAIN departure_time, stop_id 多列索引:

select_type: SIMPLE
type: range
rows: 239084

EXPLAIN stop_id, departure_time 多列索引:

select_type: SIMPLE
type: range
rows: 141

更新 3: EXPLAIN for IN(51511,51509,51508,51510,6,53851,51522,51533)

select_type: SIMPLE
type: ALL
rows: 3556973 (lol)

EXPLAIN for IN("51511","51509","51508","51510","6","53851","51522","51533")

select_type: SIMPLE
type: range
rows: 141

Answer 1

您是否创建了索引 stop_id, departure_time？因为 departure_time, stop_id 什么都不做。

这是一个真的困难的 - 它有处理索引的所有可能的坏事:(

您有一个范围、一个 OR 和一个不连续的 IN - 没有比这更糟糕的了。

尝试 stop_id, departure_time 如果它没有帮助，那么除了切换到 PostgreSQL 之外，您无能为力。

---

您也可以尝试将查询重写为:

SELECT * 
from stop_times 
WHERE ( stop_times.stop_id IN(51511,51509,51508,51510,6,53851,51522,51533)
      AND departure_time BETWEEN '02:41' AND '05:41'
      )
   OR ( stop_times.stop_id IN(51511,51509,51508,51510,6,53851,51522,51533)
      AND departure_time BETWEEN '26:41' AND '29:41' 
      ) 

或:

    SELECT * 
    from stop_times 
    WHERE ( stop_times.stop_id IN(51511,51509,51508,51510,6,53851,51522,51533)
          AND departure_time BETWEEN '02:41' AND '05:41'
          )
UNION ALL
    SELECT * 
    from stop_times 
    WHERE ( stop_times.stop_id IN(51511,51509,51508,51510,6,53851,51522,51533)
          AND departure_time BETWEEN '26:41' AND '29:41' 
          )