记一次SQL优化

昨天同事找我，说他接到一个任务，优化SQL，那条SQL执行时间长达90+s，而这条SQL的作者正是本人（o(╥﹏╥)o），于是便开始了对SQL的优化，废话不多说，先上SQL

SELECT
 oos.id,kvs.sn,oos.node_id node_id,oos.USER_QUESTION,
 kvs.type sc,kvs.question,max( kdv.VALUE ) answer 
FROM
 (SELECT kv.QUESTION,s.node_id,s.type,count( s.node_id ) sn,kv.DISABLED 
FROM
 om_sl_suggested s
 LEFT JOIN kb_val kv ON s.node_id = kv.value_id 
WHERE
 s.state = 0 AND s.SHOW_TYPE = 1 AND (kv.disabled = 0 
 OR s.type = 1 OR s.type = 2 ) 
GROUP BY
 s.node_id,s.type,kv.QUESTION,kv.DISABLED 
 ) KVS
 INNER JOIN om_sl_suggested oos ON KVS.NODE_ID = oos.NODE_ID
 LEFT JOIN kb_dim_val kdv ON kvs.node_id = kdv.value_id 
WHERE
 oos.state = 0 AND oos.SHOW_TYPE = 1 
 AND (( kvs.node_id IS NOT NULL AND kvs.disabled = 0 ) OR oos.type = 1 OR oos.type = 2 ) 
GROUP BY
 oos.id,kvs.type,oos.node_id,kvs.sn,oos.USER_QUESTION,kvs.question 
ORDER BY
 kvs.type ASC,kvs.sn DESC,oos.node_id,oos.id ASC,oos.USER_QUESTION,kvs.QUESTION;

先说明一下，这条SQL不是数据库最终执行的SQL，最终执行会根据数据库类型不同，使用不同的分页方式，另外，语句中很多看起来没有必要的order by，group by内容是为了兼容SQLSERVER数据库写的，对于SQLSERVER的一些写法不是很清楚，可能SQL有些地方可以修改，这里不做研究了。这条SQL的核心表是OM_SL_SUGGEST，type为空、1、2分别代表不同的类型，type为2时node_id是一样的，type为空时node_id是另一张表的主键，这条SQL就是为了查出符合某个固定条件（state=0 and show_type=1）的数据，并且最大方向是按照type 按照null,1,2的顺序排序，之后符合条件的node_id越多越往前排，最后是按照主键id排序。SQL优化首先想到的第一个问题是or，并且type不为null时是不需要连表查询的，于是尝试把or改成union连接，只有type为null的部分使用了关联查询，改动后SQL如下:

SELECT
 oos.id,kvs.sn,oos.node_id node_id,oos.USER_QUESTION,
 kvs.type sc,kvs.question,max( kdv.VALUE ) answer 
FROM
 (SELECT kv.QUESTION,s.node_id,s.type,count( s.node_id ) sn,kv.DISABLED 
FROM
 om_sl_suggested s
 LEFT JOIN kb_val kv ON s.node_id = kv.value_id 
WHERE
 s.state = 0 AND s.SHOW_TYPE = 1 AND kv.disabled = 0 
 union
SELECT NULL QUESTION,S.NODE_ID,S.TYPE,COUNT(S.NODE_ID) SN, NULL DISABLED
FROM OM_SL_SUGGESTED S
WHERE s.type IN (1, 2) 
GROUP BY
 s.node_id,s.type,kv.QUESTION,kv.DISABLED 
 ) KVS
 INNER JOIN om_sl_suggested oos ON KVS.NODE_ID = oos.NODE_ID
 LEFT JOIN kb_dim_val kdv ON kvs.node_id = kdv.value_id 
WHERE
 oos.state = 0 AND oos.SHOW_TYPE = 1 
 AND (( kvs.node_id IS NOT NULL AND kvs.disabled = 0 ) OR oos.type = 1 OR oos.type = 2 ) 
GROUP BY
 oos.id,kvs.type,oos.node_id,kvs.sn,oos.USER_QUESTION,kvs.question 
