MySQL资料整理

MySQL体系结构

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 查询分析器组件
• 缓冲组件
• 插件式存储引擎
• 物理文件

各存储引擎对比

InnoDB存储引擎

InnoDB存储引擎体系架构

InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责：

• 维护所有进程/线程需要访问的多个内部数据结构
• 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存
• 重做日志（redo log）缓冲
• ......
  

后台线程：

• Master Thread：主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(INSEERT BUFFER)、UNDO页的回收等。
• IO Thread：在InnoDB存储引擎中大量使用AIO来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调处理
• Purge Thread：十五被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。(InnoDB1.1 版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU使用率以及提升存储引擎的性能)
• Page Cleaner Thread： 将之前版本中脏页的刷新操作都放入到单独的线程中来完成。(减轻原Master Thread的工作，进一步提高存储引擎性能)

内存：

1. 缓冲池
   缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。

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

对于数据库页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。

缓冲池中缓存的数据页类型有：

• 索引页
• 数据页
• undo页
• 插入缓冲
• 自适应哈希索引
• InnoDB存储的锁信息
• 数据字典信息

2. LRU List、 Free List和Flush List

LRU(Latest Recent Used): 最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU尾端的页。

InnoDB对LRU算法的优化： LRU列表中还加入midpoint位置，最新访问的页，放入到LRU列表的midpoint位置。midpoint之后的列表称为old列表，之前的列表称为new 列表。可以简单的理解new列表中的页都是最为活跃的热点数据。

优化的原因：若采用朴素LRU算法，在某些SQL操作,如索引或数据的扫描操作，需要访问表中的许多页甚至全部的页，而这些页仅在这次查询操作中需要，并不是活跃的热点数据。如果放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，从而影响缓冲池的效率。

3. 重做日志缓冲

InnoDB先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。

下面三种情况会将重做日志缓冲中内容刷新到外部磁盘的重做日志文件中：

• Master Thread每一秒将重做日志缓冲刷新到外部磁盘的重做日志文件中。
• 每个事务提交时会将重做日志缓冲刷新到重做日志文件中
• 当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件

4. 额外的内存池

在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域内存不够时，会从缓冲池中进行申请。

show variables like 'innodb_buffer_pool_size';

Checkpoint技术：

Checkpoint技术目的时解决：

• 缩短数据库的恢复时间，数据库只需对Checkpoint后的重做日志进行恢复。
• 缓冲池不够用时，将脏页刷新到磁盘
• 重做日志不可用时，刷新脏页

InnoDB关键特性

1. 插入缓冲

Insert Buffer的使用需同时满足以下两个条件：

• 索引是辅助索引
• 索引不是唯一的

问题：

在写密集情况下，插入缓冲会占用过多的缓冲池内存，默认可占用1/2缓冲池内存；
若MySQL发生宕机，势必有大量Insert Buffer没有合并到实际的非聚集索引，恢复需要很长时间。

Change Buffer
从1.0.x版本开始引入Change Buffer。InnoDB可以对DML操作——INSERT、DELETE、UPDEATE，对应Insert Buffer、Delete Buffer、Purge Buffer

2. 两次写

Insert Buffer带给InnoDB性能提升，而doublewrite带给InnoDB数据页的可靠性

在对缓冲池脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。

3. 自适应哈希索引

自适应哈希索引(Adaptive Hash Index)概念：
InnoDB存储引擎会监控对表上个索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。

AHI要求：

• 对这个页的连续访问模式必须是一样的。
• 以该模式访问了100次
• 页通过该模式访问了N次，其中N=页中记录 * 1/16

4. 异步IO

用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成，这就是AIO。

优势，提高磁盘操作性能：

• 不阻塞
• 可以进行IO Merge操作，提高IOPS的性能。

5. 刷新邻接页

Flush Neighbor Page： 当刷新一个脏页时，InnoDB存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新。

好处： 通过AIO将多个IO写入操作合并为一个IO操作。

文件

日志文件

1. 错误日志

查看错误日志文件位置：SHOW VARIABLES LIKE 'log_error';

2. 二进制文件

作用：

• 恢复：某些数据的恢复需要二进制日志
• 复制： 通过复制和执行二进制日志使一台远程的MySQL(slave)与另一台MySQL数据库(master)进行实时同步
• 审计： 用户可以通过二进制日志中信息来进行审计，判断是否有对数据库进行注入的攻击

