交维规范系列 | 为什么我们禁止使用 MyISAM 存储引擎

前言

有很多小伙伴很好奇我为什么能做到每两天就可以发一篇文章。那是因为很多文章并不是现学现写的，自从两年半前，我关闭了博客园个人博客，之后从未停止过写文章，只是放在飞书上未能公开。这些文章，有一些投稿到了别的公众号，有一些没有对外发，有一些仅仅是笔记的形式未经整理成文。其中，这篇是我几年前写的一篇文章，我看内容没有过时，稍微改改发出来吧。

背景

各业务部门经常有一些自建的数据库需要交维，交维需要严格按照交维规范⽂档提供⾜够的材料，满⾜接维条件、符合规范的我们才准予接维。但接维条件众多、交维给我们的⼯程师⽔平参差不⻬、⼯作量巨⼤，是我们遇到的挑战，所以我们有必要输出⼀个⽂档讲解交维规范中的各细项。

其中，  “禁止使用 MyISAM存储引擎” 是我们交维规范里的一条。

从 MySQL 历史说起

MySQL 支持 InnoDB、MyISAM 等数据库引擎，InnoDB 引擎是从 MySQL 5.5 版本开始的默认引擎，MySQL 5.7 开始把一部分系统表统一改为 InnoDB 引擎，MySQL 8.0 为了支持原子 DDL，把所有遗留的 MyISAM 系统表都统一修改为 InnoDB 引擎，至此，MySQL 实则上已完全抛弃了 MyISAM 存储引擎了。

从开发规范说起

早期公司的 MySQL 建设时，我们的开发规范就规定了存储引擎必须使用 InnoDB。

InnoDB 支持事务、行级锁，有更好的数据恢复能力、更好的并发性能，同时对多核CPU、大内存、SSD 等硬件支持更好，支持数据联机热备份等，因此 InnoDB 相比 MyISAM 有明显优势。

【强制】 原则上必须使用innodb存储引擎，禁用 ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB,MYISAM 存储引擎。

从特点说起

以下是 MyISAM 和 InnoDB 存储引擎的特点对比
（数据来源于《深入浅出 MySQL——数据库开发、优化与管理维护》第三版）

从表格可以看出，InnoDB 比 MyISAM 拥有更多的功能和特点，基本全方位碾压，使用 MyISAM 的业务都能使用 InnoDB 代替，反之则不行。

常见的误区是认为 MyISAM 支持 全文索引，而 InnoDB 不支持全文索引。

实际上:

1. 从 5.6.4 版本开始，InnoDB 就支持全文索引了。
2. 不建议 MySQL 上使用全文索引，性能太差。

从性能说起

在百度上可以搜到一些关于 MyISAM 和 InnoDB 性能比较的测试文章，有很多抄袭的，版本甚至是 MySQL 5.1，他们之中仍然推荐 MyISAM 也是情理中。

实际上，InnoDB 引擎被 Oracle 公司收购后，越来越好。并且 InnoDB 的性能随着硬件配置的提升，越来越好，但 MyISAM 上提升就非常有限。MyISAM 甚至没有针对 SSD 磁盘进行过优化和迭代。

在最新 MySQL 版本上，可以认为他们性能相当，但 MyISAM 在大表和超大表上的性能就很差。而且在大量并发 DML 的场景下，只支持表级锁的 MyISAM，性能会和 InnoDB 差距十分大。

MyISAM 几乎只剩下以下两个优势:

• 与未压缩的  InnoDB 表相比，表体积更小，占用磁盘空间小。
• 在 MyISAM 中 count（*）仍然要快得多。

以上两点，不是说一定要使用 MyISAM 的，有解决方案。

1. 使用 Rocksdb、TokuDB 引擎，压缩比非常高。对于轻量 OLAP 业务可以改用别的数据库，例如 clickhouse、TiDB。当然如果您对性能要求低于空间要求，可以考虑开启 InnoDB 的表压缩功能。定期归档表也是一个解决方案。方案还是很多的。MyISAM 在压缩体积上甚至没有优势。
2. MySQL InnoDB 存储引擎上，想要更快的 count 速度，应该保证表里有二级索引，在 MySQL 8.0 上甚至支持并行查询，可以利用多核 CPU 并行 count。假设速度还是没有达到理想要求，那可以考虑用 “redis 方案” 或者 “计数表方案”等等。

从业务场景说起

MyISAM

适合场景:

1. 频繁执行全表 count 语句
2. 对数据进行增删改的频率不高,查询非常频繁
3. 没有事务
4. 小型业务，表不超过百万。

InnoDB

适合场景:

