SYSTEM
存储引擎
src/content/notes/system/存储引擎.md 存储引擎
基本介绍
对比其他数据库,MySQL 的架构可以在不同场景应用并发挥良好作用,主要体现在存储引擎,插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离,可以针对不同的存储需求可以选择最优的存储引擎
存储引擎的介绍:
- MySQL 数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平等不同的功能和能力,在 MySQL 中,将这些不同的技术及配套的功能称为存储引擎
- Oracle、SqlServer 等数据库只有一种存储引擎,MySQL 提供了插件式的存储引擎架构,所以 MySQL 存在多种存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能
- 在关系型数据库中数据的存储是以表的形式存进行,所以存储引擎也称为表类型(存储和操作此表的类型)
- 通过选择不同的引擎,能够获取最佳的方案, 也能够获得额外的速度或者功能,提高程序的整体效果。
MySQL 支持的存储引擎:
- MySQL 支持的引擎包括:InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE 等
- MySQL5.5 之前的默认存储引擎是 MyISAM,5.5 之后就改为了 InnoDB
引擎对比
MyISAM 存储引擎:
- 特点:不支持事务和外键,读取速度快,节约资源
- 应用场景:适用于读多写少的场景,对事务的完整性要求不高,比如一些数仓、离线数据、支付宝的年度总结之类的场景,业务进行只读操作,查询起来会更快
- 存储方式:
- 每个 MyISAM 在磁盘上存储成 3 个文件,其文件名都和表名相同,拓展名不同
- 表的定义保存在 .frm 文件,表数据保存在 .MYD (MYData) 文件中,索引保存在 .MYI (MYIndex) 文件中
InnoDB 存储引擎:(MySQL5.5 版本后默认的存储引擎)
- 特点:支持事务和外键操作,支持并发控制。对比 MyISAM 的存储引擎,InnoDB 写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引
- 应用场景:对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,读写频繁的操作
- 存储方式:
- 使用共享表空间存储, 这种方式创建的表的表结构保存在 .frm 文件中, 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中,可以是多个文件
- 使用多表空间存储,创建的表的表结构存在 .frm 文件中,每个表的数据和索引单独保存在 .ibd 中
MEMORY 存储引擎:
- 特点:每个 MEMORY 表实际对应一个磁盘文件 ,该文件中只存储表的结构,表数据保存在内存中,且默认使用 HASH 索引,所以数据默认就是无序的,但是在需要快速定位记录可以提供更快的访问,服务一旦关闭,表中的数据就会丢失,存储不安全
- 应用场景:缓存型存储引擎,通常用于更新不太频繁的小表,用以快速得到访问结果
- 存储方式:表结构保存在 .frm 中
MERGE 存储引擎:
-
特点:
- 是一组 MyISAM 表的组合,这些 MyISAM 表必须结构完全相同,通过将不同的表分布在多个磁盘上
- MERGE 表本身并没有存储数据,对 MERGE 类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的 MyISAM 表进行的
-
应用场景:将一系列等同的 MyISAM 表以逻辑方式组合在一起,并作为一个对象引用他们,适合做数据仓库
-
操作方式:
- 插入操作是通过 INSERT_METHOD 子句定义插入的表,使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上;不定义这个子句或者定义为 NO,表示不能对 MERGE 表执行插入操作
- 对 MERGE 表进行 DROP 操作,但是这个操作只是删除 MERGE 表的定义,对内部的表是没有任何影响的
CREATE TABLE order_1( )ENGINE = MyISAM DEFAULT CHARSET=utf8; CREATE TABLE order_2( )ENGINE = MyISAM DEFAULT CHARSET=utf8; CREATE TABLE order_all( -- 结构与MyISAM表相同 )ENGINE = MERGE UNION = (order_1,order_2) INSERT_METHOD=LAST DEFAULT CHARSET=utf8;
| 特性 | MyISAM | InnoDB | MEMORY |
|---|---|---|---|
| 存储限制 | 有(平台对文件系统大小的限制) | 64TB | 有(平台的内存限制) |
| 事务安全 | 不支持 | 支持 | 不支持 |
| 锁机制 | 表锁 | 表锁/行锁 | 表锁 |
| B+Tree 索引 | 支持 | 支持 | 支持 |
| 哈希索引 | 不支持 | 不支持 | 支持 |
| 全文索引 | 支持 | 支持 | 不支持 |
| 集群索引 | 不支持 | 支持 | 不支持 |
| 数据索引 | 不支持 | 支持 | 支持 |
| 数据缓存 | 不支持 | 支持 | N/A |
| 索引缓存 | 支持 | 支持 | N/A |
| 数据可压缩 | 支持 | 不支持 | 不支持 |
| 空间使用 | 低 | 高 | N/A |
| 内存使用 | 低 | 高 | 中等 |
| 批量插入速度 | 高 | 低 | 高 |
| 外键 | 不支持 | 支持 | 不支持 |
只读场景 MyISAM 比 InnoDB 更快:
- 底层存储结构有差别,MyISAM 是非聚簇索引,叶子节点保存的是数据的具体地址,不用回表查询
- InnoDB 每次查询需要维护 MVCC 版本状态,保证并发状态下的读写冲突问题
引擎操作
-
查询数据库支持的存储引擎
SHOW ENGINES; SHOW VARIABLES LIKE '%storage_engine%'; -- 查看Mysql数据库默认的存储引擎 -
查询某个数据库中所有数据表的存储引擎
SHOW TABLE STATUS FROM 数据库名称; -
查询某个数据库中某个数据表的存储引擎
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称'; -
创建数据表,指定存储引擎
CREATE TABLE 表名( 列名,数据类型, ... )ENGINE = 引擎名称; -
修改数据表的存储引擎
ALTER TABLE 表名 ENGINE = 引擎名称;