最近一直在看关系数据库系统知识,主要还是从概念上去了解关系数据库的一些知识,之前没有系统的学习这方面的知识,现在重新去构建这块的知识体系。学习到关系查询优化这块的知识,通过对书本上的分析,了解到查询优化这块的知识,起初在我的知识里,我认为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语句来说,不同的书写方式会对执行效率产生很大的影响。