页面读取统计
改进 SQL Server 中的查询性能
Dean Smith
Founder, Atamai Analytics
表数据页
- 所有数据（内存或磁盘）都存储为 8 KB 大小的"页面"
- 一页可存多行，或一个值可跨多页
- 一页只属于一个表
- SQL Server 使用缓存在内存中的页面
- 未缓存的页面会从磁盘读取并缓存到内存
Customers：数据页
第 1 页
| ALFKI | Alfreds Futterkiste | Maria Anders | Sales Representative | …
| ANATR | Ana Trujillo Emparedados y helados | Ana Trujillo | Owner | …
| ANTON | Antonio Moreno Taqueria | Antonio Moreno | Owner | …
| AROUT | Around the Horn | Thomas Hardy | Sales Representative | …
| BERGS | Berglunds snabbkop | Christina Berglund | Order Administrator | …
| BLAUS | Blauer See Delikatessen | Hanna Moos | Sales Representative | …
| BLONP | Blondesddsl pere et fils | Frederique Citeaux | Marketing Manager | …
第 2 页
| BOLID | Bolido Comidas preparadas | Martin Sommer | Owner | …
| BONAP | Bon app | Laurence Lebihan | Owner | …
| BOTTM | Bottom-Dollar Markets | Elizabeth Lincoln | Accounting Manager | …
| BSBEV | B s Beverages | Victoria Ashworth | Sales Representative | …
| CACTU | Cactus Comidas para llevar | Patricio Simpson | Sales Agent | …
| CENTC | Centro comercial Moctezuma | Francisco Chang | Marketing Manager | …
| CHOPS | Chop-suey Chinese | Yang Wang | Owner | …
第 3 页
| COMMI | Comercio Mineiro | Pedro Afonso | Sales Associate | …
| CONSH | Consolidated Holdings | Elizabeth Brown | Sales Representative | …
| DRACD | Drachenblut Delikatessen | Sven Ottlieb | Order Administrator | …
| DUMON | Du monde entier | Janine Labrune | Owner | …
| EASTC | Eastern Connection | Ann Devon | Sales Agent | …
| ERNSH | Ernst Handel | Roland Mendel | Sales Manager | …
| FAMIA | Familia Arquibaldo | Aria Cruz | Marketing Assistant | …
第 4 页
| FISSA | FISSA Fabrica Inter. Salchichas S.A. | Diego Roel | Accounting Manager | …
| FOLIG | Folies gourmandes | Martine Rance | Assistant Sales Agent | …
| FOLKO | Folk och fa HB | Maria Larsson | Owner | …
| FRANK | Frankenversand | Peter Franken | Marketing Manager | …
| FRANR | France restauration | Carine Schmitt | Marketing Manager | …
| FRANS | Franchi S.p.A. | Paolo Accorti | Sales Representative | …
| FURIB | Furia Bacalhau e Frutos do Mar | Lino Rodriguez | Sales Manager | …
SSMS 中的 STATISTICS IO
SET STATISTICS IO ON
SELECT UNStatisticalRegion,
CountryName,
Capital
FROM Nations
WHERE Capital IN
(SELECT CityName
FROM Cities)
AND Capital IN
(SELECT CityName
FROM Cities
WHERE Pop2017 > 1000000);
(88 rows affected)
Table 'Worktable'. Scan count 1, logical reads 104568, physical reads 0, read-ahead reads 0, lob logical reads 0, ...
Table 'Cities'. Scan count 2, logical reads 548, physical reads 0, read-ahead reads 281, lob logical reads 0, lob physical reads 0, ...
Table 'Nations'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, ...
逻辑读取
(88 rows affected)
Table 'Cities'. ... , logical reads 548, ... , ...
Table 'Nations'. ... , logical reads 3, ... , ...
logical reads：每个表读取的 8 KB 页面数
示例：查询 1
SELECT UNStatisticalRegion,
CountryName,
Capital,
(SELECT MAX(Magnitude)
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN
(SELECT NearestPop
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country)
AND Capital IN
(SELECT NearestPop
FROM Earthquakes
WHERE Magnitude >= 7.5);
示例：查询 1
SELECT UNStatisticalRegion,
CountryName,
Capital,
(SELECT MAX(Magnitude) -- 1st sub-query
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN
(SELECT NearestPop
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country)
AND Capital IN
(SELECT NearestPop
FROM Earthquakes
WHERE Magnitude >= 7.5);
示例：查询 1
SELECT UNStatisticalRegion,
CountryName,
Capital,
(SELECT MAX(Magnitude) -- 1st sub-query
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN
(SELECT NearestPop -- 2nd sub-query
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country)
AND Capital IN
(SELECT NearestPop
FROM Earthquakes
WHERE Magnitude >= 7.5);
示例：查询 1
SELECT UNStatisticalRegion,
CountryName,
Capital,
(SELECT MAX(Magnitude) -- 1st sub-query
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN
(SELECT NearestPop -- 2nd sub-query
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country)
AND Capital IN
(SELECT NearestPop -- 3rd sub-query
FROM Earthquakes
WHERE Magnitude >= 7.5);
示例：查询 1
SET STATISTICS IO ON
SELECT UNStatisticalRegion,
CountryName,
Capital,
(SELECT MAX(Magnitude) -- 1st sub-query
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN
(SELECT NearestPop -- 2nd sub-query
FROM Earthquakes e
WHERE n.Capital = e.NearestPop
AND n.Code2 = e.Country)
AND Capital IN
(SELECT NearestPop -- 3rd sub-query
FROM Earthquakes
WHERE Magnitude >= 7.5);
Table 'Earthquakes'. ..., logical reads 54, ...
'Nations'. ..., logical reads 3, ...
示例：查询 2
SELECT n.UNStatisticalRegion,
n.CountryName,
n.Capital,
MAX(e.Magnitude)
FROM Nations n
INNER JOIN Earthquakes e
ON n.Capital = e.NearestPop
AND n.Code2 = e.Country
WHERE e.Magnitude >=7.5
GROUP BY n.UNStatisticalRegion,
n.CountryName,
n.Capital
示例：查询 2
SELECT n.UNStatisticalRegion,
n.CountryName,
n.Capital,
MAX(e.Magnitude)
FROM Nations n
INNER JOIN Earthquakes e
ON n.Capital = e.NearestPop
AND n.Code2 = e.Country
WHERE e.Magnitude >=7.5
GROUP BY n.UNStatisticalRegion,
n.CountryName,
n.Capital
SET STATISTICS IO OFF
Table 'Earthquakes'. ..., logical reads 18, ...
'Nations'. ..., logical reads 3, ...
查询对比
- 第一个查询包含三个子查询
Table 'Earthquakes'. ..., logical reads 54, ...
'Nations'. ..., logical reads 3, ...
SQL Server parse and compile time:
CPU time = 29 ms, elapsed time = 29 ms.
- 第二个查询使用
INNER JOIN
Table 'Earthquakes'. ..., logical reads 18, ...
'Nations'. ..., logical reads 3, ...
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
Passons à la pratique !
改进 SQL Server 中的查询性能
