关于数据库的坑1

各位好：

 

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

该慢SQL类似于：

SELECTUID,

COUNT(1)AS UID_COUNT

FROMTB_XXXXX

WHEREUID IN(

’XXX’,

’XXX’,

’XXX’,

)

GROUPBY UID

 

应用程序在IN子查询中传入超过22000个UID值，整个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影响数据库整体性能。