1. 高并发，数据增删改查都相当频繁
2. 可靠性要求比较高，要求支持事务

因为 MyISAM 不支持事务，所以几乎无法满足绝大多数 OLTP 的业务场景。

因为 MyISAM 不支持行级锁，所以根本无法解决高并发增删改查的要求。

从运维说起

1. 关于性能和安全优化

MyISAM 没有什么可以优化的参数，来来去去也就调整以下参数：

key_buffer_size
read_rnd_buffer_size
read_buffer_size

复制

InnoDB 本身可以调优的参数有几十个，其中最重要的是 innodb_buffer_pool_size ，机器内存足够大，可以让数据全跑在内存里。

另外 InnoDB 拥有著名的四大特性:

• 写入缓冲（change buffer）(提高性能)
• 两次写（double write）(提高安全)
• 自适应哈希索引（adaptive hash index）(提高性能)
• 刷临近页 (innodb_flush_neighbors) (提高性能)

MyISAM 不支持 ACID 的任何一条，不支持 redo log，无法做到 crash safe。

请看这个场景，假设您正在 update 一张 10000 行的表，更新到 5000 行时，这个时候服务器掉电或 mysqld crash 了，有可能会发生两件事:

• 部分写

排查和修复起来麻烦，不像事务的原子写，要不全部写成功要不全部写失败。

• 表损坏

需要 repair table 才能恢复，虽然一般可以通过 repair table 管理语句语法来修复，但这给运维管理带来了额外的开销。

所以姜承尧老师之前说《如何正确地关闭 MySQL 数据库？99%的 DBA 都是错的！》(点击跳转)文章里指出最佳关库操作是 kill -9，我只能说你得想清楚，前提有很多，例如是否设置了双1，例如是否库里有 myisam 表。

2. 关于 DDL

InnoDB 支持 Online DDL，而 MyISAM 不支持。
InnoDB 在做支持 Online DDL 类型的 DDL 时，是用 inplace (就地)算法的，而 MyISAM 只能用 copy (复制)算法。前者可以使 DDL 期间，不阻塞表的增删改，后者使表只读，阻塞写入，对在线业务是无法承受的。

3. 关于备份

我们数据库运维一般用 xtrabackup 做物理热备，原理图如下:

备份的时候，在第 2 步拷贝完 InnoDB 文件后，会进入第 3 步，对数据库上一个全局读锁，如果这时候你的 MyISAM 表有很多、很大，那么这个全局读锁期间就很久，业务只读，写入阻塞，无法增删改，这对业务是无法承受的。 (当然，实际上我们备份都是从备库上备份的，如果业务是单机系统就真受影响了)

有时候业务的厂商在帮忙做数据迁移时，会使用 mysqldump 进行备份。但由于数据库知识的匮乏，即使使用了 --single-transaction
参数，也无法保证数据一致。

mysqldump -S /data/mysql/mysql_3307/tmp/mysql.sock --single-transaction -ER -A > mysql3307_full.sql

复制

原因就是，因为数据库里使用 InnoDB 表的同时混合了 MyISAM 表。
--single-transaction
和 --lock-tables
是互斥的。所以执行上述命令行备份时，对于 MyISAM 是不锁表备份的，所以 MyISAM 表和其他表之间是不一致状态的。例如，可能发生备份后的 MyISAM 的表A 是下午 14 点钟的状态，而 MyISAM 的表B 是下午 14 点 05 分的状态，而其他 InnoDB 的表全部是下午 13 点状态。

以下是官方的解释:
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_single-transaction

如何整改？

对于已经在跑的业务，在经过测试后，申请停机窗口

alter table your_myisam_table1 engine=innodb; 
alter table your_myisam_table2 engine=innodb; 
...

复制

对于新建 MySQL 环境，设置 my.cnf 配置，限制不能使用 MyISAM 存储引擎

[mysqld]
...
default_storage_engine=innodb                     #  MySQL5.7官方默认值
default_tmp_storage_engine=innodb                 #  MySQL5.7官方默认值
disabled_storage_engines=ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB,MyISAM

复制

提醒：这个方法在 8.0 版本一劳永逸，但在 5.7 版本因为还有部分元数据表用得 MyISAM，所以小版本升级时执行 upgrade 命令时会报错，问题不大，具体解决办法就需要小伙伴们发挥想象力了哈。效果如下:

mysql> show variables like 'disabled_storage_engines';
+--------------------------+-------------------------------------------------------------+
| Variable_name            | Value                                                       |
+--------------------------+-------------------------------------------------------------+
| disabled_storage_engines | ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB,MYISAM |
+--------------------------+-------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> create table test (a int) engine=myisam;
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).

复制