MySQL存储引擎

MySQL高级-存储引擎

目录

• MySQL高级-存储引擎
• InnoDB
  • 1.支持事务
  • 2.外键约束
  • 3.存储方式
• MyISAM
• MEMORY
• MERGE
• 存储引擎的选择

存储引擎是基于表的，同一个数据库不同的表可能有不同的存储引擎，在MySQL5.5版本之后，存储引擎默认是InnoDB，而在之前默认是MyISAM。

在MySQL中输入 show engines；可以查看所有支持的引擎。

show variables like '%storage_engine';可以查看默认的存储引擎。

在创建表的时候可以指定存储引擎 engine关键字

create table goods_innodb(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;

在创建之后通过 alter table可以将一个已经存在的表修改成其他的存储引擎

alter table goods_innodb engine = innodb;

---

InnoDB

---

​ 最重要也是最广泛的存储引擎，它被设计用来处理大量短期事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复的特性，使得它在非事务型存储的需求中也很少流行。除非有非常特别的原因需要使用其他引擎。

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

特点：支持事务、行级锁、外键

1.支持事务

在MySQL中执行语句会默认提交，因为是没有开启事务的，在使用InnoDB引擎开启事务之后，新增一条数据

 start TRANSACTION;//开启事务

 INSERT INTO city(city_name,country_id) VALUES('岳阳',4);

数据库还是查询不到，commit提交之后数据库才能查询到

2.外键约束

InnoDB引擎提供了四个约束外键的方式

RESTRICT、NO ACTION:是指限制在子表有关联记录的情况下， 父表不能更新；

CASCADE:表示父表在更新或者删除时，更新或者删除子表对应的记录；

SET NULL: 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。

在有外键的情况下，导入数据可以先关闭外键检查，再重新打开

SET FOREIGN_KEY_CHECKS = 0;//关闭外键检查
SET FOREIGN_KEY_CHECKS = 1;//打开外键检查

下面两张表，country是主表，city是父表，

 CREATE table country//新建国家表
 (
   countryID int NOT NULL AUTO_INCREMENT,//自动增长
    countryName varchar(100) NOT NULL,
    primary key(countryID)//主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE table city//新建城市表
(
    cityID int not NULL AUTO_INCREMENT,
		cityName VARCHAR(50) not NULL,
		countryID int ,
		key idx_fk_country_id(countryID),//索引
		PRIMARY key(cityID),
		CONSTRAINT `fk_city_country` FOREIGN KEY(countryID) REFERENCES //外键 
       //设置了子表删除的时候父表不能更新，父表在更新的时候也更新子表的记录
		country(countryID) ON DELETE RESTRICT ON UPDATE CASCADE  
)ENGINE=INNODB DEFAULT charset=utf8;

INSERT into country(countryName) values('中国'),('新西兰');
INSERT into  city(cityName,countryID) VALUES('长沙',1),('北京',1);

删除countryID=1的数据

DELETE FROM country WHERE countryID=1

更新父表的countryID为10

子表数据变更为

3.存储方式

MySQL数据库数据默认存储在 /var/lib/mysql/下，可以进去查看信息

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

• 使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引 保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是 多个文件。
• 使用多表空间存储，.frm后缀名文件存储的是表结构，.ibd存储的是索引和数据。
  

可以比较方便地进行单表备份和恢复操作，但是直接复 制.ibd 文件是不行的，因为没有共享表空间的数据字典信息，直接复制的.ibd 文件和.frm 文 件恢复时是不能被正确识别的，但可以通过以下命令：

ALTER TABLE 表名 DISCARD TABLESPACE;
ALTER TABLE 表名 IMPORT TABLESPACE; 

但是备份恢复默认只能恢复到之前的数据库，，如果要恢复到其他数据库需要通过 mysqldump 和 mysqlimport 来实现。

即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB 把内部数据词典和日志放在这个文件中。

---

MyISAM

​ 不支持事务也不支持外键，对事物的完整性没有要求或者以select、insert为主的应用基本都可以使用这个引擎。

特点：访问速度快

每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：

• .frm（存储表定义）
• .MYD（MYData，存储数据）
• .MYI （MYIndex，存储索引）

数据文件和索引文件可以放置在不同的目录，平均分布 IO，获得更快的速度。

---

MEMORY

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

在创建表的时候要在 CREATE TABLE语句中指定子句，可以用 max_heap_table_size来设置表的大小进行扩容，表的默认大小是16MB。子句的意思有点像子查询，从其他表取数据。

CREATE TABLE tab_memory ENGINE=MEMORY
(
 SELECT city_id,city,country_id FROM city GROUP BY city_id;
)

SET max_heap_table_size = 1024*1024*2;

创建索引的时候，可以指定使用 HASH 索引还是 BTREE 索引：

create index mem_hash USING HASH on tab_memory (city_id) ;

MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作 的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

---

MERGE

MARGE是一组MyISAM表的组合，这些表的结构必须完全相同，MERGE表本身没有数据，但是可以对该表进行查询，更新、删除。DROP删除MARGE表时不会对其他表数据造成影响。

使用MARGE创建表citys_all

create table city1(
	city1_id int  not NULL AUTO_INCREMENT,
	city_name varchar(50),
	primary key (city1_id)
)engine = myisam default charset=utf8;

create table city2(
	city2_id int not NULL AUTO_INCREMENT,
	city_name varchar(50),
	primary key (city2_id)
)engine = myisam default charset=utf8;

create table citys_all(
	city_id int ,
	city_name varchar(50),
	primary key (city_id)
)engine = merge union = (city1,city2) INSERT_METHOD=LAST default charset=utf8;

INSERT into city1(city_name) values('长沙'),('北京');
INSERT into city2(city_name) values('上海'),('深圳');

INSERT_METHOD 插入表可以有三个不同的值

• FIRST：使得插入操作在第一个表

• LAST：使得插入操作在最后一个表

• NO：不能对表进行插入操作

查询city1表

查询city2表

查询citys_all表

往citys_all中插入一条数据，因为创建该表时定义的是INSERT_METHOD=LAST，所以会在最后一张表city2中插入。

insert into citys_all values(3,'海口');

---

存储引擎的选择

下面是常用存储引擎的适用环境。

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