MySQL基础和详解

1-MySQL简介

1.1-关系型数据库与NoSQL

关系型数据库，顾名思义，是指存储的数据之间具有关系。这种所谓的关系通常用二维表格中的行列来表示，即一个二维表的逻辑结构能够反映表中数据的存储关系。

概念总是拗口难懂的。那么简单来说，关系型数据库的存储就是按照表格进行的。数据的存储实际上就是对一个或者多个表格的存储。通过对这些表格进行分类、合并、连接或者选取等运算来实现对数据库的管理。常见的关系型数据库有MySQL、Oracle、DB2和SqlServer等。

非关系型数据库（NoSQL）是相对于关系型数据库的一种泛指，它的特点是去掉了关系型数据库中的关系特性，从而可获得更好的扩展性。NoSQL并没有严格的存储方式，但采用不同的存储结构都是为了获得更高的性能和更高的并发。

NoSQL根据存储方式可分为四大类，键值存储数据库、列存储数据库、文档型数据库和图形数据库。这四种数据的存储原理不尽相同，因而在应用场景上也有些许的差异。一般常用的有作为数据缓存的redis和分布式系统的HBase。

目前常见的数据库排名可见网站：https://db-engines.com/en/ranking

关系型数据库与非关系型数据库本质上的区别就在于存储的数据是否具有一定的逻辑关系，由此产生的两类数据库看的性能和优劣势上也有一定的区别。二者对比可见下图。

1.2-MySQL介绍

在关系型数据库中，MySQL可以说是其中的王者。它是目前最流行的数据库之一，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL数据库具有以下几个方面的优势：

  • 体积小、速度快；
  • 代码开源，采用了 GPL 协议，可以修改源码来开发自己的 MySQL 系统；
  • 支持大型的数据库，可以处理拥有上千万条记录的大型数据库；
  • 使用标准的 SQL 数据语言形式，并采用优化的 SQL 查询算法，有效地提高查询速度；
  • 使用 C 和 C++ 编写，并使用多种编译器进行测试，保证源代码的可移植性；
  • 可运行在多个系统上，并且支持多种语言；
  • 核心程序采用完全的多线程编程，可以灵活地为用户提供服务，充分利用CPU资源。

1.3-MySQL的逻辑架构

MySQL的逻辑架构可分为四层，包括连接层、服务层、引擎层和存储层，各层的接口交互及作用如下图所示。需要注意的是，由于本文将主要讲解事务的实现原理，因此下文针对的都是InnoDB引擎下的情况。

连接层： 负责处理客户端的连接以及权限的认证。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层： 定义有许多不同的模块，包括权限判断，SQL接口，SQL解析，SQL分析优化， 缓存查询的处理以及部分内置函数执行等。MySQL的查询语句在服务层内进行解析、优化、缓存以及内置函数的实现和存储。

引擎层： 负责MySQL中数据的存储和提取。MySQL中的服务器层不管理事务，事务是由存储引擎实现的。其中使用最为广泛的存储引擎为InnoDB，其它的引擎都不支持事务。

存储层： 负责将数据存储与设备的文件系统中，将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互。

1.4-SQL语句的执行流程

客户端请求 —> 连接器（验证用户身份，给予权限） —> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —> 分析器（对SQL进行词法分析和语法分析操作） —> 优化器（主要对执行的sql优化选择最优的执行方案方法） —> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

  1. 当客户端连接到 MySQL 服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过 SSL 证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。
  2. 在正式查询之前，服务器会检查查询缓存，如果能找到对应的查询，则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集。
  3. MySQL 的解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，比如 SQL 的关键字是否正确，关键字的顺序是否正确。而预处理器主要是进一步校验，比如表名，字段名是否正确等。
  4. 查询优化器将解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是根据成本找到这其中最优的执行计划。
  5. 执行计划调用查询执行引擎，而查询引擎通过一系列 API 接口查询到数据。
  6. 得到数据之后，在返回给客户端的同时，会将数据存在查询缓存中。

2-存储引擎

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能 。

MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。

2.1-查看存储引擎

-- 查看支持的存储引擎
SHOW ENGINES

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'

--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename

--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

2.2-设置存储引擎

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

默认情况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表。

2.3-MyISAM和InnoDB

常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。

InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键。

1. 文件存储结构对比

在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，.frm 文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为 数据表名.frm，如user.frm。

查看MySQL 数据保存在哪里：show variables like ‘data%’

MyISAM 物理文件结构为：

  • .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
  • .MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据
  • .MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

InnoDB 物理文件结构为：

  • .frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
  • .ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

2. MyISAM和InnoDB对比

  1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
  2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
  3. InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  4. InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
  5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

❝
一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；

如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

