数据库——MySQL存储引擎

MySQL存储引擎有哪些

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不多的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。包括处理事务安全表的引擎和处理非事务安全表的引擎。

在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

 

Mysql5.7与Mysql8.0均支持的存储引擎：

InnoDB,MyISAM,Memory,Merge,Archive,Federate,CSV，BLACKHOLE

InnoDB

InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL5.5.5之后是默认的存储引擎，InnoDB 主要特性有：

• 支持事务
• 灾难恢复性好
• 为处理巨大数据量的最大性能设计
• 实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
• 支持外键完整性约束。存储表中的数据时，每张表的存储都按逐渐顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID,并以此作为主键。
• 被用在众多需要高性能的大型数据库站点上。

MyISAM

MyISAM 基于 ISAM 的存储引擎，并对其进行扩展。它是在Web、数据存储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。在 MySQL5.5.5 之前的版本中，MyISAM 是默认的存储引擎。MyISAM 主要特性有：

• 不支持事务
• 使用表级锁，并发性差
• 主机宕机后，MyISAM表易损坏，灾难恢复性不佳
• 可以配合锁，实现操作系统下的复制备份、迁移
• 只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
• 数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
• 可以把数据文件和索引文件放在不同目录

使用 MyISAM 引擎创建数据库，将产生3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm 文件存储表定义，数据文件的扩展名为 .MYD（MYData），索引文件的扩展名是 .MYI（MYIndex）。

Memory

MEMORY 存储引擎将表中的数据存储在内存中，为查询和引用其他表数据提供快速访问。每个memory表只实际对应一个磁盘文件，格式是.frm。默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。MEMORY 主要特性有：

• Memory类型的存储引擎主要用于内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果
• 使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈
• 只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪费空间
• 不支持TEXT、BLOB字段。

当有些查询需要使用到临时表（使用的也是MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁盘的MyISAM表，严重降低性能

• 由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错
• 服务器重启后数据会丢失，复制维护时需要小心
• MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围
• Hash索引优点：

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

• Hash索引缺点： 因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持。

存储引擎的选择

功能	InnoDB	MyISAM	Memory
存储限制	64TB	256TB	RAM
支持事务	Yes	No	No
支持全文索引	No	Yes	No
支持数索引	Yes	Yes	Yes
支持哈希索引	No	No	Yes
支持数据缓存	Yes	No	N/A
支持外键	Yes	No	No

1、如果要提供提交、回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB 是个很好的选择。

2、如果数据表主要用来插入和查询记录，则 MyISAM 引擎能提供较高的处理效率。

3、如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以将数据保存在内存中的 Memory 引擎。MySQL 中使用该引擎作为临时表，存放查询的中间结果。

4、如果只有 INSERT 和 SELECT 操作，可以选择 Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

MyISAM与InnoDB如何选择

1.两种存储引擎的大致区别表现在：

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

3）InnoDB支持外键，MyISAM不支持

4）从MySQL5.5.5以后，InnoDB是默认引擎

5）InnoDB不支持FULLTEXT类型的索引

6）InnoDB中不保存表的行数

如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。

7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

9）InnoDB支持行锁

（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'）

10）MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。

那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！myisam是有读锁和写锁(2个锁都是表级别锁)。

 

有人说MYISAM只能用于小型应用，其实这只是一种偏见。

如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，读写分离，而不是单纯地依赖存储引擎。

现在一般都是选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

https://blog.csdn.net/naughty610/article/details/7390976

https://blog.csdn.net/riemann_/article/details/89930883