MySQL技术

1、表

1.1索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。在InnoDB存储引擎中，每张表都有一个主键，如果在创建表时没有显示得定义主键，则InnoDB存储引擎会按照如下方式选择或创建主键：

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

1.2InnoDB存储引擎逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个表空间中，称之为表空间，表空间又由段（segment）、区（extent）、页（page）组成。

• 段：表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等
• 区：在任何情况下每个区的大小都为1MB，为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区。在默认情况下，InnoDB存储引擎引擎页的大小为16KB，即一个区中一共有64个连续的页。
• 页：页是InnoDB磁盘管理的最小单位，默认每个页的大小为16KB

需要牢记：B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入内存，然后通过Page Directory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找很快，因为通常忽略这部分查找所用的时间。

1.3约束

1.3.1数据的完整性

数据库完整性有以下三种形式：

• 实体完整性保证表中有一个主键
• 域完整性保证数据每列的值满足特定的条件
• 参照完整性保证两张表之间的关系

区别于三范式

1.3.2约束的创建和查找

约束的创建可以在表建立时就进行约束定义，也可以利用ALTER TABEL命令来进行创建约束

对Unique Key（唯一索引约束）的约束，用户可以通过命令CREATE UNIQUE INDEX来创建。对于主键约束而言，其默认约束为PRIMARY，而对于Unique Key约束而言，默认约束名和列名一样。

约束和索引的区别：当用户创建了一个唯一索引就创建了一个唯一的约束，但是约束和索引的概念还是有所不同的，约束更像是一个逻辑概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

1.3.3外键约束

外键用来保证参照完整性，MySQL数据库的MyISAM存储引擎本身并不支持外键，对于外键的定义只是起到一个注释的作用。而InnoDB存储引擎则完整支持外键约束。

1.4分区表

分区的过程是将一个表或索引分解成多个更小更可管理的部分。对访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。

MySQL支持以下几种类型的分区：

• RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区

# 创建一个id列的区间分区表，当id小于10时，数据插入p0分区，当数据大于20时，数据插入p1分区
CREATE TABLE t(
id INT
) ENGINE = INNODB
PARTITION BY RANEG(ID)(
PARTITION p0 VALUES LESS THAN(10)
PARTITION p1 VALUES LESS THAN(20)
)

此时查看磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成。

• LIST分区：和RANG分区类型，只是LIST分区面向的是离散的值

CREATE TABLE t(
a INT,
b INT
) ENGINE = INNODB
PARTITION BY LIST(b)(
PARTITION p0 VALUES IN (1,3,5,7,9)
PARTITION p1 VALUES IN(2,4,6,8,10)
)

• HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数
  HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各个分区的数据量大致是一样的

# 创建一个HASH分区的表，分区按照日期b进行
CREATE TABLE t(
a INT,
b DATETIME
) ENGINE = INNODB
PARTITION BY HASH(YEAR(b))
PARTITIONS 4

• KEY分区：根据MySQL提供的哈希函数来进行分区
  KEY分区和HASH分区相似，不同之处在于HASH分区使用用户自定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区

CREATE TABLE t(
a INT,
b DATETIME
) ENGINE = INNODB
PARTITION BY KEY(b)
PARTITIONS 4

无论创建何种类型的分区，如果表中存在主键或者唯一索引，分区列必须是唯一索引的一个组成部分！如果建表时没有指定主键、唯一索引，可以指定任何一个列为分区列。

错误示范：

CREATE TABLE t1 (
col1 int not null
col2 date not null
col3 int not null
col4 int not null
UNIQUE KEY (col1,col2)
)
PARTTION BY HASH(col3)
PARTTIONS 4

由于表中存在唯一索引，那么分区列就只能是唯一索引包含的列！

1.5子分区

MySQL数据库允许在RANGE和LIST分区的基础上再进行HASH或KEY分区

1.6分区中的NULL值

• 对于RANGE分区，如果像分区列插入了NULL值，则MySQL数据库会将该值放入最左边的分区
• 在LIST分区下要使用NULL值，则必须显式地指出哪个分区中放入NULL值，否则会报错
• HASH和KEY分区对于NULL的处理方式和RANGE和LIST分区不一样，任何分区函数都会将含有NULL值得记录返回0

