关于数据库的坑1

各位好:

 

  在今天排查磁盘使用率异常时,我们发现一条慢SQL造成大量的物理IO读操作,严重消耗服务器IO资源。

该慢SQL类似于:

SELECTUID,

COUNT(1)AS UID_COUNT

FROMTB_XXXXX

WHEREUID IN(

’XXX’,

’XXX’,

’XXX’,

)

GROUPBY UID

 

应用程序在IN子查询中传入超过22000UID值,整个SQL语句超过45000行,执行时间超过130

 

  从程序角度来优化,应该严格控制IN子查询传入的参数数量,超量的参数会导致MySQL消耗大量CPU资源去进行语法检查和语法分析,且SQL响应时间较长,导致应用长期占有数据库链接无法释放。

 

  从数据库角度来优化,首先可以考虑将IN子查询改换成INNER JOIN操作,IN中的参数可以使用UNION ALL来合并,改换后SQL为:

  SELECT UID,

COUNT(1) AS UID_COUNT

FROM TB_XXXXX AS T2

INNER JOIN(

SELECT ’XXX’AS RID

UNION ALL SELECT ’XXX’

UNION ALL SELECT ’XXX’

)AS T1

ON T1.RID=T2.UID

GROUP BY UID

  改换后的SQL执行时间为1.2秒,执行效率提升约100倍。通过SQL PROFILE发现,该SQL主要99%消耗在UNION ALL SELECT 操作上,因为需要超过22000次的UNION ALL操作,哪是否可以通过字符串拆分来优化UNION ALL操作呢?

 

  首先创建一张辅助表tb001,创建语句为CREATE TABLE `tb001`(`id` int(11) NOT NULL PRIMEY KEY),然后测试插入0到99999的数据。

然后测试字符串拆分效率,SQL为:

SELECT substring_index(substring_index(T2.RIDS,',', T1.ID + 1), ',', -1)AS RID

FROM (

SELECT ',xxx,xxx,xxx,xxx,xxx' AS RIDS

) AS T2

INNER JOIN TB001 T1

ON T1.ID <  (LENGTH(T2.RIDS) - LENGTH(REPLACE(T2.RIDS, ',', ''))+ 1)

当传入的字符串较少(低于20个)时能快速返回,当传入值到650个时需要50毫秒才能返回,当传入值到22000个时需要40+秒才能返回。显然这种字符串拆分方式不够高效,不能解决问题。

总结:当业务使用上面类似场景时,我们建议将IN改为INNER JOIN查询,并尽量控制传入参数的数量,分批次小数据量地查询,以保证不会因为单条SQL影响数据库整体性能。 

 

 


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值