如何使用10个以上的联接优化查询？

Juliano Nunes Silva Oliveira 发表于 Dev

朱利安诺·内尼斯·席尔瓦·奥利维拉

我的应用程序使用单个查询来返回用户的所有权限，并且该单个查询具有10个INNER JOIN来创建整个结果集。

这是查询的预览（由于机密信息，我不得不更改表名）：

SELECT 
    TABLE9.CONTINENT, TABLE9.COD_COUNTRY, TABLE9.DES_COUNTRY, TABLE9.COD_ISO, 
    TABLE7.ID_DEL, TABLE7.COD_DEL, TABLE7.DES_DEL, TABLE7.DES_ZONE, TABLE7.GMT_MINUTES, 
    TABLE7.CANT_MIN_INI, TABLE7.CANT_MIN_SALIDA, TABLE7.CANT_MET_BASE, TABLE5.ID_TS, 
    TABLE5.COD_TS, TABLE2.ID_ROLE, TABLE2.TIMEOUT_SESION, TABLE11.ID_PERMISSION, 
    TABLE3.COD_APLICATION, TABLE3.DES_APLICATION, TABLE6.ID_PLANT, TABLE6.COD_PLANT, 
    TABLE6.DES_PLANT 

FROM TABLE1

INNER JOIN TABLE2 ON TABLE2.ID_ROLE = TABLE1.ID_ROLE
INNER JOIN TABLE3 ON TABLE3.ID_APLICATION = TABLE2.ID_APLICATION 
 INNER JOIN TABLE4 ON TABLE4.ID_PTS = TABLE1.ID_PTS
INNER JOIN TABLE5 ON TABLE4.ID_TS = TABLE5.ID_TS
INNER JOIN TABLE6 ON TABLE6.ID_PLANT = TABLE4.ID_PLANT
INNER JOIN TABLE7 ON TABLE7.ID_DEL = TABLE6.ID_DEL 
 INNER JOIN TABLE8 ON (TABLE8.ID_USER = TABLE1.ID_USER)
INNER JOIN TABLE9 ON TABLE9.ID_COUNTRY = TABLE7.ID_COUNTRY
INNER JOIN TABLE10 ON TABLE10.ID_ROLE = TABLE2.ID_ROLE
INNER JOIN TABLE11 ON (TABLE11.ID_PERMISSION = TABLE10.ID_PERMISSION 
                              AND TABLE11.ID_APLICATION = TABLE3.ID_APLICATION)

 WHERE TABLE11.COD_PERMISSION <> 'PermissionCode'
   AND TABLE8.ID_USER_AD = 'e5def917-73e6-4b4e-8b5b-436794768c4b'
   AND TABLE8.BOL_ENABLED = 1

这是执行计划（创建一些索引后，成本降低了，但是返回58k行仍需要39秒）：

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                             | 129 |   118K|    62   (9)| 00:00:01 |
|   1 |  SORT ORDER BY                         |                             | 129 |   118K|    62   (9)| 00:00:01 |
|   2 |   NESTED LOOPS                         |                             | 129 |   118K|    61   (7)| 00:00:01 |
|*  3 |    HASH JOIN                           |                             |3461 |  2926K|    61   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL                  | TABLE11                     | 262 | 24890 |     4   (0)| 00:00:01 |
|*  5 |     HASH JOIN                          |                             | 185 |   139K|    57   (8)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                 | TABLE3                      |  14 |   840 |     4   (0)| 00:00:01 |
|*  7 |      HASH JOIN                         |                             | 185 |   128K|    52   (6)| 00:00:01 |
|   8 |       TABLE ACCESS FULL                | TABLE2                      |  65 |  5785 |     4   (0)| 00:00:01 |
|*  9 |       HASH JOIN                        |                             | 185 |   112K|    48   (7)| 00:00:01 |
|  10 |        TABLE ACCESS FULL               | TABLE5                      |  56 |  2800 |     4   (0)| 00:00:01 |
|* 11 |        HASH JOIN                       |                             | 185 |   103K|    43   (5)| 00:00:01 |
|  12 |         TABLE ACCESS FULL              | TABLE9                      |   1 |    70 |     3   (0)| 00:00:01 |
|* 13 |         HASH JOIN                      |                             | 185 | 92870 |    40   (5)| 00:00:01 |
|  14 |          TABLE ACCESS FULL             | TABLE7                      |  43 |  5375 |     3   (0)| 00:00:01 |
|* 15 |          HASH JOIN                     |                             | 185 | 69745 |    36   (3)| 00:00:01 |
|  16 |           TABLE ACCESS FULL            | TABLE6                      |  43 |  4128 |     3   (0)| 00:00:01 |
|* 17 |           HASH JOIN                    |                             | 185 | 51985 |    33   (4)| 00:00:01 |
|  18 |            NESTED LOOPS                |                             | 193 | 35126 |    20   (0)| 00:00:01 |
|* 19 |             TABLE ACCESS BY INDEX ROWID| TABLE8                      |   1 |    77 |     2   (0)| 00:00:01 |
|* 20 |              INDEX UNIQUE SCAN         | AK_TABLE8_2                 |   1 |       |     1   (0)| 00:00:01 |
|  21 |             TABLE ACCESS BY INDEX ROWID| ADPR_TABLE1                 | 193 | 20265 |    18   (0)| 00:00:01 |
|* 22 |              INDEX RANGE SCAN          | IX_TABLE1                   | 193 |       |     2   (0)| 00:00:01 |
|  23 |            INDEX FAST FULL SCAN        | IX_TABLE4                   |2281 |   220K|    12   (0)| 00:00:01 |
|* 24 |    INDEX UNIQUE SCAN                   | AK_TABLE10                  |   1 |    73 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

