山东大学《数据库系统》实验七：索引

灰太狼 2023-01-07 07:14 118阅读 0赞

## 实验题目：索引重要性、提高速度 ##

## 实验目的： ##

对比有无索引情况下数据检索速度,学会如何能够使用索引,掌握如何查询是否使用索引了。

## 实验原理和方法： ##

*  按 sid、name 建立两个索引。  
    create UNIQUE index student\_10\_sid on student\_10(sid)  
    create index student\_10\_name on student\_10(name)
 *  对比索引列 sid 和非索引列 sid1 进行查询,观察耗时  
    select \* from pub.student\_10 where sid=‘000000020000’,耗时在毫秒级  
    select \* from pub.student\_10 where sid1=‘000000020000’, 耗时在 1 秒以上
 *  删除索引：drop index xxxx
 *  查询用户所有表的索引 ：select \* from user\_indexes

> 写报告的时候为方便标红（蓝）了，懒得找了，都很简单

## 实验步骤： ##

**题目 1**  
1．将pub用户下表student的3个列sid,name,birthday复制到表test7\_01中。  
2．执行如下查询，观察运行速度（5 秒以上）。  
查询 Samefirstname 相同姓氏的人数。  
select \* from  
(select sid,name,birthday,  
(select count(\*) from test7\_01 where substr(name,1,1)=substr(t1.name,1,1)) samefirstname  
from pub.student\_testindex t1)  
where samefirstname=7  
为test7\_01创建一个仅仅一个索引test7\_01\_index保证上面 SQL耗时在1秒内。  
create table test7\_01 as  
select sid,name,birthday from pub.student  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center]

为姓氏创建索引：create index test7\_01\_index on test7\_01(substr(name,1,1))  
  
**题目 2**  
将 pub 用户下表 student的3个列sid,name,birthday复制到表test7\_02中。  
create table test7\_02 as  
select sid,name,birthday from pub.student  
2.将出生日期部分修改成一天：  
Update test7\_02  
set birthday=to\_date(‘19881018’,‘yyyymmdd’)  
where substr(sid,12,1)= ‘0’;  
3． 为 test7\_02 创建一个仅仅一个索引 test7\_02\_index，保证下面 SQL 耗时在 1 秒内。  
Samenamebirthday 同名同生日的人数，Samebirthday 相同出生日期的人数  
select \* from  
(select sid,name,birthday,  
(select count(*) from test7\_02 where name=t1.name and birthday=t1.birthday)  
samenamebirthday,  
(select count(*) from test7\_02 where birthday=t1.birthday) samebirthday  
from pub.student\_testindex t1)  
where samebirthday=403  
为生日创建索引：create index test7\_02\_index on test7\_02(birthday)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center 1]

4． 验证问题：用计划验证下面这个查询能使用 test7\_02\_index 的索引吗？  
select \* from  
(select sid,name,birthday,  
(select count(\*) from test7\_02 where name=t1.name) samename  
from pub.student\_testindex t1)  
where samename=7  
答：能。未建立索引耗时3.093s，使用索引index（birthday，name）仅用0094s

**题目 3**  
1． pub 用户下表 student 已经用下面两句 SQL 创建了两索引。  
Create index student\_birthday on student(birthday);  
Create index student\_name on student(name);  
2． 下面 SQL 在访问 pub.student 时不能用索引 student\_name 因此耗时超过 2 秒，在逻辑不变情况下，修改 SQL 中标为记红色的子查询的 where 条件部分，不要修改其它地方，使其能使用索引。  
说明：因为 pub.student\_testindex 数据行数太少，不能通过修改主句 where 绕过问题。  
查询 samefirstname 同姓氏的人数、samename 同姓名的人数。  
select \* from  
(select sid,name,birthday,  
(select count(*) from pub.student  
where substr(name,1,1)=substr(t1.name,1,1)  
) samefirstname  
from pub.student\_testindex t1) where samefirstname=7  
修改以后验证耗时在 2 秒之内，将修改以后语句创建成视图 create view test7\_03 as select ……。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center 2]  
create view test7\_03 as  
select \* from  
(select sid,name,birthday,  
(select count(*) from pub.student  
where name like concat(substr(t1.name,1,1),’%’)  
) samefirstname  
from pub.student\_testindex t1) where samefirstname=7

