MySQL原理学习笔记

MySQL原理学习笔记

1. 事务

1.1 事务简介

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系
统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

1.2 事务的四大特性

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

ACID

1.3 并发事务问题

事务并发问题

问题 描述
脏读 一个事务读到另外一个事务还没有提交的数据。
不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现该行数据已经存在，好像出现了“幻影”。

事务隔离级别

隔离级别 赃读 不可重复读 幻读
Read uncommitted √ √ √
Read committed × √ √
Repeatable Read(默认) × × √
Serializable × × ×
  • 事务隔离级别越高，数据越安全，但是性能越差

2. 存储引擎

2.1 MySQL体系结构

  1. 连接层
    最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  2. 服务层
    第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
  3. 引擎层
    存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
  4. 存储层
    数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询
    日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互

2.2 存储引擎介绍

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

2.3 InnoDB存储引擎特点

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

特点

  • DML操作遵循ACID模型，支持事务；能保证事务安全
  • 行级锁，提高并发访问性能；
  • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

文件

  • xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结
    构（frm-早期的 、sdi-新版的）、数据和索引

逻辑存储结构:

InnoDB引擎与MyISAM引擎的区别 ?

  • InnoDB引擎, 支持事务, 而MyISAM不支持。
  • InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
  • InnoDB引擎, 支持外键, 而MyISAM是不支持的

3. 索引

3.1 索引概述

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

  • 无索引的情况：全表扫描
  • 有索引：根据索引依次查询，提高效率
类型 描述
优势 提高数据检索的效率，降低数据库的IO成本。
通过索引对数据进行排序，降低数据排序的成本，减少CPU的消耗。
劣势 索引列会占用额外的存储空间。
虽然索引提高了查询效率，但会降低更新表的速度（如INSERT、UPDATE、DELETE操作）。

3.2 索引结构

3.2.1 有哪些索引结构

索引结构 描述
B+Tree索引 最常见的索引类型，大部分引擎都支持 B+ 树索引。
Hash索引 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
Full-text(全文索引) 通过建立倒排索引快速匹配文档的方式。类似于ES。

3.2.2 二叉树

所以，如果选择二叉树作为索引结构，会存在以下缺点：

  • 顺序插入时，会形成一个链表，查询性能大大降低。
  • 大数据量情况下，层级较深，检索速度慢。

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树

  • 即使如此，由于红黑树也是一颗二叉树，也会存在一个缺点：大数据量情况下，层级较深，检索速度慢

3.2.3 B树

B树是一种多叉路平衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉

3.2.4 B+树

B+Tree 与 B-Tree相比，主要有以下三点区别：

  • 所有的数据都会出现在叶子节点。
  • 叶子节点形成一个单向链表。
  • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，形成双向链表，便于范围查询，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序

3.2.5 Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决

  • Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
  • 无法利用索引完成排序操作
  • 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

3.2.6 思考题

为什么InnoDB存储引擎选择使用B+tree索引结构?

  • 相对于二叉树，层级更少，搜索效率高；
  • 对于B树，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
  • 相对Hash索引，B+tree支持范围匹配及排序操作

3.3 索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引

分类 含义 特点 关键字
主键索引 针对于表中主键创建的索引 默认自动创建，只能有一个 PRIMARY
唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个
全文索引 查找文本中的关键词，而不是比较索引中的值 可以有多个 FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：聚集索引&二级索引

