java—04 MySQL原理 InnoDB存储引擎-优快云博客

数据存储在磁盘上，磁盘读取数据比较慢（所以有了Redis或memory的存储引擎，这些都是基于内存的），内存快，用户取数据走的是磁盘io，分为随机io（慢）和顺序io（快），kafka里的存取数据都是从磁盘上，但是速度很快，是因为kafka有顺序读取的场景，顺序存，顺序取，所以会快，以.log的形式存储消息，有顺序指针。

一、页

1. InnoDB数据页及其结构

为了避免一条一条读取磁盘数据， InnoDB采取页的方式，作为磁盘和内存之间交互(查询&刷新)的基本单位。一个页的大小一般是16KB。（内存向磁盘发出读取数据的请求，磁盘返回的都是16KB的页）在磁盘存储的单位也是页。

InnoDB为了不同的目的而设计了多种不同类型的页。比如：存放表空间头部信息的页、存放undo日志信息的页等等。我们把存放表中数据记录的页，称为索引页or数据页。（索引即数据，数据即索引，但是非聚簇索引或二级索引不满足该条件）

File Header：页和页之间是双向指针 (pre&next) 维护的。pre指针和next指针存在于File Header中。

Infimum和Supremum：记录最小和最大的记录，这两个值也类似于数据记录，但又不完全一样。

User Records：最开始是0，随着存储的数据增多而变大。

Free Space：最开始是最大的，随着存储的数据量大增大而减小，当该空间变为0的时候，整个页存储完，不能再存储新的记录，只能产生新的页。

从磁盘查数据慢，所以需要优化。

查询优化点一：页；一个页是16kb，一条数据很小，所以一次取出一个页会包含很多的数据，如果下一次查询的数据在该页中，就不用再去内存中取数据。

优化点二：bufferpool

数据或记录就是索引。

2. 往数据页中存储数据/记录

最左边是新的页。

当数据满了要申请新的页。

3. 记录的头信息

compact行格式：主流的数据存储格式

记录的额外信息：是MySQL操作系统自动控制的，为了更方便地管理记录；

记录头信息：2个预留位为将来更高版本的InnoDB服务，暂时用不到。

deleted_flag：逻辑删除标记（0:未删除 1:已删除），可用于回滚。
min_rec_flag：B+树中每层非叶子节点中的最小的目录项记录，都会添加该标记。目录项：非叶子节点，方便进行查询数据的东西，服务于索引。在索引层。
n_owned：一个页面被分若干组后，“带头大哥”用于保存组中所有的记录条数。分完组之后有顺序，组里主键值最大的记录为“带头大哥”（加入1-10条记录中，3 4 5 6四条是一个组，则6是“带头大哥”，6这条记录的n_owned会记录存储了几条数据（该例中为4），而同组的3 4 5的n_owned值为0）
heap_no：表示当前记录在页面堆中的相对位置。（每申请一个记录空间，都会加1，即user_record的位置，infimum默认位置是0，supremum默认位置是1，最小记录的默认位置是2）（不太用得到）
record_type：表示当前的记录类型。
1. 0：普通记录（用户插入的记录）
2. 1： B+树非叶子节点的目录项记录
3. 2：表示Infimum记录
4. 3：表示Supremum记录
next_record：表示下一条记录的相对位置，也就是链表。这个属性非常重要。它表示从当前记录的真实数据到下一条记录的真实数据的距离，保证了记录按主键有序。且该属性既不指向下一条记录的头，也不指向下一条记录的尾巴，而是指向下一条记录的【额外信息】和【真实数据】中间的位置，这样设置方便查找，因为如果要找下一条记录的真实数据，可以向右查找，如果要找下一条记录的额外信息，可以向左查找，如果指向的是其他位置，找信息的话没有这个位置好找。该信息用来保证底层逻辑有序，通过指针指向下一条记录，指针的指向是有序的。

一个页的记录项是单向链表维护的。

记录的真实数据：

假设一张数据表A有n列，则【主键列的值】存表A的主键列，剩余的n-1列分别存入【a列的值、b列的值、c列的值、……、n-1列的值】
trx_id列的值：事务id，记录当前最新的记录由哪一条事务修改的。作用：可见性的控制，看到的是trx_id所记录的事务对数据更改后的结果。主要是undo log用到
roll_pointer列的值：版本链，记录了同一条记录在一系列事务下所做的更改的顺序关系，导致不同的用户看到的相同主键的数据有可能是不同的（因为版本链的控制，看到的是不同版本的事务所做更改后的结果）。例如对于id为1的名字进行了四次更改，分别为：事务T1 (Zinnia) --> 事务T2 (Bob) --> 事务T3 (James) --> 事务T4 (Tim) ，因为MVCC和Readview的控制，小宋看到的id 为1的名字为James，小张看到的id为1的名字为Bob。版本链和undo log有关。
主键/聚簇索引必须有，哪怕没有非主键索引，指定了主键，会选择该列为主键，如果没有指定，会选择非空unique列作为主键，如果都没有，就是row_id的事情了，此时row_id会作为主键，
row_id：（隐藏列）row_id作为隐藏式的id，排序时根据row_id排序，作为主键列时，每插入一条数据，该列的值增加1。一般都会设定主键列，所以一般row_id没有用。

【trx_id列】和【roll_pointer列】主要是undo日志中会用到

4. 删除操作对next_record的影响

可以看出记录是按照主键从小到大的顺序形成了一个单向链表。

以下数据都是叶子节点，所以min_rec_flag都是0。

当一条记录被删除时的步骤：

（1）逻辑删除属性【deleted_flag】从0设置为1，同时将该条记录放到垃圾链；（这样做的好处：空间可以复用，例如如果插入一条和被删除的记录相同的记录，将deleted_flag改为0即可，不用重新再申请空间，也不会涉及数据迁移）

（2）组内记录数【n_owned】减1，由5变为4；

（3）将删除记录的上一条记录的next_record指针指向被删除记录的下一条；

5. Page Directory

记录在页中是按照主键值从小到大的顺序串联成为一个单向链表，因此查询也只能以头节点开始逐一向后查询，但是如果数据量很大，那么性能就无法保证了。针对这个问题， InnoDB采取了图书目录的解决方案，即：Page Directory。

（页—页：双向指针、靠索引；记录—记录：单向链表、Page Directory）

分组规则如下所示：

对于Infimum记录所在的分组只能有1条记录。
对于Supremum记录所在的分组只能在1～8条记录之间。（若supremum存满8条，再新加入一条记录时，会将这些记录分为两组，保证其他组最少有4条记录，剩余的记录和supremum一组，其中supremum是最大的记录，始终在最后那组）
剩下的其他记录所在的分组只能在4～8条记录之间。

分组步骤如下：

初始情况下，一个数据页中只有Infimum记录和Supremum记录这两条，所以分为两个组。
之后每当插入一条记录时，都会从页目录中找到对应记录的主键值比待插入记录的主键值大，并且差值最小的槽，然后把该槽对应的n_owned加1。
当一个组中的记录数等于8时，当再插入一条记录的时候，会将组中的记录拆分成两个组（一个组中4条记录，另一个组中5条记录，5条记录的组中包含supremum，因为supremum是最大的）。并在拆分过程中，会在Page Directory中新增一个槽，并记录这个新增分组中最大的那条记录的偏移量。

槽slot存的是每个组中的最大值，一个槽对应一个组，slot指向next_record，而next_record只能单向往后，不能往前。

槽可以理解为一个有大小顺序的数组，可以通过二分法进行查找。

借助槽如何查找数据：例如有0-40条数据，分组如下：

