点击上方蓝字关注

董明斌，商业平台研发部高级开发工程师，16年3月加入链家网（现贝壳找房），专注流量方向研发工作，擅长后端开发。

1. 前言

MySQL涉及到的知识多且深，这里主要捡两个最基础也是后端RD最常接触到的点来展开：InnoDB的事务及索引原理，偏理论，面试中被问到的概率非常大。为了更好的说明原理，贴了很多图，大多来源于网络，侵删。

2. InnoDB存储引擎

2.1 MySQL分层架构

接入层：主要负责连接处理、授权认证、安全等事宜。
服务层：查询解析、分析、优化、缓存及所有内置函数，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图、binlog、表锁等。
存储引擎层：负责MySQL中数据的存储和提取，服务层通过API与存储引擎通信，存储引擎包含几十个底层函数API，每种引擎提供一套具体实现。
系统文件层：负责底层文件系统的读写。

这种分层架构，可以将各层的职责划分得很清晰，方便扩展。

2.2 InnoDB存储引擎

InnoDB属存储引擎层，是MySQL的默认存储引擎（5.1版本及以上）。InnoDB相较其它存储引擎的主要特点有：支持事务、支持高并发、自动崩溃恢复、基于聚簇索引组织表数据等。我们主要关注如下问题：InnoDB是如何保证事务？如何支持高并发？数据如何存储？

3. 事务原理

事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID，这是标准SQL规范，InnoDB通过自己的方式实现之。

3.1 ACID特性

原子性：最小工作单元，要么全成功，要么全失败。
一致性：事务开始和结束后，数据库的完整性不会被破坏。
隔离性：事务之间互不影响，四种隔离级别 RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）。
持久性：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失。

acid特性

主要关注下隔离性，InnoDB默认隔离级别为RR，该级别下InnoDB通过MVCC机制—— “非阻塞的快照读和加锁（行锁+间隙锁）的当前读”避免了幻读的发生。那么什么是幻读呢？所谓幻读，是指同一个事务里，相同语句的当前读，返回的记录是完全相同的 (记录数量一致，记录本身也一致)，后面的当前读，不会比第一次返回更多的记录 (幻象)。

3.2 事务日志

InnoDB 使用undo、 redo log 来保证事务原子性、一致性及持久性，同时采用预写日志方式将随机写入变成顺序追加写入，提升事务性能。

undo log：记录事务变更前的状态。操作数据之前，先将数据备份到undo log ，然后进行数据修改，如果出现错误或用户执行了rollback 语句，则系统就可以利用undo log 中的历史版本恢复到事务开始之前的状态。
redo log：记录事务将要变更后的状态。事务提交时，只要将redo log 持久化即可，数据可在内存中变更。当系统崩溃时，虽然数据没有落盘，但是redo log 已持久化，系统可以根据redo log 的内容，将所有数据恢复到最新的状态。
checkpoint：随着时间的积累，redo log 会变的很大很大。如果每次都从第一条记录开始恢复，恢复的过程就会很慢。为了减少恢复的时间，就引入了checkpoint 机制。定期将databuffer 的内容刷新到磁盘datafile 内，然后清除checkpoint 之前的redo log 。
自动恢复：InnoDB通过加载最新快照，然后重放最近的checkpoint 点之后所有redo log 事务（包括未提交和回滚了的），再通过undo log 回滚那些未提交的事务，来完成数据恢复。需要注意的地方是，undo log 其实也是行数据，对其写操作也会记录到redo log 内，即undo log 也是通过redo log 来保证持久化的。

事务流程

上图为事务写操作执行的大致过程，整个过程中只有一次刷盘操作，即事务提交时的redo log 的写盘。其实写盘并不一定会立马持久化到磁盘，要看数据库配置，默认情况下Innodb_flush_log_at_trx_commit=1 ，即一次redo log 写盘操作会立即写到磁盘中，是最保险的方案。

InnoDB中多个事务共享一个redo log buffer ，写盘时，会将当前buffer 中的多个事务日志持久化，而不管事务有没有commit ，而且并不是只有事务commit 才会触发redo log 写盘，其它操作如redo log buffer 空间不足、触发checkpoint 、实例shutdown 及binlog 切换时都会触发redo log 写盘操作。

3.3 MVCC

InnoDB使用MVCC机制来提升RR隔离级别的并发性。MVCC (Multi-Version Concurrency Control) 多版本并发控制协议，将读操作分成两类：快照读 与当前读 。读取的是记录的最新版本，会对返回的记录加上锁，确保其他事务不能并发修改 。

快照读：简单的查询操作，属于快照读，不加锁。如：

1 select * from table where ?;

当前读：特殊的读操作及插入/更新/删除操作，属于当前读，需要加锁。以下都是当前读：

1  select * from table where ? lock in share mode;2  select * from table where ? for update;3  insert into table values (…);4  update table set ? where ?;5  delete from table where ?;

