MySQL2_2单表查询

发表于 2020-05-27 更新于 2025-07-07 分类于 MySQL

只涉及一张表的情况下查询语句的执行过程。

阅读全文 »

MySQL2_3多表查询

发表于 2020-05-27 更新于 2025-07-07 分类于 MySQL

分析多表查询，也就是分析MySQL中连接（Join）是怎么执行的。

阅读全文 »

MySQL2_4查询成本分析

发表于 2020-05-27 更新于 2025-07-07 分类于 MySQL

MySQL执行查询语句的成本分析

阅读全文 »

redo log

实现持久性的方式：
1、在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘；
但是只修改了一点数据也刷新整个页比较浪费，且一次写入可能涉及到很多不连续的页，这样随机 IO 效率比较低。
2、把修改了哪些东西记录一下就好，即使系统崩溃也能恢复
即 redo log，优点是占用空间小、顺序写入。

WAL（Write-Ahead Logging）

先写日志，再写磁盘，同时，InnoDB 引擎会在系统相对空闲的时候将操作记录同步到磁盘上。
WAL 保证了 InnoDB 是crash-safe的，因为即使数据库发生异常重启，之前提交的记录都不会丢失。

redo log 的结构

每条 redo log 会记录以下属性：

type：该条 redo 日志的类型。
space ID：表空间 ID。
page number：页号。
data：该条 redo 日志的具体内容。

对不同类型字段作修改时会记录不同类型的 redo log，比如：

表中没有主键时，会生成一个row_id隐藏列保存到 data 字段里；
涉及变长字符串类型的 redo log 因为不确定具体占用多少字段空间，因此 data 字段中还有一个len字段。

Mini-Transaction

在 MySQL 中对底层页面中的一次原子访问的过程称之为一个Mini-Transaction，简称mtr，一个 mtr 可以包含一组 redo log，在进行崩溃恢复时这一组 redo log 是一个不可分割的整体。
比如插入一条记录的时候，如果数据页的空闲空间不足，需要进行页分裂操作：新建一个叶子节点，然后把原先数据页中的一部分记录复制到这个新的数据页中，然后再把记录插入进去，把这个叶子节点插入到叶子节点链表中，最后还要在内节点中添加一条目录项记录指向这个新创建的页面。这个过程中需要对多个页面进行改改，因此会产生多条 redo log，这个过程必须是原子的，InnoDB 会以组的形式来记录 redo log，崩溃恢复时要么整组恢复、要么一条也不恢复，因此被称为悲观插入。

如果数据页的空闲空间充足则可以直接插入，这种方式被称为乐观插入。

redo 日志的存储结构

MySQL-redolog
MySQL 会向操作系统申请一块redo log buffer连续内存空间，这块内存空间之后被划分为若干连续 redo log block。
InnoDB 会维护一个全局变量buf_free，指示后续 redo 日志应该写入到 log buffer 中的哪个位置。
最终 redo log 会被刷新到磁盘中被称为block的页中，其中关键字段包括：

该 block 的唯一标识；
第一条 redo 日志偏移量：一个 mtr 会生成多条 redo 日志记录（redo log record），这些日志被统称为一个 redo 日志记录组（redo log record group），block 会记录这个 redo 日志记录组第一条记录的偏移量。
checkpoint 的序号。
lsn：每条日志都有一个序列号Log Sequence Number，简称为lsn，它的值是不断增长的，初始值为 8704，lsn 值越小，则说明该 redo log 生成的时间越早。

redo log 刷盘时机

当内存数据页和磁盘数据页内容不一致时，我们称这个内存页为”脏页”，内存数据写入到磁盘后，内存和磁盘上的数据页内容就一致了称为”干净页”，，redo log 就是内存数据页，而 B+树结构的聚簇索引就是磁盘数据页。
redo log 会被复制到 log buffer 中，但是 log buffer 的空间是有限的，当满足一定条件时需要被刷新到磁盘里：

log buffer 空间不足时
log buffer 的大小是有限的（通过系统变量innodb_log_buffer_size指定），当要读入的数据页没有在内存中的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页就必须将脏页先刷到磁盘，变成干净页后才能复用。
事务提交时
我们前边说过之所以使用 redo 日志主要是因为它占用的空间少，还是顺序写，在事务提交时可以不把修改过的 Buffer Pool 页面刷新到磁盘，但是为了保证持久性，必须要把修改这些页面对应的 redo 日志刷新到磁盘。
后台线程的执行
后台有一个线程，大约每秒都会刷新一次 log buffer 中的 redo 日志到磁盘。
正常关闭服务器时
做checkpoint时

批量从 flush 链表中刷出脏页

我们在介绍 Buffer Pool 的时候说过，一般情况下都是后台的线程在对 LRU 链表和 flush 链表进行刷脏操作，这主要因为刷脏操作比较慢，不想影响用户线程处理请求。但是如果当前系统修改页面的操作十分频繁，这样就导致写日志操作十分频繁，系统 lsn 值增长过快。如果后台的刷脏操作不能将脏页刷出，那么系统无法及时做 checkpoint，可能就需要用户线程同步的从 flush 链表中把那些最早修改的脏页（oldest_modification 最小的脏页）刷新到磁盘，这样这些脏页对应的 redo 日志就没用了，然后就可以去做 checkpoint 了。

查看各种 LSN 值

1	SHOW ENGINE INNODB STATUS

Log sequence number：代表系统中的 lsn 值，也就是当前系统已经写入的 redo 日志量，包括写入 log buffer 中的日志。
Log flushed up to：代表 flushed_to_disk_lsn 的值，也就是当前系统已经写入磁盘的 redo 日志量。
Pages flushed up to：代表 flush 链表中被最早修改的那个页面对应的 oldest_modification 属性值。
Last checkpoint at：当前系统的 checkpoint_lsn 值。

innodb_flush_log_at_trx_commit

为了保证事务的持久性，一般来说用户线程在事务提交时需要将该事务执行过程中产生的所有 redo 日志都刷新到磁盘上。
但是出于效率上的考虑，可以修改innodb_flush_log_at_trx_commit的取值来调整这个过程：

0：当该系统变量值为 0 时，表示在事务提交时不立即向磁盘中同步 redo 日志，这个任务是交给后台线程做的。
这样很明显会加快请求处理速度，但是如果事务提交后服务器挂了，后台线程没有及时将 redo 日志刷新到磁盘，那么该事务对页面的修改会丢失。
1：当该系统变量值为 1 时，表示在事务提交时需要将 redo 日志同步到磁盘，可以保证事务的持久性。1 也是innodb_flush_log_at_trx_commit的默认值。
2：当该系统变量值为 2 时，表示在事务提交时需要将 redo 日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘。
这种情况下如果数据库挂了，操作系统没挂的话，事务的持久性还是可以保证的，但是操作系统也挂了的话，那就不能保证持久性了。

崩溃恢复

1、确定恢复的起点
checkpoint_lsn之前的 redo 日志都可以被覆盖，也就是说这些 redo 日志对应的脏页都已经被刷新到磁盘中了，既然它们已经被刷盘，我们就没必要恢复它们了。对于checkpoint_lsn之后的 redo 日志，它们对应的脏页可能没被刷盘，也可能被刷盘了，我们不能确定，所以需要从checkpoint_lsn开始读取 redo 日志来恢复页面。
redo 日志文件组的第一个文件的管理信息中有两个 block 都存储了 checkpoint_lsn 的信息，我们当然是要选取最近发生的那次 checkpoint 的信息。衡量 checkpoint 发生时间早晚的信息就是所谓的 checkpoint_no，我们只要把 checkpoint1 和 checkpoint2 这两个 block 中的 checkpoint_no 值读出来比一下大小，哪个的 checkpoint_no 值更大，说明哪个 block 存储的就是最近的一次 checkpoint 信息。这样我们就能拿到最近发生的 checkpoint 对应的 checkpoint_lsn 值以及它在 redo 日志文件组中的偏移量 checkpoint_offset。
2、确定恢复的终点
普通 block 的 log block header 部分有一个称之为 LOG_BLOCK_HDR_DATA_LEN 的属性，该属性值记录了当前 block 里使用了多少字节的空间。

对于已经填满的 block 来说，该值就是 512，也就是说如果该值不是 512，那么它就是这次崩溃恢复中需要扫描的最后一个 block 了。
3、恢复
确定起点和终点后，我们就可以按照 redo log 的顺序依次扫描checkpoint_lsn之后的各条 redo 日志来执行恢复了。

redo log flush

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
MySQL 偶尔的抖动，很有可能就是在刷脏页（flush）。
有以下几种情况都会引起 flush：

redo log 写满了，需要释放一些空间，将 checkpoint 往前推进，并将之间的日志对应的脏页都 flush 到磁盘上。
系统内存不足，需要新的内存页时内存不够用了，需要淘汰一些数据页，空出内存来给其他数据页使用，如果淘汰的是脏页，则需要先将脏页写入到磁盘。
空闲期间，MySQL 会自动用过剩的计算能力执行 flush 任务。
正常关闭时，MySQL 会将内存的脏页都 flush 到磁盘。

bin log

WAL 机制保证了 MySQL 数据不会丢失，WAL 的核心是 bin log 和 redo log。

bin log 与 redo log 区别

1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

bin log 只能用于归档，没有 crash-safe 能力，而 redo log 是 InnoDB 的，可以提供 crash-safe 能力。

binlog 写入机制

事务执行过程中，先把日志写到 binlog cache；
事务提交的时候，再把 binlog cache 写入到 binlog 文件中。

binlog 写入的关键是要保证原子性：
MySQL-binlog写盘

每个线程有自己的 binlog cache，但是共用同一份 binlog 文件；
上图的write指的是把日志写入文件系统的page cache，并没有把数据持久化到磁盘，所以速度较快；
上图的fsync才是将数据持久化到磁盘的操作。

redo log 会被先写入到 redo log buffer 内，分以下几种情况：
MySQL-redolog存储状态

存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。

redo log buffer 写入磁盘的时机：

后台线程每秒轮询，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘；
redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

bin log 格式

bin log 有 3 种格式：

statement: 存的是语句原文，可能会导致主备不一致，比如在主库和之后在备库上执行时选取的索引不一样；
row: 会记录具体作用的目标数据，比较占用空间、且会消耗大量 IO 资源；
比如一条 delete 语句，statement 格式的 bin log 会直接记录该语句，而 row 格式会记录具体删除的记录的 ID。
mixed: 自动判断 SQL 语句是否可能导致主备不一致，若有可能则采用 row，否则 statement。

查看 bin log

本地创建配置文件：

[mysql]
[mysqld]
log-bin=mysql-bin
expire-logs-days=14
server-id=1
binlog_format=statement

使用 Docker 启动 MySQL 进程，注意-v 前面是宿主机的配置文件所在目录，后面是容器内的配置文件目录：

1	sudo docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -v /Users/huanggaochi/Downloads/mysql:/etc/mysql/conf.d -d mysql:5.7

连接时如果遇到文件，可以使用docker logs [CONTAINER ID]查看容器启动日志。

连接 MySQL 后查看 bin log 是否有被开启：

1	show variables like 'log_bin';

下面是测试用 SQL 语句：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;

运行后，查看 bin log：

1	show binlog events in 'mysql-bin.000001';

undo log

需要回滚的情况：
1、事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。
2、程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前的事务的执行。
为了有需要时能够回滚，每当我们要对一条记录做改动时都需要将回滚时所需的东西都记录一下，包括：
1、插入记录时，记录主键，这样回滚时直接删除这条记录即可；
2、删除记录时，将这条记录的内容记录下来，回滚时重新插入即可；
3、修改记录时，将旧值记录下来，回滚时重新更新回旧值。

事务 ID（trx_id）

分配时机

如果某个事务执行过程中对某个表执行了增、删、改操作，那么 InnoDB 存储引擎就会给它分配一个独一无二的事务 id。
1、只读事务（START TRANSACTION READ ONLY）
只读事务中不可以对普通的表进行增删改操作，但可以对临时表做增、删、改操作。
对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话是不分配事务 id 的。
2、读写事务（START TRANSACTION READ WRITE、BEGIN、START TRANSACTION）
在读写事务中可以对表执行增删改查操作。
对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话也不会分配事务 id

总而言之，只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的事务 id。

生成方式

和 row_id 的生成方式类似：

服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务 id 时，就会把该变量的值当作事务 id 分配给该事务，并且把该变量自增 1。
每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 5 的页面中一个称之为 Max Trx ID 的属性处，这个属性占用 8 个字节的存储空间。
当系统下一次重新启动时，会将上边提到的 Max Trx ID 属性加载到内存中，将该值加上 256 之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于 Max Trx ID 属性值）。

undo log 格式

1、查看 table id
记录 undo log 时会使用到表的 table id，这个值可以通过SELECT * FROM information_schema.innodb_sys_tables WHERE name = 'database_name/undo_demo';这条命令来查看
2、INSERT 操作的 undo log

undo no 在一个事务中是从 0 开始递增的，也就是说只要事务没提交，每生成一条 undo 日志，那么该条日志的 undo no 就增 1。
主键可能是有多个列组成的，如果有多个列，则每个列占用的存储空间大小和对应的真实值都需要记录下来。
比如对下面这条插入了两条记录的 SQL：

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

# 插入两条记录
INSERT INTO undo_demo(id, key1, col) 
    VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');

针对这两条数据生成的 undo log 如下所示：

3、DELETE 操作对应的 undo log
插入到页面中的记录会通过头信息中的next_record属性组成一个单向链表，而被删除的记录则会组成另一个链表，Page Header中的PAGE_FREE属性指向了这个链表的头节点。

删除时会先将记录的delete_mask标识位设置为 1，表示已经被逻辑删除了。

当该删除语句所在的事务提交之后，会有专门线程将记录真正地清除掉：将该记录从链表中移除并移入自由链表中。

由上可知，在事务执行完毕之前，记录并不会被真正地清除，所以回滚时也只需要将这个删除标识清除即可。
3、UPDATE 操作的 undo log
不更新主键的情况下，如果更新后的列和更新前的列值占用的存储空间一样大，那么就可以执行就地更新，直接在原记录上修改对应列的值；但是如果有任何一个列更新后占用的存储空间大小有变化，那么就需要将旧的记录从聚簇索引页面中删除（这是真正的删除，不是逻辑删除），然后创建一条新的记录插入到页面中。
更新主键的情况下，旧记录会执行delete mark操作，由一个后台线程做 purge 操作，将其加入到垃圾链表中。

roll_pointer 隐藏列

每条记录的结构中都包含了一个 roll_pointer 隐藏列，其实这个字段是指向该记录对应 undo log 的指针。

undo log 写入过程

https://juejin.im/book/5bffcbc9f265da614b11b731/section/5c923cf3f265da60fb3bea67

slow log

查看慢查询日志

查看是否开启慢查询日志：

1	show variables like "%slow%";

使用 sql 命令开启慢查询日志：

1	set global slow_query_log=1

设置慢查询阈值，执行超过该时间的 sql 将被视作慢查询：

1	set global long_query_time=4

注意修改这个阈值后需要重新连接或新开一个会话才能看到修改值。

MySQL4_1事务

发表于 2020-05-25 更新于 2025-07-07 分类于 MySQL

事务的使用

事务的状态

