Mysql

MySQL体系架构

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

网络连接层

客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流的服务器编程技术，例如常见的Java、C、Python、.NET等，它们通过各自的API技术与MySQL建立连接。

服务层（MySQL Server）

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。 - 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。 - 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群 管理等 - SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。 - 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。 - 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

select uid,name from user where gender=1; 选取--》投影--》联接 策略 1）select先根据where语句进行选取，并不是查询出全部数据再过滤 2）select查询根据uid和name进行属性投影，并不是取出所有字段 3）将前面选取和投影联接起来最终生成查询结果

缓存（Cache&Buffer）：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓 存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

 系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

 MySQL运行机制

建立连接（Connectors&Connection Pool）

通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。

查询缓存（Cache&Buffer）

这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

解析器（Parser）

将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。

查询优化器（Optimizer）

根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

查询执行引擎负责执行 SQL 语句

此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。

索引概述

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，包括B+树或者Hash表。由于存储引擎表示的是数据在磁盘上面的不同的组织形式，所以索引底层采用哪种数据结构是跟数据库的存储引擎相关的。如果是MyIsam或者是InnoDB存储引擎，那么对应的底层的数据结构为B+树，如果是Memory存储引擎，那么对应的底层的数据结构为Hash表。

为什么用B+树？

为什么不用二叉树?

用二叉树其实就是借助于二分法来实现nlogn时间内查找到数据

但是二叉树(包括红黑树)的出度只有2

必然会导致树高度过高 => 查找速度慢

为什么不用B树?为什么用B+树

1)B树的非叶子节点也会存储数据信息 而B+树只有叶子结点会存储该信息

    所以相同内存页大小 B+树可以查找的关键字更多 ==> 减少了IO读写次数

2)B+数的查询效率更加稳定

    任何关键字的查找必须走一条从根节点到叶子结点的路 所有关键字查询路径相同

3)B+树更便于遍历,更适合区间查询

    B+树的数据都存在叶子节点上且为一个双向链表

    遍历时只需要搜一遍叶子结点即可 而B树必须进行一次中序遍历

索引分类

 索引失效

• 不满足最左匹配原则
• like语句导致索引失效
• 在列上进行运算导致失效
• 索引不会包含含有NULL值的列

为什么联合索引要满足最左匹配原则？

原因出在底层的B+数存储上 我们知道 B+树的最底层(叶子结点)是有序的双链表 在这之上的非叶子节点其实就是通过索引来值来把叶子结点这条链表分成不同的片区来加快查找速度

那么叶子按左优先来进行索引排序 也就决定了索引必须满足最左匹配原则

索引原理：联合索引（最左前缀原则）_底小治的博客-CSDN博客_联合索引最左原则原理

三层B+数可以储存多少数据(不同存储引擎结果不同)

一页(Page) = 16KB 假设主键为BigInt(8B) 在InnoDB中指针大小为6B,则一页可以存

16*1024/14 =1170个指针 三层B+数有两层为指针 ==> 1170*1170

假设一行数据为1KB,则一页可以存16行

那么三层总共可以存储数据:1170*1170*16 千万行

tips:需要注意的是此处的页 指的是InnoDB中的页大小 为16KB Linux中的页大小为4KB

事务

什么是事务?

事务是访问并更新数据库中各项数据项的一个程序执行单元.

在事务中操作,要么都做修改,要么都不做.

事务的四特性:

• Atomicity（原子性）：事务是一个不可分割的整体，事务内所有操作要么全做成功，要么全失败。

• Consistency（一致性）：务执行前后，数据从一个状态到另一个状态必须是一致的（A向B转账，不能出现A扣了钱，B却没收到）。

• Isolation（隔离性）： 多个并发事务之间相互隔离，不能互相干扰。

• Durability（持久性）：事务完成后，对数据库的更改是永久保存的，不能回滚

如何保证事务四特性

可靠性：原子性、一致性、持久性可以归纳为可靠性。可靠就是要保证数据的一致与不丢失。数据库要保证数据的一致，就要处理commit与rollBack；显然处理commit指令的时候需要记录要提交哪些数据，rollback的时候需要知道回退的原数据。

mysql中commit需要redo log，rollBack 对应undo log

原子性：

Innodb中的undo log可以是实现原子性的关键，当事务回滚时会撤销所有已经执行完毕的sql语句，但是需要记录回滚的日志信息。

