快一步收藏：Mysql数据库核心知识汇总

快一步收藏：Mysql数据库核心知识汇总

毫无疑问，MySQL是当下最流行的开源数据库。凭借强大的性能和易于使用性，它已被Google、Facebook、YouTube、百度、网易和新浪等大型互联网公司所应用。更有统计，世界上一流的互联网公司中，排名前20的有80%都是MySQL的忠有用户。今日我就带大家一起来学习MySQL数据库的核心学问。

一、Mysql规律架构

Mysql规律架构主要分三层：

第一层：负责连接处理，授权认证，平安等等

1、每个客户端连接都会在服务器进程中拥有一个线程，服务器维护了一个线程池，因此不需要为每一个新建的连接创建或者销毁线程；

2、当客户端连接到Mysql服务器时，服务器对其进行认证，通过用户名和密码认证，也可以通过SSL证书进行认证；

3、一旦客户端连接胜利，服务器会连续验证客户端是否具有执行某个特定查询的权限。

其次层：负责编译并优化SQL

1、这一层包括查询解析，分析，优化，缓存以及全部的的内置函数；

2、对于SELECT语句，在解析查询前，服务器会先检查查询缓存，假如能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果；

3、全部跨存储引擎的功能都在这一层实现:存储过程，触发器，视图。

第三层：是存储引擎

1、存储引擎负责在MySQL中存储数据、提取数据；

2、存储引擎通过API与上层进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透亮     ；

3、存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简洁地响应上层服务器的恳求。

二、主从复制

主从复制原理，简言之，就三步曲：

1、主数据库有个binlog二进制文件，纪录了全部增删改Sql语句。（binlog线程）

2、从数据库把主数据库的binlog文件的sql语句复制过来。（io线程）

3、从数据库的relaylog重做日志文件中再执行一次这些sql语句。（Sql执行线程）

三、InnoDB文件存储结构

从物理意义上讲，InnoDB表由共享表空间文件（ibdata1）、独占表空间文件（ibd）、表结构文件（.frm）、以及日志文件（redo文件等）组成。

四、表结构文件

在MYSQL中建立任何一张数据表，在其数据名目对应的数据库名目下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，.frm文件跟数据库存储引擎无关，也就是任何存储引擎的数据表都必需有.frm文件。

五、表空间结构

1、表空间（tablespace)

全部数据都放在表空间中。假如开启了innodb_file_per_table选项，则InnoDB会为每张表开拓一个表空间。但是需要留意的是表空间存放的只是数据、索引和插入缓冲bitmap页，其他数据比如undo信息，插入缓冲索引页，系统事务信息，二次写缓冲还是会放在原来的共享表空间内。

假如rollback后，共享表空间不会自动收缩，但是会推断空间是否需要（比如undo空间），假如不需要的话，会将这些空间标记为可用空间，供下次undo使用。

2、段(segment)

表空间由各个段组成，比如数据段，索引段，回滚段等。

3、区(extent)

区由连续的页组成，在任何状况下区的大小都是1M。InnoDB存储引擎一次从磁盘申请也许4-5个区(4-5M)。在默认状况下，页的大小为16KB，即一个区中有也许64个连续的页。

4、页(page)

