今天在查询一个表行数的时候，发现count(1)和count(*)执行效率居然是一样的。这跟oracle还是有区别的。遂查看两种方式的执行计划：
mysql> select count(1) from customer;+----------+| count(1) |+----------+| 150000 |+----------+1 row in set (0.03 sec)mysql> flush tables;query ok, 0 rows affected (0.00 sec)mysql> select count(*) from customer;+----------+| count(*) |+----------+| 150000 |+----------+1 row in set (0.03 sec)
查看执行计划：
mysql> explain select count(1) from customer;+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-------------+| 1 | simple | customer | index | null | i_c_nationkey | 5 | null | 151191 | using index |+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-------------+1 row in set (0.00 sec)mysql> explain select count(*) from customer;+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-------------+| 1 | simple | customer | index | null | i_c_nationkey | 5 | null | 151191 | using index |+----+-------------+----------+-------+---------------+---------------+---------+------+--------+-------------+1 row in set (0.00 sec)mysql> show index from customer;+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment |+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| customer | 0 | primary | 1 | c_custkey | a | 150525 | null | null | | btree | | || customer | 1 | i_c_nationkey | 1 | c_nationkey | a | 47 | null | null | yes | btree | | |+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.08 sec)
发现不管是count(1)或count(*)都是走的i_c_nationkey这个索引。平时我们检索数据的时候肯定是主键索引效率高，那么我们强制主键索引来看看：
mysql> select count(*) from customer force index(primary);+----------+| count(*) |+----------+| 150000 |+----------+1 row in set (0.68 sec)mysql> explain select count(*) from customer force index(primary);+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+| 1 | simple | customer | index | null | primary | 4 | null | 150525 | using index |+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+1 row in set (0.00 sec)
可以看到走主键索引的时候效率比较差。那么是为什么呢。
平时我们检索一列的时候，基本上等值或范围查询，那么索引基数大的索引必然效率很高。但是在做count(*)的时候并没有检索具体的一行或者一个范围。那么选择基数小的索引对
count操作效率会更高。在做count操作的时候，mysql会遍历每个叶子节点，所以基数越小，效率越高。mysql非聚簇索引叶子节点保存的主键id，所以需要检索两遍索引。但是这里相对于遍历主键索引。及时检索两遍索引效率也比单纯的检索主键索引快。
那么再以一个表作为证明：
mysql> explain select count(*) from lineitem;+----+-------------+----------+-------+---------------+--------------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+----------+-------+---------------+--------------+---------+------+---------+-------------+| 1 | simple | lineitem | index | null | i_l_shipdate | 4 | null | 6008735 | using index |+----+-------------+----------+-------+---------------+--------------+---------+------+---------+-------------+1 row in set (0.00 sec)mysql> show index from lineitem;+----------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment |+----------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| lineitem | 0 | primary | 1 | l_orderkey | a | 2997339 | null | null | | btree | | || lineitem | 0 | primary | 2 | l_linenumber | a | 5994679 | null | null | | btree | | || lineitem | 1 | i_l_shipdate | 1 | l_shipdate | a | 5208 | null | null | yes | btree | | || lineitem | 1 | i_l_suppkey_partkey | 1 | l_partkey | a | 428191 | null | null | yes | btree | | || lineitem | 1 | i_l_suppkey_partkey | 2 | l_suppkey | a | 1998226 | null | null | yes | btree | | || lineitem | 1 | i_l_partkey | 1 | l_partkey | a | 461129 | null | null | yes | btree | | || lineitem | 1 | i_l_suppkey | 1 | l_suppkey | a | 19213 | null | null | yes | btree | | || lineitem | 1 | i_l_receiptdate | 1 | l_receiptdate | a | 17 | null | null | yes | btree | | || lineitem | 1 | i_l_orderkey | 1 | l_orderkey | a | 2997339 | null | null | | btree | | || lineitem | 1 | i_l_orderkey_quantity | 1 | l_orderkey | a | 1998226 | null | null | | btree | | || lineitem | 1 | i_l_orderkey_quantity | 2 | l_quantity | a | 5994679 | null | null | yes | btree | | || lineitem | 1 | i_l_commitdate | 1 | l_commitdate | a | 7836 | null | null | yes | btree | | |+----------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+12 rows in set (0.96 sec)
这里一看l_shipdate并不是基数最小的呀，殊不知这个统计信息是不准确的。我们用sql看一下。
mysql> select count(distinct(l_shipdate)) from lineitem;+-----------------------------+| count(distinct(l_shipdate)) |+-----------------------------+| 2526 |+-----------------------------+1 row in set (0.01 sec)
那么比他小的那些列呢？
mysql> select count(distinct(l_receiptdate)) from lineitem;+--------------------------------+| count(distinct(l_receiptdate)) |+--------------------------------+| 2554 |+--------------------------------+1 row in set (0.01 sec)
其他就不看了，这里再次说明mysql选择了基数小的索引。