MySQL永远在“发送数据”。查询简单，数据量大

Question

我正在尝试对一个相当大的数据集运行一个我认为是简单的查询，并且执行时间非常长--它在“发送数据”状态中停滞了3-4个小时或更长时间。

表格如下所示：

CREATE TABLE `transaction` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uuid` varchar(36) NOT NULL,
`userId` varchar(64) NOT NULL,
`protocol` int(11) NOT NULL,
... A few other fields: ints and small varchars
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `uuid` (`uuid`),
KEY `userId` (`userId`),
KEY `protocol` (`protocol`),
KEY `created` (`created`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 COMMENT='Transaction audit table'

查询如下：

select protocol, count(distinct userId) as count from transaction
where created > '2012-01-15 23:59:59' and created <= '2012-02-14 23:59:59'
group by protocol;

该表大约有2.22亿行，查询中的where子句过滤到大约2000万行。distinct选项将把它减少到大约700,000个不同的行，然后在分组之后(当查询最终完成时)，实际上返回4到5行。

我知道这是一个很大的数据量，但是对于这个查询来说，4-5个小时的时间似乎太长了。

谢谢。

编辑:作为参考，这是在一个db.m2.4xlarge RDS数据库实例上的AWS上运行的。

Answer 1

这是一个非常繁重的查询。要了解为什么需要这么长时间，您应该了解其中的细节。

在索引字段上有一个范围条件，即MySQL在索引中找到创建的最小值，对于每个值，它从索引中获取相应的主键，从磁盘检索行，并获取当前索引记录中缺少的必需字段(协议、userId)，将它们放在“临时表”中，对这700000行进行分组。索引实际上可以使用，并且在这里仅用于加速范围条件。

提高速度的唯一方法是建立一个包含所有必要数据的索引，这样MySQL就不需要在磁盘上查找行。这被称为covering index。但是您应该理解，索引将驻留在内存中，并且将包含~ sizeOf(created+protocol+userId+PK)*rowCount字节，这本身对于更新表和其他索引的查询来说可能成为一种负担。创建单独的聚合表并使用查询定期更新表更容易。

Answer 2

为什么不分析一个查询，看看到底发生了什么？

SET PROFILING = 1; 
SET profiling_history_size = 0; 
SET profiling_history_size = 15; 
/* Your query should be here */
SHOW PROFILES; 
SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 3 GROUP BY state ORDER BY `duration (summed) in sec` DESC; 
SET PROFILING = 0; 
EXPLAIN /* Your query again should appear here */;

我认为这将帮助您了解查询花费时间的确切位置，并根据结果执行优化操作。

Answer 3

distinct和group by都需要在服务器上对临时数据进行排序和存储。由于有这么多数据，这可能需要一段时间。

索引userId、created和protocol的不同组合会有所帮助，但我不能说有多大帮助，或者什么索引最有帮助。