SQL性能：SELECT DISTINCT与GROUP BY

发布时间：2020-12-12 07:03:00 所属栏目：MsSql教程来源：网络整理

导读：我一直在尝试改进现有的Oracle数据库驱动的应用程序的查询时间，该应用程序运行有点迟钝。应用程序执行几个大型查询，例如下面的一个，可能需要一个小时才能运行。在查询中使用GROUP BY子句替换DISTINCT将执行时间从100分钟缩短到10秒。我的理解是，SELECT DI

我一直在尝试改进现有的Oracle数据库驱动的应用程序的查询时间，该应用程序运行有点迟钝。应用程序执行几个大型查询，例如下面的一个，可能需要一个小时才能运行。在查询中使用GROUP BY子句替换DISTINCT将执行时间从100分钟缩短到10秒。我的理解是，SELECT DISTINCT和GROUP BY以相同的方式运行。为什么执行时间之间如此巨大的差距？在后端如何执行查询有什么区别？有没有一种情况，SELECT DISTINCT运行速度更快？

注意：在以下查询中，WHERE TASK_INVENTORY_STEP.STEP_TYPE =’TYPE A’仅表示可以过滤结果的多种方法之一。提供了此示例以显示加入所有没有列中包含列的表的原因，并将导致所有可用数据的十分之一

SQL使用DISTINCT：

SELECT DISTINCT 
    ITEMS.ITEM_ID,ITEMS.ITEM_CODE,ITEMS.ITEMTYPE,ITEM_TRANSACTIONS.STATUS,(SELECT COUNT(PKID) 
        FROM ITEM_PARENTS 
        WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID
        ) AS CHILD_COUNT
FROM
    ITEMS
    INNER JOIN ITEM_TRANSACTIONS 
        ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
        AND ITEM_TRANSACTIONS.FLAG = 1
    LEFT OUTER JOIN ITEM_METADATA 
        ON ITEMS.ITEM_ID = ITEM_METADATA.ITEM_ID
    LEFT OUTER JOIN JOB_INVENTORY 
        ON ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID     
    LEFT OUTER JOIN JOB_TASK_INVENTORY 
        ON JOB_INVENTORY.JOB_ITEM_ID = JOB_TASK_INVENTORY.JOB_ITEM_ID
    LEFT OUTER JOIN JOB_TASKS 
        ON JOB_TASK_INVENTORY.TASKID = JOB_TASKS.TASKID                              
    LEFT OUTER JOIN JOBS 
        ON JOB_TASKS.JOB_ID = JOBS.JOB_ID
    LEFT OUTER JOIN TASK_INVENTORY_STEP 
        ON JOB_INVENTORY.JOB_ITEM_ID = TASK_INVENTORY_STEP.JOB_ITEM_ID 
    LEFT OUTER JOIN TASK_STEP_INFORMATION 
        ON TASK_INVENTORY_STEP.JOB_ITEM_ID = TASK_STEP_INFORMATION.JOB_ITEM_ID
WHERE 
    TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
ORDER BY 
    ITEMS.ITEM_CODE

SQL使用GROUP BY：

SELECT
    ITEMS.ITEM_ID,(SELECT COUNT(PKID) 
        FROM ITEM_PARENTS 
        WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID
        ) AS CHILD_COUNT
FROM
    ITEMS
    INNER JOIN ITEM_TRANSACTIONS 
        ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
        AND ITEM_TRANSACTIONS.FLAG = 1
    LEFT OUTER JOIN ITEM_METADATA 
        ON ITEMS.ITEM_ID = ITEM_METADATA.ITEM_ID
    LEFT OUTER JOIN JOB_INVENTORY 
        ON ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID     
    LEFT OUTER JOIN JOB_TASK_INVENTORY 
        ON JOB_INVENTORY.JOB_ITEM_ID = JOB_TASK_INVENTORY.JOB_ITEM_ID
    LEFT OUTER JOIN JOB_TASKS 
        ON JOB_TASK_INVENTORY.TASKID = JOB_TASKS.TASKID                              
    LEFT OUTER JOIN JOBS 
        ON JOB_TASKS.JOB_ID = JOBS.JOB_ID
    LEFT OUTER JOIN TASK_INVENTORY_STEP 
        ON JOB_INVENTORY.JOB_ITEM_ID = TASK_INVENTORY_STEP.JOB_ITEM_ID 
    LEFT OUTER JOIN TASK_STEP_INFORMATION 
        ON TASK_INVENTORY_STEP.JOB_ITEM_ID = TASK_STEP_INFORMATION.JOB_ITEM_ID
WHERE 
    TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
GROUP BY
    ITEMS.ITEM_ID,ITEM_TRANSACTIONS.STATUS
ORDER BY 
    ITEMS.ITEM_CODE

以下是使用DISTINCT的查询的Oracle查询计划：

以下是使用GROUP BY查询的Oracle查询计划：

解决方法

性能差异可能是由于SELECT子句中子查询的执行引起的。我猜测它是在清除之前的每一行重新执行这个查询。对于该组，它将在组之后执行一次。

改为使用连接替换它：

select . . .,parentcnt
from . . . left outer join
      (SELECT PARENT_ITEM_ID,COUNT(PKID) as parentcnt
       FROM ITEM_PARENTS 
      ) p
      on items.item_id = p.parent_item_id

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!