事务是一个抽象的概念，它对应一个或多个数据库操作。根据这些操作所执行的不同阶段，我们可以把事务大致划分为以下几个状态：
MySQL-事务状态
如上图可知，事务从活动的开始，直到提交或中止状态时生命周期才算结束，当事务是已提交的，它做的修改会持久生效（事务的持久性），当事务中止，该事务所做的一切修改都会被回滚（原子性）。
1、活动的（active）
事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。
2、部分提交的（partially committed）
当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。
3、失败的（failed）
当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。
4、中止的（aborted）
事务执行到一半出错会变为失败状态，此时，需要进行回滚，即撤销失败事务对当前数据库造成的影响，回滚完毕后，事务就处在了中止的状态。
比如 A 向 B 转账，A 账户扣除后遇到错误，导致 B 账户余额没变，此时需要将 A 账户的扣减操作回滚，恢复到原来的金额。
5、提交的（committed）
当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

语法

1、开启事务

1 2	-- 注意后面的WORK可有可无 BEGIN [WORK];

或者

1
2
3

-- START TRANSACTION和BEGIN的区别主要是前者还能指定事务的访问模式，如果不设置访问模式，
则默认是READ WRITE模式
START TRANSACTION [READ ONLY | READ WRITE | WITH CONSISTENT SNAPSHOT];

需要注意的是，begin/start transaction并不是事务的起点，当执行到第一个操作 InnoDB 表的语句时事务才真正启动。或者可以使用start transaction with consistent snapshot来立刻启动。
2、提交事务

1	COMMIT [WORK];

3、手动中止事务

1	ROLLBACK [WORK];

注意，ROLLBACK 语句是我们程序员手动的去回滚事务时才去使用的，如果事务在执行过程中遇到了某些错误而无法继续执行的话，事务自身会自动的回滚。
4、事务中涉及到的所有表都支持事务
目前只有 InnoDB 和 NDB 存储引擎是支持事务的，如果某个事务中操作的表使用的是不支持事务的表，则对这些表所做的修改将无法被回滚。
5、自动提交

1	SHOW VARIABLES LIKE 'autocommit';

如果这个自动提交开关是打开的，则只要我们不显式使用 START TRANSACTION 或者 BEGIN 语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交。
6、隐式提交
就算 autocommit 开关是关闭的，如果我们输入了某些语句还是会触发隐式的提交，包括：

DDL：CREATE、ALTER、DROP
对系统表执行的操作：ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD
事务控制或关于锁定的语句：当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务；或原来 autocommit 为 OFF 的情况下将其修改为 ON，也会触发隐式提交；或者使用 LOCK TABLES、UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。
加载数据的语句：比如我们使用 LOAD DATA 语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。
关于 MySQL 复制的一些语句：使用 START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO 等语句时也会隐式的提交前边语句所属的事务。
其它的一些语句：使用 ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET 等语句也会隐式的提交前边语句所属的事务。
7、保存点
在事务执行的某个阶段设置检查点，下次回滚时可以回滚至此处，而不是从头开始：
1
2
3
SAVEPOINT 保存点名称;
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;
RELEASE SAVEPOINT 保存点名称;

事务的属性

事务具有以下 4 个基本特征：

原子性(Atomic)

事务中包含的每个操作都被看做一个逻辑单元，这个逻辑单元中的操作要么全部成功，要么全部失败。（记录之前的版本，允许回滚）。

一致性(Consistency)

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。换句话说，事务执行前和事务执行后数据内在的逻辑始终是成立的。比如转帐前后两人的存款总和始终不变。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这时数据库就处于一种不正确的状态，或者说是不一致的状态。例如在一在进行转账的操作中，需要从账户 A 取出 100 元转入账户 B。那么就可以定义一个事务，该事物包括两个操作：第一个操作是从账户 A 中减去 100 元，第二个操作是向账户 B 中转入 100 元。这两个操作要么全做，要么全不做。全做或者全不做，数据库就会处于一致性状态。如果只做一个操作，则逻辑上就会发生错误，减少或增加 100 元，数据库就处于不一致的状态了。所以说一致性和原子性是密不可分的。

但是现在问题来了——原子性就一定能够保证一致性吗？
答案是否定的：原子性不能完全保证一致性。因为在多个事务并行进行的情况下，即使保证了每个事务的原子性，仍然可能导致数据不一致的结果。例如事务 1 需要将 100 元转入账户 A：先读取 A 的账户余额的值，然后在这个值上加上 100.但是，在这两个操作之间，事务 2 修改了账户 A 的值，为它增加了 100 元，那么最后结果应该是 A 增加了 200 元。但事实上，当事务 1 最终完成时，账户 A 只增加了 100 元，因为事务 2 的执行结果被事务 1 覆盖掉了。所以为了保证并发事务的一致性，就引入了事务的隔离性。(事务开始和结束之间的中间状态不会被其他事务看到)

隔离性(Isolation)

一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，T2 要么在 T1 开始之前就已经结束，要么在 T1 结束之后才开始，这样每个事务都感觉不到有其他事务在并发的执行。关于事务的隔离性数据库提供了多种隔离级别，后面会提到。(适当地破坏一致性来提升性能与并行度例如：最终一致 ~= 读未提交)

持久性(Durability)

持久性是指一个事务一旦提交，它对数据库中数据的改变就是永久性的。接下来的操作或故障不应该对其执行结果有影响。(每一次的事务提交之后就会保证不丢失)

事务隔离级别

事务并发执行可能遇到的问题

在不保证串行执行的情况下，多个事务的并行执行可能会导致一些问题，按由重到轻分别为：
1、脏写（Dirty Write）：一个事务修改了另一个未提交事务修改过的数据；
2、脏读（Dirty Read）：一个事务读到了另一个未提交事务修改过的数据；
3、不可重复读（Non-Repeatable Read）：一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值；
4、幻读（Phantom）：一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。
5、丢失更新：两个事务在并发下同时更新，后一个事务的更新覆盖了前一个事务的更新；此时前一个事务再去查询时会发现刚才修改的内容没有被修改，好像丢失了更新；丢失更新可以发生在事务的提交和回滚阶段；丢失更新的解决方案是加悲观锁（for update）或乐观锁（）。

隔离级别越低，越严重的问题就越可能发生，按隔离级别的严格程度由轻到严分别为：
1、READ UNCOMMITTED：未提交读。
2、READ COMMITTED：已提交读。
3、REPEATABLE READ：可重复读。
4、SERIALIZABLE：可串行化。

隔离级别 | 脏读 | 不可重复读 | 幻读 | 丢失更新
READ UNCOMMITTED | 会 | 会 | 会 | 会
READ COMMITTED | 不会 | 会 | 会 | 会
REPEATABLE READ | 不会 | 不会 | 会 | 会
SERIALIZABLE | 不会 | 不会 | 不会 | 不会

设置隔离级别

以可重复读为例，设置某次连接的事务隔离级别：

1	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别与 MVCC、锁之间的关系

接下来我们会讨论如何实现各种隔离级别，原理是利用 MVCC 机制和各种锁，因此这里提前说明一下实现各种隔离级别所需的技术：

隔离级别 | 是否使用 MVCC | 是否使用行锁 | 是否使用间隙锁

| - | -
读未提交 | 否 | 否，忽略其他事务放置的锁 | 否
读已提交 | 是 | 是 | 否
可重复读 | 是，与读已提交的区别是创建 ReadView 的时机不同 | 是 | 是
序列化 | 否，所有事务序列化执行，没有使用 MVCC 的必要了 | 是 | 是

事务隔离级别与MVCC

MVCC 利用 Read View（一致性读视图）来表示数据一个可见的状态，当数据当前的 Read View 是不可见时，能够通过 undo log 串联起来的版本链回溯找到数据可见的版本。

undo log

在 InnoDB 引擎中，聚簇索引都会有 2 个隐藏列：

row_id 并不是必要的，我们创建的表中有主键或者非 NULL 的 UNIQUE 键时都不会包含 row_id 列

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

这个undo log就是 MVCC 的核心，undo log 的类型包括：
1、insert undo：在事务正式提交之后就可以被释放掉了，因为要回滚插入操作直接将记录删掉即可；
2、update undo：因为 update undo 还需要支持 MVCC，不能直接释放。

每次修改记录时，都会记录一条 undo log，每条 undo log 都有一个 roll_pointer 属性，因此所有 undo log 实际山可以组成一个链表，称为版本链：
MySQL-版本链
版本链的头节点就是当前记录最新的值，注意每条 undo log 都含有一个事务 id（trx_id）。

Read View (一致性读视图)

Read View 只有在 READ COMMITTED、REPEATABLE READ 两种隔离级别下才能生效。
READ UNCOMMITTED：由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本；
SERIALIZABLE：加锁；
READ COMMITTED、REPEATABLE READ：只能读到已提交的事务修改过的记录，因此需要判断一下版本链中的哪个版本是当前事务可见的。

MySQL-数据版本可见性规则
对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

落在中间部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
落在右边，表示这个版本是由将来启动的事务生成的，不可见；
如果落在左边，若 row trx_id 在数组中，表示这个版本是由还未提交的事务生成的，不可见；若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

总而言之，除了自己的更新总是可见以外，有三种情况：

版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。
虽然期间这行数据可能被修改过，但是事务不论在什么时候查询，看到这行数据的结构都是一致的，所以我们称之为一致性读。

前面提过 READ COMMITTED、REPEATABLE READ 都需要维护版本，它们之间的区别主要是生成 ReadView 的时机不同。

READ COMMITTED - 每次读取数据前生成一个 ReadView

1、首先有两个事务在执行：
ID 为 100 的事务：

# Transaction 100
BEGIN;

UPDATE hero SET name = '关羽' WHERE number = 1;

UPDATE hero SET name = '张飞' WHERE number = 1;

ID 为 200 的事务：

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

此时，表 hero 中 number 为 1 的记录得到的版本链表如下所示：
MySQL-ReadView版本链

2、上面两个事务正在执行的过程中，如果有一个使用 READ COMMITTED 隔离级别的事务开始执行：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

这个 SELECT1 的执行过程如下：

在执行 SELECT 语句时会先生成一个 ReadView，ReadView 中的 trx_id 列表为[100, 200]；
trx_id=100 的事务写入了两个版本，这些版本处在未提交事务集合内，因此不符合可见性要求，跳到下面的版本；
跳到刘备这个版本后，因为 trx_id=80，处于已提交事务集合内，是可见的，因此最终返回的版本就是这条数据。

3、提交其中一个事务
我们把事务 id 为 100 的事务提交一下，就像这样：

# Transaction 100
BEGIN;

UPDATE hero SET name = '关羽' WHERE number = 1;

UPDATE hero SET name = '张飞' WHERE number = 1;

COMMIT;

然后再到事务 id 为 200 的事务中更新一下表 hero 中 number 为 1 的记录：

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

UPDATE hero SET name = '赵云' WHERE number = 1;

UPDATE hero SET name = '诸葛亮' WHERE number = 1;

此刻，表 hero 中 number 为 1 的记录的版本链如下所示：
MySQL-ReadView版本链1

4、然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 number 为 1 的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'张飞'

这个 SELECT2 的执行过程如下：

在执行 SELECT 语句时会又单独生成一个 ReadView，该 ReadView 的 trx_id 列表为[200]，此时 trx_id 为 100 的事务已经提交了；
头两个版本诸葛亮、赵云因为版本 trx_id=200 处于未提交事务集合内，因此不可见；
下一个版本张飞可见，因此被返回。

REPEATABLE READ - 在第一次读取数据时生成一个 ReadView

READ COMMITTED 会在每次读取数据时生成 ReadView，而 REPEATABLE READ 则只在第一次读取数据时生成一个 ReadView，之后的查询就不会重复生成了。
同上一节中的例子，在 REPEATABLE READ 隔离级别的事务中多次查找这个 number 为 1 的记录

# 使用REPEATABLE READ隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值仍为'刘备'

这个 SELECT2 的执行过程如下：

因为当前事务的隔离级别为 REPEATABLE READ，而之前在执行 SELECT1 时已经生成过 ReadView 了，所以此时直接复用之前的 ReadView，快照中的 trx_id 列表为[100, 200]。
头几个版本诸葛亮、赵云、张飞、关羽均不符合可见性原则；
下一个版本刘备 trx_id 为 80，符合可见性原则，因此返回。

之后该事务不论何时查找，结果都会是这样。

更新

更新数据都是先读后写的，如果按照之前读取的规则，当前事务应该是读取不到其他更新的事务所做的修改的，但是这样可能会导致其他事务的更新丢失，因此更新操作的读取都是读当前的值，即当前读（current read）。

除了 update 语句外，如果 select 语句加锁也是当前读，可以用 lock in share mock（读锁）或 for update（写锁）语句来加锁。

MySQL-当前读
如上图所示，事务 A、B 刚开始时就创建了视图，事务 C 没有显式使用 begin/commit，表示该 update 语句本身就是一个事务。

刚开始，id 为 1 的记录的字段 k 值为 1；
事务 C 更新 k 为 2；
由于当前读的规则，事务 B 在 update 时能看到 k 为 2，并更新为 3，注意因为更新后最新版本是当前事务写入的，因此当前事务之后的读操作总是可以读到最新的数据。
事务 A 在继续查询时，依据版本链找到可见的 k 为 1。

可见，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，我们称之为一致性读。

注意，上面的事务 C 更新完后直接提交了，如果不提交，由于二段锁协议，事务 B 将一直等待 C 提交事务。
MySQL-死锁

删除

DELETE 语句并不会立即将记录从页面中清除，而是执行一个所谓的delete mark操作，为此，表的每一行记录都会额外增加一个删除版本号（delete_version）。
1、新增
| - | - | - |

id	create_version	delete_version
1	1

2、更新
采用 delete+add 的方式实现，将老数据行标志为删除，然后新增一行新的数据：

| - | - | - |

id	create_version	delete_version
1	1	2
1	2

3、删除
删除会直接将数据的删除版本号更新为当前事务的版本号：

| - | - | - |

id	create_version	delete_version
1	1	2
1	2	3

4、查询
为了避免查到旧数据或已经被其他事务更改过的数据，需要满足：

当前事务的版本号需要大于等于创建版本号；
当前事务的版本号需要小于删除的版本号。

count(*)

count(*)用于统计表中的数据量，不同的存储引擎的实现方式有所不同：

MyISAM：总行数会被存到磁盘，执行 count(*)时直接返回这个值；
InnoDB：把数据一行一行读出然后累积计数。

上述讨论的 count(*)都是不带查询条件的，不然肯定还要用到索引来过滤数据，MyISAM 也不能这么快返回。

InnoDB 不把数据总数存起来的原因是，由于 MVCC，即使多个查询是在同一时刻发生的，它们该返回多少行数据也是不确定的，如下图所示：
MVCC与count星查询
三个会话，最后查出的数据总量是不同的。
show table status命令输出结果中也有一个TABLE_ROWS字段用于显示该表当前有多少行，但是由于误差比较大，不能直接拿来用。

由于上述方法存在的缺陷，当需要获取数据库表记录总数时，我们一般会自己计数：

用缓存系统计数
由于更新数据库和更新缓存并不是一个原子操作，可能会导致其他线程读到缓存中的计数但是读不到新数据的情况。
在数据库保存计数
由于会话 A 的事务还未提交，对表 C 的技术值的修改对会话 B 就是不可见的，因此可以保证逻辑上是一致的。

注意，最好先插入操作记录再更新计数表，因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少了事务之间的锁等待，提升了并发度。

count(主键ID)、count(*)、count(1)、count(字段)之间的比较：

count(主键ID)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
因为不取值、不涉及解析数据行及拷贝字段值的操作，所以count(1)会比count(主键ID)要快。
count(字段)：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
count(*)：并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
因此count(*)会比其他方式更快。