分类 含义 特点
聚集索引(Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个
二级索引(Secondary Index) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

  • 聚集索引的叶子节点下挂的是这一行的数据 。
  • 二级索引的叶子节点下挂的是该字段值对应的主键值。
  • 回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

3.4 SQL性能分析工具

3.4.1 SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了

3.4.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志

通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化

3.4.3 profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

3.4.4 explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行
过程中表如何连接和连接的顺序

字段 含义
id select查询的序列号，表示查询中执行select子句或操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）。
select_type 表示SELECT类型，常见取值：
• SIMPLE（简单表，不使用表连接或子查询）
• PRIMARY（主查询，外层查询）
• UNION（UNION中第二个或后面的查询语句）
• SUBQUERY（SELECT/WHERE后的子查询）
type 表示连接类型，性能由好到差为：NULL > system > const > eq_ref > ref > range > index > all。
possible_keys 显示可能用到的索引，可能应用在这张表上的索引（一个或多个）。
key 实际使用的索引，NULL表示未使用索引。
key_len 索引使用的字节数（最大可能长度，非实际长度），在保证精确性前提下越短越好。
rows MySQL认为必须执行查询的行数（InnoDB引擎中是估计值，可能不准确）。
filtered 返回结果行数占需读取行数的百分比，值越大越好。

3.5 索引使用

3.5.1 最左前缀法则

联合索引：这个索引就叫联合索引，它包含了age和sex两个字段
CREATE INDEX idx_user_age_sex ON users (age, sex);

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)

  • 对于最左前缀法则指的是，查询时，最左边的列必须存在，否则索引全部失效。
  • 而且中间不能跳过某一列，否则该列后面的字段索引将失效

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效

  • 当范围查询使用>= 或 <= 时，走联合索引了
  • 所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

3.5.2 索引失效情况

  1. 索引列运算
    • 不要在索引列上进行运算操作， 索引将失效。
  2. 字符串不加引号
    • 字符串类型字段使用时，不加引号，索引将失效
  3. 模糊查询
    • 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效, %工程
  4. or连接条件
    • 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
  5. 如果MySQL评估使用索引比全表更慢，则不使用索引

3.5.3 覆盖索引

覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

  • 尽量使用覆盖索引，减少select *。

一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对
以下SQL语句进行优化, 该如何进行才是最优方案:

1
select id,username,password from tb_user where username = 'itcast';

答案: 针对于 username, password建立联合索引, sql为: 

1
create index idx_user_name_pass on tb_user(username,password);

这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。避免回表查询

3.5.4 前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

前缀长度如何确定？

  • 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

3.5.5 单列索引与联合索引

  • 单列索引：即一个索引只包含单个列。
  • 联合索引：即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引

3.6 索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含
    NULL值时，它可以更好地确定哪个索引最有效地用于查询

4. 锁

4.1 锁的概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

  • 全局锁：锁定数据库中的所有表。
  • 表级锁：每次操作锁住整张表。
  • 行级锁：每次操作锁住对应的行数据

4.2 全局锁

4.2.1 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

  • 其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

4.2.2 特点

  • 数据库中加全局锁，是一个比较重的操作，存在以下问题：
    • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
    • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

4.3 表级锁

4.3.1 介绍

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

对于表级锁，主要分为以下三类：

  • 表锁
  • 元数据锁（meta data lock，MDL）
  • 意向锁

4.3.2 表锁

对于表锁，分为两类：

  1. 表共享读锁（read lock）
  2. 表独占写锁（write lock）

语法：
加锁：lock tables 表名… read/write。
释放锁：unlock tables / 客户端断开连接

  • 读锁不会阻塞其他客户端的读，但是会阻塞写。
  • 写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写

4.3.3 元数据锁

meta data lock , 元数据锁，简写MDL。

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)

4.3.4 意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

有了意向锁之后 :

  1. 客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁
  2. 而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

4.4 行级锁

4.4.1 介绍

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

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

  • 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
  • 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
  • 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

4.4.2 行锁

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

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

两种行锁的兼容情况如下：

当前锁类型\请求锁类型 S（共享锁） X（排他锁）
S（共享锁） 兼容 冲突
X（排他锁） 冲突 冲突

常见的SQL语句，在执行时，所加的行锁如下：

