主页 / dba / 问题 / 189593
Accepted
Déjà vu
Asked: 2017-10-29 22:38:43 +0800 CST

实现一个查询，两种策略

  • 772

表尚未创建，但为了简化，有一个groups和一个items表。一个组包含一个项目 ID（和一个组 ID）。并且多个组可能包含相同的项目 ID。

例如

CREATE TABLE groups (
          gid int,
          iid int,
          primary key(gid,iid)
);
CREATE TABLE items (
          iid int primary key,
          label char(5)
);

并在其中放入一些数据，以帮助可视化即将出现的问题

insert into items values (1,"pen"),(2,"gum"),(3,"cat"),(4,"dog"),
                         (5,"hug"),(6,"art"),(7,"fun");

insert into groups values(1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,1),
                         (3,4),(4,2),(4,3),(4,5),(4,6),(4,7);

检索多个组（例如组 1、2、3、4）的项目使用 aDISTINCT删除重复项

(1) SELECT DISTINCT i.iid,i.label FROM groups g JOIN items i ON g.iid=i.iid 
    WHERE g.gid in (1,2,3,4) ORDER BY i.iid;

但是由于表最终将包含更多数据，使用子查询的查询会更有效吗

(2) SELECT i.iid,i.label FROM items i 
    JOIN (SELECT DISTINCT iid FROM groups WHERE gid IN (1,2,3,4)) AS s
    ON i.iid=s.iid ORDER BY i.iid;

因为distinct只适用于项目 ID，尽管使用子查询的开销？

或者@hypercube 建议的这个（见评论）：

(3) SELECT i.iid,i.label FROM items AS i 
    WHERE EXISTS
       (SELECT * FROM groups AS g WHERE g.gid IN (1,2,3,4) AND i.iid=g.iid)
    ORDER BY i.iid

供参考，最终

  • items 表将有50~100k行
  • 一组可能有5~10k个项目
  • 单次查询选择的groups id在5~20范围内
  • 一个项目属于 2 个选定组的概率：50%
  • 一个项目属于 3 个选定组的概率：30%
  • 一个项目属于 4 个选定组的概率：10%
mysql performance

1 个回答

  1. Best Answer

    (1)简单明了。这可能是最有效的。

    (2) 是一个共同的加速。但是当非索引部分（就i.label你的情况而言）很大和/或表中的键 ( i.id) 不是PRIMARY KEY. 因此，如果您的示例从实际代码中淡化，则 (2)可能优于 (1)。

    (3)不太可能有效率，因为它需要达到items50~100K次。

    附带问题：

    CHAR除非列确实是固定长度，否则不要使用；相反，使用VARCHAR.

    我假设您正在使用 InnoDB。（MyISAM，因为对 的处理方式不同PRIMARY KEY，效率会比较低。）

    如果groups是多对多映射表，请参阅我的提示。它可能会帮助 (3) 一些。

    • 1

相关问题