存储引擎概述
和大多数的数据库不同,MySQL中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式。存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。
Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应的引擎,或者编写存储引擎。
MySQL5.0支持的存储引擎包含:
- InnoDB
- MyISAM
- BDB
- MEMORY
- MERGE
- EXAMPLE
- NDB Cluster
- ARCHIVE
- CSV
- BLACKHOLE
- REDERATED
其中InnoDB和BDB提供事务安全表,其他存储引擎都是非事务安全表。
可以通过指定下面语句,来查询当前数据库支持的存储引擎。
show engines;
查看默认引擎
show variables like '%storage_engine%';
各种存储引擎特性
特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
---|---|---|---|---|---|
存储限制 | 64TB | 有 | 有 | 没有 | 有 |
事务安全 | 支持 | ||||
锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 |
B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
哈希索引 | 支持 | ||||
全文索引 | 支持(5.6版本之后) | 支持 | |||
集群索引 | 支持 | ||||
数据索引 | 支持 | 支持 | 支持 | ||
索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||||
空间使用 | 高 | 低 | N/A | 低 | 低 |
内存使用 | 高 | 低 | 中等 | 低 | 低 |
批量插入速度 | 低 | 高 | 高 | 高 | 高 |
支持外键 | 支持 |
这里重点要掌握InnoDB/MyISAM,另外三种了解就行。
其中不管什么引擎表存储在磁盘上的linux文件路径都为/var/lib/mysql
路径下。该目录下根据数据库名分文件夹,进入文件夹后下方是根据表名存储的不同文件。
注意:在MySQL8开始删除了原来的frm文件,并采用 Serialized Dictionary Information (SDI), 是MySQL8.0重新设计数据词典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中。
如何可以查看表结构信息,官方提供了一个工具叫做ibd2sdi,在安装目录下可以找到,可以离线的将ibd文件中的冗余存储的sdi信息提取出来,并以json的格式输出到终端。
InnoDB
事务
# 开启事务
start transaction;
# 提交事务
commit;
# 回滚事务
rollback;
外键
重点外键功能
- ON DELETE RESTRICT 当从表中有用到该外键的数据时,主表不能删除该对应的数据
- ON UPDATE CASCADE 当主表修改了该外键某行该字段的值,从表关联的值也同时做修改
存储形式
InnoDB存储表和索引有以下两种存储方式:
- 使用共享表空间存储,这种方式创建的表的表结构保存在
表名.frm
文件中,数据和索引保存在innodb_data_home_dir
和innodb_data_file_path
定义的表空间中,可以时多个文件 - 使用多表空间存储,这种方式创建的表结构仍然存储在
表名.frm
文件中,但是每个表的数据和索引单独保存在表名.ibd
文件中
MyISAM
MyISAM不支持事务、也不支持外键,其优势是访问的速度快,对事务的完整性没有要求或以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
不支持事务
不管如何start transaction;commit;rollback;
都无法操作事务
文件存储方式
每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但扩展名分别是:
- .frm(存储表定义)
- .MYD(MYData,存储表数据)
- .MYI(MYIndex,存储表索引)
MEMORY
Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件,格式是.frm,该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。MEMORY类型的表访问非常的快,因为他的数据是存放在内存中的,并且默认使用HASH索引,但是服务一旦关闭,表中的数据就会丢失。
MERGE
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有存储数据,对MERGE类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行的。
对于MERGE类型表的插入操作,是通过INSERT_METHOD子句定义插入的表,可以有3个不同的值,使用FIRST或LAST值使得插入操作被相应地作用在第一或者最后一个表上,不定义这个子句或者定义为NO,表示不能对这个MERGE表执行插入操作。
可以对MERGE表进行DROP操作,但是这个操作只是删除MERGE表的定义,对内部的表是没有任何影响。
可以理解MERGE存储引擎类型的表,将数据结构相同的MyISAM表union all关联起来的视图,只是视图不能条件筛选,但是merge存储引擎的表可以通过where进行条件筛选
创建MERGE存储引擎的表
# 省略创建 user_01,user_02 engine为myisam的表
# 创建 engine为merge的表
CREATE TABLE user_all (
#user_01,user_02两张表相同的表结构
)engine = merge union = (user_01,user_02) INSERT_METHOD=LAST default charset=utf8mb4;
分别向两张表中插入记录
INSERT INTO user_01 values();
INSERT INTO user_01 values();
INSERT INTO user_02 values();
INSERT INTO user_02 values();
存储引擎的选择
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用程序,还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。
- InnoDB:对事务业务要求较高,更新、删除操作频繁。对于类似计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB是最合适的选择。
- MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性,并发性要求不是很高,那么选择MyISAM是非常合适的。
- MEMORY:通常用于更新不频繁的表,用以快速得到访问结果,现在更多是用NoSQL替代了如redis、MongoDB
- MERGE:用于将一系列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用他们。MERGE表的优点在于可以突破MyISAM表的限制大小,并且通过将不同的表分布在多个磁盘上,可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓库等环境十分合适。个人认为对于数据量大而分表的数据也可以用MERGE来合并查询。
放在最后
本文根据学习B站【黑马程序员】2020最新MySQL高级教程(求职面试必备)p32-p36整理笔记,有需要的同学可以前去系统的学习。