天天看点

慢查询性能优化实践

作者:JAVA后端架构
慢查询性能优化实践

一、背景

  项目组接到两家客户反馈,部分生产统计报表查询缓慢,甚至某些报表无法查询出1个月的统计数据。收到问题反馈后,运维同学远程至客户服务,验证生产问题现状,以PM工时卡片(公司研发内控平台)形式转至研发生产问题甬道。

  传统站务系统部署于客户本地机房,使用Mysql-5.7.37数据库;这两家客户均已上线多年,软件版本较老;以他们订单表单表为例,均拥有百万量级数据。单站站务的报表具有两个特性,一是以业务驱动,二是满足财务报表要求(交付类项目通病);这就使得报表的级联关系较多,sql语句比较复杂,以下是针对生产慢查询的优化思路。

1.1 慢查询优化思路

  • 一、分析sql,优化sql(检查sql语句本身/聚合/拆分sql);
  • 二、创建合适的索引;
  • 三、检查mysql服务器配置;
  • 四、增加缓存、使用读库(读写分离);
  • 五、若有代码,优化代码逻辑;
  • 六、提升服务器IO性能,如提高硬件配置,机械盘换成SSD固态盘;
  • 七、清洗表、中间件(如ES);
  • 八、分库分表(通常分库分表是最后的手段);

  以上是站务精英总结的慢查询优化思路,可能还有其他办法,顺序也不一定正确,需要根据实际的情况灵活处理。比如有钱,升级机器的成本代价可能小于分库分表带来的研发成本代价。

二、慢sql举例

select
    s.*
from
    tbl_zw_sell_ticket s
left join tbl_sys_user u on u.code = s.ticket_seller_id
left join tbl_sys_role r on u.role_code = r.code
where
    s.leave_date between DATE_FORMAT('2022-12-01 00:00:00', '%Y-%m-%d') and DATE_FORMAT('2022-12-30 23:59:59', '%Y-%m-%d')
    and s.ticket_state not in ('锁票', '解锁')
order by
    s.sell_time desc
limit 50

           

2.1 检查sql(not in)

  观察sql,最先发现 ‘s.ticket_state not in (‘锁票’, ‘解锁’)’。老生常谈的问题,not in 到底走不走索引?

  MySQL 会在选择索引的时候进行优化,如果 MySQL 认为全表扫描比走索引+回表效率高, 那么他会选择全表扫描。注意,我们的sql里面是 select s.* ,按理会出现大量的回表; 但是在sql的末尾 limit 50 , 因为 limit 的增加,让 MySQL 优化的时候发现,索引 + 回表的性能更高一些。

  如果不放心的同学,可以把 not in 换成 not exists 提升执行效率(取决于not exists里面的内容是否多于主表)。

三、Explain

3.1 explain

  老办法,首先想到explain,查看sql的执行计划。在使用explain之前,执行上述sql查询一个月的数据,竟然耗时2分钟左右,赶紧使用explain查看执行计划。

慢查询性能优化实践

  百万量级的 tbl_zw_sell_ticket 表 s 命中 cr_date 索引(sell_time)字段,执行计划显示扫描1167行。

  ‘order by s.sell_time desc’ 语句进入视线,s 表命中这个排序的索引,并不能减少查询量级,这段sql中 s.leave_date 对订单时间过滤是减少查询量级的关键,没有命中 leave_date 的索引将造成不可避免的慢查询。

<font color="red"> 对生产执行第一个优化 </font>
           

++对生产执行第一个优化++:

drop index idx_leave_date on tbl_zw_sell_ticket;
create index idx_leave_date on tbl_zw_sell_ticket (leave_date, sell_time);
force index(idx_leave_date);
           

  更换原来的 leave_date 索引,改为联合索引 leave_date,sell_time 。(注意:原来生产单独的 sell_time 索引不要动,涉及到联合索引命中顺序的问题,可能引起其他报表卡顿)

  对这个报表单独使用的sql,增加强制索引语句,force index(idx_leave_date) ,这轮优化做完后,sql查询一个月数据,已达到秒级/毫秒级的执行速度。

慢查询性能优化实践

3.2 order by的警惕

  • 一、尽量使用index排序,避免filesort排序;
  • 二、检查mysql的配置文件 sort_buffer_size 大小;

四、mysql服务配置

4.1 my.cnf

