为什么这个mysql查询（用null检查）是这么慢这个其他吗？ [英] Why this mysql query (with is null check) is so slower than this other one?

问题描述

我对IS NULLMySQL检查有疑问。我有这2个查询。第一个运行约300秒。第二个运行时间少于1秒！

I getting in doubt with "IS NULL" MySQL check. I have this 2 queries. The first one runs in about 300 seconds. The second one run less then 1 second!

慢查询：

SELECT count(distinct(u.id))
FROM ips_usuario AS u 
JOIN ips_fatura AS f
    ON ((u.id = f.ips_usuario_id) OR
       (u.ips_usuario_id_titular IS NOT NULL AND
        u.ips_usuario_id_titular = f.ips_usuario_id));

快速查询：

SELECT count(distinct(u.id))
FROM ips_usuario AS u 
JOIN ips_fatura AS f
    ON ((u.id = f.ips_usuario_id) OR
       (u.ips_usuario_id_titular = f.ips_usuario_id));

所有连接条件都使用外键索引列。表ips_usuario有约20.000条记录，表ips_fatura有约500.000条记录。

All join conditions use foreign keys indexed columns. The table ips_usuario have about 20.000 records and the table ips_fatura have about 500.000 records.

推荐答案

我很惊讶，我建议用 exists 替换它们：

I am surprised that either is fast. I would suggest replacing them with exists:

SELECT COUNT(*)
FROM ips_usuario u  
WHERE EXISTS (SELECT 1 FROM ips_fatura f WHERE u.id = f.ips_usuario_id) OR
      EXISTS (SELECT 1 FROM ips_fatura f WHERE u.ips_usuario_id_titular = f.ips_usuario_id);

第二个：

SELECT COUNT(*)
FROM ips_usuario u  
WHERE EXISTS (SELECT 1 FROM ips_fatura f WHERE u.id = f.ips_usuario_id) OR
      (u.ips_usuario_id_titular IS NOT NULL AND
       EXISTS (SELECT 1 FROM ips_fatura f WHERE u.ips_usuario_id_titular = f.ips_usuario_id)
      )

对于这两个，您需要两个索引： ips_fatura（ips_usuario_id）和 ips_fatura（ips_usuario_id_titular）。您可以检查说明以确保 EXISTS 正在使用索引。如果不是，新版本的MySQL使用 IN 的索引：

For both these, you want two indexes: ips_fatura(ips_usuario_id) and ips_fatura(ips_usuario_id_titular). You can check the explain to be sure that EXISTS is using the index. If not, the newer releases of MySQL use indexes for IN:

SELECT COUNT(*)
FROM ips_usuario u  
WHERE u.id IN (SELECT f.ips_usuario_id FROM ips_fatura f) OR
      u.ips_usuario_id_titular IN (SELECT f.ips_usuario_id FROM ips_fatura f);

无论如何（ EXISTS 或 IN ）的目标是做一个半连接。也就是说，仅仅使用匹配而不是所有匹配的第一行。这是一个重要的效率，因为它允许查询避免重复删除。

In either case (EXISTS or IN) the goal is to do a "semi-join". That is, to only fine the first row with a match rather than all matches. This is an important efficiency, because it allows the query to avoid duplication removal.

我会推测，问题是优化 / code> - 通常这会导致低效的 JOIN 算法。但是，也许MySQL在你的第一种情况下是聪明的。但是将 IS NULL 添加到外部表中会使它失效。

I would speculate that the issue is the optimization of the or -- usually this results in inefficient JOIN algorithms. However, perhaps MySQL is smart in your first case. But the addition of the IS NULL to the outer table throws it off.

这篇关于为什么这个mysql查询（用null检查）是这么慢这个其他吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！