背景：
我们知道一条慢SQL会造成大量的物理IO读操作，严重消耗服务器IO资源。
该慢SQL类似于：
SELECT UID,
COUNT(1) AS UID_COUNT
FROM TB_XXXXX
WHERE UID IN(
’XXX’,
’XXX’,
’XXX’,
)
GROUP BY UID
应用程序在IN子查询中传入超过22000个UID值，整个SQL语句超过45000行，执行时间超过130秒。
分析：
【1】从程序角度来优化，应该严格控制IN子查询传入的参数数量，超量的参数会导致MySQL消耗大量CPU资源去进行语法检查和语法分析，且SQL响应时间较长，导致应用长期占有数据库链接无法释放。
【2】从数据库角度来优化，首先可以考虑将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+秒才能返回。显然这种字符串拆分方式不够高效，不能解决问题。
PS: 由于京东的数据库使用规范中要求避免使用自定义函数和存储过程，因此没测试自定义函数的拆分方式。
总结：
当业务使用上面类似场景时，我们建议将IN改为INNER JOIN查询，并尽量控制传入参数的数量，分批次小数据量地查询，以保证不会因为单条SQL影响数据库整体性能。
网友评论