最全的数据库-MySQL知识汇总4

7、E-R图

E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。

E-R方法是“实体-联系方法”（Entity-Relationship Approach）的简称。它是描述现实世界概念结构模型的有效方法，是表示概念模型的一种方式，用矩形表示实体型，矩形框内写明实体名；用椭圆表示实体的属性，并用无向边将其与相应的实体型连接起来；用菱形表示实体型之间的联系，在菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上联系的类型（1:1,1:n或m:n）。

在ER图中有如下四个成分：

矩形框： 表示实体，在框中记入实体名。

菱形框： 表示联系，在框中记入联系名。

椭圆形框： 表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。

连线： 实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。（对于一对一联系，要在两个实体连线方向各写1；对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)

实体型(Entity)： 具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体;在E-R图中用矩形表示，矩形框内写明实体名；比如学生张三丰、学生李寻欢都是实体。如果是弱实体的话，在矩形外面再套实线矩形。

属性(Attribute)： 实体所具有的某一特性，一个实体可由若干个属性来刻画。在E-R图中用椭圆形表示，并用无向边将其与相应的实体连接起来；比如学生的姓名、学号、性别、都是属性。如果是多值属性的话，在椭圆形外面再套实线椭圆，如果是派生属性则用虚线椭圆表示。

联系(Relationship)： 联系也称关系，信息世界中反映实体内部或实体之间的联系。实体内部的联系通常是指组成实体的各属性之间的联系；实体之间的联系通常是指不同实体集之间的联系。在E-R图中用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1 : 1，1 : n或m : n），比如老师给学生授课存在授课关系，学生选课存在选课关系。如果是弱实体的联系则在菱形外面再套菱形。

联系可分为以下 3 种类型：

• 一对一联系(1 ∶1)

例如，一个班级有一个班长，而每个班长只在一个班级任职，则班级与班长的联系是一对一的。

• 一对多联系(1 ∶N)

例如，某校教师与课程之间存在一对多的联系“教”，即每位教师可以教多门课程，但是每门课程只能由一位教师来教。

• 多对多联系(M ∶N)

例如，学生与课程间的联系(“学 ”)是多对多的，即一个学生可以学多门课程，而每门课程可以有多个学生来学。联系也可能有属性。例如，学生“ 学” 某门课程所取得的成绩，既不是学生的属性也不是课程的属性。由于“ 成绩” 既依赖于某名特定的学生又依赖于某门特定的课程，所以它是学生与课程之间的联系“ 学”的属性。

作图步骤：

1. 确定所有的实体集合。
2. 选择实体集应包含的属性。
3. 确定实体集之间的联系。
4. 确定实体集的关键字，用下划线在属性上表明关键字的属性组合。
5. 确定联系的类型，在用线将表示联系的菱形框联系到实体集时，在线旁注明联系的类型。

8、数据库的七种锁

1. 行锁（Record Locks） ：行锁一定是作用在索引上的。
2. 间隙锁（Gap Locks）：锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。这里的共同间隙包括两种场景：其一是两个间隙锁的间隙区间完全一 样；其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的 子集 。间隙锁本质上是用于 阻止其他事务在该间隙内插入新记录 ，而 自身事务是允许在该间隙内插入数据的 。也就是说间隙锁的应用场景包括并发 读取 、并发 更新 、并发删除和并发 插入 。在RU和RC两种隔离级别下，即使你使用select ... in share mode或select ... for update，也无法防止 幻读 （读后写的场景）。因为这两种隔离级别下只会有 行锁 ，而不会有 间隙锁 。这也是为什么示例中要规定隔离级别为RR的原因。
3. 临键锁（Next-key Locks）：临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止 幻读 ；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止 幻读 。
4. 共享锁/排他锁（Shared and Exclusive Locks） ：共享锁/排他锁都只是 行锁 ，与间隙锁无关，这一点很重要，后面还会强调这一点。其中共享锁是一个事务并发读取某一行记录所需要持有的锁，比如select ... in share mode；排他锁是一 个事务并发更新或删除某一行记录所需要持有的锁，比如select ... for update。不过这里需要重点说明的是，尽管共享锁/排他锁是行锁，与间隙锁无关，但一个事务在请求共享锁/排他锁时，获取到的结果却可能是 行锁 ，也可能是 间隙锁 ，也可能是 临键锁 ，这取决于数据库的隔离级别以及查询的 数据是否存在 。关于这一点，后面分析场景一和场景二的时候还会提到。
5. 意向共享锁/意向排他锁（Intention Shared and Exclusive Locks） ：意向共享锁/意向排他锁属于 表锁 ，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的 前置条件 。
6. 插入意向锁（Insert Intention Locks） ：插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。与间隙锁的另一个非常重要的差别是：尽管插入意向锁也属于 间隙锁 ，但两个事务却不能在同一时间内一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。这里我们再回顾一下共享锁和排他锁：共享锁用于读取操作，而排他锁是用于更新或删除操作。也就是说插入意向锁、共享锁和排他锁涵盖了常用的增删改查四个动作。
7. 自增锁（Auto-inc Locks） ：增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。为了便于介绍innodb_autoinc_lock_mode参数，我们先将需要用到自增锁的Insert语句进行分类：

