MySql分层结构：

MySql分层结构.png

1.MySQL中InnoDB与MyISAM的区别：

a.InnoDB引擎是MySql的默认引擎（从5.5.8版本开始），支持事务，采用行级锁，支持外键，默认非锁定读，其设计主要作为OLTP（Online Transaction Processing，即在线事务处理）的应用。
b.MyISAM引擎不支持事务和表锁设计，但支持全文索引，主要面向OLAP（Online Analytical Processing，即在线分析处理）。

2.InnoDB的体系结构

InnoDB引擎体系结构.jpg

2.1.后台线程

主要负责刷新内存池中的数据，保存缓存池中的内存缓存的是最近的数据，还负责将修改的数据文件刷新的磁盘文件，同时保证数据库发生异常的情况下mysql能够恢复到正常运行状态。

2.1.1 Master Thread

作为核心后台线程，Master Thread 负责缓冲池中的数据异步刷新到磁盘，保证数据的一致性。
Master Thread的演进：

1.InnoDB 1.0.*之前

算法结构

public void masterThread() {
    while(true) {

       for(int i=0;i<10;i++) {
            /**
 1.将重做日志缓存刷到磁盘里的重做日志文件，即使事务还没有提交;
 2.合并插入缓存（以当前1秒内IO次数是否小于5为界限，小于5在执行）;
3.刷最多100个脏页到磁盘（脏页比例是否超过设置的阀值）；
4.如果当前没有用户活动，则切换到background循环中。
            */
             do thing per second;
             Thread.sleep(1000*1);
          }


/**
 1.刷100个脏页到磁盘（在10秒内IO次数小于200次的情况下）;
 2.合并最多5个插入缓存;
3.将日志缓冲刷到磁盘；
4.删除无用的undo页；
5.刷100个或10个脏页到磁盘（当脏页比例大于70%时刷100个脏页，否则刷10个脏页）
            */
    do thing per 10 seconds;

    }

}

background循环：

1.删除无用的undo页；
2.合并20个插入缓冲；
3.调回到主循环

2.InnoDB 1.2.*之前

通过分析InnoDB 1.0.*版本，我们知道InnoDB采用硬编码的方式实现，InnoDB 1.2.*版本支持用户来自定义IO吞吐量，通过innodb_io_capacity参数来设置，这样就适应了高性能和低性能的磁盘，另外还有innodb_max_dirty_pages_pct来设置脏页在缓冲池的百分比。另外还可以配置innodb_adaptive_flushing和innodb_purge_batch_size可以设置。

3.InnoDB 1.2.*：

算法结构：

if(innodb is idle) {
     do 10秒间隔的操作
} else {
     do 1秒间隔的操作
}

另外，对于刷脏页的操作，innoDB从Master Thread 分离出了单独线程Page Cleaner Thread，减轻了Master Thread的工作。

2.1.2 IO Thread

InnoDB大量使用了Async IO 来处理IO请求，极大的提高了数据库性能，IO Thread主要负责这些IO请求的回调（call back）。

2.1.3 Purge Thread

当事务被提交之后，其所使用的undolog就不再需要了，Purge Thread主要负责回收这些undo数据所占用的空间。

2.1.4 Page Cleaner Thread

主要负责脏页（即缓冲池中的页和磁盘中的页数据不一致）的刷新操作，减轻Master Thread的负担，提高引擎性能。

2.2.内存

2.2.1 缓冲池

缓存为了解决CPU和磁盘之间速度的鸿沟，来提高基于磁盘的数据库系统的整体性能。针对数据库页的修改，InnoDB则先修改缓冲池中的页，然后再以一定的频率刷新到磁盘上，而不是每次页有修改就刷新到磁盘上。

2.2.2 重做日志缓冲

