数据库
1 数据库三范式
1）列都是不可再分
第一范式的目标是确保每列的原子性:如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）。
2）每个表只描述一件事情
首先满足第一范式，并且表中非主键列不存在对主键的部分依赖，要求都是完全依赖。 第二范式要求每个表只描述一件事情。下图中，（订单编号，产品编号）----->价格，是部分依赖。
3）不存在对非主键列的传递依赖
第三范式定义是，满足第二范式，并且表中的列不存在对非主键列的传递依赖。如下图，除了主键订单编号外，顾客姓名依赖于非主键顾客编号。
2 数据库事务
事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 。事务是一个不可分割的工作逻辑单元。
事务必须具备以下四个属性，简称ACID 属性：
1）原子性（Atomicity）
事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
2）一致性（Consistency）
当事务完成时，数据必须处于一致状态。
3）隔离性（Isolation）
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
4）永久性（Durability）
事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。
3 存储过程（暂时不懂）
一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
存储过程优化思路：
- 尽量利用一些sql语句来替代一些小循环，例如聚合函数，求平均函数等。
- 中间结果存放于临时表，加索引。
- 少使用游标。sql是个集合语言，对于集合运算具有较高性能。而cursors是过程运算。比如对一个100万行的数据进行查询。游标需要读表100万次，而不使用游标则只需要少量几次读取。
- 事务越短越好。sqlserver支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁。导致查询极慢，cpu占用率极地。
- 使用try-catch处理错误异常。
- 查找语句尽量不要放在循环内。
4 触发器（一段能自动执行的程序）不懂
触发器是一段能自动执行的程序，是一种特殊的存储过程，触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作时触发。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。
5 数据库并发策略
并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳。
1）乐观锁
乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁就刚好相反，觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间戳就是不加锁，通过时间戳来控制并发出现的问题。
2）悲观锁
悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允许其他用户访问那部分数据。
3）时间戳
时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字段也读出来，当写回去的时候，把该字段加1，提交之前 ，跟数据库的该字段比较一次，如果比数据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁机制，但是这种方法可以大大提高数据库处理的并发量，
以上悲观锁所说的加“锁”，其实分为几种锁，分别是：排它锁（写锁）和共享锁（读锁）。
6 数据库锁
1）行级锁
行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时，Oracle会自动应用行级锁：
- INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
- SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新
- 使用COMMIT或ROLLBACK语句释放锁。
2）表级锁
表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
3）页级锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁。
7 数据访问冲突
1）脏读
脏读：事务A查询数据后进行了一次修改且未提交，而事务B这个时候去查询，然后使用了这个数据，因为这个数据还没有被事务A 提交到数据库中，所以事务B的得到数据就是脏数据，对脏数据进行操作可能是不正确的。
2）不可重复读
不可重复读: 事务A访问了两次数据，但是这访问第二次之间事务B进行一次并进行了修改，导致事务A访问第二次的时候得到的数据与第一次不同，导致一个事务访问两次数据得到的数据不相同。因此叫做不可重复读。
3）幻读
幻读： 与不可重复读都点相似，只是这次是事务B在事务A访问第二次的之前做了一个新增，导致事务A第二次读取的时候发现了多的记录，这就是幻读。
4）丢失修改
丢失修改：事务A访问该数据，事务B也访问该数据，事务A修改了该数据，事务B也修改了该数据，这样导致事务A的修改被丢失，因此称为丢失修改；
注：不可重复度和幻读区别：
不可重复读主要是修改操作，幻读的主要在于新增或者删除。
幻读主要在于数据的条数变了，而不可重复读主要在于数据内容变了。
8 数据库隔离级别
1）Read uncommitted
会出现脏读，不可重复读，幻读
2）Read committed
会出现不可重复读，幻读
3）Repeatable read
会出现幻读(但在Mysql实现的Repeatable read配合gap锁不会出现幻读！)
4）Serializable
串行，避免以上的情况！
9 分区分表
分库分表有垂直切分和水平切分两种。
1）垂直切分(按照功能模块)
将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数据库workDB、商品数据库payDB、用户数据库userDB、日志数据库logDB等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表等。
2）水平切分（按照规则划分存储）
当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，例如userID散列，进行划分，然后存储到多个结构相同的表，和不同的库上。
10 两阶段提交协议
分布式事务是指会涉及到操作多个数据库的事务,在分布式系统中，各个节点之间在物理上相互独立，通过网络进行沟通和协调。
二阶段提交(Two-phaseCommit)是指，在计算机网络以及数据库领域内，为了使基于分布式系统架构下的所有节点在进行事务提交时保持一致性而设计的一种算法(Algorithm)。通常，二阶段提交也被称为是一种协议(Protocol))。在分布式系统中，每个节点虽然可以知晓自己的操作时成功或者失败，却无法知道其他节点的操作的成功或失败。当一个事务跨越多个节点时，为了保持事务的ACID特性，需要引入一个作为协调者的组件来统一掌控所有节点(称作参与者)的操作结果并最终指示这些节点是否要把操作结果进行真正的提交(比如将更新后的数据写入磁盘等等)。因此，二阶段提交的算法思路可以概括为：参与者将操作成败通知协调者，再由协调者根据所有参与者的反馈情报决定各参与者是否要提交操作还是中止操作。
1）准备阶段
事务协调者(事务管理器)给每个参与者(资源管理器)发送Prepare消息，每个参与者要么直接返回失败(如权限验证失败)，要么在本地执行事务，写本地的redo和undo日志，但不提交，到达一种“万事俱备，只欠东风”的状态。
2）提交阶段
如果协调者收到了参与者的失败消息或者超时，直接给每个参与者发送回滚(Rollback)消息；否则，发送提交(Commit)消息；参与者根据协调者的指令执行提交或者回滚操作，释放所有事务处理过程中使用的锁资源。(注意:必须在最后阶段释放锁资源)。
注：
缺点：
同步阻塞问题
1、 执行过程中，所有参与节点都是事务阻塞型的。
单点故障
2、 由于协调者的重要性，一旦协调者发生故障。参与者会一直阻塞下去。
数据不一致（脑裂问题）
3、 在二阶段提交的阶段二中，当协调者向参与者发送commit请求之后，发生了局部网络异常或者在发送commit请求过程中协调者发生了故障，导致只有一部分参与者接受到了commit请求。于是整个分布式系统便出现了数据部一致性的现象(脑裂现象)。
二阶段无法解决的问题（数据状态不确定）
4、 协调者再发出commit消息之后宕机，而唯一接收到这条消息的参与者同时也宕机了。那么即使协调者通过选举协议产生了新的协调者，这条事务的状态也是不确定的，没人知道事务是否被已经提交。
11 三阶段提交事务
三阶段提交（Three-phase commit），也叫三阶段提交协议（Three-phase commit protocol），是二阶段提交（2PC）的改进版本。
与两阶段提交不同的是，三阶段提交有两个改动点。
1、引入超时机制。同时在协调者和参与者中都引入超时机制。
2、在第一阶段和第二阶段中插入一个准备阶段。保证了在最后提交阶段之前各参与节点的状态是一致的。也就是说，除了引入超时机制之外，3PC把2PC的准备阶段再次一分为二，这样三阶段提交就有CanCommit、PreCommit、DoCommit三个阶段。
1）CanCommit阶段
协调者向参与者发送commit请求，参与者如果可以提交就返回Yes响应，否则返回No响应。
2）PreCommit阶段
协调者根据参与者的反应情况来决定是否可以继续进行，有以下两种可能。假如协调者从所有的参与者获得的反馈都是Yes响应，那么就会执行事务的预执行假如有任何一个参与者向协调者发送了No响应，或者等待超时之后，协调者都没有接到参与者的响应，那么就执行事务的中断。
3）doCommit阶段
该阶段进行真正的事务提交，主要包含1.协调者发送提交请求 2.参与者提交事务 3.参与者响应反馈（ 事务提交完之后，向协调者发送Ack响应。）4.协调者确定完成事务。
12 柔性事务（之后再写）
13 CAP
CAP原则又称CAP定理，指的是在一个分布式系统中， Consistency（一致性）、 Availability（可用性）、Partition tolerance（分区容错性），三者不可得兼。
一致性（C）：
在分布式系统中的所有数据备份，在同一时刻是否同样的值。（等同于所有节点访问同一份最新的数据副本）
可用性（A）：
在集群中一部分节点故障后，集群整体是否还能响应客户端的读写请求。（对数据更新具备高可用性）
分区容错性（P）：
以实际效果而言，分区相当于对通信的时限要求。系统如果不能在时限内达成数据一致性，就意味着发生了分区的情况，必须就当前操作在C和A之间做出选择。
14 索引
1) mySql为啥用B+树
索引这个词，相信大多数人已经相当熟悉了，很多人都知道MySQL的索引主要以B+树为主，但是要问到为什么用B+树？
索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。 索引最形象的比喻就是图书的目录了。注意这里的大量，数据量大了索引才显得有意义，如果我想要在[1,2,3,4]中找到4这个数据，直接对全数据检索也很快，没有必要费力气建索引再去查找。索引在mysql数据库中分三类：
B+树索引、Hash索引、全文索引
由于作者太菜了，先只写B+树索引吧。
要介绍B+树索引，就不得不提二叉查找树，平衡二叉树和B树这三种数据结构。B+树就是从他们仨演化来的。
1）二叉查找树：
从图中可以看到，我们为user表（用户信息表）建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点，节点中存储了键(key)和数据(data)。
键对应user表中的id，数据对应user表中的行数据。二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。 顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。
如果我们需要查找id=12的用户信息，利用我们创建的二叉查找树索引，查找流程如下：
-
将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。
-
继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。
-
把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=12,name=xm。
利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。
2）平衡二叉树
为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。
平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。
下面是平衡二叉树和非平衡二叉树的对比：
平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。
平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。
3）B树
因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。
但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。
如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。
如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。
那说明什么？
说明每个磁盘块仅仅存储一个键值和数据！
那如果我们要存储海量的数据呢？
可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！
为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的B树。
B树（Balance Tree）即为平衡树的意思，下图即是一颗B树。
图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。- 图中的每个节点称为页，页就是我们上面说的磁盘块，在mysql中数据读取的基本单位都是页，所以我们这里叫做页更符合mysql中索引的底层数据结构。
从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。
基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。
假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：
- 先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。
- 将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。
- 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。
4）B+树
B+树是对B树的进一步优化。让我们先来看下B+树的结构图：
根据上图我们来看下B+树和B树有什么不同。
- B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。
- 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
有心的读者可能还发现上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。
通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。
MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。
2）聚集索引vs非聚集索引（物理存储角度分类）
在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。
这里我们着重介绍innodb中的聚集索引和非聚集索引。
- 聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。
- 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。
明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。
还是这张B+树索引图，现在我们应该知道这就是聚集索引，表中的数据存储在其中。现在假设我们要查找id>=18并且id<40的用户数据。对应的sql语句为select * from user where id>=18 and id <40，其中id为主键。具体的查找过程如下：
-
一般根节点都是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。
从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。
从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。 -
要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。
从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。 -
同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。
将页8读取到内存中后。
因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。
此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值18对应的数据。
因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。
我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。 -
因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。
那么查找到此终止。
最终我们找到满足条件的所有数据为：
(18,kl),(19,kl),(22,hj),(24,io),(25,vg),(29,jk),(31,jk),(33,rt),(34,ty),(35,yu),(37,rt),(39,rt)。
总共12条记录。
在叶子节点中，不在存储所有的数据了，存储的是键值和主键。
对于叶子节点中的x-y，比如1-1。左边的1表示的是索引的键值，右边的1表示的是主键值。如果我们要找到幸运数字为33的用户信息，对应的sql语句为select * from user where luckNum=33。
查找的流程跟聚集索引一样，这里就不详细介绍了。我们最终会找到主键值47，找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。
下面看下具体的查找流程图：
3）索引逻辑角度分类
1 主键索引
2 唯一索引
3 普通索引
4 复合索引
参考：
索引的分类及失效情况
4）创建索引的好处和坏处
好处：
①通过创建索引,可以在查询的过程中,提高系统的性能
②通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
③在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
坏处：
①创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大
②索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大
③在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护
5）应该和不应该建立索引的情况
应该在哪些列上创建索引：
①经常需要搜索的列上
②作为主键的列上
③经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
④经常需要根据范围进行搜索的列上
⑤经常需要排序的列上
⑥经常使用在where子句上面的列上
不应该在哪些列上创建索引：
①查询中很少用到的列
②对于那些具有很少数据值的列.比如人事表的性别列,bit数据类型的列
③对于那些定义为text,image的列.因为这些列的数据量相当大
④当对修改性能的要求远远大于搜索性能时.因为当增加索引时,会提高搜索性能,但是会降低修改性能
3108
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