SQL 语句 行锁类型 说明
INSERT … 排他锁 自动加锁
UPDATE … 排他锁 自动加锁
DELETE … 排他锁 自动加锁
SELECT（正常） 无锁
SELECT … LOCK IN SHARE MODE 共享锁 需要手动在 SELECT 之后加 LOCK IN SHARE MODE
SELECT … FOR UPDATE 排他锁 需要手动在 SELECT 之后加 FOR UPDATE

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  • 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  • InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁

4.4.3 间隙锁&临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  • 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁
  • 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
  • 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

5. InnoDB引擎

5.1 逻辑存储结构

  1. 表空间
    • 表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。
  2. 段
    • 段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。
  3. 区
    • 区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
  4. 页
    • 页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
  5. 行
    • 行，InnoDB 存储引擎数据是按行进行存放的
    • 在行中，默认有两个隐藏字段：
      • Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
      • Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

5.2 架构

5.2.1 概述

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发
中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

5.2.2 内存结构

在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 接下来介绍一下这四个部分。

Buffer Pool 缓冲区

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。

在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

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

Change Buffer 更改缓冲区

Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer的意义是什么呢?
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO

Adaptive Hash Index自适应哈希索引

自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。

InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

Log Buffer日志缓冲区

Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

5.2.3 磁盘结构

再来看看InnoDB体系结构的右边部分，也就是磁盘结构

System Tablespace

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)

File-Per-Table Tablespaces

如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中

General Tablespaces

通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

Undo Tablespaces

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存undo log日志。

Temporary Tablespaces

InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

Doublewrite Buffer Files

双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据

Redo Log

重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用

5.2.4 后台线程

后台线程的作用是将缓冲区的数据写入到磁盘文件中的线程

在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread

  1. Master Thread
    核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性， 还包括脏页的刷新、合并插入缓存、undo页的回收 。
  2. IO Thread
    在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。
  3. Purge Thread
    主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。
  4. Page Cleaner Thread
    协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞

5.3 事务原理

5.3.1 事务基础

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

ACID

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环
    境下运行。
    持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

5.3.2 redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用

5.3.3 undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制)

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

  • Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
  • Undo log存储：undo log采用段的方式进行管理和记录

5.4 MVCC

5.4.1 基本概念

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode(共享锁)，select … for update、update、insert、delete(排他锁)都是一种当前读

快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

  • Read Committed：每次select，都生成一个快照读。
  • Repeatable Read：开启事务后第一个select语句才是快照读的地方。
  • Serializable：快照读会退化为当前读

在当前默认的RR隔离级别下，开启事务后第一个select语句才是快照读的地方，后面执行相同的select语句都是从快照中获取数据，可能不是当前的最新数据，这样也就保证了可重复读

MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView

5.4.2 隐藏字段

隐藏字段 含义
DB_TRX_ID 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

5.4.3 undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

  • 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
  • 而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录

5.4.4 readview

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id

包含了四个核心字段：

字段 含义
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID（当前最大事务ID+1）
creator_trx_id ReadView创建者的事务ID

具体判断逻辑大致是：

  • 如果行的trx_id < min_trx_id，说明这个版本在当前事务开始前就提交了 → 可见 ✅。
  • 如果行的trx_id在活跃事务列表中，说明这个版本是未提交的 → 不可见 ❌。
  • 如果行的trx_id > max_trx_id，说明这个版本来自未来事务 → 不可见 ❌。

不同的隔离级别，生成ReadView的时机不同：

  • READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
  • REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView，从而保证可重复读

5.4.5 原理分析

在MVCC（多版本并发控制）中，ReadView 主要的作用是：
确定当前事务在可重复读快照下，应该“看到”哪些行版本。避免读到其他事务未提交或刚提交的数据。

1
2
3
4
5
6
7
8
9
10
事务开始 → 创建ReadView  
|
|→ 查询时，遍历记录版本链
|   |
|   |→ 判断版本trx_id是否符合ReadView
|   |   |
|   |   |→ 不符合，继续沿 undo log 向前找版本
|   |   |→ 符合，返回数据
|
事务提交 → 不需要更新ReadView

END