infimum (0)	槽1
1-10条数据	槽2
11-20条数据	槽3
21-30条数据	槽4
31-40条数据	槽5

如果想找第24条数据，尽管第24条数据在槽4，但是由于槽指向的是每个组中的最大的那条记录的next_record，而槽4指向的是30条数据的next_record，又因为next_record只能往后找31-40条数据，所以要定位到槽3，通过槽3指向的第20条数据的next_record继续向下遍历，依次找到21,22,23,24。

引入page directory之后，单页查询变得简单，因为可以先二分法找组，再从组里找记录，而一个组里最多只有8条记录，会很方便。

多页查询是索引部分的内容。

6. 页满后的操作

（页与页之间是双向指针）想插入id为7的数据，但当前页在插入了1 2 3 4 10之后已经满了，所以需要先分页，再数据迁移，先将10放入新生成的page2，再将7放入page1，

底层数据按照主键自动排好序。

数据主键：主键id（自增）内部用主键索引，存储数据用，

业务主键：（非自增）类似学号，工号之类，unique，外部用二级索引，为了防止外部人员猜测到id，恶意操作数据库。

数据主键无序，不便于在底层存储，业务主键有序

业务主键是非主键，建的是二级索引，主键索引存储的是全部的数据，非主键索引存储的不是全部数据，而是主键相关的内容，通过二级索引查找某个记录的时候，先通过二级索引找到对应的主键，然后再根据主键值通过回表再去主键索引里找到全部数据。在系统里主要采用数据主键，外部有关的时候，用户交互的时候主要用业务主键，业务主键加二级索引就可以。

问：如果要查询某一条记录，查询过程是怎样的？

页的范围还是多页范围：

（默认）多页：全表扫 / 索引（加索引的时候） --> 主键索引或者非主键索引或组合索引--> 找到该记录在哪一个页上 --> 页上的查找

页：首先通过page Directory ，通过slot 二分法定位到某条记录应该在那个组中（单向向后查找的原因），所以找到这个slot的上一个slot，然后通过next_record找值，如果有返回，无则没有

二、B树 & B+树

图文详解：B树和B+树的插入、删除图文详解 - nullzx - 博客园

MySQL索引的底层是B+树

模拟B树和B+树的网站：

B+树：B+ Tree Visualization

B树：B-Tree Visualization

N=5时的B树和B+树：

B树和B+树相同点：

一个节点可以存储多个元素。
与B树一样，叶子节点是有序的。
每个节点中的元素，也都按照从小到大的顺序排列，即：左小右大。
所有叶子节点都位于同一层，或者说根节点到每个叶子节点的高度都相同。

不同点：

B+树的叶子节点是有单向指针的，其中：MySQL（InnoDB存储引擎）中采用的是双向指针。
B+树的非叶子节点的元素是与叶子节点有冗余的

三、Index——索引

多个页用到索引。

主键、一级、聚簇索引

二级索引、非主键索引、非聚簇索引

叶子节点里存储完整的数据/记录（数据页）。非叶子节点，存储主键索引（索引页）。

record_type为1代表目录项，目录项中包含两个参数：最小id和page号。

如果将索引当做目录项存储在页中(如上图)，有空间限制（16KB），依旧需要page Directory，不是很有效率。

当记录越来越多，创建的页也会越来越多，如果仅通过链表的方式遍历查询，性能会出现很大问题，这时要借助B+树结构，即：叶子节点里存储完整的数据（数据页），非叶子节点存储主键索引（索引页）

索引当做记录的话，不会受到页大小空间的控制，而受到页大小的控制是因为想以page directory的方式进行索引的管理。

索引当成记录是没有空间限制的，如下：

1. 聚簇索引——主键

根节点表示的范围是[1, 209)

第二层蓝色的1代表的是目录项

主键索引存的是全量的数据（所有的字段），当找到了想要的id，即可取出该id对应的全部数据（字段）。

每个索引都要维护B+树。树一般3-4层。

使用主键自增，数据在加入的时候就是有顺序的，无需再根据大小进行调整，加快了查询效率

2. 二级索引——非主键

根节点表示的范围是[2, 8]

叶子节点中粉色的数据：第一行是C2列的值，第二行是主键的值。所以二级索引的叶子节点保存的是创建二级索引的列C2和主键。

上图中最下面的页是按C2列的大小排序的，

若查询“select c3 from table where c2 = '5' ”，则先从二级索引即非主键索引找到根节点的2，再找到第二层的4，再找到第三层叶子节点的5，在叶子节点根据c2列为5的这条记录对应的主键209，进行回表操作，回到主键索引，进而找到主键为209对应的这条数据的C3列的值进行返回。如果要查询“select c1 from table where c2 = '5' ”，则不需要回表，因为非主键索引的B+树保存了C1的值，直接在非主键索引查询中就可以获得。

select * from table 是全表查。

3. 联合索引——多列

上图中最下面叶子节点的页，每条记录包含的三个值分别是：要建索引的c2、c3和主键。

给c2和c3列创建索引：先对c2排序索引，c2相同的话，再对c3排序索引。c2索引先从小到大排列，对于相同的c2，c3索引从小到大排列。所以C2是有序的，C3是局部有序的，局部有序对于总体来说无序。（最左匹配原则）

最左匹配原则：坚毅草Ai

并不是查询速度慢就要重新建立索引，因为建立索引后还要维护，维护成本高，所以建立索引要适度。

4. 目录项记录的唯一性

为了让新插入的记录能找到自己在哪个页中，就需要保证B+树同一层内节点的目录项记录除页号字段外是唯一的。所以二级索引的内节点的目录项记录的内容实际是有3部分构成的：索引列的值(C2)+主键值(C1)+页号(pageNo)。这样，如果C2列的值相同，那么可以接着比较主键值。所以可以认为，为C2列建立的二级索引其实相当于为（C2, C1）列建立了一个联合索引。

索引建多了，新增和修改的性能降低了，因为要维护树。

四、Buffer Pool——缓冲池

查询id=1时，取出一个页，假如该页包含的是1-2000的数据，现在又想获取id= 2 的数据，可以直接从上次取出的页中拿数据，该页数据保存在了缓存中，因此不需要走数据库磁盘io，加快查询的一个有效手段。

主要作用：加快查询。

1. 缓冲池介绍

为了缓存磁盘中的页，MySQL服务器启动时就向操作系统申请了一片连续的内存空间（好处：查找等操作比较快），该片内存名为Buffer Pool (缓冲池)。默认Buffer Pool只有128M，可以在启动服务器的时候配置innodb_buffer_pool_size （单位为字节）启动项来设置自定义缓冲池大小。Buffer Pool对应的一片连续的内存被划分为若干个页面，默认也是16KB，该页面称为缓冲页（缓存获取到的页）。为了更好管理Buffer Pool中的这些缓冲页，InnoDB为每个缓冲页都创建了控制块，它与缓冲页是一一对应的。

缓冲页：缓存获取到的页。

控制块：记录缓冲页的状态信息。

2. Free链表

Buffer Pool的初始化过程中，是先向操作系统申请连续的内存空间128M，然后把它划分成若干个【控制块&缓冲页】对儿。当插入数据的时候，为了能够知道哪些缓冲页是空闲且可以分配的，MySQL把所有的空闲的缓冲页对应的控制块作为一个节点放到一个链表中，这个链表便称为free链表。

