我有一个小而狭窄的InnoDB表,大约有900万条记录。在桌子上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
什么是/如何COUNT(*)在InnoDB 上优化性能?