简介

改进 SQL Server 中的查询性能

Dean Smith

Founder, Atamai Analytics

地震数据库

NBA 2017-2018 赛季数据库

客户订单数据库

易读吗？

Select ps.Team, count(p.PlayerName) 
As NonNthAmerPlayers from 
 PlayerStats ps inner 
join (select PlayerName FROM Players 
    WHERE Country <> 'USA' Or Country 
 <> 'Canada' ) 
 p on p.PlayerName = ps.PlayerName 
 group BY ps.Team
having Count(p.PlayerName) 
>=24 Order by NonNthAmerPlayers desc

Team	NonNthAmerPlayers
HOU	24
LAL	24
MEM	24
MIL	24

建议

保持一致
SQL 语法用全大写
主要语句各占一行：SELECT、FROM、WHERE 等
缩进代码：
- 子查询
- ON 语句
- AND/OR 条件
- 避免超长单行，如多个列名
以分号 (;) 结束查询
需要时用 AS 起别名

更好了……

原始

Select ps.Team, count(p.PlayerName) 
As NonNthAmerPlayers from 
 PlayerStats ps inner 
join (select PlayerName FROM Players 
    WHERE Country <> 'USA' Or Country 
 <> 'Canada' ) 
 p on p.PlayerName = ps.PlayerName 
 group BY ps.Team
having Count(p.PlayerName) 
>=24 Order by NonNthAmerPlayers desc

优化为

SELECT ps.Team, 
    COUNT(p.PlayerName) NonNthAmerPlayers
FROM PlayerStats ps
INNER JOIN
        (SELECT PlayerName 
         FROM Players 
         WHERE Country <> 'USA'
                OR Country <> 'Canada' ) p
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24
ORDER BY NonNthAmerPlayers DESC;

块注释

/* 
返回拥有 24 名或以上非北美球员的 NBA 球队列表。
*/

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers
FROM PlayerStats ps
INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
         OR Country <> 'Canada' ) p
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24
ORDER BY NonNthAmerPlayers DESC;

使用 /* 与 */ 注释一段（块）代码或文本

块注释

/* 
返回拥有 24 名或以上非北美球员的 NBA 球队列表。
*/

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers
FROM PlayerStats ps
INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
         OR Country <> 'Canada' ) p
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24
ORDER BY NonNthAmerPlayers DESC;

使用 /* 与 */ 注释一段（块）代码或文本

Team	NonNthAmerPlayers
HOU	24
LAL	24
MEM	24
MIL	24

行注释

使用 -- 注释单独一行代码或文本

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers
FROM PlayerStats ps

INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
        OR Country <> 'Canada' ) p
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24

ORDER BY NonNthAmerPlayers DESC;

行注释

使用 -- 注释单独一行代码或文本

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers -- 球员数量
FROM PlayerStats ps

INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
        OR Country <> 'Canada' ) p -- 缩进的子查询
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24

ORDER BY NonNthAmerPlayers DESC;

注释：新列为球员计数

注释：子查询已缩进

行注释

使用 -- 注释单独一行代码或文本

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers -- 球员数量
FROM PlayerStats ps
-- 从这里开始 INNER JOIN
INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
        OR Country <> 'Canada' ) p -- 缩进的子查询
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24
-- 移除 ORDER BY，非必需
ORDER BY NonNthAmerPlayers DESC;

注释：在 INNER JOIN 前留出分隔

注释：说明 ORDER BY 的必要性

行注释

使用 -- 注释单独一行代码或文本

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers -- 球员数量
FROM PlayerStats ps
-- 从这里开始 INNER JOIN
INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
        OR Country <> 'Canada' ) p -- 缩进的子查询
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24;
-- 移除 ORDER BY，非必需
-- ORDER BY NonNthAmerPlayers DESC

已将 ORDER BY 语句注释掉

Passons à la pratique !

改进 SQL Server 中的查询性能