mysql如何分割一个大的查询,以小的查询并将它们附加作为单个查询

标签: MySQL PHP
发布时间: 2017/4/9 23:25:49
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有个大的疑问就采取更多的时间来执行。 我想要将查询拆分成小的查询和执行它们并将它们附加作为单个结果。

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;

解决方法 1:

分手的查询和放在一起,只是为了优化......不是一个好主意。DBMS 引擎应该能够优化其自身上的东西,如果你在问这个问题是明确的。

子查询本身不是邪恶的的。一些 RDBMS (如火鸟) 可以处理许多子查询在相同的表,并一次优化的计划只是获得所需的表。不幸的是并不是所有的 RDBMS 执行此操作。因此,移除子查询可能会帮忙,尤其是在这种情况下在哪里你都从同一表中读取几个列。

另一个问题是老的 JOIN 语法 (使用 WHERE 条件)。它现在被建议使用显式连接...在语法上。这也可能对优化的影响。

这里是您的查询的优化的版本,你应该试着在更多的极端措施之前︰

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;

通知已用于许多左联接。这是因为我也不知道,如果行必须存在或不相关的表中。你不告诉我们什么都知道。可以优化查询性能进一步使用 (内) 连接而不是左联接上必须有匹配的行的表。

我也希望你有外键 costraints 所有那些表......,或至少索引来支持这些关系。

官方微信
官方QQ群
31647020