内容简介:MySQL涉及到的知识多且深,这里主要捡两个最基础也是后端RD最常接触到的点来展开: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 (串行化)。
-
持久性:事务提交后,对数据的修改是永久性的,即使系统故障也不会丢失 。
主要关注下隔离性,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
更
多
精
彩
请猛戳右边二维码
关注我们的公众号
产品技术先行
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。