mysql详解之innoDB

索引

Mysql由索引组织，所以索引是mysql多重要概念之一。

聚簇索引

InnoDB和MyISAm一样都是采用B+树结构，但不同点在于InnoDB是聚簇索引（或聚集索引），将数据行直接放在叶子节点后面。

这里可能存在一个误区：觉得MyISAm是B树。innoDB采用了B+树，将数据存在了叶子节点上。
这个逻辑是不对的。这两个存储引擎都采用的B+树，只是叶子节点上存的数据不一样。MyISAm存的是一个地址，而innoDB存的一整行数据。

聚簇索引是innoDB的主键索引的实现方式。如果没有设置主键呢？难道就没有聚簇索引了？当然不会了，你不创建，innoDB会帮你找一个：

1. 首先看看是不是有唯一索引，如果有的话，那么第一个唯一索引就是聚簇索引

2. 如果唯一索引也没有，表结构中还藏了一个隐藏字段（DB_ROW_ID）作为聚簇索引字段。

如图所示：

辅助索引（二级索引）

非聚簇索引都是辅助索引，包括唯一索引，普通索引，组合索引。

之所以叫辅助索引，是因为一般情况下都需要“回表”，最终还是要靠聚簇索引查出数据。

select * from tab where name = 'a';

如图

索引分裂

当新增/修改索引数据时，索引块空间不够，分裂出一个新的索引块过程成为索引分裂。分裂方式方式包括：

1-9分裂：传统分裂方式。一块索引写完，加一块新的，上一个索引块数据尽量不动。适合索引自增长，事务并发比较低的情况。但可能引起索引块层级突然升高多层的问题。

5-5分裂：新增索引块时，把上一个索引块一半的数据分到新索引块中。保证了增加了B+树层级的稳定性（避免增加一个索引块，增加两层以上的情况）。适合随机索引数据，事务并发高的情况。但会引起索引的空间利用率不高的问题。

buffer poor（缓冲池）

数据库的读操作，并不是直接从磁盘读取数据。而是首先在buffer poor里找，如果有就直接返回，没有再去磁盘获取，放在buffer poor里，然后返回。听起来像缓存，但其实不是。

mysql缓存是[sql语句-查询结果]的key-value数据（由于命中效率不高，在mysql8就去掉了这块功能），属于mysql的server层。
buffer poor里单纯是数据，是存储引擎的一部分。

数据库的写操作，并不是直接改的磁盘文件，而是拿到缓冲池操作。从这个角度，缓冲池起到一个工作空间的作用。修改过的数据页称为脏数据页。等待被刷到磁盘（在刷到磁盘之前先写到redo log中）。

可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小。

表空间

表空间(tablespace)是mysql数据在磁盘上的存储形式。或者说是.ibd文件的数据格式。表空间的逻辑，从大到小依次可拆分为：段(segment)，区(extent)，页(page)，行(Row)。如下图所示

一般把页作为基本单位。

事务

innoDB为实现事务，引入了一系列技术，同时也带来了很多问题，为解决这些问题，又引入更多新技术。

所以mysql没有一开始就给自己默认存储引擎加入事务，也是可以理解的。

事务四大特性：

如图所示

锁

Mysql是一个多线程系统，要实现事务，保证数据库的一致性，锁是必要的技术。

日志

redo log

redo log称为重做日志，用于记录事务操作。以便在服务重启后，通过前滚操作，恢复数据。保证事务在服务正常/异常重启的情况下依然能保障原子性。

有些文章会这么做比喻：

redo log可以理解为饭店里的小黑板。饭店允许客人记账赊账。当就餐人太多时，就先把钱记到小黑板上。这样就不用在忙的焦头烂额时还去翻账本，去记账算账了。
或者
redo log 就像快递代收点，往一家一家送快递，太慢，先统一摆到代收点。有时间再往用户家里送。

从某种程度上说，这么比喻也没问题。但可能会让人产生误解：redo log在起“缓冲”作用？那么下一步是不是：把数据从redo log再往数据文件挪？

如果你这么想，那就可能就被误导了。

redo log和数据写入数据库表文件。正常情况下，两者并没有很强的关联性。你写你的日志，我写我的数据。只有当服务异常重启（断电/服务宕机），redo log才开始起作用。为数据库恢复缺失的数据。如图所示

上图中，左边是redo log buffer写到磁盘日志文件的过程。右边是buffer poor写入磁盘数据库文件的过程。左右两边在正常情况下，并没有什么交集。恢复过程是服务重启时才会起作用。

当然，但也不是完全没关联。redo log在正常情况下并不完全只会无脑记日志。它还起到一个很重要的作用：纪录完后，作为事务完成的标志。有时候甚至会说：日志比数据更重要。