❝
哪个存储引擎执行 select count(*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

  • 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
  • 在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

3-MySQL的数据类型

主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

3.1-整数类型

MySQL数据类型含义（有符号）
tinyint(m)1个字节 范围(-128~127)
smallint(m)2个字节 范围(-32768~32767)
mediumint(m)3个字节 范围(-8388608~8388607)
int(m)4个字节 范围(-2147483648~2147483647)
bigint(m)8个字节 范围(+-9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用。

3.2-浮点型(float和double)

MySQL数据类型含义
float(m,d)单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)双精度浮点型 16位精度(8字节) m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200。

3.3-定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

3.4-字符串(char,varchar,_text)

MySQL数据类型含义
char(n)固定长度，最多255个字符
varchar(n)固定长度，最多65535个字符
tinytext可变长度，最多255个字符
text可变长度，最多65535个字符
mediumtext可变长度，最多2的24次方-1个字符
longtext可变长度，最多2的32次方-1个字符

char和varchar的区别：

  1. char是固定长度，varchar长度可变：
  2. char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
  3. 存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间。
  4. char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
  • 相同点
    1. char(n)，varchar(n)中的n都代表字符的个数；
    2. 超过char，varchar最大长度n的限制后，字符串会被截断。
  • 不同点
    1. char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。
    2. 能存储的最大空间限制不一样：char的存储上限为255字节。
    3. char在存储时会截断尾部的空格，而varchar不会。
    4. char类型的字符串检索速度要比varchar类型的快。

char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。

3.5-二进制数据(_Blob)

  1. _BLOB和TEXT存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。

  2. _BLOB存储的数据只能整体读出。

  3. _TEXT可以指定字符集，_BLO不用指定字符集。

  • BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB
  • TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
  • BLOB 保存二进制数据，TEXT 保存字符数据。

3.6-日期时间类型

MySQL数据类型含义
date日期 ‘2008-12-2’
time时间 ‘12:25:36’
datetime日期时间 ‘2008-12-2 22:06:44’
timestamp自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

3.7-数据类型的属性

MySQL关键字含义
NULL数据列可包含NULL值
NOT NULL数据列不允许包含NULL值
DEFAULT默认值
PRIMARY KEY主键
AUTO_INCREMENT自动递增，适用于整数类型
UNSIGNED无符号
CHARACTER SET name指定一个字符集

4-索引

常见面试问题：

说说你对 MySQL 索引的理解？
数据库索引的原理，为什么要用 B+树，为什么不用二叉树？
聚集索引与非聚集索引的区别？
InnoDB引擎中的索引策略，了解过吗？
创建索引的方式有哪些？
聚簇索引/非聚簇索引，mysql索引底层实现，为什么不用B-tree，为什么不用hash，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？

4.1-索引介绍

  • MYSQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构
  • 索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。
  • 可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。左边的数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。
  • 索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上
  • 平常说的索引，没有特别指明的话，就是B+树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，符合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。

4.2-索引基本语法

1. 创建

  • 创建索引：CREATE [UNIQUE] INDEX indexName ON mytable(username(length));

    如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

  • 修改表结构(添加索引)：ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)

2. 删除

DROP INDEX [indexName] ON mytable

3. 查看

SHOW INDEX FROM table_name\G –可以通过添加 \G 来格式化输出信息

4. 使用ALERT命令

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引，索引值可出现多次。
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)该语句指定了索引为 FULLTEXT ，用于全文索引。

4.4-索引的优缺点

1. 优点

  • 提高数据检索效率，降低数据库IO成本
  • 降低数据排序的成本，降低CPU的消耗

2. 缺点

  • 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

4.5-索引分类

1. 从数据结构角度

  • B+树索引
  • Hash索引
  • Full-Text全文索引
  • R-Tree索引

2. 从物理存储角度

  • 聚集索引（clustered index）
  • 非聚集索引（non-clustered index），也叫辅助索引（secondary index）聚集索引和非聚集索引都是B+树结构

聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个

聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续

聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序

非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序.

索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

3. 从逻辑角度

  • 主键索引：主键索引是一种特殊的唯一索引，不允许有空值
  • 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引
  • 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
  • 唯一索引或者非唯一索引
  • 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

为什么MySQL 索引中用B+tree，不用B-tree 或者其他树，为什么不用 Hash 索引
聚簇索引/非聚簇索引，MySQL 索引底层实现，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？
使用索引查询一定能提高查询的性能吗？为什么?

4.6-索引的底层原理

我们知道，索引的作用是做数据的快速检索，而快速检索的实现的本质是数据结构。通过不同数据结构的选择，实现各种数据快速检索。在数据库中，高效的查找算法是非常重要的，因为数据库中存储了大量数据，一个高效的索引能节省巨大的时间。比如下面这个数据表，如果 Mysql 没有实现索引算法，那么查找 id=7 这个数据，那么只能采取暴力顺序遍历查找，找到 id=7 这个数据需要比较 7 次，如果这个表存储的是 1000W 个数据，查找 id=1000W 这个数据那就要比较 1000W 次，这种速度是不能接受的。

1. 索引底层数据结构

MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

  • 哈希表（Hash）

    哈希表是做数据快速检索的有效利器。

    哈希算法：也叫散列算法，就是把任意值(key)通过哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构。

    考虑这个数据库表 user，表中一共有 7 个数据，我们需要检索 id=7 的数据，SQL 语法是：

    select * from user where id=7;

    哈希算法首先计算存储 id=7 的数据的物理地址 addr=hash(7)=4231，而 4231 映射的物理地址是 0x77，0x77 就是 id=7 存储的额数据的物理地址，通过该独立地址可以找到对应 user_name=’g’这个数据。这就是哈希算法快速检索数据的计算过程。

    但是哈希算法有个数据碰撞的问题，也就是哈希函数可能对不同的 key 会计算出同一个结果，比如 hash(7)可能跟 hash(199)计算出来的结果一样，也就是不同的 key 映射到同一个结果了，这就是碰撞问题。解决碰撞问题的一个常见处理方式就是链地址法，即用链表把碰撞的数据接连起来。计算哈希值之后，还需要检查该哈希值是否存在碰撞数据链表，有则一直遍历到链表尾，直达找到真正的 key 对应的数据为止。

    从算法时间复杂度分析来看，哈希算法时间复杂度为 O（1），检索速度非常快。比如查找 id=7 的数据，哈希索引只需要计算一次就可以获取到对应的数据，检索速度非常快。但是 Mysql 并没有采取哈希作为其底层算法，这是为什么呢？

    因为考虑到数据检索有一个常用手段就是范围查找，比如以下这个 SQL 语句：

    select * from user where id >3;

    针对以上这个语句，我们希望做的是找出 id>3 的数据，这是很典型的范围查找。如果使用哈希算法实现的索引，范围查找怎么做呢？一个简单的思路就是一次把所有数据找出来加载到内存，然后再在内存里筛选筛选目标范围内的数据。但是这个范围查找的方法也太笨重了，没有一点效率而言。

    所以，使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

  • 二叉查找树(BST)

    二叉查找树是一种支持数据快速查找的数据结构，如图下所示:

    二叉查找树的时间复杂度是 O(lgn)，比如针对上面这个二叉树结构，我们需要计算比较 3 次就可以检索到 id=7 的数据，相对于直接遍历查询省了一半的时间，从检索效率上看来是能做到高速检索的。此外二叉树的结构能不能解决哈希索引不能提供的范围查找功能呢？

    答案是可以的。观察上面的图，二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也算是比较容易实现。

    但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。比如以下这个情况，二叉树已经极度不平衡了，已经退化为链表了，检索速度大大降低。此时检索 id=7 的数据的所需要计算的次数已经变为 7 了。

    在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此，简单的二叉查找树存在不平衡导致的检索性能降低的问题，是不能直接用于实现 Mysql 底层索引的。

  • AVL 树和红黑树

    二叉查找树存在不平衡问题，因此学者提出通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

    首先简单介绍红黑树，这是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O（logn）），也就保证了查找效率不会明显减低。比如从 1 到 7 升序插入数据节点，如果是普通的二叉查找树则会退化成链表，但是红黑树则会不断调整树的形态，使其保持基本平衡状态，如下图所示。下面这个红黑树下查找 id=7 的所要比较的节点数为 4，依然保持二叉树不错的查找效率。

    红黑树拥有不错的平均查找效率，也不存在极端的 O(n)情况，那红黑树作为 Mysql 底层索引实现是否可以呢？其实红黑树也存在一些问题，观察下面这个例子。

    红黑树顺序插入 1~7 个节点，查找 id=7 时需要计算的节点数为 4。

    红黑树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 6 次。观察一下这个树的形态，是不是当数据是顺序插入时，树的形态一直处于“右倾”的趋势呢？从根本上上看，红黑树并没有完全解决二叉查找树虽然这个“右倾”趋势远没有二叉查找树退化为线性链表那么夸张，但是数据库中的基本主键自增操作，主键一般都是数百万数千万的，如果红黑树存在这种问题，对于查找性能而言也是巨大的消耗，我们数据库不可能忍受这种无意义的等待的。

    现在考虑另一种更为严格的自平衡二叉树 AVL 树。因为 AVL 树是个绝对平衡的二叉树，因此他在调整二叉树的形态上消耗的性能会更多。

    AVL 树顺序插入 1~7 个节点，查找 id=7 所要比较节点的次数为 3。

    AVL 树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。从树的形态看来，AVL 树不存在红黑树的“右倾”问题。也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题。

    总结一下 AVL 树的优点：

    1. 不错的查找性能（O（logn）），不存在极端的低效查找的情况。
    2. 可以实现范围查找、数据排序。

    看起来 AVL 树作为数据查找的数据结构确实很不错，但是 AVL 树并不适合做 Mysql 数据库的索引数据结构，因为考虑一下这个问题：

    数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 id=7 这个数据我们就要进行磁盘 IO 三次，这是多么消耗时间的。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

    磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，我们可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理了。

  • B树

    下面这个 B 树，每个节点限制最多存储两个 key，一个节点如果超过两个 key 就会自动分裂。比如下面这个存储了 7 个数据 B 树，只需要查询两个节点就可以知道 id=7 这数据的具体位置，也就是两次磁盘 IO 就可以查询到指定数据，优于 AVL 树。

    下面是一个存储了 16 个数据的 B 树，同样每个节点最多存储 2 个 key，查询 id=16 这个数据需要查询比较 4 个节点，也就是经过 4 次磁盘 IO。看起来查询性能与 AVL 树一样。

    但是考虑到磁盘 IO 读一个数据和读 100 个数据消耗的时间基本一致，那我们的优化思路就可以改为：尽可能在一次磁盘 IO 中多读一点数据到内存。这个直接反映到树的结构就是，每个节点能存储的 key 可以适当增加。

    当我们把单个节点限制的 key 个数设置为 6 之后，一个存储了 7 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。

    一个存储了 16 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。相对于 AVL 树而言磁盘 IO 次数降低为一半。

    所以数据库索引数据结构的选型而言，B 树是一个很不错的选择。总结来说，B 树用作数据库索引有以下优点：

    1. 优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；
    2. 尽可能少的磁盘 IO，加快了检索速度；
    3. 可以支持范围查找。
  • B+树

    B 树和 B+树有什么不同呢？

    第一，B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

    第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

    通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。因此 Mysql 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

2. Innodb 引擎和 Myisam 引擎的实现

Mysql 底层数据引擎以插件形式设计，最常见的是 Innodb 引擎和 Myisam 引擎，用户可以根据个人需求选择不同的引擎作为 Mysql 数据表的底层引擎。我们刚分析了，B+树作为 Mysql 的索引的数据结构非常合适，但是数据和索引到底怎么组织起来也是需要一番设计，设计理念的不同也导致了 Innodb 和 Myisam 的出现，各自呈现独特的性能。

MyISAM 虽然数据查找性能极佳，但是不支持事务处理。Innodb 最大的特色就是支持了 ACID 兼容的事务功能，而且他支持行级锁。Mysql 建立表的时候就可以指定引擎，比如下面的例子，就是分别指定了 Myisam 和 Innodb 作为 user 表和 user2 表的数据引擎。

执行这两个指令后，系统出现了以下的文件，说明这两个引擎数据和索引的组织方式是不一样的。

Innodb 创建表后生成的文件有：

  • frm:创建表的语句
  • idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有

  • frm:创建表的语句
  • MYD:表里面的数据文件（myisam data）
  • MYI:表里面的索引文件（myisam index）

从生成的文件看来，这两个引擎底层数据和索引的组织方式并不一样，MyISAM 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式；Innodb 引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式。下面将从底层实现角度分析这两个引擎是怎么依靠 B+树这个数据结构来组织引擎实现的。

  1. MyISAM 引擎的底层实现（非聚集索引方式）

MyISAM 用的是非聚集索引方式，即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

当我们为某个字段添加索引时，我们同样会生成对应字段的索引树，该字段的索引树的叶子节点同样是记录了对应数据的物理地址，然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

  1. Innodb 引擎的底层实现（聚集索引方式）

InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name=’Bob’。

这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY！拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

问题来了，为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢?

其实很简单，因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

