php - 如何将大查询拆分为小查询并将它们作为单个查询附加

Question

我有一个大型查询，需要更多时间来执行。我想将查询拆分为小查询并执行它们并将它们附加为单个结果。

select
  mm.Member_Id,
  (SELECT
     SubCategory_Name
   From member_subcategory
   WHERE mm.Admiral_Type_Id = SubCategory_Id) AS Admiral_Type_Id,
  (SELECT
     SubCategory_Name
   From member_subcategory
   WHERE mm.Royalty_Type_Id = SubCategory_Id) AS Royalty_Type_Id,
  (SELECT
     SubCategory_Name
   From member_subcategory
   WHERE mm.Parent_Type_Id = SubCategory_Id) AS Parent_Type_Id,
  (SELECT
     SubCategory_Name
   From member_subcategory
   WHERE mm.Guest_Type_Id = SubCategory_Id) AS Guest_Type_Id,
  mha.First_Name,
  mha.Middle_Name,
  mha.Last_Name,
  mha.Address,
  mha.City,
  mha.State,
  mha.Zip,
  mwa.First_Name    AS Work_First_Name,
  mwa.Middle_Name   AS Work_Middle_Name,
  mwa.Last_Name     AS Work_Last_Name,
  mwa.City          AS Work_City,
  mwa.State         AS Work_State,
  mwa.Zip           AS Work_Zip,
  mrm.Year          AS Royalty_Year,
  mrm.Title         AS Royalty_Title,
  mrm.First         AS Royalty_First,
  mrm.Maiden        AS Royalty_Maiden,
  (CASE WHEN mha.Preferred=0 THEN 'Work Address' WHEN mha.Preferred=1 THEN 'Home Address' END) AS Preferred,
  (select
     msi.MaidenName  AS Spouse_MaidenName
   FROM member_spouse_info msi
   WHERE msi.Member_Id = mm.Member_Id) as Spouse_MaidenName,
  (select
     msi.Mobile      AS Spouse_Mobile
   FROM member_spouse_info msi
   WHERE msi.Member_Id = mm.Member_Id) as Spouse_Mobile,
  (select
     mai.Fleet_Year  AS Fleet_Year
   FROM member_admiral_info mai
   WHERE mai.Member_Id = mm.Member_Id) as Fleet_Year,
  mmt.Admiral_Title,
  mmt.Spouse_Title,
  mmt.Couple_Title,
  (select
     mdi.Year          AS Duchess_Year
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Year,
  (select
     mdi.College       AS Duchess_College
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_College,
  (select
     mdi.Major         AS Duchess_Major
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Major,
  (select
     mdi.Sorority      AS Duchess_Sorority
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Sorority,
  (select
     mdi.Parent_Name   AS Duchess_Parent_Name
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Parent_Name,
  (select
     mdi.Escort_Name   AS Duchess_Escort_Name
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Escort_Name,
  (select
     dp.Duchess_Note   AS Duchess_Note
   FROM duchess_payment dp
   WHERE dp.Duchess_Id = (select
                mdi.Duchess_Id
              FROM member_duchess_info mdi
              WHERE mdi.Member_Id = mm.Member_Id)) as Duchess_Note,
  (select
     em.Year           AS Escort_Year
   FROM escort_master em
   WHERE em.Member_Id = mm.Member_Id) as Escort_Year,
  (select
     em.Name           AS Escort_Name
   FROM escort_master em
   WHERE em.Member_Id = mm.Member_Id) as Escort_Name,
  (select
     em.Parent_Name    AS Escort_Parent_Name
   FROM escort_master em
   WHERE em.Member_Id = mm.Member_Id) as Escort_Parent_Name,
  (select
     em.Notes          AS Escort_Note
   FROM escort_master em
   WHERE em.Member_Id = mm.Member_Id) as Escort_Note,
  (select
     guest.Guest_Name
   FROM guest
   WHERE guest.Member_Id = mm.Member_Id) as Guest_Name,
  (select
     (CASE WHEN mpm.Status=0 THEN 'Current' WHEN mpm.Status=1 THEN 'Past due' WHEN mpm.Status=2 THEN 'Paid' END) AS Payment_Status
   FROM member_payment_master mpm
   WHERE mpm.Member_Id = mm.Member_Id) AS Payment_Status,
  (select
     mpm.Payment_Date
   FROM member_payment_master mpm
   WHERE mpm.Member_Id = mm.Member_Id) AS Payment_Date,
  (select
     mpm.Payment_Method
   FROM member_payment_master mpm
   WHERE mpm.Member_Id = mm.Member_Id) AS Payment_Method,
  (select
     mpm.Payment_Amount
   FROM member_payment_master mpm
   WHERE mpm.Member_Id = mm.Member_Id) AS Payment_Amount
FROM member_master mm,
  member_home_address mha,
  member_work_address mwa,
  member_mailing_title mmt,
  member_royalty_master mrm
where mha.Member_Id = mm.Member_Id
    AND mwa.Member_Id = mm.Member_Id
    AND mmt.Member_Id = mm.Member_Id
    AND mrm.Member_Id = mm.Member_Id
ORDER BY mm.Member_Id;

Answer 1

仅出于优化目的而分解查询并将各个部分组合在一起......不是一个好主意。如果您的要求很明确，DBMS 引擎应该能够自行优化。

子查询本身并不邪恶。某些 RDBMS(例如 FireBird)可以处理同一表上的多个子查询，并优化计划以仅访问所需的表一次。不幸的是，并非所有 RDBMS 都这样做。因此，删除子查询可能会有所帮助，特别是在这种情况下，您要从同一个表中获取多个列。

另一个问题是旧的 JOIN 语法(使用 WHERE 条件)。现在建议使用显式 JOIN...ON 语法。这也可能对优化产生影响。

这是您的查询的优化版本，您应该在采取更极端的措施之前尝试:

SELECT
   mm.Member_Id,
   sca.SubCategory_Name AS Admiral_Type_Id,
   scr.SubCategory_Name AS Royalty_Type_Id,
   scp.SubCategory_Name AS Parent_Type_Id,
   scg.SubCategory_Name AS Guest_Type_Id,
   mha.First_Name,
   mha.Middle_Name,
   mha.Last_Name,
   mha.Address,
   mha.City,
   mha.State,
   mha.Zip,
   mwa.First_Name       AS Work_First_Name,
   mwa.Middle_Name      AS Work_Middle_Name,
   mwa.Last_Name        AS Work_Last_Name,
   mwa.City             AS Work_City,
   mwa.State            AS Work_State,
   mwa.Zip              AS Work_Zip,
   mrm.Year             AS Royalty_Year,
   mrm.Title            AS Royalty_Title,
   mrm.First            AS Royalty_First,
   mrm.Maiden           AS Royalty_Maiden,
   (CASE
      WHEN mha.Preferred=0 THEN 'Work Address'
      WHEN mha.Preferred=1 THEN 'Home Address'
   END)                 AS Preferred,
   msi.MaidenName       AS Spouse_MaidenName,
   msi.Mobile           AS Spouse_Mobile,
   mai.Fleet_Year       AS Fleet_Year,
   mmt.Admiral_Title,  
   mmt.Spouse_Title,   
   mmt.Couple_Title,   
   mdi.Year             AS Duchess_Year,
   mdi.College          AS Duchess_College,
   mdi.Major            AS Duchess_Major,
   mdi.Sorority         AS Duchess_Sorority,
   mdi.Parent_Name      AS Duchess_Parent_Name,
   mdi.Escort_Name      AS Duchess_Escort_Name,
   dp.Duchess_Note      AS Duchess_Note,
   em.Year              AS Escort_Year,
   em.Name              AS Escort_Name,
   em.Parent_Name       AS Escort_Parent_Name,
   em.Notes             AS Escort_Note,
   g.Guest_Name         AS Guest_Name,
   (CASE
      WHEN mpm.Status=0 THEN 'Current'
      WHEN mpm.Status=1 THEN 'Past due'
      WHEN mpm.Status=2 THEN 'Paid'
   END)                 AS Payment_Status,
   mpm.Payment_Date     AS Payment_Date,
   mpm.Payment_Method   AS Payment_Method,
   mpm.Payment_Amount   AS Payment_Amount
FROM      member_master         mm
JOIN      member_home_address   mha ON mha.Member_Id = mm.Member_Id
JOIN      member_work_address   mwa ON mwa.Member_Id = mm.Member_Id 
JOIN      member_mailing_title  mmt ON mmt.Member_Id = mm.Member_Id
JOIN      member_royalty_master mrm ON mrm.Member_Id = mm.Member_Id
LEFT JOIN member_spouse_info    msi ON msi.Member_Id = mm.Member_Id
LEFT JOIN member_admiral_info   mai ON mai.Member_Id = mm.Member_Id
LEFT JOIN member_duchess_info   mdi ON mdi.Member_Id = mm.Member_Id
LEFT JOIN duchess_payment       dp  ON dp.Duchess_Id = mdi.Duchess_Id
LEFT JOIN escort_master         em  ON em.Member_Id  = mm.Member_Id
LEFT JOIN guest                 g   ON g.Member_Id   = mm.Member_Id
LEFT JOIN member_payment_master mpm ON mpm.Member_Id = mm.Member_Id
LEFT JOIN member_subcategory    sca ON sca.SubCategory_Id = mm.Admiral_Type_Id
LEFT JOIN member_subcategory    scr ON scr.SubCategory_Id = mm.Royalty_Type_Id
LEFT JOIN member_subcategory    scp ON scp.SubCategory_Id = mm.Parent_Type_Id
LEFT JOIN member_subcategory    scg ON scg.SubCategory_Id = mm.Guest_Type_Id
ORDER BY mm.Member_Id;

请注意，我使用了很多 LEFT JOINS。这是因为我不知道相关表中是否必须存在这些行。你还没有告诉我们任何有关此事的事情。通过在必须具有匹配行的表上使用 (INNER) JOIN 而不是 LEFT JOIN，可以进一步优化查询性能。

我还希望您在所有这些表上都有外键约束...或者至少有索引来支持这些关系。