MVCC 小结

从上边的描述中我们可以看出来，所谓的 MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ 这两个隔离级别的一个很大不同就是生成 ReadView 的时机不同：

READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成一个 ReadView；
而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个 ReadView，之后的查询操作都重复使用这个 ReadView 就好了。

可重复读的核心是一致性读（consistent read），而事务更新数据时，只能用当前读。如果当前的记录的行锁被其他事务占用，则需要进入锁等待。

事务隔离级别与锁

可重复读解决幻读问题 - 间隙锁

MySQL-当前读例子2
注意上图中的 Q1、Q2、Q3 语句末尾带了for update，说明均为当前读，并会加上写锁，可以看到，Q3 语句看到了 Session B 和 Session C 插入的记录，说明发生了幻读的现象。

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。
当前读包括写操作和加锁的查询语句（for update）。
Q2 读到了 Session B 更新的数据，这个修改结果是可以被当前读看到的，但不能称为幻读，幻读特指新插入的行被其他事务读到了。

当前读的规则是要读到所有已经提交的记录的最新值。

幻读可能导致的问题及解决办法 - 间隙锁

由于当前读只会对当前可见的那些记录进行加锁，因此上述两种情况引入的新记录都是锁不上的，锁不上一方面导致可重复读的语义被破坏，因为我要把所有满足条件的记录都锁住，但是事务执行期间却有其他事务引入的满足条件的记录没有被锁住；另一方面是数据一致性问题，如下图所示：
MySQL-幻读导致的数据不一致问题
执行期间：

经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的；
经过 T2 时刻，id=0 这一行变成 (0,5,5)；
经过 T4 时刻，表里面多了一行 (1,5,5)；
其他行跟这个执行序列无关，保持不变。

但是 binlog 中的内容：

T2 时刻，session B 事务提交，写入了两条语句；
T4 时刻，session C 事务提交，写入了两条语句；
T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。

注意 binlog 中 T1 的语句位置变到了最后提交的时候执行。

那么 update 和 insert 引入“新记录”有什么区别呢？为什么只有 insert 这种情况被称为幻读而要单独拿出来讲？其实是因为，就算我们在当前读的时候就算把所有记录都加上锁（极端情况），新插入的记录因为其还未被分配存储空间，所以我们是无法为其加锁的。
MySQL 中解决幻读的方式是间隙锁，在给对应记录加行锁的同时，MySQL 还会给行两边的间隙加间隙锁，行锁和间隙锁又合称Next-Key Lock。
不同于行锁，间隙锁之间是不存在冲突关系的，跟间隙锁存在冲突关系的，是“往这个间隙中插入一条记录”这个操作。

间隙锁的生效条件

事务的隔离级别是可重复读
查找过程中访问到的对象才会加锁
查找并非特指select，不管是delete还是update，语句执行的第一步都是先找到对象，因此这些语句都会用到锁。

间隙锁存在的问题 - 死锁

间隙锁可能会引起死锁，如下图所示：
MySQL-间隙锁引起的死锁 ……

session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10)；
session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

可见，两个 session 进入了互相等待的状态，形成了死锁，可见，死锁会导致同样的语句锁住更大的范围，这其实是影响了并发度的。
如果需要避免这种死锁，可以：

将隔离级别设置为读提交，但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。

事务隔离级别的选择

为什么选择RR？

为什么MySQL选择REPEATABLE READ作为默认隔离级别？

主从复制：使用GapLock避免主从不一致，先执行update语句的事务可以通过GapLock阻塞后执行的事务。
兼容：RR可以兼容statement格式的binlog，通过GapLock避免主从不一致；而RC无法避免。

为什么选择RC？

MySQL 默认隔离级别是RR，为什么阿里这种大厂会改成RC？
RR和RC区别

RC每次读取都会重新生成一个快照，总是读取行的最新版本
一致性读：RC支持“半一致读”，一条update语句，如果 where 条件匹配到的记录已经加锁，那么InnoDB会返回记录最近提交的版本，由MySQL上层判断此是否需要真的加锁，因此可以大大减少锁冲突。
锁机制：RC隔离级别不解决幻读问题，不会加Gap Lock和Next-Key Lock，因此锁粒度相对较小，不容易形成死锁；
主从同步：RC仅支持row格式的binlog

理由

提升并发：半一致读和锁机制
减少死锁：锁机制

QA

如何正确地删除表中的大量数据

比如，现在想要删除表中的前 10000 条数据，有以下三种方法：
* 直接执行 delete from T limit 10000;
一个大的删除语句，单个语句占用时间长，锁的时间也比较长，且大事务还会导致主从延迟。
* 在一个连接中循环执行 20 次 delete from T limit 500;
相对第 1、3 两种方式较好。
* 在 20 个连接中同时执行 delete from T limit 500。
这 20 个连接互相之间可能会产生锁冲突。

下面的更新为什么没有成功

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);

以下事务可能并不能将所有 c 都置为 0。

begin;
select * from t;
update t set c = 0 where id = c;
commit;

比如：
MySQL-更新丢失
其中，事务 A 开始后，在还未对数据进行加锁的情况下，被事务 B 修改了数据，这时，读操作由于快照读规则，只能读取到老版本数据，而写操作由于当前读规则可以读取到事务 B 写入的数据。
现实中，给数据加乐观锁时也有可能会出现这种情况。

MySQL 实现各隔离级别的方式

读未提交和串行化比较简单，读未提交完全没有做任何事务控制，串行化一个个事务轮流执行。
读已提交隔离级别下，事务会在每次执行读操作前创建一个 ReadView，记录当前正在执行中的所有事务的 trx_id 集合——当然包括当前事务，读取数据时有一个回溯版本的过程：若判断数据当前版本在 ReadView 内，则说明该事务正在执行中，不可读，因此回溯到上一个版本，直到找到一个版本不在 ReadView 内。
可重复读和读已提交原理类似，只是可重复读是在第一次执行读操作的时候生成 ReadView 的。

MySQL 怎么实现事务的 ACID

一致性
数据库通过原子性、隔离性、持久性来保证一致性，也就是说 ACID 四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。
原子性
利用 undo log，如果事务需要回滚则使用 undo log 撤销所有已经成功执行的 sql 语句。

对 delete 语句回滚即重新 insert 原语句；
对 update 回滚即 update 成旧值；
对 insert 语句回滚即 delete 该记录。
持久性
利用 redo log，事务提交前先将记录写入 redo log，提交时将 redo log 刷盘，宕机时会将 redo log 中的内容恢复到数据库，再根据 undo log 和 bin log 的内容决定应该回滚数据还是提交数据。
相对数据页来说，redo log 有以下好处：
- redo log 体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
- redo log 是一直往末尾进行追加，属于顺序 IO。效率显然比随机 IO 来的快。
隔离性
利用锁和 MVCC 机制保证隔离性。

为什么最好不要有长事务

一般当系统判断当没有事务再使用到某些回滚日志时，这些回滚日志会被删除。那么系统怎么判断一个回滚日志不会再被使用到呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

MySQL3_2索引

发表于 2020-05-24 更新于 2025-07-07 分类于 MySQL

InnoDB 记录存储结构

InnoDB 将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16 KB

行格式

指定行格式：

1	CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称、ALTER TABLE 表名 ROW_FORMAT=行格式名称

InnoDB 中提供四种可用的格式：Compact、Redundant、Dynamic 和 Compressed
行格式细节比较多，但我们只需要关注其中的部分关键字段：

row_id: InnoDB 引擎中一个表只能有一个主键，用于聚簇索引，如果表没有定义主键会选择第一个非 Null 的唯一索引作为主键，如果还没有，生成一个隐藏的 row_id 作为主键构造聚簇索引。
trx_id: 最近更改该行数据的事务 ID；
roll_ptr: undo log 的指针，用于记录之前历史数据在 undo log 中的位置；
delete bit: 索引删除标志，如果 DB 删除了一条数据，是优先通知索引将该标志位设置为 1，然后通过 purge 清除线程异步删除真实的数据。

InnoDB 数据页结构

1、数据页被组织为一个双向链表；
2、每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表；
3、每个数据页都会为存储在它里面的记录生成一个页目录，页目录又按 ID 分段形成一个个槽，遍历该槽对应分组中的记录即可快速找到指定的记录；
MySQL-页结构

数据目录

查看 MySQL 数据目录

数据目录不同于安装目录，可以使用以下命令来查看：

1	SHOW VARIABLES LIKE 'datadir';

数据目录的结构

CREATE DATABASE

在数据目录下创建一个子目录 db_name，在该子目录下再创建一个名为 db.opt 的文件，该文件中包含了该数据库的各种属性，比如该数据库的字符集（charset）、比较规则（collation）等。
可以使用 SHOW DATABASES 命令来查看有哪些数据库。

CREATE TABLE

在数据库目录（db_name）下会创建一个名为 tb_name.frm 的用于描述表结构的文件。注意这个.frm 文件是二进制文件。

表中数据的存储 - InnoDB

1、InnoDB 其实是使用页为基本单位来管理存储空间的，默认的页大小为 16KB。
2、对于 InnoDB 存储引擎来说，每个索引都对应着一棵 B+树，该 B+树的每个节点都是一个数据页，数据页之间不必要是物理连续的，因为数据页之间有双向链表来维护着这些页的顺序。
3、InnoDB 的聚簇索引的叶子节点存储了完整的用户记录，也就是所谓的索引即数据，数据即索引。

为了更好地管理这些页（目录页、数据页），InnoDB 引入了一个更高级的结构表空间（文件空间、table space、file space），这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里。表空间有许多类：
1、系统表空间（system tablespace）
默认情况下 InnoDB 会在数据目录下创建一个名为ibdata1文件，这个文件是自扩展文件，当不够用时会自动增加大小，因此不会有不够用的情况。
在一个 MySQL 服务器中，系统表空间只有一份。从 MySQL5.5.7 到 MySQL5.6.6 之间的各个版本中，我们表中的数据都会被默认存储到这个系统表空间。
2、独立表空间（file-per-table tablespace）
在 MySQL5.6.6 以及之后的版本中，InnoDB 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。
使用独立表空间时同样会在数据目录下创建一个文件，文件名为<tb_name.ibd>，用于存储该表中的数据和索引。
3、通用表空间（general tablespace）、undo 表空间（undo tablespace）、临时表空间（temporary tablespace）等。

表中数据的存储 - MyISAM

和 InnoDB 不同的是：
1、MyISAM 中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的；
2、MyISAM 并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下。
建表后，在数据目录下会新增 3 个文件 tb_name.frm、tb_name.MYD 和 tb_name.MYI。

视图的存储

我们知道 MySQL 中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个视图名.frm 的文件。

其他文件

数据目录下除了存储表数据外，还有服务器进程文件、服务器日志文件、默认/自动生成的 SSL 和 RSA 证书和密钥文件等。

MySQL 的一些系统数据库

MySQL 额外创建了几个数据库来保存一些系统信息：
1、mysql
这个数据库贼核心，它存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
2、information_schema
这个数据库保存着 MySQL 服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引吧啦吧啦。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。
3、performance_schema
这个数据库里主要保存 MySQL 服务器运行过程中的一些状态信息，算是对 MySQL 服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。
4、sys
这个数据库主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，让程序员可以更方便的了解 MySQL 服务器的一些性能信息。

表空间

表空间是什么

表空间可以理解为一个页池，当 B+树需要增加页时会从表空间中获取空闲页分配。
每个页的 File Header 由以下字段组成：

名称	占用空间大小	描述
FIL_PAGE_SPACE_OR_CHKSUM	4字节	页的校验和（checksum值）
FIL_PAGE_OFFSET	4字节	页号
FIL_PAGE_PREV	4字节	上一个页的页号
FIL_PAGE_NEXT	4字节	下一个页的页号
FIL_PAGE_LSN	8字节	页面被最后修改时对应的日志序列位置（英文名是：Log Sequence Number）
FIL_PAGE_TYPE	2字节	该页的类型
FIL_PAGE_FILE_FLUSH_LSN	8字节	仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4字节	页属于哪个表空间

1、表空间中的每一个页都对应着一个页号，也就是 FIL_PAGE_OFFSET，这个页号由 4 个字节组成，也就是 32 个比特位，所以一个表空间最多可以拥有 2³²个页，如果按照页的默认大小 16KB 来算，一个表空间最多支持 64TB 的数据。表空间的第一个页的页号为 0，之后的页号分别是 1，2，3…依此类推。
2、表空间中的每一个页都对应着一个页号，也就是 FIL_PAGE_OFFSET，这个页号由 4 个字节组成，也就是 32 个比特位，所以一个表空间最多可以拥有 2³²个页，如果按照页的默认大小 16KB 来算，一个表空间最多支持 64TB 的数据。表空间的第一个页的页号为 0，之后的页号分别是 1，2，3…依此类推
3、每个页的类型由 FIL_PAGE_TYPE 表示，比如像数据页的该字段的值就是 0x45BF，我们后边会介绍各种不同类型的页，不同类型的页在该字段上的值是不同的。

独立表空间结构

区（extent）

在表空间和页之间还有一个中间结构，称为区（extent）。对于 16KB 的页来说，连续的 64 个页就是一个区，也就是说一个区默认占用 1MB 空间大小。
不论是系统表空间还是独立表空间，都可以看成是由若干个区组成的，每 256 个区被划分成一组。

如上图可知，第一个组最开始的 3 个页面的类型是固定的，也就是说 extent 0 这个区最开始的 3 个页面的类型是固定的，分别是：

FSP_HDR 类型：这个类型的页面是用来登记整个表空间的一些整体属性以及本组所有的区，也就是 extent 0 ~ extent 255 这 256 个区的属性，稍后详细唠叨。需要注意的一点是，整个表空间只有一个 FSP_HDR 类型的页面。
IBUF_BITMAP 类型：这个类型的页面是存储本组所有的区的所有页面关于 INSERT BUFFER 的信息。当然，你现在不用知道啥是个 INSERT BUFFER，后边会详细说到你吐。
INODE 类型：这个类型的页面存储了许多称为 INODE 的数据结构，还是那句话，现在你不需要知道啥是个 INODE，后边儿会说到你吐。

其余各组最开始的 2 个页面的类型是固定的，也就是说 extent 256、extent 512 这些区最开始的 2 个页面的类型是固定的，分别是：

XDES 类型：全称是 extent descriptor，用来登记本组 256 个区的属性，也就是说对于在 extent 256 区中的该类型页面存储的就是 extent 256 ~ extent 511 这些区的属性，对于在 extent 512 区中的该类型页面存储的就是 extent 512 ~ extent 767 这些区的属性。上边介绍的 FSP_HDR 类型的页面其实和 XDES 类型的页面的作用类似，只不过 FSP_HDR 类型的页面还会额外存储一些表空间的属性。
IBUF_BITMAP 类型：上边介绍过了。

为什么要引入区的概念？实际上按之前讨论过的 B+树的结构已经能应付正常的页分配、回收操作了，引入区是为了更好地利用空间局部性，如果对页的位置不作限制，页之间可能离得特别远，导致频繁的随机 IO，而一个区是由连续的 64 个页组成的，能减少这种随机 IO 的情况。

段（segment）

如果将所有页都放到同一个区内，因为页分叶子节点（数据页）和非叶子节点（目录页），我们执行范围查询的时候是对叶子节点进行的，如果分配到一块，会导致范围查询时从各种区跳来跳去。
因此，InnoDB 又引入了段的概念，存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说，一个索引是由一个叶子节点段和一个非叶子节点段组成的。

