自加入重复检测
我有一些麻烦与执行自连接在一个子查询,它需要比我想象它应该和我有一些认识问题,为什么更多的时间查询。自加入重复检测
的问题如下,业主可以有项目,但某些项目可能会出现两次属于不同的所有者,从每个业主,我们可能得到关于项目的信息稍有不同,或某些字段可能为空。
这是我的数据库的简单版本,它不包含FK和索引,只有当他们出现存在IdOwner,IdItem和IdCategry。
业主:
+----------------+---------------+------+-----+
| Field | Type | Null | Key |
+----------------+---------------+------+-----+
| IdOwner | bigint(20) | NO | PRI |
| IdPlace | int(10) | NO | |
| SomeDate | datetime | YES | |
+----------------+---------------+------+-----+
项目:
+----------------+---------------+------+-----+
| Field | Type | Null | Key |
+----------------+---------------+------+-----+
| IdItem | bigint(20) | NO | PRI |
| IdOwner | bigint(20) | NO | MUL |
| IdCategory | int(10) | NO | |
| DupValue1 | varchar() | YES | |
.
.
.
| DupValueN | varchar() | YES | |
+----------------+---------------+------+-----+
国家:
+----------------+---------------+------+-----+
| Field | Type | Null | Key |
+----------------+---------------+------+-----+
| IdOwner | bigint(20) | NO | PRI |
| Country | Varchar() | NO | PRI |
+----------------+---------------+------+-----+
DupValues 1到N是我发现的列具有最大概率是当物品重复时也是如此。
这是我目前正与查询的简化版本:
SELECT subquery1.IdItem, subquery2.IdItem FROM
(SELECT i1.IdCategory, i1.IdOwner, i1.IdItem, i1.DupValue1, o1.IdSite, o1.SomeDate, COUNTRY.country
FROM ITEMS i1
LEFT JOIN OWNER o1 ON o1.IdOwner=i1.IdOwner
LEFT JOIN COUNTRY ON i1.IdOwner=COUNTRY.IdOwner
WHERE i1.IdOwner>9000000)
as subquery1
INNER JOIN
(SELECT i2.IdCategory, i2.IdOwner, i2.IdItem, i2.DupValue1, o2.IdSite, o2.SomeDate, COUNTRY.country
FROM ITEMS i2
LEFT JOIN COUNTRY COUNTRY ON i2.IdOwner=COUNTRY.IdOwner
LEFT JOIN OWNER o2 ON o2.IdOwner=i2.IdOwner
WHERE i2.IdOwner>9000000)
as subquery2
ON subquery1.IdItem<subquery2.IdItem
AND subquery1.IdCategory=subquery2.IdCategory
AND subquery1.IdSite!=subquery2.IdSite AND subquery1.country=subquery2.country
AND DATE(subquery1.SomeDate)=DATE(subquery2.SomeDate)
AND (subquery1.DupValue1=subquery2.DupValue1 OR subquery1.DupValue1 IS NULL OR subquery2.DupValue1 IS NULL)
还有一些更SupValue都具有相同的格式。
WHERE子句用于限制拥有者数量,因为我仍然在测试查询,当WHERE子句已经到位时,它将拥有者限制在约700,000行,并且拥有该行数的行数约为30分钟过程。
当我使用的查询说明我得到这个:
+------+-------------+---------+--------+----------------------------------------+-------------+---------+------------------------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+--------+----------------------------------------+-------------+---------+------------------------+-------+------------------------------------+
| 1 | SIMPLE | i1 | range | PRIMARY,UnivocID,dg_owner,dg_category | UnivocID | 8 | NULL | 19056 | Using index condition |
| 1 | SIMPLE | o1 | eq_ref | PRIMARY | PRIMARY | 8 | i1.IdTender | 1 | |
| 1 | SIMPLE | country | ref | PRIMARY | PRIMARY | 8 | i1.IdTender | 1 | Using index |
| 1 | SIMPLE | i2 | ref | PRIMARY,UnivocID,dg_owner,dg_category | dg_category | 4 | i1.IdMolecule | 657 | Using index condition; Using where |
| 1 | SIMPLE | o2 | eq_ref | PRIMARY | PRIMARY | 8 | i2.IdTender | 1 | Using where |
| 1 | SIMPLE | country | ref | PRIMARY | PRIMARY | 8 | i2.IdTender | 1 | Using index |
+------+-------------+---------+--------+----------------------------------------+-------------+---------+------------------------+-------+------------------------------------+
MariaDB的版本:10.1
我的2个问题:
¿是否subquery2
为subquery1
每一行执行和这是什么原因造成的执行时间是长还是这ON
条款,有过错的性质是什么?
¿可以查询被提高,也许开沟赞成JOIN
或其他运营商?
我无法测试这是因为没有表格布局或测试数据(并且您也参考了连接子句中名为SubmissionDate的列,但该字段未从子查询中返回),但应该避免以下情况使用子查询。希望这将能够更好地使用索引: -
SELECT subquery1.IdItem, subquery2.IdItem
FROM ITEMS i1
INNER JOIN ITEMS i2
ON i1.IdItem < i1.IdItem AND i1.IdCategory = i1.IdCategory AND (i1.DupValue1 = i2.DupValue1 OR i1.DupValue1 IS NULL OR i2.DupValue1 IS NULL)
INNER JOIN OWNER o1 ON o1.IdOwner = i1.IdOwner
INNER JOIN COUNTRY c1 ON i1.IdOwner = c1.IdOwner
INNER JOIN OWNER o2 ON o2.IdOwner = i2.IdOwner AND o1.IdSite != o2.IdSite
INNER JOIN COUNTRY c2 ON i2.IdOwner = c2.IdOwner AND c1.country = c2.country
WHERE i1.IdOwner > 9000000
AND i2.IdOwner > 9000000
这不起作用,它需要更多的时间,我认为这是因为WHERE子句被应用在连接的末尾,所以它只是修剪输出。 “,并且您还可以参考连接子句中名为SubmissionDate的列”。 我的不好,它已经被修复了。 –
如果您可以发布表格布局和一些示例数据,我可以尝试。但是你的当前查询将会受到影响,因为MySQL可能很难从子查询中提取任何索引来进行连接。如果您仅从每个子查询返回一小部分数据,但可能不会包含大量数据,则速度会更快。 – Kickstart
调查或处理的DUP另一种技术是将行从每个表济济一堂到一个表,然后做GROUP BY
该表。
CREATE TEMPORARY TABLE t
(SELECT stuff from one table or set of tables)
UNION ALL
(SELECT stuff from the other table or tables)
;
SELECT * FROM t
GROUP BY IdOwner, IdSite, country
;
如果需要,添加一个额外的列 “东西” 来区分来源:
SELECT 1 AS source, ...
一种表现不佳的原因:
FROM (subquery1)
JOIN (subquery2) ON ...
没有索引(直到至少5.6),以执行ON
。所以子查询结果被完全扫描。即使5.6,索引的创建也有一些开销。
另一个提示,重新:AND DATE(subquery1.SomeDate)=DATE(subquery2.SomeDate)
:构建子查询时计算DATE(SomeDate)
- 这使它成为一次性过程,而不是执行子表扫描时的重复过程。
您是否需要子查询,或者是否可以直接在没有子查询的情况下完成(如果在完整查询中在其中具有聚合函数,则可能需要子查询)。 – Kickstart
@Kickstart我需要子查询,因为我需要从3个表中的数据到1,然后我需要对结果表进行操作来检测重复项,做内部连接是我知道这样做的唯一方法。 –
可能的问题是您正在使用LEFT OUTER JOINs,因此一些返回的字段可能为NULL。而NULL不等于NULL(但你加入基于国家字段是相等的)。如果你的子查询的效果超过了LEFT OUTER JOINs,但结果被处理的方式将意味着结果可能来自INNER JOINs – Kickstart