mysql中count(1)與count(*)比較

本文章來給各位同學介紹有mysql中count(1)與count(*)性能比較，希望此方法對各位同學會有所幫助。

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的出現。

國個找一文章不懂英文沒譯

 代碼如下 復制代碼

COUNT(*) vs COUNT(col)
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:

 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 |
+————-+
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:

 

 代碼如下 復制代碼

 

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
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.

 代碼如下 復制代碼

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 |
+————+
1 row in set (0.78 sec)