MySQL的存储引擎
数据库存储引擎:是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。
使用show engines;
可以查看存储引擎
MyISAM
MyISAM 不支持事务、也不支持外键,索引采用非聚集索引,其优势是访问的速度快,对事务完整性没有要求,以 SELECT、INSERT 为主的应用基本上都可以使用这个存储引擎来创建表。
MyISAM的表在磁盘上存储成 3 个文件,其文件名都和表名相同,扩展名分别是:
- .frm(存储表定义)
- .MYD(MYData,存储数据)
- .MYI (MYIndex,存储索引)
MyISAM的索引
MyISAM引擎使用B+树作为索引结构,叶节点的data域存放的是数据记录的地址(非聚集索引)。下图是MyISAM主键索引的原理图:
MyISAM存储引擎,索引结构叶子节点存储关键字和数据地址,也就是说索引关键字和数据没有在一起存放,体现在磁盘上,就是索引在一个文件存储,数据在另一个文件存储,例如一个user表,会在磁盘上存储三个文件
- user.frm(表结构文件)
- user.MYD(表的数据文件)
- user.MYI(表的索引文件)
MyISAM的索引方式也叫做非聚集索引。
InnoDB
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全,支持自动增长列,外键等功能,索引采用聚集索引,索引和数据存储在同一个文件,所以InnoDB的表在磁盘上有两个文件,其文件名都和表名相同,扩展名分别是:
- .frm(存储表的定义)
- .ibd(存储数据和索引)
InnoDB的索引
InnoDB存储引擎的主键索引,叶子节点中,索引关键字和数据是在一起存放的(聚集索引),如图:
InnoDB的索引关键字和数据都是在一起存放的,体现在磁盘存储上,例如创建一个user表,在磁盘上只存储两种文件,
- user.frm(存储表的结构)
- user.ibd(存储索引和数据)。
InnoDB的索引树叶节点包含了完整的数据记录,这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(区别于MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
MEMORY
MEMORY 存储引擎使用存在内存中的内容来创建表。每个MEMORY 表实际只对应一个磁盘文件,格式是.frm(表结构定义)。
MEMORY 类型的表访问非常快,因为它的数据是放在内存中的,并且默认使用HASH索引(不适合做范围查询),但是一旦服务关闭,表中的数据就会丢失掉。
各种存储引擎区别
InnoDB与MyISAM区别:
**相同点:**索引都是B+树
存储结构的区别:
- MyISAM:表格式,叶子结点上的数据项存的是引用(指针),数据量大的时候用myisam。myisam的B+树索引都是在内存中的,所以myisam可以作全文索引。因为叶子结点存的是指针,所以会有二次寻址。
- InnoDB:数据项实实在在存储在叶子结点上面,数据量小的时候用innodb。innodb的B+树索引的部分叶子结点在内存中,不是全部都在内存
对于事务的支持:
- MyISAM:MyISAM不支持事务
- InnoDB:InnoDB支持事务,对数据安全性要求比较高使用InnoDB
锁的区别:
-
MyISAM:myisam采用的是表级锁
-
InnoDB:InnoDB采用的是行级锁,同时也支持表级锁。默认使用的是行级锁,当查找失败的时候,行级锁转变为表级锁