【MySQL高级】Mysql的体系结构概览及存储引擎以及索引的使用(一)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【MySQL高级】Mysql的体系结构概览及存储引擎以及索引的使用

1. Mysql的体系结构概览


8b0d7636434d42b49cd1d5aae651160a.png

整个MySQL Server由以下组成


Connection Pool : 连接池组件


Management Services & Utilities : 管理服务和工具组件


SQL Interface : SQL接口组件


Parser : 查询分析器组件


Optimizer : 优化器组件


Caches & Buffers : 缓冲池组件


Pluggable Storage Engines : 存储引擎


File System : 文件系统


1) 连接层


最上层是一些客户端和链接服务,包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。


2) 服务层


第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如 过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定表的查询的顺序,是否利用索引等, 最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。


3) 引擎层


存储引擎层, 存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。


4)存储层


数据存储层, 主要是将数据存储在文件系统之上,并完成与存储引擎的交互。


和其他数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上,插件式的存储引擎架构,将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。


2. 存储引擎


2.1 存储引擎概述


和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。


存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式 。存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。


Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应引擎,或者编写存储引擎。


MySQL5.0支持的存储引擎包含 : InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎是非事务安全表。


可以通过指定 show engines , 来查询当前数据库支持的存储引擎 :


690fba60bd514f8e9c6971ef09f015ab.png


创建新表时如果不指定存储引擎,那么系统就会使用默认的存储引擎,MySQL5.5之前的默认存储引擎是MyISAM,5.5之后就改为了InnoDB。


查看Mysql数据库默认的存储引擎 , 指令 :

show variables like '%storage_engine%' ;


55b5fa9f9eb742d3be2d7f0898536854.png


2.2 各种存储引擎特性


下面重点介绍几种常用的存储引擎, 并对比各个存储引擎之间的区别, 如下表所示 :


特点 InnoDB MyISAM MEMORY MERGE NDB
存储限制 64TB 没有
事务安全 ==支持==
锁机制 ==行锁(适合高并发)== ==表锁== 表锁 表锁 行锁

B树索引 支持 支持 支持 支持 支持
哈希索引 支持
全文索引 支持(5.6版本之后) 支持
集群索引 支持
数据索引 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 N/A
内存使用 中等
批量插入速度
支持外键 ==支持==


下面我们将重点介绍最长使用的两种存储引擎: InnoDB、MyISAM , 另外两种 MEMORY、MERGE , 了解即可。


2.2.1 InnoDB


InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。


InnoDB存储引擎不同于其他存储引擎的特点 :


事务控制


create table goods_innodb(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;
start transaction;
insert into goods_innodb(id,name)values(null,'Meta20');
commit;


f05924cead9b42759e296fe32be1241a.png


测试,发现在InnoDB中是存在事务的 ;


外键约束


MySQL支持外键的存储引擎只有InnoDB , 在创建外键的时候, 要求父表必须有对应的索引 , 子表在创建外键的时候, 也会自动的创建对应的索引。


下面两张表中 , country_innodb是父表 , country_id为主键索引,city_innodb表是子表,country_id字段为外键,对应于country_innodb表的主键country_id 。


create table country_innodb(
    country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table city_innodb(
    city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);


在创建索引时, 可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。


RESTRICT和NO ACTION相同, 是指限制在子表有关联记录的情况下, 父表不能更新;


CASCADE表示父表在更新或者删除时,更新或者删除子表对应的记录;


SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被SET NULL 。


针对上面创建的两个表, 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的, 那么在主表删除记录的时候, 如果子表有对应记录, 则不允许删除, 主表在更新记录的时候, 如果子表有对应记录, 则子表对应更新 。


表中数据如下图所示 :


e64e0dd6f4cd4bbb9c7db292efa296f4.png


外键信息可以使用如下两种方式查看 :


show create table city_innodb ;

f3dc09e166d245bd837fc471a8353047.png


删除country_id为1 的country数据:


delete from country_innodb where country_id = 1;

bac9d0f08cfa43338bde50995191b105.png


更新主表country表的字段 country_id :


update country_innodb set country_id = 100 where country_id = 1;


e8956a3b978a4b158dc7766a7c5319ea.png


更新后, 子表的数据信息为 :


dcdcc2dbc7064cfb8dbf226061d7d968.png


存储方式


InnoDB 存储表和索引有以下两种方式 :


①. 使用共享表空间存储, 这种方式创建的表的表结构保存在.frm文件中, 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中,可以是多个文件。


②. 使用多表空间存储, 这种方式创建的表的表结构仍然存在 .frm 文件中,但是每个表的数据和索引单独保存在 .ibd 中。

d3995299cd3f4ed4a8c237d8fc97a09c.png


2.2.2 MyISAM


MyISAM 不支持事务、也不支持外键,其优势是访问的速度快,对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点:


不支持事务


create table goods_myisam(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;


442600134a24448b93b7378fd3fff4ae.png


通过测试,我们发现,在MyISAM存储引擎中,是没有事务控制的 ;


文件存储方式


每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但拓展名分别是 :


.frm (存储表定义);


.MYD(MYData , 存储数据);


.MYI(MYIndex , 存储索引);

ac997b92a6194e5f88ff4c1899c2071e.png


2.2.3 MEMORY


Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件,格式是.frm ,该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。MEMORY 类型的表访问非常地快,因为他的数据是存放在内存中的,并且默认使用HASH索引 , 但是服务一旦关闭,表中的数据就会丢失。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
20天前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
87 22
 MySQL秘籍之索引与查询优化实战指南
|
3天前
|
存储 关系型数据库 MySQL
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
|
21天前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
83 10
|
29天前
|
存储 关系型数据库 MySQL
【MYSQL】 ——索引(B树B+树)、设计栈
索引的特点,使用场景,操作,底层结构,B树B+树,MYSQL设计栈
|
4天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
58 0
|
1月前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
61 3
|
1月前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
79 3
|
1月前
|
SQL 关系型数据库 MySQL
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
94 2
|
1月前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
273 15
|
1月前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。