MySQL的存储引擎

MySQL的存储引擎

1.查看所有引擎

mysql> show engines\G;  # 查看mysql的存储引擎

将结果列成表格

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

InnoDB和MyISAM对比

Feature	MylSAM	InnoDB
Clustered indexes	No	Yes
B-tree indexes	Yes	Yes
Hash indexes	No	No
T-tree indexes	No	No
Full-text search indexes	Yes	Yes
Data caches	No	Yes
Foreign key support	No	Yes
Locking granularity	Table	Row
Transactions	No	Yes
Storage limits	256TB	64TB
Compressed data*	Yes	Yes

*(MylSAM的压缩表必须使用压缩行格式，这种表在MylSAM里是只读的)

2.InnoDB

事务型

InnoDB的结构

InnoDB的主要优势

• 遵循ACID原则(atomicity原子性，consistency一致性，isolation隔离性，durability持久性)，具有事务特性的能力：commit，rollback，crash-recovery
  • 仅InnoDB和NDB(Network DB clustered database engine)支持事务和MVCC
• 行级锁和Oracle风格的读一致性，提高多用户下的并发度和性能
  • 只有通过索引条件检索数据，InnoDB才使用行级锁，否则仍然使用表锁
  • 读一致性：query时使用snapshot快照，允许其他事务进行修改，之后再根据undo log调整数据
  • 默认的隔离级别是可重复读，即同一个事务中多次读取，数据相同
• 使用主键优化查询，主键索引是聚集索引(Clustered index，仅InnoDB支持)，使查询主键时的I/O最小化
  • 聚集索引是指整个表是按照这个索引来组织的，物理存储顺序与索引顺序相同，所以聚集索引字段的修改需要很大开销
• 支持外码约束

使用InnoDB的好处

• 崩溃后能很好地恢复
  • 未完成的事务将根据redo log的数据重做
  • 已提交但未写入的修改，将从doublewrite buffer重做
  • 系统闲时会purge buffer
• 维护一个内存中的buffer pool缓冲池，数据被访问时，表和索引数据会被缓存
• 对增删改的change buffering策略，如果被修改数据的页不在缓冲池中，则这个修改可以存在change buffer中，等相应页被放进缓冲池(发生对该页的访问)时，再写入修改，称为merge
• adaptive hash index，经常被访问的页会自动在内存建立一个哈希索引，适于=和IN的查询。buffer pool中会预留这种索引需要的内存空间。建立在已有的B树索引基础上，哈希索引可以是部分的，B树索引不需要全部缓存在缓冲池中
• 使用checksum校验和机制检测内存或硬盘的损坏
• InnoDB是为处理巨大数据量的最大性能设计

可以在一个查询中join混用InnoDB引擎的表和其他引擎的表

3.MyISAM

MyISAM
适用场景：read-only or read-mostly workloads in Web and data warehousing configurations(查询效率很高，适合大量读操作的场景)

• 将创建3个文件，一个.frm文件，一个.MYD(MYData)文件存数据，一个.MYI(MYIndex)文件存索引
• 数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• 所有数据值都按小字节(low byte first)存储，因此独立于操作系统(可移植性)。但没有明显降低速度，只是需要多处理一下对齐问题，况且获取列值所花的时间不是最主要的
• 所有数字键都按大字节(high byte first)存储，利于压缩
• BLOB和TEXT列可以创建索引
• 每一个character列可以使用不同的字符编码
• 会保存表的具体行数
• 使用B树索引，string索引会被压缩，当string是索引第一项时还会压缩前缀
• 支持真正的变长字段varchar
• 支持并发的insert

4.其他存储引擎

ARCHIVE

档案/归档

