Mysql性能系列—引擎介绍及各引擎创建方法

最新推荐文章于 2022-05-04 16:08:56 发布

置顶 No_Bug_

最新推荐文章于 2022-05-04 16:08:56 发布

阅读量415

点赞数 1

分类专栏： Mysql 文章标签： mysql 引擎 mysql引擎 mysql性能优化

本文链接：https://blog.csdn.net/u013419838/article/details/100116705

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

什么是引擎？

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。通常一个项目会用到很多表，每个表都有自己的引擎，从5.5.5版本开始，MySQL的默认引擎就换成Innodb了，之前是Myisam。除非自己修改默认引擎，或者主动修改表的引擎。每个引擎都有自己的优点和缺点，我们要根据不同的业务场景设计不同的表结构和引擎，这样才能最大化的利用引擎的优势来提高系统的性能。

有哪些引擎？

想看看mysql有哪些引擎，可以直接输入命令查看show engines;
在这里插入图片描述
各引擎介绍以及使用场景说明

1.InnoDB

引擎介绍：
InnoDB是一种平衡高可靠性和高性能的通用存储引擎，在MySQL 5.6中，InnoDB是默认的MySQL存储引擎。除非配置了不同的默认存储引擎，否则CREATE TABLE不带ENGINE= 子句的语句会创建InnoDB表。

引擎主要特征：

支持事务，操作遵循 ACID模型，具有提交，回滚和崩溃恢复功能的事务来保护用户数据
行级锁定和Oracle风格的一致性读取可提高多用户并发性和性能
InnoDB表格将数据排列在磁盘上，根据主键优化查询。每个 InnoDB表都有一个称为聚簇索引的主键索引，用于组织数据以最小化的主键查找I / O
支持外键，使用外键，将检查插入，更新和删除，以确保它们不会导致不同表之间的不一致

创建语句：

CREATE TABLE `demo_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` int(1) DEFAULT '0' COMMENT '性别',
  `realname` varchar(10) DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB表';

ENGINE=InnoDB也可以不加，如果默认引擎没改，则默认创建的就是InnoDB引擎。

表文件：
.frm：保存表的元数据，包括表结构的定义等，该文件与数据库引擎无关
.ibd：保存表的数据和索引
在这里插入图片描述
适用场景：
绝大部分场景都可以用innodb引擎，毕竟是默认引擎。如果需要用到事务，或者外键的，比如金融系统，订单系统等这些对事务要去很高，就必须用这个引擎了。
innodb引擎行级锁，是目前锁中粒度最小的，支持高并发。如果表中数据频繁增删改查，建议使用innodb，其它引擎的锁粒度很大，并发性能不高。

2.MyISAM

引擎介绍：
MyISAM基于旧的（不再可用）ISAM存储引擎，但有许多有用的扩展。

引擎主要特征：

支持大文件的文件系统和操作系统支持大文件（最大63位文件长度）
每个MyISAM 表的最大索引数为64，每个索引的最大列数为16
表级锁定限制了读/写工作负载的性能，通常用于Web和数据仓库配置中的只读或大部分读取工作负载
表占用空间小，所有数字键值首先与高字节一起存储，以允许更好的索引压缩
将删除与更新和插入混合时，动态大小的行的碎片要小得多。这是通过自动组合相邻的已删除块并通过扩展块（如果删除下一个块）来完成的
MyISAM支持并发插入：如果表在数据文件的中间没有空闲块，则可以INSERT在其他线程从表中读取的同时将新行放入其中。由于删除行或使用比其当前内容更多的数据更新动态长度行，可能会发生空闲块。当所有空闲块用完（填写）时，将来的插入将再次并发
可以将数据文件和索引文件放在不同的物理设备上的不同目录中，以获得更快的速度
不支持事务，不支持外键

创建语句：

CREATE TABLE `demo_myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` int(1) DEFAULT '0' COMMENT '性别',
  `realname` varchar(10) DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MyISAM表';