碎片（fragment）

前面提到的段有浪费空间的问题，因为不管多大的表，都会给分配至少两块相同大小的区，即使这张表中的数据量非常小。
因此，InnoDB 又引入了碎片（fragment）区的概念，在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段 A，有些页用于段 B，有些页甚至哪个段都不属于。因此，为某个段分配存储空间时：
1、在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
2、当某个段已经占用了 32 个碎片区页面之后，就会以完整的区为单位来分配存储空间。
因此，段实际上是由一些零散的页面和一些完整区的集合。

区的分类

1、空闲的区：现在还没有用到这个区中的任何页面。
2、有剩余空间的碎片区：表示碎片区中还有可用的页面。
3、没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。
4、附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外 InnoDB 还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。
这四种区对应 4 种状态（State）：
状态名含义
FREE 空闲的区
FREE_FRAG 有剩余空间的碎片区
FULL_FRAG 没有剩余空间的碎片区
FSEG 附属于某个段的区

需要再次强调一遍的是，处于 FREE、FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，算是直属于表空间；而处于 FSEG 状态的区是附属于某个段的。

XDES Entry

InnoDB 使用一种称为 XDES Entry（Extent Descriptor Entry）的结构来管理这些区，每个区都对应着一个 XDES Entry 结构，这个结构记录了对应的区的一些属性。

从图中我们可以看出，XDES Entry 是一个 40 个字节的结构，大致分为 4 个部分，各个部分的释义如下：

Segment ID（8 字节）
每一个段都有一个唯一的编号，用 ID 表示，此处的 Segment ID 字段表示就是该区所在的段。当然前提是该区已经被分配给某个段了，不然的话该字段的值没啥意义。
List Node（12 字节）
这个部分可以将若干个 XDES Entry 结构串联成一个链表
如果我们想定位表空间内的某一个位置的话，只需指定页号以及该位置在指定页号中的页内偏移量即可。所以：
Pre Node Page Number 和 Pre Node Offset 的组合就是指向前一个 XDES Entry 的指针
Next Node Page Number 和 Next Node Offset 的组合就是指向后一个 XDES Entry 的指针。
State（4 字节）
这个字段表明区的状态。可选的值就是我们前边说过的那 4 个，分别是：FREE、FREE_FRAG、FULL_FRAG 和 FSEG。具体释义就不多唠叨了，前边说的够仔细了。
Page State Bitmap（16 字节）
这个部分共占用 16 个字节，也就是 128 个比特位。我们说一个区默认有 64 个页，这 128 个比特位被划分为 64 个部分，每个部分 2 个比特位，对应区中的一个页。比如 Page State Bitmap 部分的第 1 和第 2 个比特位对应着区中的第 1 个页面，第 3 和第 4 个比特位对应着区中的第 2 个页面，依此类推，Page State Bitmap 部分的第 127 和 128 个比特位对应着区中的第 64 个页面。这两个比特位的第一个位表示对应的页是否是空闲的，第二个比特位还没有用。

利用 XDES Entry 链表向段中插入数据的过程

当段中数据量比较少时，首先会查看表空间中是否有状态为 FREE_FLAG 的区，也就是有空闲空间的碎片区，如果找到了则从中取一些零散的页将数据插入，否则，从表空间申请一个状态为 FREE 的区，并把该区的状态变为 FREE_FLAG，并从中取一些页将数据插入，直到这个区没有空闲空间，则状态变成 FULL_FLAG。
当表空间的大小增大到一定的程度，这个查询操作无疑会成为瓶颈，在 InnoDB 中这个问题是通过 XDES Entry 的 List Node 来解决的：

把状态为 FREE 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就称之为 FREE 链表。
把状态为 FREE_FRAG 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就称之为 FREE_FRAG 链表。
把状态为 FULL_FRAG 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就称之为 FULL_FRAG 链表。

将记录插入段中的基本过程：
1、这样每当我们需要 FREE_FLAG 状态的区时，可以直接从 FREE_FLAG 链表中取；
2、当节点对应的区已经没有剩余的空间时，则修改这个节点的 State，并将其从 FREE_FLAG 链表移动到 FULL_FRAG 链表；
3、如果 FREE_FLAG 链表中一个节点都没有，则从 FREE 链表中取一个节点移动到 FREE_FLAG 链表，并修改该节点的 State 值为 FREE_FLAG。

段中所有区的空间都已用完，需要申请更多的空闲区，但是怎么知道段中的区都已经满了呢？我们之前只提到表空间是有 FREE、FREE_FLAG、FULL_FLAG 这三种链表的，实际上段空间也需要：

FREE 链表：同一个段中，所有页面都是空闲的区对应的 XDES Entry 结构会被加入到这个链表。注意和直属于表空间的 FREE 链表区别开了，此处的 FREE 链表是附属于某个段的。
NOT_FULL 链表：同一个段中，仍有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。
FULL 链表：同一个段中，已经没有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。

链表基节点（List Base Node）

上述的每个链表都有一个 List Base Node，该结构中包含了链表的头节点和尾节点的指针以及这个链表中包含了多少节点的信息：

其中：

List Length 表明该链表一共有多少节点，
First Node Page Number 和 First Node Offset 表明该链表的头节点在表空间中的位置。
Last Node Page Number 和 Last Node Offset 表明该链表的尾节点在表空间中的位置。

List Base Node 总是被放在表空间的固定位置，因此主要用于定位链表位置。

段的结构

段是一个逻辑上的概念，它由若干个零散的页面及一些完整的区组成。像每个区都有对应的 XDES Entry 来记录该区中的属性，段也定义了一个 INODE Entry 来记录段中的属性：

其中：

Segment ID
就是指这个 INODE Entry 结构对应的段的编号（ID）。
NOT_FULL_N_USED
这个字段指的是在 NOT_FULL 链表中已经使用了多少个页面。
3 个 List Base Node
分别为段的 FREE 链表、NOT_FULL 链表、FULL 链表定义了 List Base Node，这样我们想查找某个段的某个链表的头节点和尾节点的时候，就可以直接到这个部分找到对应链表的 List Base Node。
Magic Number：
这个值是用来标记这个 INODE Entry 是否已经被初始化了（初始化的意思就是把各个字段的值都填进去了）。如果这个数字是值的 97937874，表明该 INODE Entry 已经初始化，否则没有被初始化。（不用纠结这个值有啥特殊含义，人家规定的）。
Fragment Array Entry
我们前边强调过无数次段是一些零散页面和一些完整的区的集合，每个 Fragment Array Entry 结构都对应着一个零散的页面，这个结构一共 4 个字节，表示一个零散页面的页号。

各种类型的页面

FSP_HDR 类型

名称中文名占用空间大小简单描述
File Header 文件头部 38 字节页的一些通用信息
File Space Header 表空间头部 112 字节表空间的一些整体属性信息
XDES Entry 区描述信息 10240 字节存储本组 256 个区对应的属性信息
Empty Space 尚未使用空间 5986 字节用于页结构的填充，没啥实际意义
File Trailer 文件尾部 8 字节校验页是否完整

File Space Header 部分

名称占用空间大小描述
Space ID 4 字节表空间的 ID
Not Used 4 字节这 4 个字节未被使用，可以忽略
Size 4 字节当前表空间占有的页面数
FREE Limit 4 字节尚未被初始化的最小页号，大于或等于这个页号的区对应的 XDES Entry 结构都没有被加入 FREE 链表
Space Flags 4 字节表空间的一些占用存储空间比较小的属性
FRAG_N_USED 4 字节 FREE_FRAG 链表中已使用的页面数量
List Base Node for FREE List 16 字节 FREE 链表的基节点
List Base Node for FREE_FRAG List 16 字节 FREE_FRAG 链表的基节点
List Base Node for FULL_FRAG List 16 字节 FULL_FRAG 链表的基节点
Next Unused Segment ID 8 字节当前表空间中下一个未使用的 Segment ID
List Base Node for SEG_INODES_FULL List 16 字节 SEG_INODES_FULL 链表的基节点
List Base Node for SEG_INODES_FREE List 16 字节 SEG_INODES_FREE 链表的基节点

TODO

表空间配置和优化

表数据存储位置：innodb_file_per_table

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
建议将这个值设置为 ON，因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

收缩表空间（重建表）

表数据是存储在 B+树的叶子节点（数据页）上的，将一行数据删除置灰将这条记录标记为删除，空间并不会被释放，只有当该页的所有记录都被删除后，该页才会被标记为可复用。
当我们将整个表的数据删除，所有的数据页都会被标记为可复用，但是磁盘上的文件并不会变小，造成空洞。
可以使用alter table A engine=InnoDB命令来重建表，MySQL 会自动完成建立临时表、转存数据、交换表名、删除旧表的操作。
在往临时表写入数据的过程中如果有新数据写入到表 A 的话，就会造成数据丢失，因此在整个 DDL 过程中表 A 不能有更新，这将阻塞正常的数据库语句执行，因此说这个 DDL 不是 Online 的，但是在 MySQL5.6 之后开始引入Online DDL，对这个操作流程做了优化：

建立一个临时文件，扫描表 A 主键的所有数据页；
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
用临时文件替换表 A 的数据文件。

重建表的过程中，允许对表 A 进行增删改操作，虽然刚开始 DDL 需要拿到 MDL 写锁，但是在真正拷贝数据之前就退化成了读锁，因此并不会阻塞增删改操作。

B+树索引的结构

InnoDB 和 MyISAM 会自动为主键或者声明为 UNIQUE 的列去自动建立 B+树索引，但是如果我们想为其他的列建立索引就需要我们显式的去指明。

CREATE TALBE 表名 (
    各种列的信息 ··· ,
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;
-- 例子
CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);
ALTER TABLE index_demo DROP INDEX idx_c2_c3;

使用索引的优点

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性；
大大加快数据查询速度；
加速表和表之间的连接；
减少查询中分组和排序的时间。

没有索引时的查找规则

根据搜索条件不同分两种情况：
1、以主键为搜索条件
在页目录中使用二分查找法快速定位到对应的槽，然后遍历该槽对应分组中的记录即可定位目标记录；
2、以其他列作为搜索条件
只能从最小记录开始遍历单链表中的每条记录，效率非常低。

使用索引的缺点

创建索引和维护索引要耗费时间，并且随着数据量的增加耗费的时间也会增加；
索引需要占用磁盘空间，除了数据表占数据空间（一个数据文件）外，每一个索引还需要占用一定的磁盘空间（一个索引文件），如果有大量的索引，索引文件的大小可能超过文件数据本身；
当对表中的数据进行增、删、改时，索引也要动态地维护，会导致操作本身效率降低。

一个简单的索引方案

1、插入数据时需要保证主键值是递增的
每页的记录数必须不超过 3 条，因此当要插入更多数据时，必须执行一个页分裂的操作，并且这个过程中如果不满足主键的递增要求，记录则必须要执行移动操作，这个过程称为页分裂：
MySQL-数据页分裂
2、给所有页建立目录项
页的用户记录中最小的主键值用 key 来表示；页号用 page_no 表示
MySQL-目录页
如上所示，每个目录项实际上记录了对应页中最小的主键值（已经有点像 B+树的结构了）。
当要查找一个 key 时，会先在目录中根据二分法找到其所处的页，然后再到对应页中搜索。

InnoDB 索引方案

简化方案存在的一些问题

InnoDB 中使用页来管理存储空间，最多只能保证 16KB 的连续存储空间，而随着表中的数据量增多，一页是无法存储下所有的目录项的；
我们如果一页中的记录都已经被删除光了，该页也就没必要存在了，但是删除该页后却需要将其所处目录项之后的目录项都向前移动一下，这显然是非常低效的。
因此，InnoDB 中使用目录项来管理多级的页：

当我们要查找一条记录时，先根据主键值在目录项记录的页中查找，查找方式其实和之前说的一样，定位到后再去下一级的页中查找，直到查到底层数据页中的记录或根本没找到。
实际上上面描述的是一种称为 B+树的结构，不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到 B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出来，我们的实际用户记录其实都存放在 B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中 B+树最上边的那个节点也称为根节点。
在真实情况中 B+树存放的记录数是非常多的，层数也不会太高，如果每个数据页能存放 100 条记录，目录页能存放 1000 条记录，那么3层就能存1000x1000x100=1亿条数据，4 层就是 1000×1000×1000×100=1*10^11 条记录，所以我们用到的 B+树都不会超过 4 层，通过主键值去查找某条记录最多只需要做 4 个页面内的查找（查找 3 个目录项页和一个数据页），又因为在每个页面内有所谓的 Page Directory（页目录），所以在页面内也可以通过二分法实现快速定位记录。

B+树的形成过程大致如下

每当为某个表创建一个 B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个 B+树索引对应的根节点中既没有数据记录，也没有目录项记录。
随后向表中插入数据记录时，先把用户记录存储到这个根节点中。
当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页 a 中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页 b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页 a 或者页 b 中，而根节点便升级为存储目录项记录的页。

唯一索引

唯一索引和普通索引没有本质区别，主要是在插入时会检查是否违反了唯一约束。

聚簇索引

前面已经对 InnoDB 中的 B+树结构做了一个阐述，但是至今为止我们的讨论还没有涉及索引，我们称满足以下条件的 B+树是一个聚簇索引：
1、使用记录主键值的大小进行记录和页的排序
包括页内记录按主键大小排成单链表，各个页按页中记录的主键大小顺序排成双向链表，存放目录项记录的页在不同层次（B+树的每层）也是根据页中目录项记录的主键大小排序排成一个双向链表。
2、B+树的叶子节点存储的是完整数据
这种聚簇索引不需要手动用 INDEX 语句创建，InnoDB 引擎会为我们自动创建聚簇索引，实际上在 InnoDB 中聚簇索引就是数据的存储方式。

二级索引

聚簇索引只能根据主键的查询，如果我们需要根据别的列来查询数据，则必须另外建几棵对应字段的 B+树。
MySQL-二级索引
这棵 B+树为字段 a 增加了索引，和之前的聚簇索引的区别包括：
1、页内的记录是按字段 a 排列成一个单向链表；
2、各个存放数据的页也是根据页中记录的 a 列大小顺序排成一个双向链表；
3、存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的 a 列大小顺序排成一个双向链表。
4、叶子节点存储的并不是完成的数据，而是 a 列+主键这两列的值；
5、目录项记录中不再是主键+页号的搭配，而是 a 列+页号的搭配。
由于叶子节点只存储了字段 a 和主键，如果要获取完整数据，还得根据主键到聚簇索引中再查一遍，这个过程被称为回表。这种按照非主键列建立的 B+树需要一次回表操作才可以定位到完整的记录，所以这种 B+树也被称为二级索引（英文名 secondary index），或者辅助索引。

联合索引

我们也可以同时对多个列建立索引，实际上就是先按字段 a 排序然后再按另一个字段 b 排序，原理与之前的类似。

MyISAM 中的索引

InnoDB 中索引即数据，也就是聚簇索引的那棵 B+树的叶子节点中已经把所有完整的用户记录都包含了，而 MyISAM 的索引方案虽然也使用树形结构，但是却将索引和数据分开存储

将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。
当然 MyISAM 同样需要记录头信息来存储一些额外数据：

