比较下 count(*) 和 limit 1
比较下 count(*) 和 limit 1
场景：在删除科目ID的时候，想检查下科目ID是否有产生记录，比较下 count(*) 和 limit 1 的执行效率；
数据库版本：
mysql> SELECT @@VERSION;
+-----------+
| @@VERSION |
+-----------+
| 8.0.35 |
+-----------+
1 row in set (0.00 sec)
mysql>
创建测试表
CREATE TABLE `records` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`subject_id` int NOT NULL COMMENT '科目ID',
`amount` decimal(15,2) DEFAULT NULL COMMENT '金额',
PRIMARY KEY (`id`) USING BTREE,
KEY `record_subject_id_index` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
定义生成数据的存储过程：
mysql> DELIMITER $$
mysql> CREATE PROCEDURE records_procedure(IN total INT)
-> BEGIN
-> DECLARE j INT DEFAULT 1;
-> DECLARE subject_id INT DEFAULT 1;
-> DECLARE amount INT DEFAULT 1;
-> SET @i = (SELECT IFNULL(MAX(id), 0) FROM records);
-> SET AUTOCOMMIT = 0;
-> WHILE (j <= total) DO
-> SET @val = @i + j;
-> SELECT RAND()*100 INTO subject_id;
-> SELECT RAND()*10000 INTO amount;
-> INSERT INTO records(`id`, `subject_id`, `amount`) VALUES(@val, subject_id, amount);
-> SET j=j+1;
-> END WHILE;
-> SET AUTOCOMMIT = 1;
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql>
生成 100 万条数据：
mysql> CALL records_procedure(1000000);
Query OK, 0 rows affected (31.63 sec)
mysql>
mysql> SELECT COUNT(*) FROM records;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.03 sec)
mysql>
查出 subject_id 最多的前 10 个：
mysql> SELECT subject_id, COUNT(subject_id) AS si FROM records GROUP BY subject_id ORDER BY si DESC LIMIT 10;
+------------+-------+
| subject_id | si |
+------------+-------+
| 63 | 10323 |
| 96 | 10249 |
| 67 | 10235 |
| 73 | 10235 |
| 13 | 10212 |
| 97 | 10167 |
| 34 | 10166 |
| 11 | 10145 |
| 88 | 10138 |
| 52 | 10137 |
+------------+-------+
10 rows in set (0.13 sec)
mysql>
统计下 count 和 limit 1 的执行时间：
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT COUNT(*) FROM records;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.03 sec)
mysql> SELECT COUNT(*) FROM records WHERE subject_id = 63;
+----------+
| COUNT(*) |
+----------+
| 10323 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM records WHERE subject_id = 63 LIMIT 1;
+----+------------+---------+
| id | subject_id | amount |
+----+------------+---------+
| 54 | 63 | 7113.00 |
+----+------------+---------+
1 row in set (0.00 sec)
mysql> set profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
看下 SQL 的执行时间：
mysql> show profiles;
+----------+------------+-----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------+
| 1 | 0.02733025 | SELECT COUNT(*) FROM records |
| 2 | 0.00225250 | SELECT COUNT(*) FROM records WHERE subject_id = 63 |
| 3 | 0.00065800 | SELECT * FROM records WHERE subject_id = 63 LIMIT 1 |
+----------+------------+-----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql>
再插入 100 万条数据：
mysql> CALL records_procedure(1000000);
Query OK, 0 rows affected (31.31 sec)
mysql>
统计下SQL执行时间：
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT COUNT(*) FROM records;
+----------+
| COUNT(*) |
+----------+
| 2000000 |
+----------+
1 row in set (0.05 sec)
mysql> SELECT subject_id, COUNT(subject_id) AS si FROM records GROUP BY subject_id ORDER BY si DESC LIMIT 10;
+------------+-------+
| subject_id | si |
+------------+-------+
| 52 | 20382 |
| 97 | 20332 |
| 96 | 20299 |
| 67 | 20277 |
| 39 | 20264 |
| 30 | 20226 |
| 25 | 20224 |
| 61 | 20223 |
| 95 | 20193 |
| 31 | 20190 |
+------------+-------+
10 rows in set (0.30 sec)
mysql> SELECT COUNT(*) FROM records WHERE subject_id = 52;
+----------+
| COUNT(*) |
+----------+
| 20382 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM records WHERE subject_id = 52 LIMIT 1;
+----+------------+---------+
| id | subject_id | amount |
+----+------------+---------+
| 5 | 52 | 5956.00 |
+----+------------+---------+
1 row in set (0.00 sec)
mysql> set profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
再看下 SQL 的执行时间：
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------+
| 1 | 0.02733025 | SELECT COUNT(*) FROM records |
| 2 | 0.00225250 | SELECT COUNT(*) FROM records WHERE subject_id = 63 |
| 3 | 0.00065800 | SELECT * FROM records WHERE subject_id = 63 LIMIT 1 |
| 4 | 0.05361075 | SELECT COUNT(*) FROM records |
| 5 | 0.30070025 | SELECT subject_id, COUNT(subject_id) AS si FROM records GROUP BY subject_id ORDER BY si DESC LIMIT 10 |
| 6 | 0.00319900 | SELECT COUNT(*) FROM records WHERE subject_id = 52 |
| 7 | 0.00067850 | SELECT * FROM records WHERE subject_id = 52 LIMIT 1 |
+----------+------------+-------------------------------------------------------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
mysql>
