编写高效 SQL 语句最佳实践.docxVIP

序言SQL 语言是一种强大而且灵活的语言，在使用 SQL 语言来执行某个关系查询的时候，用户可以写出很多不同的 SQL 语句来获取相同的结果。也就是说，语法 (syntactical) 不同的 SQL 语句，有可能在语义 (semantical) 上是完全相同的。但是尽管这些 SQL 语句最后都能返回同样的查询结果，它们在 DB2 中执行所需要的时间却有可能差别很大。这是为什么？众所周知，DB2 数据库具有强大的功能，可以自动地把用户输入的 SQL 语句改写为多个语义相同的形式并从中选取一个耗时最少的语句来执行。但是 DB2 并不能够永远对所有的 SQL 语句都成功的改写来取得最优的执行方案。其中一个方面的原因就是数据库应用程序的开发人员在写 SQL 语句的时候有一些习惯性的“小问题”，而正是这些小问题带来了 SQL 语句运行时性能上的大问题。正如平时所说“条条大路通罗马”，但是并非所有通往罗马的路都是坦途，我们应该找到那条最有效的道路。回页首编写高效 SQL 语句的一些最佳实践这里我们将介绍在编写 SQL 语句时可能影响 DB2 查询性能的一些常见问题，并给出相应的编写高效 SQL 语句的最佳实践（best-practices）。避免不恰当的使用“SELECT *”像“SELECT *”这样的写法在用户使用中可能很常见，它表示把满足查询条件的每一条记录（Row）的所有列都返回。但是有时候这种用法很可能导致数据库查询时候的性能问题。假定 Sale 是一个包括 25 个列（column）的表，那么下面这条查询语句就有可能在执行时性能较差，其中一部分原因就是在 SELECT 中使用了*. SELECT *FROM Sales WHERE YEAR(Date) 2004 AND Amount 1000 如果 SQL 语句使用了“SELECT *”，DB2 就需要把表的所有列都从外部存储介质上（如磁带或者硬盘）复制到 DB2 的内存中来进行处理并且返回给用户，这显然会增加 I/O 和 CPU 的开销。而且如果这条 SQL 语句还包括了排序（Sort）操作（比如 ORDER BY），那么对全部这些列进行排序也可能会影响到性能。而且当表定义的列越多，每个列定义的数据类型（Data type）长度越长，这对性能的影响就可能越明显。除此之外，DB2 还有一种被称为“Index-Only”的数据访问方法，如果某个表上需要检索的所有列都能在某个合适的索引（Index）上找到，DB2 就会使用“Index-Only”这种数据访问方式。因为这种访问方式仅需要对索引进行检索而无需对表本身进行读取，所以是一种较快高速的访问方式。但是如果用户输入的 SQL 语句中使用了“SELECT *”，就意味着需要访问表上的所有列。而通常情况下并不存在一个合适的索引是定义在这个表所有的列之上的（尤其是对于定义了许多列的表），这就使得 DB2 无法使用“Index-Only”这种较快的数据访问方式，而改用其他数据访问方式，这也有可能导致查询性能的问题。所以除非真的需要读取表中的所有列，否则基于提高查询性能的考虑，在写 SQL 语句的时候应该尽量避免使用“SELECT *”这样的情况。这是一条很简单却常常被用户忽略的最佳实践。下面来看一个具体的示例。需要说明的是，本文示例中用到的表，除特别说明外，均为 TPC-D 标准中定义的表，这样有助于读者更好的理解 SQL 语句本身。对于 TPC-D 标准的介绍，见文章最后的参考资源。在这个例子中，我们比较 2 个不同的 SQL 语句在性能上的差别。2 个 SQL 的谓词完全相同，并且这个谓词有符合的索引可以使用。 SQL 1：select * from lineitem where l_orderkey = ? SQL 2：select l_suppkey, l_partkey from lineitem where l_orderkey = ? 但是 SQL 1 使用了 select *，所以 DB2 在读取索引之后，必须再去对表中进行一次 Fetch 操作，读取那些索引中不存在的列数据。而在实际业务需求中如果并不需要这些数据，这个 Fetch 操作就是多余的而且会带来性能问题。对比 SQL 2，它明确指出了在结果集中希望得到的列 l_suppkey, l_partkey，而这些列已经全部包含在索引中，所以数据库采用了 Index-Only 的扫描方式，仅仅读取了索引，不再需要对表本身的 fetch 操作，从而使得性能得到了大幅提升。图 1. SQL 1 的访问路径图图 2. SQL 2 的访问路径图避免在本地谓词的列上使用复杂表达式所谓的本地谓词（Local predicate）是与连接谓词（Join predicate