mysqlcount(*)会选哪个索引？_MySQL-mysql教程-PHP中文网

mysqlcount(*)会选哪个索引？_MySQL

php中文网

发布： 2016-06-01 11:52:42

原创

1265人浏览过

今天在查询一个表行数的时候，发现count(1)和count(*)执行效率居然是一样的。这跟oracle还是有区别的。遂查看两种方式的执行计划：

中解商务通

实时捕捉一旦访问者打开您的网站，系统会立即显示，这时您就可以查看用户的信息，如：来自搜索引擎关键词、友情链接或直接访问；访问者的IP地址，所在地区，正在访问哪个网页；以及访问者使用的操作系统、浏览器、显示器屏幕分辨率颜色深度等。主动出击变被动为主动，可以主动邀请访问者进行洽谈勾通，帮助客户深入了解您的企业和产品，同时获得对方的采购意向、联系方式等信息。互动交流主动销售和在线客服合二为一，

查看详情

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选择了基数小的索引。