mysql - LEFT JOIN 与多个表。表现不佳

Question

我需要优化以下查询。我的表有很多记录:a、b、c、d，查询时间很长。请帮忙。

SELECT main.*, items.*
FROM main
LEFT JOIN (
  (SELECT a.*, 1 AS `type` FROM a) UNION
  (SELECT b.*, 2 AS `type` FROM b) UNION
  (SELECT c.*, 3 AS `type` FROM c) UNION
  (SELECT d.*, 4 AS `type` FROM d)
)
items ON main.`to` = items.id AND main.`type` = items.`type`
WHERE main.id > 0
LIMIT 20

Answer 1

哎呀!像这样的事情:

SELECT main.*, z.*
    FROM (
    ( SELECT  1 AS type, main.id AS mid, a.*, xx
        FROM  main
        JOIN  a  ON a.id = main.to
        WHERE  main.id > 0  AND main.type = 1
        ORDER BY  xx
        LIMIT  20 )
    UNION ALL
    ( SELECT  2 AS type, main.id, b.*, xx
        FROM  main
        JOIN  b  ON a.id = main.to
        WHERE  main.id > 0  AND main.type = 2
        ORDER BY  xx
        LIMIT  20 )
    UNION ALL
    ( SELECT  3 AS type, main.id, c.*, xx
        FROM  main
        JOIN  c  ON a.id = main.to
        WHERE  main.id > 0  AND main.type = 3
        ORDER BY  xx
        LIMIT  20 )
    UNION ALL
    ( SELECT  4 AS type, main.id, d.*, xx
        FROM  main
        JOIN  d  ON a.id = main.to
        WHERE  main.id > 0  AND main.type = 4
        ORDER BY  xx
        LIMIT  20 )
      ORDER BY xx
      LIMIT 20 ) z
    JOIN main  ON main.id = z.mid
ORDER BY xx;

加上索引:

main: PRIMARY KEY(id), INDEX(type, xx, id, to)
a,b,c,d:  PRIMARY KEY(id)

注释:

• 我假设您确实想按 xx 排序，它位于 main 中。
• 我假设所有表都有PRIMARY KEY(id)
• main 上的 4 列索引对于执行过滤和排序有一定帮助；它是“覆盖”。
• 6 ORDER BY + LIMIT 会将中间表最小化为 80 行。最后一个是因为子查询不需要保留顺序。
• main.id > 0 很痛苦——我们能摆脱它吗？
• ALL 而不是 DISTINCT 假设不会有重复。
• SELECT * 很痛苦；可以避免吗？因此，我弄乱了确切的列列表。
• JOIN 返回到 main 是为了避免拖拉所有 main.*；相反，只需要获取 80 个。