发布于

DB基础知识

一、MySQL

1.什么是MySQL

MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息等。

2.MySQL架构

  • 连接器： 身份认证、权限相关。
  • 查询缓存： 查询的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  • 分析器： 词法分析、语法分析。
  • 优化器： 按照 MySQL 认为最优的方案去执行。
  • 执行器： 操作引擎，返回结果。
  • 存储引擎 ： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

3.MySQL存储引擎

主要有四种：MyISAM、InnoDB、MEMORY、Archive，其他的还有BDB等

3.1 MyISAM：

数据库存储对应三个文件，一个索引文件，一个数据文件，一个表结构文件。叶子节点data域存放的是数据的地址。

MyISAM拥有较高的插入、查询速度，但不支持事务，支持表锁，不支持外键。

MYI：索引文件

MYD：数据文件

3.2 InnoDB：

数据库存储对应的两个文件，一个表结构文件，一个数据和索引文件。叶子节点存放的是数据。

支持事务安全表（ACID），支持行级锁和外键，InnoDB是默认的MySQL引擎。

3.3 MEMORY：

MEMORY存储引擎将表中的数据存储到内存中，MySQL中使用该引擎作为临时表，存放查询的中间结果。数据的处理速度很快但是安全性不高。

3.4 Archive：

如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。

 

注： MySQL当前默认的存储引擎是 InnoDB。 5.5.5 之前，MyISAM 是默认存储引擎。

注2：show engines;    //查看MySQL引擎

二、事务

1.什么是事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

2.数据库事务四大特性

ACID

  • Atomic(原子性)     事务必须是原子的工作单元
  • Consistent(一致性)  事务完成时，必须使所有数据都保持一致状态
  • Isolation(隔离性)    并发事务所做的修改必须和其他事务所做的修改是隔离的
  • Duration（持久性） 事务完成之后，对系统的影响是永久性的

3.MySQL事务隔离级别

3.1 并发事务带来的问题

3.1.1 脏读：在一个事务处理过程里读取了另一个未提交的事务中的数据。（读取了另外事务未提交的内容）

3.1.2 不可重复读：一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。（读取了另外事务提交的内容）

3.1.3 幻读：事务T1将表中所有某列的数据由“1”改为“2”，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

注：不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

3.2MySQL事务隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
读已提交/不可重复读（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否

3.3 MySQL隔离级别的实现

基于锁和 MVCC 机制共同实现。

SERIALIZABLE 隔离级别，是通过锁来实现的。除了 SERIALIZABLE 隔离级别，其他的隔离级别都是基于 MVCC 实现。

4. Mysql里的事务处理过程

  1. 记录redo和undo log文件，确保日志在磁盘上的持久化
  2. 更新数据记录
  3. 提交事务 ，redo 写入commit记录

4.1 Undo + Redo事务的简化过程

假设有A、B两个数据，值分别为1,2，开始一个事务，事务的操作内容为：把1修改为3，2修改为4，那么实际的记录如下（简化）：
A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录A=3到redo log.
E.记录B=2到undo log.
F.修改B=4.
G.记录B=4到redo log.
H.将redo log写入磁盘。
I.事务提交

5.分布式事务

单独开模块说明

三、索引

1.什么是索引

索引是帮助高效获取数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

为什么不用hash：hash无法实现范围查找，比如搜索id>5。hash冲突。

优点：加快数据检索速度；通过唯一索引可以保证数据唯一。

缺点：创建和维护耗费时间，降低更新表的速度；存储索引耗费物理空间。

1.1 时间局部性原理和空间局部性原理

  • 时间局部性原理：查找时，会缓存。比如先查找了itemID=1的，猜想你可能还会查找=1的，则会缓存
  • 空间局部性原理：扩大范围查找。要查找itemID=1的，则同时可能会读取itemID=2或3 的。

1.2 三种存储方式可视化页面

https://www.cs.usfca.edu/~galles/visualization/BST.html

https://www.cs.usfca.edu/~galles/visualization/RedBlack.html

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

1.3 BTree和B+TREE

BTree也就是B-树，是一种多路搜索树，对应节点都存储数据。B+树只有叶子节点存储数据。

 

1.4 MYISAM和INNODB

MYI：索引文件

MYD：数据文件

以name为索引的B+Tree类似于上图，每次找到对应节点，该节点记录地址，然后从对应地址取得数据返回。

MyISAM为非聚集索引（数据存储在一个地方，索引存储在另外一个地方）。

 

以id（主键）查找数据时，从根节点开始找到对应节点，返回数据。而且对应id>1这种查找（空间局部性原理）会比较方便。

以name为副索引查找时，会先找到主键，然后从根节点查找对应数据返回。

 

1.5 为什么要用自增ID做主键

