如果想要修改 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)
来源: http://www.linuxidc.com/Linux/2019-08/160181.htm