使用左联合和内部联合优化查询 [英] optimizing a query using union left joins and inner joins

查看：82
本文介绍了使用左联合和内部联合优化查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个要优化的查询.两个工会执行separatley都运行良好，但是，一旦我包含内部联接，则最多需要57秒.我该如何解决.我的查询如下

SELECT
    p.PROJID,
    p.StartDate,
    o.ORDERNO,
    p.PROJCODE,
    p.PROJECT,
    cat.type AS CATEGORY,
    p.AREA,
    p.STATE,
    p.COUNTRY,
    p.VALUE,
    p.PROCESSOR,
    p.PROJINFO,
    p.NES,
    p.SPECSALE,
    p.OFFICE,
    p.DEPTCODE,
    p.INTERNLCHG,
    p.INTERCOCHG,
    p.LORM,
    p.PERCENT,
    d.COMPANY,
    CONCAT(
        d.LASTNAME,
        ", ",
        d.FIRSTNAME
    ) AS Contact
FROM
    (
        (
            SELECT
                *
            FROM
                (
                    SELECT
                        `clients`.`CLIENTID` AS `CLIENTIDA`,
                        `clients`.`COMPANY` AS `COMPANY`
                    FROM
                        `hdb`.`clients`
                    UNION
                        SELECT
                            `accounts`.`id` AS `CLIENTIDA`,
                            `accounts`.`name` AS `COMPANY`
                        FROM
                            `sugarcrm`.`accounts`
                ) AS a
            INNER JOIN (
                SELECT
                    `hdb`.`contacts`.`CONTACTID` AS `CONTACTID`,
                    `hdb`.`contacts`.`CLIENTID` AS `CLIENTIDC`,
                    `hdb`.`contacts`.`FIRSTNAME` AS `FIRSTNAME`,
                    `hdb`.`contacts`.`LASTNAME` AS `LASTNAME`
                FROM
                    `hdb`.`contacts`
                UNION
                    SELECT
                        `sugarcrm`.`contacts`.`id` AS `CONTACTID`,
                        `sugarcrm`.`accounts_contacts`.`account_id` AS `CLIENTIDC`,
                        `sugarcrm`.`contacts`.`first_name` AS `FIRSTNAME`,
                        `sugarcrm`.`contacts`.`last_name` AS `LASTNAME`
                    FROM
                        `sugarcrm`.`contacts`
                    LEFT JOIN `sugarcrm`.`accounts_contacts` ON `sugarcrm`.`contacts`.`id` = `sugarcrm`.`accounts_contacts`.`contact_id`
            ) AS c ON a.CLIENTIDA = c.CLIENTIDC
        ) AS d
    )
INNER JOIN (
    (
        projects AS p
        INNER JOIN orders AS o ON p.ORDERNO = o.ORDERNO
    )
    INNER JOIN category AS cat ON p.category_id = cat.category_id
) ON d.CONTACTID = o.CONTACTID

对此进行解释可提供以下内容:

1, PRIMARY, cat, ALL, PRIMARY, , , , 10, 
1, PRIMARY, p, ref, FK_orders_projects,FK_category_projects_idx, FK_category_projects_idx, 5, hdb.cat.category_id, 400, Using where
1, PRIMARY, o, eq_ref, PRIMARY, PRIMARY, 4, hdb.p.ORDERNO, 1, 
1, PRIMARY, <derived2>, ALL, , , , , 18878, Using where
2, DERIVED, <derived3>, ALL, , , , , 7087, 
2, DERIVED, <derived5>, ALL, , , , , 18879, Using where
5, DERIVED, contacts, ALL, , , , , 8261, 
6, UNION, contacts, ALL, , , , , 10251, 
6, UNION, accounts_contacts, ref, idx_contid_del_accid, idx_contid_del_accid, 111, sugarcrm.contacts.id, 1, Using index
, UNION RESULT, <union5,6>, ALL, , , , , , 
3, DERIVED, clients, ALL, , , , , 2296, 
4, UNION, accounts, ALL, , , , , 4548, 
, UNION RESULT, <union3,4>, ALL, , , , , ,

没有联合的原始查询需要0.125秒

SELECT p.PROJID, p.StartDate, o.ORDERNO, p.PROJCODE, p.PROJECT, cat.type AS CATEGORY, p.AREA, p.STATE, p.COUNTRY,
p.VALUE, p.PROCESSOR, p.PROJINFO, p.NES, p.SPECSALE, p.OFFICE, p.DEPTCODE, p.INTERNLCHG, p.INTERCOCHG, p.LORM,
p.PERCENT, a.COMPANY, CONCAT(c.LASTNAME, ", ", c.FIRSTNAME) AS Contact
FROM (clients AS a 
INNER JOIN contacts AS c ON a.CLIENTID =c.CLIENTID) 
INNER JOIN ((projects AS p INNER JOIN orders AS o ON p.ORDERNO = o.ORDERNO) 
INNER JOIN category AS cat ON p.category_id = cat.category_id) ON c.CONTACTID = o.CONTACTID
ORDER BY p.PROJID, a.COMPANY;