如果想要查询id=1的数据，通过前边介绍的查找的方法已经定位到了page25，接下来确定page25在缓冲池存不存在，如果存在则直接返回，确定的方法：MySQL中有一个类似于hashmap的关系，key值是表空间+页号，可确定页，value是控制块，如果发现没有缓存到buffer pool中，就要在缓冲池中找可以用来存放该页的缓冲块（页），此时需要借助free链表，链表中的count变量记录了可用的控制块的数量，如果为0则无可用空间，如果有值，则从头开始遍历链表，找到链表前端控制块对应的缓冲页用来存放案例中的page25即可，同时要将该控制块从链表去除，并将count-1。

3. flush链表

如果修改了Buffer Pool中某个缓冲页的数据，那么它就与磁盘上的页不一致了，这样的缓冲页也被称为脏页。为了性能问题，每次修改缓冲页后，并不着急立刻把修改刷新到磁盘上，而是将被修改过的缓冲页对应的控制块作为节点加入到这个链表中，该链表被称为flush链表。

flush链表中的控制块都是脏的，同步到磁盘后会从flush链表去除。

flush链表刷新方式：（不重要。面：系统定期将脏页刷到磁盘里）

【1】从flush链表中刷新一部分页面到磁盘

后台线程会根据当时系统的繁忙程度确定刷新速率，定时从flush链表中刷新一部分页面到磁盘。——即：BUF_FLUSH_LIST

有时后台线程刷新脏页的进度比较慢，导致用户准备加载一个磁盘页到Buffer Pool中时没有可用的缓冲页。此时，就会尝试查看LRU链表尾部，看是否存在可以直接释放掉的未修改缓冲页。如果没有，则不得不将LRU链表尾部的一个脏页同步刷新到磁盘（与磁盘交互是很慢的，这会降低处理用户请求的速度）。——即：BUF_FLUSH_SINGLE_PAGE

【2】从LRU链表的冷数据中刷新一部分页面到磁盘——即：BUF_FLUSH_LRU

后台线程会定时从LRU链表的尾部开始扫描一些页面，扫描的页面数量可以通过系统变量 innodb_lru_scan_depth来指定，如果在LRU链表中发现脏页，则把它们刷新到磁盘。

控制块里会存储该缓冲页是否被修改的信息，所以在扫描LRU链表时，可以很轻松地获取到某个缓冲页是否是脏页的信息

4. LRU链表(latest recent use)

LRU链表是缓存的控制块，在于缓存，无所谓脏不脏。

查询MySQL的一些参数用命令：show variables like 'innodb_old_blocks_pct';

链表的最前边热度最高，提升热度的目的：提高内存的命中率，而不是到磁盘去命中

预读 + 全表扫：MySQL自带的预判的能力。

访问页的方式：线性、随机。表空间>区>页>记录

线性预读：如果顺序访问某个区（extent，一个区默认64个页）的页面超过了innodb_read_ahead_threshold（默认56）的值，就会触发一次异步读取下一个区中的全部的页到Buffer Pool中的请求。

随机预读：如果开启了随机预读功能（默认innodb_random_read_ahead=OFF），如果某个区（extent）有13个连续的页面都已经被加载到了Buffer Pool中，无论这些页面是不是顺序读取的，都会触发一次异步读取本区全部的页到Buffer Pool中的请求。

预读：试图加载进缓冲页的数据都是没有读过的数据，free链表和flush链表是读过的数据，所以预读有误操作的可能，造成LRU链表缓存的数据的准确性不高。（预读操作将页加载到缓冲池，但是加载进缓冲池的页数据并不一定是需要被读的）

线性预读和随机预读都可以预判，预判就可能有错误，预判加载的数据在后边不会被访问，白白加载到了缓冲池。通过线性预读和随机预读将数据刷到了缓冲池，破坏了原有的链表的热度，降低了命中率。

全表扫也会将对应的页加载到缓冲池中，之前链表的顺序也会被全表扫的结果破坏。

为了解决预读和全表扫造成的问题，将LRU链表分成了两个区：热数据和冷数据，热度大的数据在young区，预读预判的数据读到old区，所以最多只会影响链表37%的数据。

预判的数据加载到old区，但是读的时候要放到young区，也说明预判到的数据是正确的。

预读和全表扫的区别：

预读：加载（到old区，不会影响young区）预读只会影响old区。

全表扫：加载+读，加载是放到old区，读会影响young区，要将old区的数据放到young区才能读，此时热区和冷区都会影响到。为了解决这个问题，设置innodb_old_blocks_time=1000ms，意为两次访问old区的时间差小于1000ms，则认为该次访问为页的全表扫，如果超过了1000，则可以让要访问的页到young区，否则在冷区默认全表扫

针对LRU链表方案缺点的优化：

针对预读的优化：（还没读）

InnoDB规定，当磁盘上的某个页在初次加载（只是加载，没有涉及读取）到Buffer Pool中的某个缓冲页时，该缓冲页对应的控制块会被放到old区域的头部。这样预读页就只会在old区域，不会影响young区域中使用比较频繁的缓冲页。

针对全表扫描的优化：（已经读了，但读的不一定是热点数据，old/young区解决不了全表扫的问题）

（问题：全表扫【select * from table】将所有数据加载到缓冲池，会冲刷掉LRU链表的形成的业务热点数据。）

虽然首次加载放到的是old区域的头部，但是由于是全表扫秒，会对加载的数据进行访问，那么第一次访问的时候，就会将该页放到young区域的头部。这样仍然会把那些使用频率比较高的页面给“排挤”下去。

如何解决：由于全表扫描有一个特点，就是它对某个页的频繁访问且总耗时很短。所以针对这种，InnoDB规定，在对某个处于old区域的缓冲页进行第一次访问时，就在它对应的控制块中记录下这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内（即：innodb_old_blocks_time，默认为1000ms，时间间隔短默认为全表扫，默认数据并不是全部想要的，所以一直放在old区），那么该页面就不会从old区域移动到young区域的头部，否则将它移动到young区域的头部。

5. chunk和BufferPool实例（×）

默认buffer pool只有一个，但可以划分为多个。

6. Change Buffer（× ）

正常的修改操作：两次IO

将待操作的page加载到BufferPool中（IO）
对BufferPool中的页进行修改操作
对脏页进行Flush刷新到磁盘的操作（IO）

要看是不是读取操作：

读取操作：一定要加载page到BufferPoll中，返回结果给用户；
非读取操作：会对数据进行操作，但是并不需要返回结果。优化空间了。

所以简化后的修改操作：（步骤2-2和步骤3、4是读取操作）

step1：将要修改的操作页A保存到一个地方——ChangeBuffer（BufferPool中的25%的空间）
step2-1：定时对这个修改操作刷新到磁盘上（IO）
step2-2：从磁盘上读取操作对应的那个页A。
step3：merge操作，将操作行为应用到BufferPool中的页A，再将最新的结果返回给客户即可。
step4：将脏页刷新到（IO）

在MySQL5.5之前，叫insert buffer，只针对insert做了优化；现在对delete和update也有效，叫做 change buffer。它是一种应用在非唯一普通索引页（non-unique secondary index page）不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更（buffer changes），等未来数据被读取时，再将数据合并（merge）恢复到缓冲池中的技术。写缓冲的目的是降低随机读写操作的磁盘IO，提升数据库性能。

ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整参数innodb_change_buffer_max_size。

为什么写缓冲优化，仅适用于非唯一普通索引页呢？如果索引设置了唯一（unique）属性，在进行修改操作时，InnoDB必须进行唯一性检查。而Buffer Pool中仅缓存了热点的Page页，如果想要进行全表的唯一性校验，就需要到磁盘上进行全表扫描。这样 change buffer所谓的降低磁盘随机IO读写的优势就荡然无存了。

除了数据页被访问，还有哪些场景会触发刷写缓冲中的数据呢？