2、索引与算法

2.1B+树索引

B+树索引并不能找到一个给定键值的具体行。B+树索引能够找到的只是被查找数据行所在的页，然后
数据库通过把页读到内存，再在内存中进行查找（二分查找法），最后找到要查找的数据。

在B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。

2.2索引分类

MySQL的索引包括普通索引、唯一索引、主键索引、全文索引

2.2.1普通索引

在创建索引时，不附加任何限制条件只是用于提高效，这类索引可以用在任何数据类型中，其值是否唯一和非空，要由字段本身的约束条件决定。普通索引可以分为单列索引和多列索引。

2.2.2唯一索引

使用UNIQUE参数可以设置索引为唯一索引，在创建唯一索引时，限制该索引的值必须是唯一的，但允许有空值。一张表可以有多个唯一索引。

2.2.3主键索引

主键索引就是一个特殊的唯一索引， 在唯一索引的基础上增加了不为空的约束，一张表里最多只能有一个索引。

2.2.4全文索引

使用参FULLTEST可以设置为全文索引，查询数据量较大的字符串类型的字段时，可以提高查询速度。全文索引只能建立在CHAR、VARCHAR、TEXT类型的字段上。

2.3MySQL8.0索引新特性

2.3.1支持降序索引

索引默认是升序的，如果在创建索引的时候，指定索引列的排序方式，可以显著提升查询速度。

2.3.2隐藏索引

从MySQL8.0开始，将待删除的索引设置为隐藏索引，使查询器不再使用这个索引，确认将索引隐藏后系统不会受到任何印象，就可以彻底删除该索引。这种方式也叫做软删除。

2.3索引的设计原则

2.3.1哪些情况适合添加索引

1、字段的数值有唯一性的限制
2、频繁作为where查询条件的字段
3、经常GROUP BY和ORDER BY的字段
4、UPDATE、DELETE语句中的列
5、DISTINCT字段需要创建索引
6、多表JOIN连接操作时，对连接的字段创建索引，并且该字段在两张表中类型必须一致
7、如果一个字符串很长又需要频繁查询，可以对字符串的前若干个字符创建索引，叫做前缀索引
8、使用区分度高的字段作为索引，例如：学号，反例：性别
9、使用最频繁的列放在联合索引的左侧
10、在多个字段都要创建索引的情况下，联合索引优于单值索引

2.3.2哪些情况不适合添加索引

1、在where中用不到的字段，不要创建索引
2、数据量小的表不要创建索引
3、有大量重复数据的列上不要创建索引
4、避免对经常更新的表创建过多的索引
5、不要使用无序的值作为索引
6、删除不再使用的索引
7、不要定义冗余的索引

3、SQL优化工具

3.1慢查询日志

运行时间超过10S的语句，会被记录到慢查询日志中，通过分析慢查询语句，可以对SQL进行优化

3.1.1 操作查询日志

查看是否已经开启：

show variables like '%slow_query_log%'

开启慢查询日志：

set global slow_query_log = on

查看慢SQL默认阈值：

show variables  like '%long_query_time%'

修改慢SQL阈值：

set global long_query_time = 1

查看慢查询：

SHOW GLOBAL STATUS LIKE '%Slow_queries%'

3.2EXPLAIN执行计划

执行计划详解

4、索引优化与查询优化

4.1索引失效案例

索引失效的情况

4.2join查询的优化

4.2.1外连接

外连接操作时，对连接的字段创建索引，并且该字段在两张表中类型必须一致，例如：

select * from category left join book on category.card = book.cark

在category表的card字段和book表的card字段上创建索引可以提升查询速度

4.2.2内连接

对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为驱动表

4.3子查询优化与排序优化

4.3.1子查询优化

在MySQL中，可以使用连接（JOIN）查询来替代子查询，连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用了索引的话，性能更好。

4.3.2排序优化

1、SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在*WHERE子句中避免全表扫描，在ORDER BY子句中避免使用FileSort排序。

2、尽量使用Index完成ORDER BY排序，如果WHERE和ORDER BY后面是相同的列就使用单列索引，如果不同就使用联合索引

4.3.3优化分页查询

