19.逻辑架构与存储引擎

逻辑架构

逻辑架构剖析

连接层

系统（客户端）访问 MySQL 服务器前，首先建立 TCP 连接。

经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

• 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行

• 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依 赖于此时读到的权限

TCP 连接收到请求后，必须要分配一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

服务层

SQL Interface: SQL接口

• 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT ... FROM就是调用SQL Interface
• MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口

Parser: 解析器

• 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
• 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。

Optimizer: 查询优化器

• SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划。
• 这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
• 它使用“选取-投影-连接”策略进行查询。例如：

SELECT id,name FROM student WHERE gender = '女';

这个SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。

Caches & Buffers： 查询缓存组件

• MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
• 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。
• 这个查询缓存可以在不同客户端之间共享。
• 由于捕获率极低，不推荐使用查询缓存，并在MySQL 8.0中删除。

引擎层

插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

所有的数据都是存在文件系统上，以文件的方式存在，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备。但没有必要。在文件系统之下，可以使用本地磁盘，可以使用 DAS、NAS、SAN等各种存储系统。

SQL执行流程

MySQL的查询流程：

1. 查询缓存：查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。在MySQL 中的查询缓存，不是缓存查询计划，而是查询结果。这就意味着查询匹配的鲁棒性大大降低，只有相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。

   可通过query_cache_type参数开关查询缓存

   同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql、 information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。

   此外，缓存有失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！对于更新压力大的数据库来说，查询缓存的命中率会非常低。

2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。

   分析器先做“词法分析”。MySQL 需要识别出SQL语句中字符串分别是什么，代表什么。 例如将"select"关键字识别出来，这是一个查询语句。要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

   接着，要做“语法分析”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果SQL语句正确，则会生成一个语法树。

3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索等。在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。

4. 执行器：在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行 SQL查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

SQL执行原理

确认开启profiling（SQL语句记录）

#profiling=0 代表关闭
select @@profiling;
show variables like 'profiling';
#开启profiling
set profiling=1;

查看当前会话SQL的全部记录：

show profiles;

查看执行计划：

#默认查询最后一条SQL语句
show profile;
#根据QueryID指定查询SQL语句
mysql> show profile for query 7;
#查询更丰富的内容
show profile cpu,block io for query 6;

MySQL5.7开启查询缓存：

在 /etc/my.cnf 中新增：

query_cache_type=1

重启服务后即可生效

Oracle中的SQL执行流程(了解)

Oracle 中采用了共享池来判断 SQL 语句是否存在缓存和执行计划，通过这一步骤我们可以知道应该采用硬解析还是软解析。

1. 语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。

2. 语义检查：检查 SQL 中的访问对象是否存在。比如SELECT 语句中列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。

3. 权限检查：看用户是否具备访问该数据的权限。

4. 共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。

   在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算 ，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划 ，就直接拿来执行，直接进入“执行器”的环节，这就是软解析 。

   如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器” 这个步骤，这就是硬解析 。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。数据字典缓冲区 存储的是 Oracle 中的对象定义，比如表、视图、索引等对 象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的，为了提升 SQL 的执行效率，我们应该尽量避免硬解析。

在 Oracle 中， 绑定变量是一大特色。在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。这样做的好处是能提升软解析的可能性 ，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。

示例：

select * from player where player_id = 10001;
#绑定变量
select * from player where player_id = :player_id;

第二种方式使用了绑定变量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。可以通过使用绑定变量来减少 Oracle 的解析工作量。但 使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。

数据库缓冲池

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。

这样可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。

缓冲池 vs 查询缓存

在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存，如下图所示：

缓冲池的重要性：

缓存原则：“位置*频次”可以帮我们对I/O访问效率进行优化。

首先位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载 。

缓冲池的预读特性：

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。

查询缓存：提前把查询结果缓存 起来，这样下次不需要执行就可以直接拿到结果。

查看/设置缓存池

使用 InnoDB 存储引擎时可以通过innodb_buffer_pool_size变量来查看缓冲池的大小

show variables like 'innodb_buffer_pool_size';

默认 InnoDB 的缓冲池大小只有134217728/1024^2=128MB

#修改缓冲池大小为256MB
set global innodb_buffer_pool_size = 268435456;

或在/etc/my.cnf中修改：

[server]
innodb_buffer_pool_size = 268435456

多个Buffer Pool实例

[server]
innodb_buffer_pool_instances = 2

表示要创建2个 Buffer Pool 实例。

查看缓冲池的个数：

show variables like 'innodb_buffer_pool_instances';

