MySQL的存储引擎

1.存储引擎

1.1.概述

和大多数数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可以被称为表类型。

Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用响应引擎，或者编写存储引擎。

MySQL5.0支持的存储引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事物安全表。

在MySQL5.5之前默认使用的存储引擎是MyISAM，5.5之后默认使用的存储引擎是InnoDB，在创建表的时候如果不指定存储引擎的话就会使用数据库默认的存储引擎

查看MySQL支持的存储引擎

在终端/黑窗口中，我们可以使用 mysql -u用户名 -p密码命令来连接MySQL客户端。连接上客户端后我们可以使用show engines;命令来查看当前数据库支持的存储引擎

信息分析：

Engine：驱动名称

Support：是否支持

Comment：功能描述/注释

Transactions：事物支持

XA：分布式事务支持 <Mysql数据库分布式事务XA详解>

Savepoints：保存点支持（设置保存点，并和rollback结合使用，实现回滚到指定保存点）<Savepoints保存点相关文章>

查看数据库默认支持的存储引擎

通过show variables like '%storage_engine%';命令来查看数据库默认支持的存储引擎

1.2.各存储引擎特性

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	没有	没有	有
事务安全	支持
锁机制	行锁（适合高并发）	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持（5.6版本后）	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

1.3.存储引擎的特性

1.3.1.InnoDB存储引擎的特性

InnoDB存储引擎是MySQL5.5版本之后默认使用的存储引擎，InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事物安全。

但是对比MyISAM的存储引擎，InnoDB写的效率差一些，并且会占用更多的磁盘空间用来保留数据和索引。

InnoDB存储引擎不同于其他引擎的特点：

• 1.事物控制

  • 创建一个测试库，创建一张表用来演示InnoDB的事物控制

  -- 创建mysql_innodb_demo测试库
  CREATE DATABASE `mysql_innodb_demo` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  
  -- 创建用户表
  create table `user`(
      id   int primary key auto_increment comment '主键',
      name varchar(20) not null comment '名称'
  )comment '用户表' engine = INNODB default charset = utf8;
  
  -- 向表中插入一条数据
  insert into user(id,name) values(null,'张三');
  

  -- 查询表数据
  select * from user;
  

  

  此时查询表数据的时候表中是只有name=张三这一条数据的，现在我通过事务控制的方式再向表中添加一条name=李四的数据

  操作步骤：
  1、在进行插入操作前我先开启事物
  2、进行插入操作
  3、插入操作结束后回滚事物

  -- 操作前开启事物
  start transaction;
  
  -- 事物开启后，进行插入操作
  insert into user(id,name) values(null,'李四');
  

  在执行insert语句后，再查询一下表数据看李四这条数据是否有被插入到表中，结果如下图所示：

  注意：如果是终端操作的话需要重新开启一个窗口去连接mysql客户端选择库对表进行查询。如果直接在当前窗口进行查询的话是可以查询到刚刚插入的这条数据的，原因是这些操作都是属于同一个连接肯定是能查询到的。所以我们需要新建一个连接。

  

  如上图所示，在没有提交前数据是查询不到的。现在执行commit;语句进行事物提交。

  -- 提交事物
  commit;
  

  再次查询，结果如图所示：

  

  可见，已经查询到了刚刚插入的数据。这一系列的操作便是InooDB引擎的事物控制

• 外键约束

  MySQL支持外键的存储引擎只有InnoDB。外键是用来维护两表之间的关系，保证数据的一致性。在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会主动的创建对应的索引。

  -- 查看外键是非启用
  SELECT @@FOREIGN_KEY_CHECKS;
  -- 如果查询出来的状态值为1的话说明外键是启用状态的，如果是0未开启状态的话我们也可以通过如下这条SQL来开启外键
  SET FOREIGN_KEY_CHECKS=1;
  

  创建两张表，一张用来描述部门的表，另一张用来描述员工，部门、员工表之间的关系为一对多。两表进行关联，部门表的主键作为员工表中的一个字段，将该字段设置为外键。

  -- SQL
  create table department(
  		 id int primary key auto_increment COMMENT '主键',
  		 dep_name varchar(20) COMMENT '部门名称',
  		 dep_location varchar(20) COMMENT '部分地址'
  ) COMMENT '部门表';
  
  create table employee(
  		id int primary key auto_increment  COMMENT '主键', 
      	name varchar(20) COMMENT '员工名称',
  		age int COMMENT '年龄',
  		dep_id int COMMENT '外键', -- 外键对应主表的主键
  		#在创建表时添加外键约束添加外键约束
  		CONSTRAINT emp_dep_id FOREIGN KEY(dep_id) REFERENCES department(id) on update cascade on delete cascade
  ) COMMENT '员工表';
  
  INSERT INTO department values(null, '研发部','广州'),(null, '销售部', '深圳'); 
  INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1),('李四', 21, 1),('王五', 20, 1),('老王', 20, 2),('大王', 22, 2),('小王', 18, 2);
  

  外键额外参数说明：

  在创建外键索引时，可以指定删除、更新父表时，可以对字表进行相关操作。主要包括：restrict、cascade、set null、no action

  restrict和no action相同，是限制在字表有记录的情况下父表记录不能删除

  cascade表示父表在更新或者删除时，更新或者删除子表对应记录

  set null则表示父表在更新或者删除的时候，字表的对应字段被记录的字段被设置为null

  正对这些参数对照上面的SQL语句中我们使用的是如下两个限制条件：

  1、on update cascade：联动更新，更新主表时，如果字表有关联记录，则更新子表记录。

  2、on delete cascade：联动删除，删除主表数据时，如果有关联记录，则也会被删除删除。

  我们可以来验证一下，先查看一下两张表中的记录：

  

  可以看到部门表中的两条记录和员工表中的6条记录都存在着关联关系，那么我现在去删除主表中id为1的记录，对应的子表中有关联关系的数据都会被联动删除掉。如果现象符合我们的预期的话，也恰恰证实了外键生效了

  

  相对应的更新操作也是一样的，当主表中的关联字段更新的时候子表中对应的关联记录字段也会被更新

  