① 有一个后台线程，会认为数据库空闲时；
② 数据库缓冲池不够用时；
③ 数据库正常关闭时；
④ redo log写满时（几乎不会出现redo log写满，此时整个数据库处于无法写入的不可用状态）；

五、redo日志

了解：redolog的样式，为什么有它（数据库恢复），大概的结构，底层的组装 作用三个指针

如果我们只在内存的Buffer Pool中修改了页，假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交的事务在数据库中所做的更改也就丢失了。针对这种问题，怎么处理呢？（提交成功可能是写到了缓冲池，真正的提交成功是redolog写到了磁盘上）

方案1：在事务提交时，把该事务修改的所有页面都刷新到磁盘，刷新成功了才提示事务提交成功。（粒度太粗了）

（1）刷新一个完整的数据页太浪费了

虽然对只修改了一条记录，但是会将这条记录所在的页（16KB）都刷新到磁盘上，会造成大量磁盘I/O的浪费。

（2）随机I/O刷新起来比较慢

一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，并且该事务修改的这些页面可能并不相邻。这就意味着将某个事务修改的Buffer Pool中的页面刷新到磁盘时，需要进行很多的随机I/O。而随机I/O要比顺序I/O慢，尤其是机械硬盘。（刷盘的时候是随机io、日志的方式记录是顺序io）

方案2：在事务提交时，只需要把修改的内容记录一下就好了（日志）

例如：“将第0号表空间第100号页面中偏移量为1000处的值更新为2。”

1. redo简单日志类型

在对页面的修改是极其简单的情况下，redo日志中只需要记录一下在某个页面的某个偏移量处修改了几个字节的值、具体修改后的内容是什么就可以了，比如Max Row Id。（当记录里没有主键列时，row_id作为主键）

MLOG_1BYTE：表示在页面的某个偏移量处写入1字节的redo日志类型

MLOG_2BYTE：表示在页面的某个偏移量处写入2字节的redo日志类型

MLOG_4BYTE：表示在页面的某个偏移量处写入4字节的redo日志类型

MLOG_8BYTE：表示在页面的某个偏移量处写入8字节的redo日志类型

下图“页面中的偏移量”是max row id

MLOG_WRITE_STRING：表示在页面的某个偏移量处写入一个字节序列。

MLOG_1BYTE、MLOG_2BYTE、MLOG_4BYTE、MLOG_8BYTE、MLOG_WRITE_STRING是类型

2. redo复杂日志类型——MLOG_COMP_REC_INSERT

rec：冗余的；comp：紧凑的（除冗余的另外的三种）

3. redo日志组

在执行语句的过程中产生的redo日志，被innodb划分成了若干个不可分割的组。比如：更新Max Row Id属性时产生的redo日志为一组，是不可分割的；向聚簇索引/二级索引对应B＋树的页面中插入一条记录时产生的redo日志为一组，是不可分割的。

InnoDB认为，比如向某个索引对应的B+树中插入一条记录的过程必须是原子的，不能说插入了一半之后就停止了。否则就会形成一棵不正确的B+树。所以他们规定在执行这些需要保证原子性的操作时，必须以组的形式来记录redo日志。在进行恢复时，针对某个组中的redo日志，要么把全部的日志都恢复，要么一条也不恢复。

以上的redo0、redo1、redo2全部读完了看到有MLOG_MULTI_REC_END标志才会执行一次，如果没有这个标志，尽管前边的redo指令读到了，也不会执行。

蓝色方块中1代表单条日志，读完就恢复，0代表多条，需要以MLOG_MULTI_REC_END结束一次执行。

4. MTR (Mini-Transaction)

对底层页面进行一次原子访问的过程被称为一个Mini-Transaction（MTR）。

事务、语句、MTR、redo日志之间的关系：

① 1个事务可以包含N条SQL语句

② 1条SQL语句可以包含N个MTR

③ 1条MTR可以包含N条redo日志

5. redo log block

为了更好地管理redo日志，InnoDB把通过MTR生成的redo日志都放在了大小为512字节的页中，把用来存储redo日志的页成为block。

记录 --> 保存在页（16kb）--> 记录存储的索引

redolog --> 保存在block（512字节） --> undolog

LOG_BLOCK_HDR_DATA_LEN：记录的初始长度就是header的长度12，当body加入100byte会变成112，但是当body加入496byte，会直接变成512；

LOG_BLOCK_FIRST_REC_GROUP：本页面里第一个mtr的位置；

6. redo日志缓冲区——log buffer

与Buffer Pool类似，写入redo日志时也不能直接写到磁盘中，实际上在服务器启动时就向操作系统申请了一大片称为redo log buffer（redo日志缓冲区）的连续内存空间，也可以将其简称为log buffer。这片内存空间被划分为若干个连续的redo log block。

其中，用innodb_log_buffer_size来指定log buffer的大小。该启动选项默认值为16MB。

7. redo日志写入log buffer

向log buffer中写入redo日志的过程是顺序写入的，其中，buf_free是一个全局变量，该变量指明后续写入的redo日志应该写到log buffer中的哪个位置。

一个MTR执行过程中可能产生若干条redo日志，这些redo日志是一个不可分割的组，所以并不是每生成一条redo日志就将其插入到log buffer中，而是将每个MTR运行过程中产生的日志先暂时存到一个地方，当该MTR结束的时候，再将过程中产生的一组redo日志全部复制到log buffer中。

log buffer强制要求事务提交的时候，将缓存的数据提交到磁盘上，所以其生命周期就是事务开启到结束之间的生命周期。

8. redo日志刷盘和日志文件组

MTR运行过程中产生的一组redo日志在MTR结束时会被复制到log buffer中。可是这些日志总在内存里也不是办法，在一些情况下他们会被刷新到磁盘中。

log buffer空间不足50%的时候
事务提交的时候（※）
后台有一个线程，大约以每秒1次的频率将log buffer中的redo日志刷新到磁盘。
正常关闭服务器时
做checkpoint时

9. 查看redo日志相关配置信息

datedir：查看数据目录所在位置。

innodb_log_group_home_dir：指定了redo日志文件所在的目录，默认值为当前的数据目录。

innodb_log_file_size：指定了每个redo日志文件的大小，默认值为48MB。

innodb_log_files_in_group：指定了redo日志文件的个数，默认值为2，最大值为100个。

redolog --> mtr --> block --> log buffer --> log file

10. redo日志文件格式

在磁盘上。

11. lsn（logsequencenumb）

lsn全称为log sequence number，是一个全局变量，用来记录当前总共已经写入的redo日志量。

lsn初始值为8704，也就是说，一条redo日志什么也没写入的时候，lsn的值就是8704。

12. buf_next_to_write和flushed_to_disk_lsn

redo日志是先写到log buffer中，之后才会被刷新到磁盘的redo日志文件中。所以InnoDB提出了一个名为buf_next_to_write的全局变量，用来标记当前log buffer中已经有哪些日志被刷新到磁盘中了。

前面说过，lsn表示当前系统中写入的redo日志量，这包括了写到log buffer 但没有刷新到磁盘的redo日志。相应地，lnnoDB提出了一个表示刷新到磁盘中的redo日志数量的全局变量，名为flushed_to_disk_lsn。

13. block写入redo日志文件的过程

首先，系统在第一次启动后，向log buffer中写入了mtr_1(8,716～8,916)、 mtr_2(8,916～9,948)、mtr_3(9,948~10,000)这3个MTR产生的redo日志。此时的lsn已经增长到了10,000，由于没有刷新操作，此时flushed_to_disk_lsn的值仍为初始值8,704。。如下图所示：

