今天使用count的时候发现了一些问题。回顾一下,温故而知新,可以为师矣~ <2,0>
语法: COUNT(
expr
)
<PS:count is a kind of aggregate functions in mysql.也就是我们说的group by配合使用的>
Returns a count of the number of non-NULL
values of expr
in the rows retrieved by a SELECT
statement. The result is a BIGINT
value.
COUNT(*)
is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL
values.
COUNT(*)
is optimized to return very quickly if the SELECT
retrieves from one table, no other columns are retrieved, and there is no WHERE
clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization applies only to MyISAM
tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB
, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.
摘了一部分官方doc,重点词汇已经加粗了.
想说的是:
1、count(*) 使用场合:MyISAM引擎、没有where条件的sql,这种情况mysql是做过优化的。
目前市面上充斥着用count(具体的列)替换count(*) 可以带来性能提升的言论,其实不然,这和store engine有莫大的关系,晚些时候我再写点关于InnoDB and MyISAM的一些区别。
我做了一个测试,数据114w,
count(1) : 487 ms
count(col1) : 531ms
count(*): 489ms
2、count(*) 返回记录和是否是null无关
比如一个Table test(col1,col2) 数据只有2条,都为null,null
mysql > select count(*) from test;
mysql > select count(col1) from test;
结果在本文第一行末尾已经给出,看看你能张口说出来不? 哈哈