TERADATA基础理论及SQL优化.ppt

TERADATA介绍及SQL优化,2009年10月,目录,TARADATA介绍SQL语言介绍与语句优化,TERADATA发展简介,1976年TERADATA公司成立，瞄准联机分析处理市场，公司目标为设计能够处理大数据量的数据库计算机。1984年推出世界上第一个基于海量并行处理技术的商用系统。硬件平台DBC（DataBaseComputer），操作系统为TOS（TERADATAOS)。1989年TERADATA公司和NCR公司合作，一起研制下一代数据库计算机。1992年，TERADATA与ncr合并，开始将封闭数据库计算机系统移植到开放平台。1996年NCR推出unix平台的TERADATA关系数据库系统。1998年推出WINDOWSNT平台下的TERADATA关系数据库系统。2007年TERADATA从NCR拆分,独立在纽约上市,TERADATA数据库简介,TERADATA是关系型数据库管理系统（RDBMS）为运行世界上最大的商业数据库而设计企业数据仓库的首选解决方案基于UNIX与NT的开放式系统平台完全遵从于ANSI工业标准，采用标准的SQL查询语言可以运行单节点(SMP)或多节点(MPP)可以作为一个贯穿整个企业的客户端应用的数据库服务器运用并行处理方式管理Terabytes字节以上的数据,TERADATA数据库的框架结构,TERADATA系统主要包括三个部分：处理节点（Node）用于节点间通信的内部高速互联（InterConnection）数据存储介质（通常是磁盘阵列）每个节点都是SMP结构的单机，多个节点一起构成一个MPP系统，多个节点之间的内部高速互联是通过一种被称为BYNET的硬件来实现的，整个系统的组成如下图所示。,TERADATA节点结构(Node),在软件结构上，每个节点自下向上包括操作系统软件（OS）、TERADATA并行数据库扩展（PDE）和相关应用程序，其中PDE的主要职责是管理和运行虚拟处理器，其中主要包括PE和AMPs。（1）PDE:并行数据库扩展（2）VPROC:虚拟处理器（3）PE:解析引擎,将接收到的SQL语句进行分解并优化。（4）AMP:存取模块处理器,完成数据的存取操作，是实现并行机制的关键。（5）VDisk:虚拟磁盘,TERADATAMPP系统,在TeradataMPP系统中，各个节点间（确切地说是各个AMP之间）的内部高速互联是通过BYNET实现的，它就是Teradata系统中那些松散耦合的节点之间互相联系的通讯总线，但事实上，它却远远没有这么简单。BYNET是一组硬件和运行在这组硬件上的一些处理通讯任务的软件进程的组合体，用于：（1）节点之间的双向广播（bidirectionalbroadcast）（2）多路传递（multicast）（3）点对点通信（point-to-pointcommunication）（4）实现SQL查询过程中的合并功能,TERADATA数据库系统数据库空间,Permspace-数据库中用来存储真正数据的空间，也称为永久空间。-只是逻辑的概念,表示数据库可使用的存储空间的上限.不预先分配.-可以动态修改Spoolspace-用于存储处理交易请求的中间结果.交易结束,空间释放.-也是逻辑的概念,表示数据库使用的spool空间不能超过此上限.-整个系统的剩余空间都可以用作spoolTempspace-用于存储临时表,session结束,空间释放-也是逻辑的概念,表示数据库使用的temp空间不能超过此上限.-整个系统的剩余空间都可以用作temp,TERADATA数据库系统数据库用户,在数据库系统中创建，与操作系统用户无关创建用户时可以定义permspace,也可以指定为0。Permspace为0的用户只能拥有视图和宏等对象，不能拥有表，除非具有访问其它数据库的权限。用户与数据库的唯一区别：用户有帐号，可以登录系统。因此用户有口令，而数据库没有。创建用户的同时，也创建了命名和用户一致的数据库。,TERADATA数据库系统-概览,TERADATA的数据存储,TERADATA使用hash算法，通过AMP将数据随意地或均匀地分布到存储设备上每个AMP负责表的记录行的一个子集。负载的均匀分布取决于数据的均匀分布。数据分布通过PI（PRIMARYINDEX）来实现。-每张表都必须有一个PI-如果建表时不指定PI，将自动以表中第一个不能为空的字段作为PI-PI字段不能修改-PI可以为一个字段或多个字段,目录,TARADATA介绍SQL语言介绍与语句优化,TERADATA操作语言SQL,根据各种SQL语句功能不同，可以将其分成以下三大类：（1）数据定义语言（DDL）：用来在系统中创建或者修改各种对象的结构，也可以用来删除系统中已有的对象，其命令集主要包括：CREATE：定于一个新的数据库、用户、数据库对象或索引DROP：删除一个已存在的数据库、用户、数据库对象或索引ALTER：修改一个表的结构和保护定义，启动或禁止触发器（2）数据操作语言（DML）：用来存取对象中的信心，因此，DML是SQL中使用最频繁的语言。其命令集主要包括：SELECT：执行关系查询INSERT：在表中增加新行UPDATE：修改表中已存在的行的值DELETE：删除表中已存在的行（3）数据控制语言（DCL）：用来控制用户存取数据库的权限或者方式，其命令集主要包括：GRANT：赋给用户权限REVOKE：去除用户权限GIVE：转让数据库所有权,经营分析系统中的重要操作连接操作,经营分析系统中几乎所有的SQL语句都会用到连接操作，并且是复杂的连接操作。连接语句的使用是否正确，不仅关系到SQL语句的性能，而且关系到SQL语句的结果的正确性连接操作主要分为5类：（1）INNERJOIN（2）LEFTOUTERJOIN（3）RIGHTOUTERJOIN（4）FULLOUTERJOIN（5）CROSSJOIN,第1类：连接操作内连接（INNERJOIN）,INNERJOIN：内连接，是最常见的一种连接，只连接匹配的行。select*fromtable1ast1innerjointable2ast2ont1.city_id=t2.city_idwheret1.city_id=“855等同于select*fromtable1ast1,table2ast2wheret1.city_id=t2.city_idandt1.city_id=“855,表：table1,表：table2,第2类：连接操作左连接（LEFTOUTERJOIN）,LEFTOUTERJOIN：左连接，结果集包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行select*fromtable1ast1leftouterjointable2ast2ont1.city_id=t2.city_id,表：table1,表：table2,第3类：连接操作右连接（RIGHTOUTERJOIN）,RIGHTOUTERJOIN：右连接，结果集包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行select*fromtable1ast1rightouterjointable2ast2ont1.city_id=t2.city_id,表：table1,表：table2,第4类：连接操作全外连接（FULLOUTERJOIN）,FULLOUTERJOIN：全外连接，结果集包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。select*fromtable1ast1fullouterjointable2ast2ont1.city_id=t2.city_id,表：table1,表：table2,第5类：连接操作交叉连接（CROSSJOIN）,CROSSJOIN：不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配，结果集是笛卡尔乘积。大多数交叉连接都是由于错误操作而造成的，但是它在造测试数据时非常有用。select*fromtable1ast1crossjointable2ast2等同于select*fromtable1ast1，table2ast2,表：table1,表：table2,数据库的连接操作查询条件的使用,在内连接中加入查询条件，无论是加到join子句，还是加到where子句，其效果完全一样外连接情况就不同了：当把条件加入到join子句时，数据库会返回外连接表的全部行，然后使用指定的条件返回第二个表的行当把条件放到where子句中，数据库首先进行连接操作，然后使用where子句对连接后的新表行进行数据筛选。,案例1：外连接时，把条件加入到join子句,例：select*fromtable1ast1leftouterjointable2ast2ont1.city_id=t2.city_idandt1.city_id=“855”结果：city_iduser_idcity_iduser_id852B855E855K859M,表：table1,表：table2,案例2：外连接时，把条件加入到where子句,例：select*fromtable1ast1leftouterjointable2ast2ont1.city_id=t2.city_idwheret1.city_id=“855”结果：city_iduser_idcity_iduser_id855E855K,表：table1,表：table2,SQL语句优化的通用方法索引,建立合理的索引,索引的设计要建立在对各种查询的分析和预测上，遵循以下原则:有大量重复值、且经常有范围查询，例如：(between,=,=0andcol1=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。第二个原则：where子句中字段的顺序应和索引中字段顺序一致。在下面的select语句中select*fromtabwherea=andb=andc=;若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。,SQL语句优化举例合理应用命令提高效率,以下假设在COL1上有唯一索引I1，在COL2上有非唯一索引I2。(1)前者效率高，因为后者在索引扫描后要多一步ROWID表访问，前提是查询结果属于索引字段，即I1索引建在（col1，col3，col4）上selectcol3,col4fromtbwherecol1=sdf快select