41.MySQL引擎

1.基本介绍

1. 1. MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。
   2. MySQL 数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoBDB。
   3. 这六种又分为两类，一类是”事务安全型”(transaction-safe)，比如: InnoDB；其余都属于第二类，称为”非事务安全 型”(non-transaction-safe)[mysiam 和 memory]。

使用命令查看所有引擎：

SHOW ENGINES

解释：

引擎	支持状态	注释说明	事务支持	XA 事务支持	保存点支持
InnoDB	DEFAULT	支持事务、行级锁	YES	YES	YES
MRG_MYISAM	YES	相同 MyISAM 表的集合	NO	NO	NO
MEMORY	YES	基于哈希，存储在内存中（适用于临时数据）	NO	NO	NO
BLACKHOLE	YES	类 /dev/null 存储引擎（写入即消失）	NO	NO	NO
MYISAM	YES	MyISAM 存储引擎	NO	NO	NO
CSV	YES	CSV 存储引擎	NO	NO	NO
ARCHIVE	YES	归档存储引擎（压缩存储，适合日志）	NO	NO	NO
PERFORMANCE_SCHEMA	YES	性能模式（监控数据库性能）	NO	NO	NO
FEDERATED	NO	联邦 MySQL 存储引擎（跨库映射）	(NULL)	(NULL)	(NULL)

特点：

特点	Myisam	InnoDB	Memory	Archive
批量插入的速度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
存储限制	没有	64TB	有	没有
B树索引	支持	支持	支持
哈希索引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

2.MyISAM

MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。

特点：

1. 添加速度快

2. 不支持外键和事务

3. 支持表级锁

使用该引擎创建一个表：

CREATE TABLE t04(
  id INT,
  NAME VARCHAR(32)
)ENGINE MYISAM;

开启事务：

START TRANSACTION;

设置保存点：

SAVEPOINT t1;

插入一条数据：

INSERT INTO t04 VALUES(1,"tom");

查看一下：

SELECT * FROM t04;

试着回滚到t1保存点：

ROLLBACK TO t1;

出现警告：

1 queries executed, 1 success, 0 errors, 1 warnings

查询：rollback to t1

共 0 行受到影响， 1 个警告

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec

注意：要查看所有警告的完整列表，请启用 工具 -> 首选项 -> 常规 -> 在信息选项卡下显示警告

再次查看表t04:

SELECT * FROM t04;

发现该表根本没有回滚，myisam引擎的表是不支持事务的。

3.InnoDB

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

特点：

1. 支持事务

2. 支持外键

3. 支持行级锁

4.MEMORY

MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。

适用于经常操作的，不需要持久化的数据。

特点：

1. 数据存储在内存中（关闭MySQL服务后数据丢失，但是表结构还在）

2. 执行速度很快 (没有 IO 读写)

3. 默认支持索引 (hash 表)

使用该引擎创建一个表：

CREATE TABLE t05(
  id INT,
  NAME VARCHAR(32)
)ENGINE MEMORY;

插入几条数据：

INSERT INTO t05 VALUES(1,"tom");
INSERT INTO t05 VALUES(2,"jack");
INSERT INTO t05 VALUES(1,"lucy");

查看一下该表：

SELECT * FROM t05;

重启一下MySQL服务：终端输入

net stop mysql;
net start mysql;

再次查看一下该表：表中数据丢失

SELECT * FROM t05;

查看一下该表的结构：结构依然存在

DESC t05;

5.如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择,速度快。
2. 如果需要支持事务，选择InnoDB。
3. Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法 用户的在线状态().)

6.修改表的存储引擎

使用以下命令：

ALTER TABLE table_name ENGINE = engine_name;