记一次对MySQL按时间查询SQL的速度优化过程。
一、环境信息
- MySQL: 8.0.22;
二、待优化SQL
SELECT
*
FROM
sys_leave_records r
LEFT JOIN sys_dept d ON r.dept_id = d.dept_id
where ( d.dept_id = 1703 or find_in_set(1703, d.ancestors) )
and r.start_time >= STR_TO_DATE('2022-12-01','%Y-%m-%d')
and r.start_time <= STR_TO_DATE('2022-12-30','%Y-%m-%d')
order by r.create_time desc
其中,sys_dept表的ancestors字段是”0,105,1703
“这种格式的字符类型,存储的是单位的层级结构;sys_leave_records表id主键是整形自动增长的;sys_leave_records表start_time和create_time字段都是日期类型。关联键dept_id已添加索引。
这个SQL是想查询’2022-12-01’到’2022-12-30’之间的请假数据,并按create_time字段倒叙排列。
三、优化过程
sys_leave_records表的数据量大约在62万左右,sys_dept表数据不到1000条。上面的SQL查询,分页查询,花费30s+。
至于为何这么慢,开始的优化思路考虑了find_in_set优化、分页查询优化(因为id是自增的,考虑先按id倒叙,查询出id集合,再根据id进行查询最终结果),但是效果不明显。
主要影响的地方是时间字段,按时间范围查询和按时间排序,尝试去掉时间过滤条件速度就会明显提高。使用explain
排查发现是全表扫描,加了索引不起作用。
最终的优化点落在时间查询/按时间排序的优化上。搜索了一下,对mysql的innodb_buffer_pool_size
参数优化,可以提高缓存大小,从而提高查询性能。
四、innodb_buffer_pool_size
参数调整
innodb_buffer_pool_size
参数表示缓冲池大小,一般来说默认值是128M。使用下面命令可以查询当前系统的设置:
show global variables like 'innodb_buffer_pool_size';
需要注意的是:缓存池(buffer pool)越大,对物理内存会造成压力,因此,在机器内存有限的情况下,合理调整参数大小,避免机器的性能下降。
mysql只是建议你最多用到物理内存的80%, 主要是出于以下几点考虑:
- 跟操作系统竞争内存, 有可能会导致操作系统的频繁缺页, 导致整个机器的性能下降;
- mysql会为它的一些其它数据结构保留部分内存, 所以实际占用内存会比
buffer pool
多个10%左右; buffer pool
一般需要分配连续内存, 否则在windows操作系统中会有一些问题;buffer pool
的初始化时间是跟它的大小成正比的;
4.1 为什么innodb_buffer_pool_size会导致sql执行慢?
当buffer pool不够用的时候, 大多数的数据请求都会落到磁盘数, 磁盘IO性能会比内存读取高出很多个数量级。
4.2 为什么innodb_buffer_pool_size只影响到了降序排序的sql?
参见:为什么innodb_buffer_pool_size只影响到了降序排序的sql。
我将innodb_buffer_pool_size
参数从默认的128M调整为1280M,上面的SQL耗时0.3秒,提升了差不多1000倍。
参考: