如何通过使用索引在InnoDB上优化COUNT(*)性能 [英] How to optimize COUNT(*) performance on InnoDB by using index

问题描述

我的InnoDB桌比较宽大，但记录却很窄，只有约900万条记录.在表上执行count(*)或count(id)的速度非常慢(超过6秒):

I have a largish but narrow InnoDB table with ~9m records. Doing count(*) or count(id) on the table is extremely slow (6+ seconds):

DROP TABLE IF EXISTS `perf2`;

CREATE TABLE `perf2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT NULL,
  `timestamp` bigint(20) NOT NULL,
  `value` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ts_uniq` (`channel_id`,`timestamp`),
  KEY `IDX_CHANNEL_ID` (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

RESET QUERY CACHE;
SELECT COUNT(*) FROM perf2;

虽然该语句不是运行得太频繁，但对其进行优化会很不错.根据 http://www. cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/这可以通过强制InnoDB使用索引来实现:

While the statement is not run too often it would be nice to optimize it. According to http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/ this should be possible by forcing InnoDB to use an index:

SELECT COUNT(id) FROM perf2 USE INDEX (PRIMARY);

解释计划似乎很好:

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      perf2   index   NULL            PRIMARY 4       NULL    8906459 Using index

不幸的是，语句的速度和以前一样慢.根据>选择计数(*)"即使使用where子句，速度也很慢我也曾尝试过优化表，但没有成功.

Unfortunately the statement is as slow as before. According to "SELECT COUNT(*)" is slow, even with where clause I've also tried optimizing the table without success.

在InnoDB上优化COUNT(*)性能的方法是什么/是什么?

What/is the/re a way to optimize COUNT(*) performance on InnoDB?

推荐答案

目前，我已经通过使用以下近似值解决了这个问题:

For the time being I've solved the problem by using this approximation:

EXPLAIN SELECT COUNT(id) FROM data USE INDEX (PRIMARY)

如上所示，使用InnoDB时，可以从解释计划的rows列中读取大约行数.使用MyISAM时，由于对表引用进行了优化，因此将保留EMPTY，因此，如果空回退到传统的SELECT COUNT

The approximate number of rows can be read from the rows column of the explain plan when using InnoDB as shown above. When using MyISAM this will remain EMPTY as the table reference isbeing optimized away- so if empty fallback to traditional SELECT COUNT instead.

这篇关于如何通过使用索引在InnoDB上优化COUNT(*)性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！