30 分钟快快乐乐学 SQL Performance Tuning

有些程序员在撰写数据库应用程序时,常专注于 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 在此的「集簇索引」扫描,并未直接使用索引,性能上几乎只等于扫描整个数据表

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值