原创

InnoDB事务及索引原理

MySQL涉及到的知识多且深,这里主要捡两个最基础也是后端RD最常接触到的点来展开——InnoDB的事务及索引原理,偏理论,面试中被问到的概率非常大。有理解不对的地方,欢迎纠错。

一、MySQL分层架构


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

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


二、InnoDB存储引擎

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


三、事务原理

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

1、ACID 定义如下:

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


主要关注隔离性,InnoDB默认隔离级别为RR,在该级别下InnoDB通过MVCC机制—— “非阻塞的快照读和加锁(行锁+间隙锁)的当前读”避免了幻读的发生。那么什么是幻读呢?所谓幻读,就是同一个事务,连续做两次当前读 (例如:select * from t1 where id = 10 for update;),那么这两次当前读返回的是完全相同的记录 (记录数量一致,记录本身也一致),第二次的当前读,不会比第一次返回更多的记录 (幻象)。

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之后所有事务(包括未提交和回滚了的),再通过undo log回滚那些未提交的事务,来完成数据恢复。需要注意的地方是,undo 日志其实也是行数据,对其写操作也会记录到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、MVCC

InnoDB使用MVCC机制来提升RR隔离级别的并发性。MVCC (Multi-Version Concurrency Control) 多版本并发控制协议,将读操作分成两类:快照读 (snapshot read)与当前读 (current read)。快照读,读取的是记录的可见版本 (有可能是历史版本),不用加锁。当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。

快照读:简单的select操作,属于快照读,不加锁。如:

select * from table where ?;

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

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
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即可被删除)。

4、加锁分析:总结于何登成的 http://hedengcheng.com/?p=771MySQL 加锁处理分析

当前读都会加锁,怎么加?则要看具体情景——隔离级别及索引情况。
在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锁。


四、索引结构

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

1、B+树:

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



2、聚簇索引及二级索引

聚簇索引:是按照每张表的主键构造一颗B+树,同时叶子结点存放的即为整张表的行纪录数据(聚集索引的叶子结点也称为数据页),是一种数据存储方式。主键id为自增是有优势的,插入是顺序的,比完全随机性能要高,不会产生页分裂和碎片。

二级索引:InnoDB叶节点存储的是主键id,走二级索引查询数据详情时,先索引到主键id,再回聚簇表查询数据详情,需要走两次索引查询。主键的数据类型尽量要小,它直接影响索引树的存储空间。

3、高性能索引策略

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

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

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


五、总结

后端RD会经常遇到MySQL写操作死锁及慢SQL优化,带着这些问题,我们能更快的去了解InnoDB的事务及索引原理;反之,理解了原理,再回顾之前遇到的场景,也能豁然。

六、参考

1、 http://hedengcheng.com/?p=771

2、《高性能MySQL》

3、 https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/

4、 https://www.cnblogs.com/shijingxiang/articles/4743324.html

5、 http://mysql.taobao.org/monthly/2015/05/01/


正文到此结束
该篇文章的评论功能已被站长关闭
本文目录