记一次MySQL时间排序/时间过滤查询速度优化过程(innodb_buffer_pool_size)

记一次对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';

-- 1280M
set global innodb_buffer_pool_size = 1342177280;

需要注意的是：缓存池(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倍。

参考：

1. 一次mysql order by desc 慢的排查；
2. MySQL分页查询优化；
3. MySQL大数据量分页优化（八）；
4. MySQL调优之innodb_buffer_pool_size大小设置；
5. MySQL 优化系列（1）– InnoDB重要参数优化；