MySQL 5.7.16 查询优化：查找tbl_flightno表中的孤儿记录

针对你在MySQL 5.7.16中查询孤儿记录耗时45秒的问题，结合表结构和EXPLAIN结果，提供以下优化方案：

方案1：用NOT EXISTS替代LEFT JOIN #

原LEFT JOIN会遍历所有500万条tbl_flightno记录并关联tbl_airline，再过滤匹配失败的行。改用NOT EXISTS可以提前终止匹配判断，减少不必要的计算开销：

SELECT 
    COUNT(DISTINCT N.World, N.AirlineCode) AS 'Orphans',
    COUNT(N.FlightNoID) AS 'Flights'
FROM tbl_flightno N
WHERE NOT EXISTS (
    SELECT 1 
    FROM tbl_airline A 
    WHERE A.World = N.World 
      AND A.AirlineCode = N.AirlineCode
);

NOT EXISTS的逻辑是：对tbl_flightno的每一行，只要确认tbl_airline中无匹配行就停止查找，相比LEFT JOIN后过滤，能有效减少关联次数。

方案2：预分组减少关联次数 #

原查询需要统计DISTINCT(World, AirlineCode)的数量，先对tbl_flightno按这两个字段分组，再判断分组是否存在于tbl_airline中，可将关联次数从500万降至分组后的数量（通常远小于500万）：

SELECT 
    COUNT(*) AS 'Orphans',
    SUM(flight_count) AS 'Flights'
FROM (
    SELECT 
        World, 
        AirlineCode, 
        COUNT(FlightNoID) AS flight_count
    FROM tbl_flightno
    GROUP BY World, AirlineCode
) AS grouped_flights
WHERE NOT EXISTS (
    SELECT 1 
    FROM tbl_airline A 
    WHERE A.World = grouped_flights.World 
      AND A.AirlineCode = grouped_flights.AirlineCode
);

tbl_flightno已存在World_Airline索引，分组操作可直接利用该索引，无需全表扫描，效率更高。

方案3：强制匹配最优索引 #

tbl_airline的VUnique索引字段顺序为(World, AirlineCode)，与你的关联条件顺序完全匹配，比PRIMARY KEY更适合当前查询。可强制查询使用该索引，减少索引查找开销：

SELECT 
    COUNT(DISTINCT N.World, N.AirlineCode) AS 'Orphans',
    COUNT(N.FlightNoID) AS 'Flights'
FROM tbl_flightno N
WHERE NOT EXISTS (
    SELECT 1 
    FROM tbl_airline A FORCE INDEX (VUnique)
    WHERE A.World = N.World 
      AND A.AirlineCode = N.AirlineCode
);

方案4：使用临时表优化定期查询 #

如果孤儿记录检查是定期执行的，可创建仅包含关联字段的临时表，减少关联时的IO开销：

1. 创建临时表：

CREATE TEMPORARY TABLE temp_airline_keys (
    World varchar(25) NOT NULL,
    AirlineCode int(8) NOT NULL,
    PRIMARY KEY (World, AirlineCode)
) ENGINE=InnoDB;

2. 插入tbl_airline的关联字段数据：

INSERT INTO temp_airline_keys (World, AirlineCode)
SELECT World, AirlineCode FROM tbl_airline;

3. 执行查询：

SELECT 
    COUNT(DISTINCT N.World, N.AirlineCode) AS 'Orphans',
    COUNT(N.FlightNoID) AS 'Flights'
FROM tbl_flightno N
LEFT JOIN temp_airline_keys A 
    ON A.World = N.World AND A.AirlineCode = N.AirlineCode
WHERE A.World IS NULL;

临时表仅保留必要字段，数据更紧凑，关联时的内存和IO开销远低于原tbl_airline。

额外优化建议 #

• 定期维护World_Airline索引：执行OPTIMIZE TABLE tbl_flightno;（需在业务低峰期操作，会锁表），减少索引碎片，提升索引扫描效率。
• 调整MySQL配置：检查innodb_buffer_pool_size（建议设为服务器内存的50%-70%，专用数据库服务器）、join_buffer_size等参数，优化内存分配，缓解数据库负载压力。
• 错峰执行：在业务低峰期运行孤儿记录查询，避免影响正常业务。

内容的提问来源于stack exchange，提问作者Matth