mysql5.7的group by后的order by失效解决方案

版本5.7 

MySQL 5.7参考手册  /  ...  /  使用合并或实现优化派生表和视图引用

8.2.2.4使用合并或实现来优化派生表和视图引用

优化器可以使用两种策略（也适用于视图引用）处理派生表引用：

• 将派生表合并到外部查询块中

• 将派生表实现为内部临时表

例1：

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

通过合并派生表 derived_t1，该查询的执行类似于：

SELECT * FROM t1;

例2：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

通过合并派生表 derived_t2，该查询的执行类似于：

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

具有实现，derived_t1并 derived_t2在每个查询中被视为单独的表。

优化器以相同的方式处理派生表和视图引用：它尽可能避免不必要的实现，这样可以将条件从外部查询推送到派生表，并生成更高效的执行计划。（有关示例，请参见 第8.2.2.2节“使用实现优化子查询”。）

如果合并将导致外部查询块引用超过61个基表，则优化程序将选择实现。

ORDER BY如果这些条件都为真 ，则优化器将派生表或视图引用中的子句传播到外部查询块：

• 外部查询未分组或聚合。

• 外部查询不指定 DISTINCT，HAVING或 ORDER BY。

• 外部查询将此派生表或视图引用作为FROM子句中的唯一源。

否则，优化器会忽略该ORDER BY子句。

以下方法可用于影响优化程序是否尝试将派生表和查看引用合并到外部查询块中：

• 可以使用系统变量 的derived_merge标志 optimizer_switch，假设没有其他规则阻止合并。请参见第8.9.2节“可切换的优化”。默认情况下，启用该标志以允许合并。禁用该标志可防止合并并避免 ER_UPDATE_TABLE_USED 错误。

  该derived_merge标志也适用于不包含ALGORITHM子句的视图。因此，如果 ER_UPDATE_TABLE_USED使用等效于子查询的表达式的视图引用发生错误，则添加ALGORITHM=TEMPTABLE到视图定义会阻止合并并优先于该 derived_merge值。

• 可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响并不明确。防止合并的构造对于派生表和视图引用是相同的：

  • 聚合函数（SUM()， MIN()， MAX()， COUNT()，等等）

  • DISTINCT

  • GROUP BY

  • HAVING

  • LIMIT

  • UNION 要么 UNION ALL

  • 选择列表中的子查询

  • 分配给用户变量

  • 仅引用文字值（在这种情况下，没有基础表）

该derived_merge标志也适用于不包含ALGORITHM子句的视图。因此，如果 ER_UPDATE_TABLE_USED使用等效于子查询的表达式的视图引用发生错误，则添加ALGORITHM=TEMPTABLE 到视图定义会阻止合并并优先于当前derived_merge值。

如果优化器选择实现策略而不是合并派生表，它将按如下方式处理查询：

• 优化器推迟派生表实现，直到在查询执行期间需要其内容。这提高了性能，因为延迟实现可能导致根本不必执行此操作。考虑将派生表的结果连接到另一个表的查询：如果优化器首先处理该另一个表并发现它不返回任何行，则不需要进一步执行连接，并且优化器可以完全跳过实现派生表的实现。

• 在查询执行期间，优化器可以向派生表添加索引以加速从中检索行。

EXPLAIN 对于SELECT包含派生表的查询， 请考虑以下语句：

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

优化程序通过延迟派生表来避免实现派生表，直到SELECT执行期间需要结果为止 。在这种情况下，不执行查询（因为它出现在 EXPLAIN语句中），因此永远不需要结果。

即使对于执行的查询，派生表实现的延迟也可能使优化器完全避免实现。发生这种情况时，执行实现所需的时间会更快地执行查询。请考虑以下查询，该查询将派生表的结果连接到另一个表：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

如果优化进程t1首先并且WHERE子句产生空结果，则连接必须为空并且不需要实现派生表。

对于派生表需要实现的情况，优化器可以向实现表添加索引以加速对其的访问。如果这样的索引允许 ref访问表，则可以大大减少查询执行期间读取的数据量。请考虑以下查询：

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

优化器在列上构造索引 f1，derived_t2如果这样做将允许使用ref最低成本执行计划的 访问。添加索引后，优化程序可以将具体化派生表视为具有索引的常规表，并且与生成的索引类似。与没有索引的查询执行成本相比，索引创建的开销可以忽略不计。如果 ref访问会导致比其他访问方法更高的成本，则优化程序不会创建任何索引并且不会丢失任何内容。

对于优化程序跟踪输出，合并的派生表或视图引用不会显示为节点。只有其基础表出现在顶部查询的计划中。

 

SELECT * from (
SELECT a.id,
a.uin uin,
a.Contact_Number contactNumber,
b.Add_Time addTime,
b.Rate rate,
Order_Num orderNum,
c.mq_server_id mqServerId,
b.Verify,
c.server_id serverId,
c.Nick_Name nickName,
a.contact_name contactName
FROM add_contact_details a
INNER JOIN wechat_user_add_settings b on b.uin = a.uin
INNER JOIN wechat_user c on c.uin = a.uin
WHERE
c.`Status` = 1
and a.Status = 0
and b.Is_Start = 1
and locate('Ipad',c.mq_server_id)
and b.addCount < b.Wx_Limit_Number
GROUP BY a.id
ORDER BY Order_Num

) tmp
GROUP BY tmp.uin,tmp.mqServerId;