业务SQL优化

最新推荐文章于 2024-03-29 21:22:47 发布

清，纯一色

最新推荐文章于 2024-03-29 21:22:47 发布

阅读量124

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/gczheng/p/9323410.html

本文介绍了一个具体的SQL查询优化案例，通过调整SQL语句结构及利用主键进行连接操作，将原本耗时80多秒的查询减少到仅2秒。优化主要涉及减少冗余字段、更改日期格式的处理方式，并使用更高效的主键连接。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1，个人开户报表统计

优化前语句,执行时间80多秒

SELECT
    a.DA AS f_da,
    a.account_name AS f_account_name,
    a.sex AS f_sex,
    a.nick_name AS f_nickname,
    a.create_time AS f_open_account_time,
    DATE_FORMAT(a.create_time, '%Y%m%d') AS f_open_account_date,
    HOUR (a.create_time) AS f_open_account_hour,
    a.group_ids AS f_group_id,
    a.f_reg_source AS f_reg_source,
    t.f_user_area_id AS f_user_area_id,
    t.f_user_type AS f_user_type,
    t.f_source AS f_user_source,
    device.device_id AS f_device_id,
    CONCAT_WS(
        ",",
        device.f_cai_id,
        device.f_mobile_id,
        device.f_pid_id,
        device.f_stb_id
    ) AS f_device_series_id,
    t.f_customer_code AS f_customer_code,
    token.f_first_login_time AS f_first_login_time,
    DATE_FORMAT(
        token.f_first_login_time,
        '%Y%m%d'
    ) AS f_first_login_date,
    HOUR (token.f_first_login_time) AS f_first_longin_hour,
    token.f_app_version AS f_app_version
FROM
    (
        (
            (
                account_info a
                LEFT JOIN t_da_boss_info t ON a.DA = t.f_da
            )
            LEFT JOIN (
                SELECT
                    d.home_id,
                    GROUP_CONCAT(d.device_id) AS device_id,
                    GROUP_CONCAT(d.cai_id) AS f_cai_id,
                    GROUP_CONCAT(d.mobile_id) AS f_mobile_id,
                    GROUP_CONCAT(d.pad_id) AS f_pid_id,
                    GROUP_CONCAT(d.stb_id) AS f_stb_id
                FROM
                    device_info d
                GROUP BY
                    d.home_id
            ) device ON a.home_id = device.home_id
        )
        LEFT JOIN (
            SELECT
                b.DA,
                b.f_extend,
                b.f_extend AS f_app_version,
                MIN(b.f_create_time) AS f_first_login_time
            FROM
                account_token b
            GROUP BY
                b.DA
        ) token ON a.DA = token.DA
    )
WHERE
    a.create_time > '20000101'
AND a.create_time < '20000102'

优化后语句，执行2S

EXPLAIN
SELECT SQL_NO_CACHE
    a.DA AS f_da,
    a.account_name AS f_account_name,
    a.sex AS f_sex,
    a.nick_name AS f_nickname,
    a.create_time AS f_open_account_time,
    a.home_id AS f_home_id,
    DATE_FORMAT(a.create_time, '%Y%m%d') AS f_open_account_date,
    HOUR (a.create_time) AS f_open_account_hour,
    a.group_ids AS f_group_id,
    a.f_reg_source AS f_reg_source,
    t.f_user_area_id AS f_user_area_id,
    t.f_user_type AS f_user_type,
    t.f_customer_code AS f_customer_code,
    d.device_id AS f_device_id,
    CONCAT_WS(
        ",",
        d.f_cai_id,
        d.f_mobile_id,
        d.f_pid_id,
        d.f_stb_id
    ) AS f_device_series_id,
    min(b.f_first_login_time) as min_f_first_login_time,
    DATE_FORMAT(
        b.f_first_login_time,
        '%Y%m%d'
    ) AS f_first_login_date,
    HOUR (b.f_first_login_time) AS f_first_longin_hour,
    b.f_app_version AS f_app_version
FROM
    ((account_info a
LEFT JOIN t_da_boss_info t ON a.DA = t.f_da)
LEFT JOIN (
    SELECT
        home_id,
        GROUP_CONCAT(device_id) AS device_id,
        GROUP_CONCAT(cai_id) AS f_cai_id,
        GROUP_CONCAT(mobile_id) AS f_mobile_id,
        GROUP_CONCAT(pad_id) AS f_pid_id,
        GROUP_CONCAT(stb_id) AS f_stb_id
    FROM
        device_info
    GROUP BY
        home_id
)
d ON a.home_id = d.home_id)
LEFT JOIN (
    SELECT
        DA,
        f_extend,
        f_extend AS f_app_version,
        f_create_time AS f_first_login_time
        FROM
        account_token
) b ON a.DA = b.DA
WHERE
    a.create_time BETWEEN '2000-01-01 00:00:00'
AND '2000-01-02 00:00:00'  GROUP BY a.da;