InnoDB首先将重做日志信息放入到缓冲区，然后按照一定的频率将其刷新到外部磁盘的重做日志文件。一般情况下1秒刷新就会刷新一次，因此重做日志缓冲缓冲的大小不需要很大，8MB基本都能够满足。在下列三种情况下会刷新重做日志缓冲到外部磁盘的重做日志文件：
a.Master Thread 每秒将重做日志缓冲刷新到重做日志文件；
b.每次事务提交时会将重做日志缓冲刷新到重做日志文件；
c.当重做日志缓冲剩余空间小于1/2时，将重做日志缓冲刷新到重做日志文件。

2.2.3 额外内存池

在InnoDB引擎中，对数据进行内存分配时，首先充额外的内存池中进行申请，当该区域的内存不够时，才会从缓冲池中申请。

2.3 CheckPoint（检查点）技术

为了协调CPU和磁盘速度的鸿沟，因此页的操作都是在缓冲池中进行的。若每一次的页变化都将其刷新到磁盘中，那么开销巨大。InnoDB在事务提交时，采用先写重做日志，再修改页，这样系统宕机时，可以通过重做日志来恢复，而不会导致数据丢失。CheckPoint实现了在数据库宕机时，不需要重做所有日志，因为在CheckPoint之前的数据都已经刷到了磁盘里，故数据库只需要对CheckPoint之后的重做日志进行恢复即可，这样极大地缩短了恢复时间，同样也减少了重做日志所占的磁盘空间（只需要保存CheckPoint之后的重做日志）。
CheckPoint刷数据的两种方式：
a.Sharp CheckPoint 每次发生数据库关闭时，将所有脏页刷到磁盘，对数据库可用性产生很大影响。
b.Fuzzy CheckPoint 每次发生数据库关闭时，将部分脏页刷到磁盘，而不是所有脏页。

2.4 InnoDB特性

2.4.1 插入缓存（Insert Buffer）

针对非聚集索引的更新和插入操作（要求是非聚集索引，且索引不是unique的，因为数据库不去查找索引页来判断插入记录的唯一性），不是每次都是直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入；否则先放到一个Insert Buffer对象中，然后再以一定的频率和情况将Insert Buffer对象和非聚集索引叶子节点的进行合并（即多步合成一步），大大提高了非聚集索引的插入性能。缺点：遇到段时间内大量写的情况，Insert Buffer会占用使用过多的缓冲池空间，影响性能，如果此时宕机，会导致大量的Insert Buffer对象中的数据，没有合并到非聚集索引中，影响系统恢复效率。
Insert Buffer也是由一个B+树构成，MySql 4.1版本之后，每个数据库实例只有一个Insert Buffer对象。

2.4.2 两次写（Double Write）

在对系统重做日志前，用户需要一个页的副本，当写入失败时，先通过副业来还原对应的页，再执行重做日志。补充了重做日志只对页进行操作的粗粒度的弊端。

2.4.3 自适应Hash索引（Adaptive Hash Index）

Hash算法在不冲突的情况下，时间复杂度为O(1)，效率明显高于B+树。InnoDB通过监控表上的索引页的查询，如果监控到通过建立Hash索引可以带来速度提升，则InnoDB会通过缓冲池的B+树页来构造，创建Hash索引，不需要人为控制调整，故称为自适应Hash索引。

2.4.4 异步IO（Asynchronous IO）

InnoDB中，read ahead方式的读取都是通过AIO实现的，另外脏页的刷新（写入到磁盘）也是由AIO完成。

3.数据存储

MySql InnoDB的逻辑存储结构.jpg

说明：
namespace表空间：所有数据都保存在namespace中，如果innodb_file_per_table停用（默认停用），所有的数据保存在共享表空间ibdata1中；innodb_file_per_table启用，每张表的表空间存放的有数据、索引和插入缓存Bitmap页数据，其他如回滚undo信息，插入缓存索引页，系统事务信息，二次写缓冲等还是保存在共享表空间ibdata1中。
segment段：namespace由segment构成，常见的有数据段（B+树的叶子节点），索引段（B+树的非叶子节点），回滚段等。segment由Innodb自动管理，DBA无法管理。
extent区：segment由extent构成，extent由连续的page组成的空间，大小都是1MB，不可设置。
page页：page是InnoDB磁盘管理的最小单位，默认大小为16KB，可设置。
row行： MySQL数据按行存放，即数据库中的一条数据记录。

