【读过的书，留下的迹】MySQL技术内幕-InnoDB存储引擎

最新推荐文章于 2023-04-24 09:36:16 发布

linxdcn

最新推荐文章于 2023-04-24 09:36:16 发布

阅读量638

点赞数

分类专栏：读书笔记文章标签： mysql 存储

本文链接：https://blog.csdn.net/linxdcn/article/details/72850052

版权

读书笔记专栏收录该内容

15 篇文章 0 订阅

订阅专栏

1 MySQL体系结构和存储引擎

（1）MySQL存储引擎

InnoDB存储引擎
- 支持事务
- 支持行锁
- 支持外键
- 支持非锁定读
MyISAM存储引擎
- 不支持事务
- 表锁
- 支持全文索引

2 InnoDB存储引擎

（1）InnoDB体系架构

后台线程
- Master Thread：负责数据异步刷新到磁盘，保证数据的一致性
- IO Thread：IO请求
- Purge Thread：挥手已经使用并分配的undo页
内存池
- 主要包括索引页，数据页，undo页，插入缓冲，自适应哈希索引，锁信息，字典信息

（2）InnoDB关键特性

插入缓冲：对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页，而是先判断插入的非聚集索引页是否在缓冲池中，若在则直接插入；若不在，则先放入到一个Insert Buffer对象中
两次写：重做日志中记录的是对页的物理操作，在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做

自适应哈希索引：InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引
异步IO：用户可以在发出一个IO请求后立即再发出另一个IO请求
刷新邻接页：当刷新一个脏页时，InnoDB存储引擎会检测该页所在的区（extent）的所有页，如果是脏页，那么一起刷新

3 文件

（1）日志文件

错误日志：对MySQL的启动、运行、关闭过程进行了记录，不仅记录所有错误信息，也记录一些警告或正确信息。
慢查询日志：定位可能存在问题的SQL语句
查询日志：记录所有对MySQL数据库请求的信息
二进制日志：记录对MySQL数据库执行更改的所有操作

（2）InnoDB存储引擎文件

表空间文件：InnoDB采用将存储的数据按表空间进行存放的设计
重做日志文件：它与二进制日志的区别
- 二进制日志记录所有与MySQL有关的日志；而InnoDB存储引擎重做日志只记录有关InnoDB的事务日志
- 二进制记录关于一个事务的具体操作内容；重做日志记录关于每个页的更改的物理情况
- 二进制日志仅在事务提交前进行提交；而在事务进行的过程中，却不断有重做日志被写入

4 表

4.1 InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称为表空间。表空间又由段、区、页组成

段：常见的段有数据段、索引段、回滚段
区：区由连续的页组成，在任何情况下每个区的大小都为1MB
- 对于小表，开始时先用32个碎片页来存放数据，用完后才会申请64个连续页（一个区），这样可以节约空间
页：默认每个大小为16KB

4.1 InnoDB行记录格式

（1）Compact行记录格式（老版本InnoDB）

NULL列不占任何空间，只占NULL标志位
页内部通过一种链表的结构来串连各个行记录
不管是BLOB或VARCHAR，当数据页放不下时，部分存储在数据页，其余存储在溢出页（需保证一个数据页至少有两条记录）

（2）Compressed行记录格式（新版本InnoDB，InnoDB1.0.x开始支持）

对于BLOB中的数据采用完全的行溢出存储

（3）char的行结构存储

对于UTF-8下的CHAR(10)类型的列，其最小可以存储10字节的字符，最大可以存储30字节的字符。

因此对于多字节字符编码的CHAR数据类型的存储，InnoDB存储引擎在内部将其视为变长字符类型，与VARCHAR的实际存储基本没区别。

4.2 InnoDB数据页结构

File Header：用来记录页的一些头信息
Page Header：用来记录数据页的状态信息
Infimum和Supremum Record：用来限定记录的边界
User Record和Free Space
Page Directory：B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找
File Trailer：检测页是否已经完整地写入磁盘

4.3 分区表/font>

分区地过程是将一个表或索引分解为多个更小、更可管理地部分。MySQL数据库支持地分区类型是水平分区，并不支持垂直分区。

（1）分区类型

RANGE：NULL值小于任何一个非NULL值，NULL值分在最左边地分区
LIST：若需NULL分区，必须显式指出那个分区放NULL值
HASH：HASH和KEY，任何函数都会将含有NULL值地记录返回0
KEY

（2）分区性能

数据库的应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏；另一类是OLAP（在线分析处理），如数据仓库、数据集市。

对于OLAP的应用，分区可以很好提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。

对于OLTP的应用，分区应该非常小心。因为其大部分都是通过索引返回几条记录。对于一张大表，一般的B+树需要2～3次磁盘IO，能较快解决问题。分区反而可能会导致更多次磁盘IO。

5 索引与算法

5.1 B+树

所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。

一般插入操作

一般删除操作

ps：为尽量减少分页，B+树提供了旋转操作，旋转发生在Leaf Page已经满，但其左右兄弟节点没有满的情况下

5.2 B+树索引

数据库中的B+树索引可以分为：

聚集索引：按每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页
辅助索引（非聚集索引）：叶子节点并不包含行记录的全部数据

（1）聚集索引

聚集索引的存储并不是物理上连续的，而是逻辑上连续的
对于主键的排序查找和范围查找速度非常快

（2）非聚集索引

过程：InnoDB存储引擎会遍历辅助索引并通过页级别的指针（书签页）获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录

其他数据库，如SQL Server，或引擎，如MyISAM，无聚集索引。它们通过维护一个堆表，即行数据的存储按照插入的顺序存放，这时索引的书签页是一个行标识符

5.3 Cardinality值

Cardinality值表示索引中不重复记录数量的预估值，是通过抽样的方法估算的（默认随机对8个叶子节点抽样）

什么时候索引有用：

在访问表中很少一部分行记录时B+树索引才有意义
Cardinality/n_rows_in_table值接近1时

5.4 B+树索引的使用

（1）联合索引

数据页按联合索引的顺序存放，如（a, b, c)
左前缀查找有效：如
- where a = 1 and b < 1
- where a = 1 and b =3
- where a like ‘some%’

（2）覆盖索引

覆盖索引：即从辅助索引中就可以得到查询的记录，不需要查询聚集索引中的记录

5.5 哈希索引

InnoDB中是自适应哈希索引，数据库自身创建并使用，不能对其进行干预

5.6 全文索引

全文索引是将存储与数据库中的整本书或整篇文章中的任意内容信息查找出来的技术，采用倒排序索引实现，它在辅助表中存储了单词与单词自身在一个或多个文档所在位置之间的映射：

inverted file index，表形式：｛单词，单词所在文档、记录的ID｝
full inverted index，表形式：｛单词，单词所在文档、记录的ID，具体位置｝
InnoDB引擎：
- 有stopword列表，表示该列表中的word不需要索引分词
- 限制
  - 每张表只能有一个全文检索的索引
  - 不支持每个单词界定符的语言，如中文，韩文