本文共 5612 字，大约阅读时间需要 18 分钟。

查看MySQL支持的存储引擎

mysql> mysql> show engines; ±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+ | Engine | Support | Comment | Transactions | XA | Savepoints | ±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+ | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

PerconaDB:默认是XtraDB

MariaDB:默认是InnoDB

第三方的存储引擎:

RocksDB MyRocks TokuDB 压缩比较高,数据的插入性能高.其他功能和InnoDB没差.

主要看一看常用的两种存储引擎MyISAM和InnoDB

1 INnodb引擎特点 MySQL5.5.5之后默认引擎

1 支持事务

2 行级锁定 更新时一般是锁当前行 通过索引实现 全表扫描依然是表锁 3 读写阻塞与事务隔离级别相关 4 具有非常高效的缓存特性能缓存索引也能缓存数据 5 整个表和主键以cluster 方式存储 6 所有secondary index都会保存主键信息 7 支持分区，表空间，类似oracle数据库 8 支持外键约束

数据库事务介绍

简单的说，事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，执行时要么全成功要么全失败。 事务的四大特性（ACID） 1 原子性（Atomicity） 事务是一个不可分割的单位，事务中的所有SQL等操作要么都发生，要么都不发生 2 一致性（Consistency） 事务发生前和发生后，数据的完整性必须一致 3 隔离性（Isolation） 当并发访问数据库时，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的 备份参数–single-transaction就是利用这点 4 持久性（Durability） 一个事务一旦被提交，他对数据库中的数据改变就是永久性的，如果出了错误，事务也不允许撤销，只能通过补偿性事务

innodb适合应用场景

1 需要事务支持 2 行级锁定对高并发有很好的适应能力，但需要确保查寻时通过索引完成 3 数据更新交为频繁的场景 4 数据一致性要求高的场景 5 硬件设备内存较大，可以利用较好的缓存能力提高内存使用率尽可能减少磁盘io

InnoDB存储引擎物理存储结构

最直观的存储方式(/data/mysql/data)

ibdata1：系统数据字典信息(统计信息)，UNDO(回滚)表空间等数据

ib_logfile0 ~ ib_logfile1: REDO日志文件，事务日志文件。 ibtmp1： 临时表空间磁盘位置，存储临时表 frm：存储表的列信息(表结构) ibd：表的数据行和索引 建议：每表一个独立的表空间文件

innodb独立表空间参数(5.5需要修改,之后版本不需要添加)

vim /etc/my.cnf innodb_file_per_table = 1 innodb_data_home_dir = /data/xxx

表空间数据问题

ibdata1 : 整个库的统计信息+Undo ***** ibd : 数据行和索引

共享表空间(ibdata1~N)

5.5 版本的默认模式,5.6中转换为了独立表空间 需要将所有数据存储到同一个表空间中 ，管理比较混乱 5.5版本出现的管理模式，也是默认的管理模式。 5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。 5.7 版本,临时表被独立出来了 8.0版本,undo也被独立出去了

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)

[(none)]>select @@innodb_data_file_path; [(none)]>show variables like ‘%extend%’; innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend innodb_autoextend_increment=64

例如: *****

mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

独立表空间 *****

从5.6，默认表空间不再使用共享表空间，替换为独立表空间。 主要存储的是用户数据 存储特点为：一个表一个ibd文件，存储数据行和索引信息

最终结论：

一张InnoDB表= frm+idb+ibdata1

MySQL的存储引擎日志：

Redo Log: ib_logfile0 ib_logfile1，重做日志 Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志

临时表:ibtmp1，在做join union操作产生临时数据，用完就自动清理

查看innodb事务状态

mysql> show variables like ‘%autocom%’; ±--------------±------+ | Variable_name | Value | ±--------------±------+ | autocommit | ON | mysql> set global autocommit=OFF; 关闭事务自动提交 关闭后所写入的语句不会自动保存到硬盘，需要commit写入

2 Myisam引擎特点