在进行 InnoDB 和 MyISAM 特点对比时谈到，MyISAM 查询性能更好，从上面索引文件数据文件的设计来看也可以看出原因：MyISAM 直接找到物理地址后就可以直接定位到数据记录，但是 InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，那当然 MyISAM 查询性能更高。

本文首先探讨了哪种数据结构更适合作为 Mysql 底层索引的实现，然后再介绍了 Mysql 两种经典数据引擎 MyISAM 和 InnoDB 的底层实现。最后再总结一下什么时候需要给你的表里的字段加索引吧：

  1. 较频繁的作为查询条件的字段应该创建索引；
  2. 唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；
  3. 更新非常频繁的字段不适合创建索引。

3. 为什么使用 B 数或 B+ 树？

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B Tree 作为索引结构，主要有以下两个原因：

  • 更少的检索次数

    平衡树检索数据的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。

    红黑树的出度为 2，而 B Tree 的出度一般都非常大。红黑树的树高 h 很明显比 B Tree 大非常多，因此检索的次数也就更多。

    B+Tree 相比于 B-Tree 更适合外存索引，因为 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，检索效率会更高。

  • 利用计算机预读特性

    为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，因此速度会非常快。

    操作系统一般将内存和磁盘分割成固态大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点，并且可以利用预读特性，相邻的节点也能够被预先载入。

  • 使用 B+ 树的理由

    数据库索引采用 B+ 树的主要原因是 B树在提高了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+ 树应运而生。B+ 树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低） 。

4.7-何时创建索引？

1. 哪些情况需要创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 单键/组合索引的选择问题，高并发下倾向创建组合索引
  5. 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
  6. 查询中统计或分组字段

2. 哪些情况不要创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
  4. 频繁更新的字段不适合创建索引（会加重IO负担）
  5. where条件里用不到的字段不创建索引

4.8-MySQL高效索引

覆盖索引（Covering Index）,或者叫索引覆盖， 也就是平时所说的不需要回表操作

  • 就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
  • 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含（覆盖）满足查询结果的数据就叫做覆盖索引。
  • 判断标准使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

4.9-索引失效的场景

  • 不要在查询中使用 !=、not in、not exists

SELECT * FROM goods WHERE STATUS != 0

可优化为：

SELECT * FROM goods WHERE STATUS = 1 OR STATUS = 2

由于在使用not in时，MySQL会进行全表扫描而不利用索引，因为MySQL认为使用索引反而更麻烦。比如，从 1000 个人中把编号为145、761 这两个学生之外的所有学生资料抄写一遍。你是先找出这两个学生的成绩放一边儿，然后再开始抄写，还是直接开始一份挨一份抄写，看到这两个号直接跳过去？

  • 不要在where条件的等号左侧中使用函数

SELECT * FROM goods WHERE YEAR (time) > = '2017'

可优化为：

SELECT * FROM goods WHERE time > = '2017-01-01'

对于带有列的函数计算，MySQL是无法使用索引的（Oracle可以使用函数索引）。在使用函数时，如SUBSTR函数，将’apple’更改为了’app’，此时值发生了改变，导致之前设置的索引无效。

  • MySQL支持前缀索引，但是不支持后缀索引

SELECT * FROM goods WHERE NAME LIKE '%A'

可优化为：

SELECT * FROM goods WHERE NAME LIKE 'A%'

  • 设置联合索引时，索引的建立顺序要和where或者order by条件中的字段顺序相同，如建立name、status（分别为key1、key2）的联合索引，此时以name字段为第一查询顺序的索引有效：
SELECT * FROM goods WHERE NAME = 'A' AND STATUS = 0

SELECT * FROM goods WHERE NAME = 'A'

但以STATUS开头的查询索引都会失效：

SELECT * FROM goods WHERE STATUS = 0 AND NAME='A'

SELECT * FROM goods WHERE STATUS = 0

  • where条件中有or，需将or中的每个字段都加上索引

    SELECT * FROM goods WHERE NAME = 'A' OR STATUS = 0

    若仅设置 name 或 status 其中一个字段为索引，索引无效。

  • 状态值少的字段不宜使用索引

    例如索引列 sex 字段有2个状态值，如果有10w条数据，那么where sex = 0 将访问表中的 5w个数据块，再加上访问索引块，一共要访问大于 5w个的数据块。如果全表扫描，假设10条数据一个数据块，只需访问 1w个数据块，此时全表扫描比利用索引效率更高，因此对于状态值很少的列，不适合建立索引。

  • 数据类型不匹配

    当索引列的数据类型为字符型，按照数字查询会导致索引失效：

    SELECT * FROM goods WHERE card = 1234567890

    可更改为：

    SELECT * FROM goods WHERE card = '1234567890'

    通常情况下，当我们为字符类型的字段赋值时需要加入单引号，但是如果把一个纯数字的字符串赋值给一个字符型的字段时，可以不用加单引号，因为此时数据库系统会自动把这串数字转换为字符型数据。但当我们进行查询（where 条件中包含索引字段）时，如果采用纯数字，数据库会采用全表扫描，而非引用索引。我个人的理解是，可以将引号看做是一种函数，失效原因则与在 where条件左侧使用函数导致索引失效的原理相同。

5-MySQL锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

数据库的乐观锁和悲观锁？
MySQL 中有哪几种锁，列举一下？
MySQL中InnoDB引擎的行锁是怎么实现的？
MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁？

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事物，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

5.1-锁的分类

1. 从对数据操作的类型分类

  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
  • 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

2. 从对数据操作的粒度分类

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
    • 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
    • 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
    • 最大程度的支持并发，同时也带来了最大的锁开销。
    • 在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
    • 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

5.2-MyISAM 表锁

1. MyISAM表级锁模式

  • 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
  • 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死” ，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行）。

可以设置改变读锁和写锁的优先级：

  • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
  • 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
  • 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

2. MyISAM加表锁方法

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作
（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用：

如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾。 在这种情况下，你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候，同时将行插入到MyISAM表中。 文件中间的空闲块可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快，则并发插入会被禁用，但是当所有空闲块都填充有新数据时，它又会自动重新启用。 要控制此行为，可以使用MySQL的concurrent_insert系统变量。

如果你使用LOCK TABLES显式获取表锁，则可以请求READ LOCAL锁而不是READ锁，以便在锁定表时，其他会话可以使用并发插入。

  • 当concurrent_insert设置为0时，不允许并发插入。
  • 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这也是MySQL的默认设置。
  • 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

3. 查询表级锁争用情况

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺，如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况：

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+

5.3-InnoDB行级锁和表级锁

1. InnoDB锁模式

InnoDB 实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  • 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

  • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁模式的兼容情况：

（如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。）

2. InnoDB加锁方法

  • 意向锁是 InnoDB 自动加的， 不需用户干预。

  • 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB
    会自动给涉及数据集加排他锁（X)；

  • 对于普通 SELECT 语句，InnoDB 不会加任何锁；
    事务可以通过以下语句显式给记录集加共享锁或排他锁：

    • 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
    • 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁
  • 隐式锁定：

InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

  • 显式锁定 ：
select ... lock in share mode //共享锁 
select ... for update //排他锁
  • select for update：

    在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

  • select lock in share mode ：

    **in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

  • 性能影响：

    select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。
    select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

  • for update 和 lock in share mode 的区别：

    前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

3. InnoDB 行锁实现方式：

  • InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
  • 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
  • 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
    别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。（更多阅读：MySQL索引总结）
  • 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

4. InnoDB的间隙锁：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB使用间隙锁的目的：

  1. 防止幻读，以满足相关隔离级别的要求；
  2. 满足恢复和复制的需要：

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。

二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

5. 获取 InnoDB 行锁争用情况：

可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%'; 
+-------------------------------+-------+ 
| Variable_name | Value | 
+-------------------------------+-------+ 
| InnoDB_row_lock_current_waits | 0 | 
| InnoDB_row_lock_time | 0 | 
| InnoDB_row_lock_time_avg | 0 | 
| InnoDB_row_lock_time_max | 0 | 
| InnoDB_row_lock_waits | 0 | 
+-------------------------------+-------+ 
5 rows in set (0.01 sec)

6. 锁模式(InnoDB有三种行锁的算法)

  • **记录锁(Record Locks)**：单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；SELECT * FROM table WHERE id = 1 FOR UPDATE;它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：– id 列为主键列或唯一索引列
    UPDATE SET age = 50 WHERE id = 1;
  • 间隙锁（Gap Locks）：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
  • 临键锁(Next-key Locks)：临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

❝
select for update有什么含义，会锁表还是锁行还是其他

for update 仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！假设有个表单 products ，里面有id跟name二个栏位，id是主键。

  • 明确指定主键，并且有此笔资料，row lock
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
  • 明确指定主键，若查无此笔资料，无lock
SELECT * FROM products WHERE id='-1' FOR UPDATE;
  • 无主键，table lock
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
  • 主键不明确，table lock
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
  • 主键不明确，table lock
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

7. LOCK TABLES 和 UNLOCK TABLES

Mysql也支持lock tables和unlock tables，这都是在服务器层（MySQL Server层）实现的，和存储引擎无关，它们有自己的用途，并不能替代事务处理。 （除了禁用了autocommint后可以使用，其他情况不建议使用）：

  • LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
  • UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，
    或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁
LOCK TABLES语法：
  • 在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则MySQL 不会给表加锁；
  • 事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES会隐含地提交事务；
  • COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用UNLOCK TABLES 释放表锁。

正确的方式见如下语句：
例如，如果需要写表 t1 并从表 t 读，可以按如下做：

SET AUTOCOMMIT=0; 
LOCK TABLES t1 WRITE, t2 READ, ...; 
[do something with tables t1 and t2 here]; 
COMMIT; 
UNLOCK TABLES;
使用LOCK TABLES的场景：

给表显示加表级锁（InnoDB表和MyISAM都可以），一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。（与MyISAM默认的表锁行为类似）

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且 MySQL 不支持锁升级。也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

其实，在MyISAM自动加锁（表锁）的情况下也大致如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

例如，有一个订单表 orders，其中记录有各订单的总金额 total，同时还有一个 订单明细表 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检 查这两个表的金额合计是否相符，可能就需要执行如下两条 SQL：

Select sum(total) from orders; 
Select sum(subtotal) from order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，
order_detail 表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local; 
Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 
Unlock tables;

（在 LOCK TABLES 时加了“local”选项，其作用就是允许当你持有表的读锁时，其他用户可以在满足 MyISAM 表并发插入条件的情况下，在表尾并发插入记录（MyISAM 存储引擎支持“并发插入”））

5.4-死锁（Deadlock Free）

  • 死锁产生：

    • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
    • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
    • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

  • 检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

  • 死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

  • 外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

  • 死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

1. MyISAM避免死锁：

  • 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

2. InnoDB避免死锁：

  • 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
  • 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
  • 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
  • 通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
  • 改变事务隔离级别

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

5.5-一些优化锁性能的建议

  • 尽量使用较低的隔离级别；
  • 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
  • 选择合理的事务大小，小事务发生锁冲突的几率也更小
  • 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
  • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
  • 不要申请超过实际需要的锁级别
  • 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
  • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能

5.6-乐观锁、悲观锁

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

  • **乐观锁(Optimistic Lock)**：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。

    乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

    乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式

  • **悲观锁(Pessimistic Lock)**：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

    悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

    悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

6-MySQL查询

count(*) 和 count(1)和count(列名)区别 ps：这道题说法有点多

执行效果上：

  • count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
  • count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
  • count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

  • 列名为主键，count(列名)会比count(1)快
  • 列名不为主键，count(1)会比count(列名)快
  • 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
  • 如果有主键，则 select count（主键）的执行效率是最优的
  • 如果表只有一个字段，则 select count(*) 最优。

MySQL中 in和 exists 的区别？

  • exists：exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false
  • in：in查询相当于多个or条件的叠加
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

UNION和UNION ALL的区别?

