mysql 中select count1_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)，效率相差不大