4.事务（Transaction）

4.1 事务的特性：

原子性(Atomicity)：由redo log和undo log实现；
一致性(Consistency)：由undo log实现；
隔离性（Isolation）：由锁实现；
持久性(Durability)：由redo log实现。

4.2 事务隔离级别：

1.RU(Read UnCommitted) 读未提交;
2.RC(Read Commited) 不可重复读；
3.RR(Repeatable Read) 可重复读；
4.Serializable 串行化。

4.3 分布式事务：

分布式事务指允许多个独立的事务资源（Transactional Resources，如一个事务涉及到一个是MySQL，一个是Oracle，还有一个SQLServer）参与到一个全局事务中。
MySQL通过XA事务来支持分布式事务，XA事务允许不同数据库之间的分布式事务。在分布式事务时，InnoDB的事务隔离级别必须是最高级别，即Serializable 串行化。

分布式事务.jpg

分布式事务采用两段式提交（two-phase commit）方式:
第一阶段：所有参与全局事务的节点都开始准备，然后告诉事务管理器他们准备好提交了。
第二阶段：事务管理器告诉资源管理器执行commit或者rollback，如果任何一个节点显示不能commit，则所有节点都被告知需要rollback。

备注：应避免循环commit，自动commit和自动rollback。

5.索引

InnoDB支持的索引有:

5.1 B+（Balance+）树索引

B+树是平衡二叉树的扩展，B+树索引不能够找到一个给定键值的具体行，B+树索引找到的知识查找数据行所在的页（即InnoDB进行磁盘管理的最小单位，默认为16K），让后数据库把页读入到内存，再在内存中进行查找，最后得到要查找的数据。
在数据库中，B+树的一般高度都在2~4层，太高了会影响查询性能，毕竟搜索到每一层都需要IO。

5.1.1 聚集索引

聚集索引（clustered index）按照每张表的主键构造一颗B+树，同时叶子节点（又称数据页）存放的即为整张表的行记录数据（注意：聚集索引的存储是逻辑上连续的，而不是物理上连续的），即叶子节点上保存了完整的每行的记录，而在非叶子节点中存放的只有键值和指向子节点的偏移量，每个聚集索引的叶子节点都通过一个双向链表连接。 因为叶子节点只能按照一颗B+树进行排序，因此每张表只能够创建一个聚集索引。

聚集索引和主键的关系与区别：

关系：
a.如果一个表定义了主键，那么这个主键就是聚集索引；
b.如果表没有定义主键，那么该表的第一个唯一非空索引作为聚集索引；
c.如果没有主键和唯一非空索引，那么innodb内部会生成一个隐藏的主键作为聚集索引（隐藏主键是一个6字节的自增列）。
区别：

属性	主键	聚集索引
个数	一个表可以创建多个主键	一个表只能创建一个聚集索引
是否允许为NULL	不可以	可以
是否要求唯一	要求	不要求，可以通过单独设置为否限制唯一

自增主键的优缺点：

优点：速度快，数字型空间小，数据库可以自己控制主键自动增长；
缺点：插入指定ID的数据时，很难保证主键冲突；分库和分表情况下，自增主键将是一个很大的挑战。

5.1.2 非聚集索引

非聚集索引的叶子节点并不包含行记录的所有数据，叶子节点包含了键值和书签（书签告诉InnoDB哪里可以找到与索引相应的行数据，即相应行数据的聚集索引键），因此效率和聚集索引没法比，几乎耗时是聚集索引的两倍，不过一张表上可以创建多个非聚集索引。

5.1.3 Cardinality值

Cardinality值表示索引中不重复记录的预估值，不是一个准确值，因为为了不影响数据库性能，Cardinality值是通过采样获得的。当Cardinality/Table_total_count值越大（最大值为1），B+树索引创建的价值越大；当值很小时，没有必要创建B+索引。

