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 事务悬挂

 

 

发表评论

邮箱地址不会被公开。 必填项已用*标注