MySQL 查询太慢，我该如何改进？

Question

我有这个查询，在一个有 ~300.000 行的表上提取数据需要大约 14 秒。
该表将在不久的将来增加其大小……超过一百万行。
我使用了 EXISTS 子句而不是 IN 子句，我给出了改进。
但是查询太慢了。
你有什么解决办法吗？
提前致谢。

这是查询:

SELECT 
    flow,
    COUNT(*) tot
FROM
    (
        SELECT 
            ff.session_id,
            GROUP_CONCAT(ff.page, '#', ff.snippet_params,'$',ff.is_lead SEPARATOR '|') flow 
            FROM table_a ff
            WHERE EXISTS 
                (
                    SELECT
                        f.session_id
                    FROM table_a f
                    WHERE f.session_id = ff.session_id
                    AND f.is_lead = 1
                    GROUP BY f.user_id 
                    ORDER BY f.user_id, f.`timestamp` 
                )
            GROUP BY ff.user_id 
            ORDER BY ff.user_id, ff.`timestamp`, ff.session_id 
    )
AS flow
GROUP BY flow 
ORDER BY tot DESC LIMIT 10

这是解释:

id  select_type         table       type    possible_keys       key         key_len  ref                              rows  Extra                                         
------  ------------------  ----------  ------  ------------------  ----------  -------  -----------------------------  ------  ----------------------------------------------
 1  PRIMARY             <derived2>  ALL     (NULL)              (NULL)      (NULL)   (NULL)                            532  Using temporary; Using filesort               
 2  DERIVED             ff          ALL     (NULL)              (NULL)      (NULL)   (NULL)                         322154  Using temporary; Using filesort               
 3  DEPENDENT SUBQUERY  f           ref     is_lead,session_id  session_id  767      ff.session_id       3  Using where; Using temporary; Using filesort  

Answer 1

ORDER BY 中的额外表达式没有任何意义，因为“GROUP BY user_id”将保证 user_id 的唯一值。
ORDER BY 操作在 GROUP BY 操作之后应用。如果我的意图是为每个 session_id 获得最低的 user_id ，我将使用 MIN 聚合。在原始查询中，ORDER BY 对返回哪个 session_id 没有任何影响。 session_id 返回的值不确定。

(其他数据库会在此查询中引发错误。特定于 MySQL 的 GROUP BY 扩展允许查询运行，但我们可以通过在 sql_mode 中包含 ONLY_FULL_GROUP_BY 来获得更标准的行为。)

EXISTS 子查询中的 GROUP BY 没有任何意义。如果找到行，则存在一行。无需执行 GROUP BY 并聚合找到的行。

更仔细地观察，似乎不需要在 SELECT 列表中返回 session_id 。 (在 flow View 查询或 EXISTS 子查询中。)

如果我们删除无关的语法并将查询精简到其本质，即真正重要的部分，我们将得到一个如下所示的查询:

 SELECT flow.flow  AS flow
      , COUNT(*)   AS tot
   FROM (
          SELECT GROUP_CONCAT(ff.page,'#',ff.snippet_params,'$',ff.is_lead SEPARATOR '|') AS flow
            FROM table_a ff
           WHERE EXISTS
                 ( SELECT 1
                     FROM table_a f
                    WHERE f.is_lead = 1
                      AND f.session_id = ff.session_id
                 )
           GROUP BY ff.user_id
        ) flow
  GROUP BY flow.flow
  ORDER BY tot DESC
  LIMIT 10

该查询基本上是说从(不幸命名的表) table_a 中获取所有行，这些行的 session_id 与 table_a 中的至少一行匹配， session_id 的值也相同， is_lead 的值也为 1。

然后获取所有找到的行，并根据 user_id 列中的值聚合它们。

GROUP_CONCAT 中没有 ORDER BY 很奇怪，没有 DISTINCT 关键字有点奇怪。

GROUP_CONCAT 聚合返回不确定的行顺序，并且还可能包含重复值，这很奇怪。 (假设外部查询将根据从该 GROUP_CONCAT 聚合返回的值执行另一个聚合。)

但是，我不确定这个查询应该回答什么问题。我不知道什么是独特的，什么不是。

我们知道 EXISTS 子查询可以重写为 JOIN 操作:

 SELECT flow.flow  AS flow
      , COUNT(*)   AS tot
   FROM (
          SELECT GROUP_CONCAT(ff.page,'#',ff.snippet_params,'$',ff.is_lead SEPARATOR '|') AS flow
            FROM ( SELECT d.session_id
                     FROM table_a d
                    WHERE d.is_lead = 1
                    GROUP BY d.session_id
                 ) e
            JOIN table_a ff
              ON ff.session_id = e.session_id
           GROUP BY ff.user_id
        ) flow
  GROUP BY flow.flow
  ORDER BY tot DESC
  LIMIT 10

我们可以努力使查询运行得更快。但在我这样做之前，我想确保查询返回一个与规范匹配的集合。我需要确保查询实际上是在回答它旨在回答的问题。

我怀疑原始查询不正确。也就是说，我认为如果查询返回“正确”的结果，它是意外地这样做，而不是因为它可以保证。或者因为表中行的唯一性(基数)有一些特殊之处，或者由于处理行的意外顺序。

在我花时间调整它并添加索引之前，我想确保查询能保证返回正确的结果。

问:为什么 ORDER BY 中没有 GROUP_CONCAT ？例如

 GROUP_CONCAT( foo ORDER BY something)

问:没有 DISTINCT 关键字是否有特定原因？

 GROUP_CONCAT(DISTINCT foo ORDER BY something)

问:我们是否应该关注 GROUP_CONCAT(悄悄地)返回截断值的可能性？ (基于 group_concat_max_length 变量的设置？)

跟进

为了获得上述答案中最后一个查询的最佳性能，我建议添加以下索引:

 ... ON table_a (session_id, is_lead, page, snippet_params) 

或任何类似的索引，以 session_id 和 is_lead 作为前导列(按此顺序)，还包括 page 和 snippet_params 列。如果将 ORDER BY 添加到 GROUP_CONCAT，我们可能需要一个稍微不同的索引。

对于外部查询，无法绕过派生 flow 列的“使用文件排序”操作。 (除非您正在运行更新版本的 MySQL，其中可能会创建索引。或者我们愿意将查询分解为两个单独的操作。一个查询将内联 View 具体化为一个表，第二个查询要运行反对。)