9、数据库高并发的解决方案

1. 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
2. 增加数据库索引。提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
3. 主从读写分离，让主服务器负责写，从服务器负责读。
4. 将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。
5. 使用分布式架构，分散计算压力。

10、SQL语句的内部致性过程

• 连接器：客户端先通过连接器连接到 MySQL 服务器。
• 缓存：连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。
• 分析器：分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
• 优化器：优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
• 执行器：优化器执行完就进入执行器，执行器就开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

11、为什么要分库分表

数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；另外，由于无法进行分布式式部署，而一台服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。

12、分布式事务

分布式事务用于在分布式系统中保证不同节点之间的数据一致性。分布式事务的实现有很多种，最具有代表性的是由Oracle Tuxedo系统提出的XA分布式事务协议。

XA协议包含两阶段提交（2PC）和三阶段提交（3PC）两种 ：

1. 2PC: 在XA分布式事务的第一阶段，作为事务协调者的节点会首先向所有的参与者节点发送Prepare请求。在接到Prepare请求之后，每一个参与者节点会各自执行与事务有关的数据更新，写入Undo Log和Redo Log。如果参与者执行成功，暂时不提交事务，而是向事务协调节点返回“完成”消息。

   当事务协调者接到了所有参与者的返回消息，整个分布式事务将会进入第二阶段。
   在XA分布式事务的第二阶段，如果事务协调节点在之前所收到都是正向返回，那么它将会向所有事务参与者发出Commit请求。
   接到Commit请求之后，事务参与者节点会各自进行本地的事务提交，并释放锁资源。当本地事务完成。提交后，将会向事务协调者返回“完成”消息。

当事务协调者接收到所有事务参与者的“完成”反馈，整个分布式事务完成。

注意:

在XA的第一阶段，如果某个事务参与者反馈失败消息，说明该节点的本地事务执行不成功，必须回滚。于是在第二阶段，事务协调节点向所有的事务参与者发送Abort请求。接收到Abort请求之后，各个事务参与者节点需要在本地进行事务的回滚操作，回滚操作依照Undo Log来进行。

XA两阶段提交的不足

1. 性能问题

XA协议遵循强一致性。在事务执行过程中，各个节点占用着数据库资源，只有当所有节点准备完毕，事务协调者才会通知提交，参与者提交后释放资源。这样的过程有着非常明显的性能问题。

2. 协调者单点故障问题

事务协调者是整个XA模型的核心，一旦事务协调者节点挂掉，参与者收不到提交或是回滚通知，参与者会一直处于中间状态无法完成事务。

3. 丢失消息导致的不一致问题。

在XA协议的第二个阶段，如果发生局部网络问题，一部分事务参与者收到了提交消息，另一部分事务参与者没收到提交消息，那么就导致了节点之间数据的不一致。

为了解决上诉问题，提供下面几重分布式事务解决方案

1. XA三阶段提交

XA三阶段提交在两阶段提交的基础上增加了CanCommit阶段，并且引入了超时机制。一旦事物参与者迟迟没有接到协调者的commit请求，会自动进行本地commit。这样有效解决了协调者单点故障的问题。但是性能问题和不一致的问题仍然没有根本解决。

2. MQ事务

利用消息中间件来异步完成事务的后一半更新，实现系统的最终一致性。这个方式避免了像XA协议那样的性能问题。

3. TCC事务

TCC事务是Try、Commit、Cancel三种指令的缩写，其逻辑模式类似于XA两阶段提交，但是实现方式是在代码层面来人为实现。

既然能看到文末，我相信你一定是个非常有潜力的选手，你的耐心和你的专注度已经超过了同龄人，点赞收藏，需要用到的时候翻出来再好好复习复习。

如果这样看起来不爽的话，你也可以后台私信我，发你PDF版的，下载下来慢慢看哈哈。