使用IN子查询改善MySQL查询 [英] Improve MySQL Query with IN Subquery

问题描述

我有一张桌子items和一张桌子item_attributes.

I hava a table items and a table item_attributes.

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

For simplicity let's say my table item has a column id and a column name. Of cource there is a index on the id column.

item_attributes表具有列id，item_id，attribute_name和attribute_value以及索引为attrubute_name

the item_attributes table has the columns id, item_id, attribute_name and attribute_value and an index ON attrubute_name

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

Now I want to query all items with a specific attribut without using a join.

我通过以下查询进行此操作:

I do this with the following query:

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'
)

SubQuery本身运行速度很快.

the SubQuery itself runs fast.

如果我先执行查询本身，然后将结果用于IN查询

If I execute the query itself first and use the result for an IN query

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

它也很快.

但是，合并查询的速度非常非常慢(> 2秒). 如果我调查查询计划，我会明白为什么:MySQL对items表执行全表扫描，而不是先执行子查询并使用结果进行索引查询.

However, combined the query is very, very slow (>2 secs.) If I investigate the query plan I see why: MySQL does a full table scan on the items table instead of executing the subquery first and using the result for an index query.

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行).

Is there a way to optimize this query? I know that the subquery will always return only a small resultset (<100 rows).

推荐答案

MySQL无法在IN子句中切换引导表和从动表.这将在6.0 中进行了纠正.

MySQL cannot switch the leading and the driven table in the IN clause. This is going to be corrected in 6.0.

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

For now, you can rewrite it like this (requires a 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)上创建唯一索引，在这种情况下，可以使用普通联接:

Since you are using the EAV model you may want to make a unique index on (attribute_name, item_id) in which case you can use a plain join:

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'

这篇关于使用IN子查询改善MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！