MySQL核心原理

前言

计算机专业，大学课程会有一门课是学数据库的，参考教材是《数据库系统基础教程》，用来练习的数据库是MySQL。这是一不很不错的书，但是可能很多人那时候只是为了考试，只是记住了概念。面试的时候早已经忘光了，又或者在脑海中没有形成一个体系。写这篇文章主要也是将自己学到的东西尝试系统化起来，消化学到的东西同时能够帮助，其他学习了解MySQL或者准备面试的人提供一些帮助。原创不易，多多关注哦～

除了上面这本书，有两个相关的推荐，推荐阅读《从根上理解MySQL是怎样运行的》、一个是极课时间上的《MySQL45讲》。

（这个有资源，关注下面公众号回复1即可获取）

写文章实属不易，你的支持就是我创作的动力～

简介

MySQL 是最流行的关系型数据库软件之一，由于其体积小、速度快、开源免费、简单易用、维护成本低等，在集群架构中易于扩展、高可用，因此深受开发者和企业的欢迎。

在市面上常用的数据库如下图:

Oracle和MySQL是世界市场占比最高的两种数据库。

IOE指的是IBM的服务器，Oracle数据库，EMC存储设备，这些一般都是有钱的公司产品采购，例如银行、电信、石油、证券等大企业。

Oracle目前处于垄断，有钱的大企业会采用，互联网企业之外使用第一。

MySQL是互联网企业使用频率比较高的数据库。

发展历程

MySQL从最初的1.0、3.1再到后来的8.0，发生了各种各样的变化。被Oracle收购后，MySQL的版本演化出了多个分支，除了需要付费的MySQL企业版本，还有很多MySQL社区版本。还有一条分支非常流行

的开源分支版本叫Percona Server，它是MySQL的技术支持公司Percona推出的，也是在实际工作中经常碰到的。Percona Server在MySQL官方版本的基础上做了一些补丁和优化，同时推出了一些工具。另

外一个非常不错的版本叫MariaDB，它是MySQL的公司被Oracle收购后，MySQL的创始人Monty先生，按原来的思路重新写的一套新数据库，同时也把 InnoDB 引擎作为主要存储引擎，也算 MySQL 的

分支。

MySQL 应用架构演变

常规的网站在不同的并发访问量级和数据量下，架构会有不同的演变。

1）单机单库
一个简单的小型网站或者应用背后的架构可以非常简单, 数据存储只需要一个MySQL实例就能满足数据读取和写入需求，处于这个的阶段系统，一般会把所有的信息存到一个MySQL实例里面。

瓶颈:

• 数据量太大，超出一台服务器承受
• 读写操作量太大，超出一台服务器承受
• 一台服务器挂了，应用也会挂掉（可用性差）

2）主从架构

主要解决单体架构下的高可用和读扩展问题，通过给实例挂载从库解决读取的压力，主库宕机也可以通过主从切换保障高可用。在MySQL的场景下就是通过主从结构（双主结构也属于特殊的主从），主库抗写压力，通过从库来分担读压力，对于写少读多的应用，主从架构完全能够胜任。

3）分库分表

遇到写入瓶颈和存储瓶颈时，可以通过水平拆分来解决，水平拆分和垂直拆分有较大区别，垂直拆分拆完的结果，每一个实例都是拥有全部数据的，而水平拆分之后，任何实例都只有全量的1/n的数据。以上图所示，将Userinfo拆分为3个分片，每个分片持有总量的1/3数据，3个分片数据的总和等于一份完整数据。

引出新的问题：

• 数据如何路由？ (一般可以采用范围拆分，List拆分、Hash拆分等)。
• 如何保持数据的一致性？

4） 云数据库

云数据库（云计算）现在是各大IT公司内部作为节约成本的一个突破口，对于数据存储的MySQL来说，如何让其成为一个saas（Software as a Service）是关键点。

MySQL作为一个saas服务，服务提供商负责解决可配置性，可扩展性，多用户存储结构设计等这些疑难问题。

MySQL 架构原理

体系架构

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

图中其他的模块比较好理解，这里特别说明下系统文件层主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

【日志文件】

• 错误日志（Error log）：默认开启，show variables like ‘%log_error%’

• 通用查询日志（General query log）：记录一般查询语句，show variables like ‘%general%’;

• 二进制日志（binary log）：
  记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行时长；但是它不记录select、show等不修改数据库的SQL。
  主要用于数据库恢复和主从复制。

  show variables like ‘%log_bin%’; //是否开启

  show variables like ‘%binlog%’; //参数查看

  show binary logs;//查看日志文件

• 慢查询日志（Slow query log）:记录所有执行时间超时的查询SQL，默认是10秒。

  show variables like ‘%slow_query%’; //是否开启

  show variables like ‘%long_query_time%’; //时长

【配置文件】
用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。

【数据文件】

​ db.opt 文件：记录这个库的默认使用的字符集和校验规则。

​ frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。

​ MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个.MYD 文件。

​ MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。

​ ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种。

​ 表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张InnoDB 表对应一个 .ibd 文件。
​ 共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。

​ ibdata1 文件：系统表空间数据文件，存储表元数据、Undo日志等 。

​ ib_logfifile0、ib_logfifile1 文件：Redo log 日志文件。

【pid 文件】

​ pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id。

【socket文件】

​ socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

运行机制

存储引擎

​ 存储引擎在MySQL的体系架构中位于第三层，负责MySQL中的数据的存储和提取，是与文件打交道的子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎。使用show engines命令，就可以查看当前数据库支持的引擎信息。

在5.5版本之前默认采用MyISAM存储引擎，从5.5开始采用InnoDB存储引擎。

• InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全。
• MyISAM：不支持事务和外键，访问速度快。
• Memory：利用内存创建表，访问速度非常快，因为数据在内存，而且默认使用Hash索引，但是一旦关闭，数据就会丢失。
• Archive：归档类型引擎，仅能支持insert和select语句。
• Csv：以CSV文件进行数据存储，由于文件限制，所有列必须强制指定not null，另外CSV引擎也不支持索引和分区，适合做数据交换的中间表
• BlackHole: 黑洞，只进不出，进来消失，所有插入数据都不会保存。
• Federated：可以访问远端MySQL数据库中的表。一个本地表，不保存数据，访问远程表内容。
• MRG_MyISAM：一组MyISAM表的组合，这些MyISAM表必须结构相同，Merge表本身没有数据，对Merge操作可以对一组MyISAM表进行操作。

InnoDB和MyISAM是使用MySQL时最常用的两种引擎类型，两者区别主要如下：

• 事务和外键

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

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

• 锁机制

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

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

• 索引结构

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

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

• 并发处理能力

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

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

• 存储文件

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

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

适用场景

MyISAM

• 不需要事务支持（不支持）
• 并发相对较低（锁定机制问题）
• 数据修改相对较少，以读为主
• 数据一致性要求不高

InnoDB

• 需要事务支持（具有较好的事务特性）
• 行级锁定对高并发有很好的适应能力
• 数据更新较为频繁的场景
• 数据一致性要求较高
• 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO

总结

两种引擎该如何选择？

• 是否需要事务？有，InnoDB
• 是否存在并发修改？有，InnoDB
• 是否追求快速查询，且数据修改少？是，MyISAM
• 在绝大多数情况下，推荐使用InnoDB

存储引擎详细对比如下：

InnoDB存储结构

内存结构

内存结构主要包括Buffffer Pool、Change Buffffer、Adaptive Hash Index和Log Buffffer四大组件。

1）Buffer Pool

缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，
以后使用可以减少磁盘IO操作，提升效率。

Page管理机制

• free page ： 空闲page，未被使用
• clean page：被使用page，数据没有被修改过
• dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致。

针对上述三种page类型，InnoDB通过三种链表结构来维护和管理

• free list ：表示空闲缓冲区，管理free page
• flflush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。
  脏页即存在于flflush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flflush链表负责管理脏页的刷盘操作。
• lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，
  存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少数据，占37%。

改进型LRU算法维护

• 普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰
• 改性LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。每当有新的page数据读取到buffffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

Buffffer Pool配置参数

• show variables like ‘%innodb_page_size%’; //查看page页大小
• show variables like ‘%innodb_old%’; //查看lru list中old列表参数
• show variables like ‘%innodb_buffffer%’; //查看buffffer pool参数

建议：将innodb_buffffer_pool_size设置为总内存大小的60%-80%，innodb_buffffer_pool_instances可以设置为多个，这样可以避免缓存争夺。

2）Change Buffffer

写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。
并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。ChangeBuffffer占用BufffferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。参数innodb_change_buffffer_max_size;当更新一条记录时，该记录在BufffferPool存在，直接在BufffferPool修改，一次内存操作。如果该记录在BufffferPool不存在（没有命中），会直接在ChangeBuffffer进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进性磁盘读取，然后再从ChangeBuffffer中读取信息合并，最终载入BufffferPool中。

写缓冲区，仅适用于非唯一普通索引页，为什么？