join_buffer_size = 2M
sort_buffer_size = 2M
innodb_buffer_pool_size=2G
           

  生产配置,sort_buffer_size 为 2M,join_buffer_size 为 2M,innodb_buffer_pool_size=2G,而整体服务器的内存占用只达到了50%,如何判断 innodb_buffer_pool_size=2G 内存页是否合理 ?

4.1.1 内存页是否合理判断

  通过 show status like ‘Innodb_buffer_pool_%’;可以看到跟buffer pool有关的一些信息。

  Innodb_buffer_pool_read_requests表示读请求的次数。

  Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数

  pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

  一般情况下buffer pool命中率都在99%以上,如果低于这个值,才需要考虑加大innodb buffer pool的大小。

4.1.2 max_connections等待sql配置

  判断max_connections 等待sql排队执行的情况,在生产环境max_connections=5000,不用修改。

<font color="red"> 对生产执行第二个优化 </font>
           

++对生产执行第二个优化++:

join_buffer_size = 64M
sort_buffer_size = 64M
innodb_buffer_pool_size = 4G
           

4.2 sql监控-profiling

  如果执行计划是正确的,SQL语句的性能还是很慢,可通过MySQL中的Profiling工具进一步定位问题。 通过开启 profiling 查看sql 执行流程,临时开启 set profiling=ON; 查看开启sql监控的情况show variables like ‘profiling’;

  然后正常执行sql语句,如下:

  select count(*) from checkticket_record where DEPARTURE_DATE >= ‘2023-02-01 00:00:00’ and DEPARTURE_DATE <= ‘2023-05-04 00:00:00’ and CURRENT_STATE = ‘ALREADY_CHECK’;

  执行 show profiles; 查看mysql最新执行的所有sql语句,找到刚刚执行的sql的id,执行show profile for query id;

+——————————+———-+
| Status | Duration |
+——————————+———-+
| Sending data | 0.000197 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000225 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000235 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000187 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000208 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000212 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000222 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000179 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000195 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000202 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000206 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000188 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000196 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000185 |
| Waiting for query cache lock | 0.000016 |

           

五、Waiting for query cache lock

  使用Profiling工具后,发现一段简单的sql执行,出现大量的 Waiting for query cache lock !!!!

  在my.cnf 的配置中,我发现有一段 query_cache_type = 1 的配置,该配置存储SELECT语句及其产生的数据结果,特别适用于表数据变化不是很频繁的场景。

  生产上的两家客户,并未启用读库,统计报表依然从写库查询,而在写库的mysql配置里面存在 query_cache_type = 1 的配置。对于更新压力大的写库来说,查询缓存的命中率也会非常低。

<font color="red"> 对生产执行第三个优化(写库不要开启 query_cache) </font>
           

++对生产执行第三个优化(写库不要开启 query_cache)++:

query_cache_type = 0
# query_cache_size=6M
           

我们可以将参数 query_cache_type 设置成 DEMAND(按需即用)方式,这样对于默认的SQL语句不使用查询缓存,而对于确定要使用query cache的SQL语句,可以用sql_cache的方法指定,例如:

select sql_cache * from table_name; 或 select sql_cache count(*) from table_name; 

以下是query_cache_type三个参数的含义: 

query_cache_type=0(OFF)关闭 

query_cache_type=1(ON)缓存所有结果,除非select语句使用SQL_NO_CACHE禁用查询缓存 

query_cache_type=2(DEMAND),只缓存select语句中通过SQL_CACHE指定需要缓存的查询

六、总结

性能优化是一个庞大的课题,要善用工具,本文主要是针对sql自身、索引、mysql的配置对生产报表查询进行了一轮简单的优化,利用到 sql、索引基础知识,explain 执行命令、Profiling工具等,问题因环境而不同,不同的问题处理手段也不相同,但大致的排查问题思路可以保持一致。

为帮助开发者们提升面试技能、有机会入职BATJ等大厂公司,特别制作了这个专辑——这一次整体放出。

大致内容包括了: Java 集合、JVM、多线程、并发编程、设计模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大厂面试题等、等技术栈!

慢查询性能优化实践

欢迎大家关注公众号【Java烂猪皮】,回复【666】,获取以上最新Java后端架构VIP学习资料以及视频学习教程,然后一起学习,一文在手,面试我有。

每一个专栏都是大家非常关心,和非常有价值的话题,如果我的文章对你有所帮助,还请帮忙点赞、好评、转发一下,你的支持会激励我输出更高质量的文章,非常感谢!

慢查询性能优化实践

继续阅读