本文介绍了笔者实际工作中慢sql治理的方法论,1、发现:如何发现慢SQL。2、定位:如何定位到慢SQL写在哪。3、分析:遇到慢SQL时的分析思路。4、解决:慢SQL的解决思路。 @目录一、背景
@
目录 从业务的角度来看:慢SQL会导致产品用户体验差,会减低用户对产品的好感度。
从数据库的角度来看:慢SQL会影响数据库的性能,每个SQL执行都需要消耗一定的I/O资源。假设总资源是100,有一条慢SQL占用了30的资源共计1分钟。那么在这1分钟时间内,其他SQL能够分配的资源总量就是70,如此循环,当资源分配完的时候,所有新的SQL执行将会排队等待。
在治理慢SQL前我们需要知道哪些SQL是慢SQL,即明确治理的对象。Mysql本身提供了慢查询日志,当SQL耗时超过指定阈值的时候,会将SQL记录到慢查询日志文件中,用户能够从慢查询日志文件中提取出慢SQL。
mysql是可以动态开启慢查询日志,即线上的服务器没有开启慢日志,重启后会失效。为防止线上业务受影响,可以先这样修改,同时将my.cnf配置文件补上配置项即可。
查看配置
show VARIABLES like "%query%";
开启慢查询日志
MySQL数据库默认不启动慢查询日志,需要手动设置,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响
# 开启慢查询日志
set global slow_query_log=ON;
# 慢查询阈值
set global long_query_time=1;
# 慢查询日志文件
set global slow_query_log_file=/tmp/mysql_slow.log
我们通过慢查询日志提取出慢SQL,将这些慢SQL按不同的应用进行区分并整理一份文档,再定位到对应应用的代码,在文档中记录慢SQL应用在什么业务中,运行在什么场景中(定时任务、在线实时查询等)。
接下来是根据整理的文档,对这些慢SQL做一些分析,找出慢SQL产生的原因。
使用explain命令输出SQL的执行计划,透过执行计划我们可以了解慢SQL的执行细节。
Mysql中的执行计划各列说明。
这是个非常重要的字段,也是我们判断一个SQL执行效率的主要依据(以下只列举常见的几种)。
依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL
- system:从系统表读一行。这是const联接类型的一个特例。
- const:表最多有一个匹配行,它将在查询开始时被读取。const用于常数值比较PRIMARY KEY或UNIQUE索引的所有部分
- eq_ref:它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。
- ref:相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。
- ref是我们日常开发中较为常见的情况,也是原则上期望要达到的级别,查询命中到索引。
- range:索引范围扫描
- index:只扫描索引树,不需要回表查询。在这种情况下,explain 的 Extra 列的结果是 Using index
- all:全表扫描。
根据具体的业务场景进行考虑
select something from user_table
where id in (select user_id from order_table where xxx=yyyy);
MySQL从5.7开始优化器对子查询进行了优化,会自动转换为join再执行,而对于5.7以下版本的MySQL 我们建议把子查询改成join的方式:
select a.something
from user_table a, order_table b
where a.id=b.user_id
and b.xxx=yyyy;
Limit中分页查询会随着pageNo增大而变缓慢,通过子查询+表连接解决
select * from mytbl order by id limit 100000,10 改进后的SQL语句如下:
select * from mytbl
where id >= ( select id from mytbl order by id limit 100000,1 )
limit 10
# 或者
select * from mytbl
inner join (select id from mytbl order by id limit 100000,10) as tmp
on tmp.id=ori.id;
orders建立了idx_ppo_created_at索引,使用EXPLAIN进行分析
EXPLAIN
SELECT id,
temp_id,
pos_id,
`type`,
member_id,
temp_status,
money_amount,
trans_amount,
return_trans_amount,
coupon_id,
cash_points,
is_cancel,
is_auto_cancel,
is_compensate,
is_multi_equity,
company_id,
store_id,
store_type,
source_orders,
return_order_id,
created_at,
updated_at
FROM orders
WHERE 1 = 1
AND `created_at` >= "2021-08-27 00:00:00"
AND `created_at` <= "2021-08-27 23:59:59"
AND `type` = 0
AND `is_cancel` = 0
AND `temp_status` = 0
ORDER BY id DESC
LIMIT 0,100
我们可以看到Extra列出现了Using filesort,说明MySQL会对数据使用一个外部的索引排序, 而不是按照表内索引顺序进行读取。
因为索引的叶子节点数据是根据 created_at 有序的,我们可以利用这一点来避免排序。
我们将 ORDER BY id DESC 换成 ORDER BY created_at DESC,查看执行计划,Using filesort 已消失:
如果SQL本身的性能已经到达极限了,但是耗时仍然很长。这时候,我们可以业务角度着手,看看在业务上能不能做一些变通、妥协。
如上图所示,我们在做分页时,页底都会展示符合条件的记录总数,以及分页页数。数据量少的时候,不会带来性能问题,但如果数据量较大,这个计算总量的count() 本身就不会太快,再加上每次打开页面都要计算一次的话,那这样会就带性能问题了,同时也会拖慢页面打开速度。
如果业务上允许,当数据量少时,精确显示,当数据量过大后,用户对真实数据不敏感时,那我们就可以通过 1000+ 等模糊的方式进行初步显示,减少不必要的扫描,同时也可以让用户首次打开时达到加速效果。
原SQL:select count() from table where xxx=yyy;
调整后:select count(*) from (select id from table where xxx=yyy limit 1000);
由于业务很复杂,某条SQL关联了很多表,导致表关联时的匹配耗时很长。这时候可以看看能不能将多关联SQL改成较少表的关联,使用代码方式进行关联,但是会增加请求次数。
如果单表行数超过500万行或者单表容量超过2GB,SQL再怎么优化还是会慢,这个时候就要做数据拆分,这属于架构层面的变动,影响的面很大,除了慢SQL本身之外,其他的相关SQL也可能会被“波及”到。这种慢SQL治理的手段,能够一定程度上解决慢SQL问题。减少作用数据的方式有:
本文介绍了实际工作中慢SQL治理的方法论
1、发现:如何发现慢SQL。
2、定位:如何定位到慢SQL写在哪。
3、分析:遇到慢SQL时的分析思路。
4、解决:慢SQL的解决思路。
SQL优化本身是一个比较复杂的问题,上面所列举的,只是部份优化的案例,但所有优化的思路都是尽可能的减少SQL在执行中过程中扫描数据块的次数,只要遵从这一核心思想,SQL优化并不是什么太难的事情。
以上就是今天要讲的内容,本文是笔者实际治理过程中的一些总结和心得,如有不正之处,还请指正。
原文地址:https://www.cnblogs.com/zhihong1/arcHive/2022/03/17/16018136.html
--结束END--
本文标题: 慢SQL治理方法论
本文链接: https://www.lsjlt.com/news/9202.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-06-06
2024-06-06
2024-06-05
2024-06-04
2024-06-04
2024-06-03
2024-06-03
2024-06-03
2024-06-04
2024-06-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0