快捷搜索:

SQL高手进阶 深入SQL语句性能调整

有些法度榜样员在撰写数据库利用法度榜样时,常专注于 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,可校阅阅兵如下图的「履行计划」。

498)this.style.width=498;'height=288 alt="" src="http://www.51cto.com/files/uploadimg/20081103/0948090.jpg" width=509 border=0>

图 1

可看出「查询最佳化法度榜样」有应用到索引做征采

498)this.style.width=498;'height=203 alt="" src="http://www.51cto.com/files/uploadimg/20081103/0948091.jpg" width=468 border=0>

图 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 的画面

498)this.style.width=498;'height=291 alt="" src="http://www.51cto.com/files/uploadimg/20081103/0948092.jpg" width=568 border=0>

图 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)」等功能,而且履行机能极佳。

498)this.style.width=498;'height=312 alt="" src="http://www.51cto.com/files/uploadimg/20081103/0948093.jpg" width=536 border=0>

图 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

但这种旧写法,会像先前所提到的,外层 (外圈) 查询的「每一次」查询动作都必要引用内层 (内圈) 查询的数据。以上方示例而言,外层查询的每一笔数据,都要等内层查询「扫描全部数据表」并作比对和计数,是以 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

虽然这是较极度的典型,但由此可知子查询的撰写,在应用上弗成掉慎,尤其是「关联子查询」。法度榜样员在系统开拓初期、数据量还很少时感想熏染不到此种 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 相关册本,有兴趣可自行翻阅。

您可能还会对下面的文章感兴趣: