我的sql-尝试优化查询返回更多行

v1uwarro 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(355)

我有一个查询，我继承了我以前的同事，但我需要优化它。
此查询返回72行。

SELECT  id, contract_no, customer, address, cm_mac, aps
    FROM  
    (
        SELECT  *
            from  new_installed_devices
            where  insert4date >='2018-10-28'
              AND  insert4date <='2018-10-28'
              AND  install_mark<2
    ) as d1
    left join  
    (
        SELECT  *
            from  
            (
                SELECT  contract_no AS c_no, cm_mac AS c_mc, MIN(tstamp) as time2,
                        sum(1) as aps
                    from  devices_change
                    where  contract_no in (
                        SELECT  distinct(contract_no)
                            from  devices_change
                            where  tstamp >= '2018-10-28 06:59:59'
                              AND  tstamp <= '2018-10-29 07:00:00'
                          )
                    group by  contract_no, cm_mac 
            ) as mtmbl
            where  mtmbl.time2 >= '2018-10-28 06:59:59'
              and  mtmbl.time2 <= '2018-10-29 07:00:00' 
    ) as tmp  ON d1.contract_no=tmp.c_no
    where  aps>0
    group by  contract_no, customer, address, cm_mac;

执行需要20秒。我重新编写它，试图优化它，但在这种情况下，我有75行（3个额外的行返回），但结果是在2秒钟内提出。
我这样做过（唯一的区别是在一个子查询中）：

SELECT  id, contract_no, customer, address, cm_mac, aps
    FROM  
    (
        SELECT  *
            from  new_installed_devices
            where  insert4date >='2018-10-28'
              AND  insert4date <='2018-10-28'
              AND  install_mark<2
    ) as d1
    left join  
    (
        SELECT  *
            from  
            (
                SELECT distinct
                        (contract_no) AS c_no,
                        cm_mac AS c_mc, MIN(tstamp) as time2,
                        sum(1) as aps
                    from  devices_change
                    where  tstamp >= '2018-10-28 06:59:59'
                      AND  tstamp <= '2018-10-29 07:00:00'
                    group by  contract_no, cm_mac 
            ) as mtmbl
            where  mtmbl.time2 >= '2018-10-28 06:59:59'
              and  mtmbl.time2 <= '2018-10-29 07:00:00' 
    ) as tmp  ON d1.contract_no=tmp.c_no
    where  aps>0
    group by  contract_no, customer, address, cm_mac;

就像你们看到的，在我的例子中我并没有改变很多，但我仍然得到了更多的行，这应该是结果。有人能告诉我为什么我的第二个查询没有返回完全正确的结果吗。我尝试了很多优化的方法，但都没有成功。谢谢！！！

mysql subquery select query-optimization Distinct

来源：https://stackoverflow.com/questions/53103345/my-sql-trying-to-optimize-query-returns-more-rows

2条答案

按热度按时间

hm2xizp91#

不要使用 SELECT * 当你不需要所有的列。看起来像 contract_no 是唯一需要的列 dl ，因此 new_installed_devices .
有什么理由测试吗 insert4date 以那种奇怪的方式争取平等？
推荐 INDEX(insert4date, install_mark, dl) （按顺序）
尽量避免构造 IN ( SELECT ... ) . 通常最好使用 EXISTS 或者 LEFT JOIN .
别说了 DISTINCT(contract_no), ... -- DISTINCT 不是一个函数；它的效果适用于整个表达式集。摆脱 DISTINCT 自从 GROUP BY 有这种效果。
推荐 INDEX(contract_no, cm_max, tstamp) （按顺序）
对mtmbl.time2的测试是多余的，因为'min（tstamp）已被限制在该（1天+2秒）时间范围内。
请提供 SHOW CREATE TABLE .

赞(0）回复(0）举报 2021-06-18

bf1o4zei2#

可以将from子句中的第一个子查询替换为对表的直接引用 new_installed_devices ，where子句中有一些条件。在旧版本中，mysql不能很好地处理子查询，因此请尽量避免在from子句中使用它们（特别是当您有超过1或2个子查询时）。
的范围条件 mtmbl.time2 可以折叠到子查询的having子句中，以确保尽可能快地筛选该数据，而无需使用该子查询创建大型临时表。
您能提供这些表的show create table和查询的解释吗？这会有帮助的。
当猜测mysql将在这里选择的顺序时，您可以尝试添加这些索引并运行下面的查询，看看是否效果更好。我将上述建议应用于下面的查询（希望我对列起源的猜测是正确的，否则请相应地修复所有问题）：

ALTER TABLE `devices_change` ADD INDEX `devices_change_idx_no_mac_tstamp` (`contract_no`,`cm_mac`,`tstamp`);
ALTER TABLE `devices_change` ADD INDEX `devices_change_idx_tstamp_no` (`tstamp`,`contract_no`);
ALTER TABLE `new_installed_devices` ADD INDEX `new_installed_device_idx_no_insert4date` (`contract_no`,`insert4date`);

查询：

SELECT
        new_installed_devices.id,
        new_installed_devices.contract_no,
        new_installed_devices.customer,
        new_installed_devices.address,
        new_installed_devices.cm_mac,
        new_installed_devices.aps 
    FROM
        new_installed_devices AS d1 
    LEFT JOIN
        (
            SELECT
                * 
            FROM
                (SELECT
                    devices_change.contract_no AS c_no,
                    devices_change.cm_mac AS c_mc,
                    MIN(devices_change.tstamp) AS time2,
                    sum(1) AS aps 
                FROM
                    devices_change 
                WHERE
                    devices_change.contract_no IN (
                        SELECT
                            DISTINCT (devices_change.contract_no) 
                        FROM
                            devices_change 
                        WHERE
                            devices_change.tstamp >= '2018-10-28 06:59:59' 
                            AND devices_change.tstamp <= '2018-10-29 07:00:00'
                    ) 
                GROUP BY
                    devices_change.contract_no,
                    devices_change.cm_mac 
                HAVING
                    devices_change.time2 >= '2018-10-28 06:59:59' 
                    AND devices_change.time2 <= '2018-10-29 07:00:00' 
                ORDER BY
                    NULL) AS mtmbl) AS tmp 
                    ON d1.contract_no = tmp.c_no 
            WHERE
                aps > 0 
                AND d1.insert4date >= '2018-10-28' 
                AND d1.insert4date <= '2018-10-28' 
                AND d1.install_mark < 2 
            GROUP BY
                new_installed_devices.contract_no,
                new_installed_devices.customer,
                new_installed_devices.address,
                new_installed_devices.cm_mac 
            ORDER BY
                NULL

赞(0）回复(0）举报 2021-06-18

我来回答

我的sql-尝试优化查询返回更多行

2条答案

相关问题

热门标签

最新问答