在MySQL中统计数据表的行数,可以使用三种方式:select count(*) select count(1) 和 select count(具体字段)
,使用这三者之间的查询效率是怎样的?
前提:如果你要统计的是某个字段的非空数据行数,则另当别论,毕竟比较执行效率的前提是结果一样才可以。
假设我们有表people,有100万条数据。
CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zipcode` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`firstname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`lastname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `zip_last_first` (`zipcode`,`lastname`,`firstname`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
那么如下三条SQL统计行数结果为:
# 1000000 条数据 414ms
select count(*) from people p ;
# 1000000 条数据
select count(1) from people p ;
# 999999条数据
select count(zipcode) from people p ;
count(*) 和 count(1)的执行计划
explain select SQL_NO_CACHE count(*) from people p ;
explain select SQL_NO_CACHE count(1) from people p ;
可以发现,二者执行计划是一模一样。对比二者的查询成本也能说明这一点:
从统计数据行结果来讲
count(*)
和 count(1)
都是对所有结果进行count
,二者本质没有区别(二者执行时间可能略有差别,不过你还是可以把它两的执行效率看成是相等的)。如果有 where 子句,则是对所有符合筛选条件的数据行进行统计。如果没有where子句,则是对数据表的数据行数进行统计。
count(具体字段)
则会过滤掉为null的数据。
如果是MyISAM
存储引擎,统计数据表的行数只需要O(1)
的复杂度,这是因为每张MyISAM
的数据表都有一个 meta
信息存储了 row_count
值,而一致性则由表级锁来保证。
如果是InnoDB存储引擎,因为InnoDB支持事务,采用行级锁和MVCC机制,所以无法像MyISAM一样,维护一个 row_count
变量,因此需要采用 扫描全表
,进行循环+计数
的方式来完成统计。
使用什么索引进行统计?
在InnoDB引擎中,如果采用 count(具体字段) 来统计数据行数,要尽量采用二级索引
。因为主键采用的索引是聚簇索引,聚簇索引包含的信息很多,显然会大于二级索引(非聚簇索引)。
对于count(*) 和 count(1)
来说,他们不需要查找具体的行,只是统计行数,系统会自动采用占用空间更小的二级索引来进行统计。
如果有多个二级索引,会使用 key_len 小
的二级索引进行扫描。当没有二级索引的时候,才会采用主键索引来进行统计。