逻辑架构

逻辑架构剖析

• 基础服务组件
• 连接池
• SQL接口
• 解析器
• 优化器
• 查询缓存
• 插件式存储引擎
• 文件系统
• 日志文件

连接层

1. 建立TCP连接
2. 身份认证、权限获取

MySQL服务器有专门的TCP连接池限制连接数，采用长连接模式复用TCP连接。

连接接收到请求后，必须分配一个线程专门与这个客户端交互，所以还有一个线程池。

这些内容都归纳到MySQL的连接管理组件中。

服务层

服务层主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化以及部分内置函数的执行。所有跨存储引擎的功能也再这一层实现，如过程、函数等。

• SQL Interface: SQL接口
• Parser: 解析器
• Optimizer: 查询优化器
• Caches & Buffers: 查询缓存组件

引擎层

真正负责了MySQL中数据的存储和提取，对物理服务器级别的维护的底层数据执行操作。

SQL执行流程

1. 查询缓存（因为查询缓存往往效率不高，在MySQL8.0之后就抛弃了该功能）

   大多数情况查询缓存就是个鸡肋，原因如下：

   • 查询缓存不是缓存查询计划，而是缓存查询结果。只有相同的查询才会命中缓存。
   • 如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，那这个请求就不会缓存。
   • 既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，那使用该表的缓存查询都会失效。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

2. 解析器

   • 词法分析
   • 语法分析

   如果SQL语句正确，则会生产一个语法树。

3. 优化器

   一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划。

   优化可以分为两个部分：逻辑查询优化和物理查询优化

4. 执行器

   拿到优化器的执行计划后开始执行。

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

   打开表的时候，执行器会根据表的引擎定义，调用存储引擎API对表进行读写。存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现要看表选择的存储引擎。

总结：SQL的执行流程：SQL语句->查询缓存->解析器->优化器->执行器

存储引擎

存储引擎原称表处理器，它的功能就是接收上层传下来的指令，对表中的数据进行提取或写入操作

查看存储引擎

show engines;
# 查看默认的存储引擎
show variables like '%storage_engine';

修改存储引擎

# 修改默认的存储引擎
SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改my.cnf文件：

default-storage-engine=MyISAM

设置表的存储引擎

CREATE TABLE 表名(
 ...
) ENGINE = 存储引擎名;

ALTER TABLE 表名 ENGINE = InnoDB;

存储引擎类型

InnoDB引擎：具有外键支持功能的事务存储引擎

• InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期事务。可以确保事务的完整提交（commit）和回滚（rollback）

• 除了增加和查询外，还需要更新、删除操作，应优先选择InnoDB存储引擎

• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎

• 数据文件结构：

  表名.frm 存储表结构（MySQL8.0合并到表名.ibd中）

  表名.ibd 存储数据和索引

• InnoDB是为处理巨大数据量的最大性能设计

• 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引

• MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响

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

• MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复

• MySQL5.5之前默认的存储引擎

• 优势是访问的速度快

• 针对数据统计有额外的常数存储。故而count(*)的查询效率很高

• 数据文件结构：

  表名.frm 存储表结构

  表名.MYD 存储数据

  表名.MYI 存储索引

• 应用场景：只读应用或者以读为主的业务

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

InnoDB存储引擎架构

逻辑存储结构

• 表空间
• 段
• 区
• 页
• 行

内存结构

• 缓冲池（Buffer Pool）

• 更改缓冲区（Change Buffer）

  针对于非唯一二级索引页，在执行DML语句时，如果这些数据页没有在缓冲池中，不会直接操作磁盘，而是将数据变更存在更改缓冲区中，在未来数据被读取时，再将数据合并恢复到缓冲池中，再将合并后的数据刷新到磁盘中

  Change Buffer的意义是什么？

  与聚簇索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更改可能会影响索引树种不相邻的二级索引页，如果每次都操作磁盘，会造成大量的磁盘IO。有了Change Buffer，我们可以在缓冲池中进行合并处理，减少磁盘IO。

• 自适应Hash索引

  用于优化对缓冲池数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提高速度，则建立hash索引，称之为自适应hash索引。

  自适应哈希索引，无需人工干预，是系统根据情况自动完成。

