【MySQL系列】- 常用MySQL存储引擎

【MySQL系列】- 常用MySQL存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引机制、锁定水平等功能。不同的存储引擎，都有其特定的功能及特定应用场景。

MySQL常用存储引擎有一下几类：

1、InnoDB存储引擎

InnoDB是事务性数据库首选引擎，InnoDB从Mysql5.5.5开始就成为数据默认的存储引擎，是MySQL8.0之后最重要，使用最广泛的存储引擎。支持事务安全（ACID），支持行锁定和外键。

InnoDB主要特性：

  • InnoDB支持事务：事务是数据库操作的基本单位，它是一组数据库操作的集合，要全部执行成功，要么全部不执行。InnoDB存储引擎支持ACID事务，保证了数据库的可靠性和一致性。

    在MySQL中，使用START TRANSCTION语句开启一个事务，使用COMMOT语句提交事务，使用ROLLBACK语句回滚事务。

  • 执行行级锁：行级锁是一种并发控制机制，它可以在不同事务之间实现数据的隔离性和一致性。InnoDB存储引擎支持行级锁，能够提高并发性能和可扩展性。

    在MySQL中，可以使用SELECT … FOR UPDATE语句来获取行级锁，保证在更新操作时，其他事务不能修改行数据。

  • 支持MVCC：MVCC是一种多版本并发控制机制，它可以在不同事务之间实现数据的隔离性和一致性。InnoDB存储引擎支持MVCC，能够保证不同事务之间的隔离性和一致性。

    在MySQL中，可以使用READ COMMITTEND隔离级别来支持MVCC机制。在READ COMMITTED隔离级别下，每个事务只能看到已提交的数据版本，不能看到其他事务未提交的数据版本。

  • 支持外键：在MySQL中，可以使用FOREIGN KEY约束来支持外键约束。在创建表时，可以使用FOREIGN KEY语句定义外键约束，确保在进行数据操作时，关联表之间的数据一致性。

  • 支持自动增长列：在MySQL中，可以使用AUTO_INCREMENT关键字定义自动增长列。在使用InnoDB存储引擎时，自动增长列实现方式时在表中创建一个名为AUTO_INCREMENT的隐藏列，该列用于存储下一个自动增长的值。

  • 支持崩溃恢复：InnoDB存储引擎支持崩溃恢复，能够保证数据库在崩溃后可以快速恢复到正常状态。当数据库发生崩溃时，InnoDB存储引擎会自动启动崩溃恢复过程，将未完成的事务进行回滚或者重做，从而保证数据的一致性。

2、MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。在 Web,数据仓储和其他应用环境使用的存储引擎较多。MyISAM拥有较高的插入、查询速度，但不支持事务和外键。

MyISAM主要特性：
  • 不支持事务；
  • 表级锁定：在MySQL中，MyISAM存储引擎中，当发生数据更新时，会锁定整个表，以防止其他会话对该表中数据的同时修改所致的混乱。这样做可以使得操作简单，但是会减少并发量。
  • 读写相互阻塞：在MyISAM类型表中，即不可以在向数据表中写入数据的同时另一个会话也向该表写入数据，也不允许其他的会话读取该表中的数据。只允许多个会话同时读取该数据表中的数据。
  • 只会缓存索引，不会缓存数据：缓存，是指数据库在访问磁盘数据时，将更多的数据读取进入内存，这样可以使得当访问这些数据时，直接从内存中读取而不是再次访问硬盘。MyISAM可以通过key_buffer_size缓存索引，以减少磁盘I/O,提升访问性能。MyISAM数据表并不会缓存数据。
  • 读取速度较快，占用资源较少；
  • 不支持外键约束；
  • 支持全文索引；
MyISAM适用场景：
  • 不需要事务支持的场景；
  • 读取操作比较多，写入操作较少；
  • 数据并发较低的场景；
  • 硬件条件比较差的场景；
  • 在配置数据库读写分离场景下，从库可以使用MyISAM索引；

3、Memory存储引擎

MySQL中Memory存储引擎是一个置于内存中的表，其采用的存储介质是内存。响应速度很快。但是当MySQL守护进程崩溃的时候数据会丢失。Memory存储引擎是存储的数据类型是长度不变的类型，blob/text类的数据类型不可用。

Memory存储引擎特性：

  • 存储瞬时非关键数据；

  • 内存存储，可实现快速访问和低延迟。

  • 只读或读取是主要数据访问模式：每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

  • 支持数据类型有限：不支持Text和Blob数据类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型。

  • 支持表级锁：在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈。

  • 查询中存在临时表且表中有BLOB、TEXT类型的字段，那么在这个临时表会自动转化为MyISAM类型的表。性能会急剧降低。

  • 默认索引使用Hash索引。

  • 内存表特别大时，自动转换为MyISAM类型实体表。

4、CSV存储引擎

CSV存储引擎是以CSV文件的方式存储数据。CSV是MySQL中相对比较简单并方便的存储引擎。它简单是因为其创建和使用简单。存储文件是CSV文件，可以直接对CSV文件进行修改。CSV存储引擎只有在MySQL5.0版本之后才支持。

CSV存储引擎特性：
  • 数据以文本方式存储：.CSV文件存储表内容，CSM文件存储表的元数据如表状态和数据量，.frm文件存储表结构信息。
  • 列不能为NULL；
  • 不支持索引；
  • 可对数据文件进行直接编辑；

CSV存储引擎的特性，决定CSV非常适合在数据库中担任中间交换表的角色。如对数据进行导入运维时，可以使用EXCEL转为CSV中直接复制覆盖表，并通过SQL语句对数据进行操作。