SQL的Count函数优化

题记
产品发现内部资源平台查询资源列表接口很慢,然后我开始排查问题,使用Jaeger(阿里云Golang Agent)查询发现select count(*)
一行sql语句运行了300ms,然后EXPLAIN语句发现全表扫描,然后开始查资料优化count(*)的速度,最后写了这篇文章。
什么是count()函数
count()是一个聚合函数,函数的参数不仅可以是字段名,也可以是其他任意表达式,该函数作用是统计符合查询条件的记录中,函数指定的参数不为NULL的记录有多少个。
哪种count性能最好
count函数执行过程
https://github.com/copilot/share/80464086-0864-8412-8001-3604a08c2165
如何优化
示例
创建一个MySQL的数据库(InnoDB),然后创建一张表,插入500w条简单数据,然后执行count(*)函数,然后EXPLAIN分析SQL语句的执行
1 | CREATE TABLE `test` ( |
二级索引和主键索引所占缓存区内容大小如下:
先查主键索引,然后创建二级索引,之后重启在查二级索引所占缓存区大小。
MySQL 的 select count(*)
在底层实现统计的过程中通过二级索引优于主键索引优于全表扫描,这是因为二级索引只缓存主键列和索引列,主键索引几乎缓存了所有的行记录,前者势必比后者缓存的内容少的多,当然计算的效率肯定要快的多(InnoDB的二级索引不仅体积小,而且通常更容易完全加载到内存中,减少了I/O操作)。
二级索引和主键索引所占磁盘空间大小如下:
优化手段
本文所讲的二级索引(注意:在某些情况下优化器可能不选择最优索引,此时可以通过FORCE INDEX提示来指定索引)
使用MyISAM存储引擎,自带计数器(纯负面优化,这哥们不支持事务啊)
数据仓库等其他可接入的系统来完成此工作(没用过,不评价)
缓存层优化:
- 应用层缓存:Redis计数器(INCR/DECR操作、定期同步MySQL数据)
- MySQL内部缓存(优化buffer pool配置、适当调整innodb_buffer_pool_size)
SQL触发器计数优化
MySQL 8.0 的并行查询,适用于大表查询
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;历史数据迁移,就不让你查询那么多数据了,这个有点霸道了,可以换着说法,根据业务需求,历史数据迁移,只保留某些数据(按规则)
分库分表,不多说什么,还是物理上的优化
服务器硬件资源提升,比如 SSD 硬盘等(治标不治本)
参考文章
- [MySQL优化:优化 select count()](https://learn.lianglianglee.com/文章/MySQL优化:优化 select count().md)
- count(*) 和 count(1) 有什么区别?哪个性能最好?
- 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 进行许可。