sybase数据库查询索引优化

Sybase 数据库查询索引优化

一、实验目的 (2)

二、实验环境 (2)

三、实验内容 (2)

1、书写脚本在课程-学生表上插入100条记录，在学生表上插入10000条记录。 (2)

2、索引对查询的影响 (3)

3. 索引代价： (8)

4．对相同查询功能不同查询语句的执行比较分析： (10)

（1）查询至少选修了三门课程的学生的学号和姓名； (10)

◆对于无索引的表格 (10)

◇两者比较结果 (12)

◆对于非聚集索引的表格 (13)

◇两者比较结果 (15)

◆对于聚集索引的表格 (15)

◇两者比较结果 (17)

◆最终比较结果 (18)

（2）查询优化 (18)

1：查询选修了课程编号为“dep04_s002”且成绩高于85分的学生信息 (18)

◆对于无索引的表格 (18)

◇得出初步结论 (22)

2：查询所有学生的学号、姓名、选修的课程名称和成绩； (23)

◇得出初步结论 (27)

3：查询计算机科学系林红同学选修的课程名称、学分和成绩 (28)

◇得出初步结论 (34)

◆缓存区优化 (34)

最后结论: (38)

四、实验中遇到的其他问题和解决方案 (38)

五、实验心得 (45)

一、实验目的

熟悉了解Sybase中查询分析器的使用，理解数据库查询优化的基本概念。

通过对不同情况下查询语句的执行情况的对比分析，巩固加深查询优化的理解，并进行书写优化SQL语句的初步训练，提高编写高效SQL语句进行数据查询的能力。

二、实验环境

在Win7系统中安装sybase。

三、实验内容

实验中要进行表中记录数多少、结果集大小、有无索引、不同书写方式的等效SQL、多表连接查询等情况进行查询计划分析，并比较各种查询计划的效率优劣。

1、书写脚本在课程-学生表上插入100条记录，在学生表上插入10000条记录。

在实验中,创建了STU10000的学生表和STU_COU10000课程-学生表

向课程-学生表上插入100条记录

用脚本向学生表中插入了10000条记录。

2、索引对查询的影响

预备知识：

1．创建索引语法：

create [ unique ] [clustered | unclustered] index

index-name on table-name（column-name [，…]）

SYBASE提供了两种索引，聚簇索引和非聚簇索引，聚簇索引影响到记录的物理

存放，所以一个表只能有一个聚簇索引，每个表最多有249个非聚簇索引。索引

中可以包含1~16列，但索引项宽度不能超过255个字符。

例如：在LSHSZD上创建索引

create unique clustered index LSHSZD on LSHSZD(F_FLBH,F_HSBH)

go

create nonclustered index LSHSZDJS on LSHSZD(F_HSJS)

go

2．删除索引

drop index table_name.index_name[,table_name.index_name]…

例如：

drop index LSKMZD.LSKMZD

在学生表的学生姓名列上建立非簇集索引，建立簇集索引，不建立索引

因为对同样的数据建立非簇集索引，建立簇集索引，不建立索引，所以我建立了STU10000M 和STU10000T临时表来进行对比，其中STU10000M创建了簇集索引，STU10000T建立非簇集索引，原表不建立索引。

STU10000M创建簇集索引

STU10000T建立非簇集索引

进行下列查询：

对班级号查询一个范围内的所有记录（要注意结果集不大于20条记录）。无索引：

簇集索引：

非簇集索引：

从上面可以看出，当我们查询小数据时，无索引的执行时间最长，簇集索引的执行时间次之，非簇集索引的执行时间最短。这是因为无索引需要将所有的记录读入内存，需要的IO 操作最多，而簇集索引会对记录进行排序，对小范围数据没有优势，非簇集索引会对每个记录建一条索引，查询的效率最高。

对课程号进行大范围查询（就是结果集包括几乎所有记录，比如有90条以上的记录）。分析三种情况下的查询计划有何不同？

在无索引情况下对课程-学生表进行大范围查询：

无索引：

对课程-学生表建立非簇集索引：

然后对课程-学生表再次查询：

删掉非簇集索引：

对课程-学生表建立簇集索引：

再次对课程-学生表查询：

从上面我们可以看出，当我们对表进行大范围的查询时，无索引的执行时间最长，非簇集索引略长于簇集索引。这是因为无索引的需要将所有的记录读入内存，然后选择合适的需要的时间最长，簇集索引对索引项排序，因此大范围查找时只需要定位第一个满足条件的和最后一个满足条件的就可以依次读出所有需要的记录了，这样执行的时间最短。非簇集索引处于两者之间。

3. 索引代价：

在有一般索引、簇集索引和无索引的情况下插入数据（例如在学生表上插入数据），比较插入的执行效率。

插入记录的脚本语句如下：

无索引

非聚集索引

聚集索引

结论：在无索引的情况下，插入最快，即增加了索引，增大了插入的代价。

4．对相同查询功能不同查询语句的执行比较分析：

（1）查询至少选修了三门课程的学生的学号和姓名；

(a) select 学生.学号,学生.学生姓名from 学生

left join 学生选课on 学生.学号=学生选课.学号

group by 学生.学号,学生.学生姓名

having count (*)>=3

(b)select 学生.学号,学生.学生姓名from 学生where 学生.学号in (select 学号from 学生选课group by 学号having count (*)>=3)

◆对于无索引的表格

◇针对a语句

select STU10000.student_id,student_name

from STU10000

left join STU_COU100 on STU10000.student_id=STU_COU100.student_id

group by STU10000.student_id,STU10000.student_name

having count (*)>=3