MySQl的体系结构:
-
连接层:
- 这是最上层,负责处理客户端的连接请求。主要功能包括连接处理、授权认证以及与安全相关的操作。服务器会根据安全方案验证每个客户端是否具备相应的操作权限。
-
服务层:
- 这是第二层,完成大部分核心的服务功能,如提供SQL接口、缓存查询、进行SQL的分析和优化,以及执行部分内置函数等。所有跨存储引擎的功能在这一层实现。
-
引擎层:
- 存储引擎层主要负责MySQL数据的存储和提取。服务器通过API与存储引擎进行通信,不同的存储引擎具有不同的功能,用户可以根据需求选择合适的引擎来存储数据。
-
存储层:
- 这一层将数据实际存储在文件系统上,并负责与存储引擎进行交互,确保数据的读写操作。
存储引擎的简介
如何在创建表的时候指定引擎?
在创建 MySQL 表时,可以通过 ENGINE
关键字来指定存储引擎。默认情况下,如果不指定存储引擎,MySQL 会使用默认的存储引擎(通常是 InnoDB)。如果你需要使用特定的存储引擎,可以在 CREATE TABLE
语句中进行指定。
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
) ENGINE = engine_name;
示例
使用 InnoDB 存储引擎(默认引擎):
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL
) ENGINE = InnoDB;
使用 MyISAM 存储引擎:
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
) ENGINE = MyISAM;
查询数据库支持的存储引擎
SHOW ENGINES;
该命令将返回 MySQL 当前支持的所有存储引擎及其相关信息,包括每个引擎是否支持事务,以及是否是默认存储引擎。查询结果通常包括以下列:
- Engine:存储引擎的名称。
- Support:存储引擎是否支持,通常为
YES
(支持)、NO
(不支持)或DEFAULT
(当前默认存储引擎)。 - Comment:简要描述存储引擎的功能。
- Transactions:该引擎是否支持事务(
YES
或NO
)。 - XA:该引擎是否支持分布式事务(
YES
或NO
)。 - Savepoints:该引擎是否支持保存点(
YES
或NO
)。
存储引擎的特点:
InnoDB介绍:
- 兼顾高可靠性和高性能,是 MySQL 5.5 之后的默认存储引擎。
特点:
- ACID 模型支持:InnoDB 遵循 ACID(原子性、一致性、隔离性和持久性)模型,支持事务操作,保证数据的可靠性和一致性。
- 行级锁:通过行级锁提升并发访问性能,相比表级锁,可以在高并发环境下更高效。
- 外键支持:支持外键约束(FOREIGN KEY),确保数据的完整性和正确性。
文件:
- 表的文件扩展名为
.ibd
,InnoDB 存储引擎为每个扩展表生成一个表空间文件,用于存储表的结构(如.frm
、.sdi
)以及数据和索引。 - 参数
innodb_file_per_table
用于控制每个表是否使用单独的表空间文件。
MyISAM 存储引擎特点:
- 介绍:MyISAM 是 MySQL 早期的默认存储引擎。
特点:
- 不支持事务,也不支持外键约束。
- 支持表级锁,但不支持行级锁,因此在高并发写入的场景中性能较差。
- 访问速度快:在以读为主的场景中,MyISAM 提供较快的查询速度,适合处理大量的读操作。
Memory 存储引擎特点:
- 介绍:Memory 引擎的表数据存储在内存中,因此受硬件问题或断电的影响,数据会丢失,只能用于临时表或缓存用途。
特点:
- 内存存放:表数据存储在内存中,速度非常快,但不持久化。
- Hash 索引:默认使用哈希索引,适合快速查找。
文件:
- xxx.sdi:用于存储表的结构信息。
逻辑存储结构:
存储引擎的选择:
选择存储引擎时,应根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。
-
InnoDB:
InnoDB 是 MySQL 的默认存储引擎,支持事务和外键。如果应用对事务的完整性有较高的要求,并发条件下需要数据的一致性,且操作除了插入和查询外,还包含较多的更新、删除操作,那么 InnoDB 存储引擎是比较合适的选择。 -
MyISAM:
如果应用是以读取操作和插入操作为主,只偶尔涉及更新和删除操作,且对事务的完整性和并发性要求不高,那么选择 MyISAM 存储引擎是合适的。 -
Memory:
将所有数据保存在内存中的存储引擎,访问速度快,通常用于临时表或缓存。Memory 的缺陷是对表的大小有限制,表太大时无法缓存在内存中,并且无法保障数据的安全性。