在索引列上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列的内容，例如：

select * from student t ,(select id from student order by id limit 200000,10) a where t.id = a.id

这样查询就避免了回表

4.3.4使用覆盖索引

什么是覆盖索引？覆盖索引就是一个索引列包含了满足查询结果的数据就叫做覆盖索引。例如：

CREATE INDEX idx_age_name ON student(age,name)

select age,name from student where age <> 20

4.3.5索引下推

待补充

3、锁

点击查看

4、事务

4.1事务概述

4.1.1事务的特性

事务的特性

• A（原子性）
• C（一致性）
• I（隔离性）
• D（持久性）

事务的四种特性中，隔离性是由锁机制实现的，而原子性、一致性、持久性是由redo log和undo log实现的。

4.1.2事务的状态

• 活动的：事务对应的数据库操作正在执行过程中
• 部分提交的：当事务中的最后一个操作完成，但还没有刷新到磁盘时
• 失败的：当事务在上述两种状态时，由于一些错误无法执行或被停止
• 终止的：数据库失败之后，回滚到原来的状态

4.2事务的隔离级别

4.2.1数据并发问题

• 脏写：一个事务修改了另一个事务修改过但未提交的数据
• 脏读：一个事务读取到了另一个事务已经更新但是还未提交的数据
• 幻读：一个事务读取到另一个事务插入的数据
• 不可重复读：一个事务读取到了另一个事务更新的数据

4.2.2MySQL中支持的四种隔离级别

• READ UNCOMMITTED：读取未提交，所有事务都可以读取到其他事务未提交的数据，只能解决脏写，不能解决脏读、幻读、不可重复读
• READ COMMITTED：读取已提交，只能读取其他事务已经提交的数据，可以解决脏写、脏读，不能解决幻读、不可重复读
• REPEATABLE READ：可重复读，事务A读取一条数据之后，事务B对该数据进行更新并提交，事务A再次读取还是还是原来的数据，没有受到事务B的影响。可以解决脏写、脏读、不可重复读，不能解决幻读
• SERIALIZABLE：序列化，确保事务串行执行，一个事务执行期间，禁止其他事务的执行，可以解决所有问题

5、MySQL事务日志（redo和undo日志）

MySQL中的日志类型有很多，下面先介绍的两种是跟事务相关的日志

5.1redo log（强调数据恢复）

5.1.1概述

redo log称为重做日志，提供再写入功能，恢复提交事务修改的页操作，用来保证事务的持久性。

redo log是存储引擎层生成的日志，记录的是物理级别上页的修改操作，比如页号xxx、偏移量yyy写入了zzz的数据，主要保证了数据的可靠性。

5.1.2好处

• redo log降低了刷盘频率
• redo log日志占用的空间非常小

5.1.3特点

• redo log是顺序写入磁盘的
  在执行事务的过程中，每执行一条语句，就可能产生若干条redo log，这些日志按照产生的顺序写入磁盘
• 事务执行过程中，redo log不断记录
  redo log跟bin log的区别：redo log是存储引擎层产生的，bin log是数据库层产生的。假设一个事务，对标做10万行记录的插入，在这个过成中，redo log会一直按照顺序记录，而bin log直到这个事务提交，才会一次性写入bin log文件中

5.1.4redo log的整体流程

以一个更新事务为例

第一步：先将原始数据从磁盘中读取到内存中来，修改数据的内存拷贝
第二步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
第三步：当事务commit时，将redo log buffer中的内容刷新到redo log file，采用追加的方式，顺序写入
第四步：定期将内存中修改的数据刷新到磁盘中

5.1.5redo log的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB存储引擎会在写redo log之前先写redo log buffer，之后以一定的频率刷新到磁盘，下面介绍刷盘策略。

InnoDB存储引擎给出了一个innodb_flush_log_at_trx_commit参数，该参数控制commit提交事务时，如何将redo log buffer中的日志刷新到磁盘中，它支持三种策略：

• 设置为0：每隔一秒钟进行一次进行刷盘操作
• 设置为1：每次提交事务时都进行同步（默认）
• 设置为2：每次事务提交时都只把redo log buffer内容写入page cache，由OS决定刷盘时机。

5.1.6日志文件组

