InnoDB 事务及索引原理

点击上方 蓝字 关注

董明斌,商业平台研发部高级开发工程师, 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 写盘操作会立即写到磁盘中,是最保险的方案。

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 即可被删除)。

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;另外大节点之间用双向链表互连,方便顺序扫描。

B+树
大节点

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

请猛戳右边二维码

关注我们的公众号

产品技术先行

我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章