插入：每申请一页内存后，能充分利用内存，用完后，再去申请下一块（因为自增，所以可以充分用连续的地址空间，如果是乱序的话，就不会一块用完才去申请下一块）

查找：数字大小比较 方便查找

1.6 索引类型

细节可以见：索引类型

  • 普通索引：是最基本的索引，它没有任何限制，只是为了加快查询速度。
  • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
  • 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
  • 全文索引(Full Text) ：为了检索大文本数据中的关键字的信息。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

注：主键索引是一级索引，其他都属于二级索引，即通过二级索引找到主键id，再回表查到具体的数据。

  • 聚集索引：索引结构和数据一起存放的索引。主键索引属于聚集索引。叶子（节点存放的是数据）
  • 非聚集索引：索引结构和数据分开存放的索引。二级索引属于非聚集索引。（叶子节点存放的是主键+列值）。优点：更新代价小。缺点：需要回表。

覆盖索引：需要查询的字段正好是索引的字段，就不需要再回表查询了。

2.联合/组合索引的最左前缀匹配

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配。

如：当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！（ac索引其实用的是a的？）

3.索引失效的情况

  • 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
  • 对于多列索引，不是使用的第一部分，则不会使用索引（最左前缀匹配）
  • like查询以%开头（如果是xxx%则会使用到索引）
  • 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
  • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

4.建立索引原则

  • 不为 NULL 的字段 ：对于数据为 NULL 的字段，数据库较难优化。
  • 区分度高的字段。
  • 被频繁更新的字段应该慎重建立索引。
  • 索引尽可能简洁，避免冗余索引
  • 尽可能的考虑建立联合索引而不是单列索引。

四、MySQL锁

1.锁类型/级别

MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

  • 表级锁：锁定粒度大，锁冲突概率高；开销小，加锁快；不会出现死锁。主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎使用
  • 行级锁：锁定粒度小，锁冲突概率低；开销大，加锁慢；会出现死锁。主要是InnoDB存储引擎使用；
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般，主要是BerkeleyDB存储引擎使用。

适用：表级锁更适合于以查询为主，有少量按索引更新的应用；而行级锁则更适合于有大量按索引更新数据，同时又有并发查询的应用。

注：InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。

2.共享锁和排它锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

  • 共享锁（S 锁） ：读锁，允许多个事务同时获取（锁兼容）。
  • 排他锁（X 锁） ：写锁/独占锁，不允许多个事务同时获取。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

S 锁 X 锁
S 锁 不冲突 冲突
X 锁 冲突 冲突 
# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

注：由于 MVCC 的存在，一般的 SELECT 语句，InnoDB 不会加任何锁。

3.意向锁

3.1 作用

用来快速判断是否可以对某个表使用表锁（没意向锁的话需要判断有没有行锁，得一行行遍历）。

3.2 意向锁分类：

  • 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

注：意向锁由数据引擎自己维护，用户无法手动操作。

这四种锁的共存逻辑关系：

注：这里的锁都是说明的表级别的锁兼容关系，不涉及到行（这里的X、S都是表级锁）。意向锁不会与行级的共享锁和排他锁互斥。

比如：要加意向排它锁，如果发现表已经存在共享锁（表级别）或者排它锁（表级别），那么就会加锁不成功。

又比如：一个表已经被加了意向排它锁（证明这个表中的某行数据正在被修改），那么此时加表级别共享锁S和排它锁X都会冲突，但是加其他意向锁不会冲突，因为加了意向锁，只是代表我要修改具体某行的数据，不一定会和现有的行冲突，而且就算有冲突，应该会在后续对行加锁时可以检测出来，但是并不影响意向锁的使用。上表的关系是用来表示意向锁和表级S、X锁的关系。

 

4. InnoDB 行锁分类

  • 记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
  • 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
  • 临键锁（Next-key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

5. 快照读和当前读

快照读（一致性非锁定读） ：利用 MVCC 机制，读取的是记录的历史版本，也就是快照。普通的select语句。

当前读 （一致性锁定读）： 就是给行记录加 X 锁或 S 锁。select for update

当前读常见 SQL 语句：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

五、日志

1.三大日志

binlog：事务日志

redo log：重做日志

undo log：回滚日志

2.redo log和binlog

2.1 InnoDB 的 redo log细节

简单说就是固定大小的一块地址，可以循环写，有两个指针，一个代表写入，一个代表擦除（持久化到DB了）

注：只要redolog有了，那么数据就不会丢了。

2.2 redo log和binlog区别

  • redo log 是 InnoDB 引擎特有的，而binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
  • redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

2.3 redo log和binlog协助处理流程

使用两阶段提交。先写redo log为prepare，后写binlog，然后更新redo log为commit状态。

