优化SQL语句避免锁堵塞.docx

优化SQL语句避免锁堵塞有些程序员在撰写数据库应用程序时，常专注于 OOP 及各种 framework 的使用，却忽略了基本的 SQL 语句及其性能 (performance) 优化问题。版工曾听过台湾某半导体大厂的新进程序员，所组出来的一段 PL/SQL 跑了好几分钟还跑不完；想当然尔，即使他的 AJAX 及 ooxx 框 架用得再漂亮，系统性能也会让使用者无法忍受。以下是版工整理出的一些数据库规划、SQL performance tuning 简单心得，让长年钻研 .NET、AJAX、一堆高深 ooxx framework，却无暇研究 SQL statement 的程序员，透过最短时间对本帖的阅读，能避免踩到一些 SQL 的性能地雷。(注：本帖的 SQL 语句皆经过测试可正常执行无误。有兴趣实验者，可直接拷贝后，粘贴至 SQL Server 中执行。)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;GOSELECT * 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 ONSELECT OrderID, ROW_NUMBER() OVER(ORDER BY OrderID) AS 编号 FROM dbo.Orders但如果是传统的子查询写法，或 辅以 AS 关键词的衍生数据表的语法，写法必须如下 (拷贝后在 SQL Server 中实际可执行)：SET STATISTICS TIME ONSELECT 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 rownumFROM 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的语法，衍生数据表只会存在内存中，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 语句传递的网络