面试题之数据库存储引擎
复习老师讲得
什么是存储引擎？
数据库针对数据采取的多种存取方式
如何查看常见存储引擎的方式？
show engines;
需要了解的四个存储引擎
MyISAM
MySQL5.5之前默认的存储引擎
存取数据的速度快 但是功能较少 安全性较低
InnoDB
MySQL5.5之后默认的存储引擎
支持事务、行锁、外键等操作 存取速度没有MyISAM快 但是安全性更高
Memory
基于内存存取数据 仅用于临时表数据存取
BlackHole
任何写入进去的数据都会立刻丢失
了解不同存储引擎底层文件个数
先建表
create database db2;
use db2;
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
1.innodb两个文件
.frm 表结构
.ibd 表数据(表索引)
2.myisam三个文件
.frm 表结构
.MYD 表数据
.MYI 表索引
3.memory一个文件
.frm 表结构
4.blackhole一个文件
.frm 表结构
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
ps:MySQL默认忽略大小写
补充了解
详细概念
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。可以使用 SQL Server Management Studio 管理数据库对象，使用 SQL Server Profiler 捕获服务器事件。
数据库引擎的类型
1、InnoDB引擎
这是MySQL 5.5或更高版本的默认存储引擎。它提供了事务安全(ACID兼容)表，支持外键引用完整性约束。它支持提交、回滚和紧急恢复功能来保护数据。它还支持行级锁定。当在多用户环境中使用时，它的“一致非锁定读取”提高了性能。它将数据存储在集群索引中，从而减少了基于主键的查询的I/O。
支持外键，保持数据的一致性和完整性
innoDB拥有自己独立的缓冲池，常用的数据和索引都在缓存中
InnoDB，是MySQL的数据库引擎之一，现为MySQL的默认存储引擎，为MySQL AB发布binary的标准之一。
InnoDB 由Innobase Oy公司所开发 ，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是 支持了ACID兼容的事务(Transaction)功能 ，类似于PostgreSQL。
InnoDB 物理文件结构为：
.frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等；
.ibd 文件或 .ibdata 文件： 这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件；
共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）。
2、ISAM引擎
ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。
索引顺序存取方法(ISAM, Indexed Sequential Access Method)最初是IBM公司发展起来的一个文件系统，可以连续地(按照他们进入的顺序)或者任意地(根据索引)记录任何访问。
每个索引定义了一次不同排列的记录。
3、MYISAM引擎
MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎(Mysql5.1前)。除了提供ISAM里所没有的索引和字段管理的大量功能，MYISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。
MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。
每个MyISAM在磁盘上存储成三个文件，每一个文件的名字均以表的名字开始，扩展名指出文件类型。
.frm 文件 存储 表定义;
.MYD (MYData)文件 存储 表的数据;
.MYI (MYIndex)文件 存储 表的索引。
4、MEMORY存储引擎
MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。
MEMORY主要特性有：
MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
MEMORY存储引擎执行HASH和BTREE缩影
可以在一个MEMORY表中有非唯一键值
MEMORY表使用一个固定的记录长度格式
MEMORY不支持BLOB或TEXT列
MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）
5、HEAP引擎
HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。
6、ARCHIVE引擎
这个引擎只允许插入和查询，不允许修改和删除。相当于拥有只读权限和写入权限，没有修改权限和删除权限。我突然想到这种东西有点类似于主从同步中的从库，但是又拥有写入权限，还是比较特殊的。
7、BERKLEYDB引擎
这个存储引擎典型被简称为BDB。对BDB存储引擎的支持包括在MySQL源码分发版里，在MySQL-Max二进制分发版里被激活。
BDB表可能有一个更大的崩溃幸存机会，并且也具有对事务COMMIT和ROLLBACK操作的能力。MySQL源码分发版和被补丁过可以与MySQL一起工作的BDB分发版一起提供。你不能使用一个未补丁过的BDB版本与MySQL一起工作。
InnoDB与MyISAM差别
InnoDB 支持事务，MyISAM 不支持事务。 这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 支持外键，而 MyISAM 不支持。 对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB 是聚簇索引，MyISAM 是非聚簇索引。
聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。
但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。
而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。
MyISAM一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
ACID
ACID是：
Atomic（原子性）
Consistency（一致性）
Isolation（隔离性）
Durability（持久性）
Atomic（原子性）：
指整个数据库事务是不可分割的工作单位。只有使数据库中所有的操作执行成功，才算整个事务成功；事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。
Consistency（一致性）：
指 数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。例如对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNTS表中Tom和Jack的存款总额为2000元。
Isolation（隔离性）：
指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。
Durability（持久性）：
指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。
