性能文章>慢查询性能优化实践>

慢查询性能优化实践原创

1年前
4027910

一、背景

  项目组接到两家客户反馈,部分生产统计报表查询缓慢,甚至某些报表无法查询出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查看执行计划。
1.png

  百万量级的 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工具等,问题因环境而不同,不同的问题处理手段也不相同,但大致的排查问题思路可以保持一致。

点赞收藏
站务精英

特长

请先登录,查看9条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步
10
9