UNION和UNION ALL都是将两个结果集合并为一个，两个要联合的SQL语句 字段个数必须一样，而且字段类型要“相容”（一致）；

  • UNION在进行表连接后会筛选掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录；
  • UNION会按照字段的顺序进行排序，而UNION ALL只是简单的将两个结果合并就返回；

SQL执行顺序

  • 手写

    SELECT DISTINCT <select_list>
    FROM <left_table> <join_type>
    JOIN <right_table> ON <join_condition>
    WHERE <where_condition>
    GROUP BY <group_by_list>
    HAVING <having_condition>
    ORDER BY <order_by_condition>
    LIMIT <limit_number>

  • 机读

    FROM <left_table>
    ON <join_condition>
    <join_type> JOIN <right_table>
    WHERE <where_condition>
    GROUP BY <group_by_list>
    HAVING <having_condition>
    SELECT
    DISTINCT <select_list>
    ORDER BY <order_by_condition>
    LIMIT <limit_number>

7-MySQL事务

事务的隔离级别有哪些？MySQL的默认隔离级别是什么？
什么是幻读，脏读，不可重复读呢？
MySQL事务的四大特性以及实现原理
MVCC熟悉吗，它的底层原理？

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务。

务是MySQL区别于NoSQL的重要特征，是保证关系型数据库数据一致性的关键技术。事务可看作是对数据库操作的基本执行单元，可能包含一个或者多个SQL语句。这些语句在执行时，要么都执行，要么都不执行。

事务的执行主要包括两个操作，提交和回滚。

提交：commit，将事务执行结果写入数据库。

回滚：rollback，回滚所有已经执行的语句，返回修改之前的数据。

7.1-ACID — 事务基本要素

原子性（Atomicity） ：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log日志实现的。

持久性（Durability ：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log日志。

隔离性（Isolation） ：保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制、数据的隐藏列、undo log和类next-key lock机制。

一致性（Consistency） ：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

1. 原子性

事务的原子性就如原子操作一般，表示事务不可再分，其中的操作要么都做，要么都不做；如果事务中一个SQL语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。只有0和1，没有其它值。

事务的原子性表明事务就是一个整体，当事务无法成功执行的时候，需要将事务中已经执行过的语句全部回滚，使得数据库回归到最初未开始事务的状态。

事务的原子性就是通过undo log日志进行实现的。当事务需要进行回滚时，InnoDB引擎就会调用undo log日志进行SQL语句的撤销，实现数据的回滚。

2. 持久性

事务的持久性是指当事务提交之后，数据库的改变就应该是永久性的，而不是暂时的。这也就是说，当事务提交之后，任何其它操作甚至是系统的宕机故障都不会对原来事务的执行结果产生影响。

事务的持久性是通过InnoDB存储引擎中的redo log日志来实现的，具体实现思路见下文。

3. 隔离性

原子性和持久性是单个事务本身层面的性质，而隔离性是指事务之间应该保持的关系。隔离性要求不同事务之间的影响是互不干扰的，一个事务的操作与其它事务是相互隔离的。

由于事务可能并不只包含一条SQL语句，所以在事务的执行期间很有可能会有其它事务开始执行。因此多事务的并发性就要求事务之间的操作是相互隔离的。这一点跟多线程之间数据同步的概念有些类似。

4. 锁机制

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

锁机制并不是一个陌生的概念，在许多场景中都会利用到不同实现的锁对数据进行保护和同步。而在MySQL中，根据不同的划分标准，还可将锁分为不同的种类。

7.2-并发事务处理带来的问题

  • 更新丢失（Lost Update)：事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题
  • 脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
  • 不可重复读（Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
  • 幻读（Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

1. 脏读

当前事务中读到其他事务未提交的数据，也就是脏数据。

以上图为例，事务A在读取文章的阅读量时，读取到了事务B为提交的数据。如果事务B最后没有顺利提交，导致事务回滚，那么实际上阅读量并没有修改成功，而事务A却是读到的修改后的值，显然不合情理。

2. 不可重复读

在事务A中先后两次读取同一个数据，但是两次读取的结果不一样。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。

以上图为例，事务A在先后读取文章阅读量的数据时，结果却不一样。说明事务A在执行的过程中，阅读量的值被其它事务给修改了。这样使得数据的查询结果不再可靠，同样也不合实际。

3. 幻读

在事务A中按照某个条件先后两次查询数据库，两次查询结果的行数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

以上图为例，当对0<阅读量<100的文章进行查询时，先查到了一个结果，后来查询到了两个结果。这表明同一个事务的查询结果数不一，行数不一致。这样的问题使得在根据某些条件对数据筛选的时候，前后筛选结果不具有可靠性。

4. 幻读和不可重复读的区别

  • 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
  • 幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）

5. 并发事务处理带来的问题的解决办法：

  • “更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

  • “脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：

    • 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
    • 另一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

7.3-事务隔离级别

数据库事务的隔离级别有4种，由低到高分别为

  • READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

在实际的数据库设计中，隔离级别越高，导致数据库的并发效率会越低；而隔离级别太低，又会导致数据库在读写过程中会遇到各种乱七八糟的问题。

因此在大多数数据库系统中，默认的隔离级别时读已提交（如Oracle）或者可重复读RR（MySQL的InnoDB引擎）。

查看当前数据库的事务隔离级别：

show variables like 'tx_isolation'

下面通过事例一一阐述在事务的并发操作中可能会出现脏读，不可重复读，幻读和事务隔离级别的联系。

数据库的事务隔离越严格，并发副作用越小，但付出的代价就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

1. Read uncommitted

读未提交，就是一个事务可以读取另一个未提交事务的数据。

  • 事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

  • 分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

2. Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

  • 事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

  • 分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

那怎么解决可能的不可重复读问题？Repeatable read ！

3. Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。MySQL的默认事务隔离级别

  • 事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

  • 分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

那怎么解决幻读问题？Serializable！

4. Serializable 串行化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，Serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

7.4-MVCC

MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。

可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。

MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。

