mysql - 没有结果时查询工作太慢。如何改进？

Question

我有三个表

filters (id, name)
items(item_id, name)
items_filters(item_id, filter_id, value_id)
values(id, filter_id, filter_value)

项目中大约有 20000 个条目。items_filters 中大约有 80000 个条目。

SELECT i.*
    FROM items_filters itf INNER JOIN items i ON i.item_id = itf.item_id
     WHERE (itf.filter_id = 1 AND itf.value_id = '1') 
     OR (itf.filter_id = 2 AND itf.value_id = '7') 
    GROUP BY itf.item_id 
    WITH ROLLUP 
    HAVING COUNT(*) = 2
   LIMIT 0,10;

有条目匹配查询时为 0.008 次，没有条目匹配时为 0.05 次。

我之前尝试过不同的变体:

SELECT * FROM items WHERE item_id IN (
    SELECT `item_id` 
     FROM `items_filters` 
     WHERE (`filter_id`='1' AND `value_id`=1) 
     OR (`filter_id`='2' AND `value_id`=7)
    GROUP BY `item_id` 
    HAVING COUNT(*) = 2
   ) LIMIT 0,6;

当没有条目时，这会完全卡住 mysql。

我真正不明白的是 选择 i.* FROM items_filters itf INNER JOIN 项目 i ON i.item_id = itf.item_id WHERE itf.filter_id = 1 AND itf.value_id = '1' LIMIT 0,1未找到条目时取 ~0.05，有条目时取 ~0.008

解释

| id | select_type | table | type | possible_keys | key     | key_len | ref                 | rows | Extra                           |
|  1 | SIMPLE      | i     | ALL  | PRIMARY       | NULL    | NULL    | NULL                |   10 | Using temporary; Using filesort |
|  1 | SIMPLE      | itf   | ref  | item_id       | item_id | 4       | ss_stylet.i.item_id |    1 | Using where; Using index        |

Answer 1

除了在两个 (filter_id, value_id) 上确保和索引 items_filters 之外，我会预先使用分组依据对您的项目 ID 进行资格预审，然后加入项目表。看起来您正试图找到满足两个特定条件的项目，对于那些，捕获项目...

我还在外部保留了“group by with rollup”，即使从内部查询返回的每个 ID 都有一个实例。但是由于内部查询已经应用了 0,10 条记录的限制，因此它不会抛出太多结果以连接到您的项目表。

但是，由于您没有进行任何聚合，我相信外部 group by 和 rollup 不会真正为您提供任何好处，否则可能会被删除。

SELECT i.*
   FROM
      ( select itf.item_id
           from items_filters itf
          WHERE (itf.filter_id = 1 AND itf.value_id = '1') 
             OR (itf.filter_id = 2 AND itf.value_id = '7') 
          GROUP BY itf.item_id 
          HAVING COUNT(*) = 2
          LIMIT 0, 10 ) PreQualified
      JOIN items i 
         ON  PreQualified.item_id = i.item_id

另一种方法可能是对内部查询执行 JOIN，这样您甚至不需要应用分组依据和拥有。由于您正在明确地寻找两个项目，因此我会尝试以下操作。这样，第一个限定符是它必须具有 ID = 1 和值 = '1' 的条目。它甚至没有击中那个条目，它永远不会关心第二个。然后，通过对同一个表(别名为 itf2)应用连接，它必须找到相同的 ID——以及第二个的条件(id = 2 值 = '7')。这基本上迫使看起来几乎像在考虑其他任何事情之前首先对一个条目进行单次传递。在获取项目详细信息之前，这仍然会导致您的 10 个数量有限。

SELECT i.*
   FROM
      ( select itf.item_id
           from items_filters itf
              join items_filters itf2
                 on itf.item_id = itf2.item_id
                AND itf2.filter_id = 2 
                AND itf2.value_id = '7'
          WHERE 
             itf.filter_id = 1 AND itf.value_id = '1'
          LIMIT 0, 10 ) PreQualified
      JOIN items i 
         ON  PreQualified.item_id = i.item_id

我还根据您对重复项的评论(这是我所期望的)删除了 group by/with rollup。