02-MySQL 存储引擎

存储引擎

MySQL 常见数据库引擎

MySQL 有多种存储引擎：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

你可以通过 SHOW ENGINES 命令来查看 MySQL 支持的所有存储引擎。

查看 MySQL 提供的所有存储引擎

  • InnoDB：事务型数据库的首选引擎，支持事务(ACID)、行锁和外键。MySQL5.5.5 之后，InnoDB 作为默认存储引擎。
  • MyISAM：基于 ISAM 的存储引擎，并对其进行扩展。它是在 Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。MySQL5.5.5 之前，MyISAM 是默认存储引擎。
  • MEMORY：存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

MySQL 存储引擎架构了解吗？

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

并且，你还可以根据 MySQL 定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。这些非官方提供的存储引擎可以称为第三方存储引擎，区别于官方存储引擎。像目前最常用的 InnoDB 其实刚开始就是一个第三方存储引擎，后面由于过于优秀，其被 Oracle 直接收购了。

MySQL 官方文档也有介绍到如何编写一个自定义存储引擎，地址：https://dev.mysql.com/doc/internals/en/custom-engine.html 。

都说 InnoDB 好，那还要不要使用 MEMORY 引擎

内存表就是使用 MEMORY 引擎创建的表。不建议在生产环境上使用内存表。这里的原因主要包括两个方面：

  • 锁粒度问题。
  • 数据持久化问题。由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。

MyISAM 和 InnoDB 的区别 ★★★★★

  1. 是否支持行级锁

    • MyISAM 只有表级锁(table-level locking)。
    • InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁。（没用到索引的情况下还是锁整表，如 update table set a=1 where user like '%le%'）

  2. 是否支持事务

    • MyISAM 不提供事务支持。
    • InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

  3. 是否支持外键

    • MyISAM 不支持，而 InnoDB 支持。
    • 外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

  4. 是否支持数据库异常崩溃后的安全恢复

    • MyISAM 不支持，而 InnoDB 支持。
    • 使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

  5. 是否支持 MVCC

    • MyISAM 不支持，而 InnoDB 支持。

  6. 索引实现不一样。

    InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

  7. 性能有差别。

    InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系。

    InnoDB 和 MyISAM 性能对比

  8. 数据缓存策略和机制实现不同。

    InnoDB 使用缓冲池（Buffer Pool）缓存数据页和索引页，MyISAM 使用键缓存（Key Cache）仅缓存索引页而不缓存数据页。

常见的几种 MySQL 存储引擎对比

其他：

  • 从 MySQL 5.5.5 以后，InnoDB 是默认引擎，之前是 MyISAM。

  • 在 MySQL 5.6 以前，只有 MyISAM 存储引擎支持全文索引，从 MySQL 5.6 开始 MyISAM 和 InnoDB 均支持。

  • InnoDB 中不保存表的总行数，select count(*) from table 时，InnoDB 需要扫描整个表计算有多少行，但 MyISAM 只需简单读出保存好的总行数即可。
    注：当 count(*) 语句包含 WHERE 条件时 MyISAM 也需扫描整个表。

  • 对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立联合索引。

  • 清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表。MyISAM 使用 delete 语句删除后并不会立刻清理磁盘空间，需要定时清理，命令：OPTIMIZE table dept;

  • 存储结构:
    MyISAM：每个 MyISAM 表在磁盘上存储成三个文件。文件的名字是表的名字，扩展名指出文件类型：表定义文件的扩展名为 .frm；数据文件的扩展名为 .MYD (MYData)，索引文件的扩展名是 .MYI (MYIndex)。
    InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。

  • 表主键:
    MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
    InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个字节的主键(用户不可见)，数据是主索引的一部分，辅助索引保存的是主索引的值。

  • 可移植性、备份及恢复:
    MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
    InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

  • 应用场景：
    MyISAM：不支持事务处理等高级功能，但它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么 MyISAM 是更好的选择。
    InnoDB：用于需要事务处理的应用程序。如果应用中需要执行大量的 INSERT 或 UPDATE 操作，则应该使用 InnoDB，这样可以提高安全性。
    现在一般都选用 InnoDB。

MyISAM 和 InnoDB 如何选择？

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是~我们一般都会介意啊！）。

《MySQL 高性能》上面有一句话这样写到:

不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。

因此，对于咱们日常开发的业务系统来说，你几乎找不到什么理由再使用 MyISAM 作为自己的 MySQL 数据库的存储引擎。

#MySQL #面试
02-MySQL 存储引擎
https://flepeng.github.io/interview-41-数据库-41-MySQL-02-MySQL-存储引擎/
作者
Lepeng
发布于
2020年8月8日
许可协议