InnoDB学习总结~（4.9）（索引，事务，锁）-优快云博客

本文链接：https://blog.youkuaiyun.com/HideonHacker/article/details/137547247

本文详细介绍了InnoDB数据库的页结构、行格式、页溢出处理、主键排序策略、页目录与目录页的作用，以及事务、隔离级别、MVCC和锁的概念，包括读写锁、行锁和表锁的区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引

数据库处理原理：

将数据库的Date取出来，存放到内存中，再通过Cpu的计算，将局部信息展现给用户。取数据的单位InnoDB的页。
页的大小为16kb，即每次取数据会取大概16kb的数据。

InnoDB页的结构

InnoDB的页分为以下7层

File Header 文件头
Page Header 页头（此为数据页专用，页还有日志页等形式）
Infimun + Supermun Records 最小记录和最大记录
UserRecords 用户记录层
FreeSpace 空闲空间（与用户记录的和保持不变，用户记录多空闲就少，反之亦然）
Page Directory 页尾
File Trailer 文件尾

InnoDB的行格式

一个页可以存在多个行，相当于Table中的行的形式。行存储的信息除了记录信息外还存在着许多的额外信息行的格式如下可分为：

变长字段长度列表（即对于 varchar 等形式的数据，存储的空间是变化的，存储的就是这样类型的长度。这样做是为了方便统计每行的大小）
NULL标志位（将null的位置保存起来，这样数据行可以省去记录null，这样减少了空间的使用）
记录头信息（指向下一行的指针，使得每行的数据都是连在一起的，相当于链表）
数据列1…
除了这些可视化数据，行中还存在着三个隐藏数据：
row_id 这是在表中不存在主键，且不存在唯一键的时候才会生成的，主要是代替主键进行索引
transation_id 事务id，这是用来存储上次更改该行的事务的数据信息。
roll_pointer 回滚指针，标志事务回滚时的位置

页溢出

表示行数据大于页面数据的最大值16kb，导致一页无法储存一行的数据。

解决方法

InnoDB默认使用主键排序

这样做的目的是为了方便查找。可以快速的排除不存在的数据。

页目录存在的意义

InnoDB的索引是一个B+树，页目录相当于B+数据的根，会将数据进行分组后将每组的起始值存在页目录中，这样在查找的时候可以先比较页目录，找到查询的区间。在去遍历区间数据，减少查询的时间，增加效率。这样还可以使用二分查找优化查询的速率。

二者的区别就是页目录存储的是页中数据的位置，而目录页则存储的是页的位置。目录页中是以kv的形式来存储的，k为页中最小的主键，而v则表示页数。

InnoDB插入自动生成b+树

b+树的排序是依靠主键来决定的，如果不存在主键则是通过唯一节点来确定。如果二者都没有。则使用行数据中的隐藏数据row_id进行排序。row_id是唯一且自增的。
主键推荐小且自增的形式，因为主键决定了行的大小，页面大小在确定为16kb的情况下，行增大意味着每页存储的数据变少。这样就需要增加页的数量。会导致b+树变深影响查找的效率。

InnoDB生成b+树的原理

在插入数据时，InnoDB会生成第一页存放数据，在页存储空间满了的情况下，会拷贝第一页数据作为第二页，而原来的第一页数据则会更改为目录页。因为目录页短时间不会改变的特性，这样目录页可以缓存在内存中。因而加快查询速度。

辅助索引：除开主键索引以为的索引都为辅助索引

辅助索引的创建步骤，InnoDB的处理方式

先比较列（如果是单列则进行数据比较，如果是多列，则按列的顺序进行比较。例如比较列1，在列1不相等的情况直接确定顺序。如果列1相等则比较列2，以此类推）
将列值最为key存储起来（多列索引则将所有的列值组合作为key）
value 则是存储主键的值，这样可以减少索引的储存空间。在查询的时候，根据辅助索引查询出主键，再根据主键进行“回表”操作。即根据主键索引来查询完整数据。

在查询数据很多的情况，查询优化器会使用全表扫描的方式进行查询，而不使用索引。

mysql中 utf8 是3字节的数据，而我们平时的utf8是4字节的。所以mysql定义了utf8mb4，这种utf84字节的形式，一般在创建数据库的时候我们都选择utf8mb4这样的存储格式。

数据库优化小技巧：

