学习SQL语句性能调整

【IT168 技术文档】
         有些程序员在撰写数据库应用程序时，常专注于 OOP 及各种 framework 的使用，却忽略了基本的 SQL 语句及其「性能 (performance) 优化」问题。曾听过台湾某半导体大厂的新进程序员，所组出来的一段 PL/SQL 跑了好几分钟还跑不完;想当然，即使他的 AJAX 及 ooxx 框架用得再漂亮，系统性能也会让使用者无法忍受。以下是整理出的一些数据库规划、SQL performance tuning 简单心得，让长年钻研 .NET、AJAX、一堆高深 ooxx framework，却无暇研究 SQL statement 的程序员，透过最短时间对本文的阅读，能避免踩到一些 SQL 的性能地雷。

　　1、数据库设计与规划

　　• Primary Key 字段的长度尽量小，能用 small integer 就不要用 integer。例如员工数据表，若能用员工编号当主键，就不要用身分证号码。

　　• 一般字段亦同。若该数据表要存放的数据不会超过 3 万笔，用 small integer 即可，不必用 integer。

　　• 文字数据字段若长度固定，如：身分证号码，就不要用 varchar 或 nvarchar，应该用 char 或 nchar。

　　• 文字数据字段若长度不固定，如：地址，则该用 varchar 或 nvarchar。除了可节省存储空间外，存取硬盘时也会较有效率。

　　• 设计字段时，若其值可有可无，最好也给一个默认值，并设成「不允许 NULL」(一般字段默认为「允许 NULL」)。因为 SQL Server 在存放和查询有 NULL 的数据表时，会花费额外的运算动作 [2]。

　　• 若一个数据表的字段过多，应垂直切割成两个以上的数据表，并可用同名的 Primary Key 一对多连结起来，如：Northwind 的 Orders、Order Details 数据表。以避免在存取数据时，以「集簇索引 (clustered index)」扫描时会加载过多的数据，或修改数据时造成互相锁定或锁定过久。

　　2、适当地建立索引

　　• 记得自行帮 Foreign Key 字段建立索引，即使是很少被 JOIN 的数据表亦然。

　　• 替常被查询或排序的字段建立索引，如：常被当作 WHERE 子句条件的字段。

　　• 用来建立索引的字段，长度不宜过长，不要用超过 20 个 Byte 的字段，如：地址。

　　• 不要替内容重复性高的字段建立索引，如：性别;反之，若重复性低的字段则适合建立索引，如：姓名。

　　• 不要替使用率低的字段建立索引，以免浪费硬盘空间。

　　• 不宜替过多字段建立索引，否则反而会影响到「INSERT、UPDATE、DELETE」的性能，尤其是以「OLTP (联机事务处理;在线交易)」为主的网站数据库。

　　• 若数据表存放的数据很少，就不必刻意建立索引。否则可能数据库沿着存放索引的「树状结构」(Balanced Tree) 去搜寻索引中的数据，反而比扫描整个数据表还慢。

　　• 若查询时符合条件的数据很多，则透过「非集簇索引 (non-clustered index)」搜寻的性能，反而 可能不如整个数据表逐笔扫描。

　　• 建立「集簇索引」的字段选择至为重要，会影响到整个索引结构的性能。要用来建立「集簇索引」的字段，务必选择「整数」类型 (键值会较小)、唯一、不可为 NULL。

　　3、适当地使用索引

　　• 有些书籍会提到，使用「LIKE、%」做模糊查询时，即使您已替某个字段建立索引 (如下方代码的 CustomerID 字段)，但以常量字符开头才会使用到索引，若以万用字符 (%) 开头则不会使用索引，如下所示：

USE Northwind;

　　GO

　　SELECT * FROM Orders WHERE CustomerID LIKE 'D%'; --使用索引

　　SELECT * FROM Orders WHERE CustomerID LIKE '%D'; --不使用索引

　　在 SQL Server 2005 执行完成后按 Ctrl + L，可检阅如下图的「执行计划」。

图 1　可看出「查询最佳化程序」有使用到索引做搜寻

 

　　图 2　在此的「集簇索引」扫描，并未直接使用索引，性能上几乎只等于扫描整个数据表

　　但经反复测试，这种语法是否会使用到索引，抑或会逐笔扫描，并非绝对的。仍要看所下的查询关键词，以及字段内 所存储的数据内容而定。但对于存储数据笔数庞大的数据表，最好还是少用 LIKE 做模糊查询。

　　• 以下的运算符会造成「负向查询」，常会让「查询最佳化程序」无法有效地使用索引，最好能用其它运算符和语法改写 (经版工测试，并非有负向运算符，就绝对无法使用索引)：

　　NOT 、 != 、 <> 、 !> 、 !< 、 NOT EXISTS 、 NOT IN 、 NOT LIKE

