一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第10天，点击查看活动详情
MySQL的体系结构
- MySQL由以下组件组成:
- Connection Pool : 连接池组件
- Management Services & Utilities : 管理服务和工具组件
- SQL Interface : SQL接口组件
- Parser : 查询分析器组件
- Optimizer : 优化器组件
- Caches & Buffers : 缓冲池组件
- Pluggable Storage Engines : 插件式存储引擎组件
- File System : 文件系统
- Files & Logs : 文件和日志
- MySQL的各个层级:
- 连接层:
- 连接层是最上层的一些客户端和连接服务,包含本地Socket通信和大多数基于客户端或者服务端工具实现的类似于TCP/IP的通信
- 用于完成关于连接处理,授权认证和相关的安全方案
- 连接层中引入了线程池的概念,可以为通过安全认证接入的客户端提供处理线程
- 连接层中可以实现基于SSL的安全连接
- 服务器会验证每一个安全接入的客户端的操作权限
- 服务层:
- 服务层主要用来完成数据库服务器的大部分核心功能.比如SQL接口,缓存的查询 ,SQL的分析和优化,内置函数的执行功能等. 所有与跨存储引擎的相关功能也在服务层实现,比如存储过程和存储函数等
- 在服务层中,数据库服务器会解析查询并创建对应的内部解析树,并完成相应的优化.比如确定表的查询顺序,是否利用索引等,最后生成相应的执行操作
- 如果是SELECT查询语句,服务器会查询内部的缓存来获取数据.因此如果有足够大的缓存空间,可以在提升大量读操作环境中的系统性能
- 引擎层:
- 存储引擎 .MySQL中的存储引擎负责MySQL中数据的存储和读取,服务器是通过API和存储引擎进行通信的
- 不同的存储引擎具有不同的功能,可以根据实际需要,选取合适的存储引擎
- 存储层:
- 数据存储层 .MySQL中的数据存储层主要是将数据存储在文件系统上,并且完成与存储引擎的交互
- 连接层:
- MySQL数据库服务器与其余的数据库服务器相比,MySQL数据库服务器的架构可以在不同的场景下应用并发挥良好的作用.主要体现在存储引擎上:
- 插件式的存储引擎架构. 将查询处理和其余系统任务以及数据的存储提取相分离
- 这样的业务架构是的可以根据业务的需求和实际需要选择合适的存储引擎
存储引擎
基本概念
- MySQL中存在一个存储引擎的概念,针对不同的存储需求可以选择最优化的存储引擎
- 存储引擎:
- 存储引擎就是存储数据,建立索引,更新查询数据等技术的实现方式
- 存储引擎是表类型的.因为存储引擎是基于表的,而不是基于数据库的
- MySQL和只有一种存储引擎的Oracle,SqlServer等数据库相比较 ,MySQL是一种插件式的存储引擎架构.在MySQL中存在多种存储引擎,可以根据需要使用相应的存储引擎或者自定义编写存储引擎
- MySQL中支持的存储引擎:
- InnoDB
- MyISAM
- BDB
- MEMORY
- MERGE
- EXAMPLE
- NDB Cluster
- AERCHIVE
- CSV
- BLACKHOLE
- FEDERATED
- 这里InnoDB和BDB是事务安全表,其余的存储引擎是非事务安全表
- MySQL数据库中创建新表如果不指定存储引擎,就会使用默认的存储引擎.在MySQL 5.5版本之前默认的存储引擎是MyISAM, 在MySQL 5.5版本之后默认的存储引擎是InnoDB
- 使用命令查询当前数据库支持的存储引擎:
show engines;
- 使用命令查询当前数据库中存储引擎相关的参数:
show variables like '%storage_engine%';
存储引擎特性
- 几种常用的存储引擎的特性比较:
| InnoDB | MyISAM | MEMORY | MERGE | NDB Cluster | |
|---|---|---|---|---|---|
| 存储限制 | 64 TB | 限制 | 限制 | 无限制 | 限制 |
| 事务安全 | 支持 | 不支持 | 不支持 | 不支持 | 不支持 |
| 锁机制 | 行级锁. 适合高并发 | 表级锁 | 表级锁 | 表级锁 | 行级锁 |
| 外键 | 支持 | 不支持 | 不支持 | 不支持 | 不支持 |
| B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 不支持 | 不支持 | 支持 | 不支持 | 不支持 |
| 全文索引 | 支持 | 支持 | 不支持 | 不支持 | 不支持 |
| 集群索引 | 支持 | 不支持 | 不支持 | 不支持 | 不支持 |
| 数据索引 | 支持 | 不支持 | 支持 | 不支持 | 支持 |
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 不支持 | 支持 | 不支持 | 不支持 | 不支持 |
| 空间使用 | 高 | 低 | N/A | 低 | 低 |
| 内存使用 | 高 | 低 | 中等 | 低 | 高 |
| 批量插入速度 | 低 | 高 | 高 | 高 | 高 |
InnoDB
- InnoDB存储引擎是MySQL数据库服务器的默认存储引擎
- InnoDB存储引擎提供具有提交,回滚,崩溃恢复的事务安全机制
- InnoDB存储引擎和MyISAM存储引擎相比较存在的缺点:
- 处理效率比较低
- 为了保存数据和索引会占用更多的磁盘空间
- 事务安全:
-- 开启事务 : 开启事务后,可以进行数据库的相关操作
start transaction;
-- 提交事务 : 进行数据库的相关操作后,可以提交事务
commit;
- 外键约束:
- MySQL数据库服务器中只有InnoDB存储引擎支持外键
- 创建外键时,要求外键表对应的外键字段必须要有索引
- 使用外键的表在创建外键时,会自动创建对应的索引
- 示例:
-- 外键表 CREATE TABLE country( id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 使用外键的表 CREATE TABLE city( id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, PRIMARY KEY(id), KEY idx_fk_country_id(country_id), CONSTRAINT 'fk_city_country' FOREIGN KEY(country_id) REFERENCES country(id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;- 更新外键表信息时,使用外键的表的外键相关信息也会自动更新
- MySQL数据库服务器中只有InnoDB存储引擎支持外键
- 存储方式:
- InnoDB存储表和索引有两种方式:
- 共享表空间存储:
- 创建的表的表结构存储在 .frm文件中
- 数据存储在innodb_data_home_dir定义的表空间中,可以是多个文件
- 索引存储在innodb_data_file_path定义的表空间中,可以是多个文件
- 多表空间存储:
- 创建的表的表结构存储在 .frm文件中
- 每个表的数据单独存储在 .ibd文件中
- 每个表的索引单独存储在 .ibd文件中
- 共享表空间存储:
- InnoDB存储表和索引有两种方式:
MyISAM
- MyISAM存储引擎不支持事务,不支持外键
- 适合对事务的完整性没有要求的应用
- 适合以SELECT或者INSERT为主要操作的应用
- MyISAM的优点是访问速度快
- 不支持事务
- 存储方式:
- 每个MyISAM存储引擎的表在磁盘上存储为三个文件,文件名都和表名相同,拓展名有以下三种:
- .frm - 创建的表的表结构
- .MYD - MYDATA. 创建的表的数据
- .MYI - MYIndex. 创建的表的索引
- 每个MyISAM存储引擎的表在磁盘上存储为三个文件,文件名都和表名相同,拓展名有以下三种:
MEMORY
- MEMORY存储引擎将表的数据存储在内存中
- 每个MEMORY存储引擎的表对应一个 .frm文件:
- .frm文件中只存储创建的表的表结构
- 表的数据都是存储在内存中
- MEMORY的数据都是存储在内存中,并且默认使用HASH索引
- 优点:
- 数据存储在内存中,有利于数据的快速处理,提高整个表的效率
- MEMORY存储引擎的表访问的速度非常快
- 缺点:
- 不支持事务,不支持外键
- 如果服务关闭 ,MEMORY存储引擎的表的表中的数据就会丢失
MERGE
- MERGE存储引擎的表是一组MyISAM存储引擎的表的组合
- MyISAM存储引擎的表的结构必须完全相同
- MERGE存储引擎的表不存储数据
- MERGE存储引擎的表的查询,更新和删除操作都是对内部的MyISAM存储引擎的表的操作
- MERGE存储引擎的表的插入操作:
- 通过INSERT_METHOD子句定义插入的表,可以有3个不同的值
- FIRST: 插入操作作用在第一个MyISAM存储引擎的表上
- LAST: 插入操作作用在最后一个MyISAM存储引擎的表上
- NO: 不定义子句或者将子句定义为NO, 表明不能对MEGER存储引擎的表的内部的MyISAM存储引擎的表进行插入操作
- 通过INSERT_METHOD子句定义插入的表,可以有3个不同的值
- DROP: 对MERGE存储引擎的表执行DROP操作,只是删除MERGE存储引擎的表的定义,对MERGE存储引擎的表的内部的MyISAM存储引擎的表没有任何影响
- 示例:
-- MyISAM存储引擎的表
CREATE TABLE orderA(
id int AUTO_INCREMENT,
order_money double(10,2),
order_address vachar(64),
PRIMARY KEY(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;
-- MyISAM存储引擎的表
CREATE TABLE orderB(
id int AUTO_INCREMENT,
order_money double(10,2),
order_address varchar(64),
PRIMARY KEY(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;
-- MERGE存储引擎的表
CREATE TABLE order_all(
id int AUTO_INCREMENT,
order_money double(10,2),
order_address varchar(64),
PRIMARY KEY(id)
)ENGINE=merge
UNION=(orderA,orderB)
INSERT_METHOD=LAST DEFAULT CHARSET=utf8
存储引擎使用场景
- 选择存储引擎时,需要根据应用系统的特点选择合适的存储引擎
- 对于复杂的应用系统,可以根据实际情况选择多种存储引擎进行组合
- 常见的几种存储引擎的使用场景:
- InnoDB:
- MySQL的默认存储引擎.支持事务的处理,支持外键
- 适合应用于应用系统对事务的完整性有比较高的要求,在并发的条件下要求数据的一致性,数据操作除了插入和查询以外,还包含很多的更新,删除操作
- InnoDB存储引擎可以有效降低删除和更新导致的锁定,可以确保事务的完整提交和回滚
- InnoDB引擎适合于类似计费系统和财务系统等对数据准确性要求很高的应用系统
- MyISAM:
- 适合应用于应用系统以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性,并发性要求不高
- MEMORY:
- 将所有的数据存储在内存中,在需要快速定位记录和类似的数据应用场景下,可以非常高效的访问数据
- 但是MEMORY引擎对存储表的大小有限制,无法将数据量很大的表存储到内存中
- 需要策略保证MEMORY引擎的表的数据可以恢复,保证数据库异常中止后 ,MEMORY存储引擎的表中的数据可以恢复
- MEMORY存储引擎适合应用于不频繁进行更新的小表,可以非常高效的访问数据
- MERGE:
- MERGE存储引擎是将一系统相同数据结构的MyISAM存储引擎的表以逻辑方式组合在一起,并作为一个对象来进行引用
- MERGE存储引擎的表突破了对单个MyISAM存储引擎的表的大小限制,通过将不同的MyISAM存储引擎的表分布在多个磁盘上,提高MERGE存储引擎的表的数据的访问效率
- MERGE存储引擎适合应用于数据仓储等相关的VLDB环境
- InnoDB:
