有些程序员在撰写数据库应用程序时,常专注于 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; 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 的画面 |
![](https://pic002.cnblogs.com/img/wizardwu/200810/2008102700023836.jpg)
图 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 的语法,「衍生数据表」只会存在内存中,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 筛选出所要的资料。有关这方面,网友们可参考版工我之前写的「ASP.NET 数据分页」系列的四篇帖子。
------------------------------
结论:
本文的观念,不管是写 SQL statement、Stored Procedure、自订函数或 View 皆然。本文只是挑出程序员较容易犯的 SQL 语法性能问题,以期能在短时间浏览过本文后,在写 ADO.NET 程序时能修正以往随兴的 SQL 语句撰写习惯。文中提到的几点,只不过是 SQL 语法性能议题的入门。市面上有很多更进阶的书籍,例如:「The Art of SQL」、「SQL Tuning」,亦有针对 Oracle 或 SQL Server 数据库撰写的 performance tuning 相关书籍,有兴趣者可自行翻阅。
------------------------------------------------------------
------------------------------------------------------------
参考文件:
[1] SQL 查询最佳化 (网际乌托邦):
http://www.ithome.com.tw/plog/index.php?op=ViewArticle&articleId=5421&blogId=620
------------------------------
参考书籍:
[2] SQL Server 2005 Performance Tuning 效能调校 (台湾书籍):
作者:胡百敬、姚巧枚、刘承修
出版社:悦知出版社
http://tlsj.tenlong.com.tw/WebModule/BookSearch/bookSearchViewAction.do?isbn=9789866761225&sid=41966
[3] SQL Server 2005 完全实战 (台湾书籍):
作者:章立民
出版社:碁峰出版社
http://tlsj.tenlong.com.tw/WebModule/BookSearch/bookSearchViewAction.do?isbn=9789861810454&sid=31975
------------------------------
相关文件:
[4] 台大医院数据库分割疏失,系统几近停摆 (ITHome):
http://www.ithome.com.tw/itadm/article.php?c=43597
[5]] 当 DataGrid 遇见100 万笔资料:
http://blog.sina.com.tw/4907/article.php?pbgid=4907&entryid=3921
[6] ASP.NET 2.0 GridView 范例集 - 「4-8-4、GridView 的效能」:
http://blog.csdn.net/Code6421/archive/2007/12/22/1958167.aspx
[7] 有关开启页面时,一次加载数千笔数据的性能问题:
http://www.blueshop.com.tw:80/board/show.asp?subcde=BRD200709141021458MV
------------------------------
转帖地址:
http://www.cnblogs.com/WizardWu/archive/2008/10/27/1320055.html