为什么SELECT COUNT(*)比MySQL中带有WHERE子句的SELECT慢得多

Question

这是表格。

CREATE TABLE `refdata` (
`id` VARCHAR(36) NOT NULL COLLATE 'utf8_unicode_ci',
`uid` INT NOT NULL,
`data` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
`ref_akid` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
`ref_version` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
`remote_addr` VARCHAR(32) NOT NULL COLLATE 'utf8_unicode_ci',
`fetched_at` TIMESTAMP NULL,
`created_at` TIMESTAMP NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `link_to_user` (`uid`) USING BTREE,
CONSTRAINT `link_to_user` FOREIGN KEY (`uid`) REFERENCES `selfdb`.`admin_users` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

id是一个UUID，数据大约占用1Mb。所以这个表大约是1.3G，有1.3M行。结果如下。

mysql> SELECT COUNT(*) FROM refdata;
+----------+
| COUNT(*) |
+----------+
|  1381991 |
+----------+
1 row in set (1 min 9.49 sec)
mysql> SELECT COUNT(*) FROM refdata WHERE uid > 0;
+----------+
| COUNT(*) |
+----------+
|  1382097 |
+----------+
1 row in set (0.29 sec)

为什么前一个查询比第二个查询慢得多？

附言：有一个应用程序正在运行并插入数据。表锁有什么原因吗？

---

以下是解释。

mysql> explain SELECT COUNT(*) FROM refdata;
+----+-------------+------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | refdata    | NULL       | index | NULL          | link_to_user | 4       | NULL | 1387770 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT COUNT(*) FROM refdata WHERE uid > 0;
+----+-------------+------------+------------+-------+---------------+---------------+---------+------+--------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key           | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+---------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | refdata    | NULL       | range | link_to_user  | link_to_user  | 4       | NULL | 693885 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+---------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * FROM refdata;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | refdata    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1387771 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * FROM refdata WHERE uid > 0;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | refdata    | NULL       | ALL  | link_to_user  | NULL | NULL    | NULL | 1387774 |    50.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Answer 1

这是InnoDB表的预期行为。

InnoDB引擎支持事务。因此，在执行COUNT(*(时，必须对表进行完全扫描，以避免对尚未提交的行进行计数。

但是，当您使用WHERE指定筛选条件时，只需要对筛选的行进行计数，并且由于UID在您的情况下是一个索引列，因此速度要快得多。