MySQL体系结构

🚀 MySQL体系结构

✨客户端层

客户端层是 MySQL 的最上层，它包括了连接管理、授权认证、查询缓存、分析器、优化器和执行器等组件。客户端通过连接管理器连接到 MySQL 服务器，并通过授权认证模块进行身份验证。如果身份验证通过，查询可以被缓存以提高性能。查询分析器将 SQL语句解析成语法树，优化器将语法树转换成可执行的查询计划，执行器负责执行查询计划并返回结果。同样在该层上可以实现基于SSL的安全链接

✨服务层

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

✨存储引擎层

存储引擎层是 MySQL 的最底层，它负责存储和检索数据。MySQL 支持多种存储引擎，包括 MyISAM、InnoDB、Memory、CSV、Archive 等。每种存储引擎都有自己的特点和适用场景。例如，MyISAM 存储引擎适用于读密集型应用，而 InnoDB 存储引擎适用于写密集型应用。

在 MySQL 中，客户端和服务端可以运行在同一台机器上，也可以运行在不同的机器上。存储引擎可以被视为插件，可以根据需要进行更换。

需要特别注意的！索引是在存储引擎层实现的，也就意味着不同的存储引擎，索引的结构是不一样的

🚀 存储引擎介绍

✨存储引擎，是mysql数据库的核心

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式  。存储引擎是基于表的，而不是 基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

✨建表时指定存储引擎

CREATE TABLE  表名 (
字段1  字段1类型    [ COMMENT  字段1注释 ] ,
......
字段n  字段n类型    [COMMENT  字段n注释 ]
) ENGINE = INNODB   [ COMMENT  表注释 ] ;

✨查询当前数据库支持的存储引擎

show engines;

执行：

示例

✨查询建表语句    ---默认存储引擎: InnoDB

show create table account;

此时，可以发现，就算建表时我们没有去指定存储引擎，数据库也会自动选择默认的存储引擎

✨创建表  czh,指定Memory存储引擎

create table czh (
   id int,
name varchar (10)
) engine = Memory ;

🚀 存储引擎特点 （InnoDB、  MyISAM、  Memory的特点）

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在  MySQL 5.5 之后，  InnoDB是默认的 MySQL 存储引擎。

特点：

事务支持：InnoDB是MySQL的默认存储引擎，它提供了ACID（原子性、一致性、隔离性和持久性）事务支持。它支持提交和回滚事务，并具有更好的并发性能。

行级锁定：InnoDB使用行级锁定来实现并发控制，允许多个事务同时访问表中的不同行，提高了并发性能和数据一致性。

外键约束：InnoDB支持外键约束，可以在表之间建立关系，保证数据的完整性和一致性。

Crash Recovery：InnoDB通过使用事务日志（redo log）来实现崩溃恢复功能，确保在数据库崩溃后能够恢复到一致的状态。

文件

xxx.ibd：  xxx代表的是表名，  innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结 构（ frm-早期的  、  sdi-新版的）、数据和索引。

参数：  innodb_file_per_table

show variables  like 'innodb_file_per_table ';

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。我们直接打开MySQL的数据存放目录： C:\ProgramData\MySQL\MySQL Server 8.0\Data ，这个目录下有很多文件夹，不同的文件夹代表不同的数据库，我们直接打开test文件夹。

查看：

可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如bank表，就是一个文件bank.ibd，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。

而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一个指令  ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

执行：

Innodb逻辑存储结构

✨表空间  : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。InnoDB存储引擎的表空间包括系统表空间（ibdata文件）和用户表空间（.ibd文件），系统表空间用于存储InnoDB引擎的系统信息和共享数据，用户表空间用于存储用户表的数据和索引。

✨段  : 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。

✨区  : 区是表空间的单元结构，每个区的大小为1M是固定的。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。

✨页  : 页是组成区的最小单元，  页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为16KB固定的。为了保证页的连续性，  InnoDB 存储引擎每次从磁盘申请  4-5 个区。

✨行  : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段

MyISAM

MyISAM是MySQL早期的默认存储引擎。

特点

表级锁定：MyISAM 存储引擎使用表级锁定，这意味着在进行写操作时会锁定整个表，而不是单独的行。这可能导致并发性能下降，特别是在有大量并发写操作的情况下。

不支持事务：与 InnoDB 不同，MyISAM 存储引擎不支持事务。这意味着它不具备 ACID（原子性、一致性、隔离性和持久性）属性，因此在出现故障时无法保证数据的一致性。

全文索引支持：MyISAM 存储引擎支持全文索引，这使得在文本数据上进行高效的全文搜索成为可能。

高性能读取：MyISAM 存储引擎在处理大量读取操作时具有较高的性能，适用于读取密集型应用。运行速度快

不支持外键：MyISAM 存储引擎不支持外键约束，因此无法在表之间建立关系，这可能会导致数据的完整性和一致性受到影响。

总的来说，MyISAM 存储引擎适合于读取密集型的应用，例如数据仓库或者只读数据的报表生成。然而，在需要事务支持或者并发写入的情况下，InnoDB 存储引擎通常更为适合。

文件

xxx.sdi：存储表结构信息

xxx.MYD: 存储数据

xxx.MYI: 存储索引

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为 临时表或缓存使用。

特点