随后，将log buffer中的block刷新到redo日志文件中。假设将mtr_1和mtr_2 的redo日志刷新到磁盘，那么flushed_to_disk_lsn就应该增长mtr_1和mtr_2写入的日志量，即：增长到9,948。如下图所示：

14. lsn值与redo日志文件组中的偏移量的对应关系

log file的偏移量从2,048开始，而lsn值从8,704开始。lsn值和redo日志文件组偏移量的对应关系如下图所示：

15. flush链表中的lsn

一个MTR代表对底层页面的一次原子访问，在访问过程中可能会产生一组不可分割的redo日志；在MTR结束时，会把这一组redo日志写入到log buffer中。除此之外，在MTR结束时还有一件非常重要的事情要做，就是把在MTR执行过程中修改过的页面加入到Buffer Pool的flush链表中。

第一次修改已经加载到Buffer Pool中的页时，会把这个页对应的控制块插入到 flush链表的头部，之后再次修改该页面时，就不再插入了。即：flush链表中的脏页是按照页面的第一次修改时间进行排序的。

在这个过程中，会在缓冲页对应的控制块中记录两个关于页何时修改的属性：

【oldest_modification】第一次修改Buffer Pool中的某个缓冲页时，就将修改该页的MTR开始时对应的lsn值写入这个属性。
【newest_modification】每修改一次页面，都会将修改该页的MTR结束时对应的lsn值写入这个属性。也就是说，该属性表示页最近一次修改后对应的lsn值。

16. 刷入磁盘

mtr1和mtr2生成的redo日志虽然已经写到磁盘上的log file中了，但是它们修改的脏页仍然留在Buffer Pool中，所以它们的redo日志不可以被覆盖。随着系统运行，如果页a从Buffer Pool中刷到了磁盘上，那么页a对应的控制块就会从 flush链表中移除掉。而且，它的redo日志占用的空间就可以被覆盖掉了。

17. checkpoint

如果redo日志对应的脏页已经刷新到磁盘中了，那么它也就失去了存在的意义了。它所占用的磁盘空间就可以被后续的redo日志所重用。InnoDB通过全局变量checkpoint_lsn，来表示当前系统中可以被覆盖的redo日志总量是多少。这个变量的初始值也是8704（因为lsn的初始值就是8704）。比如当页a被刷新到了磁盘上，mtr1生成的redo日志就可以被覆盖了，所以进行checkpoint_lsn的操作。我们把这个过程称为执行一次checkpoint。redo日志文件组中各个lsn值的关系，如下图所示：

18. innodb_flush_log_at_trx_commit

为了保证事务的持久性，用户线程在事务提交时，需要将该事务执行过程中产生的所有redo日志都刷新到磁盘中。

这个规则我们可以通过系统变量innodb_flush_log_at_trx_commit来进行配置修改，该变量有如下3个可选值:

0：在事务提交时，不立即向磁盘同步redo日志，这个任务交给后台线程来处理；
1：在事务提交时，需要将redo日志同步到磁盘。（默认值）
2：在事务提交时，需要将redo日志写到操作系统的缓冲区中，但并不需要保证将日志真正刷新到磁盘。如果操作系统挂掉了，则数据丢失。

六、undo日志

（用于事务回滚）

事务是需要保证原子性的，也就是说，事务中的操作要么全部完成，要么什么也不做。但有如下情况，会造成事务执行不完：

① 事务执行过程中可能遇到各种错误，比如：服务器宕机，操作系统异常，突然断电……

② 程序员在事务执行过程中手动输入rollback语句结束当前事务的执行。

遇到上面的情况，为了保证事务的原子性，我们需要把数据还原回原来的样子，这个过程就叫做回滚（rollback）

数据库为了回滚而记录的日志，我们就称之为撤销日志（undo log）

注意一点，由于SELECT操作并不会修改任何记录，所以并不需要记录相应的 undo日志。

1. 如何开启事务

只读事务：通过START TRANSACTION READ ONLY语句开启一个只读事务。在只读事务中，不可以对普通表进行增删改操作；但可以对临时表进行增删改操作。

读写事务：通过START TRANSACTION READ WRITE语句开启一个读写事务。使用BEGIN、START TRANSACTION语句开启的事务，默认也算是读写事务。在读写事务中可以对表执行增删改查操作。

2. 何时分配事务id

只有在事务对表中的记录进行改动时才会为这个事务分配一个唯一的事务id，否则事务id值默认为0。

只读事务何时分配事务id？只有在它第一次对某个用户创建的临时表（CREATE TEMPORARY TABLE）执行增删改操作时，才会为这个事务分配一个事务id，否则是不分配的。

读写事务何时分配事务id？只有在它第一次对某个表（包括用户创建的临时表）执行增删改操作时，才会为这个事务分配一个事务id，否则是不分配的。

3. 事务id是怎么生成的

事务id本质上就是一个数字，事务id生成策略如下：

① 内存中维护一个全局变量，每当需要为某个事务分配事务id时，就会把该变量值当作事务id分配给该事务，并且自增1。
② 每当这个变量的值为256的倍数时，就会将值刷新到系统表空间中页号为5 的页面中一个名为Max Trx ID的属性中（占用8个字节）。
③ 当系统下一次启动时，会将Max Trx ID的值加载到到内存中，并加上256之后赋值给前面提到的全局变量。

为什么要加256？答：因为上次关机时，该全局变量的值可能大于磁盘页面中的Max Trx ID属性值

4. 事务id在记录中存储的位置

trx_id表示对该条记录进行改动的语句所对应的事务id。

我们下面来看看，对表中数据进行不同操作都会产生什么样的undo日志？但是在此之前，我们先创建一张表作为下面实验用的数据表：

不同版本下，查询table_id的方式：

【mysql 5.x】select * from information_schema.innodb_sys_tables;

【mysql 8.x】select * from information_schema.innodb_tables;

5. INSERT对应的undo日志结构

TRX_UNDO_INSERT_REC类型的undo日志结构：

end of record：undoLog结束，下一条开始时在页面中的地址。

undo type：undoLog的类型，也就是TRX_UNDO_INSERT_REC。

undo no：undoLog对应的编号，在一个事务中是从0开始递增的，只要事务没提交，每生成一条undo日志，那么该条日志的undo no就加1。

table id：undoLog对应的记录所在表的table_id。

主键各列信息 <len, value>列表：主键的每个列占用的存储空间大小和真实值，如果记录中的主键包含多个列，那么每个列占用的存储空间大小和对应的真实值都需要记录下来。

start of record：上一条undoLog结束，本条开始时在页面中的地址

6. INSERT对应的undo日志操作演示

我们先插入两条记录：

# 显示开启一个事务，假设该事务的事务id为100

BEGIN;

# 插入两条记录

INSERT INTO tb_user(id, name, city) VALUES(1, 'muse','北京市'), (2, 'bob','上海市');

undoLog日志内容为：

7. INSERT操作对应的undo日志

roll_pointer本质上就是一个指向记录对应的undo日志的指针。不同的内容放到了不同的页面中，其中：

① 聚簇索引记录存放到类型为FIL_PAGE_INDEX的页面；
② undo日志存放到类型为FIL_PAGE_UNDO_LOG的页面；

聚簇索引记录和undo日志的存放位置，如下图所示：

8. DELETE操作之垃圾链表

记录的头信息中的next_record属性组成一个单向链表，把这个链表称为正常记录链表。被删除的记录其实也会根据记录头信息中的next_record属性组成一个链表，只不过这个链表中的记录所占用的存储空间可以被重新利用，所以也称这个链表为垃圾链表。Page Header 部分中有一个名为PAGE_FREE的属性，它指向由被删除记录组成的垃圾链表中的头节点。每删除一条记录，则该记录都会插入到垃圾链表的头节点处。