　　• 避免让 WHERE 子句中的字段，去做字符串的串接或数字运算，否则可能导致「查询最佳化程序」无法直接使用索引，而改采「集簇索引扫描」(经版工测试并非绝对)。

　　• 数据表中的数据，会依照「集簇索引」字段的顺序存放，因此当您下 BETWEEN、GROUP BY、ORDER BY 时若有包含「集簇索引」字段，由于数据已在数据表中排序好，因此可提升查询速度。

　　• 若使用「复合索引」，要注意索引顺序上的第一个字段，才适合当作过滤条件。

　　4、避免在 WHERE 子句中对字段使用函数

　　对字段使用函数，也等于对字段做运算或串接的动作，一样可能会让「查询最佳化程序」无法有效地使用索引。但真正对性能影响最重大的，是当您的数据表内若有 10 万笔数据，则在查询时就需要呼叫函数 10 万次，这点才是真正的性能杀手。程序员应注意，在系统开发初期可能感觉不出差异，但当系统上线且数据持续累积后，这些语法细节所造成的性能问题就会逐步浮现。

　　SELECT * FROM Orders WHERE DATEPART(yyyy, OrderDate) = 1996 AND DATEPART(mm, OrderDate)=7

　　可改成

　　SELECT * FROM Orders WHERE OrderDate BETWEEN '19960701' AND '19960731'

　　SELECT * FROM Orders WHERE SUBSTRING(CustomerID, 1, 1) = 'D'

　　可改成

　　SELECT * FROM Orders WHERE CustomerID LIKE 'D%'

　　注意当您在下 UPDATE、DELETE 语句时，若有采用 WHERE 子句，也应符合上述原则。

　　5、AND 与 OR 的使用

　　在 AND 运算中，「只要有一个」条件有用到索引 (如下方的 CustomerID)，即可大幅提升查询速度，如下图 3 所示：

　　SELECT * FROM Orders WHERE CustomerID='VINET' AND Freight=32.3800 --使用索引，会出现下图 3 的画面

　　SELECT * FROM Orders WHERE Freight=32.3800 --不使用索引，会出现上图 2 的画面

　　图 3

　　但在 OR 运算中，则要「所有的」条件都有可用的索引，才能使用索引来提升查询速度。因此 OR 运算符的使用必须特别小心。

　　若您将上方 AND 的范例，逻辑运算符改成 OR 的话，如下所示：

　　SELECT * FROM Orders WHERE CustomerID='VINET' OR Freight=32.3800

　　由于无法有效地使用索引，也会出现图 2 的画面。

　　在使用 OR 运算符时，只要有一个条件 (字段) 没有可用的索引，则其它所有的条件 (字段) 都有索引也没用，只能如图 2 般，把整个数据表或整个集簇索引都扫描过，以逐笔比对是否有符合条件的数据。

　　据网络上文件的说法 [1]，上述的 OR 运算语句，我们还可用 UNION 联集适当地改善，如下：

SELECT * FROM Orders WHERE CustomerID='VINET'

　　UNION

　　SELECT * FROM Orders WHERE Freight=32.3800

　　此时您再按 Ctrl + L 检阅「执行计划」，会发现上半段的查询会使用索引，但下半段仍用集簇索引扫描，对性能不无小补。

　　6、适当地使用子查询

　　相较于「子查询 (Subquery)」，若能用 JOIN 完成的查询，一般会比较建议使用后者。原因除了 JOIN 的语法较容易理解外，在多数的情况下，JOIN 的性能也会比子查询较佳;但这并非绝对，也有的情况可能刚好相反。

　　我们知道子查询可分为「独立子查询」和「关联子查询」两种，前者指子查询的内容可单独执行，后者则无法单独执行，亦即外层查询的「每一次」查询动作都需要引用内层查询的数据，或内层查询的「每一次」查询动作都需要参考外层查询的数据。

　　以下我们看一个比较极端的例子 [2]。若我们希望所有查询出来的数据，都能另外给一个自动编号，版工我在之前的文章「ASP.NET 数据分页第一篇 - 探讨分页原理及 SQL Server 2005 的 ROW_NUMBER 函数」中有介绍过，可用 SQL Server 2005 中新增的 ROW_NUMBER 函数轻易地达成，且 ROW_NUMBER 函数还能再加上「分群 (PARTITION BY)」等功能，而且执行性能极佳。

　　图 4　将 Orders 数据表的 830 笔数据都捞出来，并在右侧给一组自动编号

　　现在我们要如上图 4 般，将 Northwind 数据库中 Orders 数据表的 830 笔数据都捞出来，并自动给一组编号，若用 ROW_NUMBER 函数的写法如下所示，而且性能极佳，只要 2 ms (毫秒)，亦即千分之二秒。

SET STATISTICS TIME ON

　　SELECT OrderID, ROW_NUMBER() OVER(ORDER BY OrderID) AS 编号

　　FROM dbo.Orders