每个缓冲池实例实际占用内存：innodb_buffer_pool_size/innodb_buffer_pool_instances

Buffer Pool是MySQL内存结构中十分核心的一个组成，其更新数据流程：

若更新一半发生错误，可以使用 Redo Log & Undo Log 进行数据回滚。

存储引擎

查看提供的所有存储引擎：

show engines;

查看默认的存储引擎：

show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;

修改默认的存储引擎：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

或修改my.cnf文件：

default-storage-engine=MyISAM

重启服务后生效

设置表的存储引擎

可以为不同的表设置不同的存储引擎 ，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

1. 创建表时指定存储引擎：

#显式指定表的存储引擎
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;

2. 修改表的存储引擎：

ALTER TABLE 表名 ENGINE = 存储引擎名称;

3. 查看表的结构

SHOW CREATE TABLE engine_demo_table;

引擎介绍

InnoDB

具备外键支持功能的事务存储引擎

• InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
• 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
• 数据文件结构：
  • 表名.frm 存储表结构（MySQL8.0合并在表名.ibd中）
  • 表名.ibd 存储数据和索引
• InnoDB是为处理巨大数据量的最大性能设计。
  • 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如：.frm，.par，.trn，.isl，.db.opt等都在MySQL8.0中不存在了。
• 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
• MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

MyISAM

主要的非事务处理存储引擎

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
• 5.5之前默认的存储引擎
• 优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用
• 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
• 数据文件结构：
  • 表名.frm 存储表结构
  • 表名.MYD 存储数据 (MYData)
  • 表名.MYI 存储索引 (MYIndex)
• 应用场景：只读应用或者以读为主的业务

Archive 引擎：用于数据存档

特征	支持
B树索引	不支持
备份/时间点恢复 （在服务器中实现，而不是在存储引擎中）	支持
集群数据库支持	不支持
聚集索引	不支持
压缩数据	支持
数据缓存	不支持
加密数据（加密功能在服务器中实现）	支持
外键支持	不支持
全文检索索引	不支持
地理空间数据类型支持	支持
地理空间索引支持	不支持
哈希索引	不支持
索引缓存	不支持
锁粒度	行锁
MVCC	不支持
存储限制	没有任何限制
交易	不支持
更新数据字典的统计信息	支持

Blackhole 引擎：丢弃写操作，读操作会返回空内容

CSV 引擎：存储数据时，以逗号分隔各个数据项

使用改引擎时所有字段必须都为NOT NULL

创建CSV表还会创建相应的元文件表名.CSM，用于存储表的状态和表中存在的行数。数据存储文件表名.CSV可以被 Microsoft Excel 等电子表格应用程序读取，甚至写入。

Memory 引擎：置于内存的表

Memory采用的逻辑介质是 内存 ， 响应速度很快 ，但是当mysqld守护进程崩溃的时候 数据会丢失 。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

主要特征：

• Memory同时 支持哈希（HASH）索引 和 B+树索引 。
• Memory表至少比MyISAM表要快一个数量级 。
• MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
• 数据文件与索引文件分开存储。
• 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

使用场景：

1. 目标数据比较小 ，而且非常频繁的进行访问 ，在内存中存放数据，如果数据太大会造成内存溢 出 。可以通过参数 max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大小。

2. 如果数据是临时的 ，而且必须立即可用，那么就可以放在内存中。

3. 数据丢失也没有太大的影响 。

Federated 引擎：访问远程表

Federated引擎是访问其他MySQL服务器的一个代理 ，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

Merge引擎：管理多个MyISAM表构成的表集合

NDB引擎：MySQL集群专用存储引擎

也称 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集群。

MyISAM和InnoDB

最常用的就是 InnoDB 和 MyISAM ，有时会提一下 Memory 。其中 InnoDB 是 MySQL 默认的存 储引擎。

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
自带系统表使用	Y	N
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源
默认安装	Y	Y
默认使用	N	Y

Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。还新建了一款存储引擎叫 Xtradb 完全可以替代 Innodb ，并且在性能和并发上做得更好

补充

InnoDB表的优势

InnoDB存储引擎在实际应用中拥有诸多优势，比如操作便利、提高了数据库的性能、维护成本低等。其在主内存中维护缓冲池，高频率使用的数据将在内存中直接被处理。这种缓存方式应用于多种信息，加速了处理进程。

InnoDB不仅支持当前读写，也会缓冲改变的数据到数据流磁盘 。

