xiaoyh 的个人博客

一个只会敲代码的咸鱼

0%

面试 —— MySQL 的存储引擎

发表于 更新于

什么是存储引擎

数据库存储引擎是数据库底层软件组件，DBMS（数据库管理系统）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

比如，在研究临时数据时，就应该使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。

再比如，在某些场景需要支持事务的存储引擎，以确保事务处理不成功时数据能够回滚。

MySQL 的存储引擎

MySQL 中有很多存储引擎，但其中最常用的只有三个：MyISAM、MEMORY、InnoDB。

MyISAM

MyISAM 是基于 ISAM（Indexed Sequential Access Method，索引顺序访问方法）的存储引擎。在 MySQL 5.5 版本之前，MyISAM 是 MySQL 的默认存储引擎。

使用这个存储引擎，每个 MyISAM 在磁盘上存储成三个文件：

  1. frm 文件：存储表的定义数据
  2. MYD 文件：存放表具体记录的数据
  3. MYI 文件：存储索引（B+ 树）

该存储引擎主要特点为：

  1. 拥有较高的插入、查询速度
  2. 不支持事务；
  3. 不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用；
  4. 默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；
  5. 支持全文索引；
  6. 数据库所在主机如果宕机，MyISAM的数据文件容易损坏，且难恢复。

InnoDB

在MySQL 5.5 版本之后，MySQL的默认内置存储引擎变成了 InnoDB 。

对于 InnoDB 类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于 B+ 树的叶子节点上。

该存储引擎特点是：

  1. 支持事务。默认的事务隔离级别为可重复读，通过 MVCC（多版本并发控制）来实现；
  2. 支持外键；
  3. 使用的锁粒度为行级锁，支持更高的并发；
  4. 配合一些热备工具可以支持在线热备份；
  5. 灾难恢复性比较好。

MEMORY

MEMORY 用保存在内存中的数据来创建表，每个 memory 表对应一个 .frm 磁盘文件，其主要特点为：

  1. 将数据存在内存中，默认使用 HASH 索引，所以它的访问速度特别快
  2. 支持的数据类型有限制。比如：不支持 TEXT 和 BLOB 类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR 会被自动存储为 CHAR 类型；
  3. 支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为 MEMORY 存储引擎的瓶颈；
  4. 由于数据是存放在内存中，所以在服务器重启之后，所有数据都会丢失；

一些不常用的存储引擎

ARCHIVE

ARCHIVE 适合的场景有限，由于其支持压缩，故主要是用来做日志，流水等数据的归档，主要特点：

  1. 支持 Zlib 压缩，数据在插入表之前，会先被压缩；
  2. 仅支持 SELECT 和 INSERT 操作，存入的数据就只能查询，不能做修改和删除；
  3. 只支持自增键上的索引，不支持其他索引。

CSV

数据中转试用，主要特点：

  1. 数据格式为.csv格式的文本，可以直接编辑保存；
  2. 导入导出比较方便，可以将某个表中的数据直接导出为 csv，用Excel办公软件打开。

InnoDB 和 MyISAM 的对比

  1. InnoDB 为行级锁，MyISAM 为表级锁，因此 InnoDB 比 MyISAM 支持更高的并发。但 InnoDB 相对于 MyISAM 来说也更容易发生死锁，锁冲突的概率更大，而且上锁的开销也更大，因为需要为每一行加锁；
  2. 在备份容灾上，InnoDB 支持在线热备，有很成熟的在线热备解决方案；
  3. 查询性能上，MyISAM 的查询效率高于 InnoDB，因为 InnoDB 在查询过程中需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM 可以直接定位到数据所在的内存地址，直接找到数据；
  4. SELECT COUNT(*)语句，如果行数在千万级别以上，MyISAM 可以快速查出，而 InnoDB 查询的特别慢，因为 MyISAM 将行数单独存储了，而 InnoDB 需要逐行统计行数。所以如果使用 InnoDB 且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存；
  5. MyISAM 的表文件包括：.frm(表结构定义)，.MYI(索引)，.MYD(数据)，索引和数据分开放；而I nnoDB 的表文件为：.frm(表结构定义)和.ibd，索引和数据放一起；

如何选择合适的存储引擎

不同的存储引擎有各自的特点以适应不同的需求，可以根据以下的原则来选择 MySQL 存储引擎：

  • 如果使用场景需要事务支持，并要求实现并发控制，InnoDB 是一个很好的选择。
  • 如果主要是插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
  • 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中。
  • 对于记录日志信息、归档数据等场景，可以使用 Archive 引擎。