注意插入记录的时候并没有可以按照主键大小排序，因此无法直接使用二分查找。
使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录
这一点和 InnoDB 是完全不相同的，在 InnoDB 存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次回表操作，意味着 MyISAM 中建立的索引相当于全部都是二级索引！
如果有需要的话，我们也可以对其它的列分别建立索引或者建立联合索引，原理和 InnoDB 中的索引差不多，不过在叶子节点处存储的是相应的列 + 行号。这些索引也全部都是二级索引。

B+树索引的使用

索引的代价

1、空间代价
一个页所占的空间默认为 16KB，现实中这样的 B+树可能由许多页组成，非常占空间；
2、时间代价
每次对数据增删改操作时都需要修改各个 B+树索引，因为所有节点、页面、记录都是按照主键大小顺序排序的，增删改操作会破坏这个顺序，因此 InnoDB 需要一些额外的记录移位、页面分裂、页面回收等操作来维护节点和记录的顺序。

什么时候使用 B+树索引

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

该表有两个索引
1、表中的主键是 id 列，它存储一个自动递增的整数。所以 InnoDB 存储引擎会自动为 id 列建立聚簇索引。
2、显式定义了一个联合索引 idx_name_birthday_phone_number，它由 name、birthday、phone_number 三个字段组成。
画成图其结构大致如下：

下面我们分析不同的查询语句是如何使用这张表里的索引的。

全值匹配

搜索条件中的列和索引的定义一致。
这种情况要求搜索字段一致，但是顺序并没有太严格的要求，因为查询优化器会分析搜索条件并且按照可以使用的索引中列的顺序来决定使用搜索条件的先后顺序。

匹配左边的列

上面的索引中有 3 个字段，但是我们查询时并不一定会用到所有字段，如果只用到索引中左边的字段，索引页能生效，比如：

1	SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

但是如果查询条件中没有最左边的列，则索引是无法生效的，因为索引结构是先按索引字段定义顺序依次排序的。

最左前缀匹配

建立索引的本质就是对字段进行排序，很多时候字段都是字符串类型的，字符串类型字段在排序时会从前缀开始一个一个字符排序，所以我们只匹配前缀也是能够快速定位记录的，比如：

1	SELECT * FROM person_info WHERE name LIKE 'As%';

但是如果只给出了后缀或中间的某个字符串就无法利用索引了，只能执行全表扫描，比如：

1	SELECT * FROM person_info WHERE name LIKE '%As%';

如果需要按后缀查询，则可以考虑在存储时逆序存储，查询时就可以实现最左前缀匹配了。

匹配范围值

1	SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

因为 B+树索引是按该列值顺序从小到大排序的，因此匹配范围值时，只要分别找到’Asa’和’Barlow’记录，然后通过链表（同一页内是单链表，如果是跨多个数据页则会利用到页之间的双链表）取出它们之间的所有记录，如果是覆盖索引会直接返回，如果是涉及到其他字段则会再回表到聚簇索引中获取完整记录。
需要注意的是，对多个列执行范围查找时，只有对索引最左边那个列进行范围查找时才能用到 B+树索引，因为按 name 列范围查询出的记录并不是按照 birthday 列进行排序的，只有 name 值相同的情况下才会按 birthday 列进行排序，如下所示：
+—————-+———–+
| name | birthday |
+—————-+———–+
| a | x |
| a | y |
| b | x |
| b | y |
| c | z |
| … | … |
+—————-+———–+

1	SELECT * FROM person_info WHERE name >= 'a' AND name <= 'b' and birthday < 'y';

按上面条件进行查询时，先按 name 字段过滤，得到的 4 条记录中 birthday 并没有顺序，因此继续查询 birthday 列时是用不到这个 B+树索引的。
但是如果前面的列是精确查找，那么对后面的列就可以进行范围查找了，比如：

1	select * from person_info where name = 'a' and birthday < 'y';

排序

如果没有用到索引，InnoDB 排序前一般会先将数据加载到内存的sort_buffer中，或者由于数据量太大需要借助磁盘空间来存放中间结果，排序完后再将结果集返回给客户端，在 MySQL 中，这种在内存或磁盘上进行排序的方式被称为文件排序（filesort）。

explain 命令查看语句的执行情况，Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

但是如果 ORDER BY 子句使用到了我们的索引列，就可能省去 filesort 这个步骤了，因为索引本身就对记录进行了排序。
同理，使用联合索引进行排序时，要注意 ORDER BY 后字段的顺序，比如 birthday, name 就用不了上面建立的索引了。

全字段排序执行流程

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)) ENGINE=InnoDB;

1	select city,name,age from t where city='杭州' order by name limit 1000;

因为 city 字段加上了索引，因此我们的查询语句会走city这个索引，具体的执行流程如下：

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city=’杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。

MySQL-全字段排序例子

不可以使用索引进行排序的几种情况

1、ASC、DESC 混用
在使用联合索引进行排序时，要求各个排序的列的顺序是一致的，要么都是 ASC，要么都是 DESC，举个例子：

1	select * from person_info order by name, birthday desc limit 10;

这样，InnoDB 会先从索引的最左边确定 name 列最小的值，然后找到 name 列等于该值的所有记录，然后从这些记录最右边那条开始往左找 10 条记录；如果不足 10 条，则会继续往右找 name 第二小的记录，以此类推。
这个过程并不能高效利用索引，甚至不如直接利用文件排序。
2、where 子句中出现非排序使用到的索引列

1	select * from person_info where country = 'China' ORDER BY name LIMIT 10;

如上所示，name 字段是有索引的，但是 country 字段没有，因此查询时必须先把符合搜索条件 country=’China’的记录查出再执行排序，这样无法使用索引。
3、排序列包含非同一索引的列
用来排序的多个列不是同一个索引里的，则也不能使用索引来进行排序，原因和上一点其实差不多。
4、排序子句使用了复杂表达式
比如：

1	SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

原 name 排序可能是 Bbc、Cbc、abc，但是用 UPPER 计算后可能会变成 abc、bbc、cbc（默认情况下 MySQL 是会忽略大小写的区别的，这里只是作个例子）。
同理，下面这样的表达式也无法利用索引：

1	select * from person_info where grade / 100.0 > 0.8;

文件排序的例子

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) default charset = utf8mb4 ENGINE=InnoDB;

对下面 select 语句执行 explain，可以发现 Extra 中包含 filesort 选项，表示该查询语句将会使用到临时文件来执行文件排序：

1	explain select city, name,age from t where city='杭州' order by name limit 1000;

可以用下面方法来确定一个排序语句是否使用了临时文件：

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的，可以从输出中的 number_of_tmp_files 中看到是否使用了临时文件，如果这个值是 0，表示 sort_buffer_size 足够进行内存排序了，就没必要再执行文件排序了。

number_of_tmp_files 结果永远是 2 的倍数，因为 MySQL 使用归并排序算法，将数据分成多份分别排序后存在这些临时文件中，然后把这些有序文件合并成一个有序的大文件。

rowid 排序

如果表中每一行的字段很多、数据量较大，很容易超出sort_buffer的容量、并切换到文件排序，要对多个临时文件进行归并排序，效率很低。
字段数量过多的情况下，MySQL 会采用另一种方式来执行排序，这种方式只用将**要排序的列和主键加载到sort_buffer，但是这样排完序后还需要回到原表去带出需要返回的字段，需要更多次的读磁盘，所以不会被优先选择。

这个字段数量的阈值可以通过SET max_length_for_sort_data = 16;来设置。

使用索引排序

如果排序字段正好与索引字段一致，则 MySQL 会直接使用索引来进行排序，因为 B+树叶子节点的记录就是按索引定义的顺序来组织的，如果要查询的字段都在索引里面，则我们称该索引为覆盖索引，使用索引排序后可以直接使用索引中的字段返回、不需要再回表了。

分组

1	SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

上面这个语句会先按 name 值进行分组，所有 name 值相同的划分为一组，对 name 值相同的分组内再按 birthday 的值进行分组，以此类推。
如果没有索引，这个分组统计过程全部都需要在内存里实现，而因为 name,birthday,phone_number 这些字段已经有了索引，InnoDB 会直接使用索引列的顺序来进行分组。

回表的代价

1	select * from person_info where name > 'abc' and name < 'def';

上述 SQL 会先按二级索引查到位于’abc’和’def’之间的记录，因为这些记录在磁盘上是连续的、集中分布在几个相邻的页中，因此我们可以很快地读出这些记录，这种读取方式被称为顺序 IO。但是这些记录的 id 并不一定是连续的，在聚簇索引中它们可能被分布在不同的数据页中，读取它们时需要访问更多的页，这种读取方式被称为随机 IO。
随机 IO 的性能比顺序 IO 的性能低得多，一般需要回表的记录越多，使用二级索引的性能就越低，如果查询的全部记录数占总体比重过大，InnoDB 甚至会放弃聚簇索引而采用全表扫描。
那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询，因为回表的记录越少，性能提升就越高。
为了减少这种需要全表扫描的情况，我们需要遵循一些规范，比如：
1、写查询语句后使用 explain 评估效率；
2、如果查询列表是*，优化器会更倾向于使用全表扫描；
3、如果加了 LIMIT 条件，因为记录变少，优化器会更倾向于使用二级索引+回表的方式查询。
4、覆盖索引，即要查询的目标列都处在索引中，那么优化器就会直接使用索引而不是回表操作了。

挑选索引

1、只为用于搜索、排序或分组的列创建索引
为 WHERE、ORDER BY、GROUP BY 子句中的列建立索引，一般来说业务字段变更频繁，没有必要强行建立覆盖索引。
2、考虑列的基数
在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。
最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好，因为基数小的话，更有可能一次性查出很多记录，还需要执行回表操作，这样对性能损耗会比较大，索引就起不到作用了。
3、索引列的类型尽量小
在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如 TINYINT、MEDIUMINT、INT、BIGINT 中，相对 BIGINT 来说我们更优先使用 INT，因为：
数据类型越小，在查询时进行的比较操作越快（这是 CPU 层次的东东）
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
4、索引字符串值的前缀
一些字符串类型的字段，如果要完整索引会占用较大的存储空间，所以我们一般只对字符串的前几个字符进行索引，就算遇到某几条记录中索引字段的前缀相同，也能通过它们的主键回表查询再进行比对，可以大大节省存储空间。
但是如果只索引了字段的前缀，那么 ORDER BY 排序时就无法使用到索引了，因为如果前几个处于索引中的字符相同，后面的字符不同无法比较。
通过比较选取不同长度前缀的区分度，可以作为创建前缀索引的参考：

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

如果发现前缀区分度太低，也可以考虑使用后缀或原字段的 hash 字段作为索引。
5、主键插入顺序
InnoDB 中数据是存储在聚簇索引的叶子节点的，数据页和记录都是按照记录的主键从小到大排序的，如果插入数据的主键是依次增大的，那么每填满一个数据页就可以再创建一个继续插入。但是如果主键值位于某个页面的中间，那么将不得不另外执行页分裂操作，造成额外的性能损耗。
因此，我们写建表语句时一般都会给主键设置AUTO_INCREMENT属性，让存储引擎自己为表生成主键，而不是我们手动插入。
6、冗余索引
不要给一个字段重复定义索引。

为什么 InnoDB 会选错索引

可能选择错误的情况

使用组合索引时没有遵守最左前缀原则；
使用范围查询时（>,<,<>，!=,between and ,like），该条件查询右边的列都会失效。
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；
表中数据量比较小的时候，MySQL 优化引擎，会决定不实用索引；
使用 or 进行查询，联合索引会失效；
在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描。

即使我们正确使用了索引，还是有可能会出现没有命中索引的情况，这和 MySQL 中的所索引选择机制有关：

采样统计扫描行数
InnoDB 通过采样统计查询需要扫描的行数，然后在不同查询方式（使用哪些索引、要不要排序等）中选择需要扫描行数最少的那个。
采样统计时，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
判断执行语句本身要扫描的行数
explain 结果中的 rows 是预计扫描的行数，是结合所使用的索引得出的粗略结果，但是这个结果仅供参考，因为：
- 索引统计不准确，可以使用 analyze 来修正。
- 实际如何选择索引还会考虑其他因素，比如非主键索引因为要回表所以性能损耗更大，因此 InnoDB 会更倾向于选择主键索引——即使主键索引的扫描行数要多得多。

索引选择异常和处理

如果发现 MySQL 选择索引错误，可以通过下面的方法来优化：

使用force index强行选择一个索引；
修改语句，引导 MySQL 使用我们期望的索引。
建一个更合适的索引，或删掉不合适的索引；

索引优化策略

从前面对索引的讨论可以得出一些针对索引的优化策略：

覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引。
如果可以从索引中获取到所有数据，那么就不需要再去回表了。
覆盖索引必须要存储索引列的值，哈希索引、空间索引和全文索引都不存储列值，MySQL 只能使用 Btree 索引作为覆盖索引。
排序 MySQL 支持两种方式生成排序结果：通过排序操作；通过索引顺序扫描，MySQL 可以使用同一个索引既满足查找又满足排序，只有当索引的列顺序和 Order By 子句的顺序完全一致，并且所有列的排序方向也一样时，才能使用索引进行排序，其中 Order By 子句和查询限制一样，需要满足索引的最左前缀匹配，当前导列为常量时，可以不满足最左前缀的要求。
冗余和重复索引
重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，一旦发现重复索引，应该立即移除。
冗余索引与重复索引有些不同，如果创建了索引（A, B），再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引；索引（A, ID）对于 InnodDB 来说主键列已经包含在二级索引中，所以也是冗余。
应尽量扩展已有索引而不是创建新的索引，但也有时候出于性能考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询性能，特别是 count，group by 等统计查询。
未使用的索引，就是永远都用不到的索引，这种索引就是累赘，直接删除。
前缀索引和索引选择性
MySQL 无法使用前缀索引做 Group By 和 Order By，也无法使用前缀索引做覆盖扫描。
MySQL 本身不支持反向索引，但可以将字符串反转后存储，并基于此建立前缀索引。

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑以下准则：

索引并非越多越好
一个表中如有大量的索引，不仅占用磁盘空间，而且会影响 INSERT、DELETE、UPDATE 等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新。也可以在维护期间根据需要删除不再使用或者很少使用的索引。
避免对经常更新的表进行过多的索引，并且索引中的列尽可能的少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
尽量使用字段短的索引，这样可以提高索引的检索效率。如果是长度比较长的字段，应尽量使用前缀索引。
数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无需建立索引了，如果建立索引反而会严重降低更新速度。
当唯一性是某种数据本身的特征时，制定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
在频繁进行排序、分组、联合（即进行 group by、order by、join on 操作）的列上建立索引。如果待排序的列有多个，可以在这些列上建立组合索引。

update 语句的执行流程

一条 SQL 语句的整体执行流程如下：
MySQL-SQL语句执行流程

执行语句前要先通过连接器连接数据库；
分析器通过词法和语法解析得知该语句为更新语句；
优化器确定需要使用哪些索引；
执行更新语句前，将跟这个表有关的查询缓存全部失效；
执行器负责具体执行，找到该行数据并执行更新。

两阶段提交协议

MySQL-两阶段提交协议

1	update T set c=c+1 where ID=2;

对于上面这行简单的 update 语句，执行器和 InnoDB 引擎在执行时的内部流程如下：

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

这里的”内存”指的是 Buffer Pool。另外，如果不影响数据一致性，MySQL 会直接写入到 change buffer，而不是从磁盘加载页面到 Buffer Pool 中，当然这是有条件的，在 CacheBuffer 的相关内容中我们还需要再讨论。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

两阶段提交的 prepare 阶段。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

两阶段提交的 commit 阶段。

两阶段提交的异常情况

