连接多个表的慢查询执行 [英] Slow Query Execution joining multiple tables

问题描述

我有下表:

Parts
id int (idx)
partnumber varchar (idx)
accountnumber (idx)
enabled

样本数据:

RefUserGroup
id int (idx)
value varchar (idx)

样本数据:

Pdf < has about 15 columns I will list ones I am interested 
in currently over 300,000's rows
id int (idx)
accountnumber varchar (idx)
customername varchar (idx)

样本数据:

Ref_UserGroup_Pdf
id
groupid FK (idx)
partsnumber (idx)
enable (idx)

样本数据:

查询:这大约需要47秒至97秒的时间才能返回数据.

Query: This takes around 47secs- 97 secs to return data.

SELECT p.partsnumber
,rug.`value` AS `group`
,pd.customername AS customer
FROM ref_groupid_to_pdf rgp
INNER JOIN ref_user_group rug ON rug.id = rgp.groupid
INNER JOIN parts p on rgp.partsnumber = p.partsnumber
LEFT JOIN pdf pd on p.accountnumber= pd.accountnumber 
WHERE rug.groupid = 2
AND rug.groupid <> 1
ORDER BY customer;

任何人都可以针对如何优化此查询指出正确的方向吗?我知道通常我们将索引放在一个或多个列上，但是为此，我将索引放在每个列上，但即使那样它还是很慢的.

Can anyone point in the right direction on how to optimize this query? I am aware normally we put indexs on one, or more columns but for this I put index on every column that but even then it is really slow.

当查询加入pdf表时，它变得非常慢.

When the query joins on to the pdf table it becomes very slow.

更新说明:

根据建议更新查询:

SELECT p.partsnumber
,rug.`value` AS `group`
,pd.customername AS customer
FROM ref_groupid_to_pdfid rgp
INNER JOIN ref_user_group_pdf rug ON rug.groupid = rgp.groupid
INNER JOIN parts p on rgp.partsnumber = p.partsnumber
INNER JOIN pdf_load pd on p.accountnumber = pd.accountnumber 
WHERE rug.id = 2
GROUP BY rgp.partsnumber;

推荐答案

我会在子查询中查找客户名称.毕竟，一部分可以在pdf表中有很多条目，但是您始终只对任意查找一个这样的记录感兴趣.因此，请使用LIMIT 1个查询来选择该记录.

I'd look up the customer name in a subquery. After all a part can have many entries in the pdf table, but you are always only interested in finding one such record arbitrarily. So pick that record with a LIMIT 1 query.

select
  prt.partsnumber,
  grp.value as `group`,
  (
    select customername 
    from pdf
    where pdf.accountnumber = prt.accountnumber
    limit 1
  ) as customer
from parts prt
join ref_usergroup_pdf ref on ref.partsnumber = prt.partsnumber
join refusergroup grp on grp.id = ref.groupid
where ref.id = 2;

与子表中的零件表相同的查询代替.选择您更喜欢的一个:

Same query with parts table in the subquery instead. Choose whichever you like better:

select
  ref.partsnumber,
  grp.value,
  (
    select pdf.customername 
    from pdf
    where pdf.accountnumber = 
    (
      select prt.accountnumber
      from parts prt
      where prt.partsnumber = ref.partsnumber
    )
    limit 1
  ) as customer
from ref_usergroup_pdf ref
join refusergroup grp on grp.id = ref.groupid
where ref.id = 2;

由于在pdf(accountnumber)上有索引，因此查找应该非常快.如果在pdf(accountnumber,customername)上有一个复合索引，它将更快，因为这样一来，您将仅从索引中获取所有需要的数据，并且根本不必读取表.

As you have an index on pdf(accountnumber), lookup should be pretty fast. It would be even faster if you had a composite index on pdf(accountnumber,customername), as then you would gain all data needed from the index alone and the table wouldn't have to be read at all.

这篇关于连接多个表的慢查询执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！