InnoDB事务及索引原理

购买上帝心灵

InnoDB事务及索引原理 InnoDB事务及索引原理 董明斌 贝壳产品技术 贝壳产品技术 “贝壳产品技术公众号”作为贝壳官方产品技术号，致力打造贝壳产品、技术干货分享平台，面向互联网/O2O开发/产品从业者，每周推送优质产品技术文章、技术沙龙活动及招聘信息等。欢迎大家关注我们。 242篇内容 2018年06月01日 19:35 点击上方蓝字关注董明斌，商业平台研发部高级开发工程师，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 MVCCInnoDB使用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 log3.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更多精彩请猛戳右边二维码关注我们的公众号产品技术先行 预览时标签不可点 关闭更多小程序广告搜索「undefined」网络结果