两阶段提交异常主要体现在：如果各阶段发生重启，数据库的一致性是否会受到影响。
MySQL-两阶段提交协议

图中A处，写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash）
由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
图中B处，即bin log写完，redo log还没commit前发生crash
这时，如果redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。
这里，时刻 B 发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。

两阶段提交的问题

MySQL怎么知道bin log是完整的？
一个事务的bin log是有完整格式的。
为什么MySQL不设计成先redo log写完、再写bin log？
主要是为了解决事务的持久性问题：
对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。
只留bin log支持崩溃恢复和归档，不要redo log了可以吗？
这个主要是历史问题，MySQL的原生引擎MyISAM设计之初就没有支持崩溃恢复，后来接入InnoDB、利用InnoDB的redo log才有了崩溃恢复能力。
为什么bin log不能实现崩溃恢复呢？因为bin log记录的是写操作，它不能恢复数据页。
InnoDB 引擎使用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖于日志来恢复数据页。
如果两个事务相邻执行，第二个事务还没提交前系统崩溃了，此时，事务1的数据可能还没有刷盘，事务2由于bin log可以恢复，但是事务1因为已经提交了，就不能再应用bin log来恢复了。
能不能反过来只用redo log 不要 bin log？
如果只从崩溃恢复的角度来讲是可以的。你可以把 bin log 关掉，这样就没有两阶段提交了，但系统依然是 crash-safe 的。
但是redo log因为是循环写的，历史日志没法保留、也就起不到归档的作用。
正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
这个问题涉及到redo log和buffer pool的本质区别，我们知道写入数据实际上是写入buffer pool然后异步刷盘，那么redo log里面到底存了什么呢？
实际上redo log并没有记录数据页的完整数据，最终数据的落盘实际上和redo log毫无关系。在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让redo log更新内存内容，更新完成后，内存页变成脏页，就回到了第一种情况的状态。
redo log buffer是什么？
InnoDB并不会立刻将redo log数据写入磁盘，而是先写入到内存中的redo log buffer，只有在事务最后执行commit时才会真正地写入到redo log文件内。

其他索引类型

hash 索引

hash 索引基于哈希表实现，只有精确匹配索引的所有列的查询才会生效。
Hash 索引将所有哈希值存储在索引中，同时保持指向每个数据行的指针。
Hash 索引结构非常紧凑，查找速度非常快。
Memory 引擎支持非唯一的哈希索引。
InnoDB 有一个功能叫 “ 自适应哈希索引 ”，当它注意到某些列索引值被使用的非常频繁时，会在内存中基于 Btree 索引之上再建一个 hash 索引，以提高访问速度

空间数据索引

MyISAM 表支持空间索引，可以作为地理数据存储。但 MySQL 对 GIS 支持不够全面。

全文索引

全文索引是一种特殊类型的索引，他查找的是文中的关键词，而不是直接比较索引中的值。
全文索引有很多的细节需要调整，比如分词、停用词、词干、复数、布尔查询等。
MySQL 对空间数据索引和全文索引支持都不是很好，如果有此功能需求，建议使用其他存储引擎，如 monogDB 或基于 lucene 的 solr、es。

QA

MySQL 为什么使用 B+树作为索引的底层实现数据结构？而不是希表、数组、红黑树

vs 哈希表
哈希表支持 O(1)的读写，但是无序，无法满足排序需求。
vs （有序）数组
数组虽然通过二分查找可以保证 O(log2n)的查找效率，且有序，但是写操作需要对整个数组“挪位置”，非常耗时。
vs 红黑树
红黑树的每个节点都非常小，自顶向下搜索的过程中又会遍历多个节点，遍历的节点数量和树的高度有关，如果这些节点被分散到了多个页面上，相当于要将这些页面全部加载到内存中，并在内存不足时淘汰掉一些页面，导致 IO 压力增大。

一页可以保存多少个 B+树节点？一棵 B+树能存储多少条记录？

一页对应一个 B+树节点。
一页的大小是 16KB，假设一行数据占用空间为 1KB，那么数据页（叶子节点）一页最多能存储 16 行数据。
非叶子节点存储的是数据的主键和对其他页的指针，假设主键的类型为 bigint，则占用的空间为 8 字节，指针大小在 InnoDB 源码中设置为 6 字节，因此总共为 14 字节，非叶子节点总共能存储的记录数=16384(16KB) / 14 = 1170。
InnoDB 中 B+树的高度一般为 1-3 层，因此能存储的记录数=1170 * 1170 * 16 = 21902400(2 千万)。

为什么主键要使用自增主键

目标是占用尽可能少的空间、且保证较高的效率，自增可以保证递增的插入，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
如果是业务字段，就不容易保证有序插入了，会导致数据的写成本相对较高，而且业务字段往往较长，会更加占用存储空间。

重建索引

索引可能会因为删除、页分裂等原因而导致数据页产生空洞，为了消除这些空洞、压缩磁盘空间，就需要重建索引，将原数据按顺序插入。
重建索引时没有必要 drop 后再 add，比如：
alter table T drop index k;
alter table T add index(k);
因为这两个语句都会将整个索引重建。重建索引可以用以下语句代替：
alter table T engine=InnoDB

下面的建表语句中，既然已经有了(a, b)作为主键索引，那么在 c 上加了索引后，就已经包含了 a、b、c 三个字段，为什么还需要创建(c, a)、(c, b)这两个索引

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

为了解答这个问题，首先需要理解索引的字段顺序是有意义的，(a, b)表示先按 a 排序，a 值相同的情况下再按 b 进行排序，索引(c, a)是先按 c 排序，再按 a 排序，这实际上和索引(c)是一样的，所以(c, a)是多余的。

收缩表空间

对一个大小为 1TB 的表文件执行alter table t engine=InnoDB重建，为什么会出现占用空间没变小反而变大的情况。
这是因为，在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的，反而引入了一些空洞。

如何使用慢查询日志（slow log）

自增主键是连续的吗

由于 AUTO_INCREMENT 存在内存中，重启时导致不连续
- MyISAM 引擎的自增值保存在数据文件中。
- InnoDB 引擎的自增值，其实是保存在了内存里，重启后会从表中查当前最大的值+1 作为下一次主键取值；到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。
  也就是说，MySQL8.0 之前自增主键是有可能丢失的，比如：当前最大主键是 10，AUTO_INCREMENT=11，这时我们删除了 id=10 的行，AUTO_INCREMENT 不变，但是如果马上重启实例，则重启后的这个表的 AUTO_INCREMENT 会变成 10。
主键冲突
出现空洞还有一种情况：插入行未指定主键的时候，MySQL 会用当前的 AUTO_INCREMENT 作为主键值，并且设置 AUTO_INCREMENT+1，但是仍有可能出现跟其他唯一键冲突的情况，导致插入失败，这时 AUTO_INCREMENT 是不会回滚的，也就导致了自增主键的不连续。
回滚
1
2
3
4
5
6
insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)
为什么事务回滚不允许把自增值也回滚了？主要是存在多个事务并发执行的情况：
- 假设事务 A 申请到了 id=2，事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。
- 事务 B 正确提交了，但事务 A 出现了唯一键冲突。
- 如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。
- 接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。
事务乱序执行
多个事务并发执行时，这些事务内部各个语句执行顺序不确定，比如：
- session B 先插入了两个记录，(1,1,1)、(2,2,2)；
- 然后，session A 来申请自增 id 得到 id=3，插入了（3,5,5)；
- 之后，session B 继续执行，插入两条记录 (4,3,3)、 (5,4,4)。
  如果现在binlog_format=statement，则 binlog 里对表的更新日志，要么先记 session A 的，要么先记 session B 的，那么在备库中各 session 执行的结果 id 都是连续的，这时这个库就发生了数据不一致。
  这个问题的一种解决办法是让原库的批量插入数据语句固定生成连续的 id 值；或者在 binlog 里把插入数据的操作都如实记录进来，即innodb_autoinc_lock_mode 设置为 2，同时 binlog_format 设置为 row。

对 a, b 字段分别加索引，现有一个查询条件同时对这两个字段进行过滤，怎么知道最终使用的是哪个索引？

采样统计查询需要扫描的行数，在不同查询方式中选择需要扫描行数最少的那个。
采样统计时，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

下面的语句为什么不走索引

建表语句：

CREATE TABLE `x` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(4) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `str` varchar(20) DEFAULT NULL,
  KEY `idx_test` (`a`,`b`,`c`)
) ENGINE=InnoDB;

查询语句：

1
2
3

explain select * from x
where a = 5
order by b, c;

其中，如下图所示，key_len 字段为 5，这是 a 字段的数据长度，因此 b、c 字段实际上并没有走索引：
MySQL-某些条件下不走索引1

没有走索引的原因是：SQL 查询的执行流程是先根据where子句条件查二级索引，将数据回表带出select所需的所有字段，加载到sort_buffer内，然后排序。所以无法利用索引结构的有序性来实现排序。

为什么要加主键

主键唯一确定一行，没有主键我们无法 CRUD 特定的某行。

InnoDB的辅助索引叶子节点为什么不直接保存记录地址而要存主键键值？

试想将记录地址直接保存到叶子节点，那每次直接可以从记录地址取到数据，就不用再回表了，这样不是更快吗？
其实主要原因是InnoDB的数据是按主键组织的，数据被保存到主键的叶子节点上：

如果辅助索引保存的是记录地址，那么每次页分裂时记录的地址发生变化，还得同步到辅助索引上，这就非常麻烦了；
如果辅助索引保存的是主键，除了不受主键索引树页分裂影响外，其实还要提一句的是，回表并没有那么慢，因为InnoDB中B+树的高度是非常低的，查一条记录并不需要读多少页。

为什么不要创建过多的索引

因为维护索引开销大，当我们修改一个字段时，需要同步修改到使用了这个字段的所有索引上，也就是说会对 insert/update/delete 语句会有负面影响。
原则上应该只有查询的字段才建立索引、这些字段重复度不高且基本不变，或者通过读写分离等分库分表技术来提高数据库整体效率。

B 树和 B+树之间的区别

MySQL-B树示例
MySQL-B加树示例

B 树内部节点也会存储数据，而 B+树只有叶子节点会存储数据
对于数据库的场景来说，因为 B+树只有叶子节点存储数据，内部节点只存储每个叶子节点中最大的键值，相当于一个页面的“指针”，因为这个“指针”占用的空间很小，因此内部节点就可以存储很多叶子节点的指针，即使这个 B+树的高度只有 3 也可以存储千万级别的页面。
B+树叶子节点之间前后串联形成链表
如果需要范围查找，B 树需要中序遍历，而 B+树只需遍历叶子节点组成的链表。

InnoDB 与 MyISAM 之间的区别

索引角度
InnoDB 支持外键，而 MyISAM 不支持。
InnoDB 是聚集索引，而 MyISAM 是非聚集索引。
表行数
InnoDB 不保存具体表行数，select count(*) from table需要全表扫描，而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时可以直接读出该变量。
锁角度
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。
事务角度
InnoDB 支持事务，而 MyISAM 不支持。

MySQL 为什么会发生抖动

InnoDB 写入磁盘前需要先记redo log，redo log 是一个有限大小的环形数组，剩余空间不足以继续写入时会执行刷脏页操作，当出现以下两种情况时，都会明显地影响性能：

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
日志被写满，更新全部堵住，写性能跌为 0，这种情况对于敏感业务来说是不能接受的。

redo log 能帮助 MySQL 实现 ACID 中的持久化能力，非常重要，因此刷脏页本身是无法避免的，解决办法是优化刷脏页的控制策略：

刷脏页速度
innodb_io_capacity：告诉 InnoDB 磁盘能力，最好设置成磁盘的 IOPS。
控制刷邻居行为
innodb_flush_neighbors：如果要刷的页面旁边也是脏页，也会一块刷了，如果这个页面旁边还是脏页则这个过程会不断扩散，这个行为可以通过innodb_flush_neighbors这个参数控制，即最多刷新多少个邻居。

为什么写 bin log 和 redo log 时需要两阶段提交

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。
在写这两个 log 的时候中间如果发生了 crash，可能会出现无法恢复的情况：

先写 redo log 后写 bin log。
redo log 写完后即使系统崩溃仍然能把数据恢复过来。但是由于 bin log 没写完就 crash 了，所以 bin log 中就没有这条语句了，之后使用这个 bin log 来恢复临时库时就会丢了这次更新，导致主从不一致。
先写 bin log 后写 redo log
bin log 写完后 crash，由于 redo log 还没写，崩溃恢复后这个事务无效（update 语句相当于没执行）。但是 bin log 里已经记录了修改操作，因此之后用 bin log 恢复的时候就多了一个事务，与原库中的值不同。

哪些命令可以立刻释放磁盘空间？

一般情况下删除数据只是惰性删除，不会立刻释放磁盘空间：

delete * from t where ...
delete * from t

但是有些命令会立刻释放磁盘空间：

truncate table t删除表中全部数据
alter table t engine = innodb重做数据

规则引擎

发表于 2019-11-20 更新于 2025-07-07 分类于设计

规则引擎介绍

什么是规则引擎

规则引擎是专家系统的变种，它通过一组规则的集合、通过作用于事实，从而推理得到结果。当一个事实满足某条规则的条件时，就可以认为这个事实与这条规则匹配。比如，如果我的成绩达到 90 分以上就出去旅游，其中的事实是我，规则是if(x的成绩达到90分以上){x出去玩}

规则
可以理解为类似 if-else、switch 这样的代码；
事实
在 Java 中可以认为就是类的实例；

为什么使用规则引擎

规则引擎实现了数据同逻辑的完全解耦。
有助于规则的集中管理，但是也要注意可能发生的冲突。
每个开发的习惯、水平都不一样，有些人可能会生硬地套用一些设计模式，导致业务逻辑被强行设计得很复杂，而且随着版本、人员迭代越发严重；
而规则引擎相当于一套实现业务逻辑的规范，它会逼迫需求和研发人员梳理业务，并建立统一的 BOM（业务对象模型）。
可以使用决策表等形式来展示规则，方便业务人员浏览，减少与技术人员沟通成本。
方便业务人员修改业务逻辑，甚至可以做到动态修改、实时生效，减少规则变动带来的额外开发工作。

在复杂的大型业务场景下，规则引擎常和流程引擎搭配使用来强化对业务逻辑的管理。

阅读全文 »

Lettuce和Jedis

发表于 2019-10-25 更新于 2025-07-07 分类于缓存

Lettuce

Sentinel

com.lambdaworks.redis.RedisClient#connectSentinel(com.lambdaworks.redis.codec.RedisCodec<K,V>, com.lambdaworks.redis.RedisURI, com.lambdaworks.redis.RedisClient.Timeout)

Jedis

配置 Jedis

1.添加 Jedis 的 Maven 依赖
2.设置服务端
设置服务器防火墙关闭或放行 6379 端口（redis 占用）
然后关闭服务器的保护模式，因为保护模式下不能写入数据

1 2	CONFIG SET protected-mode no config rewrite

（或直接修改 redis.conf 中 protected-mode
或在运行服务器的配置中加上–protected-mode no
或 Setup a bind address or an authentication password）
然后注释掉 redis.conf 中的 bind 127.0.0.1，或者在后面添上本机的 ip

普通单例连接

Jedis jedis = new Jedis(host, 6379);
jedis.set("name", "bar");
String name = jedis.get("name");
System.out.println(name);
jedis.close();

使用连接池连接

