关系数据库查询优化的初次体验

最近一直在看关系数据库系统知识，主要还是从概念上去了解关系数据库的一些知识，之前没有系统的学习这方面的知识，现在重新去构建这块的知识体系。学习到关系查询优化这块的知识，通过对书本上的分析，了解到查询优化这块的知识，起初在我的知识里，我认为RDBMS会优化查询这块的内容，即认为所有人写的查询被RDBMS优化后都是一种执行情况。但事实上并不是这样，RDBMS有多种执行策略，但这种策略知识在已有的查询语句的分析下进行，它依赖于查询的语句，所以RDBMS所做的优化工作其实是有限的，为了验证这个观点，我进行了如下实验。

使用的关系数据库为Mysql

建立数据库student

在student下建立三个表student,course,sc

相应的表结构如下：

表1. student表结构

列名

类型(长度)

约束

说明

sno

varchar(20)

Not null primary key

学号

sname

varchar(20)

Not null

姓名

sex

char(2)

Not null

性别

age

smallint

Not null

年龄

dept

varchar(20)

null

专业名称

表2. course表结构

列名

类型(长度)

约束

说明

cno

int

Not null primary key

课程号

cname

varchar(20)

Not null

课程名

cpno

int

null

先修课程

credit

smallint

Not null

学分

表3. sc表结钩

列名

类型(长度)

约束

说明

sno

varchar(20)

Not null foreign key (sno) references student(sno)

学号

cno

int

Not null foreign key (cno) references student(cno)

课程号

grade

smallint

null

成绩

 

插入数据：student 100000万条数据

                     course100000万条数据

                     sc100000万条数据

 

查询要求：

求选修了2号课程的学生姓名。

SQL1：

SELECT student.sname FROM student,sc WHERE student.sno=sc.sno and sc.cno=2;

 

系统可以用多种等价的关系代数表达式来完成这一查询：

Q1 = πsname (σstudent.sno=sc.sno∧sc.cno=2 (student×sc))

Q2 = πsname (σsc.cno=2 (student∞sc))

Q3 =πsname ((student∞σsc.cno=2(sc)))

 

Q1对应的SQL语句为：

select temp.sname  from (select student.sname,sc.cno as cno,sc.sno as csno from student,sc) astemp where temp.sno=temp.csno and temp.cno=2;

 

Q2对应的SQL语句为：

select temp.sname from (select sc.cno as cno,student.sname as sname from student join sc onstudent.sno=sc.sno ) as temp where temp.cno=2

 

Q3对应的SQL语句为：

select sname from student join (select sno from sc where sc.cno=2) as temp on student.sno =temp.sno;

 

通过查看sql的执行计划，观察各个SQL的执行情况有何不同。

 

Q2对应的SQL语句的执行计划：

图1  Q2 SQL语句执行计划

Q3对应的SQL语句的执行计划：

图2  Q3 SQL 语句执行计划

 

SQL1语句执行计划：

图3  SQL1语句执行计划

explain select sname from student join sc on student.sno = sc.sno wheresc.cno=2;语句执行计划：

图4  使用join进行连接的语句执行计划

       通过对执行计划的了解，发现SQL1语句与Q3相对应的SQL语句的执行计划很相似，不同的是Q3 中的SQL多了一个临时表的建立，所以系统在对SQL1语句采取了类似于Q3 的关系代数表达式来完成查询。

       而且通过对SQL1进行变形，使用join进行连接，发现其执行计划与SQL1的执行计划一样，说明mysqlRDBMS内部还是采用了Join来实现连接。

接着对各个SQL的执行时间进行对比，发现Q1 > Q2 > Q3 ≈ SQL1，Q1很慢很慢，Q2所用时间大约是Q3 的100倍。所以对于查询的SQL语句来说，不同的书写方式会对执行效率产生很大的影响。