2021年04月07日阅读 7

Mysql-InnoDB 查询一条数据详解 | 小册免费学

本文主要通过分析Mysql Innodb存储引擎插入一条数据的过程来构建脑海中的Mysql图像，将书中琐碎的Mysql知识点关联起来。主要参考：掘金小册-《从根上儿理解Mysql》、《Mysql技术内幕-Innodb存储引擎》、《数据库系统概念》

建表

// 创建数据库
mysql> create database blog;
Query OK, 1 row affected (0.23 sec)
// 更换数据库
mysql> use blog;
Database changed
// 创建具有 聚簇索引和二级索引 这两个索引的表
mysql> create table blog (
    ->  id INT NOT NULL AUTO_INCREMENT,
    ->  secondary_index VARCHAR(100),
    ->  normal_field VARCHAR(100),
    ->  PRIMARY KEY (id), // 聚簇索引
    ->  KEY idx_secondary_index(secondary_index) // 二级索引
    -> )ENGINE=InnoDB;
// 插入数据（后续讲执行计划的时候会批量插数据）    
mysql> insert into blog (secondary_index, normal_field) values ('index_text1', 'normal_text1');
Query OK, 1 row affected (0.43 sec)
复制代码

mysql客户端-服务端交互

客户端向服务端发送一条select指令

mysql> select * from blog where id = 10\G;
复制代码

我们可以在mysql（mysql客户端程序）的配置文件或控制台中设置客户端发送命令的编码格式：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
复制代码

比如我们设置客户端编码格式为utf-8后，客户端便会将上述Select命令按照utf-8进行编码后发送到服务端。

解析请求

[mysqld]
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
复制代码

服务端按照mysqld（mysql服务端程序）中配置的字符集对客户端的请求进行解码，因此客户端与服务端的编码与解码字符集必须要是一致或兼容的。

执行查询

server层主要通过查询优化器生成执行计划，并按照执行计划依次调用存储引擎层提供的用于操作数据库数据的API；

存储引擎层，如InnoDB使用一定的数据结构+算法管理磁盘数据，并对上层提供数据操作的抽象；

查询宏观图

暂时看不懂没有关系，一开始给出概览图方便之后进行拆分讲解~

select 表

表是数据库基于操作系统提供的文件抽象对于关系模型的实现，表与文件是一对一or一对多的关系。

PS: 在InnoDB中，如果开启了innodb_file_per_table，则表空间与文件是一对一的关系

对于我们的select语句，mysql首先要定位表在磁盘中的位置。

mysql通过SYS_TABLES、SYS_INDEXS、SYS_COLUMNS、SYS_FIELDS等系统表来维护用户表的相关信息；

mysql> select * from INNODB_SYS_TABLES where name = 'blog/blog'\G;
*************************** 1. row ***************************
     TABLE_ID: 44         // 每个表在Mysql中都有唯一的Table_Id
         NAME: blog/blog  // 数据库名/表名
         FLAG: 33          
       N_COLS: 6          // 表的列数（3+2+1） 
        SPACE: 29         // 表空间id
  FILE_FORMAT: Barracuda  
   ROW_FORMAT: Dynamic    // 数据行格式(Compact格式的优化版，对于列中很长的数据值直接分BOLB页存储)
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
复制代码

然后我们通过TABLE_ID = 44在SYS_INDEXS表中查询blog表的索引信息：

mysql> select * from INNODB_SYS_INDEXES where table_id = 44\G;
*************************** 1. row ***************************
       INDEX_ID: 50 // 全局唯一索引ID
           NAME: PRIMARY // 主键索引-聚簇索引
       TABLE_ID: 44
           TYPE: 3 // 索引类型
       N_FIELDS: 1 // 索引列数=1，对于联合索引来讲该值为构成索引的列数
        PAGE_NO: 3 // 主键索引对应的根索引页号
          SPACE: 29 // 主键索引根索引页所在的表空间号
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       INDEX_ID: 51 // 全局唯一索引ID
           NAME: idx_secondary_index // 二级(辅助)索引
       TABLE_ID: 44
           TYPE: 0 // 索引类型
       N_FIELDS: 1 // 索引列数=1，对于联合索引来讲该值为构成索引的列数
        PAGE_NO: 4 // 二级索引对应的根索引页号
          SPACE: 29 // 二级索引根索引页所在的表空间号
MERGE_THRESHOLD: 50
复制代码

这样Mysql便可以通过表空间号：页号来找到每个索引的根页（不是B+树的根），Mysql为了尽量避免磁盘的随机I/O，为表中的每个索引都分别分配了叶子节点段（索引段） 和 非叶子节点段（数据段），因为在B+树中我们总是先查询索引页再查询数据页，因此我们将索引页和数据页分成两块可以减少磁盘的随机I/O。

对于我们的select语句来讲，显然可以使用聚簇索引（id），因此我们需要找到聚簇索引对应的根页，并找到该索引对应的两个段，整个过程如下图所示：

对于当前select语句，首先通过Data Dictionary（数据字典-即我们上面描述的系统表的统称）找到主键-id索引的根页面，然后通过根页面的Segment Header这个数据结构找到当前索引的非叶子节点与叶子节点对应的段结构-INode ，INode中负责维护当前段数据区域中包含的区（64个页） 三种链表-FREE、FULL 、NOT_FULL，分别表示当前段未用区、已用完区、可用区。

PS: 因为对于数据量较小的小表来讲，可能存储并不需要一个完整的区，因此mysql中对于表的存储空间分配从表空间中逐个分配32页大小的碎片区，直到用完碎片区后才会以区为单位向段中分配存储空间。

总结：

至此，我们已经找到了存储blog表所在的段基址，接着我们便需要对段进行搜索直到我们等值查询中对应的那一条数据行。

select 行

向blog表中依次插入50条数据，那么表空间中便会形成如下所示的B+树结构(仅展示，事实中一个索引页可以存储很多个稀疏索引)：

如果我们是select xxx where id = 10，因此查询的宏观顺序即为：10<30 -> 左侧（加载对于索引页到内存（如果不在Page Buffer 中）） -> 1<10<16-> 加载叶子节点(Leaf Page)加载进入内存（如果不在Page Buffer 中） -> Page Directory(页目录) -> 8<10<Sup(Max)-> Slot3-> 从当前页面第三个组(slot槽)的尾端开始遍历通过行记录中的offset形成的伪链表，找到id=10的行记录并对外返回。

数据返回

*************************** 1. row ***************************
             id: 10
secondary_index: index_text10
   normal_field: normal_text10
1 row in set (0.28 sec)
复制代码