// 配置连接池
JedisPoolConfig config = new JedisPoolConfig();
config.setMaxTotal(30); // 最大连接数
config.setMaxIdle(2); // 最大连接空闲数
JedisPool pool =
        new JedisPool(config, host, 6379);
// 从连接池中获取连接
Jedis jedis = pool.getResource();
jedis.set("name", "李四");
String name = jedis.get("name");
System.out.println(name);
// 关闭，返回到连接池
jedis.close();

并发安全问题

Jedis 不是并发安全的，如果有并发安全的需求，可以考虑采用 apache 的 commons-pool 对象池进行包装，或者，换用一个更先进的 Redis 客户端 Lettuce

spring-data-redis

1.引入依赖 spring-data-redis
2.创建 redis 缓冲配置类

// 配置头部用于被Spring识别
@Configuration
@EnableCaching
public class RedisCacheConfig {
    private volatile JedisConnectionFactory mJedisConnectionFactory;
    private volatile RedisTemplate<String, String> mRedisTemplate;
    private volatile RedisCacheManager mRedisCacheManager;
    public RedisCacheConfig() {
        super();
    }
    public RedisCacheConfig(JedisConnectionFactory mJedisConnectionFactory, RedisTemplate<String,String> mRedisTemplate,
                            RedisCacheManager mRedisCacheManager) {
        super();
        this.mJedisConnectionFactory = mJedisConnectionFactory;
        this.mRedisTemplate = mRedisTemplate;
        this.mRedisCacheManager = mRedisCacheManager;
    }
    public JedisConnectionFactory redisConnectionFactory() {
        return mJedisConnectionFactory;
    }
    public RedisTemplate<String, String> redisTemplate(RedisConnectionFactory cf) {
        return mRedisTemplate;
    }
    public CacheManager cacheManager(RedisTemplate<?, ?> redisTemplate) {
        return mRedisCacheManager;
    }
    // 用于生成主键
    @Bean
    public KeyGenerator customKeyGenerator() {
        return new KeyGenerator() {
            public Object generate(Object o, Method method, Object... objects) {
                StringBuilder sb = new StringBuilder();
                sb.append(o.getClass().getName());
                sb.append(method.getName());
                for (Object obj : objects) {
                    sb.append(obj.toString());
                }
                return sb.toString();
            }
        };
    }
}

3.创建 redis 属性文件 redis.properties

redis.host=192.168.157.128
redis.port=6379
redis.pass=1234
redis.maxIdle=300
redis.maxActive=600
redis.maxWait=1000
#借出连接时不要测试,否则很影响性能
redis.testOnBorrow=true
redis.dbIndex=0
redis.expiration=30

当然必须在 Spring 配置文件中引入属性文件扫描标签
4.在 Spring 配置文件中引入 redis
问题
ERR Client sent AUTH, but no password is set
因为 redis 没有配置密码而建立连接时却发送了密码，所以出错，解决办法是在下面 jedisConnectionFactory 标签中去掉 password 属性

<!-- 配置JedisPoolConfig实例 -->
<bean id="poolConfig" class="redis.clients.jedis.JedisPoolConfig">
    <property name="maxIdle" value="${redis.maxIdle}" />
    <property name="maxTotal" value="${redis.maxActive}" />
    <property name="maxWaitMillis" value="${redis.maxWait}" />
    <property name="testOnBorrow" value="${redis.testOnBorrow}" />
</bean>
<!-- 配置JedisConnectionFactory -->
<bean id="jedisConnectionFactory" class="org.springframework.data.redis.connection.jedis.JedisConnectionFactory">
    <property name="hostName" value="${redis.host}"/>
    <property name="port" value="${redis.port}"/>
    <property name="password" value="${redis.pass}"/>
    <property name="database" value="${redis.dbIndex}"/>
    <property name="poolConfig" ref="poolConfig"/>
</bean>
<!-- 配置RedisTemplate -->
<bean id="redisTemplate" class="org.springframework.data.redis.core.RedisTemplate">
    <property name="connectionFactory" ref="jedisConnectionFactory"/>
</bean>
<!-- 配置RedisCacheManager -->
<bean id="redisCacheManager" class="org.springframework.data.redis.cache.RedisCacheManager">
    <constructor-arg name="redisOperations" ref="redisTemplate" />
    <property name="defaultExpiration" value="${redis.expiration}"/>
</bean>
<!-- 配置RedisCacheConfig -->
<bean id="redisCacheConfig" class="com.ebuy.util.RedisCacheConfig">
    <constructor-arg ref="jedisConnectionFactory" />
    <constructor-arg ref="redisTemplate" />
    <constructor-arg ref="redisCacheManager" />
</bean>

5.测试

/* 缓存
 @Cacheable 缓存返回值，下次调用时会优先使用缓存数据
    value 缓存将被存到的地方
 @CacgeEvict 清除缓存
 */
@CacheEvict(value = {"save"}, allEntries = true)
public void save(TbContent content) {
    contentDao.insert(content);
}
@Cacheable("findall")
public TbContent findall() {
    TbContent content = new TbContent();
    content.setCategoryId((long) 1);
    return content;
}

测试时可以在 findall 中设置打印语句，连续调用 findall 两次，若第二次没有输出东西则说明使用了缓存。
6.配置连接池

<!-- 连接池配置 -->
<bean id="jedisPoolConfig" class="redis.clients.jedis.JedisPoolConfig">
    <!-- 最大连接数 -->
    <property name="maxTotal" value="30" />
    <!-- 最大空闲连接数 -->
    <property name="maxIdle" value="10" />
    <!-- 每次释放连接的最大数目 -->
    <property name="numTestsPerEvictionRun" value="1024" />
    <!-- 释放连接的扫描间隔（毫秒） -->
    <property name="timeBetweenEvictionRunsMillis" value="30000" />
    <!-- 连接最小空闲时间 -->
    <property name="minEvictableIdleTimeMillis" value="1800000" />
    <!-- 连接空闲多久后释放, 当空闲时间>该值 且 空闲连接>最大空闲连接数 时直接释放 -->
    <property name="softMinEvictableIdleTimeMillis" value="10000" />
    <!-- 获取连接时的最大等待毫秒数,小于零:阻塞不确定的时间,默认-1 -->
    <property name="maxWaitMillis" value="1500" />
    <!-- 在获取连接的时候检查有效性, 默认false -->
    <property name="testOnBorrow" value="true" />
    <!-- 在空闲时检查有效性, 默认false -->
    <property name="testWhileIdle" value="true" />
    <!-- 连接耗尽时是否阻塞, false报异常,ture阻塞直到超时, 默认true -->
    <property name="blockWhenExhausted" value="false" />
</bean>
<!-- redis单机 通过连接池 -->
<bean id="jedisPool" class="redis.clients.jedis.JedisPool" destroy-method="close">
    <constructor-arg name="poolConfig" ref="jedisPoolConfig"/>
    <constructor-arg name="host" value="172.16.205.141"/>
    <constructor-arg name="port" value="6379"/>
</bean>

7.测试

JedisPool pool =
        (JedisPool) applicationContext.getBean("jedisPool");
Jedis jedis = pool.getResource();
jedis.set("name", "李四");
String name = jedis.get("name");
System.out.println(name);
jedis.close();

Redisson

发表于 2019-10-25 更新于 2025-07-07 分类于缓存

与 Jedis 的区别

Jedis 提供了对 Redis-API 的简单封装，使用 Jedis 时，需要关注 Redis 服务器的部署细节，而 Redisson 屏蔽了这些细节，使得使用者可以将精力更集中地放到自己希望实现的功能上。
Jedis 只提供简单的 API 调用，并不关注用户如何使用这些 API，比如 string 可以实现原子变量，不过需要用户手动封装，而 Redisson 中已经有了现成的 AtomicLong。
Jedis 不支持 Cluster 环境下的事务、Lua

Sentinel 模式获取连接

public class RedissonSentinelConnectionTest {

    RedissonClient redisson;
    RedisSentinelConnection connection;
    RedisRunner.RedisProcess master;
    RedisRunner.RedisProcess slave1;
    RedisRunner.RedisProcess slave2;
    RedisRunner.RedisProcess sentinel1;
    RedisRunner.RedisProcess sentinel2;
    RedisRunner.RedisProcess sentinel3;

    @Before
    public void before() throws FailedToStartRedisException, IOException, InterruptedException {
        master = new RedisRunner()
                .nosave()
                .randomDir()
                .run();
        slave1 = new RedisRunner()
                .port(6380)
                .nosave()
                .randomDir()
                .slaveof("127.0.0.1", 6379)
                .run();
        slave2 = new RedisRunner()
                .port(6381)
                .nosave()
                .randomDir()
                .slaveof("127.0.0.1", 6379)
                .run();
        sentinel1 = new RedisRunner()
                .nosave()
                .randomDir()
                .port(26379)
                .sentinel()
                .sentinelMonitor("myMaster", "127.0.0.1", 6379, 2)
                .run();
        sentinel2 = new RedisRunner()
                .nosave()
                .randomDir()
                .port(26380)
                .sentinel()
                .sentinelMonitor("myMaster", "127.0.0.1", 6379, 2)
                .run();
        sentinel3 = new RedisRunner()
                .nosave()
                .randomDir()
                .port(26381)
                .sentinel()
                .sentinelMonitor("myMaster", "127.0.0.1", 6379, 2)
                .run();

        Thread.sleep(5000);

        Config config = new Config();
        config.useSentinelServers()
                .setLoadBalancer(new RandomLoadBalancer())
                .addSentinelAddress(sentinel3.getRedisServerAddressAndPort()).setMasterName("myMaster");
        redisson = Redisson.create(config);

        RedissonConnectionFactory factory = new RedissonConnectionFactory(redisson);
        connection = factory.getSentinelConnection();
    }

    @After
    public void after() {
        sentinel1.stop();
        sentinel2.stop();
        sentinel3.stop();
        master.stop();
        slave1.stop();
        slave2.stop();

        redisson.shutdown();
    }

    @Test
    public void testMasters() {
        Collection<RedisServer> masters = connection.masters();
        assertThat(masters).hasSize(1);
    }

    @Test
    public void testSlaves() {
        Collection<RedisServer> masters = connection.masters();
        Collection<RedisServer> slaves = connection.slaves(masters.iterator().next());
        assertThat(slaves).hasSize(2);
    }

    @Test
    public void testRemove() {
        Collection<RedisServer> masters = connection.masters();
        connection.remove(masters.iterator().next());
    }

    @Test
    public void testMonitor() {
        Collection<RedisServer> masters = connection.masters();
        RedisServer master = masters.iterator().next();
        master.setName(master.getName() + ":");
        connection.monitor(master);
    }

    @Test
    public void testFailover() throws InterruptedException {
        Collection<RedisServer> masters = connection.masters();
        connection.failover(masters.iterator().next());

        Thread.sleep(10000);

        RedisServer newMaster = connection.masters().iterator().next();
        assertThat(masters.iterator().next().getPort()).isNotEqualTo(newMaster.getPort());
    }
}

确定 Sentinel 集群内的所有节点地址
创建连接管理器（ConnectionManager）时读取所有 Master、Slave 和 Sentinel 节点的地址（org.redisson.connection.SentinelConnectionManager#SentinelConnectionManager）
Sentinel 通过监听 master 可以得到所有节点的地址。
可以从SentinelConnectionManager中看到，客户端会定时（默认1秒）地刷新服务端状态，即使集群暂时不可用，也可以通过这种刷新来恢复连接。
尝试连接一个 Sentinel
只要有一个 Sentinel 能通过 PING-PONG 校验，则返回对该 Sentinel 的连接。
执行操作
获取连接（org.redisson.command.RedisExecutor#getConnection）。
如果是只读的操作，会从 slave 中通过负载均衡选一个操作（org.redisson.connection.MasterSlaveConnectionManager#connectionReadOp）；
如果是非只读操作，从 master 里选一个操作（org.redisson.connection.ConnectionManager#connectionWriteOp）。

Cluster 模式获取连接

添加节点初始化 Redisson
对于客户端来说，Cluster 模式可以看做几个 Master、Slave 的组合（org.redisson.ClusterRunner#addNode）。

连接时从节点中选一个
以Buckets.get操作为例，跟踪代码直到CommandAsyncService#readAsync(String key, Codec codec, RedisCommand<T> command, Object ... params)

@Override
public int calcSlot(String key) {
    ...
    
    // slot的计算方法，注意这里的MAX_SLOT是固定的
    int result = CRC16.crc16(key.getBytes()) % MAX_SLOT;
    log.debug("slot {} for {}", result, key);
    return result;
}

private NodeSource getNodeSource(String key) {
    // 计算该key属于哪个slot
    int slot = connectionManager.calcSlot(key);
    // 计算该slot属于哪个节点
    MasterSlaveEntry entry = connectionManager.getEntry(slot);
    return new NodeSource(entry);
}

@Override
public <T, R> RFuture<R> readAsync(String key, Codec codec, RedisCommand<T> command, Object... params) {
    RPromise<R> mainPromise = connectionManager.newPromise();
    // 获取key所在的节点
    NodeSource source = getNodeSource(key);
    async(true, source, codec, command, params, mainPromise, 0);
    return mainPromise;
}

先计算 key 属于哪个 slot（org.redisson.cluster.ClusterConnectionManager#calcSlot）；
再由 slot 计算应该请求哪对主从（org.redisson.connection.MasterSlaveConnectionManager#getEntry）。

重连
Redisson启动后会创建一个定时任务每5秒更新一次节点状态，所以就算节点挂掉了，之后重启时客户端是可以感知到服务器的启动的。
org.redisson.cluster.ClusterConnectionManager#scheduleClusterChangeCheck

分布式锁

文档：8. 分布式锁和同步器
下面记录一下 Redisson 中对应功能所在的代码位置和基本思路。

锁的特性

互斥性
任意时刻，只会有一个客户端持有锁
不会发生死锁
即使客户端在持有锁期间崩溃而没有释放锁，也能保证其他客户端能获取到锁。
容错性
锁服务的某个节点不可用时，客户端还能继续加解锁。
可重入性
一个客户端可以重复加锁，期间其他客户端无法获取这个锁。

可重入锁（Reentrant Lock）

测试代码见：org.redisson.RedissonLockTest#testGetHoldCount
源码主要为：org.redisson.RedissonLock

可重入性是通过加锁时传的 threadId 实现的，下面是 Redisson 中用于加锁的 lua 脚本（org.redisson.RedissonLock#tryLockInnerAsync）：

-- KEYS[1]: RedissonObject中的name字段，这里表示锁的名字
-- ARGV[1]: leaseTime，过期时间，这里为30000
-- ARGV[2]: 锁的value，这里为threadId

-- 未加过锁的情况
"if (redis.call('exists', KEYS[1]) == 0) then " +
    "redis.call('hset', KEYS[1], ARGV[2], 1); " +
    "redis.call('pexpire', KEYS[1], ARGV[1]); " +
    "return nil; " +
"end; " +
-- 当前线程已加过锁的情况
"if (redis.call('hexists', KEYS[1], ARGV[2]) == 1) then " +
    "redis.call('hincrby', KEYS[1], ARGV[2], 1); " +
    "redis.call('pexpire', KEYS[1], ARGV[1]); " +
    "return nil; " +
"end; " +
-- 其他线程加过锁的情况
"return redis.call('pttl', KEYS[1]);"

