解释 SQL 和查询优化

Question

解释一个耗时超过 5 秒的查询的 SQL（在 phpmyadmin 中）给了我上述内容。我读到我们可以研究解释 SQL 来优化查询。谁能告诉我这个Explain SQL是否告诉了这样的事情吗？

谢谢你们。

编辑：

查询本身：

SELECT 
a.`depart` , a.user, 
m.civ, m.prenom, m.nom, 
CAST( GROUP_CONCAT( DISTINCT concat( c.id, '~', c.prenom, ' ', c.nom ) ) AS char ) AS coordinateur, 
z.dr
FROM `0_activite` AS a
JOIN `0_member` AS m ON a.user = m.id
LEFT JOIN `0_depart` AS d ON ( m.depart = d.depart AND d.rank = 'mod' AND d.user_sec =2 )
LEFT JOIN `0_member` AS c ON d.user_id = c.id
LEFT JOIN `zone_base` AS z ON m.depart = z.deprt_num
GROUP BY a.user

编辑2：

两个表a和d的结构。顶部：a 和底部：d

编辑 3：

我想要在此查询中得到什么？

我首先想从表 0_activite 中获取“depart”和“user”（这是一个 id）的值。接下来，我想从 0_member 获取人名（civ、prenom 和 name），我通过“user”从 0_activite 获取其 id，通过匹配 0_activite.user 和 0_member.id。这里department缺少department，它也是一个id。

所以此时，我从两个表 0_activite 和 0_member 中获取了一个人的 Depart、id、civ、nom 和 prenom。

接下来，我想知道哪个博士与此分离有关，这是我从 zone_base 获得的。 0_activite和0_member中的delete值是相同的。

然后是更棘手的部分。 0_member 中的一个人可以与多个出发相关联，这存储在 0_depart 中。此外，每个用户都有一个级别，其中之一是“mod”，代表主持人。现在我想获取与第一个用户所在位置不同的所有版主，然后再次从 0_member 中获取这些版主的姓名。我还有一个变量 user_sec，但这在这种情况下可能不太重要，尽管我不能忽视它。

这就是查询变得棘手的原因。 0_member 存储 id、用户名、+ 1 个出发，0_depart 存储用户的所有出发，每个出发一行，0_activite 存储一些其他内容，我想通过 userid 关联这些内容0_activite 以及其余的。

希望我已经说清楚了。如果不是，请告诉我，我将再次尝试编辑这篇文章。

再次非常感谢。

Answer 1

除了这里其他人提供的几个答案之外，它可能有助于更好地理解查询中的“我想要什么”。由于您在另一个问题中接受了我最近的回答，因此您已按部门信息应用了过滤器。

您的查询正在 Department 表中按rank = 'mod' 和 user_sec = 2 执行 LEFT 连接。您的总体意图是显示 0_activite 表中的所有记录，而不管 0_Depart 表的有效连接...并且如果存在与 0_Depart 表匹配，您只关心“mod”和 2 值？

如果您只关心那些与 0_depart 和 'mod' 和 2 个条件特别相关的人，我会首先从该表开始反转查询，然后加入其余的。

通过关系或条件在表上拥有键始终会带来性能优势（与没有索引相比）。

首先使用最小的集合开始查询，然后连接到其他表。

从你的问题的澄清开始...我将从最内部开始...它是谁以及他们与哪些部门相关...然后获取版主（来自条件所在的部门）...然后获取实际版主的姓名信息...最后到您的 zone_base 为基于主持人部门的博士...

select STRAIGHT_JOIN
      DeptPerMember.*
      Moderator.Civ as ModCiv,
      Moderator.Prenom as ModPrenom,
      Moderator.Nom as ModNom,
      z.dr
   from 
      ( select
              m.ID,
              m.Depart,
              m.Civ,
              m.Prenom,
              m.Nom
           from
              0_Activite as a
                 join 0_member m
                    on a.User = m.ID
                    join 0_Depart as d
                       on m.depart = d.depart  ) DeptPerMember

      join 0_Depart as DeptForMod
         on DeptPerMember.Depart = DeptForMod.Depart
         and DeptForMod.rank = 'mod'
         and DeptForMod.user_sec = 2 

         join 0_Member as Moderator
            on DeptForMod.user_id = Moderator.ID

            join zone_base z
               on Moderator.depart = z.deprt_num

注意我如何对查询进行分层以获取每个部分并连接到下一个、下一个和下一个。我正在根据先前的结果构建链，并使用清晰的“别名”参考来澄清内容。现在，您可以通过其不同的“别名”引用从任何级别获取任何相应元素......

Answer 2

EXPLAIN 的输出向我们显示列出的第一个和第三个表（a 和 d）没有数据库引擎在执行此查询时使用的任何索引。两者的键列都是 NULL - 这是一个耻辱，因为两者都是“大”表（好吧，它们并不是很大，但与其余表相比，它们是大“uns”）。

从查询来看，0_activite上有user的索引，0_depart上有(depart,rank,user_sec)的索引code> 会在某种程度上提高性能。

Answer 3

您可以看到列 key 和 key_len 为空，这意味着它没有使用 possible_keys 列中的任何键。因此表 a 和 d 都在扫描所有行。 （检查行列中较大的数字。您希望该值较小）。

处理0_depart：
确保您有 (d.depart, d.rank,d.user_sec) 上的键，它们是 0_depart 连接的一部分。

处理0_activite：
我不确定，但 GROUP 列也应该被索引，因此您需要 a.user 上的密钥