1、2024-9-1关系模式关系模式及其运算基础及其运算基础第第6章章 关系数据模型及关系数据模型及其运算基础其运算基础 2024-9-2主要内容主要内容v关系模型的基本概念关系模型的基本概念v关系数据结构及形式化定义关系数据结构及形式化定义关系关系关系模式关系模式v关系的完整性关系的完整性v关系代数关系代数传统的集合运算传统的集合运算专门的关系运算专门的关系运算2024-9-3关系模型回顾关系模型回顾vE.F.Codd于于70年代初提出关系数据理论，他因此年代初提出关系数据理论，他因此获得获得1981年的年的ACM图灵奖图灵奖v关系理论是建立在集合代数理论基础上的，有着坚关系理论是建立在集合代数
2、理论基础上的，有着坚实的数学基础实的数学基础v早期代表系统System：由IBM研制INGRES：由加州Berkeley分校研制v目前主流的商业数据库系统Oracle，Informix，Sybase，SQL Server，DB2Postgresql、Mysql、SQLite2024-9-4关系模型的基本概念关系模型的基本概念v域（域（Domain）一组值的集合，这组值具有相同的数据类型如整数的集合、字符串的集合、全体学生的集合v笛卡尔积（笛卡尔积（Cartesian Product）一组域D1,D2,Dn的笛卡尔积为:D1D2Dn=(d1,d2,dn)|diDi,i=1,n笛卡尔积的每个元素(
3、d1,d2,dn)称作一个n-元组（n-tuple）元组的每一个值di叫做一个分量（component）2024-9-5例：设 D1为教师集合（T）=t1，t2 D2为学生集合（S）=s1，s2，s3 D3为课程集合（C）=c1，c2 则D1D2D3是个三元组集合，元组个数为232，是所有可能的（教师，学生，课程）元组集合笛卡尔积可表为二维表的形式TSCt1s1c1t1s1c2t1s2c1t2s3c22024-9-6v关系关系笛卡尔积D1D2Dn的子集叫做在域D1,D2,Dn上的关系，用R(D1,D2,Dn)表示R是关系的名字，n是关系的度或目关系是笛卡尔积中有意义的子集关系是笛卡尔积中有意义
4、的子集关系也可以表示为二维表关系TEACH(T,S,C)TSCt1s1c1t1s1c2t1s2c1t2s3c2元组属性2024-9-7v关系的性质关系的性质列是同质的列是同质的v即每一列中的分量来自同一域，是同一类型的数据。v如TEACH(T,S,C)=(t1,s1,c1),(t1,t2,c1)是错误的不同的列可来自同一域，每列必须有不同的属性名不同的列可来自同一域，每列必须有不同的属性名v如Person=t1，t2，s1，s2，s3，C=c1，c2，则TEACH关系中的T和S都从Person域中取值，但不能写成TEACH(P,P,C)，还应写成TEACH(T,S,C)行的顺序无关紧要行的顺序
5、无关紧要列的顺序无关紧要列的顺序无关紧要任意两个元组不能完全相同任意两个元组不能完全相同v集合内不能有相同的两个元素每一分量必须是不可再分的数据每一分量必须是不可再分的数据v满足这一条件的关系称作满足第一范式（1NF）的2024-9-8关系模式关系模式v关系模式关系模式关系的描述称作关系模式，包括关系名、关系中的属性名、属性向域的映象、属性间的数据依赖关系等，记作R(A1,A2,An)属性向域的映象一般直接说明为属性的类型、长度等某一时刻对应某个关系模式的内容(元组的集合)称作关系关系模式是型，是稳定的 关系是某一时刻的值，是随时间不断变化的2024-9-9关系数据库语言概述关系数据库语言概述
6、v以关系为运算对象的一组运算符及其对应运以关系为运算对象的一组运算符及其对应运算规则合称为算规则合称为关系运算体系关系运算体系。v以关系运算体系为基础的数据操作语言，成以关系运算体系为基础的数据操作语言，成为为关系数据库语言关系数据库语言。2024-9-10v关系数据库语言的特点关系数据库语言的特点一体化一体化v一般关系系统的数据语言都同时具有数据定义、数据操纵和数据控制语言，而不是分为几个语言。对象单一，都是关系，因此操作符也单一。而非关系型系统，则有对记录的操作，有对系的操作非过程化非过程化v用户只需提出“做什么”，无须说明“怎么做”，存取路径的选择和操作过程由系统自动完成面向集合的存取方
7、式面向集合的存取方式v操作对象是一个或多个关系，结果是一个新的关系（一次一关系）。非关系系统是一次一记录的方式2024-9-11v抽象的查询语言抽象的查询语言关系代数关系代数v用对关系的运算来表达查询，需要指明所用操作*关系演算关系演算v用谓词来表达查询，只需描述所需信息的特性*元组关系演算v谓词变元的基本对象是元组变量*域关系演算v谓词变元的基本对象是域变量v具体系统中的实际语言具体系统中的实际语言SQLv介于关系代数和关系演算之间，由IBM公司在研制System R时提出*QUELv基于Codd提出的元组关系演算语言ALPHA，在INGRES上实现*QBEv基于域关系演算，由IBM公司研制
8、2024-9-12关系代数运算汇总关系代数运算汇总v基本运算基本运算一元运算一元运算v选择、投影、选择、投影、*更名更名多元运算多元运算v笛卡儿积、集合交集、并集、差集、连接（自然连接）笛卡儿积、集合交集、并集、差集、连接（自然连接）v其它运算其它运算外连接、除外连接、除v*扩展运算扩展运算消除重复、聚集和分组运算、排序运算、广义投影消除重复、聚集和分组运算、排序运算、广义投影v*修改操作修改操作插入、删除、更新插入、删除、更新2024-9-13关系代数的一些记号关系代数的一些记号给定关系模式R(A1,A2,An)，设R是它的一个具体的关系，tR是关系的一个元组v分量分量设tR，则tAi表示元
9、组t中相应于属性Ai的一个分量v属性列属性列Ai=Ai1,Ai2,AikA1,A2,An，称A为属性列A表示A1,A2,An中去掉A后剩余的属性组tAi=(tAi1,tAi2,tAik)2024-9-14课内用例课内用例1：成绩管理数据库：成绩管理数据库v在一个学生课程关系数据库中，包括学生关系在一个学生课程关系数据库中，包括学生关系S S，系关系系关系D D，课程关系，课程关系C C，选修关系，选修关系SCSC，数据结构如下：，数据结构如下：v学生：学生：S(S#,SN,SEX,AGE,D#)S(S#,SN,SEX,AGE,D#)依次表示：学号，姓名，性别，年龄，所在系。依次表示：学号，姓名
10、，性别，年龄，所在系。v系：系：DEPT(D#,DN,Director)DEPT(D#,DN,Director)依次表示：系号，系名，系主任。依次表示：系号，系名，系主任。v课程：课程：C(C#,CN,Credit)C(C#,CN,Credit)依次表示：课程号，课程名，学分。依次表示：课程号，课程名，学分。v选修：选修：SC(S#,C#,Grade)SC(S#,C#,Grade)依次表示：学号，课程号，成绩。依次表示：学号，课程号，成绩。2024-9-15课内用例课内用例2：职工项目管理数据库：职工项目管理数据库v这个职工项目管理系统中，有这个职工项目管理系统中，有5个关系模式组成，每个关系
11、个关系模式组成，每个关系表都存储了同一记录结构的数据。表都存储了同一记录结构的数据。vEmployee(Eno,Ename,Sex,Age,Is_Marry,Title,Dno)依次表示：依次表示：职工的职工号、职工姓名、性别、年龄、是否已婚、职职工的职工号、职工姓名、性别、年龄、是否已婚、职称和所在部门号。称和所在部门号。vItem(Ino,Iname,Start_date,End_date,Outlay,Check_date)依次表示：依次表示：项目编号、项目名称、起始日期、终止日期、项目经费项目编号、项目名称、起始日期、终止日期、项目经费和验收日期。和验收日期。vItem_Emp(Ino
12、,Eno,IENo)依次表示：依次表示：项目编号、参加该项目的职工编号、职工在该项目中的项目编号、参加该项目的职工编号、职工在该项目中的排名。排名。vSalary(Eno,Basepay,Service,Price,Rest,Insure,Fund)Salary(Eno,Basepay,Service,Price,Rest,Insure,Fund)依次表示：依次表示：职工号、基本工资、津贴、物价补贴、养老保险、医疗职工号、基本工资、津贴、物价补贴、养老保险、医疗保险和公积金。保险和公积金。vDepartment(Dno,Name,Phone,Manager)依次表示：依次表示：部门号、部门名称
13、、联系电话和部门经理。部门号、部门名称、联系电话和部门经理。2024-9-16选择运算选择运算v基本定义基本定义在关系R中选择满足给定条件的元组（从行的角度）F是选择的条件，t R，F(t)要么为真，要么为假F的形式：由连接而成逻辑运算符：，算术表达式：X YX，Y是属性名、常量、或简单函数是比较算符，,2024-9-17ABC367257723443R A5(R)ABC367257443 A5 C=7(R)ABC3672572024-9-18v示例示例找年龄不小于找年龄不小于20的男学生的男学生AGE20 SEX=male(S)查询查询“01”号部门的全体职工信息。号部门的全体职工信息。Dn
14、o=01(Employee)查询查询“01”号部门中已结婚（即婚否号部门中已结婚（即婚否=1）的职工信息。）的职工信息。Dno=015=1(Employee)规范书写为：规范书写为：Dno=01Is_Marry=1(Employee)2024-9-19投影投影v定义定义从关系R中取若干列组成新的关系（从列的角度）投影的结果中要去掉相同的行cbcfedcbaCBABCbcef B,C(R)R R2024-9-20v示例示例给出所有学生的姓名和年龄给出所有学生的姓名和年龄 SN,AGE(S)找找001号学生所选修的课程号号学生所选修的课程号 C#(S#=001(SC)查询职工的姓名和职称。查询职工
15、的姓名和职称。Ename,Title(Employee)或者或者2,6(Employee)2024-9-21并运算并运算v定义定义所有至少出现在两个关系中之一的元组集合n两个关系R和S若进行并运算，则它们必须是相容的：n关系R和S必须是同元的，即它们的属性数目必须相同n对i，R的第i个属性的域必须和S的第i个属性的域相同R S2024-9-22ABC367257723443RABC345723SABC367257723443345RS 2024-9-23v示例示例求选修了求选修了001号或号或002号课程的学生号号课程的学生号方案方案1：S#(C#=001 C#=002(SC)方案方案2：S#
16、(C#=001(SC)S#(C#=002(SC)2024-9-24差运算差运算v定义定义所有出现在一个关系而不在另一关系中的元组集合R和S必须是相容的R S2024-9-25ABC367257723443RABC345723SABC367257443RS ABC367SR 2024-9-26v示例示例求选修了求选修了001号而没有选号而没有选002号课程的学生号号课程的学生号S#(C#=001(SC)S#(C#=002(SC)2024-9-27交运算交运算v定义定义所有同时出现在两个关系中的元组集合交运算可以通过差运算来重写RS=R (R S)R S2024-9-28ABC3672577234
17、43RABC345723SABC723RS 2024-9-29v示例示例求同时选修了求同时选修了001号和号和002号课程的学生号号课程的学生号错误的写法：错误的写法：S#(C#=001 C#=002(SC)正确的写法：正确的写法：S#(C#=001(SC)S#(C#=002(SC)2024-9-30广义笛卡尔积运算广义笛卡尔积运算v元组的连串（元组的连串（Concatenation）若若r=(r1，rn)，s=(s1，sm)，则定义，则定义r与与s的连的连串为：串为：rs=(r1，rn，s1，sm)v定义定义两个关系两个关系R，S，其度分别为，其度分别为n，m，则它们的笛卡尔积是，则它们的笛
18、卡尔积是所有这样的元组集合：元组的前所有这样的元组集合：元组的前n个分量是个分量是R中的一个元中的一个元组，后组，后m个分量是个分量是S中的一个元组中的一个元组R S的度为的度为R与与S的度之和，的度之和，R S的元组个数为的元组个数为R和和S的的元组个数的乘积元组个数的乘积2024-9-31AB 12rCD 10102010EaabbsAB 11112222CD 1019201010102010Eaabbaabbr sA=C(r x s)ABCDE 122 102020aab2024-9-32v示例示例求数学成绩比王红同学高的学生求数学成绩比王红同学高的学生S.姓名姓名(R.R.成绩成绩 S
19、.S.成绩成绩 R.R.课程课程=数学数学 S.S.课程课程=数学数学 R.R.姓名姓名=王红王红(R S(R)89数学张军86数学王红93物理张军成绩课程姓名86数学王红86数学王红86数学王红R.成绩R.课程R.姓名89数学张军86数学王红93物理张军S.成绩S.课程S.姓名R*更名运算更名运算()SR2024-9-33 连接连接v定义定义从两个关系的广义笛卡儿积中选取给定属性间满足一定条件的元组 A,B为R和S上度数相等且可比的属性列 为算术比较符，为等号时称为等值连接R S=rA SB(RS)A B2024-9-34求数学成绩比王红同学高的学生。求数学成绩比王红同学高的学生。98765
20、4321CBADE3162ABCDE123311236245662 R S B D R SS.姓名姓名(课程课程=数学数学 姓名姓名=王红王红(R)(课程课程=数学数学 S(R)R.成绩成绩600(Salary)v查询查询“技术科技术科”负责人的职工号、职工姓名和职称。负责人的职工号、职工姓名和职称。Eno,Ename,Title(Employee Name=技术科技术科(Department)Eno=Managerv查询没有参加查询没有参加“200901”项目的职工号和职工姓名。项目的职工号和职工姓名。Eno,Ename(Employee)Eno,Ename(Ino=200901(Item_
21、Emp)Eno,Ename(Employee)v查询参加了全部项目的职工号和职工姓名。查询参加了全部项目的职工号和职工姓名。Eno,Ename(Employee)(Item_Emp Ino(Item)2024-9-49关系代数要点分析关系代数要点分析v对于只涉及到选择、投影、连接的查询可用下列表对于只涉及到选择、投影、连接的查询可用下列表达式表示：达式表示：(R(RS)S)或者或者 (R S)(R S)v对于对于否定否定的操作，一般要用的操作，一般要用差集差集操作表示。操作表示。例如例如“查询没有参加查询没有参加200901项目的职工项目的职工”。v对于检索具有对于检索具有“全部全部”特征的操
22、作，一般要用特征的操作，一般要用除法除法操作表示。操作表示。例如例如“查询参加了全部项目的职工查询参加了全部项目的职工”。2024-9-50关系代数要点分析（续）关系代数要点分析（续）v“检索不学检索不学C2课的学生姓名课的学生姓名”，决不能用下式表示：，决不能用下式表示：SNAME，AGE(C#C2(S SC)一定要用一定要用“差差”的形式：的形式：SNAME，AGE(S)SNAME，AGE(C#=C2(S SC)v“检索学习全部课程的学生学号检索学习全部课程的学生学号”，要用要用 S#，C#(SC)C#(C)表示，表示，而不能写成而不能写成 S#(SC C#(C)形式。形式。这是因为一个学
23、生学的课程的成绩可能是不一样的。这是因为一个学生学的课程的成绩可能是不一样的。2024-9-51课内练习课内练习v查询至少选修了号课程和号课程的学生姓名。查询至少选修了号课程和号课程的学生姓名。v查询李勇的年龄。查询李勇的年龄。v查询李明同学不及格的课程名称。查询李明同学不及格的课程名称。v查询没有选修查询没有选修“数据库概论数据库概论”的学生姓名。的学生姓名。v查询查询“数据库概论数据库概论”成绩在成绩在9090分以上的学生姓名。分以上的学生姓名。2024-9-52*包包v 查询职工的职称。查询职工的职称。Title(Employee)（a）基于集合的投影）基于集合的投影 （b）基于包的投影
24、）基于包的投影Title工程师高工助工Title工程师高工工程师工程师工程师助工助工助工2024-9-53*扩展运算扩展运算v消除重复操作符消除重复操作符，把包中的重复元素都去掉，只保留一个副本在关系当中。例：例：对于基于包的投影运算结果中，若不想出现重复元组，则(Title(Employee)用来消除重复的元组。结果就等同于基于集合的投影运算。v聚集运算聚集运算（Aggregation Operator），例如求和或者求平均值，常常和分组（grouping）操作相联系。SUM用来计算一列值的总和。AVG用来计算一列值的平均值。MIN和MAX返回集合中的最小值和最大值。COUNT返回集合中元素
25、的个数，包括重复的元组。v分组运算分组运算，根据元组的值对它们在一个或多个属性上分组，然后对分好组的各个列进行聚集操作计算。分组运算符是组合了分组和聚集操作的一个算符。2024-9-54*扩展运算（续）扩展运算（续）v排序运算符排序运算符，把一个关系变成一个元组的列表，根据一个或者多个属性来排序。例：对Employee表中数据先按所在部门号排序，部门相同的按照职称取值排序。Dno,Title(Employee)v广义投影广义投影（Generalized Projection）在普通投影操作上增加了一些功能，对原有的列进行一系列计算产生新的列。例：查询Employee表中每个职工姓名和该职工的出
26、生年份。（假设出生年份计算为2010-该职工的年龄）。Ename,2010-AgeYearofDate(Employee)2024-9-55查询优化查询优化v一、什么是查询优化？一、什么是查询优化？v使查询数据的操作有最优的时间复杂度和空使查询数据的操作有最优的时间复杂度和空间复杂度。即有最优的时间和空间效率。间复杂度。即有最优的时间和空间效率。v二、谁来做查询优化？二、谁来做查询优化？vDBMS在执行用户的查询命令时，首先做优在执行用户的查询命令时，首先做优化。化。v三、为什么要优化？三、为什么要优化？2024-9-56示例示例v数据库关系模式是：数据库关系模式是：vstudent(sno,
27、sname,age,dno)course(cno,cname)sc(sno,cno,score)2024-9-579900011c001959900021c001809900031c001859900011c002909900011c003809900021c00390c001现代汉语现代汉语c002大学英语大学英语c003社会学社会学99000011李明李明21计算机系计算机系99000021张海张海20外语系外语系99000031刘宏刘宏22数学系数学系表表student表表sc表表course假如数据库中对应的假如数据库中对应的3个表格如下：个表格如下：2024-9-58语法分析语法分析
28、v要求查询姓名是要求查询姓名是李明李明的学生选修的所有课程的成的学生选修的所有课程的成绩。绩。vSQL语句：语句：select score from student,sc where student.sno=sc.sno and sname=李明李明v多种等价的关系代数表达式：多种等价的关系代数表达式：vscore(sname=李明李明 student.sno=sc.sno(student sc)vscore(sname=李明李明(student sc)vscore(sname=李明李明(student)sc)2024-9-59v1.score(sname=李明李明 student.sno=s
29、c.sno(student sc)v首先做首先做student和和sc的笛卡儿积（无条件连接）形的笛卡儿积（无条件连接）形成一个临时表成一个临时表student有有3条记录，条记录，sc有有6条记录。执行连接操作条记录。执行连接操作3618次（占用的时间）次（占用的时间）连接后形成的表有连接后形成的表有18行，行，7列（占用空间）列（占用空间）v然后在临时表上做选择，条件是然后在临时表上做选择，条件是student.sno=sc.sno and sname=李明李明v结果从临时表上选出结果从临时表上选出3条记录。条记录。v最后在最后在3条记录形成的临时表上投影，即选择列条记录形成的临时表上投影
30、，即选择列score。2024-9-60v2.score(sname=李明李明(student sc)v首先做首先做student和和sc的自然连接形成一个临时表如下：的自然连接形成一个临时表如下：这种算法循环执行次数与第一种算法相同，也是这种算法循环执行次数与第一种算法相同，也是3618次。次。但是形成的临时表的空间只有但是形成的临时表的空间只有6行，行，6列。大大提高了空间列。大大提高了空间效率。效率。v然后对临时表做选择然后对临时表做选择sname=李明李明和投影选择列和投影选择列score。9900011李明李明21计算机系计算机系c001959900011李明李明21计算机系计算机系
31、c002909900011李明李明21计算机系计算机系c003809900021张海张海20外语系外语系c001809900021张海张海20外语系外语系c003909900031刘宏刘宏22数学系数学系c001852024-9-61v3.score(sname=李明李明(student)sc)v首先从表首先从表student中选择中选择sname=李明李明的记录，的记录，形成只有一条记录的临时表形成只有一条记录的临时表v然后将这个临时表与表然后将这个临时表与表sc做自然连接操作，形成临做自然连接操作，形成临时表如下：时表如下：这个自然连接操作循环次数为这个自然连接操作循环次数为1*6次次形成
32、的临时表为形成的临时表为3行，行，6列列v最后在做投影，选择列最后在做投影，选择列score。v显然最后一种算法的时间和空间效率最优。显然最后一种算法的时间和空间效率最优。9900011李明李明21计算机系计算机系c001959900011李明李明21计算机系计算机系c002909900011李明李明21计算机系计算机系c003802024-9-62DBMS查询优化的步骤查询优化的步骤vstep1：把查询转换成一种计算机内部表示结构：把查询转换成一种计算机内部表示结构（语法树）（语法树）vstep2代数优化代数优化：利用关系代数等价变换规则以及：利用关系代数等价变换规则以及查询优化的一般策略，
33、将语法树进行优化查询优化的一般策略，将语法树进行优化优化策略：优化策略：v一元选择首先做；投影，选择同时做；乘积选择合并做；索引排一元选择首先做；投影，选择同时做；乘积选择合并做；索引排序预先做。序预先做。v关系代数的等价变换规则关系代数的等价变换规则vstep3物理优化物理优化：选择适当的底层存取路径：选择适当的底层存取路径vstep4：生成一组查询计划，从中选择一个代价最：生成一组查询计划，从中选择一个代价最小的计划小的计划2024-9-63语法树语法树studentscstname=李明李明scorescstudentstname=李明李明scoresnoscore stname=李明李
34、明 student.sno=sc.sno StudentSC2024-9-64vDBMS处理查询计划的过程：处理查询计划的过程：在做完查询语句的词法、语法检查之后，将语句在做完查询语句的词法、语法检查之后，将语句提交给提交给DBMS的查询优化器，优化器做完代数优的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执行结果返回给用户。交给系统处理执行，最后将执行结果返回给用户。v几种改善用户查询计划的解决方案。几种改善用户查询计
35、划的解决方案。2024-9-651合理使用索引合理使用索引v索引是数据库中重要的数据结构，它的根本目的就是为了提索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用高查询效率。现在大多数的数据库产品都采用IBM最先提出最先提出的的ISAM索引结构。索引的使用要恰到好处，其使用原则如索引结构。索引的使用要恰到好处，其使用原则如下：下：在经常进行连接，但是没有指定为外键的列上建立索引，而不经常在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。连接的字段则由优化器自动生成索引。在频繁进行排序或分组（即进行在频繁进行排序
36、或分组（即进行group by或或order by操作）的列上操作）的列上建立索引。建立索引。在条件表达式中经常用到的不同值较多的列上建立检索，在不同值在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的少的列上不要建立索引。比如在雇员表的“性别性别”列上只有列上只有“男男”与与“女女”两个不同值，因此就无必要建立索引。如果建立索引不但两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。不会提高查询效率，反而会严重降低更新速度。如果待排序的列有多个，可以在这些列上建立复合索引如果待排序的列有多个，可以在这些列上建
37、立复合索引（compound index）。）。当数据库表更新大量数据后，删除并重建索引可以提高查询速度。当数据库表更新大量数据后，删除并重建索引可以提高查询速度。2024-9-662避免或简化排序避免或简化排序v应当简化或避免对大型表进行重复的排序。当能够应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：避免了排序的步骤。以下是一些影响因素：索引中不包括一个或几个待排序的列；索引中不包括一个或几个待排序的列；group by或或order by子句中列的次序与索引的次序不一
38、子句中列的次序与索引的次序不一样；样；排序的列来自不同的表。排序的列来自不同的表。为了避免不必要的排序，就要正确地增建索引，合理地为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。当试图简化它，如缩小排序的列的范围等。2024-9-673消除对大型表行数据的顺序存取消除对大型表行数据的顺序存取v在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。在嵌套查询中，对表
39、的顺序存取对查询效率可能产生致命的影响。v比如采用顺序存取策略，一个嵌套比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询层的查询，如果每层都查询1000行，行，那么这个查询就要查询那么这个查询就要查询10亿行数据。亿行数据。避免这种情况的主要方法就是对连接的列进行索引。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄例如，两个表：学生表（学号、姓名、年龄）和选课表（学号、课程号、）和选课表（学号、课程号、成绩）。成绩）。如果两个表要做连接，就要在如果两个表要做连接，就要在“学号学号”这个连接字段上建立索引。这个连接字段上建立索引。v还可以使用并集来避
40、免顺序存取。还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用子句强迫优化器使用顺序存取。下面的查询将强迫对顺序存取。下面的查询将强迫对orders表执行顺序操作：表执行顺序操作：SELECT FROM orders WHERE customer_num=104 OR order_num=1008 虽然在虽然在customer_num和和order_num上建有索引，但是在上面的语句中优上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。化器还是使用顺序存取路径扫描整个表。因为这个语句要
41、检索的是分离的行的集合，所以应该改为如下语句：因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：SELECT FROM orders WHERE customer_num=104 UNION SELECT FROM orders WHERE order_num=1008 这样就能利用索引路径处理查询。这样就能利用索引路径处理查询。2024-9-684避免相关子查询避免相关子查询v一个列的标签同时在主查询和一个列的标签同时在主查询和where子句中子句中的查询中出现，那么很可能当主查询中的列的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查值改变之后，子查询必
42、须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。在子查询中过滤掉尽可能多的行。2024-9-695避免困难的正规表达式避免困难的正规表达式vLIKE关键字支持通配符匹配，技术上叫正规表达式。但这关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。种匹配特别耗费时间。v例如：例如：SELECT FROM customer WHERE zipcode LIKE“98_ _ _”即使在即使在zipcode字段上建立了索引，在这种情况下也
43、还是采字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。用顺序扫描的方式。v如果把语句改为如果把语句改为SELECT FROM customer WHERE zipcode“98000”，在执行查询时就会利用索引来查询，在执行查询时就会利用索引来查询，显然会大大提高速度。显然会大大提高速度。v另外，还要避免非开始的子串。另外，还要避免非开始的子串。v例如语句：例如语句：SELECT FROM customer WHERE zipcode2，3“80”，在，在where子句中采用了非开始子串，子句中采用了非开始子串，因而这个语句也不会使用索引。因而这个语句也不会使用索引。2024-9-70
44、6使用临时表加速查询使用临时表加速查询 v把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。排序操作，而且在其他方面还能简化优化器的工作。v例如：例如：SELECT cust.name，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id=rcvlbes.customer_id AND rcvblls.balance0 AND cust.postcode“98000”ORDER
45、 BY cust.name v如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：一个临时文件中，并按客户的名字进行排序：SELECT cust.name，rcvbles.balance，other columns INTO TEMP cust_with_balance FROM cust，rcvbles WHERE cust.customer_id=rcvlbes.customer_id AND rcvblls.balance0 ORDER BY cust.name 然
46、后以下面的方式在临时表中查询：然后以下面的方式在临时表中查询：SELECT FROM cust_with_balance WHERE postcode“98000”v临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘盘I/O，所以查询工作量可以得到大幅减少。，所以查询工作量可以得到大幅减少。v注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。注意不要丢失数据。2024-9-717用排序来取代非顺序存取用排序来取代非顺序存取v非顺序磁盘存取是最慢的操作，表现在磁盘非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。存取臂的来回移动。SQL语句隐藏了这一情语句隐藏了这一情况，使得我们在写应用程序时很容易写出要况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。求存取大量非顺序页的查询。有些时候，用数据库的排序能力来替代非顺有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。序的存取能改进查询。2024-9-72作业作业v书本第书本第6章习题章习题P157-P158：第第2、4、5、12、14题题