对此的解释如下:

1, SIMPLE, cat, ALL, PRIMARY, , , , 10, Using temporary; Using filesort
1, SIMPLE, p, ref, FK_orders_projects,FK_category_projects_idx, FK_category_projects_idx, 5, hdb.cat.category_id, 400, Using where
1, SIMPLE, o, eq_ref, PRIMARY,FK_contacts_orders, PRIMARY, 4, hdb.p.ORDERNO, 1, 
1, SIMPLE, c, eq_ref, PRIMARY,FK_clients_contacts, PRIMARY, 52, hdb.o.CONTACTID, 1, 
1, SIMPLE, a, eq_ref, PRIMARY, PRIMARY, 52, hdb.c.CLIENTID, 1,

查询视图:

SELECT
    p.PROJID,
    p.StartDate,
    o.ORDERNO,
    p.PROJCODE,
    p.PROJECT,
    cat.type AS CATEGORY,
    p.AREA,
    p.STATE,
    p.COUNTRY,
    p.VALUE,
    p.PROCESSOR,
    p.PROJINFO,
    p.NES,
    p.SPECSALE,
    p.OFFICE,
    p.DEPTCODE,
    p.INTERNLCHG,
    p.INTERCOCHG,
    p.LORM,
    p.PERCENT,
    a.COMPANY,
    CONCAT(
        c.LASTNAME,
        ", ",
        c.FIRSTNAME
    ) AS Contact
FROM
    (
        view_accounts_sugar_hdb AS a
        INNER JOIN view_contacts_sugar_hdb AS c ON a.CLIENTID = c.CLIENTID
    )
INNER JOIN (
    (
        projects AS p
        INNER JOIN orders AS o ON p.ORDERNO = o.ORDERNO
    )
    INNER JOIN category AS cat ON p.category_id = cat.category_id
) ON c.CONTACTID = o.CONTACTID
ORDER BY
    p.PROJID,
    a.COMPANY;

这需要340秒.

解决方案

这个绝对比我为您提供帮助的最后一个丑陋:)...无论如何，都适用相同的原理.为了将来，请务必尝试了解我在这里所做的事情.首先编写JOIN关系，以了解数据的来源.另外，看看我的缩进...我在各个级别上都展示了可读性.

Orders -> Projects -> Categories...

然后，普通客户表中的路径通过

Orders -> Contacts -> Clients

最后到达SugarCRM联系人... 

Orders -> Accounts_Contacts -> Accounts

因此，既然您已经设置了关系(并使用了别名)，那么这与对普通联系人/客户与CRM联系人/帐户实施LEFT-JOIN的最后一个答案类似.

订单"，产品"和类别"表上的字段列表非常简单，因为它们非常直接.这样就只剩下LEFT-JOIN所在的谁/客户"信息.如果普通"客户为null，则使用CRM版本字段，否则使用普通"客户字段... 

SELECT
      P.PROJID,
      P.StartDate,
      O.ORDERNO,
      P.PROJCODE,
      P.PROJECT,
      cat.`type` AS CATEGORY,
      P.AREA,
      P.STATE,
      P.COUNTRY,
      P.VALUE,
      P.PROCESSOR,
      P.PROJINFO,
      P.NES,
      P.SPECSALE,
      P.OFFICE,
      P.DEPTCODE,
      P.INTERNLCHG,
      P.INTERCOCHG,
      P.LORM,
      P.PERCENT,
      CASE when HCLIENT.ClientID IS NULL
           then SCLIENT.`name`
           ELSE HCLIENT.Company end as Company,
      CASE when HCLIENT.ClientID IS NULL
           then CONCAT( SCT.LAST_NAME, ", ", SCT.FIRST_NAME ) 
           ELSE CONCAT( HCT.LASTNAME, ", ", HCT.FIRSTNAME ) end as Contact
   FROM
      orders O
         JOIN projects P
            ON O.OrderNo = P.OrderNo
            JOIN category AS cat 
               ON p.category_id = cat.category_id

         LEFT JOIN hdb.contacts HCT
            ON O.ContactID = HCT.ContactID
            LEFT JOIN hdb.clients HCLIENT
               ON HCT.ClientID = HCLIENT.ClientID

         LEFT JOIN sugarcrm.contacts SCT
            ON O.ContactID = SCT.ID
            LEFT JOIN sugarcrm.accounts_contacts SAC
               ON SCT.ID = SAC.contact_id
               LEFT JOIN sugarcrm.accounts SCLIENT
                  ON SCT.account_id = SCLIENT.ID

