关系型数据库设计与优化手段总结

　数据库的设计是大多数人都会面临的问题，进行必要的优化则会使性能得到可靠的提升。日常工作中，可能没有意识已经做了某些优化，但是要将哪些手段都具体列出来、说清楚，可能一时间会不知所措，其实还是理论不够夯实。前两天回顾了一下之前的工作，查阅相关理论资料，发现数据库理论和经验仍待提升，所以打算从设计和优化两方面给自己做个总结，回顾学习。

范式

第一范式（1NF）

第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

要求数据表的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。

即同一列（字段）中不能有多个值。表的每行只包含一个实例的信息（实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系）。

简而言之，第一范式就是无重复的列。

第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

要求数据表的每个实例或行必须可以被惟一区分。为实现区分通常需要为表加一列，以存储各个实例的惟一标识（这个惟一属性列被称为主关键字或主键、主码）。

即满足1NF后，要求表的所有列或实体的所有属性完全依赖于主键，而不能有任何一列与主键没有关系（一个表只描述一件事情，所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系）。

简而言之，第二范式就是需要主键，非主属性完全依赖主键。

第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。

要求表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。

简而言之，第三范式就是一个数据表不要包含已存在其它表中的非主关键字信息。

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（简单理解为消除冗余）

反范式

这里的反范式主要指与第三范式相反，即在一些情况下，通过适当增加冗余，以达到优化查询 SQL 的目的。

约束

主键约束（Primay Key Coustraint）

约束：唯一性，非空性，表只能有一列（表约束）

注意事项：

主键默认非空，默认唯一性约束，只有主键可以设置自动增长（主键不一定自增，自增一定是主键）
添加主键约束会自动创建唯一索引。
如果表中尚未创建聚焦索引，则自动创建聚焦唯一索引。如果表中已存在聚焦索引，则自动创建非聚焦索引。

设置方法：

在定义列时设置：id INT UNSIGNED PRIMARY KEY。
在列定义完成后设置：PRIMARY KEY(id)。

唯一约束（Unique Counstraint）

约束：唯一性，可以空, 主键列不能再加此约束，可以多列

注意事项：

添加唯一约束会自动创建唯一索引。
如果未在unique关键字后加上［nonclustered｜clustered］, 则默认会创建非聚焦索引。

外键约束 (Foreign Key Counstraint)

约束：需要建立两表间的关系并引用主表的列（表约束）

注意事项：

只有INNODB的数据库引擎支持外键，修改my.ini文件设置default-storage-engine=INNODB
外键与参照列的数据类型必须相同。（数值型要求长度和无符号都相同，字符串要求类型相同，长度可以不同）
设置外键的字段必须要有索引，如果没有索引，设置外键时会自动生成一个索引。

设置方法：

[CONSTRAINT 外键名] FOREIGN KEY(外键字段) REFERENCES 参照表(参照字段) [ON 删改操作参照操作]

参照操作：

RESTRICT: 拒绝对参照字段的删除或修改（默认）；
NO ACTION: 与RESTRICT相同，但这个指令只在MySql生效；
CASCADE: 删除或更新参照表的参照字段时，外键表的记录同步删除或更新；
SET NULL: 删除删除或更新参照表的参照字段时，外键表的外键设为NULL (此时外键不能设置为NOT NULL)。

检查约束 (Check Counstraint)

约束：对该列数据的范围、格式的限制，可以多列（如：年龄、性别等）

默认约束 (Default Counstraint)

约束：该数据的默认值，可以多列

索引

索引是什么？

索引是一个排序的数据结构，在数据库管理系统中，用以协助快速查询、更新数据库表中数据，比如用于快速找出在某个列中有一特定值的行，相当于书（表）的目录。

对比不使用索引：MySQL必须从第1条记录开始，然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。
索引实现：通常使用B树及其变种B+树实现索引。大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

索引类型有哪些?

数据库功能上：

唯一索引

不允许其中任何两行具有相同索引值的索引。（UNIQUE）
主键索引

是唯一索引的特定类型，表主键自动创建该索引，查询中使用主键索引可以允许对数据的快速访问。（PRIMARY KEY）
聚集索引

