MySql数据库存储引擎

Mysql数据库存储引擎

MySql数据库存储引擎

简介

​ Mysql是关系型数据库，用于存储和组织信息的数据结构，不同表不同的结构，有的复杂有的简单，在实际开发中可能需要各种各样的表，不同的表意味着需要不同的数据结构和数据内容，数据处理上也存在不同的差异，对于Mysql提供了不同的存储引擎、索引技巧、锁定水平等功能，我们可以根据不同的需求来选择不同的存储引擎，存储引擎是数据库管理系统用来从数据库创建，读取和更新数据的软件模块。

​ Mysql支持的存储引擎有9种，但是常见的就那么几种，在Mysql 5.5之后默认支持的是InnoDB。

引擎查看

​ SHOW ENGINES

​

​ 不同的存储引擎有各自的特点，以下为常见的存储引擎的不同特点，为做出选择，首先考虑每个存储引擎的特点

​

存储引擎

1. InnoDB

   默认索引，InnoDB主要特点如下：

   1）可以通过自动增长列，方法是auto_increment。

   2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。

   3）使用的锁粒度为行级锁，可以支持更高的并发；

   4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。

   5）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

   6）配合一些热备工具可以支持在线热备份；

   7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

   8）主键索引采用聚集索引 主键索引采用聚集索引，辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问主键索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

   对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。

   场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

2. MyISAM

   使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

   1）frm文件：存储表的定义数据

   2） MYD文件：存放表具体记录的数据

   3）MYI文件：存储索引

   MyISAM支持的数据类型

   1）静态固定长度表

   这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。

   2）动态可变长表

   优点是节省空间，但是一旦出错恢复起来比较麻烦。

   3）压缩表

   上面说到支持数据压缩，说明肯定也支持这个格式。在数据文件发生错误时候，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。

   4）MyISAM的表锁有读锁和写锁：

   表共享读锁和表独占写锁。在对MyISAM表进行读操作时，不会阻塞其他用户对同一张表的读请求，但是会阻塞其他用户对表的写请求；对其进行写操作时会阻塞对同一表读操作和写操作。

   5）采用非聚集索引

   采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

   有一个重要的特点那就是不支持事务，但是这也意味着他的存储速度更快，如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎

   场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

3. Memory

   将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。

   1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；

   2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；

   3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；

   4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；

   5）默认使用hash索引。

   6）如果一个内部表很大，会转化为磁盘表。

4. MERGE

   将多个类似的MyISAM表分组为一个表，可以处理非事务性表，默认情况下包括这些表。

   MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

   场景：对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

5. ARCHIVE

   用于存储大量数据，不支持索引。

   Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

   场景：由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

6. CSV

   在文本文件中以逗号分隔值格式存储数据。

   使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。csv的编码转换需要格外注意。

   场景：这种引擎支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件，将其存放在MySQL服务器的数据目录中，服务器就能够马上读取相关的CSV文件。同样，如果写数据库到一个CSV表，外部程序也可以立刻读取它。在实现某种类型的日志记录时，CSV表作为一种数据交换格式，特别有用。

7. BLACKHOLE

   该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

   场景：如果配置一主多从的话，多个从服务器会在主服务器上分别开启自己相对应的线程，执行binlogdump命令而且多个此类进程并不是共享的。为了避免因多个从服务器同时请求同样的事件而导致主机资源耗尽，可以单独建立一个伪的从服务器或者叫分发服务器。

8. FEDERATED

   将数据存储在远程数据库中。

   该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。

   Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据，针对federated存储引擎表的查询会被发送到远程数据库的表上执行，本地是不存储任何数据的。

   缺点：

   1）对本地虚拟表的结构修改，并不会修改远程表的结构

   2）truncate 命令，会清除远程表数据

   3）drop命令只会删除虚拟表，并不会删除远程表

   4）不支持 alter table 命令

   5）select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题，但是按主键或索引列查询，则很快

   6）如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常

   7）类似 where name like “str%” limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为federated引擎会将所有满足条件的记录读取到本地，再进行 limit 处理。

   场景： dblink。

9. PERFORMANCE_SCHEMA

   该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。 MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

   场景： DBA能够较明细得了解性能降低可能是由于哪些瓶颈。

修改存储引擎

1. 通过命令直接修改表的存储引擎

   alter table mytest ENGINE = MyIsam;

   优点：

   1、操作简单；

   2、适用于任何存储引擎。

   缺点：

   1、耗时较长（Mysql会将数据从原表复制到一张新表中，在复制期间会消耗大量的I/O）。

   2、不安全（转换时原表会加上读锁，在变更繁忙的表上执行要特别小心。）

2. 导出和导入

   这个比较容易操作，直接把导出来的sql文件给改了，然后再导回去。

   缺点：不安全

3. 创建与查询（create和select）（推荐）

   create table new_table like old_table;

   alter table new_table engine=innodb;

   insert into new_table select * from old_table;

参考网站：https://baijiahao.baidu.com/s?id=1655327558614401593&wfr=spider&for=pc

​ https://blog.csdn.net/yjclsx/article/details/81911027