我也对性能改进感兴趣.

I have a query that I want to optimize. Both unions executed separatley run fine, however as soon as I include inner join it takes up to 57 seconds. How do I solve this. My query is as follows

SELECT
    p.PROJID,
    p.StartDate,
    o.ORDERNO,
    p.PROJCODE,
    p.PROJECT,
    cat.type AS CATEGORY,
    p.AREA,
    p.STATE,
    p.COUNTRY,
    p.VALUE,
    p.PROCESSOR,
    p.PROJINFO,
    p.NES,
    p.SPECSALE,
    p.OFFICE,
    p.DEPTCODE,
    p.INTERNLCHG,
    p.INTERCOCHG,
    p.LORM,
    p.PERCENT,
    d.COMPANY,
    CONCAT(
        d.LASTNAME,
        ", ",
        d.FIRSTNAME
    ) AS Contact
FROM
    (
        (
            SELECT
                *
            FROM
                (
                    SELECT
                        `clients`.`CLIENTID` AS `CLIENTIDA`,
                        `clients`.`COMPANY` AS `COMPANY`
                    FROM
                        `hdb`.`clients`
                    UNION
                        SELECT
                            `accounts`.`id` AS `CLIENTIDA`,
                            `accounts`.`name` AS `COMPANY`
                        FROM
                            `sugarcrm`.`accounts`
                ) AS a
            INNER JOIN (
                SELECT
                    `hdb`.`contacts`.`CONTACTID` AS `CONTACTID`,
                    `hdb`.`contacts`.`CLIENTID` AS `CLIENTIDC`,
                    `hdb`.`contacts`.`FIRSTNAME` AS `FIRSTNAME`,
                    `hdb`.`contacts`.`LASTNAME` AS `LASTNAME`
                FROM
                    `hdb`.`contacts`
                UNION
                    SELECT
                        `sugarcrm`.`contacts`.`id` AS `CONTACTID`,
                        `sugarcrm`.`accounts_contacts`.`account_id` AS `CLIENTIDC`,
                        `sugarcrm`.`contacts`.`first_name` AS `FIRSTNAME`,
                        `sugarcrm`.`contacts`.`last_name` AS `LASTNAME`
                    FROM
                        `sugarcrm`.`contacts`
                    LEFT JOIN `sugarcrm`.`accounts_contacts` ON `sugarcrm`.`contacts`.`id` = `sugarcrm`.`accounts_contacts`.`contact_id`
            ) AS c ON a.CLIENTIDA = c.CLIENTIDC
        ) AS d
    )
INNER JOIN (
    (
        projects AS p
        INNER JOIN orders AS o ON p.ORDERNO = o.ORDERNO
    )
    INNER JOIN category AS cat ON p.category_id = cat.category_id
) ON d.CONTACTID = o.CONTACTID

Explain on this provides the following:

1, PRIMARY, cat, ALL, PRIMARY, , , , 10, 
1, PRIMARY, p, ref, FK_orders_projects,FK_category_projects_idx, FK_category_projects_idx, 5, hdb.cat.category_id, 400, Using where
1, PRIMARY, o, eq_ref, PRIMARY, PRIMARY, 4, hdb.p.ORDERNO, 1, 
1, PRIMARY, <derived2>, ALL, , , , , 18878, Using where
2, DERIVED, <derived3>, ALL, , , , , 7087, 
2, DERIVED, <derived5>, ALL, , , , , 18879, Using where
5, DERIVED, contacts, ALL, , , , , 8261, 
6, UNION, contacts, ALL, , , , , 10251, 
6, UNION, accounts_contacts, ref, idx_contid_del_accid, idx_contid_del_accid, 111, sugarcrm.contacts.id, 1, Using index
, UNION RESULT, <union5,6>, ALL, , , , , , 
3, DERIVED, clients, ALL, , , , , 2296, 
4, UNION, accounts, ALL, , , , , 4548, 
, UNION RESULT, <union3,4>, ALL, , , , , ,

The original query without the union takes 0.125 seconds

SELECT p.PROJID, p.StartDate, o.ORDERNO, p.PROJCODE, p.PROJECT, cat.type AS CATEGORY, p.AREA, p.STATE, p.COUNTRY,
p.VALUE, p.PROCESSOR, p.PROJINFO, p.NES, p.SPECSALE, p.OFFICE, p.DEPTCODE, p.INTERNLCHG, p.INTERCOCHG, p.LORM,
p.PERCENT, a.COMPANY, CONCAT(c.LASTNAME, ", ", c.FIRSTNAME) AS Contact
FROM (clients AS a 
INNER JOIN contacts AS c ON a.CLIENTID =c.CLIENTID) 
INNER JOIN ((projects AS p INNER JOIN orders AS o ON p.ORDERNO = o.ORDERNO) 
INNER JOIN category AS cat ON p.category_id = cat.category_id) ON c.CONTACTID = o.CONTACTID
ORDER BY p.PROJID, a.COMPANY;

