数据库学习之(2)存储引擎

存储引擎	事务	锁粒度	主要应用	忌用	B-tree	FULL-TEXT	Hash
MyISAM	no	支持并发插入的表级锁	SELECT，INSECT	读写操作频繁	yes	yes	no
MRG-MYISAM	no	支持并发插入的表级锁	分段归档，数据仓库	全局查找过多的场景	-	-	-
Innodb	yes	支持MVCC的行级锁	事务处理	无	yes	yes	no
Archive	no	行级锁	日志记录，只支持SELECT，INSECT	需要随机读取，更新，删除	no	no	no
Ndb cluster	no	行级锁	高可用性	大部分应用	-	-	-
NDB	yes	行级锁	-	-	no	no	yes

一.存储引擎概念介绍

　　存储引擎就是如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。数据库中的存储引擎是对使用了该引擎的表进行某种设置，数据库中的表设定了什么存储引擎，那么该表在数据存储方式、数据更新方式、数据查询性能以及是否支持索引等方面就会有不同的“效果”。通过选择不同的存储引擎，获得额外的速度或者功能，从而改善服务器的应用的整体功能。

　　使用用命令SHOW ENGINES可以查看mysql中支持的存储引擎。MySql5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CVS、BLACKHOLE、FEDERATED等，其中只有InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

　　创建新表时如果不指定存储引擎，系统将使用默认的存储引擎，MySql5.5之前默认的存储引擎是MyISAM，5.5之后改为了InnoDB。如果要修改默认的存储引擎，可以在参数文件中设置default-table-type。

二.存储引擎分类

ISAM：ISAM是一个定义明确且历经时间考验的数据表格管理方法，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，不支持事务处理，不能够容错：如果硬盘崩溃了，数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

MyISAM：MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。适合主要用来插入和查询记录的数据表。

HEAP：HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。

InnoDB：Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束的支持，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统。MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。而MyISAM保存了表的行数可以直接返回结果。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。例如updatetable set num=1 where name like “a%”。适合要提供提交、回滚**、崩溃恢复能力的事物安全（**ACID兼容）能力，并要求实现并发控制的数据库。

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

三.InnoDB引擎详细介绍

innodb是事务型存储引擎，适合对事务要求较高的场景中，但较适用于处理大量短期事务（即小事务，即少量语句，如十几个语句的处理）；

基于MVCC（Mutli Version Concurrency Control）支持高并发；支持四个隔离级别，默认级别为REPEATABLE-READ；间隙锁以防止幻读；

使用聚集索引（主键索引）；索引和内容放在一起。一般是有序的，聚集索引只能一个，其他索引都是非聚集的，MyISAM没有聚集索引；

支持”自适应Hash索引“；键值数据，即kv；

锁粒度：行级锁；间隙锁；关系型数据库写锁是独占的；

行级锁：innodb改一行只锁一行，行级别的力度。myISAM修改一行需要把整张表锁掉；

间隙锁：锁住行和行之间的间隙，如查询的是两行的信息时，有操作需要在这两行间插入新的记录，这里的间隙锁，就是锁住两行间的间隙，使得不能在两行间插入间隙；

innodb小结：innodb的特点

数据存储：表空间；

并发：MVCC，间隙锁，行级锁；

索引：聚集索引、辅助索引；

性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；

备份：支持热备；备份是重点知识。

查看存储引擎的状态信息，如InnoDB的状态

SHOW ENGINE INNODB STATUS;

四.MyISAM引擎详细介绍

支持全文索引（FULLTEXT index）、非聚集索引、压缩、空间函数（GIS）；这些是innodb不支持的特性；

不支持事务；

锁粒度：表级锁；

崩溃无法保证表安全恢复；

适用场景：只读（数据仓库）或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）；

文件：每个表有三个文件，存储于数据库目录中

tbl_name.frm：表格式定义，约束，数据结构

tbl_name.MYD：数据文件；

tbl_name.MYI：索引文件；

参考：https://blog.csdn.net/ningyuxuan123/article/details/86749595

http://www.uml.org.cn/sjjm/201804132.asp?artid=20627
https://www.cnblogs.com/zhangjinghe/p/7599988.html