大型数据库技术－sql查询优化

第9讲sql查询优化数据库技术

--SQLServer主讲人占跃华信息工程学院教学任务发布任务背景描述：在“Exam”数据库中我们已经能够根据用户的需求，从单个表或多个表中查询出数据了。但在SqlServer内部，这些查询语句到底是怎样执行的？如果查询效率低下，有没有办法进行优化？工作任务之：了解SqlServer查询计划了解常用的查询优化技术了解SqlServer查询计划对于SqlServer的优化来说，优化查询可能是很常见的事情。1.看看SqlServer查询计划从这个图中，我们至少可以得到3个有用的信息：哪些执行步骤花费的成本比较高。显然，最右边的二个步骤的成本是比较高的。哪些执行步骤产生的数据量比较多。对于每个步骤所产生的数据量，SQLServer的执行计划是用【线条粗细】来表示的，因此也很容易地分辨出来。每一步执行了什么样的动作。了解SqlServer查询计划对于SqlServer的优化来说，优化查询可能是很常见的事情。2.对于一个比较慢的查询来说，我们通常要知道哪些步骤的成本比较高，进而，可以尝试一些改进的方法。为【scan】即扫描这类操作增加相应字段的索引有时重建索引或许也是有效的调整语句结构，引导SQLServer采用其它的查询方案去执行调整表结构（分表或者分区）了解SqlServer查询计划3.

SQLServer会有以下方法来查找您需要的数据记录：

【TableScan】：遍历整个表，查找所有匹配的记录行。这个操作将会一行一行的检查，当然，效率也是最差的。【IndexScan】：根据索引，从表中过滤出来一部分记录，再查找所有匹配的记录行，显然比第一种方式的查找范围要小，因此比【TableScan】要快。【IndexSeek】：根据索引，定位（获取）记录的存放位置，然后取得记录，因此，比起前二种方式会更快。【ClusteredIndexScan】：和【TableScan】一样。注意：不要以为这里有个Index，就认为不一样了。其实它的意思是说：按聚集索引来逐行扫描每一行记录，因为记录就是按聚集索引来顺序存放的。而【TableScan】只是说：要扫描的表没有聚集索引而已，因此这二个操作本质上也是一样的。【ClusteredIndexSeek】：直接根据聚集索引获取记录，最快！了解SqlServer查询计划4.我们可以做的：可以首先检查哪些操作的成本比较高，再看看那些操作在查找记录时，是不是【TableScan】或者【ClusteredIndexScan】，如果确实和这二种操作类型有关，则要考虑增加索引来解决了。不过，增加索引后，也会影响数据表的修改动作，因为修改数据表时，要更新相应字段的索引。所以索引过多，也会影响性能。还有一种情况是不适合增加索引的：某个字段用0或1表示的状态。例如可能有绝大多数是1，那么此时加索引根本就没有意义。这时只能考虑为0或者1这二种情况分开来保存了，分表或者分区都是不错的选择。如果不能通过增加索引和调整表来解决，那么可以试试调整语句结构，引导SQLServer采用其它的查询方案去执行。这种方法要求：1.对语句所要完成的功能很清楚，2.对要查询的数据表结构很清楚，3.对相关的业务背景知识很清楚。如果能通过这种方法去解决，当然也是很好的解决方法了。不过，有时SQLServer比较智能，即使你调整语句结构，也不会影响它的执行计划。了解SqlServer查询计划5.SQLServerJoin方式：在SQLServer中，常见的表之间的InnerJoin,OuterJoin都会被执行引擎根据所选的列，数据上是否有索引，所选数据等选择性转化为LoopJoin,MergeJoin,HashJoin这三种物理连接中的一种。【NestedLoopsjoin】即嵌套循环连接，如果连接的外部循环表小，而内部循环表很大且已在其连接列上创建了索引，则索引NestedLoops连接是最快的连接操作。了解SqlServer查询计划5.SQLServerJoin方式：在SQLServer中，常见的表之间的InnerJoin,OuterJoin都会被执行引擎根据所选的列，数据上是否有索引，所选数据等选择性转化为LoopJoin,MergeJoin,HashJoin这三种物理连接中的一种。【MergeJoin】即合并连接，如果两个连接输入表不小且已在二者连接列上排序（例如，如果它们是通过扫描已排序的索引获得的），则合并连接是最快的连接操作。了解SqlServer查询计划5.SQLServerJoin方式：在SQLServer中，常见的表之间的InnerJoin,OuterJoin都会被执行引擎根据所选的列，数据上是否有索引，所选数据等选择性转化为LoopJoin,MergeJoin,HashJoin这三种物理连接中的一种。【HashJoin】，哈希匹配连接相对前面两种方式更加复杂一些，但是哈希匹配对于大量数据，并且无序的情况下性能均好于MergeJoin和LoopJoin。对于连接列没有排序的情况下(也就是没有索引)，查询分析器会倾向于使用HashJoin。嵌套循环连接合并连接哈希连接外层循环小，内存循环条件列有序输入两端都有序数据量大，且没有索引sql查询优化1.数据库设计阶段的优化逻辑设计的规范化：关系模式全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。合理的数据冗余：完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。sql查询优化1.数据库设计阶段的优化主键的设计：主键是必要的，SQLSERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的。在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。sql查询优化1.数据库设计阶段的优化外键的设计：外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、Check约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。谨慎使用级联删除和级联更新，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。sql查询优化1.数据库设计阶段的优化字段的设计：字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：数据类型尽量用数字型，数字型的比较比字符型的快很多。数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。尽量不要允许Null，除非必要，可以用NotNull+Default代替。少用Text和Image，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。自增字段要慎用，不利于数据迁移。sql查询优化1.数据库设计阶段的优化索引的设计：在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。关于索引的选择，应该主意：根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。一个表不要加太多索引，因为索引影响插入和更新的速度。sql查询优化2.查询时只返回需要的数据返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：横向来看，不要写Select*的语句，而是选择你需要的字段纵向来看，合理写Where子句，不要写没有Where的SQL语句不要写一些没有意义的查询，比如Select*FromEmpWhere1=2sql查询优化3.设计Select语句查询时尽量做到注意SelectInto后的Where子句，因为SelectInto把数据插入到临时表，这个过程会锁定一些系统表，如果这个Where子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程对于聚合查询，可以用Having子句进一步限定返回的行用Where子句替换Having子句Count(*)比Count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如Count(TestNo)使用表的别名(Alias),当在Sql语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。sql查询优化4.注意连接条件的写法多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件的时候需要特别的注意。多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。连接条件尽量使用聚集索引注意On部分条件和Where部分条件的区别,两表连接条件放在join-on后面，各表自己的过滤条件放在where后面，尊重语义，易读易维护sql查询优化5.子查询的优化子查询是一个Select查询，它嵌套在Select、Insert、Update、Delete语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用In、NotIn、Exists、NotExists引入。关于相关子查询，应该注意：用Exists、NotExists替代In、NotIn用表连接替换Exists大多数的情形，用Exists的写法，性能都比Except/Intersect要好。（数据量足够大才有比较）用Exists替换Distinctsql查询优化用表连接替换Exists

