关系代数与SQL查询优化的研究

关系代数与SQL查询优化的研究（共4篇）

关系代数与SQL查询优化的研究 篇1

关系代数与SQL查询优化的研究

详细分析关系代数表达式等价变换前后的查询代价.针对DBMS查询优化器如何生成成本最小的查询计划问题,给出关系代数表达式的优化规则.提出基于关系代数运算等价变换规则的`SQL查询优化策略.该策略提供了查询优化器生成成本最小的查询计划的设计依据.

作 者：王峥 王亚平WANG Zheng WANG Ya-ping 作者单位：王峥,WANG Zheng(郑州铁路职业技术学院,河南郑州,450052)

王亚平,WANG Ya-ping(西安外事学院,陕西西安,710077)

刊 名：电子设计工程 ISTIC英文刊名：ELECTRONIC DESIGN ENGINEERING年，卷(期)：17(8)分类号：O158 TP311.132.3关键词：关系代数 查询优化 查询计划 优化策略 relational algebra query optimization query plan optimization strategy

关系代数与SQL查询优化的研究 篇2

关系代数是一种抽象的查询语言,它是关系数据库标准语言SQL查询操作的理论基础。所以采用关系代数除法运算与SQL子查询对比的方法进行教学,将抽象的除法运算与具体的数据库操作联系起来,收到了良好的效果。

1 关系代数中的除法及SQL查询

给定关系R(X,Y)和S(Y,Z),其中X,Y,Z为属性组。R中的Y与S中的Y可以有不同的属性名,但必须出自相同的域集。R与S的除运算得到一个新的关系P(X),P是R中满足下列条件的元组在X属性列上的投影:元组在X上分量值x的像集Yx包含S在Y上投影的集合。即:R÷S邀tr[X]|tr∈R∧πY(S)哿Yx妖。其中Yx表示x在R中的像集,x=tr[X]。

SQL语言中数据查询功能使用最频繁,在数据库开发中使用方式灵活,带有EXISTS谓词的子查询是数据查询的一个重点,其查询语句格式为:

其中字段名是表1和表2的共同字段。

2 实例分析

以学生选课数据库为例,数据库中的各表如表1~表3。

2.1 查询选修全部课程的学生学号

问题分析:关系代数表达式涉及到的关系有课程表、选课表。在课程关系中找到所有课程,对课程表做投影运算πcno(C)(1),再在选课表中判断每一个学号所选修的课程,即对选课表做投影运算πsno,cno(SC)(2),若(2)的sno对应的cno包含(1)中的cno,则sno就是所求结果之一。针对sc中的学号s1选修的课程号为c1,c2,c3包含课程关系的所有课程号(c1,c2,c3),所以s1是结果之一,同理可得s2,s3,s4不包含在结果中。因此关系代数表达式为πsno,cno(sc)÷πcno(C)。如何用SQL语句来实现,授课时,把问题转换为如何求解与此关系代数表达式等价的SQL查询语句。

SQL语言中没有全称量词坌(For All),具体实现时,可以把带有全称量词的谓词转换为等价的带有存在量词的谓词□x P(x)≡┒□x(┒P(x),谓词P(X)表示学生选修的课程。这样就可以用双嵌套not exists来实现带全称量词的查询,解决所谓for all的问题。此例的等价自然语义是输出这样的学号,不存在某门课程,在他的选课记录里没有选这门课。通过自然语义,则对应的SQL语句设计如下:

在sql server 2000的环境下运行结果如图1所示。

2.2 查询至少选修学生s3选修的全部课程的学生学号

问题分析:找到学号s3学生选修的课程,涉及到sc表,对sc进行选择和投影运算πcno(σ学号='s3'(SC))(1),再在sc表中针对每一个学号,找选修的课程,即对sc表进行投影运算,πsno,cno(SC)(2),若(2)学号的像集cno包含了(1)中的cno,则(2)中的sno包含在结果中。所以此题被除关系和除关系都是sc表,关系代数表达式为πsno,cno(SC)÷πcno(σ学号='s3'(SC))。对应的sql语句,可以做如下分析:P表示谓词:学生s3选修的课程y,Q表示谓词:所求学生x选修的课程y。则查询可以表示为□y(P(y)→Q),表示只要s3学生选修的课程,所求学生也选了,可以转换为如下等价形式:□y(P(y)→Q)≡┓y□(P(y)∧┓Q),即输出这样的学生学号。同样可以用双嵌套not exists来实现。由此得到sql查询语句如下:

在sql server 2000的环境下运行结果如图2所示。

3 讨论说明

查询应用中,SQL语句中使用exists的子查询并不一定与除法运算一一对应,在两者的对比教学法中,我们主要解决了实际语义中带有“全部”,“至少”此类问题的关系代数除法运算与sql查询的分析手段及解决方法。

4 结束语

除法运算在教学中一直是学生不能真正理解的关系代数运算,带有exists的子查询是SQL语言中学生最难掌握的内容,在教学中,我们以对比教学法,以理论教学与实践教学相结合的方式,在sql server 2000的环境下,引导学生在感性的认识上进一步理解除法运算的定义,更熟练地掌握带有exists的子查询的使用以及两者之间的联系。

参考文献

[1]李春葆.数据库原理及应用系统解析[M].北京:清华大学出版社,2002.

关系代数与SQL查询优化的研究 篇3

关键词：海量数据；多数据库；表分区；分表技术

中图分类号：TP311.131文献标识码：A文章编号：1007-9599 （2010） 14-0000-03

Massive Data Query Optimization Research and Application Based on Data Partition

Jia Junkai1,Xu Hui1,Gu Guoqiang1、2

(1.Donghua University,School of Computer Science,Shanghai200051,China;2.Shanghai Triman Informatin&Technology Co.,Ltd.,Shanghai200051,China)

Abstract:The low efficiency of massive data data query,comparison and analysis of a variety of massive data query optimization solutions,advantages and disadvantages,division massive data based on data query performance optimization.The method uses multi-database processing,table partitioning,sub-table technology will be in three dimensions the data stored in the data division,reducing the size of massive data queries.After large-scale experiments that the method improves the efficiency of massive data queries.

Keywords:Massive data;Multi-database;Table partition;Points table technology

在面对海量数据时，用户关心的是如何从其中查询出对自己有价值的信息而非海量数据本身，如何使查询海量数据的性能更为高效是目前国内外数据库系统研究的热点问题。

在海量数据查询优化策略有：一：代数优化；它对查询语句进行变换以减少语句执行开销[1]。二：规则优化；它是根据启发式规则选择执行策略[2]。两种方法的不足表现在：当数据量规模超出系统软硬件处理能力时，通过优化语句很难提升查询性能。三：物理优化；它是选择合适的存储策略进行的优化，但是在对语句的执行效率考虑不足。四：代价估算优化。它是对已经存的优化策略进行代价估算，选择最小的执行代价策略[3]。不足：计算最小执行代价耗费时间过多且实用性不高。

目前的海量数据查询性能低下大都是由于数据的规模超出系统的软硬件处理能力。本文利用多数据库中间件插件技术在存储海量数据时将数据划分存储到多个自治的数据库中，在一维上降低数据规模并优化查询语句；在本地数据库中运用表分区技术，将海量数据划分存储到多个表分区中，在二维上降低海量数据的规模；利用分表措施将分区表分成多个子表，再次降低海量数据的规模。通过对海量数据的三维划分、优化查询语句以及降低数据的扫描规模提高了海量数据的查询性能。

一、数据划分

数据划分是指按照某种规则将数据分布到特定范围内，使得在对数据进行查询时系统并行处理能力提高，以此降低查询的响应时间，提高数据库的查询性能。数据划分对于能否充分利用系统的cpu和带宽资源，减少通信开销，平衡系统负载和减少计算量，最佳的发挥并行性和系统性能至关重要[4]。

（一）多数据库并行处理技术

多数据库并行处理技术是以中间件为技术支撑，对海量数据进行合理存储，高效查询的一种技术。多数据库并行处理结构如图a所示：

当用户提出加载请求时，通过负载均衡系统将请求均衡的分发给并行加载服务，并行加载服务首先读取全局数据字典中的元数据，通过数据划分器和表加载器将数据加载到底层数据库中，当用户提出的查询服务时，系统将请求发给并行查询服务，并行查询服务首先读取全局数据字典用于获得多数据库的配置信息，然后通过查询语句改写服务优化查询语句并发送给查询服务器，用以完成数据的查询功能。

多数据库并行查询技术通过中间层组件对查询语句进行分析、优化，根据分析的结果将查询分解或者复制为多个等价的子查询，将多个子查询语句在相应的数据库节点上执行，它降低了每个本地数据库的查询规模，并实现查询的本地化并行查询，提高了数据的查询效率。

（二）表分区

针对底层数据库数据查询规模大的问题，对其特定业务的查询条件属性做表分区，以提高查询的性能。图b为根据时间将底层数据库中的数据进行表分区的示意图。

由图可知，经表分区后，数据在数据库中按一定的规律存放。当进行数据查询时，系统只需扫描存放数据的响应表分区，因而查询扫描规模可以大大降低，数据查询性能也会得到相应的提高。

当分区的规模较大时，在海量数据库中无法有效降低数据规模，这样的数据划分无意义；当用户分区规模较小时，在进行多数据查询时，会涉及到多个表分区，查询性能也难以提高。因此表分区的粒度需要优先考虑两方面：一是数据的存储规模；二是用户的查询粒度。

（三）分表技术

1.原理。

在分区表的基础上，将表根据所查找的属性字段划分成为多个子表，利用数据库的Union ALL视图将需要查询的子表动态的合并起来，使对原表的查询转化为对视图或者子表的查询，以此降低数据查询的扫描规模。

2.算法。

输入：业务层提交给中间层的查询语句；

输出：语句改写后的查询语句；

流程：

（1）将业务层提交的查询语句提交给语法分析器；通过语法分析器截取where子句后的条件表达式。

（2）分析条件表达式中是否含有与分表字段有关的条件。当不包含分表字段时返回原语句，算法结束；否则提取该字段。

（3）根据提取的字段分析其涉及到的数据范围，找到相对应的视图或者子表。

（4）将原语句中from子句后的原表改为3得到的视图或者子表，此时得到查询语句a。

（5）去除a中与分表有关字段的条件，返回改写后的查询语句，算法结束。

二、数据划分性能分析

（一）数据库级别的数据划分

通过中间件，将复杂查询语句转化为简单查询，将的串行查询改为在各个本地库上的并行查询。如果将数据分布到各个数据库中，此时需要的网络和磁盘I/O开销会非常的大，通过中间组件服务在数据加载时预先进行数据布局，在对大规模的数据进行处理的时候，不需要将中间结果在局部数据库之间的网络上进行传输，可以极大提高并行处理的性能。

（二）数据库表级别的数据划分

为更好的分析数据划分对查询性能的作用，建立以下场景：设有结构为TB{RID，CSDZ，XZDZ，CJSJ，NRXX}的表，RID为主键，CSDZ和XZDZ为二维空间属性，其各自的值域为0-31，CJSJ为时间属性，NRXX为内容信息属性。

通过表分区的方式将其按照时间来进行数据划分。由于数据量大并且查询针对小时范围较为频繁，选取按小时对海量数据进行表分区。

不考虑物理存储的条件下：

假设a查询扫描同等数据规模所花费的时间相同。

假设b查询的响应时间仅仅考虑数据的扫描时间。

假设c查询数据划分的各部分时能充分并行。

对于基于表分区的数据划分：

查询时间间隔为n小时的数据时，理想条件下，数据被平均的分成24分，这样查询扫描的数据在规模上减少到n/24；在假设a和假设b的条件下，查询的响应时间会减少到n/24，在查询性能上提高了24/n倍。实际情况下，数据不可能被完全的平分，这样查询扫描的数据在最坏情况下受到最大表分区规模的制约。

对于基于分表的数据划分：

设关系R为CSDZ和XZDZ之间组成的关系，关系R可以划分成多个子关系Rc_x，在CSDZ和XZDZ的数据划分基础上，针对全表的查询，在假设c成立的条件下，其查询性能受限于最大子关系的数据规模上。假设最大子关系的数据规模为m，总的数据规模为v，在上述假设都成立的情况下，查询性能的响应时间减少为原来的m/v，性能提高了v/m；考虑数据结果合并的开销，设原来查询相应时间为T，查询处理时间为T1，查询合并时间为T2，则T=T1+T2。基于上述分析，T1的时间减少为原来的m/v，查询合并时间不变，则经过数据划分后查询的响应时间为m/v*T1+T2，其查询性能提升T1+T2/（m/v*T1+T2）。

在考虑物理存储的条件下：

假设rownum为原表中信息总数，length为每条记录的长度，k为子关系的个数，ROWNUMi为子关系i的信息总数。在数据划分之前，设q为查询的总时间复杂度，则q=O（rownum*length）。设q1为数据划分后的查询总时间复杂度，q2为所有子关系的查询时间复杂度总和，q3为返回合并结果的时间复杂度。则q1=q2+q3。因海量数据查询时查询的响应时间远远大约返回合并结果的时间复杂度，即q3<

三、海量数据划分策略的总体架构

通过对数据划分的理解以及对数据划分性能的分析，本文提出基于数据划分策略的海量数据查询模型总体架构分为如图c所示：

底层是各种异构数据库，通常我们称之为“本地数据库”；此层主要用来存放海量数据，并在此层实现表分区、分表的数据划分。中间层是一个基于CORBA标准的事务处理“中间件”层。它的作用是优化数据的存储结构，改写语句的执行策略。上层为业务层，为中间层提供数据和查询语句来源。

该架构数据划分存储到多个自治的数据库中并优化查询语句的执行效率，在底层数据库中用表分区、分表减少数据规模以期提高查询效率。

四、试验

试验一：

试验方案：在公安系统中的oracle9i数据库中选取一组集群，在集群中建立一张TB表，同时建立1000张与TB表相同表结构的子表，其表命名为TB1-TB1000。将数据量为1TB的数据导入到TB表中，并将这1TB的数据平均划分然后导入到1000张子表中，同时将子表动态的组成一张和TB表相同的视图。对TB表和视图分别进行大规模数据查询。

本试验的目的在于测试相同数据规模下单表查询和对多表建立的视图查询的数据查询性能对比，因此在视图查询时不采用并行查询方式，仅测试多表建立的全局视图对查询性能的影响。

试验结果：在数据规模都在80000万的条件下，在串行条件下，对无索引的时间进行顺序扫描，单表扫描的时间大约为十五至二十分钟多分钟，而对分表建立的索引所花费时间要超过二十五分钟；对分表属性进行查询时，二种方式的查询都不超过三分钟。当调整查询方式为并行查询，并行度为10时，对时间的扫描，二者都约三分钟。对内容字段的查询，二者在查询性能上差别不大。

试验结论：在相同规模的海量数据情况下，虽然通过对分表建立统一视图会导致存储空间的增大，但是不明显。并行全视图扫描在查询性能和单表查询达到相同的水平，因而得出结论分表视图的方案可行。

试验二：

方案：在试验一得基础上，通过中间件的并行查询服务对TB表进行单向匹配、双向匹配、不同属性值之间组合的统计查询。

试验结果：

结果分析：在三类查询中除测试c中的一般查询外，分表后的查询相应时间都比为分表后的响应时间短。测试c的一般查询涉及到未进行分表的内容字段，此时会受到文本索引的影响，同时查询结果会在局部的数据库之间传递，此时网络通信时间会占据数据查询响应时间的大部分时间。在对常用的分表字段进行条件查询时，因对数据的扫描规模降低，数据查询相应时间会小于未分表的查询相应时间。进而证实了多维数据划分查询优化方法的可行性。

五、结论

通过对海量数据查询的优化与性能的分析，基于数据划分策略的海量数据优化方法解决了因数据规模在系统的软硬件处理能力之外所造成的数据查询性能低的问题。查询性能得到大幅提升。

数据划分中的分表技术将原表转为子表，子表太多会使系统I/O的次数剧增，造成数据查询性能降低。因此我们要不断的总结、探索找到子表个数的峰值，使得系统充分利用并行性的时候数据的查询性能最高。

参考文献：

[1]朱宁贤.数据库查询的优化策略探讨.办公自动化杂志,2007,8

[2]王能斌.数据库系统.电子工业出版社,1998

[3]韩忠,汪伟.海量数据的查询优化.科技资讯,2006,10

[4]侯潇.海量数据处理中基于数据划分的查询优化研究与实现,2007,11

[5]陈光.多数据库事务处理策略的研究,2000,4

关系代数与SQL查询优化的研究 篇4

关键词：数据库 性能 调整与优化

SQL SERVER数据库作为计算机系统的核心，基于SQL SERVER数据库性性能的调整与优化主要目的就是通过将网络流通、磁盘I/O和CPU时间减到最低，减少每个查询时间，以此提高数据库服务的吞吐量。SQL SERVER数据库性能调整与优化是提高计算机系统稳定性的重要技术支撑。

1 SQL SERVER数据库的特点

①具有高性能设计和先进的管理系统。高性能设计就是说其可以利用windows NT 为计算机提供优越的服务，并且通过先进的管理系统实现计算机功能的全面，比如可以为计算机使用者提供支持本地以及远程的管理与配置，同时也具有图形化管理功能。②具有强大的处理功能和兼容性。SQL SERVER数据库性具有事务处理功能，它可以根据具体的计算机系统要求正确的保持数据的完整，实现相关数据的安全，同时SQL SERVER数据库性可以兼容不同的计算机系统，能够根据不同的计算机系统为使用者提供一个稳定的数据库平台。

2 SQL SERVER数据库性能调整与优化的方法

无论什么原因导致计算机数据管理系统出现问题都会影响数据库的运行效率，因此要想提高SQL Server数据库性能发挥最大效率，应该不断调整与优化SQL Server数据库系统，实现SQL Server数据库系统各个功能的最大发挥。

2.1 SQL SERVER数据库设计优化 要想提高SQL SERVER数据库性能的稳定性，就必须首先提高数据库的设计，保证数据库设计方案的性能做大优化。

2.1.1 数据库的事务设计。数据库事务系统是由不同的SQL语句模块所构成的，事务处理是由计算机系统的应用程序实现的，因此事务处理的起止点也应该由应用系统完成，基于此程序，数据库事务设计，要遵循运行效率的最大化原则，要保证数据库短事务，实现事务中的SQL语句能够科学的占有与释放系统资源，避免在系统运行中占用过多的资源而导致系统运行速度的下降。

2.1.2 降低返回数据服务器信息的数量。在进行数据库设计时，要对数据量比较大的表进行自定义查询条件设置，其目的就是防止因为错误的信息选择，而导致数据的频繁返回，而增加网络信息的拥堵，加重服务器的负担。因为如果过多的信息返回就会引起SQL数据库性能的下降，而一旦信息返回量超过数据库系统的容量后，就会导致客户端在运行时出现错误。

2.1.3 提高数据显示，加快屏幕的切换速度。屏幕数据的显示速度对于计算机系统的运行效果也会产生重要的影响，如果屏幕中的功能过多的话，就会影响屏幕的切换速度，因此数据库设计时一定要做到：一是尽量减少在一个屏幕内放置过多的显示功能，因为过多的显示功能会影响到屏幕的切换速度，增加用户等待时间；二是避免屏幕出现空屏等待的现象发生，就要保证数据再返回服务器之后再进行数据的显示，以此提高屏幕的切换速度；三是在进行数据检索时，一定要及时对数据库缓冲区的数据记录进行限制，以此提高系统的运行速度。

2.2 SQL语句优化 对SQL语句优化主要是通过调整与优化SQL语句实现，因为SQL Server数据库具有高效的查询功能，它能够对查询语句进行分析，进而从数据库中找到符合条件的结果，因此通过优化SQL语句可以为查询过程节省大量的时间与资源。根据目前技术的发展现状，SQL语句优化中常用的方法：

2.2.1 查询语句中尽量不要使用“*”符号进行查询，应该选用SELECT*FROM table这种方式。因为带有“*”的查询语句，需要数据库将“*”替换为表后，才能将结果反馈给用户，而这个过程会消耗系统工作时间，占用一定的系统资源，因此在语句编写时，要将语句按照SELECT专业编号的格式编写，而不应该采用SELECT*FROM专业表。

2.2.2 采用TRUNCATE 代替DELETE进行删除操作。之所以采用TRUNCATE主要是因为此语句的速度要更快，其占用的系统资源相对比较少，而且TRUNCATE删除信息时，可以对删除的信息进行保存，以便系统备份数据恢复时使用，因此SQL语句的优化一般选用TRUNCATE语句释放表。

2.3 调整与优化磁盘I/O

数据库的相关数据要以磁盘的方式储存，因此磁盘I/O 操作是提高数据库性能的重要一方面，也是影响SQL Server 数据库稳定性的主要瓶颈之一，因此及时监控磁盘I/O，对其合理区分磁盘I/O是提高SQL Server数据库性能的重要手段之一。

3 结束语

随着计算机技术的广泛应用，SQL Server数据库的应用范围越来越广泛，基于SQL Server数据库对计算机系统的运行效果的关键性影响，因此研究SQL Server数据库性能的调整与优化具有非常重要的现实意义。SQL Server数据库调整与优化属于系统工程，调整与优化SQL Server数据库性能的方法有很多种，本文所阐述的几点调整与优化的方法主要是经常使用的，无论基于哪种优化方法其目的就是通过优化SQL Server数据库性能，提高计算机系统的运行速度和分析。

参考文献：

[1]孟盛.SQL Server数据库安全系统分析[J].价值工程，2011（12）.

[2]丁向朝.SQL Server数据库查询语句优化的研究[J].华章，2012（25）.