redo log在buffer中的记录可以抽象成一个环，环中记录着事务的操作记录，操作记录刷盘成功就擦除掉

• write pos：当前记录的位置，一边写一遍后移
• check point：当前要擦除的位置，也是往后推移
  

5.2undo log（强调事务回滚）

5.2.1概述

undo log称为回滚日志，回滚记录到某个特定版本，用来保证事务的原子性、一致性。

undo log是存储引擎层生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了insert操作，那么undo log就记录一条与之相反的delete操作。主要用于事务的回滚（undo log记录的是每个操作的逆操作）和一致性非锁定读（undo log回滚行记录到某种特定版本-MVCC）。

5.2.2作用

• 作用一：回滚数据
  undo log是逻辑日志，因此只是将数据逻辑地回复到了原来的样子，但是数据结构和页本身在回滚之后可能大不相同。
• 作用二：MVCC
  undo log的另一个重要作用是MVCC，即在InnoDB存储引擎中MVCC是通过undo log来实现的。当用户在读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo log读取之前的行版本信息，以此实现非锁定读取。

5.2.3生成过程

对于InnoDB来说，每个行记录除了记录本身的数据之外，还有几个隐藏的列：

• DB_ROW_ID：如果没有为表显式的定义主键，并且表中也没有唯一索引，那么InnoDB会自动为表添加一个row_id隐藏字段作为主键。
• DB_TRX_ID：每个事务都会分配一个事务ID，当对某条记录发生变更时，就会将这个事务的事务ID写入trx_id中。
• DB_ROLL_PTR：回滚指针，本质上就是只想undo log的指针

每次修改数据都会产生一个trx_id，指向undo log，用于回滚数据，如果一条数据被多次修改，那么trx_id就会生成多个，并且以头插法的形式连接起来，确保能一直回滚到原始状态。

6、锁

事务的隔离性是由锁来实现的

6.1MySQL并发事务访问相同记录

6.1.1读-读情况

并发事务相继访问相同的记录，读操作不会堵记录有任何影响，所以运行这种情况发生

6.1.2写-写情况

并发事务相继对相同记录做出改动。在这种情况下，会发生脏写问题，任何一种隔离级别都不允许这种情况存在。所以，在多个未提交的事务相继对同一条记录做改动时，需要让他们排队执行，这个排队的过程就是通过锁来实现的。这个所谓的锁其实是内存中的结构，在事务执行前本来是没有锁的，也就是说一开始没有任何锁结构和行数据关联。

每次新来一个事务，都会生成一个锁与之关联

在锁结构里有很多信息，有两个比较重要的属性：

• trx：代表这个锁结构是哪个事务产生的
• is_waiting：代表当前事务是否需要等待

在T1事务提交之前，另一个事务T2也想对该记录做改动，发现已经有一个锁与该记录关联了，就把自己的is_waiting属性值置为true，表示当前事务需要等待，我们把这个场景叫做加锁失败

等T1事务结束之后，发现事务T2还在等待，于是就把T2事务的is_waiting属性值置为false，这样T2就算获取到锁了

6.1.3读-写或写-读的情况

读-写或写-读，即一个事务进行读取操作，另一个事务进行改动操作，这种情下可能发生脏读、幻读、不可重复读的问题。

6.1.4并发问题的解决方案（重要）

怎么解决脏读、幻读、不可重复读的问题呢？

• 方案一：读操作利用MVCC（下章专门讲解），写操作进行加锁

快速入门和：所谓的MVCC，就是生成一个视图，通过视图找到符合条件的记录版本（历史版本由undo log构建）。查询语句只能读到在生成视图之前已提交的事务所做的更改，在生成视图之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新的版本记录，读记录的历史版本和改动记录的最新版本并不冲突，也就是采用MVCC时，读-写操作并不冲突。

普通的SELECT语句在READ COMMITED和REPEATABLE READ的隔离级别下会使用到MVCC读取记录

• 在读取已提交隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个视图，视图的存在本身就保证了事务不可以读取到未提交的事务所做的修改，也就成功避免了脏读现象
• 在可重复读隔离级别下，一个事务在执行过程中，只有第一次执行SELECT操作时才会生成一个视图，之后SELECT操作的复用这个视图，这样也就避免了不可重复读和幻读的问题。