快照读是通过undo log 来实现多个版本的控制。如下图，每个数据行：row_id 为行id，trx_id 表示最近修改的事务id，db_roll_ptr 为指向undo segment 中undo log 的指针。快照读时，比较当前事务id与trx_id 的关系，如果trx_id 小于事务id，则该条数据对当前事务可见，反之不可见，不可见时再通过db_roll_ptr 查找历史版本记录，取出可见的最近的历史记录。undo log 的链路不会很深，后台purge 线程定期清除无用的历史版本（在没有活动事务依赖时，undo log 即可被删除）。

3.4 加锁分析：总结于何登成的《 InnoDB加锁处理分析》

当前读都会加锁，怎么加？则要看具体情景——隔离级别及索引情况。在InnoDB的RR隔离级别下，对于同一条SQL语句：

DELETE FROM T1 WHERE ID=10;

当ID列为主键时：锁主键索引上id=10 的记录。
当ID列为唯一索引：先锁唯一索引上的id=10 的行，再锁主键索引上name=d 的行。

当ID列为二级索引：在二级索引上，会给id=10 的所有行加X锁，而且会给被锁行的前后范围加GAP锁；主键索引上，给相应记录加X锁。

当ID列未加索引：此种情况后果很严重！主键索引所有行都被加X锁，所有间隙被加GAP锁！全表的数据都被锁的，没有并发可言，因此一定要检查当前读的where条件语句是否走索引。

GAP锁的意义：当前事务占住间隙范围，避免其它事务往这个范围插入数据，引起幻读，只发生在RR隔离级别。如果id列是唯一索引（或主键索引），且当前读条件语句中的id不存在时，InnoDB也会给范围加GAP锁。

4. 索引结构

使用索引的优点：减少需要扫描的数据量，避免文件排序及临时表，将随机I/O变为顺序I/O等，从而达到更快的读写数据。InnoDB采用B+树的结构来组织索引。

4.1 B+树

InnoDB之所以采用B+树来组织索引，是由其扁平化的结构决定的。非叶子节点记录索引列的key值，真实数据只存于叶子节点，这样的好处是非叶子节点很适合做缓存（一个大节点约16k，能存储1200多个key值）。真实数据库中的B+树是非常扁平的，高度为3时容量可达22GB;高度为4时则可存储26TB；另外大节点之间用双向链表互连，方便顺序扫描。

4.2 聚簇索引及二级索引

聚簇索引：是按照每张表的主键，构造一颗B+树，同时叶子节点存放的是表的行纪录数据（聚集索引的叶子节点也称为数据页）。聚簇索引是一种数据存储方式。将主键id设为自增，可使随机insert变为顺序append，不会产生页分裂和碎片，提升写性能。
二级索引：InnoDB二级索引的叶节点存储的是主键id，查询数据时，先索引到主键id，再回聚簇表查询数据详情，需要走两次索引查找。主键的数据类型尽量要小，它直接影响索引树的存储空间。

4.3 高性能索引策略

正确地创建和使用索引是实现高性能查询的基础。

独立的列：指索引列不能是表达式的一部分，也不能是函数的参数。我们应该养成简化 WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。
前缀索引及索引选择性：有时候需要索引很长的字符列，这会让索引变得大且慢。可以索引开始的部分字符，能大大节约索引空间，提高索引效率，这就是前缀索引。索引的选择性越高则查询效率越高，前缀索引取多长字符，需要折中数据大小与选择性强弱。
合适的索引列顺序：索引不是越多越好，通常会建一个复合索引，以满足多个查询语句，这就要求合适的索引列顺序。复合索引的匹配规则是，最左前缀匹配，且遇到第一范围查询条件时，停止匹配。因此通常会将通用的列放索引前面，范围查询列放索引后面。
覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，称之为“覆盖索引”。这是个非常有用的工具，能够极大的提高性能，只需要扫描二级索引而无须回表。
使用索引扫描来排序：MySQL有两种方式生成有序的结果，排序操作或者按索引顺序扫描。排序操作费时费空间，而索引扫描只需要从一条索引记录移到紧接着的下一条记录，是很快的。需要注意，只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。

SQL优化跟索引息息相关，需要具体场景具体分析。EXPLAIN之后，关注有没有走预期的索引，有没有文件排序，扫描多少数据量等等。

5. 总结

后端RD在日常工作中会经常遇到MySQL死锁及慢查询问题，带着这些问题，我们能更快的去了解InnoDB的事务及索引原理；反之，理解了原理，再回顾之前遇到的场景，也能豁然。通过本文希望大家能理解InnoDB是如何保证事务？如何支持高并发？数据如何存储？

参考

InnoDB加锁处理分析
《高性能MySQL》
InnoDB存储引擎MVCC实现原理
MySQL的InnoDB索引原理详解
MySQL · 引擎特性 · InnoDB redo log漫游

作者：董明斌

监审：程天亮

编辑：钟艳

网址：tech.lianjia.com

更

多

精

彩

请猛戳右边二维码

关注我们的公众号

产品技术先行