mysql - 多次加入同一个表

Question

我想多次使用子查询(需要不同的输入)并与父查询连接。问题是，查询需要几分钟才能执行。优化以下查询的最佳方法是什么。

SELECT A.PROJECT_ID,A.PROJECT_BASIC_INFORMATION_ID,B.VALUE AS BU_START_DATE,C.VALUE AS BU_DURATION,D.VALUE AS BU_AMOUNT,E.VALUE AS PLUG FROM 
PROJECT_BASIC_INFORMATION A,
CUSTOM_ATTRIBUTES_VALUES B,
CUSTOM_ATTRIBUTES_VALUES C,
CUSTOM_ATTRIBUTES_VALUES D,
CUSTOM_ATTRIBUTES_VALUES E
WHERE 
A.TENANT_ID = '100' AND
B.MAP_ID = (SELECT MST_ATTRIBUTE_ID FROM `MST_TENANT_CUSTOM_ATTRIBUTES` 
WHERE LABEL='Budget Project Savings Start Date' AND 
TENANT_ID='100') AND
C.MAP_ID = (SELECT MST_ATTRIBUTE_ID FROM `MST_TENANT_CUSTOM_ATTRIBUTES` 
WHERE LABEL='Budget Savings Duration' AND 
TENANT_ID='100') AND
D.MAP_ID = (SELECT MST_ATTRIBUTE_ID FROM `MST_TENANT_CUSTOM_ATTRIBUTES` 
WHERE LABEL='Budget Annualized Savings' AND 
TENANT_ID='100') AND
E.MAP_ID = (SELECT MST_ATTRIBUTE_ID FROM `MST_TENANT_CUSTOM_ATTRIBUTES` 
WHERE LABEL='Plug' AND 
TENANT_ID='100')

GROUP BY A.PROJECT_BASIC_INFORMATION_ID ORDER BY A.PROJECT_ID

谢谢。

Answer 1

master_attribute_id 表上的所有子查询正在自杀。我已经更改了有关 do 执行 JOIN 的查询。从原始的“A”别名开始，然后对同一“A.TENANT_ID”(非固定值)上的每个属性表进行 JOIN，但还每个实例另外 AND ...LABEL = 'whatever'。因此，每个联接都会获取每个相同租户和标签的自定义属性。现在，通过 MAP_ID 将其中每一个连接到属性表。因此，我的别名与“CAV”(custom_Addtribute_Values)表以及根据需要的 A、B、C、D 相关联。因此，您只需要最外层 WHERE 子句中的实际租户 ID。

现在，为了帮助确保查询的优化，您的 MST_TENANT_CUSTOM_ATTRIBUTES 表，我将在 (tenant_id, label, map_id) 上建立一个覆盖索引。对于 Custom_Attributes_Values 表，我将在 (map_id, value) 上有一个索引

SELECT 
      A.PROJECT_ID,
      A.PROJECT_BASIC_INFORMATION_ID,
      CAV_A.VALUE AS BU_START_DATE,
      CAV_B.VALUE AS BU_DURATION,
      CAV_C.VALUE AS BU_AMOUNT,
      E.VALUE AS PLUG 
   FROM 
      PROJECT_BASIC_INFORMATION A
         JOIN MST_TENANT_CUSTOM_ATTRIBUTES MT_A
            ON A.TENANT_ID = MT_A.TENANT_ID
            AND MT_A.LABEL = 'Budget Project Savings Start Date' 
            JOIN CUSTOM_ATTRIBUTES_VALUES A
               ON MT_A.MAP_ID = CAV_A.MAP_ID

         JOIN MST_TENANT_CUSTOM_ATTRIBUTES MT_B
            ON A.TENANT_ID = MT_B.TENANT_ID
            AND MA_B.LABEL = 'Budget Savings Duration' 
            JOIN CUSTOM_ATTRIBUTES_VALUES B
               ON MT_B.MAP_ID = CAV_B.MAP_ID

         JOIN MST_TENANT_CUSTOM_ATTRIBUTES MT_C
            ON A.TENANT_ID = MT_C.TENANT_ID
            AND MA_C.LABEL = 'Budget Annualized Savings'
            JOIN CUSTOM_ATTRIBUTES_VALUES C
               ON MT_C.MAP_ID = CAV_C.MAP_ID

         JOIN MST_TENANT_CUSTOM_ATTRIBUTES MT_D
            ON A.TENANT_ID = MT_D.TENANT_ID
            AND MA_D.LABEL = 'Plug' 
            JOIN CUSTOM_ATTRIBUTES_VALUES D
               ON MT_D.MAP_ID = CAV_D.MAP_ID
   WHERE 
      A.TENANT_ID = 100
   GROUP BY 
      A.PROJECT_BASIC_INFORMATION_ID 
   ORDER BY 
      A.PROJECT_ID