• 方案二：读、写操作都采用加锁的方式
• 小结对比：
  • 采用MVCC方式的话，读-写操作彼此互不冲突，性能更高
  • 采用加锁方式的话，读-写操作彼此需要排队执行，影响性能
  • 一般情况下，我们更乐意采用MVCC来解决读-写操作并发执行问题，但是业务在某些特殊情况下，要求必须采用加锁发方式来执行，下面开始讲解锁。

阶段小结：MVCC用来解决读-写并发操作的问题

6.2锁的不同角度分类

6.2.1从数据操作的类型划分为：读锁、写锁

对于写-写、读-写、写-读情况引起的并发问题，除了使用MVCC的方式来解决之外，还可以使用加锁的方式。使用加锁的方式不仅可以解决上述并发问题，还不会影响读-读操作。

MySQL中的锁系统由共享锁（Shared Lock）和排它锁（Exclusive Lock）组成，也叫做读锁（Read Lock）和写锁（Write Lock）

• 读锁：用英文S表示，针对同一个数据，多个事务的读操作可以同时进行而不会互相影响，互相不阻塞
• 写锁：用英文X表示，当前写操作没有完成前，他会阻断其他写锁和读锁，这样就能确保在给定的时间内，只有一个事务能执行写入，并防止其他用户读取正在写入的资源

注意：对于InnoDB引擎来说，读锁和写锁可以加在表上，也可以加在行上

锁定读

在采用加锁的方式解决脏读、幻读、不可重复读这些问题时，读取一条记录时需要获取该记录的S锁，其实是不严谨的，有时候需要在读取记录时就获取记录的X锁，来禁止别的事务读写该条记录，为此MySQL提出了两张比较特殊的SELECT语句格式：

• 对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE
#或者
SELECT ... FOR SHARE

在普通的 SELECT语句后边加LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加S 锁，这样允许别的事务继续获取这些记录的 S 锁（比方说别的事务也使用SELECT … LOCK IN SHARE MODE语句来读取这些记录），但是不能获取这些记录的X锁（比如使用SELECT … FOR UPDATE语句来获取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的 S 锁释放掉。

总结：事务获取S锁之后，只允获取S锁，不允许获取X锁

• 对读取的记录加X锁

SELECT ... FOR UPDATE

在普通的SELECT语句后边加FOR UPDATE，如果当前事务执行了该语句，那么它会为读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁（比方说别的事务使用SELECT … LOCK IN SHARE MODE语句来读取这些记录），也不允许获取这些记录的X锁（比如使用SELECT … FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的S锁或者X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的X锁释放掉

总结：事务获取X锁之后，会阻塞其他所有获取锁的操作

写操作

写操作可以分为DELETE、UPDATE、INSERT三种，大体上可以认为有以下两种情况：

• DELETE和UPDATE：加X锁
• INSERT：不会加锁，通过一种叫做隐式锁的结构来保护这条记录在本事务提交之前不会被别的事务访问

6.2.2 从数据操作的粒度划分：表级锁、页级锁、行锁

表锁

表锁一次会将整个表锁定，可以很好得避免死锁问题，但是并发效率会大打折扣

（1）表级锁的行S锁、X锁
一般情况下不会使用InnoDB存储引擎提供的表级别的S锁和X锁
（2）意向锁
InnoDB支持多粒度，他允许行级锁和表级锁共存，而意向锁就是其中一种表锁。

• 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁共存
• 意向锁是一种不与行级锁冲突的表级锁，这一点非常重要
• 表明某个事务正在某些行持有了锁或该事务准备去持有锁

意向锁分两种：

• 意向共享锁（IS）：事务有意向对表中的某些行加共享锁（S锁）

#事务要想获取某些行的S锁，还需先获得表的IS锁
SELECT ... LOCK IN SHARE MODE

• 意向排它锁（IX）：事务有意向对表中某些行加排它锁（X锁）

#事务要获取某些行的X锁，必须先获得表的X锁
SELECT ... FOR UPDATE

总结：意向锁是由存储引擎维护的，用户无法手动操作意向锁，在为数据添加共享锁/排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁

