首页 文章

为什么我的多列查询比相应的单列查询慢得多，即使使用多列索引？

提问于
浏览 次
1

我有以下查询：

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)

在~800ms内返回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)

在~100ms内返回~5k行 .

我尝试使用多列索引（departure_time和stop_id）以及2个单独的索引，但在任何一种情况下，第一个查询似乎都不会少于~800ms . 我的stop_times表有大约3.5M行 . 有什么我可以丢失，这会大大加快第一次查询？

UPDATE 1: SHOW TABLE CREATE:

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而不是整数是我无法控制的......

UPDATE 2: EXPLAIN for departure_time, stop_id 多列索引：

select_type: SIMPLE
type: range
rows: 239084

EXPLAIN for stop_id, departure_time 多列索引：

select_type: SIMPLE
type: range
rows: 141

UPDATE 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
mysql performance indexing

2 回答

  • 0

    你创建了索引 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' 
          )
    回复于
  • 3

    你可以尝试一种可能性，即准备一个列表，列出两个范围内首先出现的所有时间，然后将它们粘在一起，放在一个大的 IN 子句中 - 它可能看起来很糟糕，但它会删除 OR 条件帮助你的查询...你应该能够使用自己喜欢的编程语言构建 IN 字符串:)

    WHERE departure_time IN ('02:41','02:42','02:43', ... '26:41','26:42','26:43', ... etc )

    您的查询包含两个三小时的块，相当于 IN 子句中的6 * 60 = 360个条目...

    值得一试至少......

    回复于

相关问题