MySQL 学习笔记——存储引擎

MySQL 体系结构

MySQL 主要分为连接层、服务层、存储引擎层和文件系统层

连接层

连接层主要负责连接管理、授权认证、安全等。每个客户端连接都对应着服务器上的一个线程，服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过 SSL 证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限

服务层

服务层是 MySQL 的核心，负责查询解析、SQL 执行计划分析、SQL 执行计划优化、查询缓存、以及诸如存储过程、触发器、视图等跨存储引擎的功能

存储引擎层

存储引擎层负责 MySQL 中数据的存储与读取。 服务层中的查询执行引擎通过 API 与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL 采用插件式的存储引擎。MySQL 提供了许多存储引擎，每种存储引擎有不同的特点，我们可以根据不同的业务特点，选择最适合的存储引擎

文件系统层

文件系统层主要负责将数据库的数据存储在文件系统上，并完成与存储引擎的交互

---

存储引擎基本操作

创表时指定存储引擎

针对每个表可以选择不同的存储引擎，存储引擎是针对表的，故存储引擎也称表类型

create table 表名 (
	字段名 字段类型 [comment 字段注释],
    字段名 字段类型 [comment 字段注释]
) engine = 存储引擎名 [comment 表注释]

查询全部存储引擎信息

show engines

---

常见存储引擎特点

MySQL 在 5.5 版本之前默认存储引擎是 MyISAM，在此之后默认存储引擎是 InnoDB

InnoDB

• DML 操作遵循 ACID ，支持事务
• 行级锁，提高并发访问性能
• 支持外键约束
• 访问速度快

InnoDB 引擎下的每一张表，都会对应一个 xxx.ibd 的表空间文件，xxx 为表名，保存表结构、数据、索引信息，在 8.0 版本后，InnoDB 默认开启独立表空间（my.ini 中配置 innodb_file_per_table = 1）

show variables like 'innodb_file_per_table'

MyISAM

• 不支持事务
• 不支持外键
• 支持表级锁，不支持行级锁

Memory

Memory 的表数据存储在内存中，一般只能将这些表作为临时表或缓存使用

• 访问速度快
• 支持哈希索引（默认）
• 支持表级锁

---

存储引擎的选择

InnoDB

数据一致性要求很高、需要支持事务、数据读写及更新都比较频繁的业务场景

MyISAM

事务要求不高、并发要求不高、绝大多数操作为查询的业务场景，如今已逐渐被 NoSQL（MongoDB）代替

Memory

使用临时表、缓存的业务场景，如今已逐渐被 NoSQL（Redis）代替

---

InnoDB 与 MyISAM 的区别

• InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语句都默认封装成事务，自动提交
• InnoDB 支持外键，而 MyISAM 不支持
• InnoDB 支持表、行(默认)级锁，而 MyISAM 支持表级锁
• InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（因为 InnoDB 的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此 count 统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询）
• InnoDB 是聚集索引， MyISAM 是非聚集索引
• InnoDB 表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列 Row_id 来充当默认主键），而 Myisam 可以没有

InnoDB 为什么推荐使用自增 ID 作为主键？

自增 ID 可以保证每次插入时 B+ 索引是从右边扩展的，可以避免 B+ 树频繁合并和分裂（对比使用 UUID ），如果使用字符串主键和随机主键，会使得数据随机插入，效率较低