MySQL Group By 即使建立索引也很慢，并且实际优化没有意义

Question

使用:MySQL 5.7，已知整个数据集缓存在 Linux 上的操作系统 memcache 中。引擎是InnoDB

SELECT colA, colB, count(*)
FROM someTable use index (idx_someTable_Date_colA_colB)
WHERE Date >= ? and Date < ?
GROUP BY colA, colB;

上述性能是非线性的，并且很快会比下一个查询构造慢 10 倍:

DROP TEMPORARY TABLE IF EXISTS TEMPT;
CREATE TEMPORARY TABLE TEMPT (
  colA bigint(20),
  colB varchar(50)
) 
AS (
  SELECT colA, colB
  FROM someTable use index (idx_someTable_Date_colA_colB)
  WHERE Date >= ? and Date < ?
);
SELECT colA, colB, count(*)
FROM TEMPT
GROUP BY colA, colB;

我找不到任何合理的解释来解释为什么对于大型数据集，第二种查询方式应该比第一种查询方式快 10 倍。首先，原始数据具有多列索引，涵盖了感兴趣的确切列。其次，更快的版本需要创建一个单独的表，该表至少复制一次所有数据，然后对未索引的临时表进行表扫描。

为什么第一个构造非常慢，而第二个构造却达到了与 Postgresql 相当的性能？

<小时/>

热门查询的配置文件:

# Status, Duration
'starting', '0.000046'
'Waiting for query cache lock', '0.000012'
'starting', '0.000013'
'checking query cache for query', '0.000164'
'checking permissions', '0.000022'
'Opening tables', '0.000054'
'init', '0.000077'
'System lock', '0.000025'
'optimizing', '0.000048'
'statistics', '0.000206'
'preparing', '0.000068'
'Creating tmp table', '0.000172'
'Sorting result', '0.000032'
'executing', '0.000030'
'Sending data', '48.525629'
'Creating sort index', '0.016266'
'end', '0.000042'
'query end', '0.000030'
'removing tmp table', '0.001459'
'query end', '0.000024'
'closing tables', '0.000020'
'freeing items', '0.000052'
'cleaning up', '0.000049'

<小时/>

底部查询的配置文件(创建临时/插入):

# Status, Duration
'starting', '0.000310'
'checking permissions', '0.000034'
'checking permissions', '0.000019'
'Opening tables', '0.000098'
'init', '0.000256'
'creating table', '0.023076'
'After create', '0.000056'
'System lock', '0.000038'
'optimizing', '0.000037'
'statistics', '0.000274'
'preparing', '0.000058'
'executing', '0.000017'
'System lock', '0.000040'
'Sending data', '3.877377'
'Waiting for query cache lock', '0.000047'
'Sending data', '0.000017'
'end', '0.000012'
'query end', '0.000705'
'closing tables', '0.000031'
'freeing items', '0.000070'
'cleaning up', '0.000038'

<小时/>

底部查询的配置文件(从临时中选择):

# Status, Duration
'starting', '0.000069'
'Waiting for query cache lock', '0.000018'
'starting', '0.000009'
'checking query cache for query', '0.000102'
'checking permissions', '0.000025'
'Opening tables', '0.000016'
'init', '0.000111'
'System lock', '0.000036'
'optimizing', '0.000020'
'statistics', '0.000051'
'preparing', '0.000049'
'Creating tmp table', '0.000090'
'Sorting result', '0.000045'
'executing', '0.000016'
'Sending data', '0.273446'
'Creating sort index', '0.002288'
'end', '0.000052'
'query end', '0.000027'
'removing tmp table', '0.000022'
'query end', '0.000017'
'closing tables', '0.000018'
'freeing items', '0.000064'
'cleaning up', '0.000057'

Answer 1

查看详细的 EXPLAIN 输出(最好是 JSON 格式，因为它有更多详细信息)将有助于了解发生了什么。造成不同性能的原因之一可能是在两种情况下使用了不同的分组/聚合算法。人们可以在分组之前进行排序，或者可以在读取行时逐渐增加临时表中的计数。这两种方法可能会产生不同的性能。

请注意，您不需要创建显式临时表。您可以将第一个查询放在 FROM 子句的子查询中(称为派生表):

SELECT colA, colB, count(*)
FROM (
  SELECT colA, colB
  FROM someTable use index (idx_someTable_Date_colA_colB)
  WHERE Date >= ? and Date < ?
  LIMIT 100000000
) dt
GROUP BY colA, colB;

注意 LIMIT 的数字很大。我使用它来防止优化器将子查询合并到外部查询中。