操作演示，有3条正常记录和2条被删除记录，他们在页中的记录分布情况如下所示：

9. 删除记录的两个步骤

第一步：delete mark阶段

仅仅将记录的deleted_flag标识位设置为1，但是这条记录并没有加入到垃圾链表中。也就是说，这条记录既不是正常记录，也不是已删除记录。在删除语句所在的事务提交之前，被删除的记录一直都处于这种中间状态（其实主要是为了实现MVCC的功能才这样处理的）。

第二步：purge阶段

当该删除语句所在的事务提交后，会有专门的线程来把该记录从正常记录链表中移除，并加入到垃圾链表中作为头节点。

10. DELETE操作对应的undoLog结构

TRX_UNDO_DEL_MARK_REC类型的undo日志结构：

info bits：记录头信息的前4个比特的值。

trx_id：旧记录的trx_id值。※

roll_pointer：旧记录的roll_pointer值，指向不同的版本。

len of index_col_info：也就是下边的【索引列各列信息】部分和本部分占用的存储空间总和。

索引列各列信息<pos, len, value>列表：凡是被索引的列的各列信息。

11. DELETE操作对应的undo日志

删除id等于1的这条记录：

# 删除一条记录 DELETE FROM tb_user WHERE id = 1;

<0,4,1> 0是主键的位置，4是主键int的长度，1是主键的值（id= 1）

<3,4,'muse'> 3是二级索引name 的相对位置，4是属性值'muse'的长度为4， 'muse'是name列的值

2是连接上面的insert插入两条记录（0和1）操作，此处的删除为第3条操作，所以是2

12. UPDATE操作对应的undoLog结构

TRX_UNDO_UPD_EXIST_REC类型的undo日志结构：

n_updated：表示本条UPDATE语句执行后将有几个列被更新。

被更新的列更新前信息<pos, old_len, old_value>列表：被更新列在记录中的位置、更新前该列占用的存储空间大小、更新前该列的真实值。

13. UPDATE操作对应的undo日志

更新id等于2的这条记录：

绿色的2代表更新的两列：sam和成都市

其中，占用空间等于：(1+1+4)+(1+1+3)=11，最后，再加上len of index_col_info属性本身占2个字节，所以总共13字节。即：len of index_col_info=13。

七、事务（本地事务）

1. ACID

Atomicity原子性：某个操作，要么全都执行完毕，要么全都回滚。（事务中的一个特性）

Consistency一致性：数据库中的数据全都符合现实世界中的约束，则这些数据就符合一致性。事务执行的结果应该符合数据库的约束条件和逻辑。比如性别约束男or女；人民币面值不能为负数；出生地址不能为null；参与转账的账户总余额不变；等等。

Isolation隔离性：~~多个事务访问相同数据时，对该数据不同状态的转换对应的数据库操作的执行顺序有一定的规律，彼此不干涉~~。在不同的业务处理过程中，事务保证了各自业务正在读、写的数据互相独立，不会彼此影响。

Durability持久性：现实中的状态转换映射到数据库中，意味着对数据所做的修改都应该在磁盘中保存。memory存储引擎是内存层面，不具有持久性。

2. 事务的状态

部分提交：redolog提交到buffer pool，尚未刷到磁盘内。（平常说的事务成功一般都是部分提交）

3. 事务并发时数据一致性问题

单线程一般不会出现问题，出现问题一般出现在多线程之间。任何数据库对“脏写”都有严格限制，“脏写”是数据库底线。以下是针对非加锁的读的一致性问题：

（1）脏读

如果一个事务（小明）读取到了另一个未提交事务（小丽） 修改过的数据，就意味着发生了脏读现象。

（2）不可重复读

如果一个事务（小丽）修改了另一个未提交事务（小明）读取的数据，就意味着发生了不可重复读现象，或者叫模糊读FuzzyRead

（3）幻读

如果一个事务（小明）先根据某些搜索条件（select ... where vip='是'）查询了一些记录，但是在该事务并未提交时，另一个事务（小丽）写入了一些符合上面搜索条件的记录（这里的写入可以值 insert、delete、update操作。例如： insert into ... values('0003',700,'是')），就意味着发生了幻读现象。

不可重复读：某一条记录；
脏读：对于某一范围内的多条记录；

（4）脏写

4. SQL事务隔离级别

由于无论哪种隔离级别，都不允许脏写的情况发生，所以没有列入到表格中。

MySQL默认在repeatable read 层就解决了幻读的问题。

serializable为加锁。

八、MVCC

readview可以当做一个snapshot（快照）

MVCC (Multi-Version Concurrency Control) ：多版本并发控制，利用记录的版本链和ReadView，来控制并发事务访问相同记录时的行为。

ReadView：一致性视图，用来判断版本链中的哪个版本是当前事务可见的。

1. 版本链

在每次更新该记录后，都会将旧值放到一条undo日志中。随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一条链表，这个链表就称之为版本链。

2. ReadView包含的内容

ReadView也叫一致性视图，用来判断版本链中的哪个版本是当前事务可见的。ReadView包含4个比较重要的内容：

m_ids：在生成ReadView时，当前系统中活跃的读写事务的事务id列表。

min_trx_id：在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。

max_trx_id：在生成ReadView时，系统应该分配给下一个事务的事务id值。

creator_trx_id：生成该ReadView的事务的事务id。

只有在对表中的记录进行改动时（即：insert、delete、update）才会为事务分配唯一的事务id，否则一个事务的事务id值都默认为0

3. 如何通过ReadView来判断记录的某个版本是否可见？

如果RC.trx_id == RV.creator_trx_id，则表明当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

如果RC.trx_id < RV.min_trx_id，则表明生成该版本的事务在当前事务生成ReadView之前已经提交了，所以该版本可以被当前事务访问。

如果RC.trx_id >= RV.max_trx_id，则表明生成该版本的事务在当前事务生成ReadView之后才开启，所以该版本不可以被当前事务访问。

如果RC.trx_id in RV.m_ids，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。  如果RC.trx_id not in RV.m_ids，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问（m_ids为空集合）。

如果某个版本的数据对当前事务不可见，那就顺着版本链找到下一个版本的数据，并继续执行上面的步骤来判断记录的可见性，以此类推，直到版本链中的最后一个版本。

4. ReadView生成的时机

通过生成时机的不同来说明read commited和repeated read解决不可重复读的问题：

READ COMMITTED和REPEATABLE READ隔离级别之间一个非常大的区别就是——它们生成ReadView的时机不同！！

READ COMMITTED——在一个事务中，每次读取数据前都生成一个ReadView。

REPEATABLE READ——在一个事务中，只在第一次读取数据时生成一个ReadView。

5. 隔离级别及事务重要参数查询和设置

show variables like 'transaction_isolation';

set session transaction isolation level read committed;

set session transaction isolation level repeatable read;

show variables like 'autocommit';

set autocommit=0;

九、锁

锁的维度：

维度1：写写、写读/读写

维度2：行锁、表锁

维度3：读锁S、写锁X

维度4：读的意向共享锁IS、写的意向独占锁IX

维度5：不同隔离级别下的加锁方式

维度6：行锁的种类

1. 并发事务问题——写-写

由于任何一种隔离级别都不允许脏写（写-写）的现象发生，所以，当多个未提交事务相继对一条记录进行改动的时候，就需要让它们排队执行。

这个排队的过程其实是通过为该记录加锁来实现的。这个锁本质上是一个内存中的结构。