所有锁都保存在一个 key 为 lock 的 hash 对象下，第一次加锁时保存的结果为<lock, {threadId}, 1>，过期时间为 30s，同一线程第二次加锁时，更新为<lock, {threadId}, 2>，且过期时间被刷新。
当加锁成功时（包括同一线程调用重入多次）返回 null，而加锁失败时，返回锁的剩余过期时间，根据返回值是否为空可以判断加锁是否成功，当还未获取到锁时，客户端会轮询检查（org.redisson.RedissonLock#lock(long leaseTime, TimeUnit unit, boolean interruptibly)中的 while 循环），也就是说这种加锁方式并不是公平的。
加锁监控保证了当业务执行时间超过加锁时间时，不会因为锁过期而让其他线程进入临界区，在 Redisson 中是通过一个 TimerTask 每隔 10s（即加锁时间 / 3）刷新一次锁的过期时间来实现的（org.redisson.RedissonLock#renewExpiration）。

另外，由于加锁时保存了 threadId，unlock时同样会传 threadId、只能释放当前线程加上的锁，下面是用于释放锁的 lua 脚本（org.redisson.RedissonLock#unlockInnerAsync）：

-- KEYS[1]: {lockName}
-- KEYS[2]: 
-- ARGS[1]: UNLOCK_MESSAGE
-- ARGS[2]: 加锁时间，默认30s
-- ARGS[3]: {threadId}

-- 检查当前线程是否有加该锁
"if (redis.call('hexists', KEYS[1], ARGV[3]) == 0) then " +
    "return nil;" +
"end; " +
-- 计数器-1
"local counter = redis.call('hincrby', KEYS[1], ARGV[3], -1); " +
-- 如果计数器未减完，说明重入了多次，且这里刷新了一次过期时间
"if (counter > 0) then " +
    "redis.call('pexpire', KEYS[1], ARGV[2]); " +
    "return 0; " +
-- 计数器减完，删除该锁，并通知其他正在等待的线程
"else " +
    "redis.call('del', KEYS[1]); " +
    "redis.call('publish', KEYS[2], ARGV[1]); " +
    "return 1; "+
"end; " +
"return nil;"

公平锁（Fair Lock）

测试代码见：org.redisson.RedissonFairLockTest#testIsLockedOtherThread
源码主要为：org.redisson.RedissonFairLock

公平性是通过队列实现的，（org.redisson.RedissonFairLock#tryLockInnerAsync）：

// remove stale threads
"while true do " +
    "local firstThreadId2 = redis.call('lindex', KEYS[2], 0);" +
    "if firstThreadId2 == false then " +
        "break;" +
    "end;" +

    "local timeout = tonumber(redis.call('zscore', KEYS[3], firstThreadId2));" +
    "if timeout <= tonumber(ARGV[4]) then " +
        // remove the item from the queue and timeout set
        // NOTE we do not alter any other timeout
        "redis.call('zrem', KEYS[3], firstThreadId2);" +
        "redis.call('lpop', KEYS[2]);" +
    "else " +
        "break;" +
    "end;" +
"end;" +

// check if the lock can be acquired now
"if (redis.call('exists', KEYS[1]) == 0) " +
    "and ((redis.call('exists', KEYS[2]) == 0) " +
        "or (redis.call('lindex', KEYS[2], 0) == ARGV[2])) then " +

    // remove this thread from the queue and timeout set
    "redis.call('lpop', KEYS[2]);" +
    "redis.call('zrem', KEYS[3], ARGV[2]);" +

    // decrease timeouts for all waiting in the queue
    "local keys = redis.call('zrange', KEYS[3], 0, -1);" +
    "for i = 1, #keys, 1 do " +
        "redis.call('zincrby', KEYS[3], -tonumber(ARGV[3]), keys[i]);" +
    "end;" +

    // acquire the lock and set the TTL for the lease
    "redis.call('hset', KEYS[1], ARGV[2], 1);" +
    "redis.call('pexpire', KEYS[1], ARGV[1]);" +
    "return nil;" +
"end;" +

// check if the lock is already held, and this is a re-entry
"if redis.call('hexists', KEYS[1], ARGV[2]) == 1 then " +
    "redis.call('hincrby', KEYS[1], ARGV[2],1);" +
    "redis.call('pexpire', KEYS[1], ARGV[1]);" +
    "return nil;" +
"end;" +

// the lock cannot be acquired
// check if the thread is already in the queue
"local timeout = redis.call('zscore', KEYS[3], ARGV[2]);" +
"if timeout ~= false then " +
    // the real timeout is the timeout of the prior thread
    // in the queue, but this is approximately correct, and
    // avoids having to traverse the queue
    "return timeout - tonumber(ARGV[3]) - tonumber(ARGV[4]);" +
"end;" +

// add the thread to the queue at the end, and set its timeout in the timeout set to the timeout of
// the prior thread in the queue (or the timeout of the lock if the queue is empty) plus the
// threadWaitTime
"local lastThreadId = redis.call('lindex', KEYS[2], -1);" +
"local ttl;" +
"if lastThreadId ~= false and lastThreadId ~= ARGV[2] then " +
    "ttl = tonumber(redis.call('zscore', KEYS[3], lastThreadId)) - tonumber(ARGV[4]);" +
"else " +
    "ttl = redis.call('pttl', KEYS[1]);" +
"end;" +
"local timeout = ttl + tonumber(ARGV[3]) + tonumber(ARGV[4]);" +
"if redis.call('zadd', KEYS[3], timeout, ARGV[2]) == 1 then " +
    "redis.call('rpush', KEYS[2], ARGV[2]);" +
"end;" +
"return ttl;"

联锁（MultiLock）

源码位置：org.redisson.RedissonMultiLock

联锁是对批量加锁的封装，其关键是如何实现死锁避免，其中的关键代码如下（org.redisson.RedissonMultiLock#tryLock(long waitTime, long leaseTime, TimeUnit unit)）：

// 将已经获取的锁释放掉
unlockInner(acquiredLocks);
if (waitTime == -1) {
    return false;
}
failedLocksLimit = failedLocksLimit();
acquiredLocks.clear();
// 重置iterator，重新加一遍锁
while (iterator.hasPrevious()) {
    iterator.previous();
}

红锁（RedLock）

测试代码：org.redisson.RedissonRedLockTest#testLockLeasetime
源码位置：org.redisson.RedissonRedLock

红锁实际上是联锁的子类，原理基本一致，它和联锁的区别主要是：

联锁不允许加锁失败（org.redisson.RedissonMultiLock#failedLocksLimit），而红锁允许少于半数次的加锁失败（org.redisson.RedissonRedLock#failedLocksLimit）。
使用时，红锁的加锁目标最好包含多个 Redis 实例，从而实现高可用。

如果一个Redis实例加多次锁，那么这个Redis挂掉了就会导致全部加锁请求都失败了。

红锁执行流程

获取当前时间戳；
开始获取锁：Client按顺序从每台Redis实例上获取锁；
注意每台服务器都有一个获取的截止时间，超过一段时间获取不到就放弃，而且这个截止时间要比总的获取锁的TTL时间要短很多，避免由于等待部分已停机的Redis实例时间过长而导致获取锁失败了。
比如总TTL为5s，那么每台Redis实例的获取时间就可以定为1s。
因为是顺序获取的，所以每台实例上锁的过期时间也是不一样的。
怎么样算获取成功：过半数，且未超时
- 过半数：比如总共有5个Redis实例的情况下，需要有至少3个实例成功获取到锁才算获取成功；
- 未超时：(总TTL) - (每台服务器获取锁花费的时间之和)需要大于0，如果获取成功，锁的真正有效时间就是这个时间差。
获取失败释放锁
不满足获取成功条件的情况下，把之前获取过锁的Redis实例都给释放掉。

锁续期 - 看门狗

看门狗原理，下图来自于这里
红锁-看门狗
加锁时启动定时任务刷新锁的过期时间：
org.redisson.RedissonLock#tryAcquireOnceAsync
-> org.redisson.RedissonLock#scheduleExpirationRenewal
释放锁时关掉该定时任务：
org.redisson.RedissonLock#unlock
-> org.redisson.RedissonLock#cancelExpirationRenewal

可重入性

可重入性原理，下图来自于这里
红锁-可重入性

红锁存在的问题

一般Redis集群都是多主多从，但是使用多主多从的情况下，锁是加到主服务器上的，而主从复制是异步完成的，如果在客户端获取到锁之后，主复制锁到从的过程中崩溃了，导致没有复制到从Redis中，那么之后即使再选举出一个从升级为主，主服务器里也是没有锁的，并且能够成功被获取到锁，导致互斥失效。
所以，使用红锁时Redis集群一般都是单节点，而不是主从的。
5主无从的情况下，如果一个客户端获取到锁之后，所有Redis重启，这时其他客户端又可以获取到锁了，显然违背了锁的互斥原则；如果Redis实例开启了AOF持久化存储，在持久化间隔时间内断电，照样会导致数据丢失。

显然AOF不能开启Always（每个命令都同步到硬盘），这样会造成性能急剧下降。

读写锁（ReadWriteLock）

TODO

信号量（Semaphore）

TODO

可过期性信号量（PermitExpirableSemaphore）

TODO

闭锁（CountDownLatch）

TODO

异常情况分析

因为主从同步导致锁被重复获取
Redis集群如果采用Cluster集群或Master-Slave主从复制的方式，就会存在key刚写完Master、但是在同步到Slave之前Master挂掉的情况，这时如果发生主从切换，就有可能会出现多个线程同时持有锁的情况。
因为GC导致锁被重复获取
如果出现GC停顿时间过长，或者其他情况导致客户端和Redis连接断开，也有可能出现多个线程同时持有一个锁的情况。

参考

Redlock（redis分布式锁）原理分析

MySQL5_2分库分表和Sharding

发表于 2019-10-19 更新于 2025-07-07 分类于 MySQL

分库分表

为什么需要分库分表

MySQL单表超过容量上限
这一上限的推荐值是2kw行，但实际情况需要综合考虑单库的CPU、磁盘、内存压力、具体业务，如果硬件无法继续垂直扩容了、且业务也无法优化了，就会选择分库分表、或者在原来分库分表基础上水平伸缩。

以什么维度分库分表？

分表键需要考虑实际的业务场景，比如TO C的业务一般可以uid作为分表键，TO B业务常用orgId。
还有一些场景需要支持多种方式查询，可以采用叫“基因法”的方式来分表[1]。

ShardingJDBC

事务

柔性事务

2.0 后提供柔性事务支持，执行事务前先发消息给一个 EventBus，失败后由 EventBus 负责重试。

TCC 模式

3.0 后借助 Seata 提供 TCC 模式的分布式事务。

源码分析

启动

数据源元数据信息和表元数据信息的收集
表分库分表策略和算法的配置信息收集

ShardingDataSourceFactory#createDataSource 创建数据源 ShardingDataSource 实例
-> ShardingDataSource#ShardingDataSource 创建 ShardingContext，其持有ShardingRule、ShardingMetaData两个属性，根据一个表以及这个表的列可以从 ShardingRule 中获取这个表的分库分表策略和算法，ShardingMetaData 则维护了数据源和表的元数据信息

ShardingJDBC 如何嵌入 MyBatis

ShardingJDBC 接入 MyBatis 的原理是 DataSource 的替换，调用链如下：
org.apache.ibatis.executor.SimpleExecutor#prepareStatement
-> SimpleExecutor#getConnection 从 transaction 获取连接
-> SpringManagedTransaction#getConnection 可以看到成员变量 dataSource 是 ShardingDataSource
-> ShardingDataSource#getConnection 得到 ShardingConnection
-> ShardingConnection#prepareStatment 得到 ShardingPreparedStatement

路由及改写引擎

ShardingPreparedStatement#executeQuery、executeUpdate、execute
-> ShardingPreparedStatement#shard
-> BaseShardingEngine#shard
-> PreparedQueryShardingEngine#route
-> PreparedStatementRoutingEngine#route ParsingSQLRouter 使用四个引擎对 sql 进行解析和重写
-> ParsingSQLRouter#parse
-> SQLParsingEngine#parse 使用SQLParsingEngine解析 sql，返回 SQLStatement 作为解析的结果
-> SQLParserFactory#newInstance 获取 SQLParser 实例，如果是在 MySQL 中执行一个 DML 语句会匹配到 AntlrParsingEngine（Antlr 是一个开源语法分析器）
-> AntlrParsingEngine.parse 分析 SQL，返回 SQLStatement
-> SQLParserEngine#parse 解析 SQL 语法，生成 AST（抽象语法树）
-> SQLSegmentsExtractorEngine#extract
-> SQLStatementFillerEngine#fill
-> SQLStatementOptimizerEngine#optimize
-> ParsingSQLRouter#route
-> OptimizeEngine#optimize 使用OptimizeEngine对 SQLStatement 进行优化，返回 ShardingConditions 对象
-> RoutingEngine#route 使用RoutingEngine根据库表分片配置以及 ShardingConditions 找到目标库表，返回 RoutingResult 对象
-> ShardingMasterSlaveRouter#route(SQLRouteResult sqlRouteResult)
-> BaseShardingEngine#rewriteAndConvert
-> SQLRewriteEngine#rewrite 使用SQLRewriteEngine根据路由结果重写 sql

执行引擎

ShardingPreparedStatement#executeQuery、executeUpdate、execute
-> …
-> ShardingPreparedStatement#initPreparedStatementExecutor
-> PreparedStatementExecutor#init 把 SQLRouteResult 中的 RouteUnit 对象转换为 ShardingExecuteGroup对象集合并从数据源获取连接和 PreparedStatement
-> PreparedStatementExecutor#obtainExecuteGroups 根据路由的结果 SQLRouteResult 中的 RouteUnit 集合创建 StatementExecuteUnit 集合对象
-> AbstractConnectionAdapter#getConnections 获取数据源连接
-> getDataSourceMap().get(dataSourceName) 根据逻辑数据源名称获取真实数据源 DataSource
-> AbstractConnectionAdapter#createConnections 从数据源获取连接 Connection
-> getExecuteGroups().addAll() 将 StatementExecuteUnit 集合保存在 AbstractStatementExecutor 的属性 executeGroups 中
-> AbstractStatementExecutor#cacheStatements 从连接获取 Statement 并缓存
-> PreparedStatementExecutor#executeQuery、executeUpdate、execute
-> AbstractStatementExecutor#executeCallback
-> SQLExecuteTemplate#executeGroup
-> ShardingExecuteEngine#groupExecute 使用ShardingExecuteEngine执行，执行 ShardingExecuteGroup
-> ShardingExecuteEngine#parallelExecute 异步执行，如果 inputGroups 集合不止一个，则第一个同步执行、其他的异步执行

归并引擎

ShardingPreparedStatement#executeQuery、executeUpdate、execute
-> …
-> ShardingPreparedStatement#getResultSet(MergeEngine mergeEngine)
-> AbstractStatementExecutor#getResultSets
-> ShardingPreparedStatement#getCurrentResultSet 合并结果，返回 ShardingResultSet
-> MergeEngine#merge 使用MergeEngine对结果进行合并，executeQuery()返回的是一个 List集合，此时需要对 QueryResult 集合进行合并，MergeEngine 接口有两个实现类 DQLMergeEngine 和 DALMergeEngine，这两个实现类分别负责数据查询 sql 的合并和数据库管理 sql 的合并

参考

分库分表

帖子中心，1亿数据，架构如何设计？
提到给帖子中心设计数据库结构时将tid（帖子ID）还是uid（用户ID）作为分表key，因为实际业务中既包含根据tid查询的场景又包含根据uid查询的场景，因此最终方案是采用所谓的“基因法”。