| 1、数据库设计
数据库设计不可能一蹴而就，应该是反复探寻，逐步求精的过程。
1.1、基本步骤
- 需求分析
- 定义实体
- 定义联系
- 定义键基视图（？？？没看懂）
- 定义属性
- 定义其他规则和约束
1.2、命名规范
1.2.1、表
- 表名一般用名词或名词短语。
- 表名不应出现空格，多个单词间用"_"分割。
1.2.2、列
- 尽量采用unicode数据存储格式，用nvarchar代替varchar
- 特殊的数据结构可考虑使用xml数据类型
- 尽量用默认值代替null值
| 2、设计范式
不规范的设计范式通常有以下缺点：
- 数据冗余
- 数据一致性差
- 数据修改复杂
- 增删容易出现异常
零状态数据
user
| name | company | url1 | url2 |
|---|---|---|---|
| joe | ABC | abc.com | xyz.com |
| jill | XYZ | abc.com | xyz.com |
2.1、第一范式
所有的属性值都是单纯的域。
- 消除表中的重复列。
- 为每套相关的数据建立独立的表格。
- 使用一个主键来标识每套数据。
显然零状态的表没有扩展性，当需要新增url的时候，需要新增列。因此拆分后的表如下：
user
| id | name | company | url |
|---|---|---|---|
| 1 | joe | ABC | abc.com |
| 2 | joe | ABC | xyz.com |
| 3 | jill | XYZ | abc.com |
| 4 | jill | XYZ | xyz.com |
2.2、第二范式
属性绝大部分都依赖于主键
- 重复记录的字段应该建立独立的表
- 通过外键关联
user
| id | name | company |
|---|---|---|
| 1 | joe | ABC |
| 2 | jill | XYZ |
url
| id | user_id | url |
|---|---|---|
| 1 | 1 | abc.com |
| 2 | 1 | xyz.com |
| 3 | 2 | abc.com |
| 4 | 2 | xyz.com |
2.3、第三范式
消除不依赖于主键的字段。
在user表中，company应该是不依赖于user，可独立扩展的。
user
| id | name | company_id |
|---|---|---|
| 1 | joe | 1 |
| 2 | jill | 2 |
url
| id | user_id | url |
|---|---|---|
| 1 | 1 | abc.com |
| 2 | 1 | xyz.com |
| 3 | 2 | abc.com |
| 4 | 2 | xyz.com |
company
| id | company |
|---|---|
| 1 | ABC |
| 2 | XYZ |
2.4、第四范式
在多对多的关系中，独立的实体不能放在同一个表格里。应该将实体独立出来，关系移动独立的表格中去。
user
| id | name | company_id |
|---|---|---|
| 1 | joe | 1 |
| 2 | jill | 2 |
url
| id | url |
|---|---|
| 1 | abc.com |
| 2 | xyz.com |
company
| id | company |
|---|---|
| 1 | ABC |
| 2 | XYZ |
url_relation
| id | url_id | user_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 1 | 2 |
| 4 | 2 | 2 |
2.5、反规范化
规范化的深度越深，分离就越深。但在查询的时候，连接的表就越多。而连接操作是非常费时的，特别在以查询为主的数据库应用中。大量频繁的连接会大大影响性能，有时候也需要一些反规范化来提高性能。
- 增加冗余列
- 增加派生列（派生列的数据来源于其他表，这样就能减少连接，避免使用集函数）
- 分割表。
- 把常用的记录放到一张表中，把不常用的记录放到另一张表中。需要查询所有记录的时候，在用union去连接。（大数据量的时候，可以分库分表）
- 把常用的列放到一张表，把不常用的列放到另一张表中。需要查询所有记录的时候，在用join去连接
| 3、设计原则
3.1、索引
合理的索引能大幅度提升查询效率，尤其是当表数据量很大的时候。索引建立的原则：
- 挑选重复值较少的字段作为索引
- 经常使用的where子句的条件，可以作为索引
- 索引不可多建
- 大型字段不要作为索引
- 小型表不要建索引，插入删除索引的开销大于全表扫描的的开销
3.2、字段原则
- Text和Image主要用于存储二进制文件，尽量少用。
- 当实体关系出现多对多的时候，尽量在两者之间建立第三个实体。形成2个一对多的关系，这样便于管理和实现。
- 提倡高级冗余（派生），反对低级冗余（重复）。
- 增加删除标记字段，而不是使用物理删除的方式，直接删除记录。
3.3、表设计原则
One Fact in One Place
- 每个表只包括其本身的基本属性。
- 不是其本身的属性，应该进行分解。
- 表之间通过外键相连。
3.4、键设计原则
- 为关联的字段创建外键
- 键必须唯一
- 使用系统生成的主键，不可让主键具有可更新性
3.5、完整性设计原则
- 使用规则和约束来防止非法数据进入
- 约束的开销要小于规则
- 不要依赖于业务层保证数据完整性，数据库自身也应该能做到
- 通过外键实现机制来约束规则
- 级联删除、级联更新
- 采用视图的方式，避免不必要的直接访问数据表
3.6、三少原则
- 表尽量少。去掉多余、重复的实体，对客观世界进行高度抽象。防止打补丁式设计。
- 组合主键的个数越少越好
- 表的字段要越少越好。要学会列变行，防止将子表中的数据拉入主表。在主表中留下大量的空余字段。
全局综合数据库 > 主题数据库 > 应用数据库
集成的程度越高，数据共享性就越强，信息孤岛的数量就会越少。防止打补丁式的对数据库修改，使数据库变成随意设计表的垃圾堆或数据表的大杂院。
3.7、触发器
触发器可以实现主外键不能保证的完整性和一致性，功能很强大。但触发器性能较低，会大量的消耗服务器资源，因此要慎用。
| 4、SQL语句优化
- 避免出现 SELECT * FROM table，要明确字段
- 查询的条件尽可能用索引覆盖
- 避免在 order by语句中，使用表达式
- 关联查询的时候，关联的表一般不要超过7个
- 避免在where子句中，使用or in来作为条件。in集合的数量不超过200个。
select id from t where num = 10 or num = 20
select id from t where num = 10
union
select id from t where num = 20
- 避免在where子句中使用 != <>等操作符。
- 对于能使用连接或者视图方式实现的功能，不要使用子查询。
- 在where子句中，避免对列进行任何表达式的计算
- 不要对过多的列使用 group by 或者 order by
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
