MySQL 体系结构与存储引擎

一、MySQL 体系结构

层级职责
连接层最上层是客户端和链接服务，完成连接处理、授权认证及相关的安全方案；服务器会为每个安全接入的客户端验证其操作权限
服务层完成大多数核心服务功能：SQL 接口、缓存的查询、SQL 的分析与优化、部分内置函数的执行；所有跨存储引擎的功能（过程、函数等）也在这一层实现
引擎层存储引擎真正负责 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎通信；不同引擎功能不同，可按需选择
存储层将数据存储在文件系统之上，并完成与存储引擎的交互

二、存储引擎简介

  • 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式；
  • 存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
-- 查看当前数据库支持的存储引擎
SHOW ENGINES;

-- 建表时指定存储引擎
CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    ...
    字段n 字段n类型 [COMMENT 字段n注释]
) ENGINE = INNODB [COMMENT 表注释];

-- 查看已有表的建表语句（可看到使用的存储引擎，默认 InnoDB）
SHOW CREATE TABLE account;

-- 指定其他引擎
CREATE TABLE my_myisam(id INT, name VARCHAR(10)) ENGINE = MyISAM;
CREATE TABLE my_memory(id INT, name VARCHAR(10)) ENGINE = Memory;

三、常见存储引擎特点

InnoDB

  • 介绍：兼顾高可靠性和高性能的通用存储引擎，MySQL 5.5 之后是默认存储引擎；
  • 特点：DML 操作遵循 ACID 模型，支持事务；行级锁，提高并发访问性能；支持外键约束，保证数据的完整性和正确性；
  • 文件：xxx.ibd（xxx 为表名），每张表对应一个表空间文件，存储该表的表结构、数据和索引；参数 innodb_file_per_table。

逻辑存储结构：

Tablespace（表空间）
└── Segment（段）
    └── Extent（区，1M）
        └── Page（页，16K）
            └── Row（行：Trx id / Roll pointer / col1 / col2 ...）

MyISAM

  • 介绍：MySQL 早期的默认存储引擎；
  • 特点：不支持事务、不支持外键；支持表锁，不支持行锁；访问速度快；
  • 文件：xxx.sdi 存储表结构信息、xxx.MYD 存储数据、xxx.MYI 存储索引。

Memory

  • 介绍：表数据存储在内存中，受硬件问题或断电影响，只能将这些表作为临时表或缓存使用；
  • 特点：内存存放、hash 索引（默认）；
  • 文件：xxx.sdi 存储表结构信息。

三者对比

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

四、存储引擎选择

引擎适用场景
InnoDB对事务的完整性有较高要求、并发条件下要求数据一致性，数据操作除了插入和查询之外还有较多的更新、删除 → 首选
MyISAM以读操作和插入操作为主，更新、删除很少，对事务完整性、并发性要求不高
Memory所有数据保存在内存中、访问速度快，通常用于临时表及缓存；缺陷是对表的大小有限制，太大的表无法缓存在内存中，且无法保障数据安全

相关

最近更新:
Contributors: liujw