数据库相关基础知识总结

一、数据库三大范式和数据库中的约束

　　范式， 英文名称是 Normal Form，它是英国人 E.F.Codd（关系数据库的老祖宗）在上个世纪70年代提出关系数据库模型后总结出来的，范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。

　　设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

　　目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

　　范式越高，冗余最低，一般到三范式，再往上，表越多，可能导致查询效率下降。所以有时为了提高运行效率，可以让数据冗余(反三范式，一般某个数据经常被访问时，比如数据表里存放了语文数学英语成绩，但是如果在某个时间经常要得到它的总分，每次都要进行计算会降低性能，可以加上总分这个冗余字段)。

　　后面的范式是在满足前面范式的基础上，比如满足第二范式的一定满足第一范式。

第一范式（1NF）：确保每一列的原子性

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

　　例如： userInfo: '山东省烟台市 1318162008' 依照第一范式必须拆分成
                   userInfo: '山东省烟台市'　　 userTel: '1318162008'两个字段

第二范式（2NF）：非键字段必须依赖于键字段

　　满足1NF后要求表中的所有列，都必需依赖于主键，而不能有任何一列与主键没有关系（一个表只描述一件事情）。

　　第二范式要求每个表只描述一件事。

　　例如：订单表只能描述订单相关的信息，所以所有的字段都必须与订单ID相关。
                  产品表只能描述产品相关的信息，所以所有的字段都必须与产品ID相关。
      因此在同一张表中不能同时出现订单信息与产品信息。

第三范式（3NF）：满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）

　　任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

　　例如：订单表中需要有客户相关信息，在分离出客户表之后，订单表中只需要有一个用户ID即可，而不能有其他的客户信息，因为其他的客户信息是直接关联于用户ID，而不是关联于订单ID，需要单分出来一个表描述用户ID和客户信息之间的关系。

　　再例如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

　　这样一个表结构，就存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话)。这是一个传递关系（院校地址与院校电话与主键间接相关）。

　　这样的表结构，我们应该拆开来，如下。（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）.

 

　　三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

 

数据库中的五大约束包括：

1.主键约束（Primay Key Coustraint） 唯一性，非空性；

2.唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个；

3.默认约束 (Default Counstraint) 该数据的默认值；

4.外键约束 (Foreign Key Counstraint) 需要建立两表间的关系；

5.非空约束（Not Null Counstraint）:设置非空约束，该字段不能为空。

二、事务

　　所谓事务（Transcation），它是一个操作序列，这些操作要么全部执行，要么不执行，它是个不可分割的工作单位。事务的四大特性：

1 ACID四大特性

Atomicity（原子性）

　　原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

Consistency（一致性）

　　一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

Isolation（隔离性）

　　多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。

　　这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

Durability（持久性）

　　持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

 

2 事务的并发问题（4类）

一个庞大的业务系统，一般会有多个线程同时访问数据库，容易产生如下4种并发问题：

丢失修改：

　　两个事务T1，T2读入同一数据并修改，T2提交的结果被T1破坏了，导致T1的修改丢失。（订票系统）

脏读：

　　事务T1修改某个数据并写回磁盘，事务T2读取同一数据，但T1由于某种原因撤销了，这时T1修改过的数据恢复原来的值，T2读取的数据就与数据库中的数据不一致。

不可重复读：

　　不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；

幻读：

　　幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。

 

3 事务的隔离等级（4个）

　　为了避免上面出现几种情况在标准SQL规范中定义了4个事务隔离级别，不同隔离级别对事务处理不同 。Mysql的InnoDB引擎都支持。Mysql的默认隔离级别是可重复读。

未提交读（Read Uncommitted）

　　最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没 commit 的数据，这样可能会提高性能，但是会导致脏读问题；

提交读（Read Committed）

　　在一个事务中只允许对其它事务已经 commit 的记录可见，该隔离级别不能避免不可重复读问题；

可重复读（Repeatable Read）

　　在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务 commit 或 rollback。但是，其他事务的 insert/delete 操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复 select 的结果一样，除非本事务中 update 数据库。

可序列化(Serializable)

　　最高级别的隔离，只允许事务串行执行。

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：

• • MyISAM：不支持事务，用于只读程序提高性能；
  • InnoDB：支持ACID事务、行级锁、并发；
  • Berkeley DB：支持事务。

 

4 数据库的锁（4个）

　　锁是实现并发控制的一个非常重要的技术，所谓锁就是事务T在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁。常见的锁包括以下4种：

排他锁（写锁）：

　　若事务T对数据对象A加写锁，则只允许T读取和修改A，其他事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。

共享锁（读锁）：

　　若事务T对数据对象A加读锁，则只允许T可以读取但不能修改A，其他事务只能再对A加读锁，而不能加写锁，直到T释放A上的读锁为止。

悲观锁：

　　悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进行业务操作。通常来讲，在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select … for update时会获取被select中的数据行的行锁，因此其他并发执行的select … for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

　　MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

乐观锁：

　　乐观锁的特点先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过，若未被更新过，则更新成功；否则，失败重试。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号或者时间戳，然后按照如下方式实现：

查询出商品信息
select (status,status,version) from t_goods where id=#{id}
根据商品信息生成订单
修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

　　一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

5 小结　　

　　事务的ACID是有关系数据库管理系统（RDBMS）来实现的，RDBMS采用日志来保证事务的原子性（A)、一致性（C）、持久性（D）。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。
　　RDBMS通过采用锁机制来实现事务的隔离性（I）。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

 

