存储引擎是数据库底层软件,数据库管理系统通过存储引擎进行增删改查操作,不同的存储引擎提供不同的机制,我们可以根据业务需求选择合适的存储引擎。
我们可以通以下下 sql 脚本查看存储引擎:
// 查看当前数据库支持的存储引擎
SHOW ENGINES;
// 查看某个表使用的存储引擎
show create table 表名;
可以看出 InnoDB 是默认的存储引擎,其中后面三列分别表示是否支持事务、分布式是否符合 XA 规范,是否支持事务处理中的保存点。
命令二通过创建表的语句可以看出这张表使用什么存储引擎,同一个库,不同表可以使用不同的存储引擎。已经创建的表也可以修改存储引擎,但需要保证前后存储引擎间不存在冲突。
InnoDB
事务型存储引擎,所谓事务就是指将几条 sql 封装为独立事务,事务执行期间不允许被插队,具备原子性。其中它主要具备以下特性:
- 具备提交、回滚和崩溃恢复的事务安全能力(ACID)
- 支持行级锁,并且提供了非锁定读
- 支持外键,表中数据按照主键顺序存储
总得来说,对于事务的完整性要求较高、需要实现并发控制等场景,InnoDB 具有很大优势
MyISAM
不支持事务以及外键,但插入、查询速度较快。它的主要特性有:
- 支持大文件系统
- 支持全文索引,InnoDB 不支持大索引
- 索引可以为空
- 索引文件可以和数据文件分开
- 表中包含字段记录数据条数
使用 MyISAM 引擎创建表将存在三个文件。frm 文件存储表定义、myd 文件存储数据,myi 文件存储索引信息
InnoDB 和 MyISAM 的区别
InnoDB 和 MyISAM 是最常被用到和问到的存储引擎,这里我列出两者的主要区别:
- InnoDB 支持事务,MyISAM 不支持事务
- InnoDB 支持外键,MyISAM 不支持外键
- InnoDB 采用聚焦索引,MyISAM 采用非聚焦索引,两者索引都通过 B+ 树实现
- InnoDB 表不保存行数,需要全表扫描,MyISAM 索引会存储数据行数
- InnoDB (5.7之后支持) 不支持全文索引,MyISAM 支持
- MyISAM 表格支持压缩后查询
- InnoDB 支持表、行级锁,MyISAM 只支持表锁
- InnoDB 表必须包含唯一索引(主键),而 MyISAM 没要求
- InnoDB 存储文件只有 frm 和 ibd (数据文件),而 MyISAM 存储文件包含 frm,myd,myi
如何选择:
- 是否需要支持事务
- 只读查询推荐 MyISAM,读写频繁建议 InnoDB
- 系统崩溃后,MyISAM 不容易恢复,InnoDB 具备容灾机制
如果不知道如何选择的话,采用默认 InnoDB 即可。
其他不常用存储引擎
- CSV:生成 .csv 文件,所以它可以和 csv 文件进行交互。不支持索引,字段不允许为空,一般用于数据交换
- MEMORY:将数据存储在内存中,查询和引用迅速,默认使用 Hash 索引,速度较快。因为数据存储在内存,重启会丢失,一般用于临时表
- ARCHIVE:归档,仅支持查询和插入两种功能。适合做日志的存储,查询效率较低。
- BLACKHOLE:写入这种引擎表的数据都会消失,适合做日志记录或同步归档的中继存储
- FEDERATED:可以将不同的 MySQL 服务器联合起来,逻辑上组成新的数据库,适合分布式应用。具体操作会发送到远程表上执行,本地不存储数据。
- PERFORMANCE_SCHEMA:主要用于收集服务器性能参数,MySQL 用户不允许创建