我有一个庞大但狭窄的InnoDB表,有~9m条记录。在桌子上做count(*)
或count(id)
非常慢(6+ 秒(:
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使用索引来实现:
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
不幸的是,声明和以前一样慢。根据"SELECT COUNT(*("很慢,即使使用 where 子句,我也尝试优化表格但没有成功。
优化InnoDB COUNT(*)
性能的方法是什么?
从MySQL 5.1.6开始,您可以使用事件调度程序并定期将计数插入统计信息表中。
首先创建一个表来保存计数:
CREATE TABLE stats (
`key` varchar(50) NOT NULL PRIMARY KEY,
`value` varchar(100) NOT NULL);
然后创建一个事件来更新表:
CREATE EVENT update_stats
ON SCHEDULE
EVERY 5 MINUTE
DO
INSERT INTO stats (`key`, `value`)
VALUES ('data_count', (select count(id) from data))
ON DUPLICATE KEY UPDATE value=VALUES(value);
它并不完美,但它提供了一个独立的解决方案(没有 cronjob 或队列(,可以轻松定制,以根据计数所需的新鲜度运行。
目前,我已经使用以下近似值解决了这个问题:
EXPLAIN SELECT COUNT(id) FROM data USE INDEX (PRIMARY)
使用 InnoDB 时,可以从解释计划的rows
列读取大致的行数,如上所示。当使用MyISAM时,这将保持为空,因为表引用正在被优化 - 所以如果空回退到传统SELECT COUNT
。
基于@Che代码,您还可以在INSERT
和UPDATE
上使用触发器来perf2
,以便实时保持统计信息表中的值是最新的。
CREATE TABLE stats (
`key` varchar(50) NOT NULL PRIMARY KEY,
`value` varchar(100) NOT NULL
);
然后:
CREATE TRIGGER `count_up` AFTER INSERT ON `perf2` FOR EACH ROW UPDATE `stats`
SET `stats`.`value` = `stats`.`value` + 1
WHERE `stats`.`key` = 'perf2_count';
CREATE TRIGGER `count_down` AFTER DELETE ON `perf2` FOR EACH ROW UPDATE `stats`
SET `stats`.`value` = `stats`.`value` - 1
WHERE `stats`.`key` = 'perf2_count';
因此,可以使用此查询实时读取perf2
表中的行数:
SELECT `value` FROM `stats` WHERE `key` = 'perf2_count';
这样做的好处是消除了执行COUNT(*)
的性能问题,并且仅在数据更改时执行perf2
。