三、索引

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。

你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

索引的优点

• • 索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页，而不是遍历所有数据页。
  • 对于非聚集索引，有些查询甚至可以不访问数据页。
  • 聚集索引可以避免数据插入操作集中于表的最后一个数据页。
  • 一些情况下，索引还可用于避免排序操作。
  • 加速查询（检索）数据表中的数据。

索引的缺点

• • • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；
    • 空间方面：索引需要占物理空间。

索引的分类：索引分为聚集索引和非聚集索引两类；InnoDB 主键使用的是聚集索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚集索引。

聚集索引

　　表中各行的物理顺序与键值的逻辑（索引）顺序相同。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。在聚集索引中，叶结点也即数据结点，所有数据行的存储顺序与索引的存储顺序一致。

　　在一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引，那么它被称为“堆集”（Heap）。这样的表中的数据行没有特定的顺序，所有的新行将被添加的表的末尾位置。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引

　　表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。

　　非聚集索引与聚集索引相比：

• • • 叶子结点并非数据结点
    • 叶子结点为每一真正的数据行存储一个“键-指针”对
    • 叶子结点中还存储了一个指针偏移量，根据页指针及指针偏移量可以定位到具体的数据行。
    • 类似的，在除叶结点外的其它索引结点，存储的也是类似的内容，只不过它是指向下一级的索引页的。
    • 表数据存储顺序与索引顺序无关。而聚集索引表中各行的物理顺序与键值的逻辑（索引）顺序相同
    • 每张表可以有多个非聚集索引，而聚集索引只能有一个。

当你需要取出一定范围内的数据时用聚簇索引也比用非聚簇索引好。

当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。

索引的实现原理

　　索引的底层数据结构是B+树。在数据结构中，我们最为常见的搜索结构就是二叉搜索树和AVL树(高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度)了。然而，无论二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉树结构成为不二选择。

　　为什么用B+树不用B树：B+树在非叶子结点不保存数据，只在叶子结点保存。而B树在叶子结点和非叶子结点都会保存。这种结构导致你如果用B树来进行查询，会增加磁盘IO的次数，导致性能不如B+树。

什么样的字段适合做索引　

• 经常作查询选择的字段
• 经常作表连接的字段
• 经常出现在order by, group by, distinct 后面的字段
• 离散程度高、占用空间小

索引失效

索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：

1. 1. 如果条件中有or，即使其中有条件带索引也不会使用。 >要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

   2. 对于多列索引，不是使用的第一部分，则不会使用索引。

   3. like查询是以%开头。

   4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。

   5. 如果 mysql 估计使用全表扫描要比使用索引快，则不使用索引。例如，使用<>、not in 、notexist，对于这三种情况大多数情况下认为结果集很大，MySQL就有可能不使用索引。

Mysql中如何添加索引

mysql中常见的索引包括主键索引（PRIMARAY KEY）、唯一索引（UNIQUE KEY）以及普通索引（INDEX）

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

 

四、Mysql存储引擎

　　在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，其由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然MyISAM性能极佳，但却有一个显著的缺点： 不支持事务处理。不过，MySQL也导入了另一种数据库引擎InnoDB，以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

　　InnoDB是MySQL的数据库引擎之一，其由Innobase oy公司所开发，2006年五月由甲骨文公司并购。与传统的ISAM、MyISAM相比，InnoDB的最大特色就是支持ACID兼容的事务功能，类似于PostgreSQL。目前InnoDB采用双轨制授权，一是GPL授权，另一是专有软件授权。具体地，MyISAM与InnoDB作为MySQL的两大存储引擎的差异主要包括：

事务支持：MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。 

　　表锁差异：MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

　　外键：MyISAM不支持外键，而InnoDB支持外键。

　　不管是MyISAM还是InnoDB，索引使用的底层数据结构都是B+树。

　　MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

　　Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引被称为聚集索引。

 

四、数据库常用操作　　

　　在数据库原理中，关系运算包含 选择、投影、连接 这三种运算。相应的在SQL语句中也有表现，其中Where子句作为选择运算，Select子句作为投影运算，From子句作为连接运算。

　　连接运算是从两个关系的笛卡尔积中选择属性间满足一定条件的元组，在连接中最常用的是等值连接和自然连接。

• • 等值连接：关系R、S,取两者笛卡尔积中属性值相等的元组，不要求属性相同。比如 R.A=S.B
  • 自然连接（内连接）：是一种特殊的等值连接，它要求比较的属性列必须是相同的属性组，并且把结果中重复属性去掉。

Join的常见用法

• • INNER JOIN（内连接，或等值连接）：取得两个表中存在连接匹配关系的记录。
  • LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
  • RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。
  • CROSS JOIN（交叉连接）：得到的结果是两个表的乘积，即笛卡尔积。在mysql中inner join其实和cross join是一样的，在不指定on的时候都笛卡尔积。

UNION常见用法

union ：用于合并多个 select 语句的结果集，并去掉重复的值。 union all ：作用和 union 类似，但不会去掉重复的值。

Join、Union、group by、order by、select、insert、update、delete等操作就不在此过多介绍了，详细查看：

https://www.cnblogs.com/BeginMan/p/3754322.html

https://github.com/hadyang/interview/blob/master/basic/database/join.md

 

 

参考链接

https://github.com/hadyang/interview/tree/master/basic/database

https://www.cnblogs.com/wmyskxz/p/9067197.html