条件中in值过多导致的慢SQL问题

在前段时间的项目中,出现了一个很典型的查询优化问题。在此跟大家分享问题分析及解决方法。

此例中SQL文本大小达1.8MB,如下:

这是一个多表连接的比较复杂的视图,SQL的过滤条件里id列 “in” 了几万个常量(红框部分)。这条语句第一次执行需要12秒,第二次执行时间为毫秒级。

原因分析                             

上述两次执行时间的差别,说明该语句执行时间主要消耗在SQL硬解析上。由于项目中相关功能的并发量较大,这条慢SQL引发了严重的性能问题。

这个问题比较普遍。主要原因是开发人员图简单,对“in”列表里常量的个数没有评估。常量动辄数万,甚至数十万,这种SQL在并发量较大的情况下就是灾难。

 

优化思路

1. 创建一个事务级的临时表

CREATE GLOBAL TEMPORARY TABLE TMP_INLIST(    ID VARCHAR(100)) ON COMMIT DELETE ROWS;

2. 将需要参与过滤的常量值插入临时表
--addBatch()批量绑定参数
INSERT INTO TMP_INLIST VALUES(?);
3. 改写SQL语句

select        count(1)from        V_XXXXXXXXXXXXXX t

where C1= '235432' and C2= '345436'

and
( C3 = 'SADFDSGADFDSAFDSAFSAD' or C3 is null ) and ( id in (select id from TMP_INLIST) );

 解决效果                             

按上述优化思路处理后,不管“in”列表里面有多少个常量,SQL解析的代价都是一样的,性能问题得到解决。在本例中,此条SQL首次执行时间由十几秒降至毫秒级

 

posted @ 2024-08-08 16:00  xuchuangye  阅读(85)  评论(0编辑  收藏  举报