MySQL的COUNT()函数利用索引进行计算

以前写过一篇《 select count(*) 和 select count(1)有什么区别？  》，参见： http://blog.163.com/li_hx/blog/static/18399141320146961258398/

现执行如下SQL并进一步分析如下：
----------------------------------------------

mysql> CREATE TABLE t_count (id INT PRIMARY KEY, uk INT, k1 INT, k2_1 INT, k2_2 INT, col INT, UNIQUE KEY (uk), KEY k1(k1), KEY k2 (k2_1, k2_2
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO t_count values (1,1,1,1,1,1),(2,2,2,2,2,2),(3,3,3,3,3,3),(4,4,4,4,4,4),(5,5,5,5,5,5),(6,6,6,6,6,6),(7,NULL,NULL,NULL,NULL,
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*),COUNT(1),COUNT(id),COUNT(uk),COUNT(k1),COUNT(k2_1),COUNT(k2_2),COUNT(col) FROM t_count;
+----------+----------+-----------+-----------+-----------+-------------+-------------+------------+
| COUNT(*) | COUNT(1) | COUNT(id) | COUNT(uk) | COUNT(k1) | COUNT(k2_1) | COUNT(k2_2) | COUNT(col) |
+----------+----------+-----------+-----------+-----------+-------------+-------------+------------+
|        7 |        7 |         7 |         6 |         6 |           6 |           6 |          6 |
+----------+----------+-----------+-----------+-----------+-------------+-------------+------------+
1 row in set (0.01 sec)
说明：
1 COUNT(*),COUNT(1),COUNT(id)的值一样，表明前2者是“数全部行数的”，不是“不数非NULL值的行”。
2 COUNT(uk),COUNT(k1),COUNT(k2_1),COUNT(k2_2),COUNT(col)的值都是6，表明NULL值不被COUNT函数计算在内，这意味着“数不计NULL值的行数”。

查看如下的执行计划：
mysql> EXPLAIN SELECT COUNT(*) FROM t_count; 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM t_count;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT COUNT(id) FROM t_count;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
说明：
1 以上三个执行计划，都是全表扫描；COUNT(id)说明即使是在主键列上执行COUNT函数，都不能利用索引。
2 但是，MySQL的InnoDB使用的是主键索引树存储结构，遍历全表其实是在主键树上按此索引树遍历元组（故实际上在“用索引”）
3 注意执行计划中都有“Select tables optimized away”，表明如下：

MySQL官方文档解释：
Select tables optimized away:
The query contained only aggregate functions (MIN(), MAX()) that were all 
resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause.
 The optimizer determined that only one row should be returned. 
补充说明：
这句话没有把所有情况说出来，对于InnoDB表COUNT(*)也可以得到“Select tables optimized away”，参见E10。
参见：《MySQL---聚集函数的优化详解 》  http://blog.163.com/li_hx/blog/static/183991413201523153748830/?COLLCC=858502883&COLLCC=3257644771&COLLCC=826142342&COLLCC=306048646&  

mysql> EXPLAIN SELECT COUNT(uk) FROM t_count;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_count | NULL       | index | NULL          | uk   | 5       | NULL |    8 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT COUNT(k1) FROM t_count;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_count | NULL       | index | NULL          | k1   | 5       | NULL |    8 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
说明：
1 以上2个执行计划表明，在索引列上执行COUNT，是可以利用到索引的。

mysql> EXPLAIN SELECT COUNT(k2_1) FROM t_count;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_count | NULL       | index | NULL          | k2   | 10      | NULL |    8 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT COUNT(k2_2) FROM t_count;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_count | NULL       | index | NULL          | k2   | 10      | NULL |    8 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
说明：
1 以上2个执行计划表明，在部分索引列（列是索引的一部分）上执行COUNT，是可以利用到索引的。

mysql> EXPLAIN SELECT COUNT(col) FROM t_count;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_count | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
说明：
1 以上1个执行计划表明，在普通引列上执行COUNT，是不可以利用到索引的。