**题目 4**  
1． pub 用户下表 student 已经用下面两句 SQL 创建了两索引。  
Create index student\_birthday on student(birthday);  
Create index student\_name on student(name);  
2． 下面 SQL 在访问 pub.student 时不能用索引 student\_birthday 因此耗时超过 1 秒，在逻辑不变情况下，  
修改 SQL 中标为记红色的子查询的 where 条件部分，不要修改其它地方，使其能使用索引。  
说明：因为 pub.student\_testindex 数据行数太少，不能通过修改主句 where 绕过问题。  
select \* from  
(select sid,name,birthday,  
(select count(*) from pub.student  
where to\_char(birthday,‘yyyymm’)=to\_char(t1.birthday,‘yyyymm’)  
) sameyearmonth,  
(select count(*) from pub.student  
where extract (year from birthday) =extract (year from t1.birthday)  
) sameyear  
from pub.student\_testindex t1) where sameyearmonth=35  
修改以后验证耗时在 1 秒之内，将修改以后语句创建成视图 create view test7\_04 as select ……。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center 3]  
select \* from  
(select sid,name,birthday,  
(select count(*) from pub.student  
where birthday>=trunc(t1.birthday,‘mm’)  
and birthday<last\_day(t1.birthday,‘mm’)  
) sameyearmonth,  
(select count(*) from pub.student  
where birthday>=trunc(t1.birthday,‘yy’)  
and birthday<last\_day(add\_months(trunc(t1.birthday,‘yy’),11))  
) sameyear  
from pub.student\_testindex t1) where sameyearmonth=35

**题目 5**  
1． pub 用户下表 student 已经用下面两句 SQL 创建了两索引。  
Create index student\_birthday on student(birthday);  
Create index student\_name on student(name);  
2． 下面 SQL 在访问 pub.student 时不能用索引 student\_birthday 因此耗时超过 1 秒，在逻辑不变情况下，  
修改 SQL 中标为记红色的子查询的 where 条件部分，不要修改其它地方，使其能使用索引。  
说明：因为 pub.student\_testindex 数据行数太少，不能通过修改主句 where 绕过问题。  
查询 nextbirthday 晚一天出生的人数  
select \* from  
(select sid,name,birthday,  
(select count(\*) from pub.student  
where birthday-1=t1.birthday  
) nextbirthday  
from pub.student\_testindex t1) where nextbirthday=7  
修改以后验证耗时在 1 秒之内，将修改以后语句创建成视图 create view test7\_05 as select ……。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center 4]

create view test7\_05 as  
select \* from  
(select sid,name,birthday,  
(select count(\*) from pub.student  
where birthday=t1.birthday+1  
) nextbirthday  
from pub.student\_testindex t1) where nextbirthday=7

## 结论分析与体会： ##

1.对索引列进行=，>=，<=，>，<，between 等运算可以使用索引。  
2.对索引列进行 Like 运算时，表达式前面没有%等通配符时则可以使用索引。

*  extract()函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
 *  concat(str1, str2,…)函数将多个字符串连接成一个字符串。

#### 就实验过程中遇到和出现的问题，你是如何解决和处理的，自拟1－3道问答题： ####

Q:查询4:没有具体日期，怎样应用实验大纲中的技巧？  
当需要查询一个月份时，to\_char(datecol,‘yyyymm’)='201801’不能使用索引，但是可以通过 datecol>=to\_date(‘20180101’,‘yyyymmdd’) and datecol< to\_date(‘20180201’,‘yyyymmdd’)解决

A:利用以下两个函数：  
select trunc(sysdate, ‘mm’) from dual:返回当月第一天.  
last\_day(time):返回指定日期所在月份的最后一天

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221119/53303a2afd3a47e990f402b8ee25fe21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221119/17d1d986b4b043438c0ad97b20a6f75a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20210116002156395.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20210116002244469.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20210116002412655.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1OTQyMQ==,size_16,color_FFFFFF,t_70#pic_center