适用场景：作为仓库，存储大量的独立的作为历史记录的数据(插入速度快但查询支持较差）

• 不支持索引
• 没有存储大小限制(InnoDB是64TB)
• 能很好地压缩数据
• 使用行级锁
• 支持INSERT, REPLACE, SELECT, 不支持 DELETE, UPDATE

存储

使用zlib 无损数据压缩。数据insert后即被压缩，放在一个压缩缓冲区中，select操作会导致清空缓冲区，此时数据被真正存储。支持批处理insert。

读取

行会根据需要解压，不设缓冲。select会导致全表扫描。select是读一致性的。大量查询during insertion会影响压缩。使用REPAIR TABLE或OPTIMIZE TABLE能获取更好的压缩。

BLACKHOLE

黑洞

适用场景：

1.转发器（会保存SQL语句的日志，并且复制给slave servers）

2.过滤器（设置使用黑洞引擎的“dummy” slave进程，依据一定规则将master的日志进行过滤并在BLACKHOLE表写一个新的日志，再复制给slaves，这样只会导致很少的开销）

• 像黑洞一样接受数据但不存储
• 创建table A会生成一个A.frm表文件，没有其他文件
• 支持所有索引
• 会保存SQL语句的日志，并且复制给slave servers，适合做转发器或过滤器

Blackhole Engine and Auto Increment Columns

会导致错误，因为不论log文件是row-based还是statement-based，blackhole表不会存储自增列的数据，所以在slaves上insert时会出现重复的主码错误

Column Filtering

使用row-based replication时，如果slaves的表的字段比master少，那么过滤机制其实是在slaves上。如果缺失字段是私密的，不能给slaves获取的；或是有很多slaves，需要在发送数据前就把数据过滤掉以减少网络负载，就不适合这种方式。BLACKHOLE表就能实现在master上进行过滤。

MRG_MYISAM（MERGE）

组合merge多个MyIsam表

适用场景：Good for VLDB environments such as data warehousing

• 要求多个Mylsam表要有相同的列信息(包括顺序)和索引信息(包括索引的order)
• 这些信息不同不会影响表合并
  • 列名和索引名
  • 所有的备注comment
  • 表的选项，例如 AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS
• 创建merge表时会创建2个文件，一个是存数据的.frm文件，一个是.mrg文件(存储哪些表应当merge起来使用)
• merge表中的表可以存于不同的数据库中
• 支持merge表的增删改查，前提是必须拥有处理其中所有表的权限
• drop table只是删除了merge表，实际存储数据的表不会被删除
• 建表需要指定UNION=(list-of-tables)表明使用哪些表，以及INSERT_METHOD=LAST/FIRST表明在哪一个表中插入数据，否则无法执行insert操作
• merge表没有主键，因为不能强制实行唯一索引

一个安全性问题：

The use of MERGE tables entails the following security issue: If a user has access to MyISAM table t, that user can create a MERGE table m that accesses t. However, if the user's privileges on t are subsequently revoked, the user can continue to access t by doing so through m.

FEDERATED

联盟

适用场景：Very good for distributed or data mart environments

• 数据不存储在本地，而是在远程数据库，本地访问时会pull远程数据库的数据
• 远程数据库的表可以是任何存储引擎的表
• 本地表和远程表应有相同的定义
• 本地用.frm文件存储表定义，并且包含一个指向远程数据库的连接字符串
• 本地执行操作时，会发送给远程去执行，使用MySQL client API
• 远程表可以是一个FEDERATED表，但注意不要造成一个循环
• FEDERATED表不支持一般意义上的索引，要远程表上有索引才有效
• 如果一个查询语句不能使用远程表的索引，会导致全表扫描，本地数据库会获取全表数据（存在本地内存中，如果数据量过大会引起交换和挂起），再在本地进行过滤
• 不支持alter table或drop table，执行drop table只会删除本地FEDERATED表
• 不支持分区
• 如果远程表改变，本地表无法获知

PERFORMANCE_SCHEMA

性能

mysql>show databases;   # 就可以看到这个数据库啦
mysql> SHOW TABLES FROM performance_schema; # 查看所有表

• 关注收集mysql server运行中的性能数据，会监视server的所有events
• performance_schema数据库名及其表名都是小写的，查询时要用小写
• 很多表都是只读的，对数据库所有表的GRANT ALL授权是不允许的
• 数据库中表的更改不会写在日志中
• 是完全in-memory的，不占用磁盘空间，mysql服务启动时表会被重新填充，关闭服务时便丢弃
• 数据收集的实现是在源码中添加"监控点"(instrumentation)，没有用额外的线程(不像"复制"或"事件调度")
• 用户不能创建存储该类型的表

MEMORY

内存

适用场景：存储临时、不重要的数据，例如作为缓存，适合大量读的情形 (limited updates)

• 不支持变长的数据类型variable-length data types (including BLOB and TEXT)
• 不支持外码约束
• 不支持压缩
• 不支持MVCC
• 支持哈希索引和B树索引，不支持全文索引和T树索引
• mysql服务关闭或重启，数据会消失(表还在)
• 数据量不能超过内存大小
• 性能限制
  • 单线程执行
  • 表更新用表级锁(高并发读写情形下，表级锁严重降低性能，还不如InnoDB快)
• 内置的临时表(也在内存中)太大时会自动转成磁盘存储，但用户自创的内存表永远不会转化
• 可以从persistent data source装载数据到内存表

Characteristics of MEMORY Tables

被删除的row会放进一个链表(不会回收内存)，等插入新数据时拿出来复用，只有整个表被删除后才会回收内存。采用定长的行存储，即使是varchar也是定长存储的。

默认使用哈希索引，并且允许非唯一的哈希索引(但如果字段含大量重复值，性能会很低，这种情况最好用B树索引)，被索引字段可以有NULL。

Managing Memory Use

# 释放内存
DELETE * FROM table_name;                               # 释放所有row占用的内存
TRUNCATE TABLE table_name;                          # 释放所有row占用的内存
DROP TABLE table_name;                                  # 释放整个table占用的内存
ALTER TABLE table_name ENGINE=MEMORY;       # 释放deleted row链表占用的内存

SET max_heap_table_size = 1024*1024*2;# 设定单个内存表的空间限制，单位是Byte，这里是2MB，默认16MB

CSV

数据存在csv文件中

• 创建一个csv表，除了.frm文件外，还创建一个.csv文件用于存储数据，还有一个.csm文件存储表状态、行数等信息，称为metafile
• 所有字段都必须NOT NULL
• 不支持索引、分区

5.设置存储引擎的方式

1.建表时设置

CREATE TABLE t3 (i INT) ENGINE = MEMORY;

2.建表后设置

ALTER TABLE t ENGINE = InnoDB;

3.设置默认存储引擎

SET default_storage_engine=NDBCLUSTER;