通常来说，采用表连接的方式比Exists更有效率。但也要视情况而定：如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用In,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用Exists。其实我们区分In和Exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是Exists，那么以外层表为驱动表，先被访问，如果是In，那么先执行子查询也就是里层表为驱动表，所以我们会以驱动表的快速返回为目标。sql查询优化用Exists替换Distinct

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在Select子句中使用Distinct。一般可以考虑用Exists替换。大多数的情形，用Exists的写法，性能都比Except/Intersect要好。（数据量足够大才有比较）虽然如此，但我们不该完全舍弃Except/Intersect的使用，因为他们的语法简洁、容易阅读。因此在您的SQL语句中，除非两种写法的性能差距很大，不然我仍建议使用Except/Intersect写法，以便项目后续的维护。若论Exists和Except/Intersect哪一种写法较好，则属见仁见智的问题，端视您的数据量、执行环境、实测两种写法的效率差距，来决定要用哪一种写法。如何测试sql查询效率可以用以下方法对您设计的SQL语句执行效率进行测试，选择最优方案：1、通过设置Statistics查看执行SQL时的系统情况。选项有Profile，Io，Time。示例如下：--显示分析、编译和执行查询所需的时间（以毫秒为单位）。SetStatisticsProfileOn--报告与语句内引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）有关的信息。SetStatisticsIoOn--显示每个查询执行后的结果集，代表查询执行的配置文件。SetStatisticsTimeOn如何测试sql查询效率如：--测试SQL脚本开始go/*selectTestName,a.TestNofromTestUsera,ExamScorebwherea.TestNo=b.TestNoandb.Subjec