2. 并发事务问题——读-写/写-读

为了避免在“读-写”情况下避免脏读、不可重复读、幻读现象，有如下两种可选的解决方案：

方案1：快照读 | 一致性读 | 一致性无锁读

读操作——MVCC；写操作——对记录进行加锁。

② 方案2：锁定读（S锁 | X锁）

读、写操作都采用加锁的方式。

如果采用MVCC方式，读-写操作彼此并不冲突，性能更高；

如果采用加锁方式，读-写操作彼此需要排队执行，从而影响性能；

所有普通的SELECT语句在READ COMMITTED或REPEATABLE READ隔离级别下都算是一致性读。所以一般来说，我们会选择采用MVCC方式来解决读-写操作并发执行的问题，但是在某些特殊的业务场景（如：银行业务，需要读取最新数据和数据准确性，对执行时间并无苛刻要求），这时我们才会选择读、写都加锁的方式。

3. 行级锁——锁定读操作

共享锁 | S锁（Shared Lock）

在事务要读取一条记录时，需要先获取该记录的S锁。（读的时候不会加锁，写的时候拒绝）
SELECT ... LOCK IN SHARE MODE;

独占锁 | 排它锁 | X锁（Exclusive Lock）

在事务要修改一条记录时，需要先获取该记录的X锁。
SELECT ... FOR UPDATE;

S锁与X锁的兼容关系如下所示：

4. 行级锁——锁定写操作

针对DELETE操作先在B+树中定位到这条记录的位置，获取这条记录的X锁，最后再执行delete mark操作。

针对INSERT操作一般情况下，新插入的一条记录受隐式锁保护，不需要在内存中为其生成对应的锁结构。

针对UPDATE操作，分为如下3种情况：

① 未修改主键并且被更新的列在修改前后所占用的存储空间未发生变化：先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，最后在原记录的位置进行修改操作。
② 未修改主键并且被更新的列在修改前后所占用的存储空间发生变化：先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，之后将原记录彻底删除掉（即：把记录彻底移入垃圾链表），最后再插入一条新记录。
③ 修改主键相当于在原记录上执行DELETE操作之后再来一次INSERT操作。加锁操作就需要按照 DELETE（MVCC）和INSERT（当前事务）的规则进行了

5. 表级锁

表级共享锁（S锁）

其他事务可以继续获得该表/该表中的某些记录的S锁。
其他事务不可以继续获得该表/该表中的某些记录的X锁。

表级独占锁（X锁）

其他事务不可以继续获得该表/该表中的某些记录的X锁或S锁。

意向共享锁（IS锁）

当事务准备在某条记录上加S锁时，首先需要在表级别加一个IS锁。

意向独占锁（IX锁）

当事务准备在某条记录上加X锁时，首先需要在表级别加一个IX锁。

6. 表级锁之间的关系

IS锁和IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录；也就是说，IS 锁和IX锁之间或者与自身都是彼此兼容的。

兼容性关系如下所示：

7. InnoDB表级锁

InnoDB存储引擎提供的表级S锁或者X锁相当“鸡肋”，只会在一些特殊情况下（比如：系统崩溃恢复时）用到。

在对某个表执行DDL语句时，其他事务在对这个表并发执行DML语句时，会发生阻塞；反之亦然。这个过程其实是通过在server层使用一种称为元数据锁（Metadata Lock，MDL）的东西来实现的，也不会使用表级S锁和X锁。

当我们使用了auto_increment修饰列的时候，就会涉及到表级AUTO-INC锁和轻量级锁。其中：innodb_autoinc_lock_mode系统变量，用来控制到底使用上述两种方式中的哪一种。

① 0：一律采用AUTO_INC锁。
② 1：混合使用。插入记录的数量确定时采用轻量级锁，不确定时采用AUTO-INC锁。
- 数量确定：insert into table values (), (), (), ();
- 数量不确定：insert into table select * from newtable
③ 2：一律采用轻量级锁。

表级AUTO-INC ：生成自增主键的时候是唯一的并且是可以插入成功的。

轻量级锁：保证生成自增id的时候不出现问题，是否能成功插入到表里不关心，对系统的消耗比较低。

数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

SELECT <字段名表>

FROM <表或视图名>

WHERE <查询条件>

示例：查询年龄大于28岁的所有学生的信息 SELECT * FROM student WHERE sage>28。

数据操作语言DML

数据操纵语言DML主要有三种形式：

1) 插入：INSERT

语法格式1： insert into 表名 values(值1，值2......，值n）；

语法格式2： insert into 表明（字段名1，字段名2，......，字段名n） values(值1,值2,......，值n)。

2) 更新：UPDATE

语法格式：update 表名 set 赋值表达式 [where 条件]

3) 删除：DELETE

语法格式：delete from 表 [where 条件]。

数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象：表、视图、索引、同义词、聚簇等。例如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

DDL操作是隐性提交的，不能rollback。

数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

1) GRANT：授权。

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。ROLLBACK：回滚。

回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK。

3) COMMIT [WORK]：提交。

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

(1)显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；

(2) 隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，

EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON [1]。

8. InnoDB行级锁——记录锁※

LOCK_REC_NOT_GAP 被称为记录锁，也就是仅仅负责把1条记录锁上的锁。

给no值为5的记录加类型为LOCK_REC_NOT_GAP的记录锁。

9. InnoDB行级锁——gap锁※

LOCK_GAP 被称为gap锁，锁住了指定记录前面的间隙，防止其间插入新记录。gap锁的提出仅仅是为了防止插入幻象记录（即：幻读现象）而提出的。

插入no=4时不能成功。

10. InnoDB行级锁——next-key锁※

LOCK_ORDINARY 被称为next-key锁，本质就是一个 记录锁 + gap锁 的合体。它既能保护该条记录，又能阻止别的事务将新纪录插入到被保护记录前面的间隙中。

11. InnoDB行级锁——插入意向锁

LOCK_INSERT_INTENTION 也被称为插入意向锁，事务在等待时也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在处于等待状态。

12. InnoDB行级锁——隐式锁1

一般情况下，执行INSERT语句是不需要在内存中生成锁结构的。

但是也会有例外，比方说：一个事务（T1）首先插入了一条记录（此时并没有与该记录关联的锁结构），然后另一个事务（T2）执行如下操作：

① 立即使用 SELECT... LOCK IN SHARE MODE 语句读取这条记录（也就是要获取这条记录的S锁），或者使用 SELECT ... FOR UPDATE 语句读取这条记录（也就是要获取这条记录的X锁），该咋办？如果允许这种情况的发生，那么可能出现脏读现象。
② 立即修改这条记录（也就是要获取这条记录的X锁）该咋办？如果允许这种情况的发生，那么可能出现脏写现象。

解决办法——使用事务id，我们把聚簇索引和二级索引中的记录分开看一下：

场景1：对于聚簇索引

有一个trx_id隐藏列，该隐藏列记录着最后改动该记录的事务id。在当前事务中新插入一条聚簇索引记录后，该记录的trx_id隐藏列代表的就是当前事务的事务id。如果其他事务此时想对该记录添加S锁或者X锁，首先会看一下该记录的trx_id隐藏列代表的事务是否是当前的活跃事务。如果不是的话就可以正常读取：如果是的话，那么就帮助当前事务创建一个 X锁的锁结构，该锁结构的is_waiting属性为false：然后为自己也创建一个锁结构，该锁结构的is_waiting属性为true，之后自己进入等待状态。

场景2：对于二级索引

