MySQL 是我们经常使用的数据库处理系统 (DBMS), 不知小伙伴们有没有注意过其中的 "存储引擎"(storage_engine) 呢? 有时候面试题中也会问道 MySQL 几种常用的存储引擎的区别. 这次就简短侃一下存储引擎那些事儿.
先去查一下 "引擎" 概念.
引擎 (Engine) 是电子平台上开发程序或系统的核心组件. 利用引擎, 开发者可迅速建立, 铺设程序所需的功能, 或利用其辅助程序的运转. 一般而言, 引擎是一个程序或一套系统的支持部分. 常见的程序引擎有游戏引擎, 搜索引擎, 杀毒引擎等.
Ok, 我们知道了, 引擎就是一个程序的核心组件.
简单来说, 存储引擎就是指表的类型以及表在计算机上的存储方式.
存储引擎的概念是 MySQL 的特点, Oracle 中没有专门的存储引擎的概念, Oracle 有 OLTP 和 OLAP 模式的区分. 不同的存储引擎决定了 MySQL 数据库中的表可以用不同的方式来存储. 我们可以根据数据的特点来选择不同的存储引擎.
在 MySQL 中的存储引擎有很多种, 可以通过 "SHOW ENGINES" 语句来查看. 下面重点关注 InnoDB,MyISAM,MEMORY 这三种.
一. InnoDB 存储引擎
InnoDB 给 MySQL 的表提供了事务处理, 回滚, 崩溃修复能力和多版本并发控制的事务安全. 在 MySQL 从 3.23.34a 开始包含 InnnoDB. 它是 MySQL 上第一个提供外键约束的表引擎. 而且 InnoDB 对事务处理的能力, 也是其他存储引擎不能比拟的. 靠后版本的 MySQL 的默认存储引擎就是 InnoDB.
InnoDB 存储引擎总支持 AUTO_INCREMENT. 自动增长列的值不能为空, 并且值必须唯一. MySQL 中规定自增列必须为主键. 在插入值的时候, 如果自动增长列不输入值, 则插入的值为自动增长后的值; 如果输入的值为 0 或空 (NULL), 则插入的值也是自动增长后的值; 如果插入某个确定的值, 且该值在前面没有出现过, 就可以直接插入.
InnoDB 还支持外键 (FOREIGN KEY). 外键所在的表叫做子表, 外键所依赖 (REFERENCES) 的表叫做父表. 父表中被字表外键关联的字段必须为主键. 当删除, 更新父表中的某条信息时, 子表也必须有相应的改变, 这是数据库的参照完整性规则.
InnoDB 中, 创建的表的表结构存储在. frm 文件中 (我觉得是 frame 的缩写吧). 数据和索引存储在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中.
InnoDB 的优势在于提供了良好的事务处理, 崩溃修复能力和并发控制. 缺点是读写效率较差, 占用的数据空间相对较大.
二. MyISAM 存储引擎
MyISAM 是 MySQL 中常见的存储引擎, 曾经是 MySQL 的默认存储引擎. MyISAM 是基于 ISAM 引擎发展起来的, 增加了许多有用的扩展.
MyISAM 的表存储成 3 个文件. 文件的名字与表名相同. 拓展名为 frm,MYD,MYI. 其实, frm 文件存储表的结构; MYD 文件存储数据, 是 MYData 的缩写; MYI 文件存储索引, 是 MYIndex 的缩写.
基于 MyISAM 存储引擎的表支持 3 种不同的存储格式. 包括静态型, 动态型和压缩型. 其中, 静态型是 MyISAM 的默认存储格式, 它的字段是固定长度的; 动态型包含变长字段, 记录的长度不是固定的; 压缩型需要用到 myisampack 工具, 占用的磁盘空间较小.
MyISAM 的优势在于占用空间小, 处理速度快. 缺点是不支持事务的完整性和并发性.
三. MEMORY 存储引擎
MEMORY 是 MySQL 中一类特殊的存储引擎. 它使用存储在内存中的内容来创建表, 而且数据全部放在内存中. 这些特性与前面的两个很不同.
每个基于 MEMORY 存储引擎的表实际对应一个磁盘文件. 该文件的文件名与表名相同, 类型为 frm 类型. 该文件中只存储表的结构. 而其数据文件, 都是存储在内存中, 这样有利于数据的快速处理, 提高整个表的效率. 值得注意的是, 服务器需要有足够的内存来维持 MEMORY 存储引擎的表的使用. 如果不需要了, 可以释放内存, 甚至删除不需要的表.
MEMORY 默认使用哈希索引. 速度比使用 B 型树索引快. 当然如果你想用 B 型树索引, 可以在创建索引时指定.
注意, MEMORY 用到的很少, 因为它是把数据存到内存中, 如果内存出现异常就会影响数据. 如果重启或者关机, 所有数据都会消失. 因此, 基于 MEMORY 的表的生命周期很短, 一般是一次性的.
四. 怎样选择存储引擎
在实际工作中, 选择一个合适的存储引擎是一个比较复杂的问题. 每种存储引擎都有自己的优缺点, 不能笼统地说谁比谁好.
InnoDB: 支持事务处理, 支持外键, 支持崩溃修复能力和并发控制. 如果需要对事务的完整性要求比较高 (比如银行), 要求实现并发控制 (比如售票), 那选择 InnoDB 有很大的优势. 如果需要频繁的更新, 删除操作的数据库, 也可以选择 InnoDB, 因为支持事务的提交 (commit) 和回滚 (rollback).
MyISAM: 插入数据快, 空间和内存使用比较低. 如果表主要是用于插入新记录和读出记录, 那么选择 MyISAM 能实现处理高效率. 如果应用的完整性, 并发性要求比 较低, 也可以使用.
MEMORY: 所有的数据都在内存中, 数据的处理速度快, 但是安全性不高. 如果需要很快的读写速度, 对数据的安全性要求较低, 可以选择 MEMOEY. 它对表的大小有要求, 不能建立太大的表. 所以, 这类数据库只使用在相对较小的数据库表.
注意, 同一个数据库也可以使用多种存储引擎的表. 如果一个表要求比较高的事务处理, 可以选择 InnoDB. 这个数据库中可以将查询要求比较高的表选择 MyISAM 存储. 如果该数据库需要一个用于查询的临时表, 可以选择 MEMORY 存储引擎.
来源: http://www.jianshu.com/p/ce95f31e6ba8