mysql - 对大表使用 LIKE 操作时 MySQL 查询速度变慢

Question

我有一个相当大的表(~ 6 GB)并且我在这个查询上有性能问题:

          SELECT f.*,
          TIME_FORMAT(f.scheme, '%H:%i') as scheme,
          TIME_FORMAT(f.actual, '%H:%i') as actual,
          DATE_FORMAT(f.flight_date, '%d-%m-%Y') as flight_date_formatted,
          a.iata
          FROM flights_database f
          LEFT JOIN airports a ON f.airport = a.airportNameClean
          WHERE f.flight_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY)
          AND DATE_ADD(CURDATE(), INTERVAL 2 DAY)
          AND (f.flight_number LIKE 'New York%' OR f.airport LIKE 'New York%' OR f.airline LIKE 'New York%')
          ORDER by f.flight_date DESC, f.flight_scheme DESC
          LIMIT 50"

我使用了 EXPLAIN 并确定了这些潜在的问题

• 使用多个 LIKE 和 OR 让我们使用一个范围(使用 WHERE)的记录并且似乎使速度变慢
• f.flight_scheme DESC，添加时使用文件排序。删除后，不使用文件排序。

我在 flight_date, flight_number, airport, airline, scheme 上有一个索引，它报告要使用它。但是这个查询仍然需要大约 30 秒，这当然太多了。

可能有用的是使用某种子查询来替换 OR 部分。但是，在运行子查询后，我如何确定我实际需要搜索的搜索查询类型(例如，哪一列)。

感谢想法和提示。

Answer 1

我认为您当前的索引不是查询的最佳索引，主要是因为“或”表达式。您应该创建 3 个索引。

(航类号、航类日期、模式)

(机场、航类日期、架构)

(航空公司、航类日期、架构)

然后更改查询以使用三个索引。您也可以尝试一下，也可以通过添加一个 order by 并将其限制为 50 来修剪每个子查询。

select flight.*,
    TIME_FORMAT(flight.scheme, '%H:%i') as scheme,
    TIME_FORMAT(flight.actual, '%H:%i') as actual,
    DATE_FORMAT(flight.flight_date, '%d-%m-%Y') as flight_date_formatted,
    a.iata
from (
    select *
    from (
        select f.Id,
            f.flight_date,
            f.schema
        from flights_database f
        where f.flight_date between DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                and DATE_ADD(CURDATE(), INTERVAL 2 DAY)
            and f.flight_number like 'New York%'
        order by f.flight_date desc,
            f.schema desc limit 50

        union

        select f.Id,
            f.flight_date,
            f.schema
        from flights_database f
        where f.flight_date between DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                and DATE_ADD(CURDATE(), INTERVAL 2 DAY)
            and f.airline like 'New York%'
        order by f.flight_date desc,
            f.schema desc limit 50

        union

        select f.Id,
            f.flight_date,
            f.schema
        from flights_database f
        where f.flight_date between DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                and DATE_ADD(CURDATE(), INTERVAL 2 DAY)
            and f.airport like 'New York%'
        order by f.flight_date desc,
            f.schema desc limit 50
        ) f1
    order by f1.flight_date desc,
        f.schema desc limit 50
    ) f2
inner join flights_database flight on f2.Id = flight.Id
left join airports a on flight.airport = a.airportNameClean;

目前您的 or 语句将扩展为:[flight_date, flight_number], [flight_date, airline], [flight_date, airport]

所以当优化器查看你的索引时，它会匹配[flight_date, flight_number] 到你当前的索引 [flight_date, flight_number, airport, airline, scheme](注意它们是如何开始的)，但是当它遇到 [flight_date, airline] 时，没有索引来匹配这个表达式。因此优化器会确定它需要进行索引扫描还是表扫描。然后它会再次遇到 [flight_date, airport] 它会确定这是需要索引扫描还是表扫描。

使用三个新索引和新查询，它将三个索引与三个条件匹配，并确定每个索引都需要索引查找(希望如此)。然后我们包含 'scheme' 以保存符合条件的所有行的 id 行查找。