SQL的Count函数优化

纸鸢 Lv4

题记

​ 产品发现内部资源平台查询资源列表接口很慢,然后我开始排查问题,使用Jaeger(阿里云Golang Agent)查询发现select count(*)一行sql语句运行了300ms,然后EXPLAIN语句发现全表扫描,然后开始查资料优化count(*)的速度,最后写了这篇文章。

什么是count()函数

​ count()是一个聚合函数,函数的参数不仅可以是字段名,也可以是其他任意表达式,该函数作用是统计符合查询条件的记录中,函数指定的参数不为NULL的记录有多少个。

哪种count性能最好

img

count函数执行过程

https://github.com/copilot/share/80464086-0864-8412-8001-3604a08c2165

如何优化

示例

​ 创建一个MySQL的数据库(InnoDB),然后创建一张表,插入500w条简单数据,然后执行count(*)函数,然后EXPLAIN分析SQL语句的执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) NOT NULL DEFAULT '0',
`password` char(120) NOT NULL DEFAULT '',
`name` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


DELIMITER //

CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 1;

-- Disable unique checks and foreign key checks for faster insertion
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
SET autocommit=0;

START TRANSACTION;

WHILE i <= 5000000 DO
INSERT INTO test (age, password, name)
VALUES (
FLOOR(1 + RAND() * 1000000), -- Random number between 1 and 1000000 for age
CONCAT(LEFT(UUID(), 120)), -- Random string for password (120 chars)
CONCAT(LEFT(UUID(), 60)) -- Random string for name (60 chars)
);

SET i = i + 1;

-- Commit every 10000 records to avoid transaction log overflow
IF i % 10000 = 0 THEN
COMMIT;
START TRANSACTION;
END IF;
END WHILE;

COMMIT;

-- Re-enable unique checks and foreign key checks
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;
SET autocommit=1;
END //

DELIMITER ;

-- Execute the procedure
CALL insert_test_data();

-- Drop the procedure after use
DROP PROCEDURE IF EXISTS insert_test_data;

img

img

二级索引和主键索引所占缓存区内容大小如下:

先查主键索引,然后创建二级索引,之后重启在查二级索引所占缓存区大小。

img

img

img

​ MySQL 的 select count(*) 在底层实现统计的过程中通过二级索引优于主键索引优于全表扫描,这是因为二级索引只缓存主键列和索引列,主键索引几乎缓存了所有的行记录,前者势必比后者缓存的内容少的多,当然计算的效率肯定要快的多(InnoDB的二级索引不仅体积小,而且通常更容易完全加载到内存中,减少了I/O操作)。

二级索引和主键索引所占磁盘空间大小如下:

img

优化手段

  1. 本文所讲的二级索引(注意:在某些情况下优化器可能不选择最优索引,此时可以通过FORCE INDEX提示来指定索引)

  2. 使用MyISAM存储引擎,自带计数器(纯负面优化,这哥们不支持事务啊)

  3. 数据仓库等其他可接入的系统来完成此工作(没用过,不评价)

  4. 缓存层优化:

    • 应用层缓存:Redis计数器(INCR/DECR操作、定期同步MySQL数据)
    • MySQL内部缓存(优化buffer pool配置、适当调整innodb_buffer_pool_size)
  5. SQL触发器计数优化

  6. MySQL 8.0 的并行查询,适用于大表查询

    img

    1
    2
    3
    4
    5
    6
    -- 启用并行查询
    SET SESSION innodb_parallel_read_threads = 4;

    -- 优化COUNT查询
    SELECT /*+ SET_VAR(innodb_parallel_read_threads = 4) */
    COUNT(*) FROM test;
  7. 历史数据迁移,就不让你查询那么多数据了,这个有点霸道了,可以换着说法,根据业务需求,历史数据迁移,只保留某些数据(按规则)

  8. 分库分表,不多说什么,还是物理上的优化

  9. 服务器硬件资源提升,比如 SSD 硬盘等(治标不治本)

参考文章

  1. [MySQL优化:优化 select count()](https://learn.lianglianglee.com/文章/MySQL优化:优化 select count().md)
  2. count(*) 和 count(1) 有什么区别?哪个性能最好?
  3. mysql 并行查询_MySQL 8.0新特性-并行查询innodb_parallel_read_threads
  • 标题: SQL的Count函数优化
  • 作者: 纸鸢
  • 创建于 : 2025-03-27 23:34:34
  • 更新于 : 2025-03-29 19:59:27
  • 链接: https://www.youandgentleness.cn/2025/03/27/SQL的Count函数优化/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
评论