我该怎么做才能改善此查询？

更新

这是我创建的索引：

create index IX_TABLE11 on TABLE11 (ID_PERMISSION, ID_APLICATION) ONLINE;
create index IX_TABLE8 on TABLE8 (ID_USER, ID_USER_AD, BOL_ACTIVE) ONLINE;
create index IX_TABLE6 on TABLE6 (ID_PLANT, ID_DEL) ONLINE;
create index IX_TABLE4 on TABLE4 (ID_PTS, ID_TS, ID_PLANT) ONLINE;
create index IX_TABLE2 on TABLE2 (ID_ROLE, ID_APLICATION) ONLINE;

浅褐色的

感谢您添加索引的说明。要基于表8的主要条件优化查询，您希望与WHERE子句关联的列位于最前面，而辅助字段为AFTER。由于您的条件是通过“ Table8”针对特定用户的，因此我对查询进行了稍微的重组，以将其置于主要位置，并稍稍更新了WHERE。

我还在相应的表中添加了索引，以指出您提供的索引以及应该稍加调整/添加的索引。

SELECT 
      -- Columns   
   FROM 
      TABLE8 
         INNER JOIN TABLE1
            ON TABLE8.ID_USER = TABLE1.ID_USER
            INNER JOIN TABLE2 
               ON TABLE1.ID_ROLE = TABLE2.ID_ROLE
               INNER JOIN TABLE3 
                  ON TABLE2.ID_APLICATION  = TABLE3.ID_APLICATION
               INNER JOIN TABLE10 
                  ON TABLE2.ID_ROLE = TABLE10.ID_ROLE
                  INNER JOIN TABLE11 
                     ON TABLE10.ID_PERMISSION = TABLE11.ID_PERMISSION 
                     AND TABLE3.ID_APLICATION = TABLE11.ID_APLICATION
                     AND TABLE11.COD_PERMISSION <> 'PermissionCode'
            INNER JOIN TABLE4 
               ON TABLE1.ID_PTS = TABLE4.ID_PTS
               INNER JOIN TABLE5 
                  ON TABLE4.ID_TS = TABLE5.ID_TS
               INNER JOIN TABLE6 
                  ON TABLE4.ID_PLANT = TABLE6.ID_PLANT
                  INNER JOIN TABLE7 
                     ON TABLE6.ID_DEL = TABLE7.ID_DEL
                     INNER JOIN TABLE9 
                        ON TABLE7.ID_COUNTRY = TABLE9.ID_COUNTRY
   WHERE
          TABLE8.BOL_ENABLED = 1
      AND TABLE8.ID_USER_AD = 'e5def917-73e6-4b4e-8b5b-436794768c4b'




Table    Index
TABLE1   (ID_USER, ID_ROLE, ID_PTS)
TABLE2   (ID_ROLE, ID_APPLICATION)   <- index already exists
TABLE3   (ID_APLICATION )
TABLE4   (ID_PTS, ID_TS, ID_PLANT )  <-  index already exists
TABLE5   (ID_TS )
TABLE6   (ID_PLANT, ID_DEL)          <-  index already exists
TABLE7   (ID_DEL, ID_COUNTRY)
TABLE8   (ID_USER_AD, BOL_ENABLED, ID_USER )   <- Added BOL_ENABLED, ID_USER as LAST column index
TABLE10  (ID_ROLE, ID_PERMISSION )
TABLE11  (ID_PERMISSION, ID_APLICATION, COD_PERMISSION )  <-- add COD_PERMISSION

从调整后的索引来看，您对它的评论仍然需要太长时间，我将提供以下内容。看来您的应用程序是基于浏览器的。如果是这样，则您的表具有特定的应用程序。我建议做的是以下几点。精简您的查询以获得一个人可以访问的DISTINCT应用程序。他们可能在屏幕上有一些东西可以让他们选择...。然后，一旦用户选择了他们想要的特定应用程序，然后运行查询，还包括他们选择的SINGLE应用程序的条件。因此，如果您有10个应用程序，则您的58k权限现在可能会减少到5-6k个记录。

因此，第一个查询可能会简化为用户可用应用程序的代码和描述。