表文件：
.frm：保存表的元数据，包括表结构的定义等
.MYD：保存表的数据
.MYI：保存表的索引
在这里插入图片描述
适用场景：
Myisam引擎适用于读/写模式的业务，比如日志，统计，定时服务等。查询和插入的方式居多，因为是表锁定，所以并发支持不高。表锁定的方式虽然粒度很大，但是不会出现死锁问题，而且开销比行锁要小，加锁快。如果需要用到事务的处理，或者频繁的更新，删除数据，对数据的并发要求高，还是使用innodb引擎比较好。

3.MEMORY

引擎介绍：
MEMORY存储引擎（以前称为 HEAP）创建具有存储在存储器中的内容的专用的表(内存存储)。由于数据易受崩溃，硬件问题或断电影响，因此只能将这些表用作临时工作区或从其他表中提取数据的只读缓存。

引擎主要特征：

涉及临时，非关键数据（如会话管理或缓存）的操作。当MySQL服务器暂停或重新启动时，MEMORY表中的数据将丢失
内存存储，可实现快速访问和低延迟。数据卷可以完全适合内存，而不会导致操作系统更换虚拟内存页
MEMORY表空间以小块分配，表对插入使用100％动态哈希，不需要溢出区域或额外的密钥空间。免费列表不需要额外的空间，已删除的行将放入链接列表中，并在向表中插入新数据时重复使用。 MEMORY表也没有通常与散列表中的删除和插入相关的问题
MEMORY表使用固定长度的行存储格式，可变长度类型，例如 VARCHAR使用固定长度存储
只读或主要读取数据访问模式（有限更新）

创建语句：

CREATE TABLE `demo_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` int(1) DEFAULT '0' COMMENT '性别',
  `realname` varchar(10) DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT='MEMORY表';

表文件：
.frm：保存表的元数据，包括表结构的定义等
在这里插入图片描述
适用场景：
MEMORY引擎用到的场景很少，由于数据是存在内存中的，插入的数据随着mysql重启就会消失。除非特地的去做持久化功能，磁盘上只保留了表的结构数据。虽然数据存在内存中，延迟低，访问快，但是性能方面还是达不到Redis的级别，而且内存还有限制的(默认16MB)。所以可以用在临时存储上，数据就算丢失也没啥问题。

4.FEDERATED

引擎介绍：
FEDERATED可以实现远程访问Mysql数据库，使用该引擎的表本地只存储表的结构信息，数据都放在远程数据库上，查询时通过建表时指定的连接符去获取远程库的数据返回到本地。远程的那张表的引擎要是mysql支持的引擎，比如myisam，innodb等。实际上相当于一个桥梁，一个视图的概念，默认这个引擎是不开启的。
在这里插入图片描述
如果想用这个引擎，需要修改配置文件，并重启服务器。

引擎主要特征：

远程服务器必须是MySQL服务器
一个FEDERATED表可以指向另一个表，但是必须注意不要创建循环
在尝试通过表访问表之前，FEDERATED表指向的远程表必须存在FEDERATED
FEDERATED表可以复制到其他从属服务器，但必须确保从服务器能够使用CONNECTION字符串（或mysql.servers表中的行）中定义的用户/密码组合来连接到远程服务器
DROP TABLE对FEDERATED表发出的任何语句只会删除本地表，而不会删除远程表
FEDERATED 表不适用于查询缓存
FEDERATED表不支持用户定义的分区
FEDERATED连接语句中密码不能有@符号，When using a CONNECTION string, you cannot use an ‘@’ character in the password

这里就不介绍FEDERATED的建表语句和文件了，因为东西比较多，而且还是比较复杂的，单独写文章去介绍这个引擎

适用场景：
FEDERATED引擎可以说是mysql跨实例访问的一种方法，只要把表建好，映射关系建好，就可以实现远程访问另外一台服务器上的mysql表数据。但是这个过程还是比较复杂的，而且mysql默认都没有开启这个引擎。大部分需要用到跨服务器访问的都会使用多数据源去操作，不会用这个引擎去干同步这事，当然既然存在，肯定有理由。如果系统未做多数据源，连的是A服务器mysql，现在需要向B服务器mysql插入数据。那么就可以使用FEDERATED引擎，在A服务器中建个表和B服务器上的一样，连接语句账户密码写好，就可以实现这个功能。

5.MRG_MYISAM

引擎介绍：
MRG_MYISAM引擎和MERGE引擎是一个引擎，只不过是叫法不一样，相同的MyISAM引擎表的集合可被用作一个表。“ 相同 ”表示所有表具有相同的列数据类型和索引信息，不能合并MyISAM以不同顺序列出列的表，相应列中没有完全相同的数据类型，或者索引的顺序不同。

引擎主要特征：

基础表和MERGE表必须具有相同的列数
基础表和MERGE表中的列顺序必须匹配
基础表必须至少具有与MERGE表一样多的索引。基础表可能有比MERGE表更多的索引，但不能少，且索引长度，类型，语言等都要相同或者相等

如果MERGE引擎由于基础表的问题而无法打开或使用表，则CHECK TABLE显示有关哪个表导致问题的信息

创建语句：

CREATE TABLE `demo_mgemyisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` int(1) DEFAULT '0' COMMENT '性别',
  `realname` varchar(10) DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=MRG_MYISAM UNION=(demo_myisam) DEFAULT CHARSET=utf8 COMMENT='MRG_MYISAM表';

表文件：
.frm：保存表的元数据，包括表结构的定义等
.MGE：保存子表(基础表)的名称
在这里插入图片描述
适用场景：
MRG_MYISAM引擎就是相当于myisam引擎的集合，相当于是一个交换机的作用，本身不存储数据，只存储关系。增删改查都是直接操作到基本表，适合于数据库分表，优点是最小改动代码，之前业务查这个表还是查这个表，不需要根据分表的条件去查不同的表。引擎自己会从不同基础表中查出数据合并返回，缺点是一旦主表建立，各个子表基本上都不能动，包括索引顺序，名称等。

6.BLACKHOLE

引擎介绍：
BLACKHOLE存储引擎形象的称为 “ 黑洞 ”，它接受数据，但它扔了出去，不存储，检索始终返回空结果。插入BLACKHOLE表中不会存储任何数据，但如果启用了基于语句的二进制日志记录，则会记录SQL语句并将其复制到从属服务器，这可用作转发器或过滤器机制。

引擎主要特征：

Blackhole引擎是一种无操作引擎，使用Blackhole执行的任何操作都不起作用，在考虑自动递增的主键列的行为时，应该记住这一点。引擎不会自动增加字段值，也不会保留自动增量字段状态。这对复制具有重要意义
BLACKHOLE存储引擎支持所有类型的索引
使用BLACKHOLE引擎可以实现主列过滤

创建语句：

