MySQL进阶 - 存储引擎

概述

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。因此存储引擎决定了表在计算机中的存储方式。

在MySQL中可以通过SHOW ENGINES来查询存储引擎的相关信息。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

参数说明：

  • Engine: 存储引擎名称
  • Support: MySQL是否支持该类型引擎
  • Comment: 引擎的描述
  • Transactions: 是否支持事务处理
  • XA: 是否分布式交易处理的XA规范
  • Savepoints: 是否支持保存点，以便事务回滚到保存点。

基本建表操作

建表时指定存储引擎

CREATE TABLE 表名(
	字段1 字段1类型 [ COMMENT 字段1注释 ] ,
	......
	字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

示例演示:

详情
-- A. 查询建表语句 --- 默认存储引擎: InnoDB
show create table time_zone;

CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1827 DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zones' |

-- 我们可以看到，创建表时，即使我们没有指定存储引擎，数据库也会自动选择默认的存储引擎。

-- B. 创建表 my_myisam , 并指定MyISAM存储引擎

create table my_myisam(
	id int,
	name varchar(10)
) engine = MyISAM ;

-- C. 创建表 my_memory , 指定Memory存储引擎

create table my_memory(
	id int,
	name varchar(10)
) engine = Memory ;

三大存储引擎

MySQL数据库最常用的三大存储引擎为InnoDB, MyISAM, MEMORY，其中默认的存储引擎是InnoDB。下面将详细介绍这三种存储引擎的特点。

InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎。

特点

  • 提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全
  • 支持外键约束，外键所在的表为子表，外键依赖的表为父表，父表中被子表关联的字段必须为主键。保证数据的完整性和正确性。
  • 支持自动增长列AUTO_INCREMENT，值不为空且唯一，规定自增列必须为主键。

存储结构

InnoDB存储引擎中，创建的表的结构存储在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间。

文件：

  • xxx.sdi(frm): 存储表结构信息
  • xxx.ibd: 存储数据、索引

  • 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
  • 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
  • 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
  • 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
  • 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

优缺点及应用场景

  • 优点: 提供了良好的事务处理、崩溃修复能力和并发控制
  • 缺点: 读写效率较差，占用的数据空间相对较大。
  • 应用场景：对事务的完整性要求较高，且要求实现并发控制，选择InnoDB有很大优势。 如果需要频繁进行更新、删除操作，也可以选择InnoDB存储引擎，该类引擎可以实现事务的提交和回滚。

MyISAM

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

特点

  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快

存储结构

基于ISAM发展的，MyISAM存储引擎的表存储为3个文件，文件的名字与表名相同。

文件：

  • xxx.sdi(frm): 存储表结构信息
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储索引

优缺点及应用场景

MyISAM存储引擎的表支持3种不同的存储格式，包括静态型、动态型和压缩型。默认存储为静态型，字段固定长度；动态型包括变长字段，记录的长度不固定；压缩型需要使用myisampack工具创建，占用磁盘空间小。优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

  • 优点: 在于插入数据快，空间和内存使用比较低
  • 缺点: 不支持事务的完整性和并行性**。
  • 应用场景：如果表主要用于插入新记录和读出记录，或者对应用的完整性、并发性要求很低，则可以选择MyISAM存储引擎。

MEMORY

MEMORY存储引擎使用其存储在内存中的内容创建表，数据也存放在内存中。

特点

  • 基于MEMORY存储引擎的表的生命周期很短，一般是一次性的。
  • 服务器需要有足够多的内存维持MEMORY存储引擎的表的使用。
  • 表的大小受限制，大小主要取决于max_rows和max_heap_table_size参数，max_rows在创建表时指定，max_heap_table_size默认大小为16MB，可以按需扩大。处理速度快，但数据易丢失，生命周期短。
  • 默认使用哈希索引，速度比B型树索引快（随机查询快，范围查询不如B+树）。

存储结构

每个基于MEMORY存储引擎的表实际对应一个磁盘文件，文件的文件名与表名相同，类型为frm类型。该文件中只存储表结构，数据存储在内存中。

文件：

  • xxx.sdi(frm): 存储表结构信息

优缺点及应用场景

  • 优点: 处理速度非常快
  • 缺点: 数据易丢失，生命周期短。
  • 应用场景：如果需要很快的读写速度，对数据安全性要求较低可以选择MEMORY存储引擎。对表的大小有要求。

存储引擎特点

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

对应表在磁盘上法存储文件格式不同：

test1,test2,test3分别对应InnoDB，MyISAM，MEMORY。

存储引擎对比

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

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

支付适合使用 InnoDB 引擎；电商中的足迹和评论适合使用 MyISAM 引擎；缓存适合使用 Memory 引擎。

Last Updated:
Contributors: klc407073648