SQL优化：优化层次、途经、最佳实践

一、优化的层次：

1、语句级别（包含索引）

2、系统级别：索引碎片、锁定、统计信息、系统参数配置、数据库设计

3、底层级别：硬件、网络

 

二、SQL Server查询性能优化需要多种途径：

 1、数据库设计。这可能是影响查询性能和数据完整性最重要的因素了，设计决策会影响读取、修改的性能。

规范化的OLTP数据库关注数据库完整性、去除冗余、建立多个实体之间的关系，这是一个适合快速事务处理的设计。通常在规范化的OLTP设计中看到很多的表，这意味着在查询中要连接很多表。

另一方面，OLAP数据仓库设计经常使用非规范化的星形结构、雪花状结构，这些设计使用了中心事实表，它连接了2个或更多的描述维度表。对于雪花结构设计，维度表也可以存在于关联它的关系表上，这种设计的重点在于查询执行的速度而不是事务的快速更新。

2、适当的索引。

基于高优先级、频繁执行的查询来建立表索引。如果一个查询一天执行上千次且每次2秒完成，则可以通过适当的索引时每次运行时间少于1秒，增加这个索引可以显著减少SQL Server实例上的I/O压力。

应该根据需要建立索引，删除不用的索引。

表上的每个索引都会给数据修改操作增加开销，如果SQL Server使用低效率的索引甚至还会减慢查询的速度。在最初设计数据库的时候，索引最好少一些（当然，聚集索引、外键的非聚集索引还是要的）。在有需要的时候再增加索引，特别是在易变的、频繁更新的数据库中，索引需求是易变的，所以应该灵活、迭代的增加删除索引。

3、索引碎片。

由于长期的数据修改，索引会产生碎片。由于碎片的增多，数据会扩散到更多的数据页中。查询同样多数据需要检索的数据页也就会更多，IO需求就会更高，查询就会更慢。

4、系统参数配置。

包含了：数据库、SQL Server实例、操作系统配置。不适当的配置（比如启动了自动收缩、自动关闭数据库）会影响应用程序的性能。

 5、最新的统计信息。

AUTO_CREATE_STATISTICS数据库选项激活了SQL Server自动产生关于列中值的分布的统计信息。如果关闭此选项，统计信息就会过期。由于SQL Server依赖统计信息区决定如何执行查询，如果SQL Server基于过期的统计信息进行判断，那么可能会选择一个不太好的查询计划。

6、硬件。

当通过索引、修改语句的写法等都不能提高查询速度时，原因可能是结果集中的行太多、IO的限制，这时应该考虑把数据库迁移到更高配置的服务器上。对处理器体系结构、可用内存、磁盘系统的选择都会显著影响查询性能。

7、网络吞吐量。

获取查询结果所花的时间会受到缓慢的、不稳定的网络连接的影响，所以需要考虑这个潜在的影响因素。

 

 三、SQL Server查询性能最佳实践

1、在select查询中，只返回需要的列。查询中存在的列越少，则使用的IO和网络带宽就会越少。

 

2、在减少列的同时，也要考虑一下减少行。使用where子句来减少查询返回的行，不要让应用程序在只需显示前10行时，却返回10000行。

 

3、减少join次数。连接到单个查询中的每个表都会增加额外的开销。虽然具体join多少个表会收到数据库设计、容量大小以及用于关联查询的列的影响，但是短小查询有短小查询的好处。所以如果存储过程中有一个执行很长时间的非常大的查询，那么可以把这个查询分解为几个更小的中间结果，这通常会显著加快生成结果集的速度。

 

4、只在需要有序结果时，采用order by。大结果集的排序操作会导致额外开销。如果排序不是必须的那就不要排序。

 

5、避免在from、where、having子句中隐式数据类型的转换。当谓词中底层数据类型不匹配就会发生隐式数据类型转换，并且是由SQL Server自动转换的。比如，java应用程序发送unicode文本到非unicode列，对于每秒处理数百个事务的程序来说，隐式转换会增加处理时间。另外，隐式数据类型的转换可能会导致不能引用索引，导致查询性能下降。

 

6、如果唯一行不是必须的，那么就不要用distinct、union（而是用union all）。

 

7、当在游标和基本集合的方法之间进行选择时，最好选择集合方法。如果必须用游标，那么使用完后一定要尽快关闭和释放。

 

8、存储过程可以提升查询执行的稳定性（重用既有的查询计划），所以可以把一个查询封装到存储过程中，这样可能会提升性能。

 

9、应该避免使用嵌套视图。如果在创建视图时引用了其他视图，而这个创建的视图所引用的对象，在被调用的视图中已经被引用过了，那么由于查询的复杂性，这个重复和重叠经常会造成未优化的查询计划。

 

10、在复杂的数据库驱动的应用程序中，在某些情况下使用查询提示是必要的。不过，当底层的数据量、分布情况发生变化时，查询提示可能会覆盖SQL Server的决策处理，误导SQL Server做出最佳计划。

 

11、在开发数据库中，将有代表性的数据集填充到表中是非常重要的。也就是说应该把与实际生产环境中相当数量的有代表性的行填充到表中，不要在开发数据库内使用没有代表性的数据。SQL Server的性能非常依赖索引和统计信息，并且会基于表内实际的值做出决策。如果测试数据不是有代表性的较真实的数据，那么生产环境中的查询效果与测试中的效果会差异很大。

转载于:https://www.cnblogs.com/momogua/p/8304332.html