mysql - 使用 IN 子查询改进 MySQL 查询

Question

我有一个表 items 和一个表 item_attributes。

为简单起见，假设我的表项有一个列 id 和一个列 name。当然，id 列上有一个索引。

item_attributes 表包含列 id、item_id、attribute_name 和 attribute_value 和索引 ON attrubute_name

现在我想在不使用连接的情况下查询具有特定属性的所有项目。

我使用以下查询来执行此操作:

SELECT *
FROM items i
WHERE i.id IN (
    SELECT item_id
    FROM item_attributes a
    WHERE a.attribute_name = 'SomeAttribute'
      AND a.attribute_value = 'SomeValue'
)

子查询本身运行速度很快。

如果我首先执行查询本身并将结果用于 IN 查询

SELECT *
FROM items i
WHERE i.id IN (1,3,5,7,10,...)

它也很快。

但是，组合查询非常非常慢(>2 秒)如果我调查查询计划，我就会明白原因:MySQL 对 items 表进行全表扫描，而不是先执行子查询并将结果用于索引查询。

1, 'PRIMARY', 'items', 'ALL', '', '', '', '', 149726, 'Using where'
2, 'DEPENDENT SUBQUERY', 'item_attributes', 'index_subquery', 'IDX_ATTRIBUTE_NAME', 'IDX_ATTRIBUTE_NAME', '4', 'func', 1, 'Using where'

有没有办法优化这个查询？我知道子查询将始终只返回一个小结果集(<100 行)。

Answer 1

MySQL 无法在IN 子句中切换主从表。这将是 corrected in 6.0 .

现在，您可以像这样重写它(需要 JOIN):

SELECT  i.*
FROM    (
        SELECT  DISTINCT item_id
        FROM    item_attributes a
        WHERE   a.attribute_name = 'SomeAttribute'
                AND a.attribute_value = 'SomeValue'
        ) ai
JOIN    items i
ON      i.id = ai.item_id

由于您使用的是 EAV 模型，您可能希望在 (attribute_name, item_id) 上创建唯一索引，在这种情况下，您可以使用普通连接:

SELECT  i.*
FROM    item_attributes ai
JOIN    items i
ON      i.id = ai.item_id
WHERE   a.attribute_value = 'SomeValue'
        AND a.attribute_name = 'SomeAttribute'