MySQL 三种 InnoDB,MyISAM 和 MEMORY 存储引擎对比
三种引擎的区别:
事务: InnoDB 支持事务, MyISAM 和 MEMORY 两个不支持.
存储限制: InnoDB 有 64TB 的存储限制, MyISAM 和 MEMORY 要跟具体情况而定.
空间使用: InnoDB 对空间使用程度较高, MyISAM 和 MEMORY 对空间使用程度较低.
内存使用: InnoDB 和 MEMORY 对内存使用程度较高, MyISAM 对内存使用程度较低.
插入数据的速度: InnoDB 插入数据的速度较低, MyISAM 和 MEMORY 插入数据的速度较高.
对外键的支持: InnoDB 对外键支持情况较好, MyISAM 和 MEMORY 两个不支持外键.
1.InnoDB
InnoDB 是事务型数据库的首选引擎, 支持事务安全表 (ACID), 其它存储引擎都是非事务安全表, 支持行锁定和外键, MySQL5.5 以后默认使用 InnoDB 存储引擎.
它的主要特点有:
(1) 可以通过自动增长列, 方法是 auto_increment.
(2) 支持事务. 默认的事务隔离级别为可重复度, 通过 MVCC(并发版本控制) 来实现的.
(3) 使用的锁粒度为行级锁, 可以支持更高的并发;
(4) 支持外键约束; 外键约束其实降低了表的查询速度, 但是增加了表之间的耦合度.
(5) 配合一些热备工具可以支持在线热备份;
(6) 在 InnoDB 中存在着缓冲管理, 通过缓冲池, 将索引和数据全部缓存起来, 加快查询的速度;
(7) 对于 InnoDB 类型的表, 其数据的物理组织形式是聚簇表. 所有的数据按照主键来组织. 数据和索引放在一块, 都位于 B + 数的叶子节点上;
当然 InnoDB 的存储表和索引也有下面两种形式:
(1) 使用共享表空间存储: 所有的表和索引存放在同一个表空间中.
(2) 使用多表空间存储: 表结构放在 frm 文件, 数据和索引放在 IBD 文件中. 分区表的话, 每个分区对应单独的 IBD 文件, 分区表的定义可以查看我的其他文章. 使用分区表的好处在于提升查询效率.
对于 InnoDB 来说, 最大的特点在于支持事务. 但是这是以损失效率来换取的.
2.MyISAM
MyISAM 基于 ISAM 存储引擎, 并对其进行扩展. 它是在 web, 数据仓储和其他应用环境下最常使用的存储引擎之一. MyISAM 拥有较高的插入, 查询速度, 但不支持事务, 不支持外键.
使用这个存储引擎, 每个 MyISAM 在磁盘上存储成三个文件.
(1)frm 文件: 存储表的定义数据
(2)MYD 文件: 存放表具体记录的数据
(3)MYI 文件: 存储索引
frm 和 MYI 可以存放在不同的目录下. MYI 文件用来存储索引, 但仅保存记录所在页的指针, 索引的结构是 B + 树结构. 下面这张图就是 MYI 文件保存的机制:
从这张图可以发现, 这个存储引擎通过 MYI 的 B + 树结构来查找记录页, 再根据记录页查找记录. 并且支持全文索引, B 树索引和数据压缩.
支持数据的类型也有三种:
(1) 静态固定长度表
这种方式的优点在于存储速度非常快, 容易发生缓存, 而且表发生损坏后也容易修复. 缺点是占空间. 这也是默认的存储格式.
(2) 动态可变长表
优点是节省空间, 但是一旦出错恢复起来比较麻烦.
(3) 压缩表
上面说到支持数据压缩, 说明肯定也支持这个格式. 在数据文件发生错误时候, 可以使用 check table 工具来检查, 而且还可以使用 repair table 工具来恢复.
特点:
插入数据快, 空间和内存使用比较低. 如果表主要是用于插入新记录和读出记录, 那么选择 MyISAM 能实现处理高效率. 如果应用的完整性, 并发性要求比较低, 也可以使用.
3.MEMORY
MEMORY 存储引擎将表中的数据存储到内存中, 为查询和引用其他表数据提供快速访问. 每一个表实际上都和一个磁盘文件关联. 文件是 frm.
特点:
(1) 支持的数据类型有限制, 比如: 不支持 TEXT 和 BLOB 类型, 对于字符串类型的数据, 只支持固定长度的行, VARCHAR 会被自动存储为 CHAR 类型;
(2) 支持的锁粒度为表级锁. 所以, 在访问量比较大时, 表级锁会成为 MEMORY 存储引擎的瓶颈;
(3) 由于数据是存放在内存中, 一旦服务器出现故障, 数据都会丢失;
(4) 查询的时候, 如果有用到临时表, 而且临时表中有 BLOB,TEXT 类型的字段, 那么这个临时表就会转化为 MyISAM 类型的表, 性能会急剧降低;
(5) 默认使用 hash 索引.
(6) 如果一个内部表很大, 会转化为磁盘表.
不同的存储引擎有不同的特点, 我们可以根据需求来选择存储引擎. 还有一个就是数据表也是有存储引擎的, 一个数据库里面的数据表可以是不同的存储引擎
创建表时指定存储引擎的类型:
CREATE TABLE 表名 (字段 约束 类型) ENGINE = INNODB
改变现有的表使用的存储引擎, 用以下语句:
ALTER TABLE mytable ENGINE = MyISAM
可以直接使用查看数据表创建的语句就能看到当前数据表使用的存储引擎:
show create table 表名;
参考博客:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
来源: https://www.cnblogs.com/yanzhongyixu/p/13402516.html