ORDER BY
 kvs.type ASC,kvs.sn DESC,oos.node_id,oos.id ASC,oos.USER_QUESTION,kvs.QUESTION limit 82000,82600;

试了下，发现没什么效果，之后把内层union改为了外层union（SQL就不粘贴了），结果还是没有用，于是对SQL进行分段测试，结果执行时间最长的不是连接查询，反而是type为2的时候的单表查询结果，至此明白原因，这条SQL的瓶颈在于type为2的时候返回结果太多，而前面的SQL都是全量查出了type为2的结果，然后要么是or要吗是union连接，导致SQL执行过慢，如果直接对type为2的结果查询进行分页便很快。但是数据是动态变化的，SQL无法处理，于是只能程序处理，思想大致为，先查第一部分(type is null)总数据量，够分页结果的，便直接查询第一部分，如果第一部分不足分页结果，便去第二部分(type = 1)取分页结果，如果第二部分还是不足，便去第三部分(type = 2)取，最后把结果合并，最终SQL如下(大致思想理解即可，不必过分计较SQL的细节，这是和我们业务有关系的)：

--查询第一部分的数量SQL
select count(1) from om_sl_suggested s inner JOIN kb_val kv ON s.node_id = kv.value_id 
WHERE
 s.state = 0 AND s.SHOW_TYPE = 1 AND kv.disabled = 0 ;
 
 --查询第一部分数据的SQL
 SELECT
  oos.id,kvs.sn,oos.node_id node_id,oos.USER_QUESTION,
 kvs.type sc,kvs.question,max( kdv.VALUE ) answer
FROM
 (SELECT kv.QUESTION,s.node_id,s.type,count( s.node_id ) sn
FROM
 om_sl_suggested s
 LEFT JOIN kb_val kv ON s.node_id = kv.value_id 
 
WHERE
 s.state = 0 AND s.SHOW_TYPE = 1 AND kv.disabled = 0 
GROUP BY
 s.node_id,s.type,kv.QUESTION,kv.DISABLED 
 ) KVS
 INNER JOIN om_sl_suggested oos ON KVS.NODE_ID = oos.NODE_ID
 LEFT JOIN kb_dim_val kdv ON kvs.node_id = kdv.value_id 
WHERE
 oos.state = 0 AND oos.SHOW_TYPE = 1 
GROUP BY
 oos.id,kvs.type,oos.node_id,kvs.sn,oos.USER_QUESTION,kvs.question  
ORDER BY
 kvs.type ASC,kvs.sn DESC,oos.node_id,oos.id ASC,oos.USER_QUESTION,kvs.QUESTION ;
--查询第二部分数量的SQL
select count(1) from om_sl_suggested where state = 0 and SHOW_TYPE = 1 and type = 1;
--查询第二部分数据的SQL
select s.id,osl.sn,s.node_id node_id,s.USER_QUESTION,1 sc,
 null question,null answer from (select count(1) sn, node_id from om_sl_suggested where state = 0 and SHOW_TYPE = 1 and type = 1
group by node_id) osl inner join om_sl_suggested s on osl.node_id = s.node_id where s.state = 0 and s.SHOW_TYPE = 1 order by osl.sn desc, s.id asc
--查询第三部分的SQL
select s.id,(select count(1) from om_sl_suggested where state = 0 and SHOW_TYPE = 1 and type = 2) sn ,s.node_id node_id,s.USER_QUESTION,2 sc,
 null question,null answer from om_sl_suggested s where s.state = 0 and s.SHOW_TYPE = 1 and s.type = 2 order by id asc;

整体执行时间大概在16s左右，可以接受，这次SQL优化过程给了我两点提醒，第一、慎用子查询(本人之前非常喜欢使用子查询)，尤其是这条SQL可能会有分页的时候，子查询可能让效率低下。第二、SQL优化需要调试分析，不能照本宣科，SQL优化中有一条尽量少的请求数据库（我们最终的方案请求数据库最大可能次数为4，至于为什么是4不是5，自行理解），但是有时多请求反而比强行把结果union起来要快的多。