在大表上使用 LIKE 操作时 MySQL 查询速度慢

我有一个相当大的表（~ 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 部分。但是，在运行子查询后，如何确定我实际需要搜索的搜索查询类型（例如哪一列）。

想法和技巧表示赞赏。

我相信您当前的索引对于查询来说并不是最佳的，主要是因为“或”表达式。您应该创建 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, airport]，它将确定需要索引扫描或表扫描。

通过三个新索引和新查询，它将三个索引与三个条件相匹配，并确定每个索引都需要索引查找（希望如此）。然后我们包含“scheme”来保存所有符合条件的行的按 id 的行查找。