DB适合做什么

DB适合做什么?

DB谝悄羁笑冒缪菀桓鍪裁唇巧?我们还可以如何挖掘DB的潜能?

1 视图

1.1 用途

1.1.1 在跨部门协作时保证数据安全性

1.1.2 展示满足特殊需求的业务实体列表

1.1.3 统一单位换算

1.1.4 封装主从Table连接逻辑

有些业务实体,在设计Table的时候需要满足范式, 所以信息被分散到多个主从结构的Table中, 但是在项目中获取这类实体的信息时信息常常同时来自主从Table, 而视图则可以封装主从Table间的连接逻辑

1.2 缺陷

1.2.1 隐藏了性能问题

习惯把它放在From子句中,但是请注意

1.3 优势

1.3.1 降低最终SQL的复杂度

1.3.2 自动随Table数据更新

1.3.3 不占用空间

1.4 待挖掘的用途

1.4.1 隔离LinkedServer

SELECT * FROM OPENQUERY(bqs_gsm,'SELECT * FROM picslne')

CREATE VIEW dbo.v_picslne_bqs_gsm AS SELECT * FROM OPENQUERY(bqs_gsm,'SELECT * FROM picslne')

SELECT * FROM v_picslne_bqs_gsm

INSERT INTO v_picslne_bqs_gsm SELECT 'a','b','c',1111,1111

UPDATE v_picslne_bqs_gsm SET segment='d' WHERE lmdate=1111

DELETE FROM v_picslne_bqs_gsm WHERE lmdate=1111

1.4.2 可更新的视图

1.4.3 物化视图(解决性能缺陷, 空间换取时间)[限Oracle]

2 存储过程

2.1 用途

2.1.1 数据汇总作业

2.1.2 封装原子业务流程

2.1.3 拼装很难用SQL产生的记录集

这些记录集我们通常是在AP去生成:从DB取来大量数据,分别循环,一一匹配,换算.但是这样的循环却是DB的专长.

2.2 缺陷

2.2.1 无法传递丰富的参数

2.2.2 错误处理机制差(2005中已有改善)

2.2.3 返回结果无法被SELECT引用 (Oracle的存储过程返回记录集极为不便)

2.3 优势

2.3.1 性能极佳

2.3.2 可执行动态SQL

2.4 待挖掘的用途

2.4.1 异构数据库间调用技术还没有被掌握

2.4.2 异构数据库间的Transaction还没有被掌握

3 函数

当我们把各种业务逻辑都移植到DB之后,一个问题就产生了,DB无法调用AP中丰富的公函.而DB的用户自定义函数就可以帮助我们解决这个问题.(当然是很有限的)

3.1 分类

3.1.1 标量函数

可以被SELECT子句引用

3.1.2 表值函数

可以被FROM子句引用

与视图有何不同

1.视图只能取到在数据库中存在的数据,而函数可以创造在数据库中不存在的数据;

2.但是视图不会占用存储空间,而函数每次都要创建表变量,会占用存储空间.

与返回记录集的存储过程有何不同

1.在存储过程中可以创建临时表,在函数中不能;

2.在函数和存储过程中都可以使用表变量;

3.但是函数有个优势,可以被FROM子句引用,那它的返回结果就可以与其他Table进行连接查询.

3.1.3 *可更新的表值函数

3.2 用途

3.2.1 处理日期类型

3.2.2 取得单个对象的业务属性

3.2.3 字符串汇总

3.2.4 创建用于外连接的主表

3.3 缺陷

3.3.1 无法执行动态SQL

3.3.2 无法调用不确定的系统函数 (可以通过视图来绕过这个限制)

3.4 优势

3.4.1 不可以更改函数以外的任何对象

即不能对Table进行删插改,不能执行DDL语句.

3.5 待挖掘的用途

4 SQL

4.1 SELECT

4.1.1 EXISTS

by实体的最后一笔记录

栏位组合上的连接

4.1.2 CASE

行列转换

交叉表

加权汇总

条件汇总

用于ORDER BY子句

4.1.3 GROUP BY

附属属性不必放入GROUP BY中

4.1.4 UNION ALL

在写"UNION"的时候请考虑可否用"UNION ALL"代替,后者开销更小.

4.1.5 SubQuery

In SELECT

限制

只能返回一笔记录

In FROM

特性

又叫派生表

In WHERE

用途

多用于产生IN子句的列表

