性能测试告诉你 mysql 数据库存储引擎该如何选？

简介
数据库存储引擎：是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL 的核心就是插件式存储引擎。

查看引擎
可以使用 SHOW ENGINES; 查看当前数据库支持的所有存储引擎

Engine 列，代表存储引擎类型；Support 列代表对应存储引擎是否能用，YES 表示可以用，NO 表示不能用，DEFAULT 表示当前默认的存储引擎

myql 提供了多种不同存储引擎，也可以在一个数据库中，针对不同的要求，使用不同的存储引擎。

SHOW VARIABLES LIKE ‘%storage_engine%’; 可以查看当前数据库默认的存储引擎

引擎介绍
InnoDB 存储引擎
InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），其它存储引擎都是非事务安全表，支持行锁定和外键，MySQL5.5 以后默认使用 InnoDB 存储引擎。
InnoDB 为 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID 兼容）存储引擎。
InnoDB 表，自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列。
InnoDB 设计的目标是处理大容量的数据库系统，这种引擎的表会在内存中建立缓冲池，用来缓冲数据和索引。
MySQL 外键的存储引擎只有 InnoDB
适用场景：
经常更新的表，多并发的表
大数据量
支持事务
容灾恢复
外键约束
MyISAM 存储引擎
MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务，不支持外键。
MYD 文件是存 MyISAM 的数据文件；MYI 文件是存 MyISAM 的索引文件；frm 文件是存 MyISAM 的表结构
MyISAM 的表支持 3 种不同的存储格式：静态(固定长度)表，动态表，压缩表
静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多
动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行 OPTIMIZE TABLE 或者 myisamchk-r 命令来改善性能
压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支
适用场景
不支持事务、外键的设计
查询速度很快，极度强调读操作，而且不占用大量的内存和存储资源
整表加锁
MEMORY 存储引擎
Memory 存储引擎使用存在于内存中的内容来创建表，所以也有叫 HEAP 堆内存引擎。每个 memory 表只实际对应一个磁盘文件，格式是。frm。memory 类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。
MEMORY 存储引擎的表可以选择使用 BTREE 索引或者 HASH 索引
Hash 索引优点：Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，查询效率要远高于 B-Tree 索引；但是，hash 算法是基于等值计算的，所以模糊查询，hash 索引无效，不支持
适用场景：
Memory 类型的存储引擎主要用于内容变化低、不频繁的，如代码表
目标数据比较小，而且非常频繁的进行访问的
数据是临时的，而且必须立即可用得到的
对存储引擎为 memory 的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中

MERGE \ MRG-MYISAM 存储引擎
Merge 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同，merge 表本身并没有数据，对 merge 类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的 MyISAM 表进行的
MRG-MYISAM 是一种水平分表方式存储引擎，把多个 myisam 的表聚合起来，但是他内部没有数据，真正的数据依然是 myisam 引擎。
使用场景：
水平分表
BLACKHOLE 黑洞引擎
任何写入此引擎的数据均会被丢弃，不做实际存储，select 结果永远为空
使用场景
复制数据到备份数据库
验证 dump file 命令的正确性
检测 binlog 功能所需的额外负载
充当日志服务器
存储引擎对比
MyISAM 引擎不支持事务等高级处理，Innodb 支持，提供事务支持、外键等高级功能
Innodb 引擎是行锁，但是也不是绝对的，当不确定范围时，Innodb 还是会锁表的
MyISAM 引擎强调的是性能，读性能非常好，比 Innodb 速度要快。
MySQL 数据库默认是开启事务的，Innodb 引擎表，要在提交大量数据时，可以先关闭自动提交事务 set autocommit=0; 待数据执行完后，再开启事务自动提交 set autocommit=1; 以此来提高速度，不然，大数据提交非常慢
对于 auto_increment 类型的字段， Innodb 中必须包含只有该字段的索引，而 MyISAM 表中，可以和其他字段一起建立联合索引。
MyISAM 支持全文索引(fulltext)、压缩引擎，Innodb 不支持
MyISAM 引擎表索引和数据分开存在两个不同格式文件中，并且索引是压缩的；而 Innodb 表的索引和数据是捆绑在一起的，没有压缩，所以，同等数据量，Innodb 引擎表占用的存储空间更大。
Innodb 表数据备份，要先到处 SQL 备份，load table from master 操作对 Innodb 不起作用。要解决这个问题，需要先把表的引擎 Innodb 改成 MyISAM，导入数据后，再改成 Innodb。但要注意，外键只有 Innodb 支持，MyISAM 不支持。