萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql中count(id) count(1) count(*)的用法區別

mysql中count(id) count(1) count(*)的用法區別

在mysql中很多朋友在寫統計count時每次可能都不一樣如,count(id) count(1) count(*)這三個統計出來的結果是一樣的,但它們之間的性能有比較過嗎?下面我來給大家舉例說明一下。

表結構如下:

 代碼如下 復制代碼 mysql> show create table userG;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `pwd` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `sex` enum('F','M','N') NOT NULL DEFAULT 'N',
  `addres` varchar(100) NOT NULL,
  `tag` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5000003 DEFAULT CHARSET=utf8 COMMENT='用戶表'
1 row in set (0.00 sec)

下面做一下explain: 1、count(id)

 代碼如下 復制代碼 mysql> select count(id) from user;
+-----------+
| count(id) |
+-----------+
|   5000002 |
+-----------+
1 row in set (1.93 sec)mysql> explain select count(id) from user;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | name | 152     | NULL | 4998401 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+1 row in set (0.05 sec)2、count(1)
mysql> select count(1) from user;
+----------+
| count(1) |
+----------+
|  5000002 |
+----------+
1 row in set (0.90 sec)mysql> explain select count(1) from user;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | name | 152     | NULL | 4998401 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)3、count(*)
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  5000002 |
+----------+
1 row in set (0.87 sec)mysql> explain select count(*) from user;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | name | 152     | NULL | 4998401 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

比較三個查詢,explain的結果一模一樣,這說明這三個的效率是一樣的嗎?
再看看下面三個操作,帶上where條件 sex='F',以下三個操作中間均會重啟mysql服務。

 代碼如下 復制代碼 1、count(id)
mysql> select count(id) from user where sex='F';
+-----------+
| count(id) |
+-----------+
|   1681259 |
+-----------+
1 row in set (18.87 sec)
mysql> explain select count(id) from user where sex='F';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4998401 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)2、count(1)
mysql> select count(1) from user where sex='F';
+----------+
| count(1) |
+----------+
|  1681259 |
+----------+
1 row in set (4.81 sec)
mysql> explain select count(1) from user where sex='F';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4998401 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)3、count(*)
mysql> select count(*) from user where sex='F';
+----------+
| count(*) |
+----------+
|  1681259 |
+----------+
1 row in set (4.69 sec)
mysql> explain select count(*) from user where sex='F';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4998401 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

以上三種查詢有一些差別,其中count(id)用時最長,count(*)比count(1)速度要稍微快一點。
兩組查詢,帶條件的都沒有使用到索引,掃描了全表;而沒有條件的則使用了索引name。

所以在應用中盡量不使用count(*)和count(1),杜絕使用count(primary_key)。

網上有很多資料說

沒有主鍵,count(1)比count(*)快;

有主鍵的話,count(primary_key)最快,但是在上面的測試中發現,count(primary_key)是最慢的,難道是測試不准確?這個有待驗證。

如果表只有一個字段,則count(*)是最快的。

說明:

count(1)中的1並不是指第一個column;

count(*)和count(1)一樣,包括對值為NULL的統計;

count(column)不包括對值為NULL的統計,這裡的column指的不是primary_key;

copyright © 萬盛學電腦網 all rights reserved