开启二进制日志会使性能下降1%；

3. 慢查询日志

默认情况，MySQL并不启动慢查询日志，用户需手工将这个参数设为ON
设置命令：

set global slow_query_log='ON';
set global slow_query_log_file='/usr/local/mysql/data/slow.log';
set global long_query_time=1;

查询命令：

show variables like 'long_query_time';
show variables like 'log_slow_queries';
show variables like 'log_queries_not_using_indexes';

将慢查询日志导出及放入一张表中：

mysqldumpslow slow.log
show create table mysql.slow_log;
show variables like 'log_output';
set global log_output= 'TABLE';
select sleep(10);
select * from mysql.slow_log;

4. 查询日志

默认文件名： 主机名.log

索引文件

InnoDB：

• xx.frm: 表结构定义文件
• xx.idb：数据文件

MyISAM：

• xx.frm: 表结构定义文件
• xx.MYD: 数据文件
• xx.MYI：索引文件

重做日志：
文件名：ib_logfile0和ib_logfile1

表

索引组织表

创建主键：

• 首先判断表中是否有非空的唯一索引，如果有，则选择建表时第一个定义的非空唯一索引即为主键(主键的选择根据的时定义索引的顺序)
• 如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针

InnoDB存储引擎的逻辑结构

1. 表空间

可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。默认有个共享表空间ibdata1。

如果启用innodb_file_per_table参数，需要注意每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等还是存放在原来共享表空间中。

2. 段

表空间是由各个段组成，常见的段有数据段、索引段、回滚段等。

• 数据段即为B+树叶子节点(Leaf node segment)
• 索引段即为B+树非叶子节点(Non-leaf node segment)

3. 区

区是由连续页组成的空间，在任何情况下每个区大小都为1MB。默认情况下，存储引擎页的大小为16KB，即一个区中一共有连续64个连续的页。而为保证页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区。

4. 页

页(也可以称块)，是InnoDB磁盘管理的最小单位。默认每个页大小16KB。1.2x版本后也可以通过参数innodb_page_size设置为4k、8k、16k

常见页类型：

• 数据页(B-tree node)
• undo页(undo log page)
• 系统页(System page)
• 事务数据页(Transaction system Page)
• 插入缓冲位图页(Insert Buffer Bitmap)
• 插入缓冲空闲列表页(Insert Buffer Free List)
• 未压缩的二进制大对象页(Uncompressed Blob Page)
• 压缩的二进制大对象页(Compressed Blob Page)

5. 行

InnoDB存储引擎是面向列的，所以数据是按行进行存放的。

InnoDB行记录格式

1. Compact行记录模式

一个页中存放的行数据越多，其性能越高。

在Compact模式下，不管是CHAR还是VARCHAR类型，NULL值都不占任何存储空间。

2. Redundant行记录模式

InnoDB 1.0.x版本开始引入新的文件格式，称为Barracuda文件格式。拥有两种新的记录格式：Compressed和Dynamic

索引与算法

B+树

B+树高度一般都在2-4层

图示B+树插入

若5-7插入70，左旋：

B+树索引

B+树索引可以分为聚集索引和辅助索引

1. 聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

2. 辅助索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行还包含了一个书签(bookmark)，该书签用来告诉InnoDB引擎哪里可以找到与索引相对应的行数据。

哈希索引

时间复杂度为o(1)

全文索引

从InnoDB 1.2.x开始支持全文索引,其采用full inverted index的方式。

全文索引通常使用倒排索引(Inverted index)来实现,它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。通常用关联数组实现，拥有两种表现形式：

• inverted file index：其表现形式为{单词，单词所在文档的id}
• full inverted index: 其表现形式为{单词，(单词所在文档ID，在具体文档中的位置)}

锁

InnoDB中的锁

锁类型：

• 共享锁
• 排他锁

意向锁(Intention Lock)： 将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

• 意向共享锁(IS Lock): 事务想要获得一张表中某几行的共享锁
• 意向排他锁(IX Lock): 事务想获得一张表中某几行的排他锁

在事务隔离级别为READ COMMITTED和REPEATABLE READ下，InnoDB存储引擎采用一致性非锁定读；但READ COMMITTED总是读取被锁定行的最新一份快照数据，而REPEATABLE READ总是读取事务开始时行数据版本。

一致性锁定读：
SELECT ... FOR UPDATE
SELECT ... LOCK IN SHARE MODE

