PHP、MySQL、Huge Join、处理速度

Question

这更像是一个理论查询，但我有一个复杂的联接(导致主表中多达 1900 条记录，再加上联接中的所有子结果表——如下所示的联接)，生成的网页在我的本地计算机上需要 5-10 分钟才能处理并完成构建。我意识到这可能很容易有很多因素，但我希望得到一些提示。基本上，我从两个表中加载一个名称数组(一个是交叉引用，因此该数组用于对名称上的数据进行排序，并带有链接和一个字段，说明它是否是交叉引用)，然后如果名称是不是交叉引用，我发出此连接:

select
  n.NameCode, n.AL_NameCode, n.Name, n.Name_HTML, n.Region, n.Local, n.Deceased,
  n.ArmsLink, n.RollOfArms, n.Blazon, n.PreferredTitle, n.ShortBio,
  n.HeadShotPhoto, n.HeadShotPhotographer, n.HeadShotContributor,
  x.NameCode, x.NameAKA, x.AlternateName,
  g.NameLink, g.`Group Name`,
  p.NameLink, p.`Relationship Type`, p.`Related To Link`,
  p2.Position_ID, p2.NameLink, p2.`Position Held`, p2.`Times Held`,
  p2.`Date Started`, p2.`Date Ended`, p2.Hyperlink as pos_Hyperlink,
  p2.`Screentip Text`,
  a.`Name Link`, a.Description, a.EventDate, a.Hyperlink, a.`Screentip Text`,
  a.ExternalLink
from who_names as n
left outer join who_crossref as x on n.NameCode=x.NameCode
left outer join who_groups as g on n.NameCode=g.NameLink
left outer join who_personal as p on n.NameCode=p.NameLink
left outer join who_positions as p2 on n.NameCode=p2.NameLink
left outer join who_arts as a on n.NameCode=a.`Name Link`
where n.NameCode = ?
order by n.Name desc, g.`Group Name`, p2.`Date Started`, a.EventDate;

为了输出数据的各个部分，我:

1) 启动一个表，2)在第一行输出名称和一些其他信息，3)然后，为了处理组(某人在组织内将自己关联到的子组)，我发出:

mysqli_data_seek( $result, 0 ); // to rewind to top of data so we're at first row

看看是否有任何东西需要为子组处理(不是每个人都有东西......)，4) 对于个人关系和其他部分，我会重复一遍，返回到数据的顶部，并循环返回是否有任何需要处理的内容。

处理完该人后，我关闭表格，然后在数组中循环到下一个名称，然后重复...

虽然这可行，但加载网页 5-10 分钟太长了。

我正在考虑解决这个问题的想法，但我不确定这是否是我的代码的任何特定方面。是返回到行集顶部的搜索吗？是浏览器中的表格吗？它是两者的结合(很可能)吗？该程序太大，无法在此处完整发布。我对如何解决这个问题感到相当困惑，希望有人能提供一些指导来帮助我加快处理速度，我希望我提供的细节足以提供一些可以使用的东西。

根据下面的评论和反馈，在 PHP Admin 中，我执行了以下操作:

explain select n.NameCode, n.AL_NameCode, n.Name, n.Name_HTML, n.Region, n.Local, n.Deceased,
                     n.ArmsLink, n.RollOfArms, n.Blazon, n.PreferredTitle, n.ShortBio, n.HeadShotPhoto,
                     n.HeadShotPhotographer, n.HeadShotContributor,
                     x.NameCode, x.NameAKA, x.AlternateName,
                     g.NameLink, g.`Group Name`,
                     p.NameLink, p.`Relationship Type`, p.`Related To Link`,
                     p2.Position_ID, p2.NameLink, p2.`Position Held`, p2.`Times Held`, p2.`Date Started`,
                     p2.`Date Ended`, p2.Hyperlink as pos_Hyperlink, p2.`Screentip Text`,
                     a.`Name Link`, a.Description, a.EventDate, a.Hyperlink, a.`Screentip Text`,
                     a.ExternalLink
                     from who_names as n
                     left outer join who_crossref as x on n.NameCode=x.NameCode
                     left outer join who_groups as g on n.NameCode=g.NameLink
                     left outer join who_personal as p on n.NameCode=p.NameLink
                     left outer join who_positions as p2 on n.NameCode=p2.NameLink
                     left outer join who_arts as a on n.NameCode=a.`Name Link`
                     where n.NameCode=638
                     order by n.Name desc, g.`Group Name`, p2.`Date Started`, a.EventDate

返回:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  n   const   PRIMARY,ix1_names   PRIMARY     4   const   1   Using temporary; Using filesort
1   SIMPLE  x   ref     ix2_crossref    ix2_crossref    4   const   1   NULL
1   SIMPLE  g   ref     ix3_groups  ix3_groups  4   const   3   NULL
1   SIMPLE  p   ref     ix4_personal    ix4_personal    4   const   1   NULL
1   SIMPLE  p2  ref     ix5_positions   ix5_positions   4   const   13  NULL
1   SIMPLE  a   ref     ix6_arts    ix6_arts    4   const   28  NULL

这似乎只是一个索引列表，所以它似乎对我没有帮助。

Answer 1

由于您使用的是单个主表，并且其余连接都是OUTER JOIN，因此有一个最重要的索引可以使您的查询更快:

create index ix1_names on who_names (NameCode, Name);

此外，针对相关表的嵌套循环联接 (NLJ) 将受益于以下索引。您可能已经拥有其中几个，因此请先检查是否拥有它们。如果不这样做，则创建它们:

create index ix2_crossref on who_crossref (NameCode);
create index ix3_groups on who_groups (NameLink);
create index ix4_personal on who_personal (NameLink);
create index ix5_positions  on who_positions (NameLink);
create index ix6_arts on who_arts (`Name Link`);

但同样，这是我认为最重要的第一个。

您需要进行真实测试，看看性能是否会因此而提高。

如果查询仍然很慢，请按照@memo的建议，使用以下方法检索执行计划:

explain select ...