关于MySQL数据库InnoDB与MyIASM存储引擎

共计 5683 个字符，预计需要花费 15 分钟才能阅读完成。

如果想要修改 MySQL 数据库的存储引擎，那么必须要了解这两种引擎，并且清楚的明白这两种引擎的区别。

MySQL 数据库支持两种常见的存储引擎：

InnoDB 引擎：提供了对数据库 ACID 事务的支持，并且实现了 SQL 标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于 MySQL 后台的完整数据库系统，MySQL 运行时 Innodb 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持 FULLTEXT 类型的索引，而且它没有保存表的行数，当 SELECT COUNT(*) FROM TABLE 时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用 Innodb 引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个 SQL 语句时 MySQL 不能确定要扫描的范围，InnoDB 表同样会锁全表。

MyIASM 引擎：是 MySQL 默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和 Innodb 不同，MyIASM 中存储了表的行数，于是 SELECT COUNT(*) FROM TABLE 时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么 MyIASM 也是很好的选择。

主要区别：
1、MyIASM 是非事务安全的，而 InnoDB 是事务安全的；
2、MyIASM 锁的粒度是表级的，而 InnoDB 支持行级锁；
3、MyIASM 支持全文类型索引，而 InnoDB 不支持全文索引；
4、MyIASM 相对简单，效率上要优于 InnoDB，小型应用可以考虑使用 MyIASM；
5、MyIASM 表保存成文件形式，跨平台使用更加方便。

应用场景：
1、MyIASM 管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量 select 操作，应该选择 MyIASM
2、InnoDB 用于事务处理，具有 ACID 事务支持等特性，如果在应用中执行大量 insert 和 update 操作，应该选择 InnoDB

查看 mysql 数据库的引擎信息

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
//InnoDB 引擎是默认的存储引擎。

Support 列，YES 表示当前版本支持这个存储引擎，DEFAULT 表示该引擎是默认的引擎。NO 表示不支持该存储引擎。
查看系统变量 default_storage_engine 或 storage_engine

mysql> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+
3 rows in set (0.00 sec)

default_storage_engine 表示永久表（permanent tables）的默认存储引擎。；
default_tmp_storage_engine 表示临时表的默认存储引擎；
storage_engine这个系统变量不推荐使用，它已经被系统变量 default_storage_engine 替代了。

修改 MySQL 数据库的默认存储引擎

[root@localhost ~]# vim /etc/my.cnf                       // 编写 mysql 服务的主配置文件
               ………………         // 省略部分内容，添加如下内容
default-storage-engine=MyISAM
[root@localhost ~]# systemctl restart mysqld            // 重新启动 mysql 服务
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
// 再次查看发现 MyISAM 引擎已经成为默认引擎

在 mysql 数据库中直接修改存储默认引擎

mysql> set default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
// 再次查看发现 InnoDB 引擎已经成为默认引擎

注意，这个系统变量 default_storage_engine 是 BOTH(全局和临时），而且可以动态修改。但是要注意，即使你修改了系统变量 default_storage_engine，重启过后就会失效，如果你要永久修改，最好在 my.cnf 配置文件里面也设置 default-storage-engine 的值。

查看表使用的默认引擎

mysql> show create table t1 \G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec
// 可以看出 t1 表默认使用的引擎是 MyISAM。

修改表的默认存储引擎

mysql> ALTER TABLE t1 ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1 \G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
// 可以看出 t1 表默认使用的引擎是 InnoDB。

创建表的时候指定存储引擎
创建表的时候，如果要指定存储引擎，只需要设置参数 ENGINE 即可。

mysql> create table t2 (id int) engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3 (id int) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

：