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

简介

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

查看引擎

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

mysql20201209152600.png

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

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

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

mysql20201209154443.png

引擎介绍

• 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 不支持。