qq_33452366
qq_33452366
采纳率20%
2017-08-02 06:46

SQL语句查询时间过长的优化

下面这段sql

 SELECT
    TT1.COUNT - TT2.COUNT COUNT
FROM
    (
        SELECT
            IFNULL(sum(T.USER_4G_LAST), 0) COUNT
        FROM
            (
                SELECT DISTINCT
                    T1.SITE_CODE,
                    T1.USER_4G_LAST
                FROM
                    BIC_PERF_USER_INFO T1
                WHERE
                    T1.MONTH_STAMP = '201707'
            ) T
    ) TT1,
    (
        SELECT
            IFNULL(sum(T.USER_4G_LAST), 0) COUNT
        FROM
            (
                SELECT DISTINCT
                    T1.SITE_CODE,
                    T1.USER_4G_LAST
                FROM
                    BIC_PERF_USER_INFO T1
                WHERE
                    T1.MONTH_STAMP = '201706'
            ) T
    ) TT2

数据库中数据有150W
所用查询时间需要8秒多,请问有什么方法能够优化这个查询~~~

图片说明

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

5条回答

  • qq_33727653 砸死接触 4年前

    SELECT
    T2.MONTH_STAMP,
    T2.4g_sum
    from(
    SELECT
    T1.MONTH_STAMP,
    IFNULL(sum(T1.USER_4G_LAST), 0) AS 4g_sum
    FROM
    BIC_PERF_USER_INFO T1
    group by T1.MONTH_STAMP,
    T1.SITE_CODE,
    T1.USER_4G_LAST

            ) T2             
    

    where T2.MONTH_STAMP='201706' or T2.MONTH_STAMP='201707'
    为什么要在sql上直接运算,查出两个数再加减操作不行?

    点赞 评论 复制链接分享
  • wodewojueding wodewojueding 4年前
      SELECT
        IFNULL(sum(TT1.USER_4G_LAST), 0)-IFNULL(sum(TT2T.USER_4G_LAST), 0)  count
    FROM
        (       
                    SELECT 
                        T1.SITE_CODE,
                        T1.USER_4G_LAST
                    FROM
                        BIC_PERF_USER_INFO T1
                    WHERE
                        T1.MONTH_STAMP = '201707' group by  T1.SITE_CODE,
                        T1.USER_4G_LAST
    
        ) TT1,
        (        
                    SELECT 
                        T1.SITE_CODE,
                        T1.USER_4G_LAST
                    FROM
                        BIC_PERF_USER_INFO T1
                    WHERE
                        T1.MONTH_STAMP = '201706'  group by  T1.SITE_CODE,
                        T1.USER_4G_LAST           
        ) TT2
    
    点赞 评论 复制链接分享
  • qq_29789613 qq_641843909 4年前

    SELECT
    TT1.COUNT - TT2.COUNT COUNT
    FROM
    (
    SELECT DISTINCT
    IFNULL(sum(T.USER_4G_LAST), 0) COUNT
    FROM BIC_PERF_USER_INFO T
    WHERE T.MONTH_STAMP = '201707'
    ) TT1,
    (
    SELECT DISTINCT
    IFNULL(sum(T.USER_4G_LAST), 0) COUNT
    FROM BIC_PERF_USER_INFO T
    WHERE T.MONTH_STAMP = '201706'
    ) TT2

    点赞 评论 复制链接分享
  • qq_33452366 qq_33452366 4年前

    有数据库大神给些建议吗

    点赞 评论 复制链接分享
  • qq_29789613 qq_641843909 4年前

    select IFNULL(sum(T1.USER_4G_LAST), 0) COUNT1 - IFNULL(sum(T2.USER_4G_LAST), 0) COUNT2 as COUNT
    from BIC_PERF_USER_INFO T1 , BIC_PERF_USER_INFO T2
    where T1.MONTH_STAMP = '201707' and T2.MONTH_STAMP = '201706'

    点赞 评论 复制链接分享