InnoDB磁盘管理的最小单位。B树节点=一个物理Page（16K），数据按16KB切片为Page并编号，编号可映射到物理文件偏移(16K*N），B+树叶子节点前后形成双向链表。Page分为几种类型，数据页和索引页就是其中最为重要的两种类型。

六、缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录根据页的方式进行管理，但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能。

在数据库中进行读取操作，首先将从磁盘中读到的页放在缓冲池中，下次再读相同的页中时，首先推断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

七、重做日志

默认状况在数据库数据文件夹下会有两个文件，ib_logfile0/ib_logfile1,这就是重做日志文件，记录了对于InnoDB存储引擎的事务日志。

每个Innodb存储引擎至少有1个重做日志文件组，每个组至少包含2个重做日志文件(ib_logfile0,ib_logfile1)。

可以通过设置多个镜像日志组(mirroredloggroups)，将不同组放到不同磁盘，提高重做日志的高可用性。

日志组中的文件大小是全都的，以循环的方式运行。文件1写满时，切换到文件2，文件2写满时，再次切换到文件1.日志组中的文件大小是全都的，以循环的方式运行。文件1写满时，切换到文件2，文件2写满时，再次切换到文件1(从头写入）。

为了保证数据的平安性，事务进行中时会不断的产生redolog，在事务提交时进行一次flush操作，保存到磁盘中,redolog是根据挨次写入的，磁盘的挨次读写的速度远大于随机读写。当数据库或主机失效重启时，会依据redolog进行数据的恢复，假如redolog中有事务提交，则进行事务提交修改数据。这样实现了事务的原子性、全都性和长久性。

对于写入重写日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redolopgbuffer）中，然后根据肯定的条件写入日志文件。

当对应事务的脏页写入到磁盘之后，redolog的使命也就完成了，重做日志占用的空间就可以重用（被掩盖）。

通过innodb_log_buffer_size可以配置重写日志缓冲的的大小。

从日志缓冲写入磁盘有两个时间点：

1、主线程每秒都会将重做日志缓冲写入磁盘的重做日志文件，不论事务是否已经提交;

2、另外一个是由参数innodb_flush_log_at_trx_mit掌握，表示在事务提交时，处理重做日志;

参数innodb_flush_log_at_trx_mit可设的值有0、1、2。0代表当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新。而1和2不同的地方在于：1是在mit时将重做日志缓冲同步写到磁盘；2是重做日志异步写到磁盘，即不能完全保证mit时确定会写入重做日志文件，只是有这个动作。

八、回滚日志

除了重做记录redolog外，当进行数据修改时还会记录undolog，undolog用于数据的撤回操作，它记录了修改的反向操作，比如，插入对应删除，修改对应修改为原来的数据，通过undolog可以实现事务回滚，并且可以依据undolog回溯到某个特定的版本的数据，实现MVCC，也即非锁定读。

事务开头之前，将当前的版本生成undolog，undo也会产生redo来保证undolog的牢靠性，事务提交之后，undolog并不能立马被删除，而是放入待清理的链表，由purge线程推断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，打算是否可以清理undolog的日志空间。

默认状况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。因此共享表空间可能会变的很大，默认状况下，也就是undo日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。

九、ACID

ACID是事务的四大特性。

1、原子性(Atomicity)

一个事务必需被视为一个不行分割的最小工作单元，整个事务中的全部操作要么全部提交胜利，要么全部失败回滚，对于一个事务来说，不行能只执行其中的一部分操作，这就是事务的原子性。

2、全都性(Consistency)

数据库总是从一个全都性的状态转换到另一个全都性的状态。

3、隔离性(Isolation)

一个事务所做的修改在最终提交以前，对其他事务是不行见的。

4、长久性(Durability)

一旦事务提交，则其所做的修改不会永久保存到数据库

十、事务的隔离性问题

假如不考虑事务的隔离性，会消失以下问题：

1、脏读

一个事务在更新一条记录，未提交前，其次个事务读到了第一个事务更新后的记录，那么其次个事务就读到了脏数据，会产生对第一个未提交数据的依靠。一旦第一个事务回滚，那么其次个事务读到的数据，将是错误的脏数据。

2、幻读

一个事务按相同的查询条件查询之前检索过的数据，确发觉检索出来的结果集条数变多或者削减（由其他事务插入、删除的），类似产生幻觉。

3、不行重复读（虚读）

一个事务在读取某些数据后的一段时间后，再次读取这个数据，发觉其读取出来的数据内容已经发生了转变，就是不行重复读。

幻读和不行重复读的区分在于幻读是数据条数发生了变化（插入、删除），而不行冲突读在于数据发生了更新，前后读取的结果不全都。

十一、事务隔离级别

脏读、不行重读度、幻读，其实都是数据库的全都性问题，必需由肯定的事务隔离机制来解决，InnoDB下的事务隔离级别有以下四种：

1、读未提交(Readunmitted)

一个事务可以读取到另一个事务未提交的修改。这会带来脏读、幻读、不行重复读问题。（基本没用）

2、读已提交(RC,ReadCommit)

一个事务只能读取另一个事务已经提交的修改。其避开了脏读，但仍旧存在不行重复读和幻读问题。

3、可重复读(RR,RepeatableRead)

同一个事务中多次读取相同的数据返回的结果是一样的。其避开了脏读和不行重复读问题，但幻读依旧存在。

4、串行化(Serializable)

事务串行执行。避开了以上全部问题。MySQL默认的级别是:Repeatableread可重复读，级别越高，数据越平安，但性能越低。

十二、MVCC

MVCC(Mutil-VersionConcurrencyControl)，多版本并发掌握，是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增加并发性的强大的技术，由于这样的一来的话查询就不用等待另一个事务释放锁。

MVCC的实现是通过保存数据在某个时间点的快照(redolog)来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据全都的视图。依据事务开头的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

在每一行数据中额外保存两个隐蔽的列：当前行创建时的版本号和删除时的版本号（可能为空）。这里的版本号并不是实际的时间值，而是系统版本号。每开头新的事务，系统版本号都会自动递增。事务开头时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本掌握的目的。

默认的隔离级别（REPEATABLEREAD）下，增删查转变成了这样：

SELECT：读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。

INSERT：将当前事务的版本号保存至行的创建版本号

UPDATE：新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号

DELETE：将当前事务的版本号保存至行的删除版本号

十三、InnoDB索引结构

Mysql索引用的B+树作为数据结构；Mysql中B+Tree在经典B+Tree的基础上进行了优化，增加了挨次访问指针。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有挨次访问指针的B+Tree。这样就提高了区间访问性能：假如要查询key为从18到49的全部数据记录，当找到18后，只需顺着节点和指针挨次遍历就可以一次性访问到全部数据节点，极大提到了区间查询效率(无需返回上层父节点重复遍历查找削减IO操作)。

MyISAMInnoDB都使用B+Tree索引结构。但是底层索引存储不同，MyISAM采纳非聚簇索引，而InnoDB采纳聚簇索引。

聚簇索引：索引和数据文件为同一个文件。

非聚簇索引：索引和数据文件分开的索引。

MyISAM索引原理：采纳非聚簇索引-MyISAMmyi索引文件和myd数据文件分别，索引文件仅保存数据记录的指针地址。叶子节点data域存储指向数据记录的指针地址。

MyISAM索引根据B+Tree搜寻，假如指定的Key存在，则取出其data域的值，然后以data域值-数据指针地址去读取相应数据记录。帮助索引和主索引在结构上没有任何区分，只是主索引要求key是唯一的，而帮助索引的key可以重复。

InnoDB索引采纳聚簇索引，InnoDB数据索引文件为一个idb文件，表数据文件本身就是主索引，相邻的索引接近存储。叶节点data域保存了完整的数据记录(数据+主索引)。叶子节点直接存储数据记录，以主键id为key,叶子节点中直接存储数据记录。(底层存储结构:**frm-表定义、ibd:innoDB数据索引文件)

由于InnoDB采纳聚簇索引结构存储，索引InnoDB的数据文件需要根据主键聚集，因此InnoDB要求表必需有主键(MyISAM可以没有)。假如没有指定mysql会自动选择一个可以唯一表示数据记录的列作为主键，假如不存在这样的列，mysql自动为InnoDB表生成一个隐含字段(6个字节长整型)作为主键。InnoDB的全部帮助索引都引用数据记录的主键作为data域。

十四、InnoDB锁类型

1、加锁机制

乐观锁与悲观锁是两种并发掌握的思想，可用于解决丢失更新问题。

2、乐观锁

每次去取数据，都