5.2 全文检索

为解决SQL的like '%XXX%' 不走索引的问题，MySQL使用了倒排索引（和Elastic Search一样），从MySQL 5.7.6开始已经支持中文了，但只能在CHAR、VARCHAR或者TEXT的字段上创建全文索引，而且要使用相同的字符集和排列规则，且每张表只能有一个全文索引。
正式由于这些问题，所以搜索很难达到Elastic Search的性能，故很少使用MySql的全文检索。

6.锁

锁用来管理对共享资源的并发访问，从而提供数据的完整性和一致性。

锁的类型：

共享锁：允许事务读一行数据；
排它锁：允许事务删除或更新一行数据。
行锁的算法：
1.Record Lock 单个行记录上的锁，总是锁住索引记录，如果没有索引，则锁定隐式主键；
2.Gap Lock 间隙锁 锁定一个范围，但不包含记录本身；
举例来说，假如student表中只有1001条记录，其student_id的值分别是1,2,...,1000,1001，下面的SQL：
SELECT * FROM student WHERE student_id > 1000 FOR UPDATE
是一个范围条件的检索，InnoDB不仅会对符合条件的student_id值为1001的记录加锁，也会对student_id大于101的“间隙”加锁，虽然这些记录并不存在。
3.Next-Key Lock 即Record Lock + Gap Lock 锁定一个范围，并包含记录本身。

6.1 脏读

脏读是指在不同的事务环境下，当前事务可以读到其他事务未提交的数据。

脏读.jpg

6.2 不可重复读

不可重复读是指在一个事务内，多次读取同一个数据集合，得到的结果集不一样（其他事务同时在做修改这个结果集包含的内容，并且已经commit）

不可重复读.jpg

6.3 幻读

幻读并不是值两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。（转自：https://segmentfault.com/a/1190000016566788）

事务-幻读.jpg

6.4 丢失更新

丢失更新.jpg

6.5 死锁

死锁是指两个或两个以上事务在执行过程中，因为相互争夺共享资源而造成的一种互相等待的现象。
解决死锁的方式：
1.超时阀值设置；
2.通过等待图来主动死锁检测，如果等待图中存在回路，就代表存在死锁。；

7 主从复制

MySQL主从复制的步骤：
+a.Master把数据修改记录保存到二进制日志binary log中；
+b.Slave把Master的binary log日志copy到自己的中继日志replay log中;
+c.Slave重做中继日志，将更改作用到自己的数据库上，以达到主从数据的最终一致性。

MySQL_copy_snapshot.jpg

7.1复制的方式

a.基于SQL语句的复制

Master记录那些更改数据的SQL到日志文件，Slave获取该SQL日志文件，然后再执行一遍Master执行过的SQL。
优点：
实现简单，主从数据传输量很小，占用带宽很少。如果出现问题可以很好的根据SQL语句去定位。
缺点：
更新必须是串行的，另外，Master和Slave执行SQL的时间和环境不同，会出现Master执行成功，Slave执行失败的问题，而且错误具有累积性。如果在使用触发器或者存储过程，最好不要使用基于SQL的复制模式。

b.基于行的复制

Master将实际数据记录保存到Binary log中，Slave获取该Binary log文件，然后更改对应的数据行。
优点：
使用场景广，可以正确的复制每一行，错误不具有累加性。
缺点：
针对一次修改数据量很大的情况，该方法会保存大量的Binary log数据，给Master和复制造成很大的负载，影响数据库性能。由于没有记录SQL语句，出现问题后，无法判断是那些SQL导致的问题。

c.折中

MySQL能够在这两种复制模式间动态切换，默认情况下使用基于SQL语句的复制，如果发现SQL语句无法正确的复制，就切换到基于行的复制模式。

7.2 复制的常见问题

Master过大的包，过大的复制延迟，受限的宽带，磁盘空间，主从系统配置不一样，加锁引起的锁争用，数据损坏，以及不确定性更改（update ... limit 5）等问题。