mysql - 每组通用最大 N 查询太慢

Question

以下查询需要 18 分钟才能完成。如何优化它以使其执行速度更快？

基本上，我对每个公民的查询都从 citizens_static 加入行和citizens_dynamic表在哪里 update_id_to列最高。

INSERT INTO latest_tmp (...)

SELECT cs1.*, cd1.*
FROM citizens c

JOIN citizens_static  cs1 ON c.id = cs1.citizen_id
JOIN citizens_dynamic cd1 ON c.id = cd1.citizen_id

JOIN (
    SELECT citizen_id, MAX(update_id_to) AS update_id_to
    FROM citizens_static
    GROUP BY citizen_id
) AS cs2 ON c.id = cs2.citizen_id AND cs1.update_id_to = cs2.update_id_to

JOIN (
    SELECT citizen_id, MAX(update_id_to) AS update_id_to
    FROM citizens_dynamic
    GROUP BY citizen_id
) cd2 ON c.id = cd2.citizen_id AND cd1.update_id_to = cd2.update_id_to;

latest_tmp table 是 MyISAM 表，在导入期间禁用了索引。禁用它们将执行时间从 20 分钟缩短到 18 分钟，所以这不是最大的问题。

我还对 LEFT JOIN 进行了基准测试接近 WHERE t2.column IS NULL 。与 INNER JOIN 相比需要几个小时我正在使用的方法。

解释下面的查询输出。它似乎正在使用索引。

citizens_dynamic和citizens_static主键位于 citizen_id,update_id_to和 update_id_to,citizen_id 上名为“id”的辅助键列。

Answer 1

你能用英语解释一下你想要什么吗？

然后参见Groupwise Max并根据需要编辑以下内容:

SELECT
        province, n, city, population
    FROM
      ( SELECT  @prev := '', @n := 0 ) init
    JOIN
      ( SELECT  @n := if(province != @prev, 1, @n + 1) AS n,
                @prev := province,
                province, city, population
            FROM  Canada
            ORDER BY
                province,
                population DESC
      ) x
    WHERE  n <= 3
    ORDER BY  province, n;

无论内部 ORDER BY 上的 ASC/DESC 如何，都会进行全表扫描和“文件排序”。