2022年sybase数据库查询索引优化 .pdf

Sybase 数据库查询索引优化一、实验目的. 2二、实验环境. 2三、实验内容. 21、书写脚本在课程-学生表上插入100 条记录，在学生表上插入10000 条记录。 . 22、索引对查询的影响. 33. 索引代价： . 84 对相同查询功能不同查询语句的执行比较分析：. 10（1）查询至少选修了三门课程的学生的学号和姓名； . 10对于无索引的表格 . 10两者比较结果. 12对于非聚集索引的表格. 13两者比较结果. 15对于聚集索引的表格 . 15两者比较结果. 17最终比较结果. 18（2）查询优化 . 181：查询选修了课程编号为“dep04_s002”且成绩高于85 分的学生信息 . 18对于无索引的表格 . 18得出初步结论. 222：查询所有学生的学号、姓名、选修的课程名称和成绩；. 23得出初步结论. 273：查询计算机科学系林红同学选修的课程名称、学分和成绩 . 28得出初步结论. 34缓存区优化. 34最后结论 : . 38四、实验中遇到的其他问题和解决方案. 38五、实验心得. 45名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 45 页 - - - - - - - - - 一、实验目的熟悉了解 Sybase中查询分析器的使用，理解数据库查询优化的基本概念。通过对不同情况下查询语句的执行情况的对比分析，巩固加深查询优化的理解，并进行书写优化 SQL 语句的初步训练，提高编写高效SQL语句进行数据查询的能力。二、实验环境在 Win7 系统中安装sybase。三、实验内容实验中要进行表中记录数多少、结果集大小、有无索引、不同书写方式的等效SQL、多表连接查询等情况进行查询计划分析，并比较各种查询计划的效率优劣。1、书写脚本在课程-学生表上插入100 条记录，在学生表上插入10000 条记录。在实验中 , 创建了 STU10000的学生表和 STU_COU10000 课程 - 学生表向课程 -学生表上插入100条记录名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 45 页 - - - - - - - - - 用脚本向学生表中插入了10000条记录。2、索引对查询的影响预备知识：1创建索引语法：create unique clustered | unclustered index index-name on table-name（ column-name ， ）SYBASE 提供了两种索引，聚簇索引和非聚簇索引，聚簇索引影响到记录的物理存放，所以一个表只能有一个聚簇索引，每个表最多有249个非聚簇索引。索引中可以包含 116列，但索引项宽度不能超过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 建立非簇集索引，原表不建立索引。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 45 页 - - - - - - - - - STU10000M 创建簇集索引STU10000T 建立非簇集索引进行下列查询：对班级号查询一个范围内的所有记录（要注意结果集不大于20条记录）。无索引：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 45 页 - - - - - - - - - 簇集索引：非簇集索引：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 45 页 - - - - - - - - - 从上面可以看出，当我们查询小数据时，无索引的执行时间最长，簇集索引的执行时间次之， 非簇集索引的执行时间最短。这是因为无索引需要将所有的记录读入内存，需要的 IO操作最多， 而簇集索引会对记录进行排序，对小范围数据没有优势，非簇集索引会对每个记录建一条索引，查询的效率最高。对课程号进行大范围查询（就是结果集包括几乎所有记录，比如有90条以上的记录） 。分析三种情况下的查询计划有何不同？在无索引情况下对课程-学生表进行大范围查询：无索引：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 45 页 - - - - - - - - - 对课程 -学生表建立非簇集索引：然后对课程 -学生表再次查询：删掉非簇集索引：对课程 -学生表建立簇集索引：再次对课程 -学生表查询：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 45 页 - - - - - - - - - 从上面我们可以看出，当我们对表进行大范围的查询时，无索引的执行时间最长，非簇集索引略长于簇集索引。这是因为无索引的需要将所有的记录读入内存，然后选择合适的需要的时间最长， 簇集索引对索引项排序，因此大范围查找时只需要定位第一个满足条件的和最后一个满足条件的就可以依次读出所有需要的记录了，这样执行的时间最短。非簇集索引处于两者之间。3. 索引代价：在有一般索引、簇集索引和无索引的情况下插入数据（例如在学生表上插入数据），比较插入的执行效率。插入记录的脚本语句如下：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 45 页 - - - - - - - - - 无索引非聚集索引聚集索引名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 45 页 - - - - - - - - - 类别所用的时间（秒）无索引0.137 非聚集索引0.22 聚集索引0.425 结论：在无索引的情况下，插入最快，即增加了索引，增大了插入的代价。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 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 45 页 - - - - - - - - - 针对 b语句select STU10000.student_id,student_name from STU10000 where STU10000.student_id in ( select STU_COU100.student_id from STU_COU100 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 45 页 - - - - - - - - - group by STU_COU100.student_id having count (*)=3 ) 两者比较结果执行时间逻辑 I/O 物理 I/O CPU 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 45 页 - - - - - - - - - a语句0.096 683.3 801 315156 b 语句0.086 672 761.7 117218.5 从结果中可以看到，b语句在执行时间，I/O数和 cpu使用方面都比a语句好， b达到了优化的效果对于非聚集索引的表格针对 a语句select STU10000A.student_id,student_name from STU10000A left join STU_COU100A on STU10000A.student_id=STU_COU100A.student_id group by STU10000A.student_id,STU10000A.student_name having count (*)=3 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 45 页 - - - - - - - - - 针对 b语句select STU10000A.student_id,student_name from STU10000A where STU10000A.student_id in ( select STU_COU100A.student_id from STU_COU100A group by STU_COU100A.student_id having count (*)=3 ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 45 页 - - - - - - - - - 两者比较结果执行时间逻辑 I/O 物理 I/O CPU a语句0.098 659 759 117195 b 语句0.036 659 759 117195 对于非聚集索引来说，I/O和CPU没事什么特别的优化，但是运行时间变短了，有变化的是对表的访问概率不同，也算有小小的优化对于聚集索引的表格针对 a语句名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 45 页 - - - - - - - - - select STU10000B.student_id,student_name from STU10000B left join STU_COU100B on STU10000B.student_id=STU_COU100B.student_id group by STU10000B.student_id,STU10000B.student_name having count (*)=3 针对 b语句select STU10000B.student_id,student_name from STU10000B where STU10000B.student_id in 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 45 页 - - - - - - - - - ( select STU_COU100B.student_id from STU_COU100B group by STU_COU100B.student_id having count (*)=3 ) 两者比较结果执行时间逻辑 I/O 物理 I/O CPU a语句0.318 682 798 124847 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 45 页 - - - - - - - - - b 语句0.05 662 759 114747 对聚集索引来讲，b 语句在时间方面比a 减少了好多，优化效果明显最终比较结果执行时间逻辑 I/O 物理 I/O CPU 无索引a语句0.096 683.3 801 315156 b 语句0.086 672 761.7 117218.5 非 聚 集 索引a语句0.098 659 759 117195 b 语句0.036 659 759 117195 聚集索引a语句0.318 682 798 124847 b 语句0.05 662 759 114747 总体来说，重写后的查询一定比原始查询更优。因为优化后连接和笛卡尔乘积项减少，student_id在底部就进行了过滤，这正符合了代码优化的策略，让缩小范围的选择操作尽可能接近查询树的叶节点（2）查询优化除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现下列查询，使之运行效率最高。写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。1：查询选修了课程编号为“dep04_s002”且成绩高于85 分的学生的学号、姓名和成绩；对于无索引的表格针对普通语句select student.student_id,student_name,grade from student,student_course where student.student_id=student_course.student_id and course_id= dep04_s002 and grade85 查询结果：I/O查询名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 45 页 - - - - - - - - - 时间查询：针对优化语句select student.student_id,student_name,grade from student,(select student_id,grade from student_course where course_id=dep04_s002 and grade85) as studentCourse where student.student_id=studentCourse.student_id 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 19 页，共 45 页 - - - - - - - - - 查询效果：关于 I/O 次数查询时间：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 20 页，共 45 页 - - - - - - - - - 建立视图效果create view grade as select student.student_id,student_name,grade,course_id from student,student_course where student.student_id=student_course.student_id and grade85 查询结果关于 I/O 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 21 页，共 45 页 - - - - - - - - - 查询时间得出初步结论执行时间逻辑I/O 物理I/O CPU 优化前0.026 4.77 4.38 5.58 优化后0.018 4.77 4.38 5.58 加视图0.005 4.77 4.38 5.58 鉴于数据太少， I/O 操作和 CPU 都没有什么明显变化，但是时间的明显减少可以看出，名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 22 页，共 45 页 - - - - - - - - - 优化语句和建立视图都是查询调优的有效手段。2：查询所有学生的学号、姓名、选修的课程名称和成绩；针对普通语句select student.student_id,student_name,course_name,grade from student,course,student_course where student.student_id=student_course.student_id and course.course_id=student_course.course_id 查询结果关于 I/O次数名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 23 页，共 45 页 - - - - - - - - - 查询时间针对优化语句select student_id,student_name,course_name,grade from course,(select student.student_id,student_name,grade,course_id from student,student_course where student.student_id=student_course.student_id) as studentCourse 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 24 页，共 45 页 - - - - - - - - - where course.course_id=studentCourse.course_id 查询结果查询 I/O 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 25 页，共 45 页 - - - - - - - - - 查询时间建立视图效果create view stco_view as select student.student_id ,student.student_name ,course.course_name,grade from student ,student_course,course where student.student_id=student_course.student_id and student_course.course_id=course.course_id 查询 I/O 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 26 页，共 45 页 - - - - - - - - - 得出初步结论名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 27 页，共 45 页 - - - - - - - - - 执行时间逻辑I/O 物理I/O CPU 优化前0.025 3 3 56.68 优化后0.005 3 3 56.68 加视图0.015 3 3 56.68 鉴于数据太少， I/O 操作和 CPU 都没有什么明显变化，但是时间的明显减少可以看出，优化语句和建立视图都是查询调优的有效手段。3：查询计算机科学系林红同学选修的课程名称、学分和成绩。(考试成绩 60有学分，否则无学分。针对普通语句select course_name,grade from student,course,student_course,classs,department where student.student_id=student_course.student_id and course.course_id=student_course.course_id and classs.class_id=student.class_id and classs.department_id=department.department_id and student_name=林红 and department_name=计算机科学 查询结果查询 I/O 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 28 页，共 45 页 - - - - - - - - - 查询时间名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 29 页，共 45 页 - - - - - - - - - 针对优化语句select course_name,grade from (select student.student_id,student_name,grade,course_id,class_id from student,student_course where student.student_id=student_course.student_id and student_name=林红 ) as studentCourse, (select class_id from classs,department where classs.department_id=department.department_id and department_name=计算机科学 ) as class_department,course where course.course_id=studentCourse.course_id and class_department.class_id=studentCourse.class_id 查询结果查询 I/O 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 30 页，共 45 页 - - - - - - - - - 查询时间名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 31 页，共 45 页 - - - - - - - - - 建立视图效果create view st_view as select course.course_name,student_course.grade from student ,course ,student_course,classs ,department where student.student_name=林红 and student.class_id=classs.class_id and student_course.student_id=student.student_id and student_course.course_id=course.course_id and classs.department_id=department.department_id and department_name=计算机科学 创建视图查询结果查询 I/O 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 32 页，共 45 页 - - - - - - - - - 查询时间名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 33 页，共 45 页 - - - - - - - - - 得出初步结论执行时间逻辑I/O 物理I/O CPU 优化前0.12 19 12 77.8 优化后0.035 20 12 62.06 加视图0.025 19 12 77.8 鉴于数据太少， I/O 操作和 CPU 都没有什么明显变化，但是时间的明显减少可以看出，优化语句和建立视图都是查询调优的有效手段。缓存区优化开始想要修改默认缓存区的大小查询缓存情况默认为 7000，是最小的，想要改大一点结果执行成功了，再查询却还是名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 34 页，共 45 页 - - - - - - - - - 在 CMD 下登录 sybase，执行刚刚的指令显示奇怪的信息，但是证明那样操作不可能后来查看查询计划，才知道查看数据缓存区信息，没有发生变化看来这个不能直接配置最后终于找到了方法：建立新的缓存区：命名为aa,大小为 10.0M 添加缓存区成功名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 35 页，共 45 页 - - - - - - - - - 将缓存区和数据库绑定执行查询操作，因为aa和 wahahadb是在不同的数据库设备中，是不同的数据库，所以，此时分别在两个数据库中对10000 条记录进行查询，比较查询效率Wahahadb 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 36 页，共 45 页 - - - - - - - - - Aa 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 37 页，共 45 页 - - - - - - - - - 最后结论 : 执行时间逻辑 I/O 物理 I/O CPU 默认缓存区0.01 548 548 10400 先建立的缓存区0.01 548 548 10400 两者参数竟然完全一样。问了老师才知道，本来就有内存的缓存，我定义了新的缓存其实没有什么太大的作用，而正如我刚开始做的那样，默认的内存缓存是不可更改的。四、实验中遇到的其他问题和解决方案1：关于显示的问题开始插入10000 条语句的时候，只看到500 条，虽然显示执行成功了，但是显示不出来，一直以为是代码写错了，而且查询500 条记录以为的记录是查询不到的。后来无意中点到了Option，发现了显示的问题，现更改方式如下：在 Interactive SQL 中修改，点工具中的选项显示全部记录10400 条记录。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 38 页，共 45 页 - - - - - - - - - 在 sybase center中心修改，原来这里显示的是1000 条记录名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 39 页，共 45 页 - - - - - - - - - 选择不限制的选项，结果如下：2：关于查询计划开始一点查询计划，就会出现: 开始以为是自己的安装问题，因为有个女生的电脑可以正常操作完全没有问题，后来发现是代码风格问题，她不习惯按回车键，只要按了回车键，就不能正常查看查询计划，后虽然还是会出现内部错误，但是点OK 的话还是可以查看相应的信息。3：关于正常退出在处理 10000 条记录的时候，用sp_config 总是死机，然后就发现了，如果正常退出，即在文件里点击正常退出的话，是可以保存此次开机进行的SQL操作的命令的，但是直接点击中的关闭按钮，是不会保存此次进行操作的语句的。要想要使用上次使用过的SQL 语句记录， 一定要养成正常退出程序的好习惯。4：关于设置在问题 1 中，我设置了显示所有的行，但是sybase center中也提示了，这样会耗光内存，如果，我把这个设置为1000，然后进行查询等基本操作名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 40 页，共 45 页 - - - - - - - - - 这个是比之前显示所有的I/O 操作 537 会增多，所以， 把所有记录放在内存和放一部分到内存，执行查询操作效果是不同的. 5：关于效率在验收的时候，我们在加入索引和不加入索引，I/O 操作总是一样，这个问题是因为我们的数据引起的， 因为我们加了聚集索引和非聚集索引，不加索引的排序是完全一致的，这样就不会出现其他的不同了，所以，我们改数据，才查看到了不同的效率6：在做启动查询计划的时候，运行结果不理想，改用在CMD 环境下操作。因为电脑休眠过了，直接用isql 登录的时候出现了用 cmd 环境登录sybase系统，也是需要开启服务的，重新开启服务后，登录成功7：开始不知道怎么弄查询计划的时候，在网上找了set showplan on 的命令，这时候显示的是名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 41 页，共 45 页 - - - - - - - - - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 42 页，共 45 页 - - - - - - - - - 关于一些总要的信息，都在消息里面有明显的显示，可以查询对应的信息8：关于扩容，开始，插入数据的时候发现只显示500 条，以为是容量不够了，故进行了扩容，首先，要扩充数据库设备名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 43 页，共 45 页 - - - - - - - - - 其实，扩充数据库名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 44 页，共 45 页 - - - - - - - - - 五、实验心得1： 这次实验开始是插入大数据量的数据，但是一直显示不出来那么多数据，接着要看结果，可是查询计划不知道在哪里看，在网上找了个set showplan on ,结果，出现一大堆的数据，但是不会查看，进展的特别慢，后来将数据全部显示了，又知道了怎么看查询计划，进展就飞速了2：学会了利用查询计划来进行SQL 语句的分析。分析的指标不光光是时间，因为时间不只有运行时间， 还有编译和链接时间，看到的都是总时间，所以查询计划里面的各项指标就都比较有用了3：这次实验，开始查了各种课外资料，对于监视状态的monitor 也有了一定的了解，也明白了怎么看各种性能指标，应该说这次实验让我对怎么使用sybase有了更多的了解4：不要放过实验中的任何细微现象很重要，就在写报告的时候，我突然发现自己一直都是把所有的数据都放在interactive SQL 和 sybase center中心中的，这个会不会是已经把数据都放入内存了呢， 我改回原来的只有一部分显示的情况，发现查询效率果然不一样，这个应该也会影响到已经进行了的所有的操作，因为这个都是在相同的条件下进行的，故相对来说还是比较公平的比较吧。但是不同的存数方法应该会影响到不同优化语句直接I/O 的差距5：同样的命令在第一次执行时运行时间明显比第二次大，命令在运行多次后，运行时间基本处于稳定。但是I/O 操作里面有个逻辑I/O 没变，但是物理I/O 会变为 0，而且也减少了编译和链接的时间，所以肯定会变快。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 45 页，共 45 页 - - - - - - - - -