意向锁要解决的问题：在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或者数据表加上意向锁，告诉其他事务这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排他锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可

（3）自增锁（了解）
MySQL中采用了自增锁的方式来实现主键的递增，AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句结束后，再把AUTO-INC锁释放掉。一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，这样可以保证一个语句中分配的递增值是连续的。
（4）元数据锁（MDL锁）
MDL的作用是：保证DML和DDL操作之间的的一致性。

比如，如果一个查询事务正在遍历一个表中的数据，而另一个事务对这个表结构做变更，增加了一列，那么查询事务拿到的数据跟表结构对不上，这肯定是不行的。

因此，当对一个表做增删改查操作的时候，加MDL读锁，当要对表结构做变更操作的时候，加MDL写锁

读锁之间不互斥，因此可以有多个事务同时对一张表增删改查。读写锁之间，写锁之间是互斥的，用来保证表结构的安全性。

注意：元数据锁不需要显式使用，在访问任何一个表的是都会被自动加上

InnoDB中的行锁

行锁也称为记录锁，顾名思义就是锁住某一行数据，需要注意的是，MySQL服务器层并没有实现行级锁机制，行级锁是在存储引擎层面实现的！

InnoDB与MyISAM最大的不同点：一是支持事务；二是采用了行级锁

（1）记录锁
记录锁也就是仅仅把一条记录锁上，比如我们给ID值为8的那条记录加一个记录锁，仅仅是锁住了ID值为8的记录，对周围数据没有影响。

记录锁也分为S锁和X锁，称之为S型记录锁和X型记录锁

• 当一个事务获取了一条记录的S锁后，其他事务可以继续获取该记录的S锁，但不可以获取X锁
• 当一个事务获取了一条记录的X锁后，其他事务既不可以获取该记录的S锁，也不可以获取该记录的X锁

（2）间隙锁
MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种：可以使用MVCC方案，也可以使用加锁方案。但是在使用加锁方案时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加锁。InnoDB提出了一种称之为Gap Locks的锁，我们称之为间隙锁。比如，把ID值为8的那条记录加一个间隙锁的示意图如下：

图中id值为8的记录加了间隙锁，意味着不允许别的事务在ID值为8的记录前边的间隙插入新记录，其实就是ID列的值(3, 8)这个区间的新记录是不允许立即插入的。比如，有另外一个事务再想插入一条ID值为4的新记录,它定位到该条新记录的下一条记录的ID值为8，而这条记录上又有一个间隙锁，所以插入操作就会被阻塞，直到拥有这个间隙锁的事务提交了之后，ID列的值在区间(3, 8)中的新记录才可以被插入。

间隙锁的提出仅仅是为了防止插入幻影记录而提出的，也就是为了解决幻读问题。

注意，给一条记录加了间隙锁只是不允许其他事务往这条记录的前边的间隙插入新记录，那对于最后一条记录之后的间隙该怎么办呢？也就是给哪条记录加锁才能阻止其他事务插入ID值在(20,+无穷)这个区间的新记录呢？解决方案就是执行下面的语句：

SELECT * FROM student WHERE id > 20 LOCK IN SHARE MODE

原理如下：
通过上面的语句知道，加间隙锁的方式就是在最后加上LOCK IN SHARE MODE！

（3）Next-Key Locks
有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，所以InnoDB就提出了Next-Key Locks。

Next-Key锁的本质就是行记录锁+间隙锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

（4）插入意向锁
我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了间隙锁( next-key锁也包含间隙锁)，如果有的话，插入操作需要等待，直到拥有间隙锁的那个事务提交。但是InnoDB规定事务在等待的
时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB就把这种类型的锁命名为Insert Intention Locks，我们称为插入意向锁。插入意向锁是一种间隙锁，不是意向锁，在INSERT操作时产生。

总结：插入意向锁可以认为是一种特殊的间隙锁

页锁（了解）

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

6.2.3按照加锁的方式划分

略

6.2.4其他锁之全局锁

全局锁就是对整个数据库实例进行加锁，当需要使整个库处于只读状态的时候，可以使用这个命令。
全局锁的命令

Flush tables with read lock