例如

    (1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据

    (2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作

    (3)当你insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

---

持久性：

innodb中的redo log可以保证持久性。Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

redo log解决上面的问题。当数据修改的时候，不仅在内存中操作，还会在redo log（磁盘）中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和bin log内容决定回滚数据还是提交数据。

---

隔离性：

Mysql利用锁和MVCC多版本并发控制(Multi Version Concurrency Control)来保证隔离性。

一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。

如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。
但是有一点说明一下，在事务隔离级别为读已提交(Read Commited)时，一个事务能够读到另一个事务已经提交的数据，是不满足隔离性的。但是当事务隔离级别为可重复读(Repeateable Read)中，是满足隔离性的。
 

---

 事务的隔离级别有哪些？

既然隔离性要求的是并发的事务之间互不影响 那我们先看看数据库中并发事务会带来什么问题：

1- 数据丢失

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

2 -读脏数据（读到了撤销修改之前被修改的垃圾数据）

T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

3- 不可重复读

在同⼀个事务中执⾏同⼀个读取操作,但是结果不⼀致。

 4-  幻读（两次相同读取范围的读取中被插入了新数据，范围内读取到的数据结果不同）

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

 tip：不可重复读和幻读两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

在可重复读级别下 只是给数据加了行级排它锁 只能对现有的数据行保护 令其不被修改或删除 但是仍可以对表进行修改(insert操作)所以还是无法避免幻读

数据库的的锁:

基于锁的属性分类：共享锁、排他锁。

基于锁的粒度分类：表锁、页锁、行锁、

共享锁(Share Lock)

共享锁又称读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。

共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

排他锁（eXclusive Lock）

排他锁又称写锁，简称X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。

排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

---

不同的隔离级别

未提交读（Read Uncommitted）：

在事务 A 读取数据时，事务 B 读取和修改 数据加了排它锁,直到事务结束后释放。

tips:就算数据被加了排它锁 但是因为事务A并没有通过申请锁来读数据 所以仍然可以读 只不过可能会出现读的数据出错罢了

这种隔离级别，会导致脏读、不可重复读以及幻读。

已提交读（Read Committed）：在事务 A 读取数据时 数据增加了共享锁，⼀旦读取，⽴即释
放锁，事务 B 读取修改数据时 给数据增加了⾏级排他锁，直到事务结束才释放锁。也就是说，事
务 A 在读取数据时，事务 B 只能读取数据，不能修改。当事务 A 读取到数据后，事务 B才
能修改。这种隔离级别，可以避免脏读，但依然存在不可重复读以及幻读的问题。
 

可重复读（Repeatable Read）：在事务 A 读取数据时增加了共享锁，事务结束，才释放
锁，事务 B 读取修改数据时增加了⾏级排他锁，直到事务结束才释放锁。也就是说，事务A
在没有结束事务时，事务 B 只能读取数据，不能修改。当事务 A 结束事务，事务 B 才能修
改。这种隔离级别，可以避免脏读、不可重复读，但依然存在幻读的问题。
 

可序列化（Serializable）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事
务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁。可序列化解决了脏读、
不可重复读、幻读等问题，但隔离级别越来越⾼的同时，并发性会越来越低。
 

MVCC

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

什么是MySQL InnoDB下的当前读和快照读?

• 当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

• 快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

当前读，快照读和MVCC的关系

• 准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念

• 而在MySQL中，实现这么一个MVCC理想概念，我们就需要MySQL提供具体的功能去实现它，而快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现

MVCC能解决什么问题，好处是？

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题:

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了MVCC，所以我们可以形成两个组合：

• MVCC + 悲观锁 MVCC解决读写冲突，悲观锁解决写写冲突(使用select ... for update)
• MVCC + 乐观锁 MVCC解决读写冲突，乐观锁解决写写冲突(更新失败则反悔失败状态,由用户决定如何进行下一步)

这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题

MVCC实现原理

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

核心思想就是:读操作只读该事务开始前的数据库的快照

基本流程:

在Undo Log链中找到对该数据修改过的最新事务ID A

==>

将该事务ID A与Read VIew中未提交的事务列表中的ID 进行比对

==>1) A < 列表中最小ID -- > 对数据的修改早于本次读 可以读数据

       2) A > 列表中最大ID --> 对数据的修改晚于本次读 不可以读该版本数据 需要读取Undo Log中

          下一个业务ID 并继续进行比较

       3) 列表中最小ID < A < 列表中最大ID

        若未提交事务列表中包含A(即A还未提交) 那么不读该版本数据

                需要读取Undo Log中下一个业务ID 并继续进行比较

        若未提交事务列表中不包含A(即A事务早于读操作之前已提交) 那么可读该版本数据

RR是如何在RC级的基础上解决不可重复读的?

• 在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见；
• 即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见
• 而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因

总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。