MySQL的COUNT()函數利用索引進行計算

現執行如下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)


 | 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 | 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 |  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
全表其實是在主鍵樹上按此索引樹遍歷元組(故實際上在“用索引”)
3 注意執行計劃中都有“”,表明如下:

:
The query contained only aggregate functions (MIN(), MAX()) that were all 
resolved , or COUNT(*) for MyISAM, and no GROUP BY clause.
 The optimizer determined that only one row should be returned. 
補充說明:
這句話沒有把所有情況說出來,對于InnoDBCOUNT(*)也可以得到“Select tables optimized away”,參見E10。
參見:《MySQL---聚集函數的優化詳解   http://aoyel.com/t/5678260bc5e91  
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       |    | 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       |    | 5       | NULL |    8 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


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)


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)

聲明:文章"MySQL的COUNT()函數利用索引進行計算"為相王科技 Array原創文章,轉載請注明出處,謝謝合作!
国产精品免费视频每日更新_久久AV七七AV_被多人玩弄的烂货_国产片手机永久免费观看