典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。下边通过 InnoDB的简化版行为来说明 MVCC 是如何工作的。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

MVCC的特点就是在同一时刻，不同事务可以读取到不同版本的数据，从而可以解决脏读和不可重复读的问题。

MVCC实际上就是通过数据的隐藏列和回滚日志（undo log），实现多个版本数据的共存。这样的好处是，使用MVCC进行读数据的时候，不用加锁，从而避免了同时读写的冲突。

在实现MVCC时，每一行的数据中会额外保存几个隐藏的列，比如当前行创建时的版本号和删除时间和指向undo log的回滚指针。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

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

另外，InnoDB实现的隔离级别RR时可以避免幻读现象的，这是通过next-key lock机制实现的。

next-key lock实际上就是行锁的一种，只不过它不只是会锁住当前行记录的本身，还会锁定一个范围。比如上面幻读的例子，开始查询0<阅读量<100的文章时，只查到了一个结果。next-key lock会将查询出的这一行进行锁定，同时还会对0<阅读量<100这个范围进行加锁，这实际上是一种间隙锁。间隙锁能够防止其他事务在这个间隙修改或者插入记录。这样一来，就保证了在0<阅读量<100这个间隙中，只存在原来的一行数据，从而避免了幻读。

  • REPEATABLE READ（可重读）隔离级别下MVCC如何工作：

    • SELECTInnoDB会根据以下两个条件检查每行记录：只有符合上述两个条件的才会被查询出来

      • InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的
      • 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除

    • INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号

    • DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识

    • UPDATE：InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

    保存这两个额外系统版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且也能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

    MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

8-MySQL日志系统

MySQL日志系统是数据库的重要组件，用于记录数据库的更新和修改。若数据库发生故障，可通过不同日志记录恢复数据库的原来数据。因此实际上日志系统直接决定着MySQL运行的鲁棒性和稳健性。

MySQL的日志有很多种，如二进制日志（binlog）、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种日志：redo log（重做日志）和undo log（回滚日志）。这里将重点针对InnoDB引擎，对重做日志、回滚日志和二进制日志这三种进行分析。

8.1-事务日志

InnoDB 使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。

事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 IO。

InnoDB 假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。

InnoDB 用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。

InnoDB 使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。

事务日志可以帮助提高事务效率：

  • 使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
  • 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
  • 事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。
  • 如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据。

目前来说，大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

8.2-事务的实现

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。

事务的实现就是如何实现ACID特性。

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现 。

❝
事务是如何通过日志来实现的，说得越深入越好。

事务日志包括：重做日志redo和回滚日志undo

  • redo log（重做日志） 实现持久化和原子性在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录Redo Log，通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。
  • undo log（回滚日志） 实现一致性undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。二者记录的内容也不同，redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

❝
又引出个问题：你知道MySQL 有多少种日志吗？

  • 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
  • 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
  • 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
  • 二进制日志：记录对数据库执行更改的所有操作。
  • 中继日志：中继日志也是二进制日志，用来给slave 库恢复
  • 事务日志：重做日志redo和回滚日志undo

8.3-重做日志（redo log）

重做日志（redo log）是InnoDB引擎层的日志，用来记录事务操作引起数据的变化，记录的是数据页的物理修改。

重做日记的作用其实很好理解，我打个比方。数据库中数据的修改就好比你写的论文，万一哪天论文丢了怎么呢？以防这种不幸的发生，我们可以在写论文的时候，每一次修改都拿个小本本记录一下，记录什么时间对某一页进行了怎么样的修改。这就是重做日志。

InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。

1. 脏数据刷盘

值得注意的是，redo log日志的大小是固定的，为了能够持续不断的对更新记录进行写入，在redo log日志中设置了两个标志位置，checkpoint和write_pos，分别表示记录擦除的位置和记录写入的位置。redo log日志的数据写入示意图可见下图。

当write_pos标志到了日志结尾时，会从结尾跳至日志头部进行重新循环写入。所以redo log的逻辑结构并不是线性的，而是可看作一个圆周运动。write_pos与checkpoint中间的空间可用于写入新数据，写入和擦除都是往后推移，循环往复的。

当write_pos追上checkpoint时，表示redo log日志已经写满。这时不能继续执行新的数据库更新语句，需要停下来先删除一些记录，执行checkpoint规则腾出可写空间。

checkpoint规则：checkpoint触发后，将buffer中脏数据页和脏日志页都刷到磁盘。

脏数据：指内存中未刷到磁盘的数据。

redo log中最重要的概念就是缓冲池buffer pool，这是在内存中分配的一个区域，包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。

当请求读取数据时，会先判断是否在缓冲池命中，如果未命中才会在磁盘上进行检索后放入缓冲池；

当请求写入数据时，会先写入缓冲池，缓冲池中修改的数据会定期刷新到磁盘中。这一过程也被称之为刷脏 。

因此，当数据修改时，除了修改buffer pool中的数据，还会在redo log中记录这次操作；当事务提交时，会根据redo log的记录对数据进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复，从而保证了事务的持久性，使得数据库获得crash-safe能力。

2. 脏日志刷盘

除了上面提到的对于脏数据的刷盘，实际上redo log日志在记录时，为了保证日志文件的持久化，也需要经历将日志记录从内存写入到磁盘的过程。redo log日志可分为两个部分，一是存在易失性内存中的缓存日志redo log buff，二是保存在磁盘上的redo log日志文件redo log file。

为了确保每次记录都能够写入到磁盘中的日志中，每次将redo log buffer中的日志写入redo log file的过程中都会调用一次操作系统的fsync操作。

fsync函数：包含在UNIX系统头文件#include <unistd.h>中，用于同步内存中所有已修改的文件数据到储存设备。

在写入的过程中，还需要经过操作系统内核空间的os buffer。redo log日志的写入过程可见下图。

