性能实战使用手册总结

性能实战使用手册总结天善问答，由天善讲师团、智囊团长期驻扎，一站式解决你在工作和学习中遇到各类BI技术问题,网址：性能实战使用手册总结1 SQL Joins方式1.1 Join几种形式 2 性能实战记录2.1 笛卡尔积执行顺序 (提高性能)2.1.1 SQL执行顺序SQL 不同于与其他编程语言的最明显特征是处理代码的顺序。在大数编程语言中，代码按编码顺序被处理，但是在SQL语言中，第一个被处理的子句是FROM子句，尽管SELECT语句第一个出现，但是几乎总是最后被处理。 每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回 给调用者。如果没有在查询中指定某一子句，将跳过相应的步骤。下面是对应用于SQL server 2000和SQL Server 2005的各个逻辑步骤的简单描述。(8)SELECT (9)DISTINCT (11) (1)FROM left_table(3) JOIN (2) ON (4)WHERE (5)GROUP BY (6)WITH (7)HAVING (10)ORDER BY 逻辑查询处理阶段简介1.FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT12.ON：对VT1应用ON筛选器。只有那些使为真的行才被插入VT2。3.OUTER(JOIN)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。4.WHERE：对VT3应用WHERE筛选器。只有使为true的行才被插入VT4.5.GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.6.CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.7.HAVING：对VT6应用HAVING筛选器。只有使为true的组才会被插入VT7.8.SELECT：处理SELECT列表，产生VT8.9.DISTINCT：将重复的行从VT8中移除，产生VT9.10.ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).11.TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。注：步骤10，按ORDER BY子句中的列列表排序上步返回的行，返回游标VC10.这一步是第一步也是唯一一步可以使用SELECT列表中的列别名的步骤。这一步不同于其它步骤的 是，它不返回有效的表，而是返回一个游标。SQL是基于集合理论的。集合不会预先对它的行排序，它只是成员的逻辑集合，成员的顺序无关紧要。对表进行排序 的查询可以返回一个对象，包含按特定物理顺序组织的行。ANSI把这种对象称为游标。理解这一步是正确理解SQL的基础。因为这一步不返回表（而是返回游标），使用了ORDER BY子句的查询不能用作表表达式。表表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。它的结果必须返回给期望得到物理记录的客户端应用程序。例如，下面的派生表查询无效，并产生一个错误：2.1.2 实战SQL执行顺序讲解功能说明：从以上两个查询分析 结合到 SQL执行计划分析可以查询1：首先做笛卡尔积，并没有进行筛选的。而在 WHERE后面再选。查询2：两个表做笛卡尔积后筛选得出虚拟表，然后用虚拟表和下一个表做笛卡尔积，一次类推。所以，通过查询一在虚拟表的数据量上明显多于查询二所输出的数据量的，所以推荐使用查询二的方法。2.2 连接 替换子查询的方式 (提高性能)2.2.1 两种查询结果比对机器环境: 84 用户名：rdcuser 密码:baisonrdc 数据库:JLW 数据量：40379查询一： 使用子查询的方式需要 3分17秒SELECT a.DJBH, a.RQ, a.LXDJ, a.XZDM, a.QDDM, a.KHDM, a.SPDM, a.JE4, a.JE5, a.JE6, a.JE7, a.JE8, a.ZK FROM dbo.vw_QDTJMX a WHERE a.RQ = (SELECT Max(b.RQ) FROM dbo.vw_QDTJMX b WHERE Substring(a.LXDJ, 1, 5) = Substring(LXDJ, 1, 5) AND a.xzdm = xzdm AND a.qddm = qddm AND a.khdm = khdm AND a.spdm = spdm) 查询二： 使用连接的形式 33秒SELECT a.DJBH, a.RQ, a.LXDJ, a.XZDM, a.QDDM, a.KHDM, a.SPDM, a.JE4, a.JE5, a.JE6, a.JE7, a.JE8, a.ZKFROM dbo.vw_QDTJMX a INNER JOIN (SELECT Max(b.RQ) RQ,Substring(b.LXDJ, 1, 5) AS LXDJ,xzdm,qddm,khdm,SPDM FROM dbo.vw_QDTJMX b GROUP BY Substring(b.LXDJ, 1, 5), b.xzdm, b.qddm, b.khdm, b.spdm)C ON A.RQ = C.RQ AND Substring(a.LXDJ, 1, 5) = C.LXDJ AND a.xzdm = C.xzdm AND a.qddm = C.qddm AND a.khdm = C.khdm AND a.spdm = C.spdm功能说明：通过对比查询一和查询二，我们很容易分辨出两者的不同点-子查询是放在where子句中，还是与主表连接，其实在大多数情况下，我们是推荐与主表的连接（特殊情况我们先暂时不谈），为什么我们推荐要和主表连接呢？这就涉及到我们本文开始部分所讨论的T-SQL的执行顺序问题，我们不妨在这里分析一下:查询一，是所有的表关联后，所产生一个虚拟表（这里虽一个表，但是这里我们分析的时候要从多个表去分析，更方便理解），我们暂且把这个虚拟表称为虚表一，然后T-SQL要做的是执行这个子查询，接着产生（叫输出更合适）虚拟表二，紧接着，查询机制要做的是将虚拟表中的字段RQ与虚表二中的字段进行查找比对，如果没有索引的话，这个是非常浪费开销的。查询二，将主表连接完，这块是和查询一是相同的，然后再执行子查询，产生虚表二，紧接着，查询分析器，估计子查询的数据量，采用不同的链接函数，小数据量使用nested loops join ,merge join 和hash join ，然后产生虚表二，虽然很多人认为他们在执行顺序上不同而已，其实内在的他们所产生的虚拟表的数据量有本质的区别的。所以推荐大家，尽量使用主表进行关联（不是绝对的，特殊情况特殊对待）。2.2.2 执行计划开销占比及分析功能说明： 实现同一查询功能的SQL写法可能会有多种，如果判断哪种最优化，如果仅仅是从时间上来测，会受很多外界因素的影响，而我们明白了MSSQL如何去执行，通过IO逻辑读、通过查看图示的查询计划、通过其优化后而执行的SQL语句，才是优化SQL的真正途径。 上图就是实现 同一SQL的两种写法，使用执行计划可以看到 第一个查询开销占 95%,第二个查询开销 占 5%.说明 第二查询性能高。2.2.3 比对两个查询结果一致性SELECT a.DJBH, a.RQ, a.LXDJ, a.XZDM, a.QDDM, a.KHDM, a.SPDM, a.JE4, a.JE5, a.JE6, a.JE7, a.JE8, a.ZK FROM dbo.vw_QDTJMX a WHERE a.RQ = (SELECT Max(b.RQ) FROM dbo.vw_QDTJMX b WHERE Substring(a.LXDJ, 1, 5) = Substring(LXDJ, 1, 5) AND a.xzdm = xzdm AND a.qddm = qddm AND a.khdm = khdm AND a.spdm = spdm)EXCEPTSELECT a.DJBH, a.RQ, a.LXDJ, a.XZDM, a.QDDM, a.KHDM, a.SPDM, a.JE4, a.JE5, a.JE6, a.JE7, a.JE8, a.ZKFROM dbo.vw_QDTJMX a INNER JOIN (SELECT Max(b.RQ) RQ,Substring(b.LXDJ, 1, 5) AS LXDJ,xzdm,qddm,khdm,SPDM FROM dbo.vw_QDTJMX b GROUP BY Substring(b.LXDJ, 1, 5), b.xzdm, b.qddm, b.khdm, b.spdm)C ON A.RQ = C.RQ AND Substring(a.LXDJ, 1, 5) = C.LXDJ AND a.xzdm = C.xzdm AND a.qddm = C.qddm AND a.khdm = C.khdm AND a.spdm = C.spdm功能说明： EXCEPT 从左查询中返回右查询没有找到的所有非重复值。 如果两个查询结果一致，就不会返回任何结果，就说明这个两个查询所产生的结果是一致的，这是05新增的集合操作之一，2000的是不适合的，如果涉及到版本的迁移，请慎用。2.3 存储过程加索引优化2.3.1 临时表加索引和不加索引性能比对机器环境: ROBAY-PCSQL2008 用户名：sa 密码:123 数据库:bserp 数据量：4000查询一：15秒UPDATE #tmpYueJie1SET QMJE = ( QCSL * QCJE + SRJE + TZJE + FYJE ) / ( QCSL + SRSL + TZSL )WHERE SPDM = SPDM AND ( QCSL + SRSL + TZSL ) 0 查询二： 不到 1秒 。在SPDM加了个聚集索引CREATE CLUSTERED INDEX INDEX_spdm_test ON #tmpYueJie1(SPDM)UPDATE #tmpYueJie1SET QMJE = ( QCSL * QCJE + SRJE + TZJE + FYJE ) / ( QCSL + SRSL + TZSL )WHERE SPDM = SPDM AND ( QCSL + SRSL + TZSL ) 0 2.3.2 临时表和普通表索引比对查询一： 5分53秒EXEC p_YueJieDataOfChengBen_New 2012-12,0,2,1,1,1,000,2013-01-16,0,0,0,0,0,0,0,0,0,0查询一： 3分 14秒EXEC p_YueJieDataOfChengBen_New 2012-12,0,2,1,1,1,000,2013-01-16,0,0,0,0,0,0,0,0,0,0功能说明： 直接在实表数据读写操作，比临时表快。补充：在dbms中存在表，临时表，和表变量这三种存储数据的结构，表我们用的很多了，但是临时表和表变量我们用的不是很多，所以接下来我们来分析分析临时表和表变量。微软认为与本地临时表相比，表变量具有如下优点 ：a. 与其他变量的定义一样，表变量具有良好的定义范围，并会被自动清除； b.在存储过程中使用表变量会减少存储过程重新编译的发生； c.表变量需要更少的锁请求和日志资源； d.可以在表变量上使用UDF,UDDT,XML。表变量和临时表相比较的话，会有很多限制的，确切的来说的话，临时表使用的范围会更广点，通过微软的官方发布，对表变量有如下限制： a.在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据很多的情况下，会导致查询优化器选择很差的执行计划； b.不能直接在表变量上创建索引，但可以通过创建约束（主键、唯一）来建立索引； c.在DECLARE后，不能再对表变量进行更改； d.不能对表变量执行INSERT EXEC , SELECT INTO语句（只针对05前的版本）； e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句，但如果表变量是在动态SQL语句内定义的，则可以。 对表变量的限制之后，我们得要了解我们什么时候使用使用它才是最合适的，才能最大程度的上发挥表变量的功能，通过我们对表变量的限制我们可以看出我们在用的时候得要了解我们的数据量是多大，一般情况下，数据量少的情况下我们推荐使用表变量，多的话，使用临时表；我们还得关心查询的类型和对索引或者统计信息的依赖程度 ，依赖程度高的话，我们没必要用表