注：提交事务先写binlog，提交redo log。如果commit阶段宕机，MySQL会检测如果有binlog，那么redo log继续提交，如果没有binlog，那么就会回滚该事务。

3.binlog

3.1 binlog的格式

  • statement ： 基于sql语句的模式。update table set name =””; effect row 1000； uuid、now() other function   （默认模式）（如果对于now()函数这种问题，statement模式可能导致主从对应字段的值不一致）
  • row： 基于行模式; 存在1000条数据变更；  记录修改以后每一条记录变化的值（可以保证每一条数据一致，但是记录数据太多）
  • mixed: 混合模式，由mysql自动判断处理（对于now()函数这种自动会用row模式）

3.2 主从同步的原理

1.   master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务

2.   slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志

3.   SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致

3.3 主从同步延迟产生原因

  • 当master库tps比较高的时候，产生的DDL数量超过slave一个sql线程所能承受的范围，或者slave的大型query语句产生锁等待
  • 网络传输： bin文件的传输延迟
  • 磁盘的读写耗时：文件通知更新、磁盘读取延迟、磁盘写入延迟

3.4 主从同步延迟解决方案

a.在数据库和应用层增加缓存处理，优先从缓存中读取数据（应用层在master写入数据的时候，把新数据缓存，等到缓存失效的时候，可能从库已经同步了数据）

b.减少slave同步延迟，可以修改slave库sync_binlog属性；

sync_binlog=0  文件系统来调度把binlog_cache刷新到磁盘

sync_binlog=n

c.增加延时监控

4.undolog

在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的。undo log在事务开始修改之前会记录当前的值。

六、分库分表&分布式事务

1.分库分表

1.1 跨库join问题

2. X/OpenDTP事务模型

X/Open Distributed Transaction Processing Reference Model

X/Open是一个组织机构，定义出的一套分布式事务标准， 定义了规范的API接口

 

2PC（two -phase-commit）, 用来保证分布式事务的完整性

J2EE 遵循了X/open DTP规范，设计并实现了java里面的分布式事务编程接口规范-JTA

XA是X/Open DTP定义的中间件与数据库之间的接口规范。 XA接口函数由数据库厂商提供

 

2.1 X/OpenDTP 角色

AP application  具体的应用 比如上图的库存中心、订单中心

RM resouces manager   资源管理器。 数据库

TM transaction manager  事务管理器，事务协调者

3. 2PC（two -phase-commit）

(CAP：CAP原则又称CAP定理，指的是在一个分布式系统中，Consistency（一致性）、 Availability（可用性）、Partition tolerance（分区容错性），三者不可得兼)

3.1 事务过程

3.1.1 阶段一：提交事务请求（投票）

1.TM向所有的AP发送事务内容，询问是否可以执行事务的提交操作，并等待各个AP的响应

2.执行事务

各个AP节点执行事务操作，将undo和redo信息记录到事务日志中，尽量把提交过程中所消耗时间的操作和准备都提前完成后确保后续

事务提交的成功率

3.各个AP向TM反馈事务询问的响应

各个AP成功执行了事务操作，那么反馈给TM yes的response；如果AP没有成功执行事务，就反馈TM no的response

3.1.2 阶段二：执行事务提交

执行提交事务

假设一个事务的提交过程总共需要30s， 其中prepare操作需要28（事务日志落地磁盘及各种io操作），而真正commit只需要2s

那么，commit阶段发生错误的概率和prepare相比， 2/28 (<10%) .只要第一个阶段成功，那么commit阶段出现失败的概率就非常小

大大增加了分布式事务的成功概率

3.1.3 中断事务提交

3.2 2pc存在的问题

  1. 数据一致性问题
  2. 同步阻塞

4. 3PC(three phase commit)

阶段一：canCommit      询问是否可以提交

阶段二：preCommit      进行预提交，类似于2pc中的预提交

阶段三：doCommit        提交

 

4.1 改进点

  1. 增加了超时机制
  2. 第二阶段，如果协调者超时没有接受到参与者的反馈，则自动认为失败，发送abort命令
  3. 第三阶段，如果参与者超时没有接受到协调者的反馈，则自动认为成功开始提交事务（基于概率）

4.2 3pc的问题

相对于2PC，3PC主要解决的单点故障问题，并减少阻塞，因为一旦参与者无法及时收到来自协调者的信息之后，他会默认执行commit。而不会一直持有事务资源并处于阻塞状态。但是这种机制也会导致数据一致性问题，因为，由于网络原因，协调者发送的abort响应没有及时被参与者接收到，那么参与者在等待超时之后执行了commit操作。这样就和其他接到abort命令并执行回滚的参与者之间存在数据不一致的情况。

 

2.1实现方式

2.2 事务悬挂