基于内存：Memory 存储引擎是基于内存的，这意味着所有的数据都存储在内存中，而不是磁盘上。这使得 Memory 存储引擎在处理数据时非常快速，适用于需要高性能的应用。

表级锁定：Memory 存储引擎使用表级锁定，这意味着在进行写操作时会锁定整个表，而不是单独的行。这可能导致并发性能下降，特别是在有大量并发写操作的情况下。

不支持事务：与 InnoDB 不同，Memory 存储引擎不支持事务。这意味着它不具备 ACID（原子性、一致性、隔离性和持久性）属性，因此在出现故障时无法保证数据的一致性。

不支持外键：Memory 存储引擎不支持外键约束，因此无法在表之间建立关系，这可能会导致数据的完整性和一致性受到影响

支持 HASH 和 B-TREE 索引：Memory 存储引擎支持 HASH 和 B-TREE 索引，这使得在内存中进行高效的数据检索成为可能。

表数据存储在文件中：虽然 Memory 存储引擎的数据存储在内存中，但是表结构和元数据是存储在文件中的。这些文件通常存储在磁盘上，并且在 MySQL 重启时需要重新加载到内存中。

总的来说，Memory 存储引擎适合于需要高性能的应用，例如缓存、会话管理、临时表等。然而，由于其数据存储在内存中，因此在内存不足的情况下，可能会导致性能下降或者系统崩溃。此外，由于不支持事务和外键，因此在需要这些功能的应用中，Memory 存储引擎可能不太适合。

文件

xxx.sdi：存储表结构信息

🚀 区别及特点

✨InnoDB引擎与MyISAM引擎的有什么区别?

InnoDB 引擎和 MyISAM 引擎是 MySQL 中两种常用的存储引擎，它们在功能和特性上有一些重要的区别：

事务支持： InnoDB 引擎是 MySQL 的事务性存储引擎，支持事务操作（ACID 属性），可以使用 COMMIT 和 ROLLBACK 控制事务的提交和回滚。而 MyISAM 引擎不支持事务，它的操作是自动提交的。

锁定级别： InnoDB 引擎支持行级锁定，这意味着在读取或修改数据时，只锁定涉及的行，而不是整个表。这样可以提高并发性能，多个用户可以同时读取和修改不同的行。而 MyISAM 引擎只支持表级锁定，这意味着在进行写操作时会锁定整个表，可能导致并发性能下降。

崩溃恢复： InnoDB 引擎具有崩溃恢复的能力，它可以在数据库崩溃后自动进行恢复，保证数据的一致性。而 MyISAM 引擎在崩溃后需要进行手动修复，可能会导致数据丢失或不一致。

索引类型： InnoDB 引擎支持 B-TREE 索引，用于高效地进行数据检索。它还支持全文索引，可以进行全文搜索。而 MyISAM 引擎也支持 B-TREE 索引，但不支持全文索引。

外键支持： InnoDB 引擎支持外键约束，可以在表之间建立关系，保证数据的完整性和一致性。而 MyISAM 引擎不支持外键约束，无法建立关系。

性能特点： 通常情况下，InnoDB 引擎在处理大量并发写入的情况下性能较好，适用于事务性应用，如银行系统、电子商务等。而 MyISAM 引擎在读取密集型应用中性能较好，适用于查询频繁、写入较少的应用，如新闻网站、博客等。

需要根据具体的应用需求来选择合适的存储引擎，权衡各种特性和性能。在 MySQL 5.5 版本之后，InnoDB 引擎成为默认的存储引擎，推荐在大多数情况下使用。

🚀存储引擎选择

选择数据库存储引擎时，需要考虑以下因素，并根据具体情况进行权衡：

事务支持： 如果应用需要支持事务（例如银行系统、电子商务系统），则应选择支持事务的存储引擎，如 InnoDB。事务支持可以确保数据的一致性和完整性。

并发性能： 如果应用需要处理大量的并发写入操作，例如社交网络、在线游戏等，应选择支持高并发写入的存储引擎，如 InnoDB。其行级锁定可以提高并发性能。

读取性能： 如果应用主要是读取密集型的，例如新闻网站、博客等，可以选择读取性能较好的存储引擎，如 MyISAM。表级锁定在读取操作中可能不会成为性能瓶颈。

数据完整性： 如果应用需要建立复杂的数据关系，需要外键约束来保证数据完整性和一致性，应选择支持外键约束的存储引擎，如 InnoDB。

崩溃恢复： 如果应用对于数据库的崩溃恢复能力要求较高，需要自动进行恢复以确保数据一致性，应选择具有崩溃恢复能力的存储引擎，如 InnoDB。

全文搜索： 如果应用需要进行全文搜索，如博客的文章搜索等，应选择支持全文索引的存储引擎，如 InnoDB 或 MyISAM。

内存需求： 如果应用需要高性能的数据存取，可以考虑使用Memory存储引擎，将数据存储在内存中，以提高访问速度。

数据量和性能需求： 最后，需要考虑应用的数据量和性能需求。某些存储引擎在处理大规模数据时可能表现更优秀，而在小规模数据下可能没有太大优势。

InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。  MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

总的来说，需要综合考虑应用的读写比例、事务需求、数据完整性、并发性能等方面的需求，以及存储引擎的特性和限制，来选择最适合的存储引擎。在实际应用中，也可以根据具体情况进行测试和评估，以确定最佳的存储引擎选择。