mysql - 按差异筛选的绩效分组

Question

我有一张这样的 table

CREATE TABLE "items" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "id_ur" varchar(255) NOT NULL,
  "window_key" varchar(255) DEFAULT NULL,
  PRIMARY KEY ("id"),
  KEY "idx_window_key" ("window_key") USING BTREE,
  KEY "idx_id_ur" ("id_ur") USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

此表包含 19 000 00 行。

我需要选择与不同的 window_key 共享 id_ur 字段的所有记录。例如，如果我有如下记录:

id,id_ur,window_key
1,"123","ABC"
2,"124","DEF"
3,"123","ABD"
4,"124","DEF"

我需要返回“123”，而不是“124”。

我正在寻找一种在 MySQL Community Server 版本 5.7.22 中执行此操作的高效方法。

我尝试了以下方法:

select c1.id_ur
from items c1
inner join items c2
on c1.id_ur = c2.id_ur
where c1.window_key <> c2.window_key;

但它并不是真正的高效。我尝试使用 group by 子句来表达它，但我不知道如何表达在特定列上没有不同的行的分组。

我在 id_ur 和 window_key 字段上都有索引。我不确定在两个字段上添加索引是否有用。

我正在寻找一个合适的查询来获取这些记录。

---

多亏了一些帮助，我才能够找到更高效的解决方案。

这是基准测试的结果:

select distinct c1.id_ur
from item c1, item c2
where c1.id_ur = c2.id_ur
and c1.window_key <> c2.window_key
-- 1483 secs

select c1.id_ur
from item c1
inner item c2
on c1.id_ur = c2.id_ur
where c1.window_key <> c2.window_key;
 -- 675 secs

select distinct c1.id_ur
from item c1
group by c1.id_ur
having count(distinct c1.window_key) > 1
-- 170 secs

SELECT dt.id_ur 
FROM 
(
  SELECT DISTINCT c1.id_ur, c1.window_key 
  FROM gbmlive.canonical AS c1
) AS dt 
GROUP BY dt.id_ur 
HAVING COUNT(*) > 1
-- 376 secs

所以最快的解决方案是使用不同计数的分组。

Answer 1

同时使用 group by 和 having :

select id_user
from items
group by id_user
having count(distinct window_key) > 1