存储引擎:
生活中,引擎就是整个机器运行的核心,不同的引擎具备不同的功能。
例如:飞机、跑车的引擎是不同的,具备不同的功能
MySQL存储引擎
MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力,在MySQL中 ,
将这些不同的技术及配套的功能称为存储引擎
在关系型数据库中数据的存储是以表的形式存进行储的,所以存储引擎也可以称为
表类型
(即存储和操作此表的类型)。Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能。
通过选择不同的引擎 ,能够获取最佳的方案 , 也能够获得额外的速度或者功能,提高程序的整体效果。所以了解引擎的特性 , 才能贴合我们的需求 , 更好的发挥数据库的性能。 MySQL支持的存储引擎
MySQL5.7支持的引擎包括:InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等
其中较为常用的有三种:
InnoDB
、MyISAM、MEMORY
MySQL体系结构详解:
客户端连接
支持接口:支持的客户端连接,例如C、Java、PHP等语言来连接MySQL数据库
第一层:网络连接层
连接池:管理、缓冲用户的连接,线程处理等需要缓存的需求。
例如:当客户端发送一个请求连接,会从连接池中获取一个连接进行使用。
第二层:核心服务层
- 管理服务和工具:系统的管理和控制工具,例如备份恢复、复制、集群等。
- SQL接口:接受SQL命令,并且返回查询结果。
- 查询解析器:验证和解析SQL命令,例如过滤条件、语法结构等。
- 查询优化器:在执行查询之前,使用默认的一套优化机制进行优化sql语句
- 缓存:如果缓存当中有想查询的数据,则直接将缓存中的数据返回。没有的话再重新查询!
第三层:存储引擎层
插件式存储引擎:管理和操作数据的一种机制,包括(存储数据、如何更新、查询数据等)
第四层:系统文件层
文件系统:配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存
常用引擎的特性对比:
MyISAM存储引擎
- 访问快,不支持事务和外键。表结构保存在.frm文件中,表数据保存在.MYD文件中,索引保存在.MYI文件中。
InnoDB存储引擎(MySQL5.5版本后默认的存储引擎)
- 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中,如果是共享表空间,数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中,可以是多个文件。如果是多表空间存储,每个表的数据和索引单独保存在 .ibd 中。
MEMORY存储引擎
- 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。
特性对比
特性 | MyISAM | InnoDB | MEMORY |
---|---|---|---|
存储限制 | 有(平台对文件系统大小的限制) | 64TB | 有(平台的内存限制) |
事务安全 | 不支持 | 支持 | 不支持 |
锁机制 | 表锁 | 表锁/行锁 | 表锁 |
B+Tree索引 | 支持 | 支持 | 支持 |
哈希索引 | 不支持 | 不支持 | 支持 |
全文索引 | 支持 | 支持 | 不支持 |
集群索引 | 不支持 | 支持 | 不支持 |
数据索引 | 不支持 | 支持 | 支持 |
数据缓存 | 不支持 | 支持 | N/A |
索引缓存 | 支持 | 支持 | N/A |
数据可压缩 | 支持 | 不支持 | 不支持 |
空间使用 | 低 | 高 | N/A |
内存使用 | 低 | 高 | 中等 |
批量插入速度 | 高 | 低 | 高 |
外键 | 不支持 | 支持 | 不支持 |
引擎的操作:
查询数据库支持的引擎
-- 查询数据库支持的存储引擎
SHOW ENGINES;
/*
表含义:
Engine : 指引擎名
support : 指服务器是否支持该存储引擎
comment : 指对引擎描述
transactions : 指存储引擎是否支持事务
XA : 指存储引擎是否支持分布式事务处理
Savepoints : 指存储引擎是否支持保存点
*/
查询某个数据库中所有数据表的引擎
-- 标准语法
SHOW TABLE STATUS FROM 数据库名称;
-- 查看数据库所有表的存储引擎
SHOW TABLE STATUS FROM db2;
查询某个数据库中某个数据表的引擎
-- 标准语法
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
-- 查看数据库中stu_score表的存储引擎
SHOW TABLE STATUS FROM db2 WHERE NAME = 'stu_score';
创建数据表,指定存储引擎
-- 标准语法
CREATE TABLE 表名(
列名,数据类型,
...
)ENGINE = 引擎名称;
-- 创建表,指定存储引擎为MyISAM
CREATE TABLE test_engine(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
)ENGINE = MYISAM;
-- 查询表的引擎
SHOW TABLE STATUS FROM db2 WHERE NAME = 'test_engine';
修改表的存储引擎
-- 标准语法
ALTER TABLE 表名 ENGINE = 引擎名称;
-- 修改表的引擎为InnoDB
ALTER TABLE test_engine ENGINE = INNODB;
-- 查询表的引擎
SHOW TABLE STATUS FROM db2 WHERE NAME = 'test_engine';
存储引擎选择_MyISAM :
特点:
不支持事务、外键,读取速度快,节约资源
使用场景:
如果应用是以查询、插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高
存储引擎选择_InnoDB :
特点:
是MySQL的默认存储引擎, 支持事务、外键、行级锁定 ,辅助索引、高缓存
使用场景:
对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,读写频繁的操作,比如BBS、计费系统、充值转账等
存储引擎选择_MEMORY:
特点:
数据在内存中,在需要快速定位记录和其他类似数据环境下,可以提供更快的访问
使用场景:
通常用于更新不太频繁的小表,用以快速得到访问结果