mysql - 将 ORDER BY 'x' 与 JOIN 一起使用，但保留没有 'x' 值的行

Question

这是一个相对复杂的问题的简化版本，我和我的同事都无法完全理解。

考虑两个表，table_a 和 table_b。在我们的 CMS 中，table_a 包含存储在数据库中的所有数据的元数据，table_b 包含一些更具体的信息，因此为简单起见，title 和 date 列。

目前我们的查询如下所示:

SELECT * 
FROM `table_a` LEFT OUTER JOIN `table_b` ON (table_a.id = table_b.id)
WHERE table_a.col = 'value'
ORDER BY table_b.date ASC
LIMIT 0,20

当 table_a 有大量行时，这会严重退化。如果更改 JOIN RIGHT OUTER JOIN(这会触发 MySQL 使用在 table_b.date 上设置的 INDEX)，查询会无限快，但不会产生相同的结果(因为如果 table_b.date 没有值，它将被忽略)。

这在我们的 CMS 中成为一个问题，因为如果用户在日期列上排序，任何没有设置日期的行都会从界面中消失，造成令人困惑的 UI 体验，并且难以为缺少它们的行。

是否有解决方案可以:

1. 使用 table_b.date 的 INDEX 以便查询将更好地扩展
2. 以某种方式保留这些行table_b 没有date设置以便用户可以输入数据

Answer 1

我赞同 ArtoAle 的评论。由于 order by 适用于 table_b 中缺失行的外连接中的空值，因此这些行无论如何都会乱序。

模拟的外连接是丑陋的部分，所以让我们先看看它。 Mysql没有except，所以你需要用exists来写查询。

SELECT table_a.col1, table_a.col2, table_a.col3, ... NULL as table_b_col1, NULL as ...
FROM
    table_a
WHERE
    NOT EXISTS (SELECT 1 FROM table_a INNER JOIN table_b ON table_a.id = table_b.id);

应该将其作为内部联接与原始查询一起UNION ALL。 UNION_ALL 需要保留原始顺序。

无论您做什么，这种查询都可能非常慢，因为没有索引可以轻松支持“外键不存在”类型的查询。这基本上归结为 table_a.id 中的索引扫描，并查找(或者可能是并行扫描)table_b.id 中的相应行。