MySQL之常用存储引擎

存储引擎

• 描述表的存储方式

  • MySQL 中叫做 存储引擎
  • Oracle 叫做 存储方式

• MySQL 表的类型由存储引擎（storage engines）决定

  • 主要使用：MyISAM、innoDB、Memory等
  • 查看所有存储引擎：show engines
  • 默认引擎：InnoDB

类型

• 不同版本支持存储引擎不同

  • InnoDB：默认使用
  • MRG_MYISAM、MEMORY、BLACKHOLE、MyISAM、CSV、ARCHIVE、PERFORMANCE_SCHEMA、FEDERATED

• 分为两类

  • 事务安全型（transaction-safe）
    • InnoDB：只有这一个
  • 非安全事务型
    • 其他所有类型
    • 如：MySIAN 和 Memory

  特点	MyISAM	InnoDB	Memory	ARCHIVE
  批量插入速度	高	低	高	非常高
  事务安全		支持
  锁机制	表锁	行锁	表锁	行锁
  存储限制	无	64TB	有	无
  全文索引	支持
  B树索引	支持	支持	支持
  哈希索引		支持	支持
  集群索引		支持
  数据缓存		支持	支持
  索引缓存	支持	支持	支持
  数据压缩	支持			支持
  空间使用	低	高	N / A	非常低
  内存使用	低	高	中等	低
  支持外键		支持

MyISAM

• 特点

  1. 访问速度快，对事务完整性无要求
  2. 不支持事务和外键
  3. 支持表级锁

• 可转为压缩、只读表来节省空间

• 使用三个文件表示一张表

  • 格式文件 ：存储表结构的定义
    • mytable.frm
  • 数据文件：存储表中数据
    • mytable.MYD
  • 索引文件：存储表上索引
    • mytable.MYI
    • 仅保存记录所在页的指针，索引结构：B+ 树

• 灵活的 AUTO_INCREMENT 字段处理

InnoDB

• MySQL 5.1 开始成为默认引擎
• 主要特征
  1. 提供具有提交、回滚和崩溃恢复能力的事务安全
     • 默认隔离级别：可重复读，通过 MVCC 实现
  2. MySQL 服务器崩溃后提供自动恢复
     • 多版本 MVCC、行级锁
  3. 支持外键及引用完整性约束
     • 包括级联删除和更新
       • 降低了表的查询速度，增加了表之间的耦合度
     • 没有显式定义主键时默认为每一行生成一个 6 字节的 ROWID 作为主键
  4. 可以通过自动增长列：auto_increment
  5. 为处理巨大数据量的最大性能设计
  6. 存在缓冲管理
     • 通过缓冲池将索引和数据全部缓存，加快查询速度
  7. 被用在需要高性能的大型数据库站点
  8. 锁粒度为行级锁，支持更高级别并发
• 比 MyISAN 的处理效率差一些
  • 且会占用更多的磁盘空间以保留数据和索引
• InnoDB 文件在数据库目录中以 .frm 格式文件表示
  • MySQL8.0之后将源数据都存储到了表空间中，所以也就不存在frm文件
• InnoDB 表空间 tablespace 用于存储表的内容
• 提供一组用来记录事务性活动的日志文件

Memory

以前被称为 HEAP 引擎

1. 数据、索引 存储在内存中
   • 访问速度非常快，默认使用 hash 索引
   • mysql 服务关闭表中数据就会丢失
     • 表结构还在
2. 表级锁机制
3. 不支持 text、blob 等数据类型
   • 只支持定长字符串，varchar 会自动存储为 char 类型
4. 用内存中的内容来创建表，每个 memory 表只实际对应一个磁盘文件
   • 数据库目录中每个表均以 .frm 格式文件表示
5. 在临时表中使用
   • 不需要存放在磁盘且需要经常操作的
     • 例如：用户状态表
   • 若临时表中有 blob、text 类型字段则临时表会转为 MyISAM 类型
     • 性能急剧降低

修改

-- 修该存储引擎，或在创建表时指定
alter table `table_name` engine = 存储引擎 

选择

• 不需要事务，只处理基本的CRUD操作
  • MyISAN 速度快
• 需要支持事务：InnoDB
• 不需要存储在磁盘且操作频繁；例如：用户在线状态等
  • Memory，存储在内存中
  • 没有磁盘 IO 的等待，速度极快
  • 但由于存储在内存中，数据在服务器关闭后都会丢失