# 前言
数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。**MySQL的核心就是存储引擎。**
开发中常用的数据引擎包括MyISMA、InnoDB、MEMORY、MERGE.
1.MyISMA为mysql默认的插件式存储引擎，如果应用是读取跟插入为主，只有很少的更新和删除，并且对事物的完整性、并发性要求不高。
2.InnoDB应用于事务处理应用支持外键，如果应用对完整性要求比较高，并发情况下要求数据的一致性，数据的操作包括CRUD，该引擎就非常适合。InnoDB存储除了有效减低由删除和更新导致的锁定，还保证了事务的完整提交和回滚。
3.MEMORY将所有的数据保存在内存中，在需要定位和其他类似的操作中可提高极快的访问速度；缺陷是对表的大小有限制，表太大无法缓存到内存，其实要保证表的可恢复性。
4.MERGE用于将一系列MyISMA引擎的表以结构相同的方式组合在一起，并可以对其进行引用。优点是在于突破单个MyISMA表大小的限制。
# 存储引擎的概述
## 为什么要合理选择数据库存储引擎：
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。
## 定义
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。
## 存储引擎作用
1）设计并创建数据库以保存系统所需的关系或XML文档。
2）实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程序，还包括生成使用SQL Server工具和实用工具以使用数据的过程。
3）为单位或客户部署实现的系统。
4）提供日常管理支持以优化数据库的性能。
## 如何修改数据库引擎
方式一：
修改配置文件my.ini
将mysql.ini另存为my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为InnoDB
方式二：
在建表的时候指定
```
create table mytbl(
id int primary key,
name varchar(50)
)type=MyISAM;
```
方式三：
建表后更改
```
alter table table_name engine = InnoDB;
```
## 怎么查看修改成功？
方式一：
```
show table status from database_name;
```
方式二：
```
show create table table_name
```
方式三：
使用数据库管理工具
# 分析使用MyIsam 和InnoDB
定义：（默认的存储引擎）
InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。
Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。
```
//这个就是select锁表的一种，不明确主键。增删改查都可能会导致锁全表，在以后我们会详细列出。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
```
适用场景：
1）经常更新的表，适合处理多重并发的更新请求。
2）支持事务。
3）可以从灾难中恢复（通过bin-log日志等）。
4）外键约束。只有他支持外键。
5）支持自动增加列属性auto_increment。
MySQL官方对InnoDB的讲解：
1）InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。
2）InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。
3）InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
4）InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
5） InnoDB被用来在众多需要高性能的大型数据库站点上产生。
补充：什么叫事务？简称ACID
A 事务的原子性(Atomicity)：指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成.
C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.
I 独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.
D 持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚.
# MyIsam
定义：
MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。
意味着：引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。
适用场景：
1）不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
2）不支持外键的表设计。
3）查询速度很快，如果数据库insert和update的操作比较多的话比较适用。
4）整天 对表进行加锁的场景。
5）MyISAM极度强调快速读取操作。
6）MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。
缺点：
就是不能在表损坏后恢复数据。（是不能主动恢复）
补充：ISAM索引方法–索引顺序存取方法
定义：
是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。
特性：
ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。
在设计之初就预想数据组织成有固定长度的记录，按顺序存储的。—ISAM是一种静态索引结构。
缺点：
1.它不 支持事务处理
2.也不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实 时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。
# InnoDB和MyIsam使用及其原理对比
## 在一个普通数据库中创建两张分别以MyIsam和InnoDB作为存储引擎的表。
```
create table testMyIsam(
id int unsigned primary key auto_increment,
name varchar(20) not null
)engine=myisam;
create table testInnoDB(
id int unsigned primary key auto_increment,
name varchar(20) not null
)engine=innodb;
```
## 对比插入效率（百万级插入）：（虽然速度上MyISAM快，但是增删改是涉及事务安全的，所以用InnoDB相对好很多）
```
//创建存储过程
delimiter $$
drop procedure if exists ptestmyisam;
create procedure ptestmyisam()
begin
declare pid int ;
set pid = 1000000;
while pid>0
do
insert into testmyisam(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$
//使用存储过程：
call ptestmyisam();
```
```
//创建存储过程（尽量把Innodb的数量级压低，不然，，卡在那里半天也不奇怪）
delimiter $$
drop procedure if exists ptestInndb;
create procedure ptestInndb()
begin
declare pid int ;
set pid = 1000000;
while pid>0
do
insert into testinnodb(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$
//使用存储过程：
call ptestInndb();
```
当然innodb默认是开启事务的，如果我们把事务给停了，会快很多。
```
//停掉事务
set autocommit = 0;
//调用存储过程
call ptestInndb;
//重启事务
set autocommit = 1;
```
## 对比更新：（虽然速度上MyISAM快，但是增删改是涉及事务安全的，所以InnoDB相对好很多）
```
update testinnodb set name = 'fuzhu' where id>0 and id<10000;
update testmyisam set name = 'fuzhu' where id>0 and id<13525;
```
## 查询对比：
1）查询总数目
```
select count(*) from testInnoDB;
select count(*) from testMyIsam;
```
2）查询无索引的列：
```
select * from testMyIsam where name > "fuzhu100" ;
select * from testInnoDB where name > "fuzhu100" ;
```
3）查询有索引的列：
```
select * from testMyIsam where id > 10 ;
select * from testinnodb where id > 10 ;
```
4）存储大小
# 效果对比总述：
1）事务。MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持已经外部键等高级数据库功能。
InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如updatetable set num=1 where name like “a%”
就是说在不确定的范围时，InnoDB还是会锁表的。
2）性能主题。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。
3）行数保存。InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。
4）索引存储。对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持
MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
5）服务器数据备份。InnoDB必须导出SQL来备份，LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
而且MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
**6）锁的支持。**MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的
# 使用建议：
以下两点必须使用 InnoDB：
1）可靠性高或者要求事务处理，则使用InnoDB。这个是必须的。
2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。
对比之下，MyISAM的使用场景：
1）做很多count的计算的。如一些日志，调查的业务表。
2）插入修改不频繁，查询非常频繁的。
MySQL能够允许你在表这一层应用数据库引擎，所以你可以只对需要事务处理的表格来进行性能优化，而把不需要事务处理的表格交给更加轻便的MyISAM引擎。对于 MySQL而言，灵活性才是关键。