![](https://gitee.com/lemon-cs/images/raw/master/redo log日志刷盘流程.png)

8.4-二进制日志（binlog）

二进制日志binlog是服务层的日志，还被称为归档日志。binlog主要记录数据库的变化情况，内容包括数据库所有的更新操作。所有涉及数据变动的操作，都要记录进二进制日志中。因此有了binlog可以很方便的对数据进行复制和备份，因而也常用作主从库的同步。

这里binlog所存储的内容看起来似乎与redo log很相似，但是其实不然。redo log是一种物理日志，记录的是实际上对某个数据进行了怎么样的修改；而binlog是逻辑日志，记录的是SQL语句的原始逻辑，比如”给ID=2这一行的a字段加1 “。binlog日志中的内容是二进制的，根据日记格式参数的不同，可能基于SQL语句、基于数据本身或者二者的混合。一般常用记录的都是SQL语句。

这里的物理和逻辑的概念，我的个人理解是：

物理的日志可看作是实际数据库中数据页上的变化信息，只看重结果，而不在乎是通过“何种途径”导致了这种结果；

逻辑的日志可看作是通过了某一种方法或者操作手段导致数据发生了变化，存储的是逻辑性的操作。

同时，redo log是基于crash recovery，保证MySQL宕机后的数据恢复；而binlog是基于point-in-time recovery，保证服务器可以基于时间点对数据进行恢复，或者对数据进行备份。

事实上最开始MySQL是没有redo log日志的。因为起先MySQL是没有InnoDB引擎的，自带的引擎是MyISAM。binlog是服务层的日志，因此所有引擎都能够使用。但是光靠binlog日志只能提供归档的作用，无法提供crash-safe能力，所以InnoDB引擎就采用了学自于Oracle的技术，也就是redo log，这才拥有了crash-safe能力。这里对redo log日志和binlog日志的特点分别进行了对比：

在MySQL执行更新语句时，都会涉及到redo log日志和binlog日志的读写。一条更新语句的执行过程如下：

从上图可以看出，MySQL在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对binlog进行写入，在InnoDB内进行redo log的写入。

不仅如此，在对redo log写入时有两个阶段的提交，一是binlog写入之前prepare状态的写入，二是binlog写入之后commit状态的写入。

之所以要安排这么一个两阶段提交，自然是有它的道理的。现在我们可以假设不采用两阶段提交的方式，而是采用“单阶段”进行提交，即要么先写入redo log，后写入binlog；要么先写入binlog，后写入redo log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。

先写入redo log，后写入binlog：

在写完redo log之后，数据此时具有crash-safe能力，因此系统崩溃，数据会恢复成事务开始之前的状态。但是，若在redo log写完时候，binlog写入之前，系统发生了宕机。此时binlog没有对上面的更新语句进行保存，导致当使用binlog进行数据库的备份或者恢复时，就少了上述的更新语句。从而使得id=2这一行的数据没有被更新。

![](https://gitee.com/lemon-cs/images/raw/master/先写redo log后写binlog的问题.png)

先写入binlog，后写入redo log：

写完binlog之后，所有的语句都被保存，所以通过binlog复制或恢复出来的数据库中id=2这一行的数据会被更新为a=1。但是如果在redo log写入之前，系统崩溃，那么redo log中记录的这个事务会无效，导致实际数据库中id=2这一行的数据并没有更新。

![](https://gitee.com/lemon-cs/images/raw/master/先写binlog后写redo log的问题.png)

由此可见，两阶段的提交就是为了避免上述的问题，使得binlog和redo log中保存的信息是一致的。

8.5-回滚日志（undo log）

回滚日志同样也是InnoDB引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB引擎不仅会记录redo log，还会生成对应的undo log日志；如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前的样子。

但是undo log不redo log不一样，它属于逻辑日志。它对SQL语句执行相关的信息进行记录。当发生回滚时，InnoDB引擎会根据undo log日志中的记录做与之前相反的工作。比如对于每个数据插入操作（insert），回滚时会执行数据删除操作（delete）；对于每个数据删除操作（delete），回滚时会执行数据插入操作（insert）；对于每个数据更新操作（update），回滚时会执行一个相反的数据更新操作（update），把数据改回去。undo log由两个作用，一是提供回滚，二是实现MVCC。

9-主从复制

主从复制的概念很简单，就是从原来的数据库复制一个完全一样的数据库，原来的数据库称作主数据库，复制的数据库称为从数据库。从数据库会与主数据库进行数据同步，保持二者的数据一致性。

主从复制的原理实际上就是通过bin log日志实现的。bin log日志中保存了数据库中所有SQL语句，通过对bin log日志中SQL的复制，然后再进行语句的执行即可实现从数据库与主数据库的同步。

主从复制的过程可见下图。主从复制的过程主要是靠三个线程进行的，一个运行在主服务器中的发送线程，用于发送binlog日志到从服务器。两外两个运行在从服务器上的I/O线程和SQL线程。I/O线程用于读取主服务器发送过来的binlog日志内容，并拷贝到本地的中继日志中。SQL线程用于读取中继日志中关于数据更新的SQL语句并执行，从而实现主从库的数据一致。

之所以需要实现主从复制，实际上是由实际应用场景所决定的。主从复制能够带来的好处有：

  1. 通过复制实现数据的异地备份，当主数据库故障时，可切换从数据库，避免数据丢失。

  2. 可实现架构的扩展，当业务量越来越大，I/O访问频率过高时，采用多库的存储，可以降低磁盘I/O访问的频率，提高单个机器的I/O性能。

  3. 可实现读写分离，使数据库能支持更大的并发。

  4. 实现服务器的负载均衡，通过在主服务器和从服务器之间切分处理客户查询的负荷。

参考

——https://zhuanlan.zhihu.com/p/29150809

——https://zhuanlan.zhihu.com/p/143969890?utm_source=wechat_session&utm_medium=social&utm_oi=912308138894557184

——https://zhuanlan.zhihu.com/p/59764376?utm_source=wechat_session&utm_medium=social&utm_oi=912308138894557184

——https://mp.weixin.qq.com/s/y7Jo67stdbakETo2UV1ivg

——https://mp.weixin.qq.com/s/2WzX1hTVsM_cStzrfABJhg