使用 WHERE 进行筛选

改进 SQL Server 中的查询性能

Dean Smith

Founder, Atamai Analytics

WHERE 的工作原理

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

PlayerStats 表

WHERE 的工作原理

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

筛选后的 PlayerStats 表

WHERE 的处理顺序

SELECT PlayerName, 
      Team, 
      (DRebound+ORebound) AS TotalRebounds
FROM PlayerStats
WHERE TotalRebounds >= 1000
ORDER BY TotalRebounds DESC;

WHERE 在 SELECT 之前处理

-- ERROR
Invalid column name 'TotalRebounds'.

使用子查询

SELECT PlayerName, 
       Team, 
       TotalRebounds
FROM
     -- Start of sub-query tr
    (SELECT PlayerName, Team, 
             (DRebound+ORebound) AS TotalRebounds
     FROM PlayerStats) tr
WHERE TotalRebounds >= 1000 -- created in the sub-query
ORDER BY TotalRebounds DESC;

使用子查询

SELECT PlayerName, 
       Team, 
       TotalRebounds
FROM
     -- Start of sub-query tr
    (SELECT PlayerName, Team, 
             (DRebound+ORebound) AS TotalRebounds
     FROM PlayerStats) tr
WHERE TotalRebounds >= 1000 -- created in the sub-query
ORDER BY TotalRebounds DESC;

PlayerName	Team	TotalRebounds
Andre Drummond	DET	1247
DeAndre Jordan	LAC	1171
Karl-Anthony Towns	MIN	1012
Dwight Howard	CHO	1012

对列进行计算

SELECT PlayerName, 
       Team, 
       (DRebound+ORebound) AS TotalRebounds
FROM PlayerStats
WHERE (DRebound+ORebound) >= 1000
ORDER BY TotalRebounds DESC;

在 WHERE 条件中对列做"计算"可能会增加查询时间

PlayerName	Team	TotalRebounds
Andre Drummond	DET	1247
DeAndre Jordan	LAC	1171
Karl-Anthony Towns	MIN	1012
Dwight Howard	CHO	1012

对列应用函数

SELECT PlayerName, College, DraftYear 
FROM Players
WHERE UPPER(LEFT(College,7)) = 'GEORGIA'; 
-- unnecessary use of functions 
-- on a filtering column

在 WHERE 条件中对列应用"函数"可能会增加查询时间

PlayerName	College	DraftYear
Damien Wilkins	Georgia
Derrick Favors	Georgia Tech	2010
Iman Shumpert	Georgia Tech	2011
R.J. Hunter	Georgia State	2015
...	...	...

简化 WHERE

SELECT PlayerName, College, DraftYear 
FROM Players 
        -- No calculation or function
WHERE College like 'Georgia%';

PlayerName	College	DraftYear
Damien Wilkins	Georgia
Derrick Favors	Georgia Tech	2010
Iman Shumpert	Georgia Tech	2011
R.J. Hunter	Georgia State	2015
...	...	...

小结

WHERE 在 SELECT 之前处理
在 WHERE 条件中对列做"计算"可能会增加查询时间
在 WHERE 条件中对列应用"函数"可能会增加查询时间

Passons à la pratique !

改进 SQL Server 中的查询性能