优米格
分享有营养的

记一次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重要参数优化
赞(1)
未经允许禁止转载:优米格 » 记一次MySQL时间排序/时间过滤查询速度优化过程(innodb_buffer_pool_size)

评论 抢沙发

合作&反馈&投稿

商务合作、问题反馈、投稿,欢迎联系

广告合作侵权联系