Mysql查询性能很慢

Question

以下查询花费了超过 8 分钟并处理了 900 000 行。它非常慢并且影响我的产品。我无法确定为什么查询变慢，所有索引都设置得很好。

explain SELECT 
    COUNT(DISTINCT (cinfo.CONTACT_ID))
FROM
    cinfo
        INNER JOIN
    LTocMapping ON cinfo.CONTACT_ID = LTocMapping.CONTACT_ID
WHERE
    (((((((((cinfo.COUNTRY LIKE '%Panama%')
        OR (cinfo.COUNTRY LIKE '%PANAMA%'))
        AND (((cinfo.CONTACT_EMAIL NOT LIKE '%test%')
        AND (cinfo.CONTACT_EMAIL NOT LIKE '%engine%'))
        OR (cinfo.CONTACT_EMAIL IS NULL)))
        AND ((SELECT 
            (GROUP_CONCAT(Temp.LIST_ID
                    ORDER BY Temp.LIST_ID) REGEXP ('.*,*221715000514445053,*.*$'))
            FROM
                LTocMapping Temp
            WHERE
                ((LTocMapping.CONTACT_ID = Temp.CONTACT_ID)
                    AND (((Temp.MAPPING_ID >= 221715000000000000)
                    AND (Temp.MAPPING_ID <= 221715999999999999))
                    OR ((Temp.MAPPING_ID >= 0)
                    AND (Temp.MAPPING_ID <= 999999999999))))
            GROUP BY Temp.CONTACT_ID) = '0'))
        AND ((SELECT 
            (GROUP_CONCAT(Temp.LIST_ID
                    ORDER BY Temp.LIST_ID) REGEXP ('.*,*221715000520574130,*.*$'))
            FROM
                LTocMapping Temp
            WHERE
                ((LTocMapping.CONTACT_ID = Temp.CONTACT_ID)
                    AND (((Temp.MAPPING_ID >= 221715000000000000)
                    AND (Temp.MAPPING_ID <= 221715999999999999))
                    OR ((Temp.MAPPING_ID >= 0)
                    AND (Temp.MAPPING_ID <= 999999999999))))
            GROUP BY Temp.CONTACT_ID) = '0'))
        AND (LTocMapping.LIST_ID IN (221715000520574130 , 221715000201569885)))
        AND (LTocMapping.STATUS = BINARY 'subscribed'))
        AND (((cinfo.CONTACT_STATUS = BINARY 'active')
        OR (cinfo.CONTACT_STATUS = BINARY 'softbounce'))
        AND (LTocMapping.STATUS = BINARY 'subscribed')))
        AND (((cinfo.CONTACT_ID >= 221715000000000000)
        AND (cinfo.CONTACT_ID <= 221715999999999999))
        OR ((cinfo.CONTACT_ID >= 0)
        AND (cinfo.CONTACT_ID <= 999999999999))))

答案是

下表 FYR

表1:

mysql> desc cinfo;
+------------------------+--------------+------+-----+-----------+-------+
| Field                  | Type         | Null | Key | Default   | Extra |
+------------------------+--------------+------+-----+-----------+-------+
| CONTACT_ID             | bigint(19)   | NO   | PRI | NULL      |       |
| CONTACT_EMAIL          | varchar(100) | NO   | MUL | NULL      |       |
| TITLE                  | varchar(20)  | YES  |     | NULL      |       |
| FIRSTNAME              | varchar(100) | YES  |     | NULL      |       |
| LASTNAME               | varchar(50)  | YES  |     | NULL      |       |     |
| ADDED_BY               | varchar(20)  | YES  |     | NULL      |       |
| ADDED_TIME             | bigint(19)   | NO   |     | NULL      |       |
| LAST_UPDATED_TIME      | bigint(19)   | NO   |     | NULL      |       |
+------------------------+--------------+------+-----+-----------+-------+

表2:

 mysql> desc LTocMapping;
+---------------------+--------------+------+-----+------------+-------+
| Field               | Type         | Null | Key | Default    | Extra |
+---------------------+--------------+------+-----+------------+-------+
| MAPPING_ID          | bigint(19)   | NO   | PRI | NULL       |       |
| CONTACT_ID          | bigint(19)   | NO   | MUL | NULL       |       |
| LIST_ID             | bigint(19)   | NO   | MUL | NULL       |       |
| STATUS              | varchar(100) | YES  |     | subscribed |       |
| MAPPING_STATUS      | varchar(20)  | YES  |     | connected  |       |
| MAPPING_TIME        | bigint(19)   | YES  |     | NULL       |       |
+---------------------+--------------+------+-----+------------+-------+

Answer 1

据我所知，您的子查询是瓶颈:

• 对于第一个子查询，您使用LTocMapping.CONTACT_ID
• 对于第二个子查询，您也使用 LTocMapping.CONTACT_ID。

这些引用(对外部查询的值)导致这些内部查询成为相关子查询(也称为相关子查询)。这意味着:对于您要在其中一个外部表 (~970000) 上获取的每一行，您将在另一个表上触发 2 个额外的查询。

因此，您正在执行 180 万个(看起来也不是微不足道的)查询。

大多数时候，相关子查询可以用适当的联接代替。但这取决于用例。使用不同的别名时，您还可以两次连接同一个表。

但是要概述一些连接选项，您需要解释为什么导致条件 group_concat(....) = '0' 的子查询很重要 - 或者也许更好，您是什么想要实现。

(ps:您还可以看到，explain 将它们概述为依赖子查询)