InnoDB存储引擎

概述

作为mysql最常用的存储引擎,本文主要介绍innodb数据结构,事务,索引,锁等。基于mysql版本8.0.15;

> 架构图

查看和使用Innodb

mysql root@192.168.99.100:test>  CREATE TABLE IF NOT EXISTS test_innodb(id int primary key, name varchar(20), id_card bigint(19) NOT NULL, gender char(10));
mysql root@192.168.99.100:test> show create table test_innodb;
+-------------+--------------------------------------------------------------------+
| Table       | Create Table                                                       |
+-------------+--------------------------------------------------------------------+
| test_innodb | CREATE TABLE `test_innodb` (                                       |
|             |   `id` int(11) NOT NULL,                                           |
|             |   `name` varchar(255) DEFAULT NULL,                                |
|             |   PRIMARY KEY (`id`)                                               |
|             | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+--------------------------------------------------------------------+
1 row in set
Time: 0.034s

说明,InnoDB已经是默认存储引擎,指定引擎可以通过如上ENGINE=完成。查看所有引擎可通过:

# 查看存储引擎
SHOW ENGINES;
SELECT * FROM INFORMATION_SCHEMA.ENGINES;
# 修改和复制
TABLE table_name ENGINE=InnoDB; 
CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM other_engine_table;

物理存储格式

先看一行记录的存储格式。有REDUNDANT、COMPACT、DYNAMIC、COMPRESSED,默认DYNAMIC。
常用操作命令详见innodb-row-format

SELECT @@innodb_default_row_format;
SET GLOBAL innodb_default_row_format=COMPACT;
CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/test_innodb' \G


每条数据大概按上图格式存储(不准确,只是大概表达意思),不同格式差异见文档innodb-row-format

页(Page)

数据存在磁盘上,存储引擎内存和磁盘交互时以页(Page)为单位,读写都是按页来,这样可以减少耗时的io操作次数。这也是为什么大部分索引使用B-树作为存储结构的原因(Btree-B+Tree)。
page的默认一般是16KB

mysql root@192.168.99.100:test> select @@innodb_page_size
+--------------------+
| @@innodb_page_size |
+--------------------+
| 16384              |
+--------------------+

索引

InnoDB使用B+树作为索引模型。B+树的结构和好处我在之前的文章中写过(Btree-B+Tree),这儿就不细说。现在往表test_innodb插入一下数据,并把id_card作为索引。

insert into test_innodb (id,name,id_card,gender)values(1,'aa',10,'f');
insert into test_innodb (id,name,id_card,gender)values(9,'bb',30,'m');
insert into test_innodb (id,name,id_card,gender)values(7,'cc',20,'f');
insert into test_innodb (id,name,id_card,gender)values(8,'dd',90,'f');

在磁盘上,索引的存储结构如下:

> 也是大概表达意思。真实情况下1页不会只有两个节点。到底是几X树,是索引大小决定的。
* 主键索引id:他的叶子节点上保存了这一行的数据,也叫聚簇索引。
* id_card索引:他的叶子节点保存是主键id,也叫二级索引。所以如果通过id_card查询,先扫描这个二级索引拿到id,再去主键索引查询需要的数据(回表),如果我只返回id_card、id这个字段,直接返回(索引覆盖),不需要回表。

下面看看组合索引:
如果我把name和id_card建组合索引:KEY(name,id_card),根据B+树的结构,如下:

所以在树的搜索时,肯定需要先匹配name才能查找,如果where id_card = 30,这颗B+树无法完成搜索。这就是“最左匹配原则”。其实也不需要记住怎么建索引和查询能命中索引,只要记住B+树的结构,一目了然。比如区间查找,只需要遍历叶子节点链表即可,因为都是按自然数小到大排列的。再如 where name=«xx» or id_card=90,因为or后面的条件不在name的结果中,也无法使用此组合索引。

对于非唯一索引,如果索引值相同,按叶子节点id的值顺序存储。

另外,对索引字段太长的情况,可以通过截取前面一段建索引的方式优化。因为每一页大小有限,索引字段越小,索引树高度可能就越小,查询走io次数就越少。尽可能一页容纳更多节点,这样每次读入内存的数据就越多,也提高了查询效率。

mysql对select count(*) form table作了优化,他们挑选最小的一颗索引树统计,也是同样的道理。

总之,索引的问题核心在B+树和Page这两个概念,明白了,很多问题迎刃而解,不需要记那么多规则。当然了,索引还有一些其他的优化,看下面的例子:

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));
#此sql能使用索引
SELECT * FROM t1 WHERE f1 + 1 > 9;
#下面sql无法使用索引。表达式必须是相同的且返回相同的类型。
SELECT * FROM t1 WHERE f1 + 1 > 9;
# 如果按下列方式建表,则都根据f1查询时不会用到索引。不能没有表达式直接映射。
CREATE TABLE t1 (f1 INT, gc INT AS (f1 ) STORED, INDEX (gc));

详细优化内容见: optimization

Buffer Pool

上文说道,内存和磁盘以页为最小单位交互,下面看看内存结果Buffer Pool。它主要是用来缓存被访问的所有和表数据,一般80%的物理内存都用来给他使用。它使用LRU淘汰算法,把page以链表的方式组织起来。LRU算法见我的另一篇文章聊聊本地缓存。结构如下图:


被使用的page放到New SubList头,从old Sublist淘汰最久不使用的页。如果新增,则插入old Sublist的头部。这么设计目的是增加缓存命中率。如果有很大的查询数据或者全表扫描的情况,可能直接把大部分page淘汰掉,降低了缓存的命中率。
buffer Pool的使用情况可以通过命令监控:

SHOW ENGINE INNODB STATUS,

Change Buffer

Change Buffer 用来记录那些要更新的索引页没有在内存(buffer pool)的情况的二级索引(非唯一索引)。下面通过问题方式解答起特点。
1. 只更新到缓存中,读操作来了怎么办:把原数据页读入内存和它合并得到最新页。
2. 内存怎么保证一致性:change buffer可以持久化,会被写入磁盘,如果没有读,会定时merge到磁盘。
3. 作用:高并发更新时,减少和磁盘交互次数,加速更新操作,避免占用更多buffer pool。适合写多读少的情况。如果读频繁,一样需要读磁盘,反而增加了merge的代价。
4. 为什么唯一索引不能用:因为要判断唯一性,只能把数据读入buffer pool,自然用不上了。
5. change buffer用的也是buffer pool的内存,大小可以配置(比如50%)。

有的业务双写历史数据表,这个表建普通索引比较适合。总结来说,他的作用是减少随机读磁盘的消耗。

redo log

redo log是用来记录物理操作的(在某个数据页上做了什么修改),如果出现数据页未更新就crash的情况,再启动时可以根据它来恢复数据(crash-safe)。在磁盘上一般由这多个固定大小的文件(个数大小都可配置)来保存,如:ib_logfile0、ib_logfile1。先记录日志再写数据这种方式叫WAL(Write-Ahead Logging)机制。

redo log顺序写,比随机写数据快很多。因为文件大小固定,它只能循环写,先保存一个数据文件同步过的位置(checkpoint),追加往后写,如果写指针回到checkpoint位置,就停下来把之前的log更新到数据页中。

mysql不是还有binlog么,为什么不能用它。因为他是mysql提供的归档日志,不具备crash-safe的能力,innoDB只能通过redo log来实现。并且,为了保证binlog事务一致性,使用到了两阶段提交:写redo log-> 写binlog->把redo log标记为提交状态。恢复时需要对比binlog和redo log的提交状态保证一致。

redo log还使用了group commit的方式,在提交之前刷磁盘,但是批量刷,而不是对每一条单独flush。这样可以提供系统吞吐。

undo logs

innodb很重要的一个特点就是支持事务,提到事务,就会说道ACID(原子性,一致性,隔离性,持久性),undo logs就是用来实现事务的,就是常说的要么成功,要么失败。隔离性有几个级别:
1. 读未提交:一个事务没提交前修改的数据其他事务也能读到。
2. 读提交:只有提交了其他事务才能读到。
3. 可重复读:事务过程中,不管其他事务有没有提交,读到的数据是一致的。
4. 串行化:一个事务完成,其他事务才能操作。

默认是隔离级别是第三种:可重复读

mysql root@192.168.99.100:test> show variables like '%ISOLATION%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

用一个示例来说明下:

红色session第一次读到0,在浅蓝色session提交后,他读到的还是0,也就是保证开启事务后读到在值都一样。但是更新后,红色查询结果为3,而不是2,是因为更新操作有”当前读“的规则,保证了数据的正确性。除了更新操作,如果查询语句是加锁或者select for update,也是当前读。

undo logs记录了数据变更的多个版本(反向操作),而开启事务的session被分配一个递增的事务id(trx_id),事务更新的数据会保存这个id,这样,此事务就可以读到它想要的版本,然后计算出对应值,实现隔离和回滚。

mysql锁一般分为全局锁、表锁、行锁。其中行锁由存储引擎自己去实现。

全局锁

FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES

整个库的表都处于只读状态,如果有更新操作,只能阻塞等待。常用于备份。他有个特点,如果session失效,自动释放锁,这是和GLOBAL read_only = ON的区别。另外,后者对super用户无效。

表级锁

lock tables ...  read/write
UNLOCK TABLES

他和全局锁使用类似

还有一个叫元数据锁(meta data lock,MDL),这种锁不需要显示使用命令,mysql自动实现的。
* 增删改查表:MDL读锁。读锁直接不互斥,也就是说可以同时增删改查。
* 变更表结构:MDL写锁,和读写锁写锁都互斥,也就是在改表结构时既不能做增删改查也不能执行其他修改表结构的语句。

所以需要小心,在改表结构时,别在大量增删改查时进行,避免阻塞导致线程耗尽。

行锁

行锁是存储引擎实现的,Innodb通过锁索引实现。标准行级锁有两种类型,共享锁和排它锁。换种方式理解,读锁是共享锁,读锁和读锁不排斥,写锁和其他锁都排斥。
* 意向性锁:它是一种表级锁,也分共享锁和互斥锁,但它们相互不阻塞,只是在有人请求锁定某行时用到。
* 对应到索引上的锁由三种:记录锁,间隙锁,Next-Key Lock。下文重点谈这几个锁。


之前索引结构图,加在索引记录上的锁也叫记录锁。只有锁住一个索引不够,如果我的更新where id between 7 and 9,此时其他事务往此区间插入一条记录,就会出现脏读。Next-Key Lock是和间隙锁和记录锁的组合。分析锁有些原则:
1. 只有扫描到的索引才加锁。
2. 间隙锁主要锁的是往间隙插入记录。其他间隙锁并不互斥。
3. 唯一索引有优化,只对扫描到的索引加锁。
4. 可以理解,锁的单位都是Next-Key Lock,左开右闭(后面会说到),如果扫描到下一个索引时不等于,会退化为间隙锁。

下面通过实例分析比较清楚点:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
test> select * from t
+----+----+----+
| id | c  | d  |
+----+----+----+
| 0  | 0  | 0  |
| 5  | 5  | 5  |
| 10 | 10 | 10 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
+----+----+----+

示例来自极客时间《mysql实战45讲》,其他有的总结也是那儿的学习笔记。
1. 一个事务通过where id=5更新,只在此索引加锁,锁住这一行:只要后续事务更新的不是同一行,事物就不会阻塞。但是通过where d=xx更新会锁住,因为d不是索引,会全表扫描(形成所有间隙的),遇到这行就阻塞了。
2. 通过where c= 5更新:c不是唯一索引,会在c=5上记录锁和间隙锁,但如果需要回表,扫描到了id=5这行,主键也加上了记录锁。
3. 如果 select * from t where d=5 for update; 不是索引,会扫描所有记录,形成Next-Key Lock:(负无穷,0],(0,5],(5,10],(10,15](15,20],(20,25],(25,正无穷],相当于锁住了整个表。

-- 事务1: 
-- 锁:
--    1. id=5这一行的记录锁;
--    2. c不是唯一索引,此索引的前后都可能插入入新的索引,所以添加2个Next-Key Lock,(0,5](5,10],因为c=10这个索引和5明显不相等,不需要锁住它,作了优化,退化为间隙锁:(0,5](5,10)
 select * from t where c=5 for update; 

  -- 事务2
select * from t where c=5 for update;  -- 阻塞: 碰到记录锁,阻塞
select * from t where c=6 for update; -- 不阻塞:  间隙锁只排斥insert。
insert into t values(12,0,2) -- 阻塞:申请id锁(10,15)不冲突,c=0产生锁区间是(-,0](0,5)和事务1冲突且是insert操作
insert into t values(6,10,2); -- 阻塞:  申请id锁(5,10)不冲突,c=10->[10,15)按理锁不冲突,为什么锁了?见下方图。
insert into t values(11,10,2) -- 不阻塞: 和上条语句正好反过来,它应该插入c=10这个索引的右边,而右边并没有被事务1锁住。
insert into t values(6,11,2); -- 不阻塞: 和上条语句类似,11在10的右边,和事务1不冲突


insert into t values(6,10,2); 这需要回到索引结构,如上图(只列出树的叶子节点)
* 主键索引key(id): 记录锁。key(id=5)
* 非唯一索引key©: 它的叶子节点记录了主键id的值,比如key(c=10)的值是这行的id(10),现在我需要插入索引key(c=10),对应值为6,按索引结构应该在左边,而事务1对此区间加锁了,所以阻塞。所以所间隙锁锁的是索引间隙,如果只看区间开闭,容易蒙。

CONTENTS