1 不支持事务（事务是一组逻辑上的sql操作，组成这组操作的各个单元，要么全成功，要么失败） 2 表锁定（更新时锁整个表） 3 读写互相阻塞 不仅会在写入的时候阻塞读取，还会在读取的时候阻塞写入，但读本身并不阻塞另外的读 4 只会缓存索引 可以通过key_buffer_size缓存索引，以大大提高访问性能，较少磁盘IO，但这个缓存区只会缓存索引，不会缓存数据 5 读取速度快，占用资源相对少 6 不支持外键约束，但支持全文索引 （连表查寻就是外键）

Myisam适合的应用场景

1 不需要事务支持的业务 一般为读数据比较多的网站 2 并发相对低的业务 3 数据修改相对少的业务（阻塞问题） 4 以读为主的业务 5 对数据一致性要求不是非常高的业务

MyISAM数据存储文件为3个文件

.MYD 数据存储文件 .MYI 索引存储文件 .frm 记录表结构

3 CSV 引擎

数据以文本方式存储在文件中 .csv文件存储表内容 .csm文件存储表的元数据如表的状态和数据量 .frm文件存储表结构信息，不论哪种存储引擎都有这个文件

特点

1.以CSV格式进行数据存储 2.所有列必须都是不能为NULL的 3.不支持索引，不适合大表 4.可以对数据文件直接编辑

适用场景

适合作为数据交换的中间表，其他应用把电子表格生成为csv文件供数据库读取，或把数据写到csv供其它应用读取

4 Archive 引擎

文件系统存储特点 以zlib对表数据进行压缩，磁盘I/O更少 数据存储在ARZ为后缀的文件中

只支持insert和select操作

只支持在自增ID列上加索引

使用场景

日志和数据采集类应用

5 Memory(HEAP) 引擎

数据保存在内存中 若数据库服务器重启，则数据丢失，但表结构不会丢失，因为表结构为frm文件，存在磁盘

功能特点

1.支持Hash索引和BTree索引，默认为Hash，innodb和myisam默认为BTree 2.所有字段都为固定长度，例：varchar（10） = char（10） 3.不支持blob和text等大字段 4.使用表级锁 5.表的最大大小由max_heap_table_size决定，默认16M，对已经存在的表无效

使用场景

1.那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。 2.目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。 3. 数据是临时的，而且必须立即可用得到，那么就可以放在内存中。 4.存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。

6 Federated 引擎

提供了远程访问MySQL服务器上表的方法 本地不存储数据，数据全部放到远程服务器上 本地需要保存表结构和远程服务器的连接信息

使用场景

偶尔的统计分析及手工查询

7 Blackhole（黑洞引擎）

任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空。 他会丢弃所有的插入的数据，服务器会记录下Blackhole表的日志，所以可以用于复制数据到备份数据库。

使用场景：

1）验证dump file语法的正确性 2）以使用blackhole引擎来检测binlog功能所需要的额外负载 3）充当日志服务器

8 Mrg_Myisam 引擎（分表的一种方式–水平分表）

定义： 是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。 也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。 比如：我们可能会遇到这样的问题，同一种类的数据会根据数据的时间分为多个表，如果这时候进行查询的话，就会比较麻烦，Merge可以直接将多个表聚合成一个表统一查询，然后再删除Merge表（删除的是定义），原来的数据不会影响。

使用场景：

没有用过，希望大神补充

9 PERFORMANCE_SCHEMA 引擎

Performance Schema 中的表是内存表，不使用磁盘存储，在 datadir 的 performance_schema 目录下，只有.frm表结构文件，没有数据文件。表内容在服务器启动时重新填充，并在服务器关闭时丢弃

使用场景

用于监视MySQL服务器

建表时指定存储引擎

mysql> create table test1(id int) engine=myisam; mysql> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE test1 ( id int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8

修改表存储引擎

mysql> alter table test1 engine=innodb; mysql> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE test1 ( id int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

转载地址：http://hkrpb.baihongyu.com/