6.2.5其他锁之死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求对方释放占用的锁资源，从而导致的恶性循环。

7、MVCC

MVCC，多版本并发控制。顾名思义，是通过数据行的多个版本管理（undo log+隐藏字段+视图实现）来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一个正在被另一个事务更新的行，并且可以看到他们被更新之前的值（不这样的话不就是脏读了！），这样在做查询的时候就不用等待另一个事务释放锁（可以理解为乐观锁，不然不就是串行执行了吗，跟锁有什么区别呢）（多读读这句话！）。

7.1快照读与当前读

7.1.1快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的SELECT都属于快照读，即不加锁的非阻塞读。

之所以出现快照读的情况，是基于提高并发性能考虑的，快照读的实现是基于MVCC，他在很多情况下，避免了锁操作，降低了开销。

既然是基于多版本，那么快照读可能读到的不一定是数据的最新版本，而有可能是之前的历史版本。

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

7.1.2当前读

当前读读取的是记录的最新版本（注意是最新！），读取时还要保证其他并发事务不能修改当前记录，当前读会对读取的记录加锁。加锁的SELECT，或者对数据库的增删改操作都会进行当前读。比如：

SELECT * FROM student LOCK IN SHARE MODE

7.2复习

7.2.1再谈隔离级别

在可重读读隔离级别下，认为是解决了脏读、不可重复读，没有解决幻读，但是在MySQL中，因为有了MVCC的加持，在可重复读隔离级别下，同样可以解决幻读问题（幻读：一个事务读取到了另一个事务插入的数据；MVCC：读取的是当前数据的历史版本）

7.2.2隐藏字段（）和undo log版本链

前置知识：每条记录都有三个隐藏字段，row_id（没有主键时候用）、trx_id、roll_pointer（MVCC要用）

对于使用InnoDB存储引擎的表来说，他的聚簇索引记录中都包含两个必要的隐藏列：

• trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id字段
• roll_pointer：每次对某条聚簇索引记录改动时，都会把旧的版本写入到undo log中，然后这个隐藏列就相当于一个指针，可以通过他找到该记录被修改前的信息

roll_pointer示意图：

7.3MVCC原理之视图

MVCC的实现三板斧：隐藏字段、undo log、视图

7.3.1什么是视图

在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在UndoLog里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView了，它帮我们解决了行的可见性问题。

ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB 为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID (“活跃”指的就是，启动
了但还没提交)

7.3.2设计思路

使用READ UNCOMMITED隔离级别的事务，由于可以读取到未提交事务
的数据，所以直接读取数据最新的版本就好了。

使用SERIALIZABLE隔离级别的事务，InnoDB规定使用锁的方式来访问记录。

使用READ COMMITED和REPEATABLE READ隔离级别的事务，都必须保证读到已提交了的事务修改的记录。假如另一个事务已经修改了记录但是没提交，是不是直接能读取到最新的版本记录，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这事视图要解决的主要问题。

这个视图中主要包含4个比较重要的内容，分别如下（重要）：

• creator_trx_id：创建这个视图的事务ID
• trx_id：表示生成视图时系统中活跃的事务
• up_limit_id：活跃事务中最小的事务ID
• low_limit_id：表示生成视图时系统应该分配给下一个事务的ID值。

7.3.3MVCC整体操作流程

1、首先获取事务自己的版本号，也就是事务ID
2、获取视图
3、查询得到数据，然后与视图中的事务版本号进行比较
4、如果不符合视图规则，就需要从undo log中获取历史数据
5、最后返回符合规则的数据

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上面的步骤判断可见性，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

总结：MVCC通过undo log + read view进行数据读取，undo log保存了数据的历史版本，而read view规则帮我们判断当前版本是否可见。

在隔离级别为READ COMMITED时，一个事务中的每一次SELECT查询都会重新获取一次read view。

在隔离级别为REPEATABLE READ时，一个事务只在第一次SELECT的时候获取一次read view，而后面的所有SELECT都会复用这个read view。

7.4举例说明

READ COMMITED隔离级别下

REPEATABLE READ隔离级别下

如何解决幻读

InnoDB解决幻读必须是在REPEATABLE READ隔离级别下才能解决幻读！

假设在student表中只要一条数据，他的undo log如下如所示：

