北邮大三下第6次数据库实验报告--mysql

Catherine2015

实验六 数据查询分析实验

一、实验内容

1、索引对查询的影响

1

（1） 对结果集只有一个元组的查询分三种情况进行执行（必如查询一个具体学

生的信息）： 不建立索引，（学号上）建立非聚集索引，（学号上）建立聚集索引。 用查询分析器的执行步骤和结果对执行进行分析比较。

（2） 对结果集中有多个元组的查询（例如查看某门成绩的成绩表）分类似（1）

的三种情况进行执行比较。

（3） 对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生

的选课情况）分类似（1）的三种情况进行执行比较，注意系统处理的选择。

（4）索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC 上

插入数据），比较插入的执行效率。

2 、对相同查询功能不同查询语句的执行比较分析 3 、查询优化

除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现下列查询，使之运行效率最高。 写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。 （1） 查找选修了每一门课的学生。

（2）查找至少选修了课程数据库原理和操作系统的学生的学号。

二、实验要求

(1) 用SQL语句完成以上操作 (2) 要求学生独立完成以上内容。

(3) 实验完成后完成要求的实验报告内容。

三、实验环境

系统：windows 7 软件：mysql5.6

四、实验步骤及结果分析

4.1. 索引对查询的影响

4.1.1 对结果集只有一个元组的查询分三种情况进行执行（必如查询一个具体

学生的信息）：

登录mysql后输入set profiling=1;

show profiles显示出来之前输入的命令的执行时间。 show profile for query n查询细节;

（1）不建立索引，在student表中查询学号为“31401”的学生：

Catherine2015

select * from student where sno=’31401’;的执行：

删除主索引：ALTER TABLE table_name DROP PRIMARY KEY

2

显示当前表格student无索引：

执行命令：

（2）（学号上）建立聚集索引并显示：

执行命令：

Catherine2015

（3）建立非聚集索引（普通索引）：

3

显示索引：

执行时间：

（4）比较：无索引时执行select * from student where sno=’31401’;耗时0.00042125秒，建立聚集索引耗时：0.00092075秒，非聚集索引耗时0.00065700s，每次执行的时间都不太一样，所以结果集只有一个元组的查询三种情况下耗时差不多一样。

4．1.2 对结果集中有多个元组的查询分类似（1）的三种情况进行执行比较。

Select* from sc

where grade>=96

（1）无索引：

Catherine2015

4

（2）聚集索引：

（3）非聚集索引：

执行：

Catherine2015

5

（4）比较：无索引耗时：0.00145075秒，聚集索引耗时：0.00079200，非聚集索引耗时0.000874s，所以结果集中有多个元组的查询的情况下聚集索引速度快。聚集索引>非聚集索引 > 无索引。

4.1.3 对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况）分类似（1）的三种情况进行执行比较，注意系统处理的选择。 （1）无索引：

（2）聚集索引：

（3）非聚集索引：

（4）比较：无索引时耗时：0.00077350s，聚集索引耗时0.00061975s，非聚集索引耗时0.00061975s，说明查询条件为一个连续的范围的查询的情况下聚集索引快, 聚集索引>非聚集索引 > 无索引。

4.1.4 索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC

上插入数据），比较插入的执行效率。

insert into student values('33331','陈菊','女','1980-1-2 00:00','电信','3022') ;

insert into student values('33332','李元','女','1980-1-2 00:00','电信','3022'); insert into student values('33333','郭莉','女','1980-1-2 00:00','电信','3022'); insert into student values('33334','王倩为','女','1980-1-2 00:00','电信','3022'); insert into student values('33335','张园','男','1980-1-2 00:00','计算机','3146'); （1）无索引：

Catherine2015

缓存后创建table耗时：0.37801800s+0.00114500s=0.379163s，事先创建view耗时：

11 0.08131075s+0.0020655s=0.083373，清空缓存后创建view耗时：

0.07034775s+0.00175s=0.07209775s.利用clear清空缓存。由执行时间可以看出，清空缓存比不清空缓存快，而且事先创建视图查询比事先创建表格查询快。

五、实验总结

mysql支持聚集索引和非聚集索引。聚集索引是主索引，在设置逐渐的时候就已

经默认设置为主索引；非聚集索引就是mysql的普通索引。

采用控制变量法验证各种优化方式的有效性，这样得出的实验结果更有说服力。不过实验过程中，有些相同的命令我执行多次，每次执行的时间都不一样，有时候相差很大，这可能是由于我的电脑的问题，不过这导致实验结果应该会有很大的bug。所以本来想以多次实验取平均值这种方法来增强实验的准确性，额，不过数据太难处理我就放弃了。

不过总体来说，我的实验结果和理论差不多，我也就放心了。当然这些实验在命令行和查询分析器里面都可以做，命令行可以的查询分析器也可以，不过查询分析器里面也要设置profiling的值哦。比如：

看来还是我对mysql了解不够，随着实验的进行，真的觉得sql越来越强大了。 每次的实验总结都在瞎说，呵呵哒！！

本文来源：https://www.bwwdw.com/article/kekf.html