为什么从大型表中查询COUNT()比SUM()快得多?

我有一个包含以下表的数据仓库:

主要

约800万条记录

CREATE TABLE `main` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cid` mediumint(8) unsigned DEFAULT NULL, //This is the customer id
`iid` mediumint(8) unsigned DEFAULT NULL, //This is the item id
`pid` tinyint(3) unsigned DEFAULT NULL, //This is the period id
`qty` double DEFAULT NULL,
`sales` double DEFAULT NULL,
`gm` double DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_pci` (`pid`,`cid`,`iid`) USING HASH,
KEY `idx_pic` (`pid`,`iid`,`cid`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=7978349 DEFAULT CHARSET=latin1

期

这个表大约有50条记录，有以下字段

id
<
年/gh>

客户

这有大约23,000条记录和以下字段

id
number//该字段是唯一的
name//这是一个简单的描述字段

下面的查询运行非常快(不到1秒)，并且返回大约2000:

select count(*) 
from mydb.main m 
INNER JOIN mydb.period p ON p.id = m.pid 
INNER JOIN mydb.customer c ON c.id = m.cid 
WHERE p.year = 2013 AND c.number = 'ABC';

但是这个查询要慢得多(超过45秒)，这与前面的查询相同，但是是求和而不是计数:

select sum(sales)
from mydb.main m 
INNER JOIN mydb.period p ON p.id = m.pid 
INNER JOIN mydb.customer c ON c.id = m.cid 
WHERE p.year = 2013 AND c.number = 'ABC';

当我解释每个查询时，我看到的唯一区别是在'count()'上查询"Extra"字段为"Using index"，而查询"sum()"字段为NULL。

Explain count() query

| id | select_type | table | type  | possible_keys        | key          | key_len | ref                 | rows | Extra       |
|  1 | SIMPLE      | c     | const | PRIMARY,idx_customer | idx_customer | 11      | const               |    1 | Using index |
|  1 | SIMPLE      | p     | ref   | PRIMARY,idx_period   | idx_period   | 4       | const               |    6 | Using index |
|  1 | SIMPLE      | m     | ref   | idx_pci,idx_pic      | idx_pci      | 6       | mydb.p.id,const     |    7 | Using index |

Explain sum() query

| id | select_type | table | type  | possible_keys        | key          | key_len | ref                 | rows | Extra       |
|  1 | SIMPLE      | c     | const | PRIMARY,idx_customer | idx_customer | 11      | const               |    1 | Using index |
|  1 | SIMPLE      | p     | ref   | PRIMARY,idx_period   | idx_period   | 4       | const               |    6 | Using index |
|  1 | SIMPLE      | m     | ref   | idx_pci,idx_pic      | idx_pci      | 6       | mydb.p.id,const     |    7 | NULL        |

我能做些什么使sum()走得更快?

提前感谢!

编辑

所有的表显示它正在使用引擎InnoDB

另外，作为旁注，如果我只是做一个'SELECT *'查询，这运行得非常快(不到2秒)。我希望'SUM()'不应该花费更长时间，因为SELECT *无论如何都必须检索行…

解决

这就是我所学到的:

由于销售字段不是索引的一部分，它必须从硬盘驱动器检索记录(这可能有点慢)。
我不太熟悉这个，但看起来I/O性能可以通过切换到SSD(固态驱动器)来提高。我得再研究一下。
~~现在，我想我要创建另一个摘要层，以获得我想要的性能~~
我在主表上重新定义了我的索引(pid,cid,iid,sales,gm,qty)，现在sum()查询运行得非常快!

谢谢大家!

索引是关键行的列表。

当您执行count()查询时，可以忽略数据库中的实际数据，只使用索引。

当您执行sum(sales)查询时，必须从磁盘读取每一行以获得销售数字，因此要慢得多。

此外，可以批量读取索引，然后在内存中处理，而磁盘访问将随机地丢弃驱动器，试图从整个磁盘读取行。

最后，索引本身可能有计数的摘要(以帮助生成计划)

更新

你的表实际上有三个索引:

PRIMARY KEY (`id`),
KEY `idx_pci` (`pid`,`cid`,`iid`) USING HASH,
KEY `idx_pic` (`pid`,`iid`,`cid`) USING HASH

所以你只有列id, pid, cid, iid上的索引。(顺便说一句，大多数数据库都足够聪明，可以组合索引，因此您可能可以稍微优化您的索引)

如果您添加另一个键，如KEY idx_sales(id,sales)，可以提高性能，但考虑到销售值的可能分布，您将为更新添加额外的性能成本，这可能是一件坏事

简单的答案是count()只计算行数。这可以通过索引满足。

sum()需要识别每一行，然后获取页面以获得sales列。这增加了很多开销——大约每行加载一个页面。

如果您将sales添加到索引中，那么它也应该运行得非常快，因为它不需要获取原始数据

相关内容

最新更新

热门标签：