MySQL存储引擎

---

MySQL 体系结构

连接层

客户端访问MySQL服务之前，做的第一件事就是建立TCP连接，经过三次握手之后，MySQL服务对TCP传输过来的账号密码做身份认证，权限获取，会从权限表中查询该用户所能操作的数据库以及数据表。

建立连接成功后，MySQL服务会专门分配一个线程为该用户服务，所以就还会存在一个线程池，去走后面的流程，每个连接从线程池中获取线程，省去了创建和销毁线程的开销。

服务层

服务层会完成大部分核心功能。

• SQL 接口
  • 接收用户的SQL指令，并返回用户需要查询的结构，如select ... from就是调用SQL接口
• 解析器
  • 在解析器中会对SQL进行语法分析、语义分析，将SQL分解成树形结构，并将这个结构传递到后续步骤，如果在这个构造语法树的过程中遇到错误，那么就会认为这个SQL是不合理的
  • 构建后语法树之后，MySQL还会对SQL查询进行语法上的优化
• 查询优化器
  • SQL语句在语法解析之后,查询之前会使用查询优化器确定SQL语句的执行路径，生成一个执行计划
  • 这个执行计划表明应该使用那些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询计划，并将查询结果返回
• 缓存
  • MySQL内部维持着一些Cache和Buffer，比如QueryCache用来缓存一条SELECT语句的执行结果，如能在缓存中查询到相同结果，就不需要进行查询解析，直接返回结果即可
  • 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
  • 这个查询缓存可以在不同客户端直接共享
  • 但其实这个缓存只使用于频繁查询的场景，如果数据频繁更新，那么缓存的命中率就会非常低，就会会频繁更新缓存，所以在MySQL8.0就将缓存这个组件给删除了

引擎层

存储引擎层，存储引擎真正负责了MySQL中数据的读取和提取，服务器通过API和存储引擎进行通信，不同的存储引擎具有不同的功能，可以更具不同场景来选择不同的存储引擎，数据库中的索引是在存储引擎层实现的

存储层

所有数据，数据库、表的定义，表的每一行的内容、索引、日志、都是存在文件系统上的，以 文件 的方式存在的，并完成与存储引擎的交互

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于数据库的，所以存储引擎也被称为表类型，可以在创建表的时候来指定存储引擎。

直接创建表会使用默认存储引擎

-- 创建表
mysql> create table my_innodb(id int,name varchar(32));
-- 查询建表语句
mysql> show create table my_innodb;
| my_innodb | CREATE TABLE `my_innodb` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

创建表并指定存储引擎为MySIAM

mysql> create table my_isam(id int,name varchar(32)) engine=myisam;

InnoDB

MySQL在大于等于5.5之后，默认采用InnoDB引擎。

特点：

• DML(增、删、改)操作遵循ACID模型，支持事务
• 支持行级锁，提高并发访问性能（和表级锁对比）
• 支持foreign key外键约束

文件，在Linux环境下的/var/lib/mysql存储了MySQL数据文件，

进入指定数据库目录就能查到到对于的文件，可以看到有很多的idb文件，每一个idb文件就对于一张表,如下图的balance表对应的就是blance.idb和blance.frm文件

• 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
• 表名.ibd 存储数据和索引

而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息

InnoDB逻辑存储结构

• Tablespece：表空间
• Segment：段
• Extent：区
• Page：页
• Row：行

1. 表空间：InnoDB存储引擎逻辑结构的最高层，.idb文件其实就是表空间文件，在表空间中可以包含多个Segment段
2. 段：表空间是由各个段组成的，常见的段数据段、索引段、回滚段等，InnoDB中对于段的管
   理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区
3. 区：区是由表空间的单元结构，每个区大小为1M，默认情况下，InnoDB存储引擎页大小为16K，也就是说一个区中一共有64个连续的页(Page)
4. 页：多个页组成一个区，页也是InnoDB存储引擎磁盘管理的最小单元，每个页默认大小为16KB，为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区域
5. 行：InnoDB引擎是面向行的，也就是数据是按行存放的，行指的就是MySQL表中的一行行数据

MyISAM

MyISAM是MySQL早期的默认存储引擎

特点：

• 不支持事务，也不支持外键
• 支持表锁，不支持行锁
• 访问速度快，因为MyISAM对事务完整性没有要求

和MyISAM相关的文件有：

• 表名.frm 存储表结构
• 表名.MYD 存储数据 (MYData)
• 表名.MYI 存储索引 (MYIndex)

应用场景：只读应用或者以读为主的业务

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

• 存放在内存
• Memory同时 支持哈希（HASH）索引 和 B+树索引 ,默认为hash索引
• Memory表至少比MyISAM表要 快一个数量级 。
• 由于存放在内存就有大小限制和数据丢失风险

对于Memory这个存储引擎只有.sid文件，用来存储表结构信息，因为他的数据是存储在内存中的

小结

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+Tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持(5.6之后)	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

InnoDB和MyISAM有什么区别？

1. 事务支持不同：InnoDB支持事务而MyISAM不支持事务
2. 锁的粒度不同：InnoDB支持表锁和行锁而MyISAM指支持表锁，所以InnoDB的并发性能更好
3. 对外键的支持不同：InnoDB支持外键而MyISAM不支持外键

存储引擎的选择

• InnoDB：如果对事务的完整性有较高要求，要保证并发条件下的数据一直性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择
• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

---