SQL优化器和语句处理性能分析过程.docx
《SQL优化器和语句处理性能分析过程.docx》由会员分享，可在线阅读，更多相关《SQL优化器和语句处理性能分析过程.docx（21页珍藏版）》请在冰豆网上搜索。
SQL优化器和语句处理性能分析过程
5.1SQL语言概述
SQL是结构化查询语言（structuredquerylanguage）的缩写，是关系数据库系统的标准语言。
它提供给用户一种表示方法，说明要查询的结果特性，至于如何查询、以及查询结果的形式都由数据库系统来完成。
这种语言由于其功能丰富、方便易学而受到用户的广泛欢迎，1986年由美国国家标准局（ANSI）及国际化标准组织（ISO）公布，作为关系数据库系统的标准语言。
目前广泛使用的SQL标准是1992年制定的SQL-92（简称为SQL2）。
SQL3是最新的SQL语言标准，在SQL2的基础上增加了许多新的特征。
SQL标准的制定使得几乎所有的数据库厂家都采用SQL语言作为其数据库语言，但各个数据库厂家又结合自身需要在SQL标准的基础上进行了扩充。
SQL名为结构化查询语言，实际功能包括数据定义、数据操纵和数据控制。
具体来说，包含以下几部分内容：
（1）数据模式定义语言（datadefinitionlanguage，DDL）。
用来定义、修改或者删除基本关系表、视图、索引、完整性约束等数据库对象。
（2）数据操纵语言（datamanipulationlanguage，DML）。
用来查询、插入、删除或者修改数据表中的记录。
（3）嵌入式SQL语言：
被嵌入程序设计语言中，执行SQL语句处理。
（4）权限管理：
用来管理和控制对数据库对象的访问。
有关SQL语言的详细描述，可以参看其它的数据库书籍，这里不再进一步介绍。
对具体的数据库系统，在使用时可以阅读该系统的相关资料，了解其SQL语言的具体使用方法。
5.2优化器概述
用户以SQL语句的形式，向数据库系统发出请求。
系统在接收到用户的SQL语句请求后，由优化器进行语法分析和优化，找出SQL语句的执行计划，然后按照此执行计划运行，最后将处理结果返回用户。
一个SQL语句可能有很多种等价的执行方式。
优化器需要对所有的执行方式进行分析，选择费用最低的执行方式，作为该SQL语句最终的执行计划。
5.2.1为什么要使用优化器
我们知道，数据的存储和管理经历了三个阶段：
人工管理阶段、文件系统阶段和数据库系统阶段。
在人工管理和文件系统阶段，应用程序和数据紧密结合，应用程序随着数据结构的改变而改变，从而导致数据的利用率不高，数据不易管理，数据一致性很难维护等问题。
而在数据库系统阶段，数据库系统采用三级模式结构，由外到内依次为：
外模式、模式和内模式，在外模式和模式、模式和内模式之间建立映象关系。
这样一来，如果模式发生改变，只需要改变外模式和模式之间的映象，外模式不需要改变。
同样，如果内模式发生改变，需要改变的只是模式和内模式之间的映象。
这种多模式结构，保证了数据的物理和逻辑独立性，减少了数据冗余，提高了数据利用率。
同时，也使用户可以使用统一的接口访问数据库中的数据，由数据库系统来决定数据的处理方式，用户不用、也不必了解数据在数据库如何存放，从而极大地方便了用户的使用。
数据库技术的发展经历了层次数据库系统、网状数据库系统、关系数据库系统三个阶段。
层次数据库系统使用层次数据模型，系统对用户请求的处理，就是从整个数据的顶层开始查找，一直找到要处理的数据为止。
整个数据模型的实现已经定义了数据的处理方式。
网状数据库系统使用网状数据模型，任何相互关联的数据之间都要建立联系，系统对用户请求的处理，只要根据这些联系找到要处理的数据就可以了。
该数据模型的实现也指定了数据的处理方式。
和层次数据库系统、网状数据库系统不同，关系数据库系统使用关系数据模型，就是使用二维表格表示数据以及数据之间的联系。
整个数据模型没有给定数据的处理方式。
对用户请求的处理，必须由系统根据数据字典信息来决定其处理方式，这就是优化器的工作。
一个用户的SQL语句请求可能有很多种等价的执行方式，优化器要决定SQL语句的最终执行计划。
然而可不可以由用户来决定SQL语句的执行计划呢？
答案是肯定的。
但由于数据库中的数据处于不断地变动之中，对一个SQL语句，用户提供的执行计划，只能反映数据及表结构的当前情况。
在经过一段时间的运行之后，由于数据量及表结构的变化，该执行计划就不再合适，甚至可能很糟糕。
而优化器动态地、根据数据库当前统计信息所生成的执行计划，就能够有效地适应数据量及表结构的这种变化。
对用户来说，也不用为指定SQL语句的执行计划而详细地去了解数据库对象的定义、数据的分布等相关信息。
5.2.2优化器的处理过程
在第2.2.7一节中，我们已经对优化器的处理过程有所了解，这里我们将作进一步的介绍。
数据库系统对SQL语句的处理，要经过以下三个步骤：
（1）语法分析与翻译
（2）优化
（3）执行
其具体执行过程可见图5-1。
其中，语法分析与翻译、优化过程由优化器进程完成，而SQL语句的执行由代理进程实现。
对一个SQL语句，如果系统能够在内存中找到它的最终执行计划，就直接使用，不会再去分析与优化。
1.语法分析与翻译
优化器对SQL语句的语法分析，就是构造该SQL语句的语法分析树表示，验证SQL语句是否存在语法错误、发出请求的用户是否有执行权限等。
然后基于语法分析树，将SQL语句翻译成一个关系代数表达式。
最后，对这个关系代数表达式使用等价转换规则，找出SQL语句所有等价的关系代数表达式，每一个关系代数表达式就对应SQL语句的一个执行方式。
2.优化
对表中数据的访问，可以使用表扫描，也可以使用索引。
在从多个表中返回结果时，要执行表的连接，可以采用嵌套循环连接，也可以使用排序归并连接、散列连接，并且表和表之间的连接顺序可以是任意的。
SQL语句的一个执行方式，就是各种可能执行途径的一种组合和编排方式，明确指定了语句执行的访问路径、连接方式以及连接顺序等等。
SQL语句的不同执行计划，在性能、资源使用上可能会有非常大的差异。
优化器对SQL语句的优化处理，就是要从SQL语句所有的执行方式中找出费用最低的一个，作为SQL语句最终的执行计划。
而所谓SQL语句的执行费用，就是执行SQL语句时，所需执行时间、CPU使用、内存空间使用、I/O操作数量、生成的中间结果集数量等多方面因素的综合。
其中由于I/O操作比较慢，是影响SQL语句运行的关键因素。
一些系统的优化器就单纯以I/O操作的数量来衡量SQL语句的费用。
为了找出费用最低的执行计划，而将所有的执行计划都执行一遍，这是不可取、也是不现实的。
优化器是根据数据字典中的相关信息，通过估算来决定每一个执行计划的执行费用，尽管这种估算有时候并不完全正确。
优化器对SQL语句的优化处理，由以下三个步骤完成：
（1）根据关系代数表达式，使用等价转换规则，找出所有等价的执行计划。
（2）依据数据字典中表和索引的结构定义和相关的统计信息，分别估算每一种执行计划的费用。
（3）比较所有执行计划的费用，费用最低的那一个就是SQL语句最终的执行计划。
3.优化器要使用的数据字典信息
在数据字典中，被优化器参照的表和索引信息，可以分为两大类：
结构定义、数据库对象统计信息。
对表、索引的结构定义，具体来说会包括以下这些：
（1）表中的字段定义（类型，长度等）、字段的完整性定义、表上是否存在主键和外键、表上是否存在索引，等等。
（2）索引建立在那些字段上、索引中字段的顺序（对复合索引而言）、索引的类型（唯一索引、非唯一索引、聚集索引），等等。
而对表、索引的统计信息，用户可以根据自己的需要进行选择、收集。
大体来说，有以下的内容：
（1）对一个表来说，可以统计：
表中的字段个数、表中记录的长度、表中的记录数、表的块因子（即一个数据库的数据块可以存放的记录数）、表使用的数据页数，等等。
（2）对表中的单个字段来说，可以统计其取值的最大值、最小值、不同取值的个数。
可以设定字段取值区间的个数、区间中不同取值的个数，进而可以统计其在每一个取值区间内所包含的记录个数。
也可以将多个字段合并在一起进行统计，等等。
（3）对索引来说，可以统计：
索引的高度、叶索引页的数目、索引中记录的长度、索引的块因子，等等。
正是依据这些结构定义和统计信息，优化器能够估算出SQL语句一个执行计划的执行成本。
例如：
根据一个查询条件，优化器可以检查相应字段的统计信息。
如果查询条件中的数值大于字段最大值、或者小于字段最小值，优化器就可以判断符合条件的记录数为零；如果查询条件中的数值在字段的某个取值区间之中，优化器就根据该取值区间中不同取值的个数、所包含的记录个数，估算出符合条件的记录数，再根据表的块因子，估算存放这些记录需要多少数据页、读入内存需要多少次的I/O操作，进而估算出需要CPU、内存资源的数量，等等。
优化器的整个估算过程是复杂、耗时的，特别是对一些复杂的SQL语句，如：
包含多个查询条件、需要多个表之间的连接、需要产生中间结果集、需要分组和排序等，将耗费更多的时间和系统资源。
数据库系统会采取一些措施，来减少优化器对SQL语句的估算、优化次数，这一点我们会在下面讲到。
5.2.3了解优化器处理过程的意义
SQL语言是用户访问数据库数据的通用接口。
要达到同样的目的，可以有很多种不同的SQL语句书写方式。
不同的SQL语句，尽管会产生相同的结果，但其执行过程、执行效率将会有明显的区别。
例如，要从员工表employee中返回所有员工的姓名，使用下面的SQL语句：
SELECT*
FROMemployee
优化器将选用表扫描作为执行方式，通过扫描整个表返回结果，不管员工表employee上是否存在索引。
而使用下面的SQL语句：
SELECTempy_name
FROMemployee
如果列empy_name上存在索引，优化器将选用索引扫描作为该语句的执行方式。
这种方式被称为索引覆盖。
由于索引中每条记录包含较少的字段，一个索引页包含更多的记录，使用索引扫描，将使用更少的磁盘I/O操作。
对程序员、数据库管理员来说，清楚优化器对SQL语句的优化处理过程，能够大体上判断出一个SQL语句的执行计划，不但有助于写出好的、效率高的SQL语句，而且能够找出引起系统性能瓶颈的SQL语句，从而保证系统的正常、稳定运行。
5.3优化器对SQL语句的处理
尽管找出最小成本的执行计划是优化器的职责，但对于糟糕的SQL语句，优化器也无能为力。
深入了解优化器对SQL语句的优化处理，能够帮助用户写出高效的SQL语句。
优化器在确定SQL语句最小代价的执行计划时，无外乎就是确定数据的访问路径（索引访问或者表扫描）、表连接的方式及顺序，以及索引不可用时是否进行排序等。
下面我们就从这几个方面进行讨论。
5.3.1排序处理
系统对SQL语句的处理可能需要进行排序。
这主要是由于以下两个原因引起的：
（1）在SQL语句中，指定要对结果集进行排序，或者包含需要排序的关键字。
（2）进行表连接时，系统选用排序归并连接、散列连接；或者系统虽然选用嵌套循环连接，但发现作为输入的表如果已排序，其实现效率会更高。
有关表连接的详细信息，可参见第5.3.2一节。
如果在要排序的字段上存在索引，系统就直接使用该索引，顺序读取表，从而完成排序。
如果在要排序的字段上不存在索引，或者尽管存在索引，但按照索引访问需要更多的系统开销时，系统就不会使用索引，只是在最后，基于此字段对结果集进行排序。
如果要排序的数据，可以一次在内存中完成，这种排序就称为内排序。
如果由于内存空间限制，无法一次完成数据的排序，系统就按照可用内存的空间大小，将要排序数据分成多个部分、分别排序，中间结果被存放在磁盘上，最后将多个已排序结果集合并，形成最终的结果集，这种排序称为外排序。
很显然，内排序的执行效率很高。
如果数据库中存在排序操作，我们当然希望所有的排序均能够一次在内存中完成。
在SQL语句中出现下列的关键字后，如果优化器没有选用相关的索引，就一定会进行排序操作。
1.orderby
该关键字要求对结果集进行排序。
如果表中的某些字段经常在orderby中使用，在这些字段上建立索引将有助于避免数据库中的排序操作。
2.groupby
该关键字要求对结果集进行分组。
系统对分组的处理，是首先基于要分组的字段进行排序，然后对已排序的记录数据，从上到下采取以下的处理步骤，形成结果集：
（1）取出第一条记录，放入结果集。
（2）取出下一条记录。
如果该条记录和结果集中最后一条记录在要分组字段上取值相等，就将该条记录和最后一条记录合并；如果不相等，就将该记录放在结果集的最后面。
（3）继续处理，直到所有的记录被处理完成。
例如，下面SQL语句返回各部门中员工的月工资总额：
SELECTdept_no,sum（empy_salary）
FROMemployeegroupbydept_no
系统首先对员工表employee基于部门号dept_no进行排序。
然后对已经排序的记录集，按照字段dept_no的值，将字段empy_salary中的值相加。
3.distinct
该关键字要求结果集中不能有重复的记录。
系统对distinct的处理，类似于对groupby的处理。
系统首先按照结果集中要求的字段进行排序，然后按照以下步骤剔除重复的记录。
（1）取出第一条记录，放入结果集。
（2）取出下一条记录。
如果该条记录和结果集中最后一条记录相同，就放弃该条记录；如果不相同，就将该记录放在结果集的最后面。
（3）继续处理，直到所有的记录被处理完成。
例如，下面SQL语句从员工表employee中返回所有的部门号：
SELECTdistinctdept_no
FROMemployee
系统首先读取表employee中的所有记录，基于字段dept_no进行排序。
然后对已经排序的记录集，按照字段dept_no的值，删除重复的记录。
4.集合操作：
union、intersect、minus
集合操作：
union、intersect、minus，用来实现了两个记录集之间的并、交、差运算，其中：
（1）union：
合并两个记录集，结果集中不能有重复的记录。
（2）intersect：
返回同时出现在两个记录集中的记录。
（3）minus：
返回出现在第一个记录集，而不在第二个记录集的所有记录。
对于这些集合运算，系统首先将两个记录集基于相同的字段或者字段组合，分别按照相同的方向排序，然后将这两个已经排序的记录集合从上到下进行比较，从而找到最终的结果集。
这里，我们需要注意关键字union和unionall的区别。
unionall允许结果集中有重复的记录，因此优化器不会要求对记录集进行排序。
5.3.2表连接的处理
在SQL语句的查询处理中，可能需要从多个表中返回数据，这就需要表的连接。
在表的连接时，系统需要考虑的问题有：
（1）多个表之间，按照什么样的顺序执行表的连接。
（2）采用什么样的方式，实现两个表之间的连接。
例如，下列SQL语句返回部门中所有员工的帐户余额：
SELECTb.dept_company,a.empy_name,c.acct_balance
FROMemployeea,departmentb,accountc
WHEREa.dept_no=b.dept_noanda.empy_no=c.empy_no
在执行表的连接时，可以使用以下八种表连接顺序：
（（employee,department）,account）、（account,（employee,department））
（（department,employee）,account）、（account,（department,employee））
（（employee,account）,department）、（department,（employee,account））
（（account,employee）,department）、（department,（account,employee））
此外，两个表之间的连接可以使用以下的连接方式：
嵌套循环连接、排序合并连接、散列连接。
对这个SQL语句，优化器应当选用那种表连接顺序、表和表之间使用什么样的连接方式呢？
我们知道，一个SQL语句有很多种不同的执行方式，这些表连接顺序、连接方式的不同编排和组合，就包含在不同的执行方式中。
优化器通过估算获取的最终执行计划，就给出了该SQL语句最合适的表连接顺序和连接方式。
对两个表以上的表连接，由于要产生中间结果集，优化器在估算时还要考虑它所消耗的内存、磁盘空间资源。
下面我们将对表和表之间的连接方式进行简单介绍。
1.嵌套循环连接
嵌套循环连接，由两个嵌套的循环语句组成。
处于外层循环中的表称为外层表，处于内层循环中的表称为内层表。
系统顺序地读取外层表中的每一条记录，将它和内层表中的每一条记录进行比较，如果满足条件就放入结果集。
整个连接的处理过程见图5-2。
for 表1中的每一条记录 do
begin
for 表2中的每一条记录 do
begin
测试表1和表2当前的记录是否满足连接条件
如果满足，就加入结果集
end
end
图5-2 嵌套循环连接
嵌套循环连接不要求表中存在索引，并且不管是什么条件，该算法都可以使用。
然而嵌套循环连接算法的代价很大，因为该算法要逐一检查两个表中的每一条记录。
对外层表中任一记录的处理，都要对内层表扫描一次。
如果内层表能够存放在内存中，将极大地减少磁盘的操作次数。
优化器在使用嵌套循环连接时，会考虑表中的记录数，将记录数较小的表作为内层表使用。
由于嵌套循环连接的处理效率比较低，人们已经对此算法进行了改进，这就是：
块嵌套循环连接和索引嵌套循环连接。
2.块嵌套循环连接
对于块嵌套循环连接，系统以块的方式而不是以记录的方式处理表的连接（所谓块，就是数据库系统的数据页，是对磁盘I/O操作的最小单位）。
系统顺序地读取外层表中的每一个数据块，将块中每一条记录和外层表所有块中的每一条记录进行比较，如果满足条件就放入结果集。
整个连接的处理过程见图5-3。
for 表1中的每一数据块 do
begin
for 表2中的每一数据块 do
begin
for 表1当前块中的每一条记录 do
begin
for 表2当前块中的每一条记录 do
begin
测试表1和表2当前的记录是否满足连接条件
如果满足，就加入结果集
end
end
end
end
图5-3 块嵌套循环连接
在要连接的两个表都不能放入内存时，表连接的处理不可避免要不停地以块为单位进行磁盘的读写。
使用块嵌套循环连接，将极大地减少对磁盘的I/O操作。
3.索引嵌套循环连接
在嵌套循环连接中，如果内层表在连接属性（表的属性也就是表的字段）上存在索引，系统就可以使用此索引访问内层表，从而取消内层循环对内层表的扫描，这种方法就称为索引嵌套循环连接。
使用表上的索引进行嵌套循环连接，一般来说会好于表的扫描。
优化器在优化处理时，如果选用了嵌套循环连接，一般会把连接属性上存在索引的表作为内层表来处理，从而可以提高SQL语句的处理性能。
如果内层表的连接属性上不存在索引，但是优化器经过估算后，发现在连接属性上建立临时索引，将会降低SQL语句的执行成本。
在这种情况下，优化器会在该SQL语句的最终执行计划中，要求首先建立内层表连接属性上的索引，然后再进行索引嵌套循环连接。
4.排序归并连接
将参与连接的两个表按照连接属性，分别在相同的方向上进行排序，然后将两个表排序后的记录从上到下，一一进行比较，将满足条件的记录存放到结果集中，这种连接方式就称为排序归并连接。
如果参与连接的表已经按照连接属性排序，则该表就不用排序。
当两个表的连接属性上存在索引时，这些索引可能会在排序归并连接中被使用。
5.散列连接
散列连接要使用散列函数。
如果一个表中的记录与另一个表中的记录满足连接条件，那么它们在连接属性上有相同的取值、相同的散列值。
基于此原理，散列连接的基本思想是使用散列函数，把两个表分别按照连接属性，划分成一系列有相同散列值的记录集合。
然后把具有相同散列值的记录集合，进行排序归并连接，最后将所有的中间结果集进行合并，形成最后的结果集。
我们知道，对大数据量表的排序，由于无法将数据全部装入内存，不能在内存中完成排序操作，整个排序过程分阶段进行，需要磁盘空间存放排序的中间结果，降低了排序的效率。
而且随着数据量的增长，排序的效率将大幅度下降。
因此对大数据量表之间的连接，直接使用排序归并连接，处理的效率会很低。
相对于排序归并连接，散列连接首先将大数据量表划分成许多小表，这些小表的排序可以在内存中完成，然后再执行排序归并连接，其连接的效率会得到提高。
因此散列连接适合于大数据量表之间的连接处理。
5.3.3访问路径的选择
对表中数据的访问，可以使用表扫描，也可以使用索引访问。
对一个SQL语句来说，究竟采用哪种数据访问路径，最终由优化器决定。
使用索引访问表中数据时，系统首先要将索引页读入内存，根据索引记录中的指针，找到数据所在表中的数据页，然后再将数据页读入内存，进而找到所需数据。
使用索引访问表中的一条记录，至少需要两次的I/O操作。
优化器通过估算I/O操作的多少，来决定表的访问路径。
对建有索引的表，优化器并不总是按照索引访问表中的数据。
有时候使用索引找到所需数据，会比单纯的表扫描需要更多的I/O操作。
在下列情况下，优化器会使用表扫描而不会选择索引，来作为数据的访问路径：
（1）表中的记录较少，只使用很少的数据页。
在这种情况下，优化器采用表扫描方式访问数据。
依据索引访问，反而会增加磁盘的I/O操作。
同时我们也可以看出，在这种小数据量的表上建立索引，对数据的查询不会有所帮助，反而影响到表的更新操作。
（2）查询语句需要返回大量的记录。
如果一个查询语句需要从表中返回大量的记录，例如：
返回记录占表中总记录的30%以上，使用索引将需要更多的I/O操作。
这时表扫描会是更好的数据访问路径。
（3）查询语句的条件选择中包含多个条件。
如果查询语句中的多个条件是或的关系，或者可以使用索引的查询条件不能有效地降低记录的数目，在这种情况下系统就使用一次表扫描，同时检查所有的查询条件。
将多个条件分开、使用索引查询，再进行记录集合并，将需要更多的I/O操作。
（4）表上没有索引，或者有索引，但无法使用。
为经常需要访问的表建立索引。
索引的创建，应结合应用程序，根据查询条件中字段的使用频度，决定在那些字段上建立索引。
至于索引创建所需要遵循的一些原则，可见第5.6.1一节。
（5）SQL语句的写法存在问题。
至于如何写好SQL语句，我们将在第5.6.2一节中说明。
5.4优化器优化过程的改进
优化器的优化过程需要时间，需要耗费CPU、内存等
升级会员 