MySQL常用存储引擎的特点和使用场景

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

MySQL作为当前最火热的开源分布式数据库，基本满足绝大多数关系型数据库的应用场景，比如对事务的要求，读写分离的要求，报表的要求。由于Mysql功能的强大并且是开源的，很多知名的互联网公司基于MYSQL开源特性，在MYSQL上进行封装，衍生出自己的产品，比如：腾讯的TiDB。
业务场景复制多样，对数据库的要求也不同，那Mysql是怎么样支持这些使用场景的呢，比如：要求查询速度快，更新、删除快，大数据的要求。下面就以存储引擎角度来分析Mysql对不同应用场景的支持。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、存储引擎怎么定义？

在创建表时，可以显示的指定数据库表的存储引擎：ENGINE=MyISAM

CREATE TABE TEST(
Id long not null ,
name varchar(20)
)ENGINE=MyISAM	 

查看当前数据库支持的存储引擎:

mysql> show engines ;

在存在的数据表中指定存储引擎：

mysql> alter table emp engine=innodb;

二、常见存储引擎分类

1.MyISAM

特征

1. 不支持事务和外键；
2. 只有表级锁；
3. 一个数据库表存在三个文件：a.表结构定义（.frm） b.表索引（.MYI ） c.表数据(.MYD)
4. 采用B+树的索引方式，所有叶子节点存储的是数据地址
   MyISAM存储引擎的数据表还分为以下三种类型：
   （1）、静态表（fixed）
   默认的存储格式，静态表中的字段都是非变长字段，每个记录都是固定的长度，当表不包含变量长度列(VARCHAR, BLOB, 或TEXT)时,使用这个格式。
   优点：存储迅速，出现故障容易恢复
   缺点：占用空间比动态表大，静态表在进行数据存储时会按照事先定义的列宽度补足空格，但在访问的时候会去掉这些空格
   （2）、动态表（dynamic）
   包含变长字段，例如varchar、、text、blob，如果一个MyISAM表包含任何可变长度的字段（varchar、blob、text），或者该表创建时用row_format=dynamic指定，则该表使用动态格式存储
   优点：占用空间小
   缺点：频繁的更新和删除操作会产生碎片，需要定期用optimize table语句或myisamchk -r命令来改善性能，并且在出现故障后较难恢复
   （3）、压缩表
   由myisampack工具创建，占据非常小的磁盘空间，因为每个记录都是被单独压缩的

2.InnoDB

Innodb是MySQL默认的存储引擎，与MyISAM相比，有如下明显的特征：

1. 主键自增：innoDB表的自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列，MyISAM表的自动增长列可以是组合索引的其他列
2. 完备的事务控制和外键约束。
3. 表定义会两个存储文件，a.表结构定义（.frm） b.表数据+表索引(.MYD)，即表数据和索引是在同一个文件中，索引也是使用B+树存储索引信息，B+树的叶子节点存储的是实际的索引数据，而非地址（区别于MyISAM）。

如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。**聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。MySQL的索引原理请参考

3.MEMORY

顾名思义，表数据存储在内存中，也就具有如下特点：

1. 表定义在一个文件.frm文件中，表数据在内存中存储。
2. 表索引是通过HASH实现
3. 数据读取速度快，但是不安全，数据库关闭后，数据也会丢失。
4. 适用内容变化不频繁的代码表，作为统计操作的中间结果表，便于利用它速率快的优势高效的对中间结果进分析。

总结

一般的数据库调优可能都停留在SQL层面的优化，通过了解MYSQL底层的存储引擎，可以根据不同的业务场景提供另外一条不一样的思路。本文只列出常见的几种存储引擎，如果想详细了解每种引擎可以参考MYSQL官网。例如针对查询插入比较多时，可以采用MyISAM存储引擎。针对更新删除比较多的表采用默认的InnoDB引擎。如果针对那些不会改变又会频繁访问的数据或者中间表数据可以采用MEMORY引擎。同样如果时存档需求还可以使用ACHIVE引擎。