论文部分内容阅读
摘要：在SQL SERVER环境下对查询符号、联合查询、多条件，联接运算等几个常见的SQL优化问题进行了分析研究，指出了由于优化器的参与，纯理论的优化建议对SQL用户的实践可能产生的误导。
关键词：SQL；优化；SQL SERVER；数据库
中图分类号：TP311文献标识码：A 文章编号：1009-3044(2008)15-21002-02
The Method of Optimization in SQL Server
WU Xian-feng
(Sichuan Institute of Administration,Chengdu 610004,China)
Abstract:This paper research some questions of SQL performance optimization with SQL SERVER such as operator, UNION operation, Multi-search condition, JION operation. Considers that, because of Models of optimizing in database, some suggestion of optimization base on pure theory would misadvise user of SQL.
Key words: SQL; optimization; SQL SERVER; database
1 引言
SQL查询的优化是数据库应用领域的一个重要问题，关于这个问题的讨论很多。但遗憾的是很多讨论更多的从SQL语句的语法和构造的层面来进行说明，脱离具体的数据库环境。事实上，优化问题是一个实践性很强的问题，数据库在执行语句的时候，并非完全按SQL语句中规定的逻辑和层次来执行，数据库会对SQL语句进行解析后，按特定的方式来执行。不同的数据库处理同样的SQL语句也会有区别。
这里我们结合SQL SERVER对比较常见的几种优化建议进行探讨。
2 查询符号的优化
在条件表达式中，查询符号的使用可能影响到索引能否被使用，从而影响查询的效率，一般遵循这样一些原则：不要使用表达式，尽量避免负逻辑，避免语句出现LIKE“%”等，此外，研究者一般认为，使用or连接条件时，会被强制使用全表扫描，也有人认为exist的效率远远低于in 的效率。
我们选用一个有10万行的表在SQL SERVER环境下进行测试，通过SQL SERVER提供的查询计划图形显示，我们很容易得到查询是按何种方式展开的。结果证明在where子句中，如果在字段上使用了代数表达式或函数时，使用LIKE“%”，查询方式是全表扫描，但是在负逻辑，or连接条件等情况下，却并不一定采用全表扫描的方式。
仔细分析其过程，可以看出，SQL SERVER优化器首先预测结果行的数量，如果结果行数量较多时，则无论是负逻辑还是正逻辑，均采用全表扫描。如果结果行较少，我们可称其为具有强选择性，则优化器会自动将负逻辑转化为等价的正逻辑表达，然后在索引上扫描。
同样，在分析or连接时，也可以看到，执行计划同样有可能在索引上扫描，是全表扫描还是扫描索引，不是取决于使用and或是or连接，而是取决于对结果行数量的预测。即使是在or的两端是不同的字段时，如：discount=1 or ordered=10000 ，只要存在相关的索引，查询时均可使用这些索引。
与此相关的另一个问题是，许多人认为or符号连接条件将被强制全表扫描，所以建议用UNION来替代OR符号，以提高效率，事实上这也是得不偿失的。因为，一般情况下，在UNION中可以用到的索引在or符号表达时同样可以用到。在少数情况下，确实存在使用or符号时不能利用索引，而在用UNION表达时可以利用索引的情况。但由于UNION操作中多了合并插入、去除重复行等工作，所以总体效率还是会低于在单一的SELECT语句中用OR符号。
3 联合查询时的优化
在联合（union）运算时，一般认为需要考虑的问题是，在SQL语句中各个SELECT语句的顺序问题。主张将结果行相对较少的行放在靠后，这样可以减少插入操作。
但在SQL SERVER中，我们针对这一问题设计含两个SELECT子句的UNION语句，使其两个SELECT语句中返回的行数又较大的差别，可以看到这样的结果：
(1)如果两个SELECT语句中，如果小结果语句具有强选择性，因而利用了索引扫描。这种情况下，大结果放在前面效率较高。
(2)如果两个SELECT语句中均不具有强选择性，但该表中有聚集索引时，SQL SERVER对两部分结果的合并采用Merge Union运算。
我们以一个具有1000个返回行的结果集为小结果，并将与其组合的大结果集的行数做多种变化，得到以下一组数据（见表1）：
这里的索引是非聚集索引，如果其中的条件覆盖聚集索引时情况稍有不同。由此我们可以看出，多条件时，条件的执行顺序会被优化器作恰当的安排，由于存在索引的因素，系统的优化也不是简单的将强条件先行执行。而开发人员在构造SQL语句的时候，则没有必要去考虑where子句中条件的顺序问题。
5 联接运算的优化
联接运算优化中的一个问题是：当一个SQL中既有联接运算，同时也存在的选择运算时，应当采用先选择运算缩小联接运算的范围，避免联接运算产生较大的临时表。所以有人举出以下典型的例子：
方法一：SELECT * FROM products ,address WHERE products.productid = address. Productid and date = {^2005 - 1 - 1}
方法二：SELECT * FROM address WHERE productid in (SELECT productid FROM products WHERE date= {^2005 - 1 - 1})
事实上这两种方法的结果集并非完全等价，因为在in操作符在处理嵌套语句的结果集时，会自动剔出重复的值。因此，它的结果等同于address和products之间为一对多的关系时的联接结果。换而言之，如果address和products之间是多对多的关系时，两种方法的结果是不一致的，第一种方法的结果集将大于第二种方法。所以，严格而言，无论第二种方法的性能如何，都不能算是一种优化方法。
如果address和products为一对多的关系时两种方法结构相同，那是否存在效率上的差异？由SQL SERVER的查询计划可以看到：优化器在处理这两种方式时，采用的是同一种策略，总是先作选择运算，然后在作联接运算。无论两表的联接是以join的方式表达，还是在where子句中表达，抑或是以嵌套查询的方式表达，其实现方式和代价是完全一样的。
6 结语
以上对SQL语句优化的一部分问题作了实践性的探讨，可见，由于数据库系统在执行SQL指令时均有一个优化过程，因此，在讨论SQL语句的优化问题时，应当结合具体环境，结合优化器的处理法则。仅仅从语法上作逻辑上的解释和想象所提出的优化对SQL用户的实践可能是一种误导。
参考文献：
[1] 王书海，刘明生，马银华.基于多表连接的分组查询语句的性能分析与优化[J].计算机工程，2000，26(7):186-187.
[2] 谷震离. 查询语句对SQL Server 数据库查询性能优化分析 [J].福建电脑，2007，3:21-22.
[3] 王振辉，吴广茂.SQL查询语句优化研究[J].计算机应用，2005，25(12):207-208.
[4] 杨庚.关系数据库SQL语言查询过程的分析和优化设计[J]，计算机工程与应用，1999，11:87-88.
[5] [美]微软公司著.Querying Microsoft SQL Server 2000 With Transact-SQL[M].北京:清华大学出版社,2001.
[6] [美]微软公司著.Programming a Microsoft SQL Server 2000 Database[M].北京:清华大学出版社,2001.
[7] 苗雪兰,刘瑞新,王怀峰. 数据库系统原理及应用教程(第2版)[M]. 北京: 机械工业出版社,2004.
关键词：SQL；优化；SQL SERVER；数据库
中图分类号：TP311文献标识码：A 文章编号：1009-3044(2008)15-21002-02
The Method of Optimization in SQL Server
WU Xian-feng
(Sichuan Institute of Administration,Chengdu 610004,China)
Abstract:This paper research some questions of SQL performance optimization with SQL SERVER such as operator, UNION operation, Multi-search condition, JION operation. Considers that, because of Models of optimizing in database, some suggestion of optimization base on pure theory would misadvise user of SQL.
Key words: SQL; optimization; SQL SERVER; database
1 引言
SQL查询的优化是数据库应用领域的一个重要问题，关于这个问题的讨论很多。但遗憾的是很多讨论更多的从SQL语句的语法和构造的层面来进行说明，脱离具体的数据库环境。事实上，优化问题是一个实践性很强的问题，数据库在执行语句的时候，并非完全按SQL语句中规定的逻辑和层次来执行，数据库会对SQL语句进行解析后，按特定的方式来执行。不同的数据库处理同样的SQL语句也会有区别。
这里我们结合SQL SERVER对比较常见的几种优化建议进行探讨。
2 查询符号的优化
在条件表达式中，查询符号的使用可能影响到索引能否被使用，从而影响查询的效率，一般遵循这样一些原则：不要使用表达式，尽量避免负逻辑，避免语句出现LIKE“%”等，此外，研究者一般认为，使用or连接条件时，会被强制使用全表扫描，也有人认为exist的效率远远低于in 的效率。
我们选用一个有10万行的表在SQL SERVER环境下进行测试，通过SQL SERVER提供的查询计划图形显示，我们很容易得到查询是按何种方式展开的。结果证明在where子句中，如果在字段上使用了代数表达式或函数时，使用LIKE“%”，查询方式是全表扫描，但是在负逻辑，or连接条件等情况下，却并不一定采用全表扫描的方式。
仔细分析其过程，可以看出，SQL SERVER优化器首先预测结果行的数量，如果结果行数量较多时，则无论是负逻辑还是正逻辑，均采用全表扫描。如果结果行较少，我们可称其为具有强选择性，则优化器会自动将负逻辑转化为等价的正逻辑表达，然后在索引上扫描。
同样，在分析or连接时，也可以看到，执行计划同样有可能在索引上扫描，是全表扫描还是扫描索引，不是取决于使用and或是or连接，而是取决于对结果行数量的预测。即使是在or的两端是不同的字段时，如：discount=1 or ordered=10000 ，只要存在相关的索引，查询时均可使用这些索引。
与此相关的另一个问题是，许多人认为or符号连接条件将被强制全表扫描，所以建议用UNION来替代OR符号，以提高效率，事实上这也是得不偿失的。因为，一般情况下，在UNION中可以用到的索引在or符号表达时同样可以用到。在少数情况下，确实存在使用or符号时不能利用索引，而在用UNION表达时可以利用索引的情况。但由于UNION操作中多了合并插入、去除重复行等工作，所以总体效率还是会低于在单一的SELECT语句中用OR符号。
3 联合查询时的优化
在联合（union）运算时，一般认为需要考虑的问题是，在SQL语句中各个SELECT语句的顺序问题。主张将结果行相对较少的行放在靠后，这样可以减少插入操作。
但在SQL SERVER中，我们针对这一问题设计含两个SELECT子句的UNION语句，使其两个SELECT语句中返回的行数又较大的差别，可以看到这样的结果：
(1)如果两个SELECT语句中，如果小结果语句具有强选择性，因而利用了索引扫描。这种情况下，大结果放在前面效率较高。
(2)如果两个SELECT语句中均不具有强选择性，但该表中有聚集索引时，SQL SERVER对两部分结果的合并采用Merge Union运算。
我们以一个具有1000个返回行的结果集为小结果，并将与其组合的大结果集的行数做多种变化，得到以下一组数据（见表1）：
这里的索引是非聚集索引，如果其中的条件覆盖聚集索引时情况稍有不同。由此我们可以看出，多条件时，条件的执行顺序会被优化器作恰当的安排，由于存在索引的因素，系统的优化也不是简单的将强条件先行执行。而开发人员在构造SQL语句的时候，则没有必要去考虑where子句中条件的顺序问题。
5 联接运算的优化
联接运算优化中的一个问题是：当一个SQL中既有联接运算，同时也存在的选择运算时，应当采用先选择运算缩小联接运算的范围，避免联接运算产生较大的临时表。所以有人举出以下典型的例子：
方法一：SELECT * FROM products ,address WHERE products.productid = address. Productid and date = {^2005 - 1 - 1}
方法二：SELECT * FROM address WHERE productid in (SELECT productid FROM products WHERE date= {^2005 - 1 - 1})
事实上这两种方法的结果集并非完全等价，因为在in操作符在处理嵌套语句的结果集时，会自动剔出重复的值。因此，它的结果等同于address和products之间为一对多的关系时的联接结果。换而言之，如果address和products之间是多对多的关系时，两种方法的结果是不一致的，第一种方法的结果集将大于第二种方法。所以，严格而言，无论第二种方法的性能如何，都不能算是一种优化方法。
如果address和products为一对多的关系时两种方法结构相同，那是否存在效率上的差异？由SQL SERVER的查询计划可以看到：优化器在处理这两种方式时，采用的是同一种策略，总是先作选择运算，然后在作联接运算。无论两表的联接是以join的方式表达，还是在where子句中表达，抑或是以嵌套查询的方式表达，其实现方式和代价是完全一样的。
6 结语
以上对SQL语句优化的一部分问题作了实践性的探讨，可见，由于数据库系统在执行SQL指令时均有一个优化过程，因此，在讨论SQL语句的优化问题时，应当结合具体环境，结合优化器的处理法则。仅仅从语法上作逻辑上的解释和想象所提出的优化对SQL用户的实践可能是一种误导。
参考文献：
[1] 王书海，刘明生，马银华.基于多表连接的分组查询语句的性能分析与优化[J].计算机工程，2000，26(7):186-187.
[2] 谷震离. 查询语句对SQL Server 数据库查询性能优化分析 [J].福建电脑，2007，3:21-22.
[3] 王振辉，吴广茂.SQL查询语句优化研究[J].计算机应用，2005，25(12):207-208.
[4] 杨庚.关系数据库SQL语言查询过程的分析和优化设计[J]，计算机工程与应用，1999，11:87-88.
[5] [美]微软公司著.Querying Microsoft SQL Server 2000 With Transact-SQL[M].北京:清华大学出版社,2001.
[6] [美]微软公司著.Programming a Microsoft SQL Server 2000 Database[M].北京:清华大学出版社,2001.
[7] 苗雪兰,刘瑞新,王怀峰. 数据库系统原理及应用教程(第2版)[M]. 北京: 机械工业出版社,2004.