如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufffferPool中，然后在缓冲池修改，不会在ChangeBuffffer操作。

为什么change buffer只对辅助索引生效？
以insert新增操作为例，插入顺序一般是按照主键递增顺序进行插入的，插入聚集索引（主键索引）一般是顺序的，不需要磁盘的随机读取。这种情况下对聚集索引的修改速度是非常快的，所以不需要进行写缓冲。而对于辅助索引的插入或者更新操作，由于B+树的索引结构的特性决定了辅助索引插入的离散型。所以，对于辅助索引的插入或者更新操作，InnoDB中不是每一次都直接插入到索引页中，而是先判断插入的辅助索引页是否在缓存区中，若在直接插入；若不在，则先放入到change buffer中，然后再以一定频率和情况进行change buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于辅助索引插入的性能。

3）Adaptive Hash Index

自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

4）Log Buffffer

用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。

日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。

LogBuffffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。

LogBuffffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffffer_size参数调大，减少磁盘IO频率。

innodb_flflush_log_at_trx_commit参数控制日志刷新行为，默认为1。

• 0 每隔1秒写日志文件和刷盘操作（写日志文件LogBuffffer–>OS cache，刷盘OS cache–>磁盘文件），最多丢失1秒数据
• 1 事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作
• 2 事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

磁盘结构

InnoDB磁盘主要包含Tablespaces，InnoDB Data Dictionary，Doublewrite Buffffer、Redo Log

• 表空间（Tablespaces）
  用于存储表结构和数据。表空间又分为系统表空间、独立表空间、通用表空间、临时表空间、Undo表空间等多种类型

  • 系统表空间
    包含InnoDB数据字典，Doublewrite Buffffer，Change Buffffer，Undo Logs的存储区域。默认包含任何用户在系统表空间创建的表数据和索引数据。
    系统表空间是一个共享的表空间因为它是被多个表共享的。该空间的数据文件通过参数innodb_data_file_path控制，默认值是ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)。
  • 独立表空间
    默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表空间中。否则，innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩（commpressed）行格式。
  • 通用表空间
    为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。
  • 撤销表空间（Undo Tablespaces）撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0。参数值为0表示使用系统表空间ibdata1;大于0表示使用undo表空间undo_001、undo_002等。
  • 临时表空间
    分为session temporary tablespaces 和global temporary tablespace两种。sessiontemporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。
    global temporary tablespace储存用户临时表的回滚段（rollback segments）。mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

• 数据字典（InnoDB Data Dictionary）
  InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。元数据物理上位于InnoDB系统表空间中。
  由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件（.frm文件）中存储的信息重叠。

• 双写缓冲区（Doublewrite Buffffer）
  在BufffferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区。
  如果在page页写入过程中出现操作系统、存储子系统或mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个备份。在大多数情况下，默认情况下启用双写缓冲区，要禁用Doublewrite 缓冲区，可以将innodb_doublewrite设置为0。使用Doublewrite 缓冲区时建议将innodb_flflush_method设置为O_DIRECT。

• 重做日志（Redo Log）
  重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。

  MySQL以循环方式写入重做日志文件，记录InnoDB中所有对Buffffer Pool修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数

  据更新到数据文件。读写事务在执行的过程中，都会不断的产生redo log。默认情况下，重做日志在磁盘上由两个名为ib_logfifile0和ib_logfifile1的文件物理表示。

• 撤销日志 （Undo Log）
  撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。
  撤消日志属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中。

MYSQL 常用的5.7版本与8.0版本主要有如下区别

【MySQL 5.7 】

• 将 Undo日志表空间从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用户自行指定文件大小和数量。
• 增加了 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据。
• Buffffer Pool 大小可以动态修改，无需重启数据库实例。

【MySQL 8.0 】

• 将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来了，以前需要ibdata中数据字典与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了。
• temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建索引，这样加快了处理的速度。
• 用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中。
  将Doublewrite Buffffer从共享表空间ibdata中也分离出来了。

InnoDB线程模型

IO Thread

在 InnoDB 中使用了大量的 AIO（Async IO）来做读写处理，这样可以极大提高数据库的性能。在 InnoDB 1.0 版本之前共有 4 个 IO Thread，分别是 write、read、insert buffer 和 log thread，后来版本将 read thread 和 write thread 分别增大到了4个，一共有10个了。

read thread：负责读取操作将数据从磁盘加载到缓存 page 页（4个线程）

write thread：负责写操作，将缓存脏页刷新到磁盘（4个线程）

