一次Sql语句的优化实战之旅

1.使用union all 代替 in

数据量很大,优化前差不多2分钟,优化后1s.

低效sql:
select col1,col2,col3 from table where id in(1,2,3,4,5);
高效sql:
select col1,col2,col3 from table where id=1
union all
select col1,col2,col3 from table where id=2
union all
select col1,col2,col3 from table where id=3
union all
select col1,col2,col3 from table where id=4
union all
select col1,col2,col3 from table where id=5

2.使用 count(1) over() 代替 count(1)或者count(*)

当时用分页需要计算总的记录数时,count(1) over()的效率远远高于 count(1)

低效sql:
select count(1) from table t1 left outer join table2 t2
on t1.col1=t2.col2 
或者
SELECT col1,col2 FROM (SELECT col1,col2, ROW_NUMBER() OVER(col) AS [num] FROM table where 1=1  ) AS TEMP1 where [num] >= startIndex and [num] <= endIndex ; SELECT COUNT(1) as [totalCount] FROM table where 1=1;

高效sql:
select totalCount=count(1) over() from table t1 left outer join table2 t2 on t1.col1=t2.col2 
或者
SELECT col1,col2,TotalCount FROM (SELECT col1,col2, ROW_NUMBER() OVER(col) AS [num],COUNT(*) over() TotalCount FROM table where 1=1 ) AS TEMP1 where [num] >= startIndex and [num] <= endIndex 

3.使用 row_number() over(partition by col1,col2 order by col) 代替 max(col) group by col1 col2

低效的sql:
select col1,col2 from table where col in(select max(col) from table group by col1,col2 order by col desc)
高效的sql:
select col1,col2 from (select num=row_number() over(partition by col1,col2 order by col desc)  from table ) where num=1