顺序io：由于日志的顺序io的特性，redo log可以实现快速的落盘操作（相比右边的数据落盘要快的多，因为右边要处理复杂的数据结构，写磁盘相当于一种随机io）。
前面说的两个比喻，其实想表达的就是这层意思。

上图中，直接从log buffer 刷到了磁盘。实际操作中其实中间还有一层OS Buffer（操作系统的内核空间），如图所示

注意看这个图，虽然也是两条路，但是redo log和 undo log。其实看到这里就会明白一件事：无论是redo log，undo log还是buffer pool里的数据都需要经过 buffer（用户空间） --> os buffer（操作系统内核空间） --> file（磁盘文件）

这里涉及到一个配置参数innodb_flush_log_at_trx_commit
这个值表示三种从内核刷到磁盘的刷盘策略：

1：【默认】事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。

0：事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。

2：每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

在《Mysql技术内幕》里，作者做了一个实验50万数据的插入测试，配置从1改为0，时间从113秒 缩短到了 13.9秒，近10倍的速度差距，还是很可观的。如果业务以写数据为主（如状态监控系统），并发量不是很大，不在乎可能丢失1秒到数据，我个人认为调为0就行。

前面我们看到有两个redo log日志（ib_logfile0, ib_logfile1），其实以后也不会再增加了。因为redo log是循环写数据。新数据会把最老的数据逐渐覆盖。这个日志本来就是为了拯救系统宕机前最后一批事务。而是不是用来恢复长期数据的。

与之对应的时binlog，有人叫它二进制日志，这纯粹是直译。其实redo log，binlog，undo log 都不是纯文本日志。有人称之为”归档日志“，这个在意思上就很贴切。

redo log和binlog的同步

redo log是innoDB用来保障数据完整性的日志。binlog是Mysql自带的日志，除了可以用来恢复数据，还用于集群之间的数据同步。所以这两者必须保证一致性。否则数据库集群之间的数据就不一致了。innoDB采用了2阶段提交机制：

先把redo log写完，但不改状态。再去写binlog，等binlog写完，再改状态。

实际生产中，在1～6处都可能出现中断。前四处中断都无所谓，二者都可以保持”未提交“状态。但问题是如果在5或者6处中断就有问题了。此时binlog完全可能已经把数据同步给集群的其他数据库了。但redo log却还是prepare状态。这就会导致本机和其他服务器数据不一致。因为2pc在理论上就无法实现一致性。

但好在日志是可以自我校验完整性的，无论是redo log还是binlog都可以。

所以当服务再次重启，redo log着手恢复数据时，如果发现最后一个事物的状态是”prepare“状态。它并不会马上把该事务数据删掉，而是要再去binlog那里看看。如果binglog的这个事务是完整的，那么就可以判断应该就是刚好在5或6的位置中断的。则把事务数据恢复后状态改为commit，这样本机和其他服务器数据就一致了。实现了弥补2pc逻辑漏洞的问题。

当然，由于5，6时间极短。6这一步，只是改一改状态，几乎不费什么时间。前面大家都把事情干完了，最后只是为了一起确认个状态。这也是为什么2pc机制即便存在逻辑漏洞，但依然被使用的原因。

redo log还有一个特性需要强调的是，它是物理日志。经常会以此和undo log一起说，或者跟binlog做对比。因为undo log和binlog都是逻辑日志。具体见博客mysql详解之redolog的关键——幂等性

redo log所在位置（mac系统的目录）

undo log

被称为回滚日志。可以在事务出现异常（非服务或服务器异常）时进行回滚操作。也就是程序正常的回滚操作。

和redo log的物理日志不同，undo log是逻辑日志。可以简单理解为sql语句。

当用户执行了insert语句，那么就会生成delete的undo log日志。当用户执行了a --> b的updte语句，那么就会生成一个b --> a的undo log日志。

undo log和redo log 共同实现了：无论是日常事务回滚还是服务重启，都可以自动保障事务的原子性。

undo log的第二大作用在是MVCC机制中实现多版本控制。MVCC中多个版本的数据，其实只有一个版本。修改就是在原数据上改的（都是buffer pool里的操作）。之所以看起来还能查到“历史版本”。不是保留了历史版本，而是通过undo log回退计算获得的历史版本。

另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

undo log默认和redo log存放在一起，也可以通过配置改为和表文件存放在一起。

参考

深入理解MySQL重做日志 redo log
MySQL事务日志(redo log和undo log)的详细分析【非常全面】
B树索引是怎么分裂的
Mysql buffer pool详解
答应我，这次要搞懂 Buffer Pool
MySQL的表空间是什么
为什么MySQL 删除表数据 磁盘空间还一直被占用
mysql深入浅出之日志(redolog, binlog, undolog)