mysql之数据库引擎

一：引擎概述

1，为什么要合理选择数据库存储引擎？

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

2，定义。

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

3，存储引擎作用

（1）设计并创建数据库以保存系统所需的关系或XML文档。
（2）实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程序，还包括生成使用SQL Server工具和实用工具以使用数据的过程。
（3）为单位或客户部署实现的系统。
（4）提供日常管理支持以优化数据库的性能。

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 type = InnoDB;

5，查看修改成功？

方式一：

show table status from table_name;

方式二：

show create table table_name

方式三：使用数据库管理工具。

二：MySQL各大存储引擎

查看MySQL都支持什么数据库引擎。

mysql> show engines\G
*************************** 1. row ***************************Engine: MyISAMSupport: YESComment: MyISAM storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 2. row ***************************Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tables
Transactions: NOXA: NOSavepoints: NO
*************************** 3. row ***************************Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tables
Transactions: NOXA: NOSavepoints: NO
*************************** 4. row ***************************Engine: BLACKHOLESupport: YESComment: /dev/null storage engine (anything you write to it disappears)
Transactions: NOXA: NOSavepoints: NO
*************************** 5. row ***************************Engine: CSVSupport: YESComment: CSV storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 6. row ***************************Engine: PERFORMANCE_SCHEMASupport: YESComment: Performance Schema
Transactions: NOXA: NOSavepoints: NO
*************************** 7. row ***************************Engine: ARCHIVESupport: YESComment: Archive storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 8. row ***************************Engine: FEDERATEDSupport: NOComment: Federated MySQL storage engine
Transactions: NULLXA: NULLSavepoints: NULL
*************************** 9. row ***************************Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keys
Transactions: YESXA: YESSavepoints: YES
9 rows in set (0.00 sec)

1，MyIsam

MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。这种存储引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。

（1）不支持事务，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
（2）不支持外键。
（3）查询速度很快。如果数据库insert和update的操作比较多的话采用表锁效率低（建议使用innodb）且其存储了表的行数，在进行读取表的行数的时候，不需要进行全表扫描。
（4）对表进行加锁。

2，Mrg_MyIsam

Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。比如：我们可能会遇到这样的问题，同一种类的数据会根据数据的时间分为多个表，如果这时候进行查询的话，就会比较麻烦，Merge可以直接将多个表聚合成一个表统一查询，然后再删除Merge表（删除的是定义），原来的数据不会影响。

3，Memory

Memory采用的逻辑介质是内存，响应速度应该是很快的，但是当mysqld守护进程崩溃的时候数据会丢失，另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用（长度不固定的）。

使用Memory存储引擎情况：
（1）目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
（2）如果数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
（3）存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。
【注】 Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。

4. Blackhole

“黑洞”存储引擎，他会丢弃所有的插入的数据，服务器会记录下Blackhole表的日志，所以可以用于复制数据到备份数据库。看其他的一些资料说：可以用来充当dummy master,利用blackHole充当一个“dummy master”来减轻master的负载，对于master来说“dummy master” 还是一个slave的角色，还有充当日志服务器等等。

5. CSV

可以将scv文件作为MySql的表来使用，但是不支持索引。CSV引擎表所有的字段都必须为非空的，创建的表有两个一个是CSV文件和CSM文件。

6. Performance_Schema

MySQL5.5以后新增了一个存储引擎，就是Performance_Schema，他主要是用来收集数据库服务器的性能参数。MySQL用户不能创建存储该类型的表。

他提供了以下的功能：
1. 提供进程等待的详细信息，包括锁、互斥变量、文件信息。
2. 保存历史的事件汇总信息，为Mysql服务器的性能做出详细的判断。
3. 对于新增和删除监控时间点都非常容易，并可以随意的改变Mysql服务器的监控周期

需要在配置文件my.cnf中进行配置才能开启。

7. Archive

archive是归档的意思，仅仅支持插入和查询两种功能，在MySQL5.5以后支持索引功能，他拥有很好的压缩机制，使用zlib压缩库，在记录请求的时候实时的进行压缩，经常被用来作为仓库使用。适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度但是对查询的支持较差。
8. Federated

Federated存储引擎是访问MySQL服务器的一个代理，尽管该引擎看起来提供了一个很好的跨服务器的灵活性，但是经常带来问题，默认是禁用的。
9. InnoDB

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。是MySQL的默认引擎。适用于以下的场合：

1. 更新多的表，适合处理多重并发的更新请求。
2. 支持事务并且实现了SQL的四种隔离级别。
3. 可以从灾难中恢复（通过bin-log日志等）。
4. 外键约束。只有他支持外键。
5. 支持自动增加列属性auto_increment。

三：InnoDB和MyIsam使用和原理对比。

1，使用效果和区别展示。

（1）在一个普通数据库中创建两张分别以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;

效果如图：

（2）对比插入效率（百万级别）：虽然速度上MyISAM快，但是增删改是涉及事务安全的，所以用InnoDB相对好很多。

为了更好地对比，我们可以使用函数的方式或者存储过程的方式。博主采用存储过程。（存储过程在往后的章节会讲到）

//创建存储过程--MyIsam
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测试百万插入的时候，数据库炸了（笑哭）。最终只成功插入1W条。可见效率对比。当然innodb默认是开启事务的，如果我们把事务给停了，会快很多。

//停掉事务
set autocommit = 0;  
//调用存储过程
call ptestInndb; 
//重启事务
set autocommit = 1;

（3）对比更新：虽然速度上MyISAM快，但是增删改是涉及事务安全的，所以InnoDB相对好很多

//耗时3秒多
update testinnodb set name = 'fuzhu' where id>0 and id<10000;
//耗时0.171秒
update testmyisam set name = 'fuzhu' where id>0 and id<13525;

（4）查询对比。

1）查询总数。

select count(*) from testInnoDB;  select count(*) from testMyIsam;

这就是innodb查一万跟myisam查一百万的区别？？效果对比立刻出现。

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）存储大小：testMyIsam 存了一百万。testinnodb 存了两万。

2，效果对比

（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都会锁全表的

3，使用建议

以下两点必须使用 InnoDB：

（1）可靠性高或者要求事务处理，则使用InnoDB。这个是必须的。

（2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。

MyISAM的使用场景：

（1）做很多count的计算的。如一些日志，调查的业务表。

（2）插入修改不频繁，查询非常频繁的。

MySQL能够允许你在表这一层应用数据库引擎，所以你可以只对需要事务处理的表格来进行性能优化，而把不需要事务处理的表格交给更加轻便的MyISAM引擎。对于 MySQL而言，灵活性才是关键。

四：InnoDB和MyIsam引擎原理

1，MyIasm引擎索引解构。

MyISAM索引结构: MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

因此，过程为： MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，根据data域的值去读取相应数据记录。

2，InnoDB引擎的索引结构。

也是B+Treee索引结构。Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

上图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

而且，与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

因此，过程为：将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 13”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。