4.2 UPDATE

4.2.1 批量更新

UPDATE ... FROM ...

4.3 INSERT

4.3.1 批量插入

INSERT INTO ... SELECT ... FROM ...

5 索引

5.1 性能优化的主要手段

5.2 分类

5.2.1 聚集索引

决定了记录的物理排序.

定义聚集索引键时使用的列越少越好，这一点很重要。如果定义了一个大型的聚集索引键，则同一个表上定义的任何非聚集索引都将增大许多，因为非聚集索引条目包含聚集键。

应该创建在

包含大量非重复值的列。

使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。

被连续访问的列。

返回大型结果集的查询。

经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。

最好不要创建在

频繁更改的列:

这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。

宽键:

来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。

5.2.2 非聚集索引

SQL Server 2000 在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。如果基础表使用聚集索引排序，则该位置为聚集键值；否则，该位置为包含行的文件号、页号和槽号的行ID(RID)。

应该创建在

包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引，因为此时表扫描通常更有效。

不返回大型结果集的查询。

返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。

经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。

在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。

最好不要创建在

包含大量重复值的列.(5%)

5.2.3 唯一索引

是PRIMARY KEY 约束和UNIQUE 约束的实现载体.

只有当唯一性是数据本身的特征时，指定唯一索引才有意义。如果必须实施唯一性以确保数据的完整性，则应在列上创建 UNIQUE 或 PRIMARY KEY 约束，而不要创建唯一索引。例如，如果打算经常查询雇员表（主键为 emp_id）中的社会安全号码 (ssn) 列，并希望确保社会安全号码的唯一性，则在 ssn 列上创建 UNIQUE 约束。如果用户为一个以上的雇员输入了同一个社会安全号码，则会显示错误。

创建 PRIMARY KEY 或 UNIQUE 约束会在表中指定的列上自动创建唯一索引。创建 UNIQUE 约束与手动创建唯一索引没有明显的区别。进行数据验证的方式相同，而且查询优化器不区分唯一索引是由约束创建还时手动创建。如果存在重复的键值，则无法创建唯一索引和 UNIQUE 约束。

在同一个列组合上创建唯一索引而不是非唯一索引可为查询优化器提供附加信息；所以最好创建唯一索引。

PRIMARY KEY 约束

表中经常有一个列或列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。

一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列。

当为表指定 PRIMARY KEY 约束时，Microsoft SQL Server 2000 通过为主键列创建唯一索引强制数据的唯一性。当在查询中使用主键时，该索引还可用来对数据进行快速访问。

如果 PRIMARY KEY 约束定义在不止一列上，则一列中的值可以重复，但 PRIMARY KEY 约束定义中的所有列的组合的值必须唯一。

UNIQUE 约束

可使用 UNIQUE 约束确保在非主键列中不输入重复值。尽管 UNIQUE 约束和 PRIMARY KEY约束都强制唯一性，但在强制下面的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束：

非主键的一列或列组合。

一个表可以定义多个 UNIQUE 约束，而只能定义一个 PRIMARY KEY 约束。

允许空值的列。

允许空值的列上可以定义 UNIQUE 约束，而不能定义 PRIMARY KEY 约束。

FOREIGN KEY 约束也可引用 UNIQUE 约束。

5.3 重建索引

DBCC DBREINDEX

重建指定数据库中表的一个或多个索引。

6 链接服务器

6.1 在DB端直接访问异构服务器

6.1.1 Oracle

6.1.2 Informix

6.2 支持常规DML语言

6.2.1 SELECT

6.2.2 INSERT

6.2.3 UPDATE

6.2.4 DELETE

7 DTS

7.1 不包含复杂业务逻辑的数据转换

7.2 支持多种异构DB

8 总结

8.1 优势

8.1.1 隔离代码,业务更改无需编译

8.1.2 性能高,大大减轻网络负载

8.1.3 两个集合间的匹配/排序/分组汇总

在处理循环的时候效率高于AP

8.2 缺陷

8.2.1 移植到异构DB基本上要重新开发

8.2.2 调试不方便,增加开发时间

8.2.3 版本控制不方便,不能与SourceSafe集成

8.2.4 控管很不方便(不存在工程/文件夹/ 模组/类这样一些分类方式)

无法按工程分类,无法按种类分类

8.2.5 多Server需要分别同步

9 其他

9.1 SQL Mail

9.2 SQL Job

9.3 触发器