Mysql查询性能很慢 [英] Mysql Query performance very slow

问题描述

以下查询耗时超过 8 分钟，处理了 900 000 行.它非常慢并且会影响我的产品.我无法确定为什么查询变慢，所有索引都设置正常.

The below query was taking more than 8 min and 900 000 rows processed. it is very slow and affect my product. I can't identify why the query getting slow, all index are set fine.

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

表一:

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       |       |
+---------------------+--------------+------+-----+------------+-------+

推荐答案

OR效率低下，看能不能避免.

OR is inefficient, see if you can avoid it.

LIKE 中的前导通配符效率低下.看看 FULLTEXT 索引是否适合您.

Leading wildcards in LIKE are inefficient. See if a FULLTEXT index would work for you.

使用正确的COLLATION，您无需同时测试大小写.您也可以避免使用 BINARY.在这两种情况下，您都可以使用索引.(你有什么索引?)

With a proper COLLATION, you don't need to test both upper and lower case. Also you can avoid use of BINARY. In both cases, you might be able to use an index. (What indexes do you have?)

尝试改变

WHERE ( ( SELECT ... ) = '0' )

到

WHERE ( NOT EXISTS ( SELECT ... ) )

(SELECT 需要一些修改.)

(请去掉一些多余的括号，难以阅读.)

(Please get rid of some of the redundant parens; it is hard to read.)

(请使用SHOW CREATE TABLE；它比DESCRIBE更具描述性.)

(Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.)

这篇关于Mysql查询性能很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！