时间戳

首页 > 解决方案 > 通过合并的 LEFT JOIN 过滤

mysql - 通过合并的 LEFT JOIN 过滤

问题描述

我有一个我正在尝试优化的 MySQL 查询，这与权限有关。这是一个简化版本：

SELECT COUNT(*) FROM photo
    LEFT JOIN photo_access ON viewer=4 AND owner=photo.photographer
WHERE
    photo.album = 10
AND NOT photo.hidden
AND COALESCE(photo_access.access, 3) >= photo.privacy; -- 3=default access

出于我们的目的，这是我们关心的每个表的列。

photo.photographer : User ID of who took the photo
photo.album        : Album ID (photo can only belong to one album)
photo.hidden       : Tombstone value for photos that were removed by moderators
photo.privacy      : Int, higher values = more restricted, 1=public 8=just_me
photo_access.viewer : Who is making the web request
photo_access.owner  : Who owns the content
photo_access.access : Value like photo.privacy

大多数用户对其他用户的内容具有默认访问级别 (3)。我们将与默认值的偏差（由于友谊或阻止人等）存储在photo_access表中。对于给定的一对查看者和内容所有者，您可以将其视为对 access=3 默认值的覆盖。

现在假设有几百万用户。实际存储所有可能的配对将是 N 2行，这有点疯狂。为简单起见，我考虑存储默认对，但在这种规模下这是不合理的。

对于某些值和情况，此设置效果很好。但是要抛出一些比例数字，为了得到与查询优化器的基数估计器相同的脑波，假设有：

就我的直觉而言，我认为首先过滤专辑/隐藏，然后过滤隐私是有意义的。但是当我EXPLAIN查询时，它：

而且这个查询不是异步报告。它运行大约 0.50 秒，而它应该在 0.01-0.06 秒范围内运行。这也让我对构建临时表保持警惕。

我在这里缺少什么吗？这些过滤条件（相册与基于摄影师的 photo_access 连接）是否具有不可能的交叉用途？即使是（准确的！）标记为重复项也会为我指明正确的方向。

标签： mysqlleft-join

解决方案

也许将您的选择拆分为两个选择的联合可能会有所帮助？像这样的东西。

SELECT COUNT(*) FROM
    (SELECT 1 as `dummy` FROM photo
        INNER JOIN photo_access ON viewer=4 AND owner=photo.photographer
    WHERE
        photo.album = 10
    AND NOT photo.hidden
    AND photo_access.access >= photo.privacy
      UNION ALL
    SELECT 1 as `dummy` FROM photo
        LEFT JOIN photo_access ON viewer=4 AND owner=photo.photographer
    WHERE
        photo.album = 10
    AND NOT photo.hidden
    AND photo_access.access is null)

推荐阅读