Mysql体系化学习总结

第一部分架构原理

第一节体系架构:

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

1.网络连接层

提供与MySQL服务器建立的支持

2.服务层

 服务层是MySQL Server的核心，主要包含1.系统管理和控制工具2.连接池3.SQL接口4.解析器5.查询优化器6.缓存六个部分

3.存储引擎层

负责MySQL中数据的存储与提取，与底层系统文件进行交互

4.系统文件层

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，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 语句执行则直接返回结果。

第三节存储引擎

负责MySQL中的数据的存储和提取，是与文件打交道的

子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎

(引擎了解)

InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全

MyISAM：不支持事务和外键，访问速度快

Memory：利用内存创建表，访问速度非常快，因为数据在内存，而且默认使用Hash索引，但是一旦关闭，数据就会丢失

Archive：归档类型引擎，仅能支持insert和select语句

Csv：以CSV文件进行数据存储，由于文件限制，所有列必须强制指定not null，另外CSV引擎也不支持索引和分区，适合做数据交换的中间表

BlackHole: 黑洞，只进不出，进来消失，所有插入数据都不会保存

Federated：可以访问远端MySQL数据库中的表。一个本地表，不保存数据，访问远程表内容。

MRG_MyISAM：一组MyISAM表的组合，这些MyISAM表必须结构相同，Merge表本身没有数据，对Merge操作可以对一组MyISAM表进行操作。

3.1:InnoDB和MyISAM对比

1事务和外键

InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作

MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作

2锁机制

InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现。

MyISAM支持表级锁，锁定整张表。

3索引结构

InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。

MyISAM使用非聚集索引（非聚簇索引），索引和记录分开。

4并发处理能力

MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。

InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发

5存储文件

InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB；

MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制是256TB。

引擎适用场景和选择

MyISAM

不需要事务支持（不支持）

并发相对较低（锁定机制问题）

数据修改相对较少，以读为主

数据一致性要求不高

InnoDB

需要事务支持（具有较好的事务特性）

行级锁定对高并发有很好的适应能力

数据更新较为频繁的场景

数据一致性要求较高

硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO

总结

两种引擎该如何选择？

是否需要事务？有，InnoDB

是否存在并发修改？有，InnoDB

是否追求快速查询，且数据修改少？是，MyISAM

在绝大多数情况下，推荐使用InnoDB

3.2 InnoDB存储结构

从MySQL 5.5版本开始默认使用InnoDB作为引擎，它擅长处理事务，具有自动崩溃恢复的特性

主要分为内存结构和磁盘结构两大部分

内存结构:主要包括1.Buffer Pool、2.Change Buffer、3.Adaptive Hash Index和4.Log Buffer四大组件。

磁盘结构:1Tablespaces，2InnoDB Data Dictionary，3Doublewrite Buffer、4Redo Log

和Undo Logs。

3.3 InnoDB线程模型(了解)

1.IO Thread:

大量的AIO（Async IO）来做读写处理，这样可以极大提高数据库的性能

2.Purge Thread:

事务提交之后，其使用的undo日志将不再需要，因此需要Purge Thread回收已经分配的undo页。

3.Page Cleaner Thread:

将脏数据刷新到磁盘,同步数据，又能达到redo log循环使用的目的

4.Master Thread:

负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘 ，保证数据的一致性。包含：脏页的刷新（page cleaner thread）、undo页回收（purge thread）、redo日志刷新（log thread）、合并写缓冲等

3.4 InnoDB数据文件

1 InnoDB文件存储结构

InnoDB数据文件存储结构(重要)：

分为一个ibd数据文件-->Segment（段）-->Extent（区）-->Page（页）-->Row（行）

Tablesapce

表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。

Segment

段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node

segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数

据，一个管理索引。每多创建一个索引，会多两个segment。

Extent

区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会一页一页分，直接分配一个区。

Page

页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页。

Row

行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field

pointers）等信息。

3.5 Undo Log

3.5.1 Undo Log介绍

Undo：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。

Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。

Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo

log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记录一个insert；执行一个update，undolog会记录一个相反的update。

Undo Log存储：undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback

segment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。

3.5.2 Undo Log作用

实现事务的原子性

Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

实现多版本并发控制（MVCC）

Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。

事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读

3.6 Redo Log和Binlog

Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。

3.6.1Redo Log：

指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。

Redo Log 的生成和释放：随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。

Redo Log工作原理

Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数

据。由后台Master线程每隔 1秒执行一次操作。

1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安

全，性能最差的方式。

2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS

cache -> flush cache to disk 的操作。

一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。

3.6.2 Binlog日志

Binlog记录模式

Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary

log（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景

主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到

Binlog后实现数据恢复达到主从数据一致性。

数据恢复：通过mysqlbinlog工具来恢复数据。

文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。

ROW（row-based replication, RBR）：

日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。

缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。

STATMENT（statement-based replication, SBR）：

每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

优点：日志量小，减少磁盘IO，提升存储和恢复速度

缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数

MIXED（mixed-based replication, MBR）：

以上两种模式的混合使用，一般会使用

STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存

binlog，MySQL会根据执行的SQL语句选择写入模式。

Binlog写入机制

根据记录模式和操作触发event事件生成log event（事件触发执行机制）

将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区

Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是

stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。

事务