MySQL常用存储引擎特性及适用场景分析

MySQL存储引擎

MySQL是一个关系型数据库管理系统（RDBMS），它提供了一套完整的数据库管理和查询功能，为了增加自身灵活性以适应不同应用场景其提供了插件式的存储引擎机制，本文将介绍一下常用存储引擎 InnoDB、MyISAM、Memory的各自特点和应用场景。

InnoDB 存储引擎

InnoDB存储引擎提供了强大的事务支持、高并发控制、数据一致性和持久性保证。

• InnoDB 存储引擎的特点：

  • 事务支持：提供具有提交、回滚和崩溃恢复能力的事务安全，能够保证数据的一致性和完整性。
  • ACID特性：遵循原子性、一致性、隔离性和持久性原则。
  • 并发控制：支持多版本并发控制算法(MVCC)、两段锁协议(2PL)等其他并发控制算法。
  • 支持自动增长列
  • 索引类型：使用B+树索引来加速数据的查找和排序，支持聚簇索引和辅助索引。聚簇索引决定了数据的物理存储顺序，使得范围查询和排序操作更加高效。
  • 提供行级锁、外键
  • 其在磁盘将表存储为两个文件，文件名与表名相同，扩展名不同：
    • .frm -> 存储表的定义(结构)
    • .ibd -> 存储表的数据和索引

• 适用场景：
  适用于大多数应用程序场景，并且在处理大量并发访问和复杂数据操作时表现出色。

MyISAM 存储引擎

MyISAM 优势是访问速度快（主要指读操作，主要是其为表级锁，读操作可以并发），对事务完整性没有要求，以SELECT、INSERT为主的应用基本都可以使用这个存储引擎创建表。

• MyISAM 存储引擎的特点：

  • 不支持事务
  • 提供表级锁，这意味着当一个会话对表执行写操作时，其他会话不能同时对该表进行写操作，从而可能导致并发性能下降，对于读操作，不会阻塞其他会话的读操作。当一个写操作（INSERT、UPDATE、DELETE）在MyISAM存储引擎上执行且未完成时，如果有新的读操作（SELECT）发生，读操作会被阻塞等待，直到写操作完成。这是因为MyISAM对整个表进行锁定而不是行级锁定，所以在写操作进行时，任何尝试读取整个表或部分表数据的操作都需要等待写操作释放锁。
  • 全文索引：是MySQL唯一支持全文索引的存储引擎。全文索引用于快速搜索和匹配文本数据。
  • 其在磁盘上将表存储为三个文件，文件名与表名相同，扩展名不同；
    • .frm -> 存储表的定义(结构)
    • .MYD -> 存储表的数据
    • .MYI -> 存储表的索引

• MyISAM 适用场景：
  需要频繁执行SELECT操作而很少进行写操作的应用。

Memory 存储引擎

Memory存储引擎以内存为基础，将数据完全存储在内存当中，而不是持久化到磁盘。简单来说，其牺牲了持久化和数据安全性来提供快速的读写操作和高性能。

• Memory 存储引擎的特点：

  • 数据存储：Memory存储引擎使用基于哈希表的数据结构，在内存当中快速存储和检索数据，故其访问表的速度非常快。它适用于需要快速读写操作而对数据持久化要求不高的场景。
  • 数据丢失：由于数据仅存储在内存当中，MySQL 服务器重启或者崩溃后一旦关闭，表中的数据就会丢失。其通常用于临时数据、会话管理、缓存和其他需要快速访问的临时数据集。
  • 数据类型支持：支持大多数基本的MySQL数据类型，如整数、浮点数、字符串，但不支持TEXT这种大型对象数据类型。
  • 索引类型：使用哈希索引（HASH索引索引）快速定位和检索数据。不适合范围查询或排序查询。
  • 其在磁盘也将表存储为一个.frm文件，文件名与表名相同，用于数据库启动时读取表的结构。

• Memory 存储引擎的适用场景：
  适用于需要快速读写操作且对数据持久化要求不高的场景，例如缓存、会话管理和临时数据处理。

各存储引擎的区别

种类	锁机制	B-树索引	哈希索引	外键	事务	索引缓存	数据缓存
InnoDB	行锁	支持	不支持	支持	支持	支持	支持
MyISAM	表锁	支持	不支持	不支持	不支持	支持	不支持
Memory	表锁	支持	支持	不支持	不支持	支持	支持