用户名:
密 码: 记住
您当前的位置:首页 > 网络编程 > mysql教程

mysql中count(1)与count(*)比较

时间:2015-01-23  来源:西部数据  作者:西部数据

sql调优,主要是考虑降低:consistent gets和physical reads的数量.

count(1)与count(*)比较:

如果你的数据表没有主键,那么count(1)比count(*)快,如果有主键的话,那主键(联合主键)作为count的条件也比count(*)要快,如果你的表只有一个字段的话那count(*)就是最快的啦,count(*) count(1) 两者比较,主要还是要count(1)所相对应的数据字段.

如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的,因为count(*),自动会优化指定到那一个字段,所以没必要去count(?),用count(*),sql会帮你完成优化的.

count详解:count(*)将返回表格中所有存在的行的总数包括值为null的行,然而count(列名)将返回表格中除去null以外的所有行的总数,有默认值的列也会被计入.

distinct 列名,得到的结果将是除去值为null和重复数据后的结果

总结三条经验:

1.任何情况下SELECT COUNT(*) FROM tablename是最优选择;

2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = 'value’这种查询;

3.杜绝SELECT COUNT(COL) FROM tablename的出现。

国个找一文章不懂英文没译,代码如下:

  1. COUNT(*) vs COUNT(col) 
  2. 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. 
  3.  
  4. Lets look at the following series of examples: 
  5.  
  6.  CREATE TABLE `fact` ( 
  7.  
  8.   `i` int(10) unsigned NOT NULL
  9.   `val` int(11) default NULL
  10.   `val2` int(10) unsigned NOT NULL
  11.   KEY `i` (`i`) 
  12. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 
  13. mysql> select count(*) from fact; 
  14. +———-+ 
  15. count(*) | 
  16. +———-+ 
  17. |  7340032 | 
  18. +———-+ 
  19. 1 row in set (0.00 sec) 
  20. mysql> select count(val) from fact; 
  21. +————+ 
  22. count(val) | 
  23. +————+ 
  24. |    7216582 | 
  25. +————+ 
  26. 1 row in set (1.17 sec) 
  27. mysql> select count(val2) from fact; 
  28. +————-+ 
  29. count(val2) | 
  30. +————-+ 
  31. |     7340032 | 
  32. +————-+ 
  33. 1 row in set (0.00 sec) 

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,代码如下:

  1. mysql> select count(*) from fact where i<10000; 
  2.  
  3. +———-+ 
  4. count(*) | 
  5. +———-+ 
  6. |   733444 | 
  7. +———-+ 
  8. 1 row in set (0.40 sec) 
  9. mysql> explain select count(*) from fact where i<10000 G 
  10. *************************** 1. row *************************** 
  11.            id: 1 
  12.   select_type: SIMPLE 
  13.         table: fact 
  14.          type: range 
  15. possible_keys: i 
  16.           key: i 
  17.       key_len: 4 
  18.           ref: NULL 
  19.          rows: 691619 
  20.         Extra: Using where; Using index 
  21. 1 row in set (0.00 sec) 
  22. mysql> select count(val) from fact where i<10000; 
  23. +————+ 
  24. count(val) | 
  25. +————+ 
  26. |     720934 | 
  27. +————+ 
  28. 1 row in set (1.29 sec) 
  29. mysql> explain select count(val) from fact where i<10000 G 
  30. *************************** 1. row *************************** 
  31.            id: 1 
  32.   select_type: SIMPLE 
  33.         table: fact 
  34.          type: range 
  35. possible_keys: i 
  36.           key: i 
  37.       key_len: 4 
  38.           ref: NULL 
  39.          rows: 691619 
  40.         Extra: Using where 
  41. 1 row in set (0.00 sec) 
  42. mysql> select count(val2) from fact where i<10000; 
  43. +————-+ 
  44. count(val2) | 
  45. +————-+ 
  46. |      733444 | 
  47. +————-+ 
  48. 1 row in set (1.30 sec) 
  49. mysql> explain select count(val2) from fact where i<10000 G 
  50. *************************** 1. row *************************** 
  51.            id: 1  //phpfensi.com 
  52.   select_type: SIMPLE 
  53.         table: fact 
  54.          type: range 
  55. possible_keys: i 
  56.           key: i 
  57.       key_len: 4 
  58.           ref: NULL 
  59.          rows: 691619 
  60.         Extra: Using where 
  61. 1 row in set (0.00 sec) 

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.代码如下:

  1. mysql> alter table fact drop key i, add key(i,val); 
  2. Query OK, 7340032 rows affected (37.15 sec) 
  3. Records: 7340032  Duplicates: 0  Warnings: 0 
  4. mysql> select count(val) from fact where i<10000; 
  5. +————+ 
  6. count(val) | 
  7. +————+ 
  8. |     720934 | 
  9. +————+ 
  10. 1 row in set (0.78 sec)
来顶一下
返回首页
返回首页
推荐资讯
WiFi太不安全:7岁女孩11分钟内入侵公共网络 WiFi太不安全:7岁女孩11分钟内入侵近期刚刚发布研究说WiFi网络能获得人们手机里多少私人信息,
不服跑个分?人工智能也出现“刷分”乱象 不服跑个分?人工智能也出现“刷分2014年,人工智能领域突然爆发,成为了科研和科技创业的热门
相关文章
    无相关信息
栏目更新
栏目热门