比较下 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>

‍