mysql count(*) count(val) count(1)比较
来源:百度文库 编辑:神马文学网 时间:2024/04/29 21:07:25
今天上网,看到一种说法如下(仅作记录,不做评论):
如果表中没有主键,那么count(1)比count(*)快
如果有主键,那么count(主键,联合主键)比count(*)快
如果表中只有一个字段,count(*)最快
再摘一篇类似的英文:http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/
Looking at how people are using COUNT(*) and COUNT(col) it looks like most of them think they are synonyms and just using what they happen to like, while there is substantial difference in performance and even query result.
Lets look at the following series of examples:
PLAIN TEXT
SQL:
As this is MYISAM table MySQL has cached number of rows in this table. This is why it is able to instantly answer COUNT(*) and
COUNT(val2) queries, but not COUNT(val). Why ? Because val column is not defined as NOT NULL there can be some NULL values in it and so MySQL have to perform table scan to find out. This is also why result is different for the second query.
So COUNT(*) and COUNT(col) queries not only could have substantial performance performance differences but also ask different question.
MySQL Optimizer does good job in this case doing full table scan only if it is needed because column can be NULL.
Now lets try few more queries:
PLAIN TEXT
SQL:
As you can see even if you have where clause performance for count(*) and count(col) can be significantly different. In fact this example shows just 3 times performance difference because all data fits in memory, for IO bound workloads you frequently can see 10 and even 100 times performance difference in this case.
The thing is count(*) query can use covering index even while count(col) can't. Of course you can extend index to be (i,val) and get query to be index covered again but I would use this workaround only if you can't change the query (ie it is third party application) or in case column name is in the query for reason, and you really need count of non-NULL values.
It is worth to note in this case MySQL Optimizer does not do too good job optimizing the query. One could notice (val2) column is not null so count(val2) is same as count(*) and so the query could be run as index covered query. It does not and both queries have to perform row reads in this case.
PLAIN TEXT
SQL:
As you can see extending index helps in this case but it makes query about 2 times slower compared to count(*) one. This is probably because index becomes about two times longer in this case.
如果表中没有主键,那么count(1)比count(*)快
如果有主键,那么count(主键,联合主键)比count(*)快
如果表中只有一个字段,count(*)最快
再摘一篇类似的英文:http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/
Looking at how people are using COUNT(*) and COUNT(col) it looks like most of them think they are synonyms and just using what they happen to like, while there is substantial difference in performance and even query result.
Lets look at the following series of examples:
PLAIN TEXT
SQL:
- 1 row IN SET (0.00 sec)
- CREATE TABLE `fact` (
`i` int(10) UNSIGNED NOT NULL,
`val` int(11) DEFAULT NULL,
`val2` int(10) UNSIGNED NOT NULL,
KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> SELECT count(*) FROM fact;
+----------+
| count(*) |
+----------+
| 7340032 |
+----------+
1 row IN SET (0.00 sec)
mysql> SELECT count(val) FROM fact;
+------------+
| count(val) |
+------------+
| 7216582 |
+------------+
1 row IN SET (1.17 sec)
mysql> SELECT count(val2) FROM fact;
+-------------+
| count(val2) |
+-------------+
| 7340032 |
+-------------+
As this is MYISAM table MySQL has cached number of rows in this table. This is why it is able to instantly answer COUNT(*) and
COUNT(val2) queries, but not COUNT(val). Why ? Because val column is not defined as NOT NULL there can be some NULL values in it and so MySQL have to perform table scan to find out. This is also why result is different for the second query.
So COUNT(*) and COUNT(col) queries not only could have substantial performance performance differences but also ask different question.
MySQL Optimizer does good job in this case doing full table scan only if it is needed because column can be NULL.
Now lets try few more queries:
PLAIN TEXT
SQL:
- 1 row IN SET (0.00 sec)
- mysql> SELECT count(*) FROM fact WHERE i<10000;
+----------+
| count(*) |
+----------+
| 733444 |
+----------+
1 row IN SET (0.40 sec)
mysql> EXPLAIN SELECT count(*) FROM fact WHERE i<10000 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: fact
type: range
possible_keys: i
KEY: i
key_len: 4
ref: NULL
rows: 691619
Extra: USING WHERE; USING INDEX
1 row IN SET (0.00 sec)
mysql> SELECT count(val) FROM fact WHERE i<10000;
+------------+
| count(val) |
+------------+
| 720934 |
+------------+
1 row IN SET (1.29 sec)
mysql> EXPLAIN SELECT count(val) FROM fact WHERE i<10000 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: fact
type: range
possible_keys: i
KEY: i
key_len: 4
ref: NULL
rows: 691619
Extra: USING WHERE
1 row IN SET (0.00 sec)
mysql> SELECT count(val2) FROM fact WHERE i<10000;
+-------------+
| count(val2) |
+-------------+
| 733444 |
+-------------+
1 row IN SET (1.30 sec)
mysql> EXPLAIN SELECT count(val2) FROM fact WHERE i<10000 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: fact
type: range
possible_keys: i
KEY: i
key_len: 4
ref: NULL
rows: 691619
Extra: USING WHERE
As you can see even if you have where clause performance for count(*) and count(col) can be significantly different. In fact this example shows just 3 times performance difference because all data fits in memory, for IO bound workloads you frequently can see 10 and even 100 times performance difference in this case.
The thing is count(*) query can use covering index even while count(col) can't. Of course you can extend index to be (i,val) and get query to be index covered again but I would use this workaround only if you can't change the query (ie it is third party application) or in case column name is in the query for reason, and you really need count of non-NULL values.
It is worth to note in this case MySQL Optimizer does not do too good job optimizing the query. One could notice (val2) column is not null so count(val2) is same as count(*) and so the query could be run as index covered query. It does not and both queries have to perform row reads in this case.
PLAIN TEXT
SQL:
- 1 row IN SET (0.78 sec)
- mysql> ALTER TABLE fact DROP KEY i, ADD KEY(i,val);
Query OK, 7340032 rows affected (37.15 sec)
Records: 7340032 Duplicates: 0 Warnings: 0
mysql> SELECT count(val) FROM fact WHERE i<10000;
+------------+
| count(val) |
+------------+
| 720934 |
+------------+
As you can see extending index helps in this case but it makes query about 2 times slower compared to count(*) one. This is probably because index becomes about two times longer in this case.
mysql count(*) count(val) count(1)比较
Object Count Impact on Garbage Collection Performance
Object Count Impact on Garbage Collection Performance
Online Advertisers Bet on Video but Count on Display
求教!用hibernate,怎么用count(),sum(),怎么能根据条件查询,执行语句? Java / 框架、开源
求教!用hibernate,怎么用count(),sum(),怎么能根据条件查询,执行语句? Java / 框架、开源 - CSDN社区 community.csdn.net
Win32版与Unix版MySQL比较
mysql 中InnoDB和MyISAM的比较
MySQL入门学习 (1)
MySQL学习[1]
MySQL学习笔记1
MySQL学习笔记1
mysql
MySQL中文参考手册- 1 mysql的一般信息
4个mysql客户端工具的比较的相关文章推荐 - - JavaEye专栏频道
卢瓦河流域Val de Loire
CH3.1 登陆和退出mysql服务器
CH3.1 登陆和退出mysql服务器
多重比较1
生物学概念比较1
什么是市净率比较1
AS "Val"微声突击步枪(苏联/俄罗斯)
VB val,Str 函数用法 (字符数字互转)
VB val,Str 函数用法 (字符数字互转)