表中行的物理顺序与键值的逻辑（索引）顺序相同，一个表只能包含一个聚集索引。（Clustered Indexes）
- 优缺点：如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。
- 举例：我们如果在“姓名”这一字段上建立了聚集索引，则表中的记录将按照姓名进行排列；如果建立了聚集索引的列是数值类型的，那么记录将按照该键值的数值大小来进行排列。非聚集索引用于指定数据的逻辑顺序，也就是说，表中的数据并没有按照索引键值指定的顺序排列，而仍然按照插入记录时的顺序存放。

逻辑上：

Single column 单列索引
Concatenated 多列索引
Unique 唯一索引
NonUnique 非唯一索引
Function-based 函数索引
Domain 域索引

物理上：

Partitioned 分区索引
NonPartitioned 非分区索引

B-tree：

Normal 正常型B树
Rever Key 反转型B树 Bitmap 位图索引

一次查询能用多个索引吗?

不能

什么样的字段适合建索引？

唯一、不为空、经常被查询的字段

建立索引好处

创建索引可以大大提高系统的性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

建立索引有哪些缺点？

索引占物理空间，如果建立聚集索引，需要的空间就会更大。（系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引）
插入、修改、删除表数据时，索引也要创建和维护，花费时间更多(更新数据时，系统必须要有额外时间来同时更新索引，以维持数据和索引的一致性，额外时间与数据量呈正相关)。

不适合索引场景

查询中很少使用或者参考的列不应该创建索引。

原因：因为很少使用，所以有无索引并不能提高查询速度。相反增加索引可能会降低系统的维护速度和增大空间需求。

对于那些只有很少数据值的列也不应该增加索引。

原因：由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

对于那些定义为text, image和bit数据类型的列不应该增加索引。

原因：这些列的数据量要么相当大，要么取值很少。

当修改性能远远大于检索性能时，不应该创建索引。

原因：修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

聚集索引或非聚集索引比较：

相同点

聚集索引和非聚集索引都采用了B+树的结构。

不同点

聚集索引会使数据访问速度更快，适用于少增删改操作，多查询的情况。
- 适合多查询原因：聚集索引表记录的排列顺序与索引的排列顺序一致，一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后，聚合索引存储记录是物理上连续存在的。
- 不适用多增删改操作原因：为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。
非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致。
- 非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。
- 非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。

如何选择聚集索引或非聚集索引

动作描述	使用聚集索引	使用非聚集索引
一个或极少不同值	不应	不应
主键列	应	应
外键列	应	应
经常被分组排序的列	应	应
返回某范围内数据	应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁被更新的列	不应	应
频繁修改索引的列	不应	应

人工重建索引

为何需要重建?

随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大量I/O的时候，重建非聚聚集索引可以维护I/O的效率。

重建索引实质上是重新组织B树。

有哪些需要重建索引的情况？

数据和使用模式大幅度变化。
排序的顺序发生改变。
要进行大量插入操作或已经完成。
使用I/O查询的磁盘读次数比预料的要多。
由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。
dbcc检查出索引有问题。

建立索引的思路

主键时常作为where子句的条件，应在表的主键列上建立聚聚集索引，尤其当经常用它作为连接的时候。
有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚聚集索引。
经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。
如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。　
在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。
在选择索引字段时，尽量选择那些小数据类型的字段作为索引键，以使每个索引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。

存储过程

什么是存储过程？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

如何调用？

可以用一个命令对象来调用存储过程。
可以供外部程序调用，比如：java程序。

存储过程的优缺点？

存储过程的优点
- 存储过程是预编译过的，执行效率高。
- 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
- 安全性高，执行存储过程需要有一定权限的用户。
- 存储过程可以重复使用，可减少数据库开发人员的工作量。
存储过程的缺点：移植性差

存储过程与函数的区别

区别方面	存储过程	函数
用途	用于在数据库中完成特定的操作或者任务（如插入、删除等）	用于特定的数据（如选择）
声明	程序头部声明用procedure	程序头部声明用function
返回类型	程序头部声明时不需描述返回类型	程序头部声明时要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句
返回	可以通过out/in out 返回零个或多个值	通过return语句返回一个值，且改值要与声明部分一致，也可以是通过out类型的参数带出的变量
能否独立执行	可作为一个独立的PL/SQL语句来执行	不能独立执行，必须作为表达式的一部分调用
能否在SQL语句(DML 或SELECT)中调用	不可调用	可以调用
能否使用in/out/in out三种模式的参数	可以使用	可以使用

视图

什么是视图？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。

视图与表的关系

视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。表就是关系数据库中实际存储数据用的。

什么是游标？

游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

视图的优缺点

视图的优点

