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的主要优势
- 遵循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）
适用场景：Good for VLDB environments such as data warehousing
- 要求多个Mylsam表要有相同的列信息(包括顺序)和索引信息(包括索引的order)
- 这些信息不同不会影响表合并
- 列名和索引名
- 所有的备注comment
- 表的选项，例如
AVG_ROW_LENGTH,MAX_ROWS, orPACK_KEYS
- 创建merge表时会创建2个文件，一个是存数据的.frm文件，一个是.mrg文件(存储哪些表应当merge起来使用)
- merge表中的表可以存于不同的数据库中
- 支持merge表的增删改查，前提是必须拥有处理其中所有表的权限
- drop table只是删除了merge表，实际存储数据的表不会被删除
- 建表需要指定UNION=(list-of-tables)表明使用哪些表，以及INSERT_METHOD=LAST/FIRST表明在哪一个表中插入数据，否则无法执行insert操作
- merge表没有主键，因为不能强制实行唯一索引
一个安全性问题：
The use of
MERGEtables entails the following security issue: If a user has access toMyISAMtable t, that user can create aMERGEtable 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表，除了.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;