锁的算法

行锁的三种算法：

• Record Lock：当个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next Key Lock：Gap Lock + Record Lock ，锁定一个范围，并且锁定记录本身

锁问题

1. 脏读

违反事务隔离性

脏页：在缓冲池中已经被修改的页，但还没有刷新到磁盘
脏数据：事务对缓冲池中行记录的修改，并且还没有被提交

2. 不可重复读

违反事务一致性

3. 丢失更新

一个事务的更新操作会被另一个事务的更新所覆盖，从而导致数据不一致

串行化解决

死锁

死锁是指两个或两个以上的事务执行过程中，因争夺资源而造成的一种互相等待的现象。

解决死锁方案

• 超时，然后对事务进行回滚
• wait-for graph等待图

事务

事务的分类

InnoDB存储引擎支持扁平事务、带有保存点的事务、链事务、分布式事务。对于嵌套事务并不原生支持。

1. 扁平事务(Flat Transactions)
   其间的操作是原子的，要么都执行，要么都回滚。

主要限制是不能提交或者回滚事务的某一部分，或分几个步骤提交。

2. 带有保存点的扁平事务(Flat Transactions with Savepoints)

保存点用来通知系统应该记住事务当前的状态，以便之后发生错误时，事务能回到保存点当时的状态。

缺点：当发生系统崩溃时，所有的保存点都将消失，因为其保存点是易失的(volatile)，而非持久的。意味着当进行恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行。

3. 链事务(Chained Transactions)

在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。

注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。意味着下一个事务将看到上一个事务的结果。

4. 嵌套事务(Nested Transactions)

嵌套事务时一个层次结构框架，由一个顶层事务控制这各个层次的事务。

子事务既可以提交也可以回滚，但它的提交操作并不马上生效，除非其父事务已经提交。

树中的任意一个事务的回滚会引起它的所有子事务一同回滚，故子事务仅保留ACI特性，不具有D的特性。

5. 分布式事务(Distributed Transactions)

通常时在一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中不同节点。

事务的实现

原子性、一致性、持久性通过数据库的redo log和undo log来完成。

redo恢复提交事务修改的页操作，而undo回滚记录到某个特定版本。

redo通常是物理日志，记录的是页的物理修改操作；undo是逻辑日志，根据每行记录进行记录。

1. redo

重做日志用来实现事务的持久性，由两部分组成：一是内存中的重做日志缓冲(redo log buffer)，其是易失的；二是重做日志文件(reod log file)，其是持久的。

参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略：

• 默认值1，表示事务提交时必须调用一次fsync操作。
• 0，表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而在master thread中每1s会进行一次重做日志文件fsync操作。
• 2，表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓冲中，不进行fsync操作。在这个设置下，当MySQL发生宕机而操作系统正常时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。

log block，重做日志缓存、重做日志文件都是以块(block)的方式进行保存，称重做日志块(redo log block)，每块的大小为512字节。日志块头(log block header)12字节，日志块尾(log block tailer)8字节

重做日志格式

恢复

2. undo

undo存放在数据库内部的一个特殊段(segment)中，这个段称为undo段(undo segment)，undo段位于共享表空间内。

除了回滚操作，undo的另一个作用时MVCC。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

undo log也会产生redo log，因为undo log也需要持久性保护。

3. purge

InnoDB先从history list中找到第一个需要被清理的记录，这里为trx1，清理之后会在trx1的undo log所在的页中继续寻找是否存中可以被清理的记录，这里会找到trx3，接着找到trx5，但是发现trx5被其他事务所引用而不能清理，故再去history list中查找，发现最尾端trx2，接着找到trx2所在页，然后依次把事务trx6、trx4进行清理。

这种设计模式避免大量的随机读取操作，从而提高purge的效率。

4. group commit

分布式事务

• 资源管理器； 提供访问事务资源的方法，通常一个数据库就是一个资源管理器。
• 事务管理器： 协调参与全局事务中各个事务。需要和参与全局事务的所有资源管理器进行通信。
• 应用程序： 定义事务的边界，指定全局事务中的操作。

分布式事务使用两段式提交：

• 第一个阶段，所有参与全局事务的节点都开始准备(PREPARE),告诉事务管理器他们准备好提交了
• 第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT，如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。
  

内部XA事务

不好的事务习惯

1. 在循环中提交
2. 使用自动提交
3. 使用自动回滚