[真伪]数据库中 select count(1) 比 select count(*) 快? 本帖最后由 ACMAIN_CHM 于 2011-09-02 20:02:55 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我倒是很想数据库能推出类似sum(number field)的函数:sum(varchar field+',') count(1) 指的是 count(第1列) 吧,而 count(*) 自己选择主键列统计。count(1) 比 count(*) 是没有依据的,或许是因为有些人自认为的优化措施罢了!SELECT * 与 COUNT(*) 这里的“*”完全是不同的意义!认为 COUNT(1) 比 COUNT(*) 快的人,肯定没有理解 SELECT * 与 COUNT(*) 中“*”表示的意义是不一样的! count(主键)肯定是最优的方法之一,至于count(*),count(1)在oracle中有可能和count(pk)一样,可能比它慢,但是不能看比它快其他数据库我不熟悉 没什么意义吧 不同得数据库有不同优化方法 如果有where条件得话则应该一样了 这个在公司好像讨论过这个问题,貌似select count(*)快一些。 本帖最后由 apple_8180 于 2011-09-03 10:08:44 编辑 count(1),其实就是计算一共有多少符合条件的行。1并不是表示第一个字段,而是表示一个固定值。其实就可以想成表中有这么一个字段,这个字段就是固定值1,count(1),就是计算一共有多少个1.同理,count(2),也可以,得到的值完全一样,count('x'),count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用,返回的值完全是一样的。就是计数。count(*),执行时会把星号翻译成字段的具体名字,效果也是一样的,不过多了一个翻译的动作,比固定值的方式效率稍微低一些。百度知道里面看到的一个人的回答,希望对楼主有用 [code=SQL]mysql> use astarDatabase changedmysql> select count(*) from trans;+----------+| count(*) |+----------+| 542535 |+----------+1 row in set (0.41 sec)mysql> Select count(1) from trans;+----------+| count(1) |+----------+| 542535 |+----------+1 row in set (0.20 sec)mysql> Select count(*) from transdetail;+----------+| count(*) |+----------+| 906699 |+----------+1 row in set (0.28 sec)mysql> select count(1) from transdetail;+----------+| count(1) |+----------+| 906699 |+----------+1 row in set (0.30 sec)mysql> select count(*) from transpayment;+----------+| count(*) |+----------+| 542505 |+----------+1 row in set (0.33 sec)mysql> Select count(1) from transpayment;+----------+| count(1) |+----------+| 542505 |+----------+1 row in set (0.20 sec)mysql> USE C1Database changedmysql> select count(*) from trans;+----------+| count(*) |+----------+| 135 |+----------+1 row in set (0.17 sec)mysql> select count(1) from trans;+----------+| count(1) |+----------+| 135 |+----------+1 row in set (0.00 sec)code]个人测试结果:数据少的时候,用COUNT(*)会比COUNT(1)快,当数据量比较大的时候,COUNT(1)就快过COUNT(*) 以后用mssql测试过在网上还有人说count(9)比其它的都快 bug!!我测试的结果在当时那个条件是运行了n次都是count(1)最快。不过差别太小,如果环境不一样可能结果不一样! 应该是一样的吧。如果count(*) 可以用到索引之类的。为什么count(常数)它就不用索引了吗?完全可以看成count(*)或都他们就可以互换。 同意下面的说法:[code=SQL]mysql> use astarDatabase changedmysql> select count(*) from trans;+----------+| count(*) |+----------+| 542535 |+----------+1 row in set (0.41 sec)mysql> Select count(1) from trans;+----------+| count(1) |+----------+| 542535 |+----------+1 row in set (0.20 sec)mysql> Select count(*) from transdetail;+----------+| count(*) |+----------+| 906699 |+----------+1 row in set (0.28 sec)mysql> select count(1) from transdetail;+----------+| count(1) |+----------+| 906699 |+----------+1 row in set (0.30 sec)mysql> select count(*) from transpayment;+----------+| count(*) |+----------+| 542505 |+----------+1 row in set (0.33 sec)mysql> Select count(1) from transpayment;+----------+| count(1) |+----------+| 542505 |+----------+1 row in set (0.20 sec)mysql> USE C1Database changedmysql> select count(*) from trans;+----------+| count(*) |+----------+| 135 |+----------+1 row in set (0.17 sec)mysql> select count(1) from trans;+----------+| count(1) |+----------+| 135 |+----------+1 row in set (0.00 sec)code]个人测试结果:数据少的时候,用COUNT(*)会比COUNT(1)快,当数据量比较大的时候,COUNT(1)就快过COUNT(*) 汗~~上面说反了,个人测试结果:数据少的时候,用COUNT(1)会比COUNT(*)快,当数据量比较大的时候,COUNT(*)就快过COUNT(1)。可能也有表设计有关系有吧,单个PRIMARY和联合PRIMARY可能执行效果也都不一样吧。 select count(1) 百分百快 但是这需要你添加几百万数据后才会更明显 -- 执行计划一样,所以:应该是差不多,不过我们习惯不用*,而用常数(1)!mysql> explain select count(*) from t_Groupon_order\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_Groupon_order type: indexpossible_keys: NULL key: ONLINE_ID_KEY key_len: 9 ref: NULL rows: 7788848 Extra: Using index1 row in set (0.00 sec)mysql> explain select count(1) from t_Groupon_order\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_Groupon_order type: indexpossible_keys: NULL key: ONLINE_ID_KEY key_len: 9 ref: NULL rows: 7788882 Extra: Using index1 row in set (0.00 sec) 这个只能说跟数据库的实现有关系。在过去低版本的实现当中,count(1)可能要快于count(*), 但是高版本的实现里头,两者可能完全一样。 这要看DBMS如何处理了,MSSql是这样定义的:COUNT(*):返回记录总数,不会读取行上的任何信息,COUNT(1):返回记录总数,但是会在每一行读取1MySql,只要不是读取行的所有列,还是优先使用COUNT(*) 由于数据库引擎的不同结果也会不一样,所以测试了 MyISAM 和 InnoDB 两种引擎,后面是测试过程的输出,结论如下:1. InnoDB 对于各种形式的 count 均一样,需要全表扫描进行计算,对于非主键的列进行计算结果反而更快(可能是缓存的影响?)2. MyISAM 对于非主键的列进行 count 会慢一些,其它的形式均很快。对于 count(*) 肯定是直接读取数据库的“行数”属性,其它的也是 0 秒的形式不大清楚其实际计算过程3. 对于不同的数据库系统和不同的引擎可能实现上不同,所以此题目所说的比较需要基于不同的环境进行------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------测试过程:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 31 to server version: 5.1.6-alpha-nt-maxType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> use test;Database changedmysql>mysql> drop table if exists tb_myisam;Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE tb_myisam ( -> id int(10) NOT NULL auto_increment, -> name char(10) default NULL, -> PRIMARY KEY (id) -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.05 sec)mysql>mysql> drop table if exists tb_innodb;Query OK, 0 rows affected (0.05 sec)mysql> CREATE TABLE tb_innodb ( -> id int(10) NOT NULL auto_increment, -> name char(10) default NULL, -> PRIMARY KEY (id) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.08 sec)mysql>mysql> drop procedure if exists testdata;Query OK, 0 rows affected (0.00 sec)mysql> delimiter $$mysql> create procedure testdata() -> begin -> declare i int; -> set i = 0; -> while i <5000000 -> do -> insert into tb_myisam(name) values(ceil(rand()*100)); -> set i = i + 1; -> end while; -> end; -> $$Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql>mysql> call testdata();Query OK, 1 row affected (4 min 6.86 sec)mysql>mysql> insert into tb_innodb -> select * from tb_myisam;Query OK, 5000000 rows affected (1 min 31.47 sec)Records: 5000000 Duplicates: 0 Warnings: 0mysql>mysql> select count(1) from tb_innodb;+----------+| count(1) |+----------+| 5000000 |+----------+1 row in set (7.75 sec)mysql> select count(id) from tb_innodb;+-----------+| count(id) |+-----------+| 5000000 |+-----------+1 row in set (7.17 sec)mysql> select count(name) from tb_innodb;+-------------+| count(name) |+-------------+| 5000000 |+-------------+1 row in set (6.22 sec)mysql> select count(*) from tb_innodb;+----------+| count(*) |+----------+| 5000000 |+----------+1 row in set (6.34 sec)mysql>mysql> select count(1) from tb_myisam;+----------+| count(1) |+----------+| 5000000 |+----------+1 row in set (0.00 sec)mysql> select count(id) from tb_myisam;+-----------+| count(id) |+-----------+| 5000000 |+-----------+1 row in set (0.00 sec)mysql> select count(name) from tb_myisam;+-------------+| count(name) |+-------------+| 5000000 |+-------------+1 row in set (1.24 sec)mysql> select count(*) from tb_myisam;+----------+| count(*) |+----------+| 5000000 |+----------+1 row in set (0.00 sec)mysql>mysql> truncate table tb_innodb;Query OK, 5000284 rows affected (0.08 sec)mysql> truncate table tb_myisam;Query OK, 0 rows affected (0.02 sec)mysql> 一个表 a 有 b,c,d,e,f 五个字段。两种方式进行查询。①select * from a②select b,c,d,e,ffrom a两者的执行速度。当有大量记录的时候②的速度明显的快于①当记录很少的时候①的速度快于②因为查找的时候 都是从系统表里面在查找,a表的内容都是存在系统表里面的。select * 会查询所有的系统表当选择条件后,查找的系统表的数目会减少。 一般情况下,两着返回结果是一样的 假如表沒有主键(Primary key), 那么count(1)比count(*)快 如果有主键的話,那主键作为count的条件时候count(主键)最快 如果你的表只有一个字段的话那count(*)就是最快的 count(*) 跟 count(1) 的结果一样,都包括对NULL的统计 count(column) 是不包括NULL的统计 INNODB,MYSQL5.1.32,,10万条记录测试SELECT SQL_NO_CACHE COUNT(XM) FROM JZG1;0.097SELECT SQL_NO_CACHE COUNT(1) FROM JZG1;0.058SELECT SQL_NO_CACHE COUNT(*) FROM JZG1;0.071SELECT SQL_NO_CACHE COUNT(BH) FROM JZG1;0.085在XM上建立有复合索引,BH上单独索引myisam,MYSQL5.1.32,,10万条记录测试在XM上建立有复合索引,BH上单独索引SELECT SQL_NO_CACHE COUNT(XM) FROM JZG1;0.042SELECT SQL_NO_CACHE COUNT(1) FROM JZG1;0.000SELECT SQL_NO_CACHE COUNT(*) FROM JZG1;0.000SELECT SQL_NO_CACHE COUNT(BH) FROM JZG1;0.036在MYISAM引擎下,两者速度一致在INNODB引擎下,COUNT(1)略快于COUNT(*) 上百万数据。引擎不同。确实不同。INNODB **********************************Array ( [count(*)] => 1138446 ) 2.555640-----------------------Array ( [count(*)] => 1139702 ) 2.20650-----------------------Array ( [count(1)] => 1138446 ) 2.591530-----------------------Array ( [count(1)] => 1139702 ) 2.056590MYISAM **********************************Array ( [count(*)] => 1138446 ) 0.622220-----------------------Array ( [count(*)] => 1139702 ) 0.000270-----------------------Array ( [count(1)] => 1138446 ) 0.568510-----------------------Array ( [count(1)] => 1139702 ) 0.000380 MYISAM下,COUNT(*)直接去系统表里取出这个表的总行数这个字段的值返回就完了。相当于从一个几百行的表里查。mysql> select table_rows from information_schema.tables where table_name='t1';+------------+| table_rows |+------------+| 4 |+------------+1 row in set (0.00 sec) 这位大侠,你碉堡了 数据库取出字段的前100个字符怎么写.急 mysql字符串哈希比较 请问mysql里怎样让字段自动自增? 初学者问题,为什么装完MYSQL后没有CREATE DATABASE的选项呢? 怎样把Access数据库中的数据转移到MicrosoftSQL数据库中呀? 高分求教,有可视化创建mysql数据库的开发工具么 求助,多表连接查询 用了order by以后数据查询特别慢,帮我看下吧 MySQL语句优化 mysql插入与原记录相似的新纪录 MYSQL 数据库总是远程连接不上
其他数据库我不熟悉
1并不是表示第一个字段,而是表示一个固定值。
其实就可以想成表中有这么一个字段,这个字段就是固定值1,count(1),就是计算一共有多少个1.
同理,count(2),也可以,得到的值完全一样,count('x'),count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用,返回的值完全是一样的。就是计数。
count(*),执行时会把星号翻译成字段的具体名字,效果也是一样的,不过多了一个翻译的动作,比固定值的方式效率稍微低一些。
百度知道里面看到的一个人的回答,希望对楼主有用
Database changed
mysql> select count(*) from trans;
+----------+
| count(*) |
+----------+
| 542535 |
+----------+
1 row in set (0.41 sec)mysql> Select count(1) from trans;
+----------+
| count(1) |
+----------+
| 542535 |
+----------+
1 row in set (0.20 sec)mysql> Select count(*) from transdetail;
+----------+
| count(*) |
+----------+
| 906699 |
+----------+
1 row in set (0.28 sec)mysql> select count(1) from transdetail;
+----------+
| count(1) |
+----------+
| 906699 |
+----------+
1 row in set (0.30 sec)mysql> select count(*) from transpayment;
+----------+
| count(*) |
+----------+
| 542505 |
+----------+
1 row in set (0.33 sec)mysql> Select count(1) from transpayment;
+----------+
| count(1) |
+----------+
| 542505 |
+----------+
1 row in set (0.20 sec)mysql> USE C1
Database changed
mysql> select count(*) from trans;
+----------+
| count(*) |
+----------+
| 135 |
+----------+
1 row in set (0.17 sec)mysql> select count(1) from trans;
+----------+
| count(1) |
+----------+
| 135 |
+----------+
1 row in set (0.00 sec)
code]
个人测试结果:数据少的时候,用COUNT(*)会比COUNT(1)快,
当数据量比较大的时候,COUNT(1)就快过COUNT(*)
在网上还有人说count(9)比其它的都快 bug!!
我测试的结果在当时那个条件是运行了n次都是count(1)最快。
不过差别太小,如果环境不一样可能结果不一样!
如果count(*) 可以用到索引之类的。为什么count(常数)它就不用索引了吗?完全可以看成count(*)或都他们就可以互换。
Database changed
mysql> select count(*) from trans;
+----------+
| count(*) |
+----------+
| 542535 |
+----------+
1 row in set (0.41 sec)mysql> Select count(1) from trans;
+----------+
| count(1) |
+----------+
| 542535 |
+----------+
1 row in set (0.20 sec)mysql> Select count(*) from transdetail;
+----------+
| count(*) |
+----------+
| 906699 |
+----------+
1 row in set (0.28 sec)mysql> select count(1) from transdetail;
+----------+
| count(1) |
+----------+
| 906699 |
+----------+
1 row in set (0.30 sec)mysql> select count(*) from transpayment;
+----------+
| count(*) |
+----------+
| 542505 |
+----------+
1 row in set (0.33 sec)mysql> Select count(1) from transpayment;
+----------+
| count(1) |
+----------+
| 542505 |
+----------+
1 row in set (0.20 sec)mysql> USE C1
Database changed
mysql> select count(*) from trans;
+----------+
| count(*) |
+----------+
| 135 |
+----------+
1 row in set (0.17 sec)mysql> select count(1) from trans;
+----------+
| count(1) |
+----------+
| 135 |
+----------+
1 row in set (0.00 sec)
code]
个人测试结果:数据少的时候,用COUNT(*)会比COUNT(1)快,
当数据量比较大的时候,COUNT(1)就快过COUNT(*)
个人测试结果:数据少的时候,用COUNT(1)会比COUNT(*)快,
当数据量比较大的时候,COUNT(*)就快过COUNT(1)。
可能也有表设计有关系有吧,单个PRIMARY和联合PRIMARY可能执行效果也都不一样吧。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_Groupon_order
type: index
possible_keys: NULL
key: ONLINE_ID_KEY
key_len: 9
ref: NULL
rows: 7788848
Extra: Using index
1 row in set (0.00 sec)mysql> explain select count(1) from t_Groupon_order\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_Groupon_order
type: index
possible_keys: NULL
key: ONLINE_ID_KEY
key_len: 9
ref: NULL
rows: 7788882
Extra: Using index
1 row in set (0.00 sec)
在过去低版本的实现当中,count(1)可能要快于count(*), 但是高版本的实现里头,两者可能完全一样。
COUNT(*):返回记录总数,不会读取行上的任何信息,
COUNT(1):返回记录总数,但是会在每一行读取1MySql,只要不是读取行的所有列,还是优先使用COUNT(*)
1. InnoDB 对于各种形式的 count 均一样,需要全表扫描进行计算,对于非主键的列进行计算结果反而更快(可能是缓存的影响?)
2. MyISAM 对于非主键的列进行 count 会慢一些,其它的形式均很快。对于 count(*) 肯定是直接读取数据库的“行数”属性,其它的也是 0 秒的形式不大清楚其实际计算过程
3. 对于不同的数据库系统和不同的引擎可能实现上不同,所以此题目所说的比较需要基于不同的环境进行------------------------------------------------------------------------------------
------------------------------------------------------------------------------------测试过程:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31 to server version: 5.1.6-alpha-nt-maxType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> use test;
Database changed
mysql>
mysql> drop table if exists tb_myisam;
Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE tb_myisam (
-> id int(10) NOT NULL auto_increment,
-> name char(10) default NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> drop table if exists tb_innodb;
Query OK, 0 rows affected (0.05 sec)mysql> CREATE TABLE tb_innodb (
-> id int(10) NOT NULL auto_increment,
-> name char(10) default NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> drop procedure if exists testdata;
Query OK, 0 rows affected (0.00 sec)mysql> delimiter $$
mysql> create procedure testdata()
-> begin
-> declare i int;
-> set i = 0;
-> while i <5000000
-> do
-> insert into tb_myisam(name) values(ceil(rand()*100));
-> set i = i + 1;
-> end while;
-> end;
-> $$
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
mysql> call testdata();
Query OK, 1 row affected (4 min 6.86 sec)mysql>
mysql> insert into tb_innodb
-> select * from tb_myisam;
Query OK, 5000000 rows affected (1 min 31.47 sec)
Records: 5000000 Duplicates: 0 Warnings: 0mysql>
mysql> select count(1) from tb_innodb;
+----------+
| count(1) |
+----------+
| 5000000 |
+----------+
1 row in set (7.75 sec)mysql> select count(id) from tb_innodb;
+-----------+
| count(id) |
+-----------+
| 5000000 |
+-----------+
1 row in set (7.17 sec)mysql> select count(name) from tb_innodb;
+-------------+
| count(name) |
+-------------+
| 5000000 |
+-------------+
1 row in set (6.22 sec)mysql> select count(*) from tb_innodb;
+----------+
| count(*) |
+----------+
| 5000000 |
+----------+
1 row in set (6.34 sec)mysql>
mysql> select count(1) from tb_myisam;
+----------+
| count(1) |
+----------+
| 5000000 |
+----------+
1 row in set (0.00 sec)mysql> select count(id) from tb_myisam;
+-----------+
| count(id) |
+-----------+
| 5000000 |
+-----------+
1 row in set (0.00 sec)mysql> select count(name) from tb_myisam;
+-------------+
| count(name) |
+-------------+
| 5000000 |
+-------------+
1 row in set (1.24 sec)mysql> select count(*) from tb_myisam;
+----------+
| count(*) |
+----------+
| 5000000 |
+----------+
1 row in set (0.00 sec)mysql>
mysql> truncate table tb_innodb;
Query OK, 5000284 rows affected (0.08 sec)mysql> truncate table tb_myisam;
Query OK, 0 rows affected (0.02 sec)mysql>
from a两者的执行速度。
当有大量记录的时候②的速度明显的快于①
当记录很少的时候①的速度快于②
因为查找的时候 都是从系统表里面在查找,a表的内容都是存在系统表里面的。
select * 会查询所有的系统表
当选择条件后,查找的系统表的数目会减少。
count(column) 是不包括NULL的统计
SELECT SQL_NO_CACHE COUNT(XM) FROM JZG1;0.097
SELECT SQL_NO_CACHE COUNT(1) FROM JZG1;0.058
SELECT SQL_NO_CACHE COUNT(*) FROM JZG1;0.071
SELECT SQL_NO_CACHE COUNT(BH) FROM JZG1;0.085
在XM上建立有复合索引,BH上单独索引myisam,MYSQL5.1.32,,10万条记录测试
在XM上建立有复合索引,BH上单独索引
SELECT SQL_NO_CACHE COUNT(XM) FROM JZG1;0.042
SELECT SQL_NO_CACHE COUNT(1) FROM JZG1;0.000
SELECT SQL_NO_CACHE COUNT(*) FROM JZG1;0.000
SELECT SQL_NO_CACHE COUNT(BH) FROM JZG1;0.036在MYISAM引擎下,两者速度一致
在INNODB引擎下,COUNT(1)略快于COUNT(*)
INNODB **********************************Array ( [count(*)] => 1138446 ) 2.555640
-----------------------
Array ( [count(*)] => 1139702 ) 2.20650
-----------------------
Array ( [count(1)] => 1138446 ) 2.591530
-----------------------
Array ( [count(1)] => 1139702 ) 2.056590MYISAM **********************************
Array ( [count(*)] => 1138446 ) 0.622220
-----------------------
Array ( [count(*)] => 1139702 ) 0.000270
-----------------------
Array ( [count(1)] => 1138446 ) 0.568510
-----------------------
Array ( [count(1)] => 1139702 ) 0.000380
+------------+
| table_rows |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)