假设现在有事务A和事务B并发执行，事务A的事务ID为20，事务B的事务ID为30：

步骤1：事务A开始第一次查询数据，查询的SQL 如下

SELET * FROM student WHERE id = 1

在开始查询之前，MySQL会为事务A产生一个ReadView，此时ReadView的内容如下：trx_ids=[20,30]，up_limit_id=20，low_limit_id=31，creator_trx_id=20。

由于此时表中只要一条数据，然后根据ReadView机制，发现该行数据的trx_id=10，小于事务A的ReadView里的up_limit_id，这表示这条事务在事务A开始前，已经被其他事务提交，因此事务A可以读取到（也可以这样理解：该行数据的trx_id=10，已经小于活跃事务ID，因此是已经提交了的，可以被查询出来）。

步骤2：事务B（trx_id=30）往表中插入两条新数据，并提交事务，此时表中已经有三条数据了，对应的undo log如下图所示：

步骤3：接着事务A开启第二次查询，根据可重复读隔离级别的规则，此时事务A并不会再重新生成ReadView。此时表中的数据都满足条件，因此都会被查出来。然后根据ReadView机制，判断每条数据是不是都可以被事务A看到。

经过事务A的分析，发现id=2和id=3的数据的trx_id这个值，还处于活跃事物的区间，表名这两条数据是还没有被提交的数据，因此，这条数据不能被事务A看到。由此就避免了幻读现象。

8、其他MySQL日志

在讲解事务时，讲解了redo log和undo log，下面讲解其他几种日志类型

8.1MySQL支持的日志

MySQL有不同类型的日志文件，用来存放不同类型的日志，除去上面讲解的两个事务日志，还有二进制日志、错误日志、慢查询日志和通用日志。

8.2慢查询日志

在第三章SQL优化工具3.1.1章节有讲解

8.3通用查询日志

用来记录用户的所有操作，包括启动和关闭MySQL服务，所有用户的连接开始和截止时间、执行的语句等。当我们发生异常时，可以查看通用日志，还原操作时的具体场景，定位问题。

8.4错误日志

错误日志记录了MySQL服务器运行和停止过程中的诊断信息，包括错误、提示、警告等。

8.4.1查看日志

MySQL错误日志是默认开启的，以文本形式存储，可以使用文本编辑器直接查看。

查看错误日志路径：

show variables like '%log_err%'

8.5二进制日志（bin log）

bin log即二进制日志，他记录了数据库执行所有DDM和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如SELECT）

他以事件形式记录并保存在二进制文件中，通过这些信息，我们可以再现数据更新操作的全过程。

bin log的主要应用场景：

• 数据恢复：如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库做了哪些修改，然后根据二进制文件中的记录来恢复数据。
• 主从复制：由于日志的延续性和时效性，主从架构中master把他的二进制文件传递给slave来达到主从数据一直的问题。

8.5.1写入时机

bin log的写入时机也非常简单，事务执行过程中，先把日志写到bin log cache，事务提交的时候，再把binlog cache写到bin log文件中。因为一个事务的bin log不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程binlog cache的大小，如果存储内容超过了这个参数，就要暂存到磁盘，bin log刷盘流程如下：

上图说明：

• write过程：是指把日志写入文件系统的page cache，并没有把数据持久化到磁盘
• fsync过程：这个过程才是将数据持久化到磁盘的操作

关于write和fsync执行的时机，可以由sync_binlog控制，分三种情况（重要！）：

• 0：表示每次提交事务都只write，由系统决定fsync时机，虽然性能有所提升，但是机器宕机，page cache里面的bin log会丢失
• 1：表示每次提交事务都会执行fsync，就如同redo log刷盘流程一样
• N：表示每次提交事务都会write，但积累N个事务后才fsync

8.5.2bin log和redo log对比

• redo log是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎层产生的
• bin log是逻辑日志，记录内容是语句的原始逻辑，类似于“给id=2的这一行的c字段加1”，属于MySQLServer层
• 虽然他们都属于持久化的保证，但是侧重点不通过：
  • redo log让InnoDB存储引擎有了崩溃恢复能力
  • bin log保证了MySQL集群架构的数据一致性