mysql - 如何优化具有 2 个不同内部连接的 Mysql 查询？ (创新数据库)

Question

我有一个使用 InnoDB 存储引擎的查询。

我想优化它。执行起来需要太多时间。我的数据库中有 500 万条数据。现在执行需要 250 秒。

INSERT INTO dynamicgroups (adressid) 

    SELECT SQL_NO_CACHE DISTINCT(addressid) FROM (
        SELECT cluster_0.addressid FROM (
            SELECT DISTINCT addressid FROM (
                SELECT group_all.addressid FROM (
                    SELECT g.addressid FROM table2.635_emadresmgroups g 
                        INNER JOIN table2.emaildata f_0
                               ON f_0.addressid = g.addressid
                        WHERE  (f_0.birthday > date(DATE_SUB(NOW(),INTERVAL 18 MONTH))
                            AND f_0.birthday < CURDATE() )
                ) group_all
            ) AS groups

        ) AS cluster_0

        INNER JOIN(
            SELECT DISTINCT addressid FROM (
                SELECT group_all.addressid FROM (
                    SELECT g.addressid FROM table2.635_emadresmgroups g 
                        INNER JOIN table2.emaildata f_0
                               ON f_0.addressid = g.addressid
                        WHERE  (marriage_date = ''
                             OR marriage_date = '1900-01-01'
                             OR marriage_date = '0000-00-00' )
                ) group_all
            ) AS groups
        ) AS cluster_1 ON cluster_1.addressid = cluster_0.addressid

        INNER JOIN(
            SELECT DISTINCT addressid FROM (
                SELECT group_all.addressid FROM (
                    SELECT g.addressid FROM table2.635_emadresmgroups g 
                        INNER JOIN table2.emaildata f_0
                                ON f_0.addressid = g.addressid
                        WHERE  (f_0.city = '34' )
                ) group_all
            ) AS groups
        ) AS cluster_2 ON cluster_2.addressid = cluster_1.addressid 
    ) AS t

Answer 1

您的查询似乎都是此查询的变体:

SELECT g.addressid
FROM table2.635_emadresmgroups g INNER JOIN
     table2.emaildata f_0
     ON f_0.addressid = g.addressid
WHERE  (f_0.birthday > date(DATE_SUB(NOW(),INTERVAL 18 MONTH)) AND f_0.birthday < CURDATE() )

我建议使用 group by 和 having 来解决这个问题:

SELECT g.addressid
FROM table2.635_emadresmgroups g INNER JOIN
     table2.emaildata f_0
     ON f_0.addressid = g.addressid
GROUP BY g.addressid
HAVING SUM(f_0.birthday > date(DATE_SUB(NOW(), INTERVAL 18 MONTH)) AND f_0.birthday < CURDATE() ) > 0 AND
       SUM(marriage_date = '' OR marriage_date = '1900-01-01'  OR marriage_date = '0000-00-00' ) > 0 AND
       SUM(f_0.city = '34' ) > 0;

根据数据量，在 group by 之前进行过滤也有帮助:

SELECT g.addressid
FROM table2.635_emadresmgroups g INNER JOIN
     table2.emaildata f_0
     ON f_0.addressid = g.addressid
WHERE (f_0.birthday > date(DATE_SUB(NOW(), INTERVAL 18 MONTH)) AND f_0.birthday < CURDATE() ) OR
      (marriage_date = ''  OR marriage_date = '1900-01-01' OR marriage_date = '0000-00-00' ) OR
      (f_0.city = '34' )
GROUP BY g.addressid
HAVING SUM(f_0.birthday > date(DATE_SUB(NOW(), INTERVAL 18 MONTH)) AND f_0.birthday < CURDATE() ) > 0 AND
       SUM(marriage_date = '' OR marriage_date = '1900-01-01'  OR marriage_date = '0000-00-00' ) > 0 AND
       SUM(f_0.city = '34' ) > 0;