• 日志缓冲区（Log Buffer）

  用来保存要写入磁盘中的log日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。

磁盘结构

• 系统表空间

  系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。

  文件：ibdata1

• 独立表空间

  每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。

  文件：xxx.ibd

• 通用表空间

  需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时，可以指定该表空间。

  文件：xxx.ibd

• 撤销表空间

  MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。

  文件：undo_001、undo_002

• 临时表空间

  InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

  文件：xxx.ibt

• 双写缓冲区

  InnoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

  文件：xxx.dblwr

• 重做日志

  用来实现事务的持久性。该日志由两部分组成：重做日志缓存（redo log buffer）以及重做日志文件（redo log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志中，用于在刷新脏页到磁盘时发生错误，进行数据恢复使用。

  文件：ib_logfile0、ib_logfile1

后台线程

• Master Thread

  核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，包括脏页的刷新、合并插入缓存、undo页的回收。

• IO Thread

  在InnoDB存储引擎中大量使用了AIO来处理IO请求，这样可以极大提高数据库性能，而IO Thread主要负责这些IO请求的回调。

  线程类型	默认个数	功能
  Read Thread	4	负责读操作
  Write Thread	4	负责写操作
  Log Thread	1	负责将日志缓冲区刷新到磁盘
  Insert Buffer Thread	1	负责将写缓冲区刷新到磁盘

• Purge Thread

  主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

• Page Cleaner Thread

  协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

缓冲池（buffer pool）

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

这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。要知道，这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

缓冲池vs查询缓存

缓冲池和查询缓存不是一个东西

• 缓冲池

  缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应索引哈希、数据字典信息等

  • 缓冲池的重要性：节省磁盘IO的开销

  • 缓存原则：位置 * 频次

    位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。

    频次决定优先级顺序，因为缓冲池的大小有限，会优先对使用频次高的热数据进行加载。

  • 缓冲池的预读特性：

    缓冲池的作用就是提升I/О效率，而我们进行读取数据的时候存在一个“局部性原理”，也就是说我们使用了一些数据，大概率还会使用它周围的一些数据，因此采用“预读”的机制提前加载，可以减少未来可能的磁盘I/О操作。

• 查询缓存

  查询缓存是提前将查询结果缓存起来，以key:value形式缓存。而缓冲池是缓存物理磁盘上的数据

缓冲池如何读取数据

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

如果我们执行SQL语句的时候更新了缓冲池中的数据，那么这些数据会马上同步到磁盘上吗？

实际上，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上。注意不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用一种叫做check point机制回写，这样做的好处就是提升了数据库的整体性能。

比如，当缓冲池不够用时，需要释放掉一些不常用的页，此时就可以强行采用check point方式，将不常用的脏页写到磁盘上，然后将这些页从缓冲池中释放掉。这里的脏页（dirty page）是指缓冲池中被修改过的页。

查看/设置缓冲池的大小

如果是MyISAM存储引擎，它只缓存索引，不缓存数据，参数为key_buffer_size；

如果是InnoDB存储引擎，可以通过innodb_buffer_pool_size变量来查看缓冲池大小

# 查看缓冲池大小
show variables like 'innodb_buffer_pool_size';
# 设置缓冲池大小
set global innodb_buffer_pool_size=268435456; # 单位字节，这里设置为256MB

多个缓冲池实例

缓冲池本质上是InnoDB向操作系统申请的一块连续的内存空间。在多线程环境下，访问缓冲池中的数据需要加锁处理。所以在缓冲池特别大而且并发访问特别高的情况下，单一的缓冲池可能会影响请求的处理速度，我们可以把它们拆成若干个小的缓冲池，每个缓冲池都称为一个实例。

[server]
innodb_buffer_pool_instances=2

查看缓冲池个数

show variables like 'innodb_buffer_pool_instances';

innodb_buffer_pool_size是总共的缓冲池大小，每个实例的占用空间计算公式如下：

innodb_buffer_pool_size/innodb_buffer_pool_instances

不过并不是说缓冲池越多越好，管理缓冲池也需要性能开销。InnoDB规定：

innodb_buffer_pool_size小于1G时，设置多个实例是无效的