MySQL的索引、事务、锁和日志那些事儿

一、索引的数据结构

哈希表

Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。

优点：Hash表在等值查询时效率很高，时间复杂度为O(1)。

缺点：不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

二叉搜索树

每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

这个特点就是为了保证每次查找都可以这折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。

 

 

缺点：时间复杂度不稳定，最坏情况下是如上图所示的线性表，时间复杂度是O(n)。

平衡二叉树

平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。查询id=6，只需要两次IO。

 

 

 

就这个特点来看，可能各位会觉得这就很好，可以达到二叉树的理想的情况了。然而依然存在一些问题：

时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘IO操作。树的高度就等于每次查询数据时磁盘IO操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差。（1百万的数据量，log2n约等于20次磁盘IO，时间20*10=0.2s）

平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

B树

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：

1. B树的节点中存储着多个元素，每个内节点有多个分叉。

1. 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。

1. 父节点当中的元素不会出现在子节点中。

1. 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

 

 

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

• 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走15的左路节点P1，到磁盘寻址磁盘块2。

• 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，走7的右路节点P2，到磁盘中寻址定位到磁盘块6。

• 第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，8<10，10=10，找到10，取出data。如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

 

B树虽然看着很理想，但它依然存在可以优化的地方：

1. B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高；

1. 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题。

• B树：非叶子节点和叶子节点都会存储数据。

• B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+树的最底层叶子节点包含了所有的索引项。从下图可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟这增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。B+树一定是检索到叶子节点才能取到数据，而B树在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

 

 

 

等值查询：

假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

• 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走15的左节点P1，到磁盘块2。

• 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，走7的右节点P2，到磁盘中寻址定位到磁盘块6。

• 第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

二、MySQL的索引类型

主键索引（聚簇索引）

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

1. 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。

1. 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。

1. 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

InnoDB的数据和索引存储在一个.ibd的文件中。InnoDB的数据组织方式是聚簇索引。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

 

根据主键等值查询数据：select * from user_innodb where id = 28;

 

 

 

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走75的左路P1。（1次磁盘IO）

1. 将左子树节点加载到内存中，比较16<28<47，走47节点的左路P2。（1次磁盘IO）

1. 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将该记录返回给客户端。（1次磁盘IO）

磁盘IO数量：3次。

根据主键范围查询数据：与等值查询前三部完全一致，会多一步步骤4。

1. 需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，然后将47对应的整行数据与28对应的整行数据一起返回。（1次磁盘IO）

磁盘IO数量：4次。

 

二级索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值。索引结果如下图。

 

 

 

 

底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。

画图分析等值查询的情况：select * from t_user_innodb where age=19;

 

 

 

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

覆盖索引

如果只需要通过辅助索引查询主键id，这种情况不需要回表，这种情况下的索引叫做覆盖索引。

联合索引

 

 

索引项是按照索引定义里面出现的字段顺序排序的。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

 

 

 

 

上面两张图是select * from tuser where name like '张%' and age=10 and ismale=1;这个SQL语句有无索引下推的执行过程。

图 4 跟图 3 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

三、MySQL的事务

事务隔离级别

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读已提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

• 读已提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

案例说明：

create table T(c int) engine=InnoDB;
insert into T(c) values(1);

 

 

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。

• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。

• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

读已提交和可重复读是依赖MVCC多版本并发控制去实现的，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

 

 

MySQL在可重复读隔离级别下可以通过MVCC和加锁来解决幻读问题

当前读：加锁

快照读：MVCC

MVCC

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

这个视图数组把所有的 row trx_id 分成了几种不同的情况。

 

 

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

• 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

• 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

• 如果落在黄色部分，那就包括两种情况a. 若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；b. 若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

• 版本未提交，不可见；

• 版本已提交，但是是在视图创建后提交的，不可见；

• 版本已提交，而且是在视图创建前提交的，可见。

 

 

RR隔离级别：

事务A查询得到的k=1，事务B查询得到的k=3

RC隔离级别：

事务A查询得到的k=2，事务B查询得到的k=3

四、MySQL中的锁

行级锁

InnoDB存储引擎中有如下两种类型的行级锁

• 共享锁（Shared Lock，简称S锁），在事务需要读取一条记录时，需要先获取改记录的S锁

• 排他锁（Exclusive Lock，简称X锁），在事务要改动一条记录时，需要先获取该记录的X锁

S锁和X锁之间的兼容关系如下：

 

 

update，delete，insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁

那什么情况下会对读操作加锁呢？

• select … lock in share mode，对读取的记录加S锁

• select … for update ，对读取的记录加X锁

• 在事务中读取记录，对读取的记录加S锁

• 事务隔离级别在 SERIALIZABLE 下，对读取的记录加S锁

InnoDB中有如下三种锁

• Record Lock：对单个记录加锁

• Gap Lock：间隙锁，锁住记录前面的间隙，不允许插入记录

• Next-key Lock：同时锁住数据和数据前面的间隙，即数据和数据前面的间隙都不允许插入记录

两阶段锁协议

在 InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

 

 

上图事务B的update语句会被阻塞，直到事务 A 执行commit之后，事务B才能继续执行。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。死锁例子如下图所示：

 

 

当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。

• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on，表示开启这个逻辑。

五、MySQL的日志

重要的日志模块：redo log

Mysql每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO成本、查找成本都很高。为了解决这个问题，MySQL的设计者就用了WAL技术来提升更新效率。

WAL的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB的 redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，总共就可以记录4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

 

 

write pos是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos和checkpoint之间还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint推进一下。

有了redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

重要的日志模块：binlog

MySQL 整体来看，其实就有两块：一块是Server层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的redo log是 InnoDB 引擎特有的日志，而 Server层也有自己的日志，称为 binlog（归档日志）。

这两种日志有以下三点不同：

• redo log是 InnoDB引擎特有的；binlog 是MySQL的Server层实现的，所有引擎都可以使用。

• redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

• redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

create table T(ID int primary key, c int);
update T set c=c+1 where ID=2;

 

 

update 语句执行流程

InnoDB 引擎在执行这个简单的 update 语句时的内部流程：

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

• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log里面，此时 redo log处于prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

• 执行器生成这个操作的binlog，并把binlog写入磁盘。

• 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

PS！上述内容来源于京东同事吴浩发言，本人后续做的整理