There is a good article;
=============================================================================
--数据查询分析--
--１． 建立三张表：
/**************************************************************
--1.1. 学生（学号，学生姓名，性别，出生年月，班级号，入学时间，家庭住址）
--并在学生表中插入100000条记录(十万个学生,班级号为C1000--C1999)
CREATE TABLE 学生
(学号 char(8) not null,
学生姓名 varchar(30),
性别 char(1),
出生年月 datetime,
班级号 varchar(10),
入学时间 datetime,
家庭住址 nvarchar(100),
CONSTRAINT PK_学生 PRIMARY KEY (学号)
)
**************************************************************/
CREATE TABLE t_students
(
std_no char(8) not null,
std_name varchar(30) ,
std_sex char(1) ,
std_birthday datetime ,
std_cls_no varchar(10) ,
std_enter_date datetime ,
std_home_addr nvarchar(100) ,
CONSTRAINT PK_t_students PRIMARY KEY (std_no)
)
go
truncate table t_students
go
declare @rn integer
declare @std_name integer
declare @cls integer
select @rn=100000
while (@rn<200000)
begin
select @std_name=ceiling(rand()*1000000)
select @cls=ceiling((@rn-100000)/100+100)
insert into t_students(std_no,std_name,std_sex,std_birthday,std_cls_no,std_enter_date,std_home_addr)
values("S"||convert(char(6),@rn), convert(char(6),@std_name)||"NAME","0",
convert(datetime,"May 01 1980 10:23PM"
,"C"||convert(char(4),(@cls)),
convert(datetime,"Apr 15 2001 10:23PM","HOME"||convert(char(6),@rn))
select @rn=@rn+1
end
commit
go
--这时学生表上已经有一个nonclustered的索引 pk_t_students
--1.2. 学生2（学号，学生姓名，性别，出生年月，班级号，入学时间，家庭住址）
--并在学生2表中插入100条记录(一百个学生,班级号为C1000)
/**************************************************************
CREATE TABLE 学生小
(学号 char(8) not null,
学生姓名 varchar(30),
性别 char(1),
出生年月 datetime,
班级号 varchar(10),
入学时间 datetime,
家庭住址 nvarchar(100),
CONSTRAINT PK_学生小 PRIMARY KEY (学号)
)
**************************************************************/
CREATE TABLE t_students2
(
std_no char(8) not null,
std_name varchar(30) ,
std_sex char(1) ,
std_birthday datetime ,
std_cls_no varchar(10) ,
std_enter_date datetime ,
std_home_addr nvarchar(100) ,
CONSTRAINT PK_t_students2 PRIMARY KEY (std_no)
)
go
truncate table t_students2
go
declare @rn integer
declare @std_name integer
declare @cls integer
select @rn=100000
while (@rn<100100)
begin
select @std_name=ceiling(rand()*1000000)
select @cls=ceiling((@rn-100000)/100+100)
insert into t_students2(std_no,std_name,std_sex,std_birthday,std_cls_no,std_enter_date,std_home_addr)
values("S"||convert(char(6),@rn), convert(char(6),@std_name)||"NAME","0",
convert(datetime,"May 01 1980 10:23PM","C"||convert(char(4),(@cls)),
convert(datetime,"Apr 15 2001 10:23PM","HOME"||convert(char(6),@rn))
select @rn=@rn+1
end
commit
go
--这时学生2表上已经有一个nonclustered的索引 pk_t_students2
/**************************************************************
--1.3.班级（班级号，班主任姓名，教室，系编号）
--并在班级表中插入1000条记录(一千个班级CLS1000--CLS1999)
CREATE TABLE 班级
(班级号 varchar(10) not null,
班主任姓名 varchar(30),
教室 varchar(30),
系编号 char(10),
CONSTRAINT PK_班级 PRIMARY KEY (班级号)
)
**************************************************************/
CREATE TABLE t_classes
(
cls_no varchar(10) not null ,
cls_director_name varchar(30),
cls_room varchar(30),
cls_fam_no char(10) ,
CONSTRAINT PK_t_classes PRIMARY KEY (cls_no)
)
go
truncate table t_classes
go
declare @rn integer
select @rn=1000
while (@rn<2000)
begin
insert into t_classes(cls_no,cls_director_name,cls_room,cls_fam_no)
values("C"||convert(char(4),@rn), convert(char(4),@rn)||"DIRECTOR_NAME",
"ROOM"||convert(char(4),@rn),"FAMILY"||convert(char(4),@rn))
select @rn=@rn+1
end
commit
go
--2. 索引对查询的影响--
--2.0. 在学生表学生名上建立非聚集索引--
--drop index t_students.idx_t_students_1--
create nonclustered index idx_t_students_1 on t_students(std_name)
go
--集合函数count 返回非空的行数
--实验列表(表中有大量记录)--
--2.1. 查询表中的少量记录(约1%) ,使用idx_t_students_1 noncluster索引 ,且不访问表数据块
--2.2. 查询表中的大量记录(约80%) ,使用idx_t_students_1 noncluster索引 ,且不访问表数据块
--2.3. 查询表中的少量记录(约1%) ,使用idx_t_students_1 noncluster索引 ,且访问表数据块
--2.4. 查询表中的少量记录(小于10%),使用idx_t_students_1 noncluster索引 ,且访问表数据块
--2.5. 查询表中的全部记录(约80%) ,使用idx_t_students_1 noncluster索引 ,且访问表数据块(Sybase优化器消除了这种执行计划)
--2.6. 查询表中的少量记录(约1%) ,不使用idx_t_students_1 noncluster索引,只访问表数据块
--2.7. 查询表中的全部记录(约80%) ,不使用idx_t_students_1 noncluster索引,只访问表数据块
--实验清单--
--2.1. 查询表中的少量记录(约1%) ,使用idx_t_students_1 noncluster索引 ,且不访问表数据块
select count(a.std_name) from t_students a (index std_name) where a.std_name LIKE "10%"
/*
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
FROM TABLE
t_students
a
Nested iteration.
Index : idx_t_students_1
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
std_name ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
STEP 2
The type of query is SELECT.
Table: t_students scan count 1, logical reads: (regular=16 apf=0 total=16), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
-----------------------------------------------------------------------------------------
(A.) "Index : idx_t_students_1"表示使用了该索引
(B.) "Index contains all needed columns. Base table will not be read."
表示本查询结果集所需字段都包含在索引中,只需访问索引块,不需要再通过索引访问表数据块.
(C.) "logical reads: (regular=16 apf=0 total=16)" 表示逻辑读为16
(D.) "physical reads: (regular=0 apf=0 total=0)" 表示物理读为0,所需读的数据块都在内存中,无物理I/O
*/
--2.2. 查询表中的大量记录(约80%),使用idx_t_students_1 noncluster索引 ,且不访问表数据块
select count(a.std_name) from t_students a (index std_name) where a.std_name>="3"
/*
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
FROM TABLE
t_students
a
Nested iteration.
Index : idx_t_students_1
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
std_name ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
STEP 2
The type of query is SELECT.
Table: t_students scan count 1, logical reads: (regular=857 apf=0 total=857), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
-----------------------------------------------------------------------------------------
(A.) 与2.1.(A.)相同
(B.) 与2.1.(B.)相同
(C.) "logical reads: (regular=857 apf=0 total=857)" 表示逻辑读为857
(D.) 与2.1.(D.)相同
(1.) 可见本SQL语句比2.1.的SQL慢.因为更多逻辑+物理读(857>16).
*/
--2.3. 查询表中的少量记录(约1%) ,使用idx_t_students_1 noncluster索引 ,且访问表数据块
select count(a.std_no||a.std_name) from t_students a (index std_name) where a.std_name LIKE "10%"
/*
Nested iteration.
Index : idx_t_students_1
Forward scan.
Positioning by key.
Keys are:
std_name ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
STEP 2
The type of query is SELECT.
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: t_students scan count 1, logical reads: (regular=1207 apf=0 total=1207), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
-----------------------------------------------------------------------------------------
(A.) 与2.1.(A.)相同
(B.) "Using I/O Size 2 Kbytes for index leaf pages."表示访问了索引数据块
(C.) "Using I/O Size 2 Kbytes for data pages"表示也访问了表数据块
因为结果集中只出现的std_no列不在idx_t_students_1(std_name)索引中,
必须根据索引中的物理行地址,去访问行所在数据块,找出其中的std_no,并放入结果集中.
(D.) "logical reads: (regular=1207 apf=0 total=1207)" 表示逻辑页读为1207
(E.) 与2.1.(D.)相同
(1.) 可见本SQL语句比2.1.的SQL慢.因为更多逻辑+物理读(1207>16).
(2.) 可见本SQL语句比2.1.的SQL多了1207-16=1101的表数据块读取
(3.) 可见本SQL语句比2.2.的SQL慢(1207>857).
*/
--2.4. 查询表中的全部记录(约7%),使用idx_t_students_1 noncluster索引 ,且访问表数据块
select count(a.std_no||a.std_name) from t_students a (index std_name) where a.std_name>="3" and a.std_name<="36"
/*
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
FROM TABLE
t_students
a
Nested iteration.
Index : idx_t_students_1
Forward scan.
Positioning by key.
Keys are:
std_name ASC
Using I/O Size 2 Kbytes for index leaf pages.
With MRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
STEP 2
The type of query is SELECT.
Table: t_students scan count 1, logical reads: (regular=6736 apf=0 total=6736), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
-----------------------------------------------------------------------------------------
(A.) 与2.1.(A.)相同
(B.) 与2.3.(B.)相同
(C.) 与2.3.(C.)相同
(D.) "logical reads: (regular=6736 apf=0 total=6736)" 表示逻辑页读为6736
(E.) 与2.1.(D.)相同
(1.) 可见本SQL语句比2.1.的SQL慢,因为更多逻辑+物理读(6736>16).
(2.) 可见本SQL语句比2.2.的SQL慢,因为更多逻辑+物理读(6736>857).
(3.) 可见本SQL语句比2.5.的SQL慢,因为更多逻辑+物理读(6736>3031)!!!!!!.
*/
--2.5. 查询表中的全部记录(约80%),使用idx_t_students_1 noncluster索引 ,且访问表数据块(Sybase优化器消除了这种执行计划)
select count(a.std_no||a.std_name) from t_students a (index std_name) where a.std_name>="3"
--2.6. 查询表中的少量记录(约1%) ,不使用idx_t_students_1 noncluster索引,只访问表数据块
--drop index t_students.idx_t_students_1
select count(a.std_name) from t_students a where a.std_name LIKE "10%"
--2.7. 查询表中的全部记录(约80%),不使用idx_t_students_1 noncluster索引,只访问表数据块
--drop index t_students.idx_t_students_1
select count(a.std_name) from t_students a where a.std_name>="3"
--2.5.,2.6,2.7三条SQL的执行计划完全一致--
/*
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
FROM TABLE
t_students
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
STEP 2
The type of query is SELECT.
Table: t_students scan count 1, logical reads: (regular=3031 apf=0 total=3031), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
-----------------------------------------------------------------------------------------
(A.) "Table Scan. "表示使用了全表扫描,而未使用指定的索引
(B.) "Using I/O Size 2 Kbytes for data pages"表示访问了表数据块,当用全表扫描时,已毋需再去访问索引块.
(C.) "logical reads: (regular=3031 apf=0 total=3031))" 表示逻辑读为3031,也是整个全表扫描所需的逻辑读
(D.) 与2.1.(D.)相同
(1.) 可见本SQL语句比2.2.的SQL慢. 因为更多逻辑+物理读(3031>857).
(2.) 可见本SQL语句比2.3.的SQL慢. 因为更多逻辑+物理读(3031>1207).
(3.) 可见本SQL语句比2.4.的SQL快. 因为更少逻辑+物理读(3031<6736)!!!!!!.
*/
--3. 表记录数对执行计划的影响--
--3.0. 在学生2表学生名上建立非聚集索引--
--drop index t_students2.idx_t_students2_1--
create nonclustered index idx_t_students2_1 on t_students2(std_name)
go
--集合函数count 返回非空的行数
--实验列表(表中有大量记录)--
--3.1. 查询表中的少量记录(约1%) ,使用idx_t_students_1 noncluster索引 ,且不访问表数据块
--3.2. 查询表中的大量记录(约80%),使用idx_t_students_1 noncluster索引 ,且不访问表数据块
--3.3. 查询表中的少量记录(约1%) ,使用idx_t_students_1 noncluster索引 ,且访问表数据块
--3.4. 查询表中的全部记录(约80%),使用idx_t_students_1 noncluster索引 ,且访问表数据块(Sybase优化器消除了这种执行计划)
--3.5. 查询表中的少量记录(约1%) ,不使用idx_t_students_1 noncluster索引,只访问表数据块
--3.6. 查询表中的全部记录(约80%),不使用idx_t_students_1 noncluster索引,只访问表数据块
--实验清单--
--3.1. 查询表中的少量记录(约1%) ,使用idx_t_students2_1 noncluster索引 ,且不访问表数据块
select count(a.std_name) from t_students2 a (index std_name) where a.std_name LIKE "1%"
/*
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
FROM TABLE
t_students2
a
Nested iteration.
Index : idx_t_students2_1
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
std_name ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
STEP 2
The type of query is SELECT.
Table: t_students2 scan count 1, logical reads: (regular=2 apf=0 total=2), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
-----------------------------------------------------------------------------------------
(A.) "Index : idx_t_students2_1"表示使用了该索引
(B.) "Index contains all needed columns. Base table will not be read."
表示本查询结果集所需字段都包含在索引中,不需要再通过索引访问表数据块.
(C.) "logical reads: (regular=2 apf=0 total=2)" 表示逻辑读为2
(D.) "physical reads: (regular=0 apf=0 total=0)" 表示物理读为0,所需读的数据块都在内存中,无物理I/O
(1.) 可见本SQL语句比2.1.的SQL更快,因为更少逻辑+物理读(2<16).
*/
--3.2. 查询表中的大量记录(约80%),使用idx_t_students2_1 noncluster索引 ,且不访问表数据块
select count(a.std_name) from t_students2 a (index std_name) where a.std_name>="3"
/*
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
FROM TABLE
t_students2
a
Nested iteration.
Index : idx_t_students2_1
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
std_name ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
STEP 2
The type of query is SELECT.
Table: t_students2 scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
-----------------------------------------------------------------------------------------
(A.) 与3.1.(A.)相同
(B.) 与3.1.(B.)相同
(C.) "logical reads: (regular=3 apf=0 total=3)" 表示逻辑读为3
(D.) 与3.1.(D.)相同
(1.) 可见本SQL语句比2.2.的SQL块.因为更多逻辑+物理读(3<857).
*/
--3.3. 查询表中的少量记录(约1%) ,使用idx_t_students2_1 noncluster索引 ,且访问表数据块(Sybase优化器消除了这种执行计划)
select count(a.std_no||a.std_name) from t_students2 a (index std_name) where a.std_name LIKE "1%"
--3.4. 查询表中的全部记录(约80%),使用idx_t_students2_1 noncluster索引 ,且访问表数据块(Sybase优化器消除了这种执行计划)
select count(a.std_no||a.std_name) from t_students2 a (index std_name) where a.std_name>="3"
--3.5. 查询表中的少量记录(约1%) ,不使用idx_t_students2_1 noncluster索引,只访问表数据块
--drop index t_students2.idx_t_students2_1
select count(a.std_name) from t_students2 a where a.std_name LIKE "1%"
--3.6. 查询表中的全部记录(约80%),不使用idx_t_students2_1 noncluster索引,只访问表数据块
--drop index t_students2.idx_t_students2_1
select count(a.std_name) from t_students2 a where a.std_name>="3"
--3.3.,3.4.,3.5,3.6四条SQL的执行计划完全一致--
/*
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
FROM TABLE
t_students2
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
STEP 2
The type of query is SELECT.
Table: t_students2 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
-----------------------------------------------------------------------------------------
(A.) "Table Scan. "表示使用了全表扫描,而未使用指定的索引
(B.) "Using I/O Size 2 Kbytes for data pages"表示访问了表数据块,当用全表扫描时,已毋需再去访问索引块.
(C.) "logical reads: (regular=4 apf=0 total=4))" 表示逻辑读为4,也是整个全表扫描所需的逻辑读
(D.) "physical reads: (regular=0 apf=0 total=0)" 表示物理读为0,所需读的数据块都在内存中,无物理I/O
(1.) 可见本SQL语句比3.1.的SQL慢,但相差无几. 因为更多逻辑+物理读(4>2).
(2.) 可见本SQL语句比3.2.的SQL慢,但相差无几. 因为更多逻辑+物理读(4<3).
(3.) 可见本SQL语句比2.4.的SQL快. 因为更少的逻辑+物理读(4<3031).
*/
扫一扫
1053
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
