一次优化sql的实践

这么一条sql

SELECT tt
,           nn
,           cc
FROM
(SELECT A.tt,           B.nn,           C.cc,           RANK() OVER (PARTITION BY C.ff, C.gg ORDER BY C.hh) AS RFROM   A LEFT JOIN B ON a.mm = B.mmLEFT JOIN C ON A.ff = C.ff AND A.gg = C.gg
) T
WHERE T.R < 2

运行10分钟还出不来，显然有问题。

首先想到的是加索引，给C表加了个索引，涵盖ff, gg, 和hh字段，没用。

问了ai后，改成

SELECT A.tt
,           B.nn
,           T.cc
FROM   A 
OUTER APPLY (SELECT C.cc,  RANK() OVER (PARTITION BY C.ff, C.gg ORDER BY C.hh) AS R
    FROM C
    WHERE C.ff = A.ff AND C.gg = A.gg
) T

LEFT JOIN B ON a.mm = B.mm
WHERE T.R < 2

1秒就出来了。问题解决了。但原因是什么呢？
C表很大，有将近1亿条数据，A表有几百万条数据，表连接耗费了大量时间。而用outer apply，将相对较小的A表的每条数据在C表里找到匹配，就快了许多。这个例子的启示是，如果表的记录很多，连接的开销是很大的，rank()或类似的函数开销也很大，可以考虑用outer apply来代替表连接。一般来说，sql鼓励用集合运算，而outer apply有点类似游标操作，本来直觉上好像不好。但是，在数据量很大的情况下，连接等集合操作开销很大，反而不如将较小的一个数据集拿出来，一条条记录去大表中找匹配，性能要好得多。
继续优化这个sql，虽然主要问题解决了，但还有优化的余地。首先，PARTION BY是多余的，可以去掉，其次，这里没必要用RANK()，可以改成ROW_NUMBER()，性能也好一些，最后。连ROW_NUMBER()也没必要用，直接用TOP就可以了，最后改成

SELECT A.tt
,           B.nn
,           T.cc
FROM   A 
OUTER APPLY (SELECT TOP 1 C.cc FROM CWHERE C.ff = A.ff AND C.gg = A.ggORDER BY C.hh
) T
LEFT JOIN B ON a.mm = B.mm