insert buffer thread：负责将写缓冲内容刷新到磁盘（1个线程）

log thread：负责将日志缓冲区刷新到磁盘（1个线程）

Purge Thread

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

show variables like '%innodb_purge_threads%';

Page Cleaner Thread

作用是将脏数据刷新到磁盘，脏数据刷盘后相应的 redo log 也就可以覆盖，既可以同步数据，又能达到 redo log 循环使用的目的。会调用 write thread 线程处理。

show variables like ‘%innodb_page_cleaners%’;

Master Thread

Master Thread 是 InnoDB 的主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。

包含：脏页的刷新（page cleaner thread）、undo 页回收（purge thread）、redo 日志刷新（log thread）合并写缓冲等

其内部有两个主处理，分别是每隔 1 秒和 10 秒处理：

每隔 1s 的操作：

• 刷新日志缓冲区，刷到磁盘
• 合并写缓冲数据，根据 IO 读写压力来决定是否操作
• 刷新脏页数据到磁盘，根据脏页比例达到 75% 才操作（innodb_max_dirty_pages_pct 、innodb_io_capacity）

每隔 10s 的操作：

• 刷新脏页数据到磁盘
• 合并写缓冲区数据
• 刷新日志缓冲区
• 删除无用的 undo 页

InnoDB数据文件

InnoDB数据文件存储结构：

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

这里不详细讲每个含义，根据图也比较容易理解。这里特别说下Row行格式。

Row行格式

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更

新时所需的I/O更少。InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。

DYNAMIC和COMPRESSED新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引前缀。每个表的数据分成若干页来存储，每个页中采用B树结构存储；

如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页，该字段被称为页外列。

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数据文件中包含一种rollbacksegment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。

show variables like '%innodb_undo%';

作用

• 实现事务的原子性

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

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

Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，

Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读。

Redo Log

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

数据进行持久化这一特性。

Redo Log写入机制

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

Redo Log相关配置参数

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认为ib_logfifile0和ib_logfifile1。

可以通过下面一组参数控制Redo Log存储：

show variables like '%innodb_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秒的事务提交数

据。

Bin Log

Binary log（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。

Binlog日志是以事件形式记录，Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。

还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景：

• 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
• 数据恢复：通过mysqlbinlog工具来恢复数据。

文件记录模式

• 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语句选择写入模式。

文件结构

MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。

比较常用的Log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。

写入机制

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

• 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区
  Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；

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

• 事务在提交阶段会将产生的log event写入到外部binlog文件中。
  不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。

Redo Log和Binlog区别

• Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。

• Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。

• Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。

索引原理

对于索引的原理，我觉得网上有很多资料讲得很详细，这个东西已经老生常谈了，这里我只是简单说一下，其他的想结合事务、锁更深地去理解MySQL索引。

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

B-Tree结构

• 索引值和data数据分布在整棵树结构中
• 每个节点可以存放多个索引值及对应的data数据
• 树节点中的多个索引值从左到右升序排列

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

B+Tree结构

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
• 叶子节点包含了所有的索引值和data数据
• 叶子节点用指针连接，提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

理解了索引的结构，其实很容易理解以下索引类型的区别：

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。

聚簇索引

聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

InnoDB的表要求必须要有聚簇索引：

• 如果表定义了主键，则主键索引就是聚簇索引
• 如果表没有定义主键，则第一个非空unique列作为聚簇索引
• 否则InnoDB会从建一个隐藏的row-id作为聚簇索引

辅助索引

InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是

为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

非聚簇索引

与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构。

后面关于索引优化这块，个人觉得内容有点多，理解了索引，理解优化这块其实比较简单，这块读者可以参考一些博客的文章，去理解索引优化的内容。

事务以及实现方式

事务的四个特性

原子性：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。
一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
持久性：指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不对其执行结果有任何影响。

实现方式

原子性

事务的原子性是通过undo log日志进行实现的。当事务需要回滚时，InnoDB引擎就会调用undo log进行SQL语句的撤销，实现数据的回滚。当事务对数据库进行修改，InnoDB不仅会记录redo log，还会生成对应的undo log。undo log与redo log不同，它属于逻辑日志，它对SQL语句执行相关的信息进行记录。当发生回滚时，InnoDB引擎会根据undo log日志中的记录做与之前相反的工作。undo log有两个作用，一是提供回滚，二是实现MVCC功能(MVCC的原理，我下面补充)。

持久性

