MySQL数据库引擎
前言:本篇博文主要记录尚硅谷的MySQL视频<<MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!>>中P113-114节视频笔记。
视频链接:https://www.bilibili.com/video/BV1iq4y1u7vj?p=113
本篇博文目录:
- 1.数据库存储引擎
- 2.InnoDB 引擎和MyISAM 引擎
- (1) InnoDB 引擎
- (2) MyISAM 引擎
- (3) InnoDB 引擎与MyISAM 引擎的对比
- 3.其他数据库引擎
1.数据库存储引擎
为了管理方便,人们把
连接管理
、查询缓存
、语法解析
、查询优化
这些并不涉及真实数据存储的功能划分为MySQL server
的功能,把真实存取数据的功能划分为存储引擎的功能
。所以在MySQL server完成了查询优化后,只需按照生成的执行计划
调用底层存储引擎提供的API,获取到数据后返回给客户端就好了。MysQL中提到了存储引擎的概念。简而言之,存储引擎就是指表的类型
。其实存储引擎以前叫做表处理器,后来改名为存储引擎,它的功能就是接收上层传下来的指令,然后对表中的数据进行提取或写入操作。
关于数据库引擎的常用操作SQL语句,如下:
- 查看mysql提供什么存储引擎:
show engines;
- 查看默认的存储引擎
show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;
- 修改默认的存储引擎
如果在创建表的语句中没有显式指定表的存储引擎的话,那就会默认使用数据库默认引擎作为表的存储引擎,如果我们想改变表的默认存储引擎的话,可以这样写启动服务器的命令行:
SET DEFAULT_STORAGE_ENGINE=MyISAM;
或者修改my.ini(Linux为my.cnf )文件:
修改配置文件的方式需要重启服务:
Windwos采用net start MySQL服务名,Linux采用 systemctl restart mysqld.service
- 创建表时指定存储引擎
存储引擎是负责对表中的数据进行提取和写入工作的,我们可以为 不同的表设置不同的存储引擎 ,也就是说不同的表可以有不同的物理存储结构,不同的提取和写入方式。
在创建的时候设置表的存储引擎:
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;
- 修改表的存储引擎
如果表已经创建号,可以通过
ALTER TABLE 表名 ENGINE = 存储引擎名称;
修改表的存储引擎:
2.InnoDB 引擎和MyISAM 引擎
(1) InnoDB 引擎
InnoDB引擎是具备外键支持功能的事务存储引擎,MySQL5.5即以后默认采用引擎,由于是事务性引擎所以被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback),如果业务中涉及到大量的更新,删除等操作优先使用InnoDB引擎。
InnoDB引擎的数据结构类型:
- 表名.frm 存储表结构(MySQL8.0时,合并在表名.ibd中)
- 表名.ibd 存储数据和索引
下表为MySQL5.6版本中test数据库中的数据文件列表:
上图中的account表采用的数据库引擎为InnoDB:
(2) MyISAM 引擎
MyISAM 引擎是一种非事务处理存储引擎,提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM
不支持事务、行级锁、外键
,有一个毫无疑问的缺陷就是崩溃后无法安全恢复
,该数据库引擎是MySQL5.5之前默认采用引擎,该数据引擎的优势是访问的速度快
,对事务完整性没有要求或者以SELECT、INSERT为主的应用,所以适合只读应用或者以读为主的业务,针对数据统计有额外的常数存储,故而 count(*) 的查询效率很高。
MyISAM 引擎的数据结构类型:
- 表名.frm 存储表结构
- 表名.MYD 存储数据 (MYData)
- 表名.MYI 存储索引 (MYIndex)
下表为MySQL5.6版本中test数据库中的数据文件列表:
上图中的account_myisam表采用的数据库引擎为MyISAM :
(3) InnoDB 引擎与MyISAM 引擎的对比
对比项 | MyISAM | InnoDB |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 |
自带系统表使用 | Y | N |
关注点 | 性能:节省资源、消耗少、简单业务 | 事务:并发写、事务、更大资源 |
默认安装 | Y | Y |
默认使用 | N | Y |
3.其他数据库引擎
数据引擎 | 描述 |
---|---|
Archive | 用于数据存档 |
Blackhole | 丢弃写操作,读操作会返回空内容 |
CSV | 存储数据时,以逗号分隔各个数据项 |
Memory | 置于内存的表 |
Federated | 访问远程表 |
Merge | 管理多个MyISAM表构成的表集合 |
NDB | MySQL集群专用存储引擎 |
数据库引擎对比如下图所示:
特点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
---|---|---|---|---|---|
存储限制 | 有 | 64TB | 有 | 没有 | 有 |
事务安全 | 支持 | ||||
锁机制 | 表锁 | 行锁 | 表锁 | 表锁 | 行锁 |
B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
哈希索引 | 支持 | 支持 | |||
全文索引 | 支持 | ||||
集群服务 | 支持 | ||||
数据缓存 | 支持 | 支持 | 支持 | ||
索引缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||||
空间使用 | 低 | 高 | N/A | 低 | 低 |
内存使用 | 低 | 高 | 中等 | 低 | 高 |
批量插入的速度 | 高 | 低 | 高 | 高 | 高 |
支持外键 | 支持 |