关系数据库查询优化.ppt
合集下载
MySQL调优技术分享PPT
多表查询优化建议
二、连接和子查询的比较 开发效率：子查询将一个SQL拆分成多个SQL进行查询，连接查询只查询一个SQL，把业务逻辑放到
SQL中，提高了开发效率。 查询效率： （子查询优于关联查询）：联合查询相较于单查询减少了通信次数，降低了通讯上时间的消耗；
由于数据库存在缓存机制，所以开启MYSQL查询的话，单查询的可重用性较高，缓存效率比联合查询也 会更高。
多表查询优化建议
一、连接（关联查询）： 基础介绍： 1、内连接：两个表中同时满足某条件的数据记录组合，两个表的交集；关键词 inner join 或 join 2、外连接：（左外连接和右外连接还有全连接） ①：左外连接：A的全集以及A和B的交集；关键词：left join ②：右外连接：B的全集以及A和B的交集；关键词：right join ③：全连接：A与B的并集。 ④：交叉连接：A与B的笛卡尔乘积。关键词：cross join 内部实现原理： 嵌套循环关联，先确定一端，再进行匹配。
04
拓展
MYSQL缓存机制
一、MYSQL缓存机制
原理：MySQL的Query_Cache:将查询语句和返回的结果集使用KV形式存在内存中；当发起一个 查询请求，先对该SQL进行权限验证，接着就通过Query Cache来查找结果。它不需要经过Optimizer模块 进行执行计划的分析优化，更不需要发生同任何存储引擎的交互，减少了大量的磁盘IO和CPU运算，所以 有时候效率非常高。
索引下推
2020
演示完毕 感谢观看
锁竞争：联合查询查询速度比单个查询要慢，所以联合查询会增加锁的竞争关系。 （关联查询优于子查询）：MYSQL查询优化器更好读懂关联查询，所以更快预算出更好的查询
路径。
第四章全局查询处理和优化
§4.4 查询优化的基础
2、查询树 在查询树中，叶子表 示关系，中间节点表 示运算，前序遍历关 系表示运算次序。 定义： ROOT：=T T：=R/（T） /TbT/UT U：=σF/ПA b： =∞/X/∪/∩//∝
§4.4 查询优化的基础
3、举例 例4.2.1 设有一供应关系数据库，有供应者和供应两关系，如下： 供应者：SUPPLIER{SNO，SNAME，AREA} 供应者编号 供应者姓名 供应者所属地域 供应：SUPPLY{SNO，PNO，QTY} 供应者编号 零件号 质量 查询要求：找出地域在″北方″供应100号零件的供应商的信息。 SQL查询语句：SELECT SNO，SNAME FROM SUPPLIER，SUPPLY WHERE AREA=″北方″AND PNO=100 AND SUPPLIER.SNO=SUPPLY.SNO
§4.4 查询优化的基础
（2）等价变换 重复律：UR ≡ UUR 交换律：U1U2R ≡ U2U1R 分配律：U（RbS）≡（UR）b（US） 结合律：Rb1（Sb2T）≡ （Rb1S）b2T 提取律：（UR）b（US） ≡ U（RbS） 其中：R、S、T为关系，U1、U2、U为一元运算符 ，b1、b2、 b为二元运算符。
§4.4 查询优化的基础
3、举例 等价的关系表达式： Q1：ПSNO，SNAMEσAREA=″北方″σPNO=100 （SUPPLIER∞SUPPLY） 查询树：
§4.2 Overview of Query Processing
通常用SQL语言操纵语言来表达全局查询。之后， 由系统将其转换成内部表示。实际上，在查询执 行过程时，最终涉及的是具体场地上的物理关系 的查询。影响查询处理效率的因素有：网络传输 代价（数据量和延迟等）、局部I/O代价及CPU 使用情况代价等，但主要由网络通信代价和局部 I/O代价来衡量。不同的分布式数据库系统可能对 评估查询处理的传输代价和I/O代价的侧重不同， 同时，为提高查询的效率，在查询处理过程中还 要进行优化处理，查询优化就是确定出一种执行 代价最小的查询执行策略或寻找相对较优的操作 执行步骤。一般可采用多级优化。本章介绍全局 查询的处理与优化。
第2章 数据库-关系模型1
• 在关系数据模型中一般将数据完整性分为三类
– 实体完整性
– 参照完整性 – 用户定义完整性
需要说明两点
• 关系是元组的集合，集合（关系）中的元素（元组） 是无序的；而元组不是分量di的集合，元组中的分量
是有序的。
例如，在关系中(a,b)≠(b,a)，但在集合中{a,b}={b,a}。
• 若一个关系的元组个数是无限的，则该关系称为无限
实体完整性规则
• 实体完整性是要保证关系中的每个元组都是可识别和唯一的。 • 实体完整性规则要求关系中元组的主键值不可以为空值。
• 实体完整性是关系模型必须满足的完整性约束条件，也称作是
关系的不变性。 • 关系数据库管理系统用主键实现实体完整性，这是由关系系统 自动支持的。
对实体完整性规则的几点说明
关系数据语言
• 关系代数语言：如ISBL
• 关系演算语言：
– 元组关系演算语言（如Aplha,Quel)
– 域关系演算语言（如QBE)
• 具有关系代数和关系演算双重特点的语言：如SQL
返回
关系完整性约束
• 在数据库中数据完整性是指保证数据正确的特性。
它包括两方面的内容：
– 与现实世界中应用需求的数据的相容性和正确性； – 数据库内数据之间的相容性和正确性。
部门表(R1)
部门编号 01 02 03 04 部门名称 经理办公室 人事部 公关部 技术部 …
02 03
外部关系键
主关系键
注意事项：
• 外部关键字和相应的主关键字可以不同名，只要 定义在相同值域即可。 • 两个关系（R和S）也可以是同一个关系模式，表 示了属性之间的联系。 • 外部关键字的值是否为空，应视具体问题而定。
mysql性能优化精品PPT课件
MySQL优化
目录索引
MySQL优化方式 MySQL技巧分享 MySQL函数
MySQL优化方式
MySQL优化方式
系统优化：硬件、架构 服务优化 应用优化
系统优化
使用好的硬件，更快的硬盘、大内存、多核CPU，专业的存 储服务器（NAS、SAN）
设计合理架构，如果 MySQL 访问频繁，考虑 Master/Slave 读写分离；数据库分表、数据库切片（分布式），也考虑使 用相应缓存服务帮助 MySQL 缓解访问压力
选项
max_connections query_cache_size sort_buffer_size
record_buffer table_cache
缺省值
100 0 (不打开）M 16M
16M 512
说明
MySQL服务器同时处理的数据库连接的最大数量
查询缓存区的最大长度，按照当前需求，一倍一倍 增加，本选项比较重要
每个线程的排序缓存大小，一般按照内存可以设置 为2M以上，推荐是16M，该选项对排序order by， group by起作用
每个进行一个顺序扫描的线程为其扫描的每张表分 配这个大小的一个缓冲区，可以设置为2M以上
为所有线程打开表的数量。增加该值能增加mysqld 要求的文件描述符的数量。MySQL对每个唯一打开 的表需要2个文件描述符。
8M
128M 0 256M
innodb_log_buffer_size
128K
8M
说明
InnoDB使用一个缓冲池来保存索引和原始数据, 这 里你设置越大,你在存取表里面数据时所需要的磁盘 I/O越少，一般是内存的一半，不超过2G，否则系 统会崩溃，这个参数非常重要
InnoDB用来保存 metadata 信息, 如果内存是4G， 最好本值超过200M
目录索引
MySQL优化方式 MySQL技巧分享 MySQL函数
MySQL优化方式
MySQL优化方式
系统优化：硬件、架构 服务优化 应用优化
系统优化
使用好的硬件，更快的硬盘、大内存、多核CPU，专业的存 储服务器（NAS、SAN）
设计合理架构，如果 MySQL 访问频繁，考虑 Master/Slave 读写分离；数据库分表、数据库切片（分布式），也考虑使 用相应缓存服务帮助 MySQL 缓解访问压力
选项
max_connections query_cache_size sort_buffer_size
record_buffer table_cache
缺省值
100 0 (不打开）M 16M
16M 512
说明
MySQL服务器同时处理的数据库连接的最大数量
查询缓存区的最大长度，按照当前需求，一倍一倍 增加，本选项比较重要
每个线程的排序缓存大小，一般按照内存可以设置 为2M以上，推荐是16M，该选项对排序order by， group by起作用
每个进行一个顺序扫描的线程为其扫描的每张表分 配这个大小的一个缓冲区，可以设置为2M以上
为所有线程打开表的数量。增加该值能增加mysqld 要求的文件描述符的数量。MySQL对每个唯一打开 的表需要2个文件描述符。
8M
128M 0 256M
innodb_log_buffer_size
128K
8M
说明
InnoDB使用一个缓冲池来保存索引和原始数据, 这 里你设置越大,你在存取表里面数据时所需要的磁盘 I/O越少，一般是内存的一半，不超过2G，否则系 统会崩溃，这个参数非常重要
InnoDB用来保存 metadata 信息, 如果内存是4G， 最好本值超过200M
2024版SQL基础培训教材课件pptx
03 统一的语言，可用于数据查询、数据操纵、数据 定义和数据控制。
SQL语言特点与优势
可移植性强，大多数 RDBMS都支持SQL。
SQL语言优势
面向集合的操作方式，支 持数据完整性约束。
01
03 02
SQL语言特点与优势
功能强大，支持复杂的数据操作和数据完整性约束。 学习曲线平缓，容易上手。
学习目标与课程安排
01 学习目标
02
掌握SQL基本概念和语法。
03
能够使用SQL进行数据查询、数据操纵、数据定义和数
据控制。
学习目标与课程安排
• 了解数据库设计和优化原则，能够编写高 效的SQL语句。
学习目标与课程安排
第1章
SQL概述与基础知识
第2章
SQL数据查询基础
学习目标与课程安排
第3章
SQL数据操纵基础
第5章
SQL数据控制基础
第4章
SQL数据定义基础
第6章
数据库设计和优化原则
02
数据类型、运算符与表达式
Chapter
数据类型介绍
01
ቤተ መጻሕፍቲ ባይዱ
02
03
数值型数据
包括整数、浮点数等， 用于存储数学运算中 的数值。
字符型数据
包括字符串、字符等， 用于存储文本信息。
日期型数据
包括日期、时间等， 用于存储和处理日期 时间信息。
主键、外键约束实现数据完整性
主键约束
确保表中每条记录的唯一性，防止重复数据的插入。
外键约束
建立表与表之间的关联关系，保证数据的引用完整性 和一致性。
级联操作
在外键约束中设置级联更新和级联删除，自动处理相 关联表的数据变更。
《数据库语言SQL》课件
06 SQL综合案例分析
CHAPTER
案例一：学生信息管理系统
要点一
总结词
要点二
详细描述
简单易用、功能全面
学生信息管理系统是一个常见的数据库应用案例，主要用 于存储和管理学生信息。通过SQL语言，可以实现对学生 信息的增删改查、排序、筛选等操作，方便学校对学生信 息的管理和维护。
案例二：商品销售系统
数据控制语言（DCL）
总结词
用于控制对数据库的访问权限
详细描述
DCL（Data Control Language）包括 GRANT和REVOKE等命令，用于控制对数 据库的访问权限。GRANT命令用于授予用
户或角色对数据库对象的访问权限，而 REVOKE命令则用于撤销先前授予的权限。 通过合理地使用DCL，可以确保数据库的安
数据操纵语言（DML）
总结词
用于插入、更新和删除数据库中的数据
详细描述
DML（Data Manipulation Language） 包括INSERT、UPDATE和DELETE等命令， 用于插入、更新和删除数据库中的数据。 INSERT命令用于向表中插入新行， UPDATE命令用于修改表中的现有行，而 DELETE命令则用于删除表中的行。
《数据库语言SQL》PPT课件
目录
CONTENTS
• SQL简介 • SQL基础语法 • SQL进阶知识 • SQL应用实践 • SQL与其他数据库语言的比较 • SQL综合案例分析
01 SQL简介
CHAPTER
Sured Query Language）是一种用于管理关系数 据库的标准编程语言。
分组等高级功能来处理查询结果。
数据定义语言（DDL）
总结词
《数据库概述》课件
3
部署和维护
4
将应用程序部署到生产环境并持续维 护。
需求分析
分析和确定数据库应用的具体需求。
程序开发
编写和测试与数据库交互的应用程序。
数据库应用程序的开发
1 Web应用
开发基于Web的数据库应用，方便用户通过浏览器访问和操作数据。
2 移动应用
开发移动应用，使用户可以随时随地对数据库中的数据进行操作。
2 数据操作
SQL可以用于插入、更 新、删除和查询数据库 中的数据。
3 数据定义
SQL还提供了定义数据 库结构和模式的功能。
数据库系统的组成
数据
存储在数据库中的数据，包 括结构和内容。
软件
数据库管理系统和其他应用 程序。
硬件
用于存储和处理数据库的计 算机和设备。
数据库应用开发
1
数据建模
2
设计和建立数据库的结构和模式。
数据库的作用
1 数据存储
数据库提供可靠的数据 存储，使数据不易丢失 或损坏。
2 数据管理
通过数据库，可以对数 据进行高效的管理和组 织。
3 数据分析
数据库可以支持复杂的 数据查询和分析，帮助 做出更明智的决策。
数据库分类
关系型数据库
通过表格和关系建立之间的连接来组织数Байду номын сангаас。
非关系型数据库
以不同方式组织和存储数据，适用于不同类型的 数据和应用场景。
关系型数据库
引入了关系模型，使数据之间的关系 更清晰和直观。
关系型数据库管理系统(RDBMS)
数据组织结构 ACID特性
事务管理
使用表格和关键字建立数据之间的关系。
提供原子性、一致性、隔离性和持久性的数据 操作。
部署和维护
4
将应用程序部署到生产环境并持续维 护。
需求分析
分析和确定数据库应用的具体需求。
程序开发
编写和测试与数据库交互的应用程序。
数据库应用程序的开发
1 Web应用
开发基于Web的数据库应用，方便用户通过浏览器访问和操作数据。
2 移动应用
开发移动应用，使用户可以随时随地对数据库中的数据进行操作。
2 数据操作
SQL可以用于插入、更 新、删除和查询数据库 中的数据。
3 数据定义
SQL还提供了定义数据 库结构和模式的功能。
数据库系统的组成
数据
存储在数据库中的数据，包 括结构和内容。
软件
数据库管理系统和其他应用 程序。
硬件
用于存储和处理数据库的计 算机和设备。
数据库应用开发
1
数据建模
2
设计和建立数据库的结构和模式。
数据库的作用
1 数据存储
数据库提供可靠的数据 存储，使数据不易丢失 或损坏。
2 数据管理
通过数据库，可以对数 据进行高效的管理和组 织。
3 数据分析
数据库可以支持复杂的 数据查询和分析，帮助 做出更明智的决策。
数据库分类
关系型数据库
通过表格和关系建立之间的连接来组织数Байду номын сангаас。
非关系型数据库
以不同方式组织和存储数据，适用于不同类型的 数据和应用场景。
关系型数据库
引入了关系模型，使数据之间的关系 更清晰和直观。
关系型数据库管理系统(RDBMS)
数据组织结构 ACID特性
事务管理
使用表格和关键字建立数据之间的关系。
提供原子性、一致性、隔离性和持久性的数据 操作。
常用中文数据库使用与检索技巧PPT课件
检索结果处理与利用
结果排序与筛选
排序方式
根据相关性、发表时间、被引次 数等指标对检索结果进行排序， 以便快速找到重要文献。
筛选条件
通过设置筛选条件，如文献类型 、学科领域、发表时间等，进一 步缩小检索范围，提高检索效率 。
结果排序与筛选
排序方式
根据相关性、发表时间、被引次 数等指标对检索结果进行排序， 以便快速找到重要文献。
检索结果处理
对检索结果进行排序、筛 选和导出；查看全文、摘 要和引文信息；利用个人 图书馆进行文献管理。
VIP维普资讯
数据库概述
检索结果处理
维普资讯是中国领先的学术文献数据 库之一，涵盖了多学科的期刊、学位 论文等学术资源。
对检索结果进行排序、筛选和导出； 查看全文、摘要和引文信息；利用个 人文献管理工具进行文献管理。
学位论文限定
限定检索结果为学位论文，获取 系统深入的研究资料。
专利文献限定
限定检索结果为专利文献，获取 技术创新和发明创造的详细信息 。
01
02
期刊论文限定
限定检索结果为期刊论文，获取 学术性较强的研究成果。
03
04
会议论文限定
限定检索结果为会议论文，获取 最新学术动态和研究成果。
文献类型限定
学位论文限定
数据库概述
定义
数据库是按照数据结构来组织、存储和管理数据的仓库，是一个长期存储在计算机内的、 有组织的、可共享的、统一管理的大量数据的集合。
类型
根据存储内容的不同，数据库可分为关系型数据库和非关系型数据库。其中，关系型数据 库以表格形式存储数据，如MySQL、Oracle等；非关系型数据库则以键值对、文档等形 式存储数据，如MongoDB、Redis等。
结果排序与筛选
排序方式
根据相关性、发表时间、被引次 数等指标对检索结果进行排序， 以便快速找到重要文献。
筛选条件
通过设置筛选条件，如文献类型 、学科领域、发表时间等，进一 步缩小检索范围，提高检索效率 。
结果排序与筛选
排序方式
根据相关性、发表时间、被引次 数等指标对检索结果进行排序， 以便快速找到重要文献。
检索结果处理
对检索结果进行排序、筛 选和导出；查看全文、摘 要和引文信息；利用个人 图书馆进行文献管理。
VIP维普资讯
数据库概述
检索结果处理
维普资讯是中国领先的学术文献数据 库之一，涵盖了多学科的期刊、学位 论文等学术资源。
对检索结果进行排序、筛选和导出； 查看全文、摘要和引文信息；利用个 人文献管理工具进行文献管理。
学位论文限定
限定检索结果为学位论文，获取 系统深入的研究资料。
专利文献限定
限定检索结果为专利文献，获取 技术创新和发明创造的详细信息 。
01
02
期刊论文限定
限定检索结果为期刊论文，获取 学术性较强的研究成果。
03
04
会议论文限定
限定检索结果为会议论文，获取 最新学术动态和研究成果。
文献类型限定
学位论文限定
数据库概述
定义
数据库是按照数据结构来组织、存储和管理数据的仓库，是一个长期存储在计算机内的、 有组织的、可共享的、统一管理的大量数据的集合。
类型
根据存储内容的不同，数据库可分为关系型数据库和非关系型数据库。其中，关系型数据 库以表格形式存储数据，如MySQL、Oracle等；非关系型数据库则以键值对、文档等形 式存储数据，如MongoDB、Redis等。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
查询优化实例（8）
• （2）计算Q2的查询时间 • ●计算自然连接：计算自然连接时读取S与SC表
的方式与Q1一致，总读取块数为2100块，花费时 间为10 5秒，但其连接结果块数大为减少，总计 104个，所花时间为104/10/20s=50s。仅为Q1的 千分之一。 • ●作选择操作：做选择操作的时间为50s。 • ●做投影操作：与Q1类似，其时间可忽略不计 • 这样，Q2的全部查询时间≈ 105+50+50= 205s
查询优化实例（5）
• 其中读S表100块，读SC表20次，每次100 块 。 由 于 每 块 花 费 时 间 1/20 秒 ， 此 时 总 共 花费时间105秒。
• 连接后的元组数为103×104=107，设每块 （约）能装10个元组，则写入中间文件要 花106/20=5×104s。
第4章 关系数据查询优化： 4.1 查询处理与优化
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（10）
• 从这3个计算时间可以看出，3种等价的查 询表达式具有完全不同的处理时间，它们 分别是10 5s、205s和10s，其差距之大令 人瞠目。
第4章 关系数据查询优化： 4.2 查询优化技术 4.2查询优化技术 4.2.1手动优化与自动优化
存放SC元组。 • ● 读/写一块磁盘的时间为1/20秒，即1秒读写20个磁盘
块。 • ● 为了简化起见，所有内存操作所花的时间忽略不计。
第4章 关系数据查询优化： 4.1 查询处理与优化
查询化实例（4）
• （1）计算Q1的查询时间 • ① 首先做笛卡尔乘积 • 将S与SC的每个元组相连接，其方法为先读入S中的50个
元组（5*10）至s表中的内存缓冲区，然后不断地将SC的 元组按100位一块读入后与S的元组相连接，直至读完所 有SC元组（共计100次）。这种操作内连接满100位后就 写中间文件一次。反复进行这样的操作，直至做完笛卡尔 乘积，此时读取的总块数为 • = 100+20×100=2100块
第4章 关系数据查询优化： 4.1 查询处理与优化
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（3）
• 作如下的统一约定： • ● 设S有1000个元组，SC有10000个元组，其中修读C5
的元组数为50。 • ● 磁盘中每个物理块能存放10个S元组，或100个SC元组。 • ● 内存有6个块的缓冲区，其中5块可以存放S元组，1块
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化概念
• 一般而言，相同查询要求和结果存在不同 实现策略，系统执行这些查询策略的开销 通常有很大差别。对于一个数据库系统来 说，查询处理过程都必须面对如何从查询 的多个执行策略中进行“合理”选择的问 题，这种“择优”的过程就是“查询处理 过程中的优化”，简称为查询优化。
第4章 关系数据查询优化： 4.1 查询处理与优化
4.2.2 查询优化器（1）
• 自动进行查询优化应当是DBMS的关键技 术。由DBMS自动生成若干候选查询计划 并且从中选取较“优”的查询计划的软件 程序称谓查询优化器。
第4章 关系数据查询优化： 4.2 查询处理与优化
4.2.2 查询优化器（2）
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（2）
• 考虑查询语句对应的三种代数表达式： • Q1 =∏Sn（σS.S# =SC。S#SC.C# =‘C5’
（S×SC））； • Q2 =∏Sn（σSC.C# =‘C5’（SSC））； • Q3 =∏Sn（SσSC.C# =‘C5’（SC））。
• 1.查询优化器所使用的技术可分三类. • ① 规则优化技术 如果查询仅仅涉及查询语句本身，根据
某些启发式规则，例如“先选择、投影和后连接”等就可 完成的优化。其特点是对相应关系代数表达式进行等价变 换，以减少执行开销，也称为代数优化。 • ② 物理优化技术 如果优化与数据物理组织和访问路径有 关，需要对如何选择实现策略进行必要考虑。 • ③ 代价估算优化技术 对于多个候选策略逐个进行执行代 价估算，从中选择代价最小的作为执行策略。
数据库系统基础教程
第4章 关系数据库查询优化
第4章 关系数据查询优化： 4.1 查询处理与优化
4.1 查询处理与优化 关系数据查询过程
查询要求 相关数据统计信息
数据库
查询结果
DML 处理器
查询转换为内部格式
关系代数语法树
查询优化器
制定执行策略
查询计划
代码生成器
生成执行代码
执行代码
运行处理器
执行查询代码
• ③ 最后做投影操作 • 第二项操作的结果满足条件的元组数为50个，它
们可全部存放在内存。对他们在S上做投影操作， 由于是在内存中进行，其时间可忽略不计。这样 Q1的全部查询时间约为105+2×5×104 ≈ 105s。 注意到一天为86400s，所以这个运算需要超过一 天的时间来完成。
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（6）
• ② 其次做选择操作 • 从中间文件中读出连接后的元组，按选择
要求选取纪录（此项为内存操作，时间可 忽略不计），此项操作所需时间与写入中 间文件时间一样，即5×104s。满足条件的 元组假设为 50个，均放在内存。
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（7）
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（9）
• （3）计算Q3的查询时间 • ●对SC做选择操作：对SC表作选择操作需读SC
表一遍共计读100块，花费5s结果为50个元组故 不需要使用中间文件。 • ●做连接运算：对S选择后的SC左联接运算，由 于选择后的SC已全部在内存，因此全部操作时间 为S读入内存的时间共100块，花费时间为5秒。 • ●作投影运算：其时间忽略不计。 • 这样，Q3的全部查询时间≈ 5+5=10s
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（1）
• 设有关系S，C，SC，查询修读课程号为 C5的所有学生姓名。
• 对应于此查询的SQL查询语言的语句为： • SELECT S.Sn • FROM S,SC • WHERE S.Sn=SC.Sn AND SC.C# = ＇
C5＇；
• （2）计算Q2的查询时间 • ●计算自然连接：计算自然连接时读取S与SC表
的方式与Q1一致，总读取块数为2100块，花费时 间为10 5秒，但其连接结果块数大为减少，总计 104个，所花时间为104/10/20s=50s。仅为Q1的 千分之一。 • ●作选择操作：做选择操作的时间为50s。 • ●做投影操作：与Q1类似，其时间可忽略不计 • 这样，Q2的全部查询时间≈ 105+50+50= 205s
查询优化实例（5）
• 其中读S表100块，读SC表20次，每次100 块 。 由 于 每 块 花 费 时 间 1/20 秒 ， 此 时 总 共 花费时间105秒。
• 连接后的元组数为103×104=107，设每块 （约）能装10个元组，则写入中间文件要 花106/20=5×104s。
第4章 关系数据查询优化： 4.1 查询处理与优化
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（10）
• 从这3个计算时间可以看出，3种等价的查 询表达式具有完全不同的处理时间，它们 分别是10 5s、205s和10s，其差距之大令 人瞠目。
第4章 关系数据查询优化： 4.2 查询优化技术 4.2查询优化技术 4.2.1手动优化与自动优化
存放SC元组。 • ● 读/写一块磁盘的时间为1/20秒，即1秒读写20个磁盘
块。 • ● 为了简化起见，所有内存操作所花的时间忽略不计。
第4章 关系数据查询优化： 4.1 查询处理与优化
查询化实例（4）
• （1）计算Q1的查询时间 • ① 首先做笛卡尔乘积 • 将S与SC的每个元组相连接，其方法为先读入S中的50个
元组（5*10）至s表中的内存缓冲区，然后不断地将SC的 元组按100位一块读入后与S的元组相连接，直至读完所 有SC元组（共计100次）。这种操作内连接满100位后就 写中间文件一次。反复进行这样的操作，直至做完笛卡尔 乘积，此时读取的总块数为 • = 100+20×100=2100块
第4章 关系数据查询优化： 4.1 查询处理与优化
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（3）
• 作如下的统一约定： • ● 设S有1000个元组，SC有10000个元组，其中修读C5
的元组数为50。 • ● 磁盘中每个物理块能存放10个S元组，或100个SC元组。 • ● 内存有6个块的缓冲区，其中5块可以存放S元组，1块
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化概念
• 一般而言，相同查询要求和结果存在不同 实现策略，系统执行这些查询策略的开销 通常有很大差别。对于一个数据库系统来 说，查询处理过程都必须面对如何从查询 的多个执行策略中进行“合理”选择的问 题，这种“择优”的过程就是“查询处理 过程中的优化”，简称为查询优化。
第4章 关系数据查询优化： 4.1 查询处理与优化
4.2.2 查询优化器（1）
• 自动进行查询优化应当是DBMS的关键技 术。由DBMS自动生成若干候选查询计划 并且从中选取较“优”的查询计划的软件 程序称谓查询优化器。
第4章 关系数据查询优化： 4.2 查询处理与优化
4.2.2 查询优化器（2）
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（2）
• 考虑查询语句对应的三种代数表达式： • Q1 =∏Sn（σS.S# =SC。S#SC.C# =‘C5’
（S×SC））； • Q2 =∏Sn（σSC.C# =‘C5’（SSC））； • Q3 =∏Sn（SσSC.C# =‘C5’（SC））。
• 1.查询优化器所使用的技术可分三类. • ① 规则优化技术 如果查询仅仅涉及查询语句本身，根据
某些启发式规则，例如“先选择、投影和后连接”等就可 完成的优化。其特点是对相应关系代数表达式进行等价变 换，以减少执行开销，也称为代数优化。 • ② 物理优化技术 如果优化与数据物理组织和访问路径有 关，需要对如何选择实现策略进行必要考虑。 • ③ 代价估算优化技术 对于多个候选策略逐个进行执行代 价估算，从中选择代价最小的作为执行策略。
数据库系统基础教程
第4章 关系数据库查询优化
第4章 关系数据查询优化： 4.1 查询处理与优化
4.1 查询处理与优化 关系数据查询过程
查询要求 相关数据统计信息
数据库
查询结果
DML 处理器
查询转换为内部格式
关系代数语法树
查询优化器
制定执行策略
查询计划
代码生成器
生成执行代码
执行代码
运行处理器
执行查询代码
• ③ 最后做投影操作 • 第二项操作的结果满足条件的元组数为50个，它
们可全部存放在内存。对他们在S上做投影操作， 由于是在内存中进行，其时间可忽略不计。这样 Q1的全部查询时间约为105+2×5×104 ≈ 105s。 注意到一天为86400s，所以这个运算需要超过一 天的时间来完成。
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（6）
• ② 其次做选择操作 • 从中间文件中读出连接后的元组，按选择
要求选取纪录（此项为内存操作，时间可 忽略不计），此项操作所需时间与写入中 间文件时间一样，即5×104s。满足条件的 元组假设为 50个，均放在内存。
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（7）
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（9）
• （3）计算Q3的查询时间 • ●对SC做选择操作：对SC表作选择操作需读SC
表一遍共计读100块，花费5s结果为50个元组故 不需要使用中间文件。 • ●做连接运算：对S选择后的SC左联接运算，由 于选择后的SC已全部在内存，因此全部操作时间 为S读入内存的时间共100块，花费时间为5秒。 • ●作投影运算：其时间忽略不计。 • 这样，Q3的全部查询时间≈ 5+5=10s
第4章 关系数据查询优化： 4.1 查询处理与优化
查询优化实例（1）
• 设有关系S，C，SC，查询修读课程号为 C5的所有学生姓名。
• 对应于此查询的SQL查询语言的语句为： • SELECT S.Sn • FROM S,SC • WHERE S.Sn=SC.Sn AND SC.C# = ＇
C5＇；