事务的持久性是通过InnoDB存储引擎中的redo log日志来实现的。重做日志（redo log）是InnoDB引擎层的日志，用来记录事务操作引起数据的变化，记录的是数据页的物理修改。InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。redo log有一些细节需要注意，redo log日志的大小是固定的，为了能够持续不断的对更新记录进行写入，在redo log日志中设置了两个标志位置，checkpoint和write_pos，分别表示记录擦除的位置和记录写入的位置。这种结构很像一个循环队列：

.

隔离性

事务之间的隔离，是通过锁机制实现的。当一个事务需要对数据库中的某行数据进行修改时，需要先给数据加锁。加了锁的数据，其它事务是不运行操作的，只能等待当前事务提交或回滚将锁释放。

在许多场景中会利用到不同实现的锁对数据进行保护和同步。而在MySQL中，根据不同的划分标准，还可将锁分为不同的种类（关于锁的内容会在下面详细讲）。

• 按照粒度划分：行锁、表锁、页锁
• 按照使用方式划分：共享锁、排他锁
• 按照思想划分：悲观锁、乐观锁

一致性

一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。
一致性是事务追求的最终目标，原子性、持久性和隔离性，实际上都是为了保证数据库状态的一致性而存在的。

什么是MVCC

MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。

多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。如何生成的多版本？每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，

该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。

实现原理

MVCC最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目

前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）。

快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）

当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。（select… for update 或lock in share mode，insert/delete/update）

以一个更新的例子做说明
假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针，如下图所示。

具体的更新过程如下：

假如一条数据是刚 INSERT 的，DB_ROW_ID 为 1，其他两个字段为空。当事务 1 更改该行的数据值时，会进行如下操作，如下图所示。

• 用排他锁锁定该行；记录 Redo log；
• 把该行修改前的值复制到 Undo log，即图中下面的行；
• 修改当前行的值，填写事务编号，使回滚指针指向 Undo log 中修改前的行。

通过以上的过程，基本可以明白什么是MVCC，该机制已经实现了读读、读写、写读并发处理。
如果想进一步解决写写冲突，可以采用乐观锁或者悲观锁。

锁机制

锁分类

在 MySQL中锁有很多不同的分类。

从操作的粒度可分为表级锁、行级锁和页级锁

表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。

行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。

页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。

从操作的类型可分为读锁和写锁

读锁（S锁）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁（X锁）：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

IS锁、IX锁：意向读锁、意向写锁，属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS或IX锁。

S锁：事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁，需要等记录的S锁全部释放。

X锁：事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作。

从操作的性能可分为乐观锁和悲观锁

乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。

悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

行锁原理

在InnoDB引擎中，我们可以使用行锁和表锁，其中行锁又分为共享锁和排他锁。InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

• RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）
• GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
• Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。

1）select … from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，

InnoDB不加锁

2）select … from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处

理，如果扫描发现唯一索引，可以降级为RecordLock锁。

3）select … from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫

描发现唯一索引，可以降级为RecordLock锁。

4）update … where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以

降级为RecordLock锁。

5）delete … where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降

级为RecordLock锁。

6）insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

悲观锁

悲观锁（Pessimistic Locking），是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制实现。从广义上来讲，前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴。

表级锁

表级锁每次操作都锁住整张表，并发度最低。常用命令如下：

手动增加表锁

lock table 表名称 read|write,表名称2 read|write;

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

表级读锁：当前表追加read锁，当前连接和其他的连接都可以读操作；但是当前连接增删改操作会报错，其他连接增删改会被阻塞。

表级写锁：当前表追加write锁，当前连接可以对表做增删改查操作，其他连接对该表所有操作都被阻塞（包括查询）。

总结：表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。

共享锁（行级锁-读锁）

共享锁又称为读锁，简称S锁。共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

使用共享锁的方法是在select … lock in share mode，只适用查询语句。

总结：事务使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞。

排他锁（行级锁-写锁）

排他锁又称为写锁，简称X锁。排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。使用排他锁的方法是在SQL末尾加上for update，innodb引擎默认会在update，delete语句加上for update。行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁住全表记录。

总结：事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录

锁（select… for update）。如果查询没有使用到索引，将会锁住整个表记录。

乐观锁

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，而是在进行事务提交时再去判断是否有冲突了。乐观锁实现的关键点：冲突的检测。悲观锁和乐观锁都可以解决事务写写并发，在应用中可以根据并发处理能力选择区分，比如对并发率要求高的选择乐观锁；对于并发率要求低的可以选择悲观锁。

乐观锁实现原理

• 使用版本字段（version）

  先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。

• 使用时间戳（Timestamp）

  与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp时间戳。
  也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。