MySQL存储引擎
- 一、先从总体架构说起
- 二、引擎介绍
- 1.存储引擎
- 2.如何更改数据库表引擎
- 3.常用引擎及其特性对比
- 4.如何选择不同类型的引擎
一、先从总体架构说起
1.MySQL架构就是一个客户端-服务器系统。架构可以分为Server层 和 Engine层两部分
- 连接器:连接器负责跟客户端建立连接、获取权限、维持和管理连接
- 分析器:
- 词法分析:将一个完整的SQL语句,拆分成语句类型(select? insert? update? …)、表名、列名等等
- 语法分析:根据语法规则,判断输入的这个 SQL 语句是否满足 MySQL 语法
- 优化器:按照 MySQL 认为最优的方案去执行
- 执行器:执行语句,然后从存储引擎返回数据
- Engine层:就是存储引擎层
二、引擎介绍
1.存储引擎
1.MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应引擎,或 者编写存储引擎
2.通过指定 show engines
, 来查询当前数据库支持的存储引擎 :
字段表含义:
- support : 指服务器是否支持该存储引擎
- transactions : 指存储引擎是否支持事务
- XA : 指存储引擎是否支持分布式事务处理
- Savepoints : 指存储引擎是否支持保存点(实现回滚到指定保存点)
3.查看MySQL数据库存储引擎配置
SHOW VARIABLES LIKE '%storage_engine%';
2.如何更改数据库表引擎
1.建表语句后面加入引擎赋值即可 ,命令举例如下:
CREATE TABLE t1(id INT, name VARCHAR(20)) ENGINE = MyISAM;
2.修改已有的表引擎 , 命令举例如下
ALTER TABLE t1 ENGINE = InnoDB;
3.常用引擎及其特性对比
- MyISAM存储引擎 :访问快,不支持事务和外键。表结构保存在.frm文件中,表数据保存在.MYD文 件中,索引保存在.MYI文件中
- innoDB存储引擎 :支持事务 , 占用磁盘空间大 ,支持并发控制。表结构保存 在.frm文件中,如果是共享表空间,数据和索引保存在 innodb_data_home_dir 和
innodb_data_file_path定义的表空间中,可以是多个文件。如果是多表空间存储,每个表的数据和 索引单独保存在 .ibd 中
- MEMORY存储引擎 : 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中
- 对比
特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
---|---|---|---|---|---|
存储限制 | 64TB | 有 | 有 | 没有 | 有 |
事务安全 | 支持 | ||||
锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 |
B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
哈希索引 | 支持 | ||||
全文索引 | 支持(5.6版本之后) | 支持 | |||
集群索引 | 支持 | ||||
数据索引 | 支持 | 支持 | 支持 | ||
索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||||
空间使用 | 高 | 低 | N/A | 低 | 低 |
内存使用 | 高 | 低 | 中等 | 低 | 高 |
批量插入速度 | 低 | 高 | 高 | 高 | 高 |
支持外键 | 支持 |
4.如何选择不同类型的引擎
1.选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据 实际情况选择多种存储引擎进行组合
2.以下是几种常用的存储引擎的使用环境
- InnoDB : 是Mysql的默认存储引擎,用于事务处理应用程序,支持外键。如果应用对事务的完整性 有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包含更新、
删除操作,那么InnoDB存储引擎是比较合适的选择。 InnoDB存储引擎除了有效的降低由于删除和 更新导致的锁定, 还可以确保事务的完整提交和回滚,对于类似于计费系统或者财务系统等对数据 准确性要求比较高的系统, InnoDB是最合适的选择 - MyISAM : 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整 性、并发性要求不是很高,那么选择这个存储引擎是非常合适的
- MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据环境下,可以提供极 快的访问。 MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保 表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。 MEMORY表通常用于更新不太 频繁的小表,用以快速得到访问结果