　　但如果是传统的「子查询」写法，或 辅以 AS 关键词的「衍生数据表」的语法，写法必须如下 (拷贝后在 SQL Server 中实际可执行)：

SET STATISTICS TIME ON

　　SELECT OrderID,

　　(SELECT COUNT(*) FROM dbo.Orders AS 内圈

　　WHERE 内圈.OrderID <= 外圈.OrderID) AS 编号

　　FROM dbo.Orders AS 外圈

　　ORDER BY 编号

　　但这种旧写法，会像先前所提到的，外层 (外圈) 查询的「每一次」查询动作都需要引用内层 (内圈) 查询的数据。以上方示例而言，外层查询的每一笔数据，都要等内层查询「扫描整个数据表」并作比对和计数，因此 830 笔数据每一笔都要重复扫描整个数据表 830 次，所耗用的时间也因此爆增至 170 ms。

　　若您用相同的写法，去查询 AdventureWorks 数据库中，有 31,465 笔数据的 Sales.SalesOrderHeader 数据表，用 ROW_NUMBER 函数要 677 ms，还不到 1 秒钟;但用子查询的话，居然要高达 233,835 ms，将近快 4 分钟的时间。

-- 用 ROW_NUMBER 的写法，改查询 AdventureWorks 数据库 (31,465 笔数据，要 677 ms，还不到 1 秒钟)

　　SELECT SalesOrderID, ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownum

　　FROM Sales.SalesOrderHeader

　　

-- 用「子查询」的写法，改查询 AdventureWorks 数据库 (31,465 笔数据，要 233,835 ms，将近 4 分钟)

　　SELECT SalesOrderID,

　　(SELECT COUNT(*) FROM Sales.SalesOrderHeader AS 内圈

　　WHERE 内圈.SalesOrderID <= 外圈.SalesOrderID) AS 编号

　　FROM Sales.SalesOrderHeader AS 外圈

　　ORDER BY 编号

　　虽然这是较极端的范例，但由此可知子查询的撰写，在使用上不可不慎，尤其是「关联子查询」。程序员在系统开发初期、数据量还很少时感受不到此种 SQL 语法的重大陷阱;但等到系统上线几个月或一两年后，就会有反应迟缓的现象， 不可不慎。

　　注：AS 关键词及「衍生数据表」是 SQL Server 2005 的新语法，「衍生数据表」只会存在内存中，AS 关键词的作用是赋予一个别名。过去许多必须用暂存数据表或 View (视图) 的情况，现在都可以用「衍生数据表」来取代，如此一来不但可以降低数据库管理工作的负担，亦可提升查询性能。

　　7、其他查询技巧

　　• DISTINCT、ORDER BY 语法，会让数据库做额外的计算。此外「联集」的使用，若没有要剔除重复数据的需求，使用 UNION ALL 会比 UNION 更优，因为后者会加入类似 DISTINCT 的算法。

　　• 在 SQL Server 2005 中，存取数据库对象时，最好明确指定该对象的「结构描述 (Schema)」，也就是使用两节式的名称，如下方代码所示。否则若呼叫者的预设 Schema 不是 dbo，则 SQL Server 在执行时，会先寻找该使用者预设 Schema 所搭配的对象，找不到的话才会转而使用预设的 dbo，会多耗费寻找的时间。因此若要执行一个叫做 dbo.mySP1 的 Stored Procedure，应使用以下的两节式名称： EXEC dbo.mySP1

　　8、尽可能用 Stored Procedure 取代应用程序直接存取数据表

　　Stored Procedure 除了经过事先编译、性能较好以外，亦可节省 SQL 语句传递的网络频宽，也方便商业逻辑的重复使用。再搭配自订函数和 View 的使用，将来若要修改数据表结构、重新切割或「反正规化」时亦较方便。

　　9、尽可能在数据来源层，就先过滤数据

　　使用 SELECT 语法时，尽量避免传回所有的数据至前端而不设定 WHERE 等过滤条件。虽然 ASP.NET 中 SqlDataSource、ObjectDataSource 控件的 FilterExpression 可再做筛选，GridView 控件的 SortExpression 可再做排序，但会多消耗掉数据库的系统资源、web server 的内存和网络频宽。最好还是在数据库和数据来源层，就先用 SQL 条件式或 Stored Procedure 筛选出所要的资料。

　　结论

　　本文的观念，不管是写 SQL statement、Stored Procedure、自订函数或 View 皆然。本文只是挑出程序员较容易犯的 SQL 语法性能问题，以期能在短时间浏览过本文后，在写 ADO.NET 程序时能修正以往随兴的 SQL 语句撰写习惯。文中提到的几点，只不过是 SQL 语法性能议题的入门。市面上有很多更进阶的书籍，例如：「The Art of SQL」、「SQL Tuning」，亦有针对 Oracle 或 SQL Server 数据库撰写的 performance tuning 相关书籍，有兴趣者可自行翻阅。