explain on this provides following:

1, SIMPLE, cat, ALL, PRIMARY, , , , 10, Using temporary; Using filesort
1, SIMPLE, p, ref, FK_orders_projects,FK_category_projects_idx, FK_category_projects_idx, 5, hdb.cat.category_id, 400, Using where
1, SIMPLE, o, eq_ref, PRIMARY,FK_contacts_orders, PRIMARY, 4, hdb.p.ORDERNO, 1, 
1, SIMPLE, c, eq_ref, PRIMARY,FK_clients_contacts, PRIMARY, 52, hdb.o.CONTACTID, 1, 
1, SIMPLE, a, eq_ref, PRIMARY, PRIMARY, 52, hdb.c.CLIENTID, 1,

Query with view:

SELECT
    p.PROJID,
    p.StartDate,
    o.ORDERNO,
    p.PROJCODE,
    p.PROJECT,
    cat.type AS CATEGORY,
    p.AREA,
    p.STATE,
    p.COUNTRY,
    p.VALUE,
    p.PROCESSOR,
    p.PROJINFO,
    p.NES,
    p.SPECSALE,
    p.OFFICE,
    p.DEPTCODE,
    p.INTERNLCHG,
    p.INTERCOCHG,
    p.LORM,
    p.PERCENT,
    a.COMPANY,
    CONCAT(
        c.LASTNAME,
        ", ",
        c.FIRSTNAME
    ) AS Contact
FROM
    (
        view_accounts_sugar_hdb AS a
        INNER JOIN view_contacts_sugar_hdb AS c ON a.CLIENTID = c.CLIENTID
    )
INNER JOIN (
    (
        projects AS p
        INNER JOIN orders AS o ON p.ORDERNO = o.ORDERNO
    )
    INNER JOIN category AS cat ON p.category_id = cat.category_id
) ON c.CONTACTID = o.CONTACTID
ORDER BY
    p.PROJID,
    a.COMPANY;

This takes over 340 secs.

解决方案

This one was definitely uglier than the last one I helped you on :)... Anyhow, the same principles apply. For future, please do try to understand what I'm doing here. Write the JOIN relationships FIRST to know where your data is coming from. Also, take a look at my indentations... I am showing at each level for readability.

Orders -> Projects -> Categories...

then, the path for those in the normal clients table via

Orders -> Contacts -> Clients

finally to the SugarCRM contacts...

Orders -> Accounts_Contacts -> Accounts

So, now that you have the relationships set (and aliased), this follows similar of last answer implementing LEFT-JOIN to normal contact/client vs CRM Contacts/Accounts.

The field list is simple on the Order, Products and Category tables as those are pretty direct. That leaves just the "who/client" information where the LEFT-JOIN comes in. If the Normal client is null, use the CRM version fields, otherwise use the normal client fields...

SELECT
      P.PROJID,
      P.StartDate,
      O.ORDERNO,
      P.PROJCODE,
      P.PROJECT,
      cat.`type` AS CATEGORY,
      P.AREA,
      P.STATE,
      P.COUNTRY,
      P.VALUE,
      P.PROCESSOR,
      P.PROJINFO,
      P.NES,
      P.SPECSALE,
      P.OFFICE,
      P.DEPTCODE,
      P.INTERNLCHG,
      P.INTERCOCHG,
      P.LORM,
      P.PERCENT,
      CASE when HCLIENT.ClientID IS NULL
           then SCLIENT.`name`
           ELSE HCLIENT.Company end as Company,
      CASE when HCLIENT.ClientID IS NULL
           then CONCAT( SCT.LAST_NAME, ", ", SCT.FIRST_NAME ) 
           ELSE CONCAT( HCT.LASTNAME, ", ", HCT.FIRSTNAME ) end as Contact
   FROM
      orders O
         JOIN projects P
            ON O.OrderNo = P.OrderNo
            JOIN category AS cat 
               ON p.category_id = cat.category_id

         LEFT JOIN hdb.contacts HCT
            ON O.ContactID = HCT.ContactID
            LEFT JOIN hdb.clients HCLIENT
               ON HCT.ClientID = HCLIENT.ClientID

         LEFT JOIN sugarcrm.contacts SCT
            ON O.ContactID = SCT.ID
            LEFT JOIN sugarcrm.accounts_contacts SAC
               ON SCT.ID = SAC.contact_id
               LEFT JOIN sugarcrm.accounts SCLIENT
                  ON SCT.account_id = SCLIENT.ID

I'd be interested in the performance improvement too.

这篇关于使用左联合和内部联合优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