来自 NOT IN 查询的 MySQL Beach Ball

Question

我有两个查询，每个查询返回一个节点 ID 列表

SELECT node.nid 
  FROM dpf_node AS node 
 WHERE node.type = 'image' AND node.nid; 

SELECT node.nid 
  FROM dpf_node AS node, dpf_image_galleries_images AS image 
 WHERE image.image_nid = node.nid  
   AND node.type = 'image'
   AND image.gallery_nid = 138;

这两个都工作正常

最终，虽然我想获取第一个结果列表中而不是第二个结果列表中的节点 ID 列表，但我一直在使用此查询:

SELECT node.nid 
  FROM dpf_node AS node 
 WHERE node.type = 'image' 
   AND node.nid NOT IN (SELECT node.nid 
                          FROM dpf_node AS node, dpf_image_galleries_images AS image 
                         WHERE image.image_nid = node.nid 
                           AND node.type = 'image' 
                           AND image.gallery_nid = 138);

有一段时间，这工作得很好，但就在今天晚上，它正在海滩上打球，导致 apache 陷入停顿。我怀疑(/希望)清除数据并重新开始可以解决问题，但真的想解决真正的问题，以防系统启动后问题再次出现。

Answer 1

将其更改为 NOT EXISTS 查询以帮助提高性能:

SELECT node.nid 
  FROM dpf_node AS node 
 WHERE node.type = 'image'
       AND NOT EXISTS (
         SELECT 1
         FROM dpf_node AS i_node
         JOIN dpf_image_galleries_images AS image ON i_node.nid = image.image_nod
         WHERE node.type = 'image'
         AND image.gallery_nid = 138
         AND i_node.nid = node.nid
       )

您还应该验证您是否有足够的索引。