本身并没有trx_id隐藏列，但是在二级索引页面的Page Header 部分有一个 PAGE_MAX_TRX_ID属性，该属性代表对该页面做改动的最大的事务id。如果 PAGE_MAX_TRX_ID属性值小于当前最小的活跃事务id，那就说明对该页面做修改的事务都己经提交了，否则就需要在页面中定位到对应的二级索引记录，然后通过回表操作找到它对应的聚集索引记录，然后再重复情景1的做法

13. InnoDB锁的内存结构

前文已经介绍过，对一条记录加锁的本质就是在内存中创建一个锁结构跟这条记录相关联。那么，我们说了这么多次的锁结构，它到底是怎么组成的呢？它其实主要是由6部分组成的。分别为：锁所在的事务信息、索引信息、表锁或行锁信息、type_mode、其他信息、与heap_no对应的比特位。

锁所在的事务信息：trx

索引信息：加锁的记录属于哪个索引

行锁信息更重要。

14. InnoDB锁的内存结构——type_mode△

15. 与heap_no对应的比特位

与heap_no对应的比特位（以n_bit=16为例）

n_bits的计算公式如下所示：n_bits = (1+((n_recs + LOCK_PAGE_BITMAP_MARGIN) / 8)) * 8

n_recs：有多少条记录，需要加上infimum和supermum

我们假设要开启一个事务T1，往 tb_user表（已存在5条记录）中表空间为67，页号为3的页面上，插入一个number=15的记录（number是主键），并为这个记录加S锁(1024)，那么我们分析一下它所生成的行级锁结构是怎么样的？

type_mode：S锁（1024）+ 不用等待（0）+ 记录锁（32）+ S锁（2）= 1058

n_bits = (1+(5+2+64)/8)*8=72

heap_no为7的位置

16. 加锁操作解析——前期准备工作

在tb_user表中，有3个字段，分别为：学号（number），学生姓名（name）和学生年龄（age）这3个字段。那么，其中number字段为聚簇索引，name字段为二级索引。

17. 加锁实战分析

那么针对于语句加锁分析，我们根据以下4类进行分析，分别为：

【1】普通的SELECT语句

【2】锁定读的语句

【3】半一致性读的语句

【4】INSERT语句

18. 加锁操作解析——普通select语句

普通的select语句其实针对于不同的隔离级别，会有不同的处理方式。（普通select语句默认情况下autocommit = 1，所以keyiren）

19. 加锁操作解析——锁定读语句

对锁定读的语句，其实可以归类为以下四种语句：

语句1：SELECT ... LOCK IN SHARE MODE;

语句2：SELECT ... FOR UPDATE;

语句3：UPDATE ...

语句4：DELETE ...

不同的隔离级别，为当前记录加不同类型的锁。在不满足查询条件的情况下，如果隔离级别为READ UNCOMMITTED或READ COMMITTED，则要释放掉加在该记录上面的锁；如果隔离级别为REPEATABLE READ或SERIALIZABLE，则不去释放记录上面的锁。

（1）SELECT ... LOCK IN SHARE MODE示例一

针对聚簇索引number作为搜索条件，隔离级别为READ UNCOMMITTED或READ COMMITTED，执行select * from tb_user where number >2 AND number <=7 AND age=25 LOCK IN SHARE MODE;

（2）SELECT ... LOCK IN SHARE MODE示例二

针对聚簇索引number作为搜索条件，隔离级别为REPEATABLE READ或SERIALIZABLE，执行select * from tb_user where number >2 AND number <=7 AND age=25 LOCK IN SHARE MODE;

（3）SELECT ... LOCK IN SHARE MODE示例三

针对二级索引name作为搜索条件，隔离级别为READ UNCOMMITTED或READ COMMITTED，执行select * from tb_user FORCE INDEX(idx_name) where name >= 'james' AND name <='tom' AND age=20 LOCK IN SHARE MODE;

（4）SELECT ... LOCK IN SHARE MODE示例四

针对二级索引name作为搜索条件，隔离级别为REPEATABLE READ或SERIALIZABLE，执行select * from tb_user FORCE INDEX(idx_name) where name >= 'james' AND name <='tom' AND age=20 LOCK IN SHARE MODE;

（5）UPDATE ... 示例一

隔离级别为READ UNCOMMITTED或READ COMMITTED，执行update tb_user set name = 'unknown' where number >2 AND number <=7 AND age<25;

（6）UPDATE ... 示例二

隔离级别为REPEATABLE READ或SERIALIZABLE，执行update tb_user set name = 'unknown' where number >2 AND number <=7 AND age<25;

（7）SELECT ... FOR UPDATE 示例 & DELETE ... 示例

SELECT ... FOR UPDATE语句的加锁过程与SELECT ... LOCK IN SHARE MODE语句类似，区别是为记录加X锁。

DELETE ... 语句的加锁过程与UPDATE的处理方式相同，当表中包含二级索引，那么二级索引记录在被删除之前都需要加X型记录锁。

对于UPDATE和DELETE语句来说，在对被更新或者被删除的二级索引记录加锁的时候，实际上加的是隐式锁，但是效果与X型记录锁一样。

对于隔离级别为READ UNCOMMITTED和READ COMMITTED的情况，采用的是一种称为半一致读的方式来执行UPDATE语句。

20. 二级索引精准匹配加锁流程

当隔离级别为READ UNCOMMITTED和READ COMMITTED的情况，如果匹配的模式为精准匹配，那么将不会为扫描区间后面的下一条记录加锁。比如我们执行select * from tb_use where name='muse' for update。那么加锁情况如下所示：

当隔离级别为REPEATABLE READ或SERIALIZABLE的情况，如果匹配的模式为精准匹配，那么会为扫描区间后面的下一条记录加gap锁。比如我们执行select * from tb_use where name='muse' for update。那么加锁情况如下所示：

21. 二级索引找不到记录

当扫描区间中没有记录，且为精确查找，隔离级别为REPEATABLE READ或SERIALIZABLE，那么也要为扫描区间后面的下一条记录加一个gap锁。比如执行：select * from tb_user where name='moon' FOR UPDATE。如下所示：

当扫描区间中没有记录，且不是精确查找，隔离级别为REPEATABLE READ或 SERIALIZABLE，那么也要为扫描区间后面的下一条记录加一个next-key锁。比如执行： select * from tb_user where name>'m' and name<'t' FOR UPDATE。如下所示：

22. 左闭区间加锁

当隔离级别为REPEATABLE READ或SERIALIZABLE，使用聚簇索引，并且扫描区间为左闭区间，如果定位到的第一个聚簇索引记录的number值正好与扫描区间中最小的值相同，那么会为该聚簇索引记录加X类型的记录锁。例如：select * from tb_user where number>=3 FOR UPDATE；加锁情况如下所示：

23. 自右向左扫描加锁

当隔离级别为REPEATABLE READ或SERIALIZABLE，从右向左的顺序扫描记录，会给匹配到的第一条记录的下一条记录加gap锁。例如：select * from tb_user where name >='john' and name <=tom and age=20 order by name DESC FOR UPDATE;

24. 加锁操作解析——半一致性读的语句

当隔离级别为READ UNCOMMITTED或READ COMMITTED且执行UPDATE语句时，将会使用半一致读。

什么是半一致读呢？就是当UPDATE语句读取到已经被其他事务加了X锁的记录时，InnoDB会将该记录的最新提交版本读出来，然后判断该版本是否与UPDATE语句中的搜索条件相匹配。如果不匹配，则不对该记录加锁，从而跳到下一条记录；如果匹配，则再次读取该记录并对其进行加锁。这样做的目的就是让UPDATE语句尽量少被别的语句阻塞。