• 存储方式

  InnoDB存储表和索引有以下两种方式：

  • 使用共享表空间存储，这种方式出案件的表的结构存储在.frm文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件
  • 使用多表空间存储，这种方式创建表的表结构依然存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中

  在Linux系统中，MySQL的库表数据都是默认存储在/var/lib/mysql目录下的，如下图所示：
  

  如图所示蓝色字体的都是我创建的数据库，我随便查看一个。详细如下图所示：
  

  可以看到一些.frm和.ibd文件这些就是分别用来存储表和索引即表数据的。

6.3.2.MyISAM存储引擎的特性

MyISAM不支持事务，也不支持外键，其优势是访问速度快，对事物的完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎来建表。他有如下两个比较重要的特点：

• 不支持事务

  • 创建一张商品表并指定表的存储引擎来演示一下这个过程

  -- SQL
  create table goods(
  	id int primary key auto_increment comment '主键',
    name varchar(20) not null comment '商品名称'
  ) ENGINE=myisam DEFAULT CHARSET=utf8 comment '商品表';
  

  显然现在goods表中是没有数据的，现在我准备往goods中插入一条数据，我在插入前开启事务，插入数据后先不提交。再开启一个新的数据库连接去查询表看是否能查询到这条数据

  

  当我新建一个连接去查询的时候查询了这条未提交的数据，显然是事务没有生效

  

• 文件存储方式

  每个MyISAM再磁盘上存储成3个文件，其文件名都和表明相同，但拓展分别是：
  .frm：存储表定义

  .MYD：MYData 存储数据

  .MYI：MYIndex 存储索引

  表数据还是默认存储在/var/lib/mysql目录下，cd到库中查看。如下图所示：

  

6.3.3.MEMORY(内存)存储引擎的特性

Memory存储引擎将表的数据存放在内存中。每个Memory表实际对应一个磁盘文件，格式是.frm，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。Memory类型的表访问非常快，因为他的数据是存放在内存中的，并且默认使用Hash索引，但是服务一旦关闭，表中的数据就会丢失。

6.3.4.MERGE存储引擎的特性

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，Merge表本身并没有存储数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

对于Merge类型表的插入操作。是通过insert_method子句定义插入的表，可以有3个不同的值，使用first或last值使得插入操作被相应的作用在第一个或者最后一个表上，不定义这个子句或者定义no，表示不能对着干Merge表执行插入操作

可以对Merge表进行drop操作，但是这个操作只能删除Merge表的定义，对内部的表是没有任何影响的。

如下图所示：

准备两张表来演示一下使用Merge作为存储引擎的表的操作

-- SQL
create table goods_01(
	id int primary key auto_increment comment '主键',
  name varchar(20) not null comment '商品名称'
) ENGINE=myisam DEFAULT CHARSET=utf8  comment '商品表1';

create table goods_02(
	id int primary key auto_increment comment '主键',
  name varchar(20) not null comment '商品名称'
) ENGINE=myisam DEFAULT CHARSET=utf8  comment '商品表2';

CREATE TABLE `goods_all` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '商品名称',
  PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`goods_01`,`goods_02`) COMMENT='商品表';

insert into goods_01(id,name) values (null,'海飞丝洗发水'),(null,'霸王洗发水');
insert into goods_02(id,name) values (null,'屈成氏发水'),(null,'沙宣发水');

我们在创建goods_all（使用Merge存储引擎）表时关联了两使用MyISAM存储引擎的表goods_01、goods_02，现在查询一下goods_all表，如下图所示：

可以看到通过goods_all表查询到了goods_01``goods_02表中的内容，其实这也是符合了上面的理论。（Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，Merge表本身并没有存储数据。对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。）

6.4.存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引的使用环境总结：

• InnoDB：MySQL的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事物的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作除了查询和插入以外还包含很多更新，删除操作。那么InnoDB存储引擎除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统和财务系统等对数据准确性要求较高的系统，InnoDB是最合适的选择。
• MyISAM：如果应用是以读操作和插入操作为主，只有少量的更新和删除操作，并且对事务的完整性要求不是很高，那么选择这个存储引擎时非常合适的。
• MEMORY：将所有数据保存在内存（RAM）中，在需要快速定位记录和其他类似数据环境下，可以提高几块的访问。MEMORY缺陷就是对表大小有限制，太大的表无法缓存到内存中，其次是确保表数据可恢复，数据库异常终止后表中的数据可以回复。MEMORY表通常用于更新不频繁的小表，用以快速得到访问结果。
• MERGE：用于将一系列等同MyISAM表以逻辑方式组合在一起，并作为一个对象应用他们。MERGE表的有点在于可以突破单个MyISAM表的大小限制，并且通过不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问速度。这对于存储诸如数据仓储等VLDB环境十分合适。