php - 从 PHP 和 MySQL 中的另一个表中排除项目

Question

我有一个表跟踪每个用户单击的链接，我还有另一个包含链接的表。这是每个表结构:

链接:编号 |链接 |值(value) |添加日期

点击:用户 ID |链接 ID | date_clicked

现在这是我用来进行搜索的代码，它可以正常工作，我只想知道是否有更有效的方法，因为点击的链接表会变得非常大非常快.

$history_query = mysql_query("SELECT * FROM clicked_links WHERE user_id = '$id'") or die(mysql_error());
$history_array = array();
while ($h = mysql_fetch_array($history_query)) {
    $history_array[] = $h['link_id'];
}
$clicked = implode(',', $history_array);

$link_query = mysql_query("SELECT * FROM chip_links WHERE id NOT IN ($clicked) ORDER BY value DESC") or die(mysql_error());
while ($r = mysql_fetch_array($link_query)) {
    echo "<div id='claim{$r['id']}' style='text-align: center; font-weight: bold; font-size: 18px; float: left; width: 183px;'>
    <a href='{$r['link']}' id='{$r['id']}' class='collect' target='_blank'>
    Claim {$r['value']} points!
    </a>
    </div>";
}

Answer 1

运行单个查询来获取结果集比运行单独的查询效率更高。

您不需要返回所有的 link_id 值，将它们放在一个数组中，将数组放入一个字符串中，然后将该字符串推送到另一个查询中，然后将其洗牌回数据库...数据库已经有了。

此查询将返回一个与您当前的 $link_query 等效的结果集，而不需要 $history_query 或 $history_array。

SELECT l.id
     , l.link
     , l.value
  FROM chip_links l
 WHERE l.id NOT IN
       ( SELECT c.link_id
           FROM clicked_links c
          WHERE c.user_id = '$id'
            AND c.link_id IS NOT NULL
       )
 ORDER BY l.value DESC

如果您无法保证 clicked_links 表中的 link_id 不为空，您需要在该子查询中包含一个 link_id IS NOT NULL 谓词，因为查询赢了如果 link_id 值为 NULL，则不返回任何行。 (这是 NOT IN (subquery) 构造的一个众所周知且可避免的问题。

MySQL 很可能会将其优化为(希望更高效但)等效的 NOT EXISTS 相关子查询，如下所示:

SELECT l.id
     , l.link
     , l.value
  FROM chip_links l
 WHERE NOT EXISTS 
       ( SELECT 1 
           FROM clicked_links c
          WHERE c.user_id = '$id'
           AND c.link_id = l.id
       )
 ORDER BY l.value DESC

不过，为了获得最佳性能，您可能希望使用反连接模式。

LEFT JOIN 操作基本上是查找匹配的行，IS NOT NULL 谓词将抛出匹配的行，所以您得到的是来自 chip_links 的行，其中clicked_links 中没有“匹配”行。

MySQL 优化器通常使用如下查询生成最有效的计划:

SELECT l.id
     , l.link
     , l.value
  FROM chip_links l
  LEFT
  JOIN clicked_links c
    ON c.link_id = l.id
   AND c.user_id = '$id'
 WHERE c.link_id IS NULL
 ORDER
    BY l.value DESC

为了在大型集合上获得良好的性能，您可能还需要索引

... ON clicked_links (user_id, link_id)

... ON chip_links (value, id, link)

这应该允许完全从索引中满足查询，而不需要排序操作。 EXPLAIN 输出将包含“Using index”，但不包含“Using filesort”)。