《TERADATA基础理论及SQL优化》由会员分享，可在线阅读，更多相关《TERADATA基础理论及SQL优化（29页珍藏版）》请在金锄头文库上搜索。
1、TERADATATERADATA基础理论及基础理论及SQLSQL优化优化目录目录 TARADATA介绍介绍SQL语言介绍与语句优化TERADATA发展简介发展简介1976年TERADATA公司成立，瞄准联机分析处理市场，公司目标为设计能够处理大数据量的数据库计算机。1984年推出世界上第一个基于海量并行处理技术的商用系统。硬件平台DBC（DataBaseComputer），操作系统为TOS（TERADATAOS)。1989年TERADATA公司和NCR公司合作，一起研制下一代数据库计算机。1992年，TERADATA与ncr合并，开始将封闭数据库计算机系统移植到开放平台。1996年NCR推出u
2、nix平台的TERADATA关系数据库系统。1998年推出WINDOWSNT平台下的TERADATA关系数据库系统。2007年TERADATA从NCR拆分,独立在纽约上市TERADATA数据库简介数据库简介TERADATA是关系型数据库管理系统（RDBMS）为运行世界上最大的商业数据库而设计企业数据仓库的首选解决方案基于UNIX与NT的开放式系统平台完全遵从于ANSI工业标准，采用标准的SQL查询语言可以运行单节点(SMP)或多节点(MPP)可以作为一个贯穿整个企业的客户端应用的数据库服务器运用并行处理方式管理Terabytes字节以上的数据TERADATA数据库的框架结构数据库的框架结构TE
3、RADATA系统主要包括三个部分：处理节点（Node）用于节点间通信的内部高速互联（InterConnection）数据存储介质（通常是磁盘阵列）每个节点都是SMP结构的单机，多个节点一起构成一个MPP系统，多个节点之间的内部高速互联是通过一种被称为BYNET的硬件来实现的，整个系统的组成如下图所示。TERADATA节点结构节点结构(Node)在软件结构上，每个节点自下向上包括操作系统软件（OS）、TERADATA并行数据库扩展（PDE）和相关应用程序，其中PDE的主要职责是管理和运行虚拟处理器，其中主要包括PE和AMPs。（1）PDE:并行数据库扩展（2）VPROC:虚拟处理器（3）PE:解
4、析引擎,将接收到的SQL语句进行分解并优化。（4）AMP:存取模块处理器,完成数据的存取操作，是实现并行机制的关键。（5）VDisk:虚拟磁盘TERADATA MPP系统系统在TeradataMPP系统中，各个节点间（确切地说是各个AMP之间）的内部高速互联是通过BYNET实现的，它就是Teradata系统中那些松散耦合的节点之间互相联系的通讯总线，但事实上，它却远远没有这么简单。BYNET是一组硬件和运行在这组硬件上的一些处理通讯任务的软件进程的组合体，用于：（1）节点之间的双向广播（bidirectionalbroadcast）（2）多路传递（multicast）（3）点对点通信（poin
5、t-to-pointcommunication）（4）实现SQL查询过程中的合并功能TERADATA数据库系统数据库系统数据库空间数据库空间Permspace-数据库中用来存储真正数据的空间，也称为永久空间。-只是逻辑的概念,表示数据库可使用的存储空间的上限.不预先分配.-可以动态修改Spoolspace-用于存储处理交易请求的中间结果.交易结束,空间释放.-也是逻辑的概念,表示数据库使用的spool空间不能超过此上限.-整个系统的剩余空间都可以用作spoolTempspace-用于存储临时表,session结束,空间释放-也是逻辑的概念,表示数据库使用的temp空间不能超过此上限.-整个系统
6、的剩余空间都可以用作tempTERADATA数据库系统数据库系统数据库用户数据库用户在数据库系统中创建，与操作系统用户无关创建用户时可以定义permspace,也可以指定为0。Permspace为0的用户只能拥有视图和宏等对象，不能拥有表，除非具有访问其它数据库的权限。用户与数据库的唯一区别：用户有帐号，可以登录系统。因此用户有口令，而数据库没有。创建用户的同时，也创建了命名和用户一致的数据库。TERADATA数据库系统数据库系统-概览概览TERADATA的数据存储的数据存储TERADATA使用hash算法，通过AMP将数据随意地或均匀地分布到存储设备上每个AMP负责表的记录行的一个子集。负载
7、的均匀分布取决于数据的均匀分布。数据分布通过PI（PRIMARYINDEX）来实现。-每张表都必须有一个PI-如果建表时不指定PI，将自动以表中第一个不能为空的字段作为PI-PI字段不能修改-PI可以为一个字段或多个字段目录目录 TARADATA介绍 SQL语言介绍与语句优化语言介绍与语句优化TERADATA操作语言操作语言SQL根据各种SQL语句功能不同，可以将其分成以下三大类：（1）数据定义语言（）数据定义语言（DDL）：）：用来在系统中创建或者修改各种对象的结构，也可以用来删除系统中已有的对象，其命令集主要包括：CREATE：定于一个新的数据库、用户、数据库对象或索引DROP：删除一个已
8、存在的数据库、用户、数据库对象或索引ALTER：修改一个表的结构和保护定义，启动或禁止触发器（2）数据操作语言（）数据操作语言（DML）：）：用来存取对象中的信心，因此，DML是SQL中使用最频繁的语言。其命令集主要包括：SELECT：执行关系查询INSERT：在表中增加新行UPDATE：修改表中已存在的行的值DELETE：删除表中已存在的行（3）数据控制语言（）数据控制语言（DCL）：）：用来控制用户存取数据库的权限或者方式，其命令集主要包括：GRANT：赋给用户权限REVOKE：去除用户权限GIVE：转让数据库所有权经营分析系统中的重要操作经营分析系统中的重要操作连接操作连接操作经营分析系
9、统中几乎所有的SQL语句都会用到连接操作，并且是复杂的连接操作。连接语句的使用是否正确，不仅关系到SQL语句的性能，而且关系到SQL语句的结果的正确性连接操作主要分为5类：（1）INNERJOIN（2）LEFTOUTERJOIN（3）RIGHTOUTERJOIN（4）FULLOUTERJOIN（5）CROSSJOIN第第1类：连接操作类：连接操作内连接（内连接（ INNER JOIN ）INNERJOIN：内连接，是最常见的一种连接，只连接匹配的行。select*fromtable1ast1innerjointable2ast2ont1.city_id=t2.city_idwheret1.ci
10、ty_id=“855等同于select*fromtable1ast1,table2ast2wheret1.city_id=t2.city_idandt1.city_id=“855City_idUser_id852B855E859MCity_idUser_id851A853C855K表：table1表：table2第第2类：连接操作类：连接操作左连接（左连接（ LEFT OUTER JOIN）LEFTOUTERJOIN：左连接，结果集包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行select*fromtable1ast1leftouterjointable2
11、ast2ont1.city_id=t2.city_idCity_idUser_id852B855E859MCity_idUser_id851A853C855K表：table1表：table2第第3类：连接操作类：连接操作右连接（右连接（ RIGHT OUTER JOIN）RIGHTOUTERJOIN：右连接，结果集包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行select*fromtable1ast1rightouterjointable2ast2ont1.city_id=t2.city_idCity_idUser_id852B855E859MCity_i
12、dUser_id851A853C855K表：table1表：table2第第4类：连接操作类：连接操作全外连接（全外连接（ FULL OUTER JOIN）FULLOUTERJOIN：全外连接，结果集包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。select*fromtable1ast1fullouterjointable2ast2ont1.city_id=t2.city_idCity_idUser_id852B855E859MCity_idUser_id851A853C855K表：table1表：table2第第5类：连接操作类：连接操作交叉连接（交叉连接（ CROSS
13、 JOIN）CROSSJOIN：不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配，结果集是笛卡尔乘积。大多数交叉连接都是由于错误操作而造成的，但是它在造测试数据时非常有用。select*fromtable1ast1crossjointable2ast2等同于select*fromtable1ast1，table2ast2City_idUser_id852B855E859MCity_idUser_id851A853C855K表：table1表：table2数据库的连接操作数据库的连接操作查询条件的使用查询条件的使用 在内连接中加入查询条件，无论是加到jo
14、in子句，还是加到where子句，其效果完全一样外连接情况就不同了：当把条件加入到join子句时，数据库会返回外连接表的全部行，然后使用指定的条件返回第二个表的行当把条件放到where子句中，数据库首先进行连接操作，然后使用where子句对连接后的新表行进行数据筛选。案例案例1：外连接时，把条件加入到：外连接时，把条件加入到join子句子句例：select*fromtable1ast1leftouterjointable2ast2ont1.city_id=t2.city_idandt1.city_id=“855”结果：city_iduser_idcity_iduser_id852B855E85
15、5K859MCity_idUser_id852B855E859MCity_idUser_id851A853C855K表：table1表：table2案例案例2：外连接时，把条件加入到：外连接时，把条件加入到where子句子句例：select*fromtable1ast1leftouterjointable2ast2ont1.city_id=t2.city_idwheret1.city_id=“855”结果：city_iduser_idcity_iduser_id855E855KCity_idUser_id852B855E859MCity_idUser_id851A853C855K表：table
16、1表：table2SQL语句优化的通用方法语句优化的通用方法索引索引建立合理的索引,索引的设计要建立在对各种查询的分析和预测上，遵循以下原则:有大量重复值、且经常有范围查询，例如：(between,=,=）和orderby,groupby发生的列，可考虑建立群集索引经常同时存取多列，且每列都含有重复值可考虑建立组合索引；组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。有索引的尽量用索引，用到索引的条件写在前面SQL语句优化的通用方法语句优化的通用方法规避全表查询规避全表查询字段提取要按照“需多少、提多少”的原则，查询时不要返回不需要的行、列，避免使用“select*”进行全
17、表查询Where条件中任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边尽量避免LIKE、IN、OR、NOT等引起全表扫描的操作符避免相关子查询，如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行避免或简化排序变量名称尽量一致SQL语句优化的通用方法语句优化的通用方法合理使用命令提高效率合理使用命令提高效率注意union和unionall的区别，unionall效率更高在没有必要时不要使用distinct和orderby，它同union一样会使查询变慢between在某些时候比in速度更快,between能够更快地根据索引找到范围用or的字句可
18、以分解成多个查询，并且通过union连接多个查询。他们的速度只同是否使用索引有关尽量少用视图，它的效率低。对视图操作比直接对表操作慢在in后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数合理使用外联接,外连接比内连接效率更高多条语句一起commit，比单条能提高效率SQL语句优化举例语句优化举例WHERE的二个原则的二个原则第一个原则：在where子句中应把最具限制性的条件放在最前面。在下面两条select语句中select*fromtable1wherecol1=0;select*fromtable1wherecol1=0andcol1=0,则第一条sele
19、ct语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。第二个原则：where子句中字段的顺序应和索引中字段顺序一致。在下面的select语句中select*fromtabwherea=andb=andc=;若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。SQL语句优化举例语句优化举例合理应用命令提高效率合理应用命令提高效率以下假设在COL1上有唯一索引I1，在COL2上有非唯一索引I2。(1)前者效率高，因为后者在索引扫描后要多一步ROWID表访问，前提是查询结果属于索引字段，即I1索引建在（col1，c
20、ol3，col4）上selectcol3,col4fromtbwherecol1=sdf 快select*fromtbwherecol1=sdf慢，(2)前者效率高，因为前者可以迅速定位索引。selectcol3,col4fromtbwherecol1=sdf快selectcol3,col4fromtbwherecol1sdf 慢(3)前者效率高，因为后者不使用索引。selectcol3,col4fromtbwherecol2likeR%快selectcol3,col4fromtbwherecol2like%R慢，成成 就就 客客 户户 走走 向向 卓卓 越越8/3/2024亚信科技(中国)有限公司结束结束
