MySQL OR 和 ISNULL 性能不佳

Question

我对一些奇怪的 mysql 性能行为感到非常惊讶。我的以下查询大约需要 3 小时才能运行:

UPDATE ips_invoice AS f SET ips_locality_id = (
        SELECT ips_locality_id 
        FROM ips_user_unit_locality AS uul 
        JOIN ips_user AS u ON u.id = uul.ips_user_id 
        WHERE 
            (u.id = f.ips_user_id OR u.ips_user_id_holder = f.ips_user_id) AND 
            uul.date <= f.date 

        ORDER BY `date` DESC 
        LIMIT 1 
) 
WHERE f.ips_locality_id IS NULL;

我也尝试了以下方法，但得到了相同的性能结果:

UPDATE ips_invoice AS f SET ips_locality_id = (
        SELECT ips_locality_id 
        FROM ips_user_unit_locality AS uul 
        JOIN ips_user AS u ON u.id = uul.ips_user_id 
        WHERE 
            IFNULL(u.ips_user_id_holder, u.id) = f.ips_user_id 
            AND 
            uul.date <= f.date 

        ORDER BY `date` DESC 
        LIMIT 1 
) 
WHERE f.ips_locality_id IS NULL;

逻辑是:如果“ips_user_id_holder”列不为空，我应该使用它，如果不是，我应该使用“id”列。

如果我将查询分成两个查询，每个查询需要 15 秒来运行:

     UPDATE ips_invoice AS f SET ips_locality_id = (
                SELECT ips_locality_id 
                FROM ips_user_unit_locality AS uul 
                JOIN ips_user AS u ON u.id = uul.ips_user_id 
                WHERE 
                    u.ips_user_id_holder = f.ips_user_id 
                    AND 
                    uul.date <= f.date 

                ORDER BY `date` DESC 
                LIMIT 1 
        ) 
        WHERE f.ips_locality_id IS NULL;

UPDATE ips_invoice AS f SET ips_locality_id = (
                SELECT ips_locality_id 
                FROM ips_user_unit_locality AS uul 
                JOIN ips_user AS u ON u.id = uul.ips_user_id 
                WHERE 
                    u.id = f.ips_user_id 
                    AND 
                    uul.date <= f.date 

                ORDER BY `date` DESC 
                LIMIT 1 
        ) 
        WHERE f.ips_locality_id IS NULL;

这不是我第一次遇到 Mysql“OR”或“null 检查”的问题相对简单的查询( Why this mysql query (with is null check) is so slower than this other one? )。

ips_invoice 表大约有 400.000 条记录，ips_user_unit_locality 大约有 100.000 条记录，ips_user 大约有 35.000 条记录。

我在 Ubuntu Amazon EC2 实例中运行 MySQL 5.5.49。

那么，第一个和第二个查询出了什么问题？造成显着性能差异的原因是什么？

Answer 1

第一个和第二个查询没有任何“错误”。但是，当您在 join 条件(或等效的相关子查询条件)中使用 or 时，引擎通常无法使用索引。

这使得一切变得非常慢。

您似乎至少了解一种解决方法，因此我不会提出其他任何建议。

编辑:

我会注意到您的查询并不完全按照您在文本中指定的方式执行。它获取两个用户 ID 中任意一个的最新日期。您似乎想要优先考虑 id。如果是这样，这就是您想要的查询:

UPDATE ips_invoice f
    SET ips_locality_id =
        COALESCE( (SELECT ips_locality_id 
                   FROM ips_user_unit_locality uul JOIN
                        ips_user u
                        ON u.id = uul.ips_user_id 
                   WHERE u.ips_user_id_holder, f.ips_user_id AND
                         uul.date <= f.date 
                   ORDER BY uul.date DESC
                   LIMIT 1
                  ),
                  (SELECT ips_locality_id 
                   FROM ips_user_unit_locality uul
                   WHERE uul.ips_user_id = f.ips_user_id AND
                         uul.date <= f.date 
                   ORDER BY uul.date DESC
                   LIMIT 1
                  )
                )
WHERE f.ips_locality_id IS NULL;