记一次对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倍。
参考：