可以定制用户数据，聚焦特定的数据。
用户通过简单的查询可以从复杂查询中得到结果，简化数据操作。
维护数据的独立性，试图可从多个表检索数据。
对于相同的数据可产生不同的视图。

视图的缺点

性能差：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据。
修改限制：当用户试图修改试图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的。

事务与锁

什么是事务？

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。

数据库事务transanction正确执行的四个基本要素。ACID

原子性(Atomicity)
一致性(Correspondence)
隔离性(Isolation)
持久性(Durability)

什么是锁？

锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

查询语句优化

查询尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

尽量避免如下 SQL 语句，会造成引擎放弃使用索引而进行全表扫描。

避免在 where 子句中使用 != 或 <> 操作符。

避免在 where 子句中对字段进行 null 值判断。

--错误示例： 

select id from t where num is null 

--解决方案：使用默认约束，设置num默认值0，确保表中num列没有null值，然后这样查询。

select id from t where num=0

避免在 where 子句中使用 or 来连接条件。

--错误示例：

select id from t where num=10 or num=20

--解决方案：

select id from t where num=10
union all
select id from t where num=20

避免模糊搜索 like 以 % 开头。

  --错误示例：
  
  select id from t where name like '%abc%' 
  
  --解决方案：对于 like '..%' (不以 % 开头)，可以应用 colunm 上的 index
  ``` 

- 避免对于连续的数值范围使用 in 和 not in。

  ``` 
  --错误示例：
  
  select id from t where num in(1,2,3)
  
  --解决方案：对于连续的数值，能用 between 就不要用 in 了
  
  select id from t where num between 1 and 3

避免在 where 子句中使用参数。

因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时, 它必须在编译时进行选择。
然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
1
2
3
4
5
6
7
--错误示例：

select id from t where num=@num
　　
--解决方案：可以改为强制查询使用索引

select id from t with(index(索引名)) where num=@num

避免在 where 子句中对字段进行表达式操作

--错误示例：

select id from t where num/2=100

--解决方案：

select id from t where num=100*2

避免在where子句中对字段进行函数操作

--错误示例：

select id from t where substring(name,1,3)='abc'; --name以abc开头的id
select id from t where datediff(day,createdate, '2005-11-30')=0; --'2005-11-30'生成的id 

--解决方案：

select id from t where name like 'abc%';
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1';

用 exists 代替 in

--错误示例：

select num from a where num in(select num from b)

--解决方案：

select num from a where exists(select 1 from b where num=a.num)

不要在 where 子句中的“=”左边，进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
在使用索引字段作为条件时，如果该索引是【复合索引】，那么必须使用到该索引中的【第一个字段】作为条件时才能保证系统使用该索引，否则该索引将不会被使用。并且应尽可能的让字段顺序与索引顺序相一致。（字段顺序也可以不与索引顺序一致，但是一定要包含【第一个字段】）
任何地方都不要使用 select * from t ，用具体的字段列表代替*，不要返回用不到的任何字段。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
尽量避免大事务操作，提高系统并发能力。

范式

第一范式（1NF）

第二范式（2NF）

第三范式（3NF）

反范式

约束

主键约束（Primay Key Coustraint）

唯一约束 （Unique Counstraint）

外键约束 (Foreign Key Counstraint)

检查约束 (Check Counstraint)

默认约束 (Default Counstraint)

索引

索引是什么？

索引类型有哪些?

数据库功能上：

逻辑上：

物理上：

B-tree：

一次查询能用多个索引吗?

什么样的字段适合建索引？

建立索引好处

建立索引有哪些缺点？

不适合索引场景

查询中很少使用或者参考的列不应该创建索引。

对于那些只有很少数据值的列也不应该增加索引。

对于那些定义为text, image和bit数据类型的列不应该增加索引。

当修改性能远远大于检索性能时，不应该创建索引。

聚集索引或非聚集索引比较：

相同点

不同点

如何选择聚集索引或非聚集索引

人工重建索引

为何需要重建?

有哪些需要重建索引的情况？

建立索引的思路

存储过程

什么是存储过程？

如何调用？

存储过程的优缺点？

存储过程与函数的区别

视图

什么是视图？

视图与表的关系

什么是游标？

视图的优缺点

视图的优点

视图的缺点

事务与锁

什么是事务？

数据库事务transanction正确执行的四个基本要素。ACID

什么是锁？

查询语句优化

唯一约束（Unique Counstraint）