InnoDB的性能优势不只存在于长时运行查询的大型表。在同一列多次被查询时，自适应哈希索引会提高查询的速度。使用InnoDB可以压缩表和相关的索引，可以在不影响性能和可用性的情况下创建或删除索引 。当处理大数据量时， InnoDB兼顾CPU，以达到最大性能 。

InnoDB和ACID模型

ACID模型是一系列数据库设计规则，着重强调可靠性。

1. 原子方面；ACID的原子方面主要涉及InnoDB事务，与MySQL相关的特性主要包括：
   • 自动提交设置。
   • COMMIT语句。
   • ROLLBACK语句。
   • 操作INFORMATION_SCHEMA库中的表数据。
2. 一致性方面；ACID模型的一致性主要涉及保护数据不崩溃的内部InnoDB处理过程，与MySQL相关的特性主要包括：
   • nnoDB双写缓存。
   • InnoDB崩溃恢复。
3. 隔离方面；隔离是应用于事务的级别，与MySQL相关的特性主要包括：
   • 自动提交设置。
   • SET ISOLATION LEVEL语句。
   • InnoDB锁的低级别信息。
4. 耐久性方面；ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样化，耐久性方面没有具体的规则可循。与MySQL相关的特性有：
   • InnoDB双写缓存，通过innodb_doublewrite配置项配置。
   • 配置项innodb_flush_log_at_trx_commit。
   • 配置项sync_binlog。 配置项innodb_file_per_table。
   • 存储设备的写入缓存。 存储设备的备用电池缓存。
   • 运行MySQL的操作系统。 持续的电力供应。 备份策略。 对分布式或托管的应用，最主要的在于硬件设备的地点以及网络情况。

InnoDB架构

1. 缓冲池
2. 更改缓存：一个特殊的数据结构，当受影响的索引页不在缓存中时，更改缓存会缓存辅助索引页的更改。索引页被其他读取操作时会加载到缓存池，缓存的更改内容就会被合并。不同于集群索引，辅助索引并非独一无二的。当系统大部分闲置时，清除操作会定期运行，将更新的索引页刷入磁盘。更新缓存合并期间，可能会大大降低查询的性能。在内存中，更新缓存占用一部分InnoDB缓冲池。 在磁盘中，更新缓存是系统表空间的一部分。更新缓存的数据类型由innodb_change_buffering配置项管理。
3. 自适应哈希索引：自适应哈希索引将负载和足够的内存结合起来，使得InnoDB像内存数据库一样运行， 不需要降低事务上的性能或可靠性。这个特性通过innodb_adaptive_hash_index选项配置，或者通过-- skip-innodb_adaptive_hash_index命令行在服务启动时关闭。
4. . 重做日志缓存：重做日志缓存存放要放入重做日志的数据。重做日志缓存大小通过 innodb_log_buffer_size配置项配置。重做日志缓存会定期地将日志文件刷入磁盘。大型的重做日志缓存使得大型事务能够正常运行而不需要写入磁盘。
5. 系统表空间：系统表空间包括InnoDB数据字典、双写缓存、更新缓存和撤销日志，同时也包括表和索引 数据。多表共享，系统表空间被视为共享表空间。
6. 双写缓存：双写缓存位于系统表空间中，用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存后，InnoDB才会将数据页写入合适的位置。
7. .撤销日志：撤销日志是一系列与事务相关的撤销记录的集合，包含如何撤销事务最近的更改。如果其他事务要查询原始数据，可以从撤销日志记录中追溯未更改的数据。撤销日志存在于撤销日志片段中，这 些片段包含于回滚片段中。
8. 每个表一个文件的表空间：每个表一个文件的表空间是指每个单独的表空间创建在自身的数据文件中，而不是系统表空间中。这个功能通过innodb_file_per_table配置项开启。每个表空间由一个单独的.ibd数据文件代表，该文件默认被创建在数据库目录中。
9. 通用表空间：使用CREATE TABLESPACE语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数据目录之外能够管理多个表并支持所有行格式的表。
10. 撤销表空间：撤销表空间由一个或多个包含撤销日志的文件组成。撤销表空间的数量由 innodb_undo_tablespaces配置项配置。
11. 临时表空间：用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间。innodb_temp_data_file_path配置项定义了相关的路径、名称、大小和属性。如果该值为空，默认会在innodb_data_home_dir变量指定的目录下创建一个自动扩展的数据文件。
12. 重做日志：重做日志是基于磁盘的数据结构，在崩溃恢复期间使用，用来纠正数据。正常操作期间，重做日志会将请求数据进行编码，这些请求会改变InnoDB表数据。遇到意外崩溃后，未完成的更改会自动在初始化期间重新进行。