CREATE TABLE `demo_blackhole` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` int(1) DEFAULT '0' COMMENT '性别',
  `realname` varchar(10) DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 COMMENT='BLACKHOLE表';

表文件：
.frm：保存表的元数据，包括表结构的定义等
在这里插入图片描述
适用场景：
BLACKHOLE使用的很少，正常系统不会用到这个索引。可以用来验证转储文件语法，或者通过使用BLACKHOLE启用和不启用二进制日志记录的性能比较来测量二进制日志记录的开销，还可用于查找与存储引擎本身无关的性能瓶颈。也可以用于作为分发服务器，缓解主服务器的压力，但实际去使用的时候，还是需要充分的了解原理，避免踩坑。

7.CSV

引擎介绍：
CSV使用逗号分隔值格式的文本文件来存储数据，创建CSV表时，服务器会在数据库目录中创建表格式文件。该文件以表名开头并具有.frm扩展名。存储引擎还会创建纯文本数据文件，其名称以表名开头并具有.CSV 扩展名。将数据存储到表中时，存储引擎会以逗号分隔值格式将其保存到数据文件中。

引擎主要特征：

CSV存储引擎不支持索引
使用CSV 存储引擎创建的所有表必须具有NOT NULL所有列的属性
使用CSV存储引擎的表不支持分区

创建语句：

CREATE TABLE `demo_csv` (
  `id` int(11) NOT NULL,
  `sex` int(1) DEFAULT '0' NOT NULL COMMENT '性别',
  `realname` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名'
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='CSV表';

表文件：
.frm：保存表的元数据，包括表结构的定义等
.CSM：保存表的状态和表中存在的行数
.CSV：保存表数据，可以直接打开，纯文本数据文件
在这里插入图片描述
适用场景：
可以用来存储表格类数据，这样其它程序直接读取就行了，不需要程序在处理。也可以将csv数据拷贝到mysql下，将结构建好，直接提供给其它服务使用。

8.ARCHIVE

引擎介绍：
ARCHIVE存储引擎产生大量未索引数据存储在一个非常小的专用表上，创建ARCHIVE表时，服务器会在数据库目录中创建表格式文件，该文件以表名开头并具有.frm 扩展名。存储引擎创建其他文件，所有文件的名称都以表名开头，数据文件的扩展名为 .ARZ。

引擎主要特征：

引擎支持 INSERT， REPLACE和 SELECT，而不是 DELETE或 UPDATE，只能新增和查询，不能删除修改
支持 AUTO_INCREMENTcolumn属性，该 AUTO_INCREMENT列可以具有唯一或非唯一索引，创建其它索引会失败
行在插入时被压缩，该ARCHIVE引擎使用zlib无损数据压缩
在检索时，行按需解压缩，没有行缓存
ARCHIVE引擎采用了行级锁，但是一个SELECT操作执行一个完整的表扫描，也就是需要进行全表扫描

创建语句：

CREATE TABLE `demo_archive` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` int(1) DEFAULT '0' COMMENT '性别',
  `realname` varchar(10) DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
)ENGINE=ARCHIVE DEFAULT CHARSET=utf8 COMMENT='ARCHIVE表';

表文件：
.frm：保存表的元数据，包括表结构的定义等
.ARZ：保存表的数据
在这里插入图片描述

适用场景：
ARCHIVE引擎由于只能新增和查询，且特点是数据会经过压缩，占用的空间会很小，所以适用于于一些历史数据的存储，归档数据存储。

9.PERFORMANCE_SCHEMA

引擎介绍：
PERFORMANCE_SCHEMA引擎是用来测量性能的，用户是不能创建这个引擎的表的。运行时检查服务器内部执行的方法，是使用 PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现的。

引擎主要特征：

性能模式监视服务器事件，通常，事件可以是函数调用，等待操作系统，SQL语句执行的阶段（例如解析或排序），或整个语句或语句组。事件集合提供对服务器和多个存储引擎的同步调用（例如互斥锁）文件和表I / O，表锁等信息的访问
性能模式事件与写入服务器的二进制日志（描述数据修改）和事件调度程序事件（这是一种存储程序）的事件不同
可以获得当前事件，以及事件历史和摘要
收集的事件存储在performance_schema数据库的表中，可以使用SELECT 其他表之类的语句查询这些表
性能模式中的表是内存表，不使用持久性磁盘存储，从服务器启动开始重新填充内容，并在服务器关闭时丢弃
即使性能模式在内部失败，服务器代码的执行也会正常进行
服务器监控连续且不显眼地发生，只需很少的开销，激活性能架构不会使服务器无法使用，也不会导致服务器行为发生任何变化

No_Bug_

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql性能系列—引擎介绍及各引擎创建方法

什么是引擎？MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。通常一个项目会用到很多表，每个表都有自己的引擎，从5.5.5版本开始，MySQL的默认引擎就换成Innodb了，之前是Myisam。除非自己修改默认引...
复制链接

扫一扫