在数据的查询中，后置索引无法使用即 like %xxx 的方式查询较慢。
解决方法是：将字段倒置，及将数据反转过来，将后置的索引变为前置索引存储在数据库中，这样就可以设置索引了。在获取数据后再在程序中进行数据反转，转为正常数据会加快数据库的查询效率。

在范围查找中会有以下的过程：

先找到最小值
再找到符合的最大值
返回两值的区间

多列索引查找中，只有第一个列会匹配到索引，后续查找无法使用索引

select * from table where b > 1,c = 1;

在这样的查找语句中，在第一个条件b > 1的时候会使用到 bcd 的多列索引快速确定位置，后续条件c = 1时不会匹配索引，只会进行全表扫描。因为无法匹配 XCX的索引形式。

但是使用order by排序的时候就可以进行索引查找，因为在创建多列索引时，索引也是按照bcd的形式排序的。

索引创建时，存储的值可能不止索引key和value，除开这些之外可能还存存储主键或row_id，这样在索引key相同的情况下可以使用主键或row_id来区分。

事务

事务是什么？

事务是一种操作流程，在数据库中表现为一系列CRUD操作的集合体，事务具有原子性，即一系列操作要么全都执行，如果其中一行操作出错则全部都不执行，数据会回滚到执行前的形式。
事务的存在保证了用户操作的可靠性。

事务的隔离级别有那些

读未提交，这是事务隔离的最低级别，即事务未提交修改的数据时，别的事务也可以通过查询操作获取到数据。导致在事务出错回滚的时候，读取到错误的数据。称为脏读。
读已提交，这个隔离级别是解决了，脏读的情况，读只会读取已经commit的事务，这样能很大概率的减少读取错误数据的可能性。
可重复读，为了解决“不可重复读”的问题，在两次读取中间，用户修改了某条数据并commit则会导致两次读取的数据不一样。而可重复读则是解决了这个问题，在一个事务两次间隔的读取，读取到的值是相同的。
串行化，是解决了可重复读中幻读的问题，即在两次范围性读取中，另一个事务创建了一个新数据并commit，导致两次读取数据不一致。串行化通过排队的形式，在一个事务活跃的时候，另一个事务无法修改数据。解决了这个问题，但这也导致了并发性能的减弱。

而在mysql中，可重复读的隔离级别也解决了幻读的问题，所以我们一般都是使用可重复读，这样相较于的串行化提升了并发性能，也杜绝了幻读的风险。

在mysql中，存在事务自动提交的选项，即autocommit

自动提交和手动提交的区别就是在于，手动提交需要先 begin开启一个事务，并在事务中进行一系列报错，使用commit提交后才会更改数据给别的数据查到。但是自动提交省去了这一步骤，每一句sql都是单一的事务，不需要begin开启和commit提交关闭。虽然这样的形式可以在不手动开创事务的时候进行回滚，但也导致了，不可以进行多条sql操作的情况。

隐式提交

在事务begin之后，使用create等创建修改数据库的操作时，事务会自动进行commit无需手写commit

保存点

这是事务进行回滚的重要标志，保存点保存的是一个回滚的位置，在没有设置事务的保存点时，回滚是清除事务的全部操作。而设置了保存点则是清楚事务中保存点之后的操作，而保存点之前的操作会进行保留。这样在不小心写错一行sql的时候也不需要回滚在最开始的时候重新开始写。

版本链

版本链即，在修改或是创建一个数据的时候，会自动生成InnoDB行属性的隐藏属性transation_id和回滚指针。事务id保存的是当前修改改行的事务id，回滚指针则是指向修改前的数据，这样回滚的时候就可以直接返回修改前的数据。

数据库中实现读已提交和可重复读都是通过ReadView，称为快照。

这个快照是一个数组，保存的是当前事务在开启时已经存在的活跃事务，即还未commit结束的事务，这样就可以通过对照ReadView和行数据中的transation_id来判断，这条数据是在事务开启前就修改了的，还是事务开启后才修改的，从而解决了事务隔离级别的问题。
而对于不同的隔离级别的实现，也有少少的不一样。在读已提交的级别中，在commit后会修改事务的ReadView，将已经commit的事务剔除出去。而可重复读则不会将commit剔除而是一直保存。因为可重复读需要保证的是事务开始到结束读取的数据是一样的。