查询顺序

改进 SQL Server 中的查询性能

Dean Smith

Founder, Atamai Analytics

强震查询

SELECT Country, Place, Magnitude
FROM Earthquakes
WHERE Magnitude >= 9
ORDER BY Magnitude DESC;

Country	Place	Magnitude
CL	比奥比奥；智利	9.5
US	阿拉斯加南部	9.2
ID	苏门答腊西北近海	9.1
JP	本州东海岸附近；日本	9.1
...	...	...

语法顺序

                                 -- 语法顺序
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                                            
WHERE Magnitude >= 9            
ORDER BY Magnitude DESC;

语法顺序

                                 -- 语法顺序
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9            
ORDER BY Magnitude DESC;

语法顺序

                                 -- 语法顺序
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;

语法顺序

                                 -- 语法顺序
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

处理顺序

                                 -- 语法顺序 | 处理顺序
SELECT Country, Place, Magnitude -- 1. SELECT        
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

处理顺序

                                 -- 语法顺序 | 处理顺序
SELECT Country, Place, Magnitude -- 1. SELECT        
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

处理顺序

                                 -- 语法顺序 | 处理顺序
SELECT Country, Place, Magnitude -- 1. SELECT      3. SELECT
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

处理顺序

                                 -- 语法顺序 | 处理顺序
SELECT Country, Place, Magnitude -- 1. SELECT      3. SELECT
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY    4. ORDER BY

处理错误


SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes            
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

处理 FROM

                        -- 处理顺序
SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes   -- 1. FROM - 表 LargeEarthquakes 不存在
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- 错误
Invalid object name 'LargeEarthquakes'.

处理 WHERE

                        -- 处理顺序
SELECT Country, 
       PlaceName, 
       Magnitude
FROM Earthquakes                              
WHERE Strength >= 9     -- 2. WHERE - 列 Strength 不存在 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- 错误
Invalid column name 'Strength'.

处理 SELECT

                        -- 处理顺序
SELECT Country, 
       PlaceName,       -- 3. SELECT - 列 PlaceName 不存在
       Magnitude 
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- 错误
Invalid column name 'PlaceName'.

处理 ORDER BY

                        -- 处理顺序
SELECT Country, 
       Place, 
       Magnitude
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;-- 4. ORDER BY - 列名拼写错误

________________________________________________________

-- 错误
Invalid column name 'Magnitud'.

无错误

SELECT Country, 
       Place, 
       Magnitude   
FROM Earthquakes                
WHERE Magnitude >= 9      
ORDER BY Magnitude DESC;

Country	Place	Magnitude
CL	比奥比奥；智利	9.5
US	阿拉斯加南部	9.2
ID	苏门答腊西北近海	9.1
JP	本州东海岸附近；日本	9.1
...	...	...

逻辑处理顺序

1.  FROM

2.  ON

3.  JOIN

4.  WHERE

5.  GROUP BY

6.  HAVING


7.  SELECT


8.  DISTINCT

9.  ORDER BY

10. TOP

Ayo berlatih!

改进 SQL Server 中的查询性能