SELECT DISTINCT
      TABLE3.COD_APLICATION, 
      TABLE3.DES_APLICATION
   FROM 
      TABLE8 
         INNER JOIN TABLE1
            ON TABLE8.ID_USER = TABLE1.ID_USER
            INNER JOIN TABLE2 
               ON TABLE1.ID_ROLE = TABLE2.ID_ROLE
               INNER JOIN TABLE3 
                  ON TABLE2.ID_APLICATION  = TABLE3.ID_APLICATION
   WHERE
          TABLE8.BOL_ENABLED = 1
      AND TABLE8.ID_USER_AD = 'e5def917-73e6-4b4e-8b5b-436794768c4b'

然后，从用户界面中选择特定的应用程序后，将该特定的应用程序添加到主查询中（注意更改仅在连接到table2时进行）

SELECT DISTINCT
      TABLE9.CONTINENT, 
      TABLE9.COD_COUNTRY, 
      TABLE9.DES_COUNTRY, 
      TABLE9.COD_ISO, 
      TABLE7.ID_DEL, 
      TABLE7.COD_DEL, 
      TABLE7.DES_DEL, 
      TABLE7.DES_ZONE, 
      TABLE7.GMT_MINUTES, 
      TABLE7.CANT_MIN_INI, 
      TABLE7.CANT_MIN_SALIDA, 
      TABLE7.CANT_MET_BASE, 
      TABLE5.ID_TS, 
      TABLE5.COD_TS, 
      TABLE2.ID_ROLE, 
      TABLE2.TIMEOUT_SESION, 
      TABLE11.ID_PERMISSION, 
      TABLE3.COD_APLICATION, 
      TABLE3.DES_APLICATION, 
      TABLE6.ID_PLANT, 
      TABLE6.COD_PLANT, 
      TABLE6.DES_PLANT
   FROM 
      TABLE8 
         INNER JOIN TABLE1
            ON TABLE8.ID_USER = TABLE1.ID_USER


            INNER JOIN TABLE2 
               ON TABLE1.ID_ROLE = TABLE2.ID_ROLE
              AND TABLE2.ID_APLICATION = [specific application user selected]


               INNER JOIN TABLE3 
                  ON TABLE2.ID_APLICATION  = TABLE3.ID_APLICATION
               INNER JOIN TABLE10 
                  ON TABLE2.ID_ROLE = TABLE10.ID_ROLE
                  INNER JOIN TABLE11 
                     ON TABLE10.ID_PERMISSION = TABLE11.ID_PERMISSION 
                     AND TABLE3.ID_APLICATION = TABLE11.ID_APLICATION
                     AND TABLE11.COD_PERMISSION <> 'PermissionCode'
            INNER JOIN TABLE4 
               ON TABLE1.ID_PTS = TABLE4.ID_PTS
               INNER JOIN TABLE5 
                  ON TABLE4.ID_TS = TABLE5.ID_TS
               INNER JOIN TABLE6 
                  ON TABLE4.ID_PLANT = TABLE6.ID_PLANT
                  INNER JOIN TABLE7 
                     ON TABLE6.ID_DEL = TABLE7.ID_DEL
                     INNER JOIN TABLE9 
                        ON TABLE7.ID_COUNTRY = TABLE9.ID_COUNTRY
   WHERE
          TABLE8.BOL_ENABLED = 1
      AND TABLE8.ID_USER_AD = 'e5def917-73e6-4b4e-8b5b-436794768c4b'

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-02-19

我来说两句

0条评论

登录后参与评论

上一篇：在Powershell中无法在此对象上找到属性“值”

来自分类Dev

Related 相关文章

文章

如何使用10个以上的联接优化查询？

如何使用10个以上的联接优化查询？

如何使用两个内部联接优化查询

如何使用条件联接优化查询？

如何使用表联接优化此查询？

如何使用自联接优化此查询？

在左联接中使用子查询时如何优化查询

如何使用Google Maps API v3优化10个以上的地址

使用联接优化 SQL 查询

如何使用多个左联接优化SQL查询

使用内部联接时如何优化Doctrine查询的数量？

如何使用一个以上的联接密钥联接两个流

使用左联接优化MySql查询

如何在MySQL中使用子查询为报表优化多个联接的查询

如何使用SQL联接3个表（1个查询）

如何优化具有两个不同内部联接的Mysql查询？（InnoDB的）

使用联接并执行需要1.5个小时的DB2查询的优化

查询优化（多个联接）

如何优化包含联接和子查询的查询

使用子查询进行查询并进行联接优化

如何在给定查询中使用内部联接并对其进行优化？

如何使用联接来合并这两个查询？

如何在Rails中对4个表使用联接查询

如何使用两个联接编写此查询

如何使用联接查询返回两个表值

具有3个联接的SQL查询优化

如何为大型数据集（数百万行）使用联接和子查询优化MySQL查询

如何优化多个左联接SQL SELECT查询？

如何优化这个极其缓慢的左外部联接sqlite查询？

如何优化此MYSQL查询-联接多个表

如何优化大表上的左联接查询