MySQL select count(*)、select count(1)效率对比

   在开发过程中，遇到select count优化问题，现结果记录如下：

1、不加条件的select count(*)与select count(*)对比

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|    60900 |
+----------+
1 row in set (0.05 sec)

执行计划：

mysql> explain select count(*) from test;
+----+-------------+----------------+-------+---------------+-------------------
-+---------+------+--------+-------------+
| id | select_type | table          | type  | possible_keys | key
 | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+-------+---------------+-------------------
-+---------+------+--------+-------------+
|  1 | SIMPLE      | test | index | NULL          | Idx_test
 | 9       | NULL | 108653 | Using index |
+----+-------------+----------------+-------+---------------+-------------------
-+---------+------+--------+-------------+
1 row in set (0.00 sec)

 

mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|    60900 |
+----------+
1 row in set (0.05 sec)

执行计划：

mysql> explain select count(1) from test;
+----+-------------+----------------+-------+---------------+-------------------
-+---------+------+--------+-------------+
| id | select_type | table          | type  | possible_keys | key
 | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+-------+---------------+-------------------
-+---------+------+--------+-------------+
|  1 | SIMPLE      | test | index | NULL          | Idx_test
 | 9       | NULL | 108653 | Using index |
+----+-------------+----------------+-------+---------------+-------------------
-+---------+------+--------+-------------+

 

select count(*) 于 select count(1)在不加条件的情况下效率相同，同时使用了索引。

 

2、增加条件查询：

mysql> select count(*) from test where user_id = 0;
+----------+
| count(*) |
+----------+
|       34 |
+----------+
1 row in set (5.33 sec)

执行计划：

mysql> explain select count(*) from test where user_id = 0;
+----+-------------+----------------+------+---------------+------+---------+---
---+-------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | re
f  | rows  | Extra       |
+----+-------------+----------------+------+---------------+------+---------+---
---+-------+-------------+
|  1 | SIMPLE      | test | ALL  | NULL          | NULL | NULL    | NU
LL | 77009 | Using where |
+----+-------------+----------------+------+---------------+------+---------+---
---+-------+-------------+
1 row in set (0.00 sec)

 

mysql> select count(1) from test where user_id = 0;
+----------+
| count(1) |
+----------+
|       34 |
+----------+
1 row in set (5.20 sec)

执行计划：

mysql> explain select count(1) from test where user_id = 0;
+----+-------------+----------------+------+---------------+------+---------+---
---+-------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | re
f  | rows  | Extra       |
+----+-------------+----------------+------+---------------+------+---------+---
---+-------+-------------+
|  1 | SIMPLE      | test | ALL  | NULL          | NULL | NULL    | NU
LL | 77009 | Using where |
+----+-------------+----------------+------+---------------+------+---------+---
---+-------+-------------+
1 row in set (0.00 sec)

 

select count(*)与select count(1)在增加条件的情况下使用了全表扫表

 

3、为where条件增加索引

mysql> create index user_id on test(user_id);

 

mysql> select count(*) from test where user_id = 0;
+----------+
| count(*) |
+----------+
|       34 |
+----------+
1 row in set (0.00 sec)

执行计划：

mysql> explain select count(*) from test where user_id = 0;
+----+-------------+----------------+------+---------------+---------+---------+
-------+------+--------------------------+
| id | select_type | table          | type | possible_keys | key     | key_len |
 ref   | rows | Extra                    |
+----+-------------+----------------+------+---------------+---------+---------+
-------+------+--------------------------+
|  1 | SIMPLE      | test | ref  | user_id       | user_id | 9       |
 const |   34 | Using where; Using index |
+----+-------------+----------------+------+---------------+---------+---------+
-------+------+--------------------------+
1 row in set (0.00 sec)

 

mysql> select count(1) from test where user_id = 0;
+----------+
| count(1) |
+----------+
|       34 |
+----------+
1 row in set (0.00 sec)

执行计划：

mysql> explain select count(1) from test where user_id = 0;
+----+-------------+----------------+------+---------------+---------+---------+
-------+------+--------------------------+
| id | select_type | table          | type | possible_keys | key     | key_len |
 ref   | rows | Extra                    |
+----+-------------+----------------+------+---------------+---------+---------+
-------+------+--------------------------+
|  1 | SIMPLE      | test | ref  | user_id       | user_id | 9       |
 const |   34 | Using where; Using index |
+----+-------------+----------------+------+---------------+---------+---------+
-------+------+--------------------------+
1 row in set (0.00 sec)

select count(*) 与select count(*)在为条件建立索引的情况加，使用索引，效率提高。

 

总结：在不使用where的情况下默认使用主键索引，如使用where，则需合理创建索引提高效率。同时测试了select count(col)，效率相差不大