MySQL 知识体系

最新推荐文章于 2024-09-11 23:45:36 发布

风灵829

最新推荐文章于 2024-09-11 23:45:36 发布

阅读量515

点赞数 26

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/XianBingMaYong/article/details/137705225

版权

MySQL基本架构

Mysql是一个关系型数据库，由表结构来存储数据与数据之间的关系，同时用sql（Structured query language）翻译过来叫做结构化查询语句来进行数据操作。
sql语句进行操作又分为几个重要的操作类型：

DQL：Data Query Language 数据检索语句 where
DML：Data Manipulation Language 添加、删除、修改语句
DDL：Data definition language 数据库结构操作，create alter等等
DCL：权限控制语句
TCL：事务相关比如commit/savepoint、rollback

总体上，我们可以把MySQL分成三层，跟客户端对接的连接层，真正执行操作的服务层，和跟硬件打交道的存储引擎层。

1 连接层

我们的客户端要连接到MySQL服务器3306端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。

连接有关的变量

2 服务层

连接层会把SQL语句交给服务层，这里面又包含一系列的流程：比如查询缓存的判断、根据SQL调用相应的接口，对我们的SQL语句进行词法和语法的解析（比如关键字怎么识别，别名怎么识别，语法有没有错误等等）。然后就是优化器，MySQL底层会根据一定的规则对我们的SQL语句进行优化，最后再交给执行器去执行。

2.1 解析器

词法解析
将sql语句打碎，转化成一个一个关键单词，然后交给语法解析器去构建语法树，判断语法是否正确。

语法解析
语法解析已经知道每个sql语句的单词了，那么在语法解析的时候，会去检查语法是否正确。表名、列名是否存在、用户是否有操作权限等等

2.2 预处理器

a. 每次执行语句时解析语句的开销更少。通常，数据库应用程序处理大量几乎相同的语句，仅更改 WHERE 查询和删除、 SET 更新和 VALUES 插入等子句中的文字或变量值。
b. 防止 SQL 注入攻击。参数值可以包含未转义的 SQL 引号和定界符。

那如果我写了一个词法和语法都正确的SQL，但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？比如：

select * from xxxx;

解析器可以分析语法，但它怎么知道数据库里面有什么表，表里面有什么字段呢？实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。它会检査生成的解析树，解决解析器无法解析的语义。比如，它会检査表和列名是否存在，检査名字和别名，保证没有歧义。

2.3 优化器

根据上面的流程，我们知道要去执行什么语句，但是具体怎么执行会有很多的方式，比如走哪个索引，你的语句是不是可以优化。优化后会生成一个最终的执行计划，所以这个语句到底怎么走，优化器来决定。

2.4 执行器

根据执行计划，去调用数据存储的地方，也就来到了我们的存储层。执行器去根据表设置的存储引擎，调用不同存储引擎的API接口获取数据。

3 存储引擎层

存储引擎就是我们的数据真正存放的地方，在MySQL里面支持不同的存储引擎。再往下就是内存或者磁盘。

常见的存储引擎： MySQL :: MySQL 8.0 Reference Manual :: 18 Alternative Storage Engines

InnoDB MySQL 8.0默认的存储引擎。InnoDB是一个事务安全(兼容ACID)的MySQL存储引擎，具有提交、回滚和崩溃恢复功能，以保护用户数据。InnoDB行级别的锁(没有升级到更粗粒度的锁)和oracle风格一致的非锁读取提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少常见的基于主键的查询的I/O。为了维护数据的完整性，InnoDB还支持外键引用完整性约束。
MyISAM 这些表占用空间很小。表级锁限制了读/写工作负载的性能，因此它经常用于Web和数据仓库配置中的只读或以读为主的工作负载中。

Innodb架构和原理

MySQL :: MySQL 8.0 Reference Manual :: 17.4 InnoDB Architecture

1 内存结构

1.1 buffer pool

数据最后落盘到我们的磁盘表空间。那么每次检索假如都去磁盘获取，明显性能会比较慢。所以InnoDB为了性能，采用了内存缓存机制，在内存中缓存相应的数据。那么这个内存区间叫做BufferPool。
缓冲池是主内存中的一个区域，在 InnoDB 访问时缓存表和索引数据。缓冲池允许直接从内存访问频繁使用的数据，从而加快处理速度。
InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。操作系统也有页的概念。操作系统的页大小一般是4KB，而在InnoDB里面，这个最小的单位默认是16KB大小。如果要修改这个值的大小，需要清空数据重新初始化服务。
修改数据的时候，也是先写入到buffer pool，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

1.2 change buffer

如果一个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert，Delete，Update）的执行速度。
这一块区域就是Change Buffer。Change Buffer 是 Buffer Pool 的一部分。5.5之前叫Insert Buffer插入缓冲，现在也能支持 delete 和 update。
最后把Change Buffer记录到数据页的操作叫做merge。什么时候发生merge? 有几种情况：在访问这个数据页的时候、或者通过后台线程、或者数据库shut down、 redo log写满时触发。
如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用Change Buffer （写缓冲）。

可以通过调大这个值，来扩大Change的大小，以支持写多读少的业务场景：

SELECT @@innodb_change_buffer_max_size; -- 默认是内存空间的25%

1.3 adaptive hash index

自适应哈希索引是 InnoDB 存储引擎特有的一个功能，它是为了优化某些热点数据的查询性能而自动构建的。自适应哈希索引不同于传统的哈希索引，因为它是自动和动态的：InnoDB 会根据查询模式和数据访问频率自动决定是否构建哈希索引，并且会根据数据的变化和查询模式的变化动态地调整哈希索引。

自适应哈希索引的工作原理是，当 InnoDB 注意到某些索引值被频繁地以等值查询的方式访问时，它会在内存中为这些值建立哈希索引，从而加速后续的等值查询。这个过程是自动的，不需要用户干预。
自适应哈希索引的优点
自动优化：自适应哈希索引会自动构建和维护，不需要用户显式创建或管理。
性能提升：对于某些等值查询，自适应哈希索引可以显著减少查找时间，哈希索引，查询消耗 O(1）
降低对二级索引树的频繁访问资源。
自适应哈希索引也有一些限制和考虑因素
内存消耗：自适应哈希索引完全在内存中构建，因此需要足够的内存资源。在高负载下，它可能会消耗大量的内存。
不可预测性：由于是基于运行时查询模式的，所以哈希索引的存在和组成是不可预测的。
不适用于所有查询：自适应哈希索引主要优化等值查询，对于范围查询或排序操作没有帮助。
hash自适应索引会占用innodb buffer pool；

总的来说，自适应哈希索引是 InnoDB 存储引擎为了提高特定类型查询性能而自动构建的一种内存中的哈希索引结构。它可以根据查询模式和数据访问频率自动调整，以优化数据库的性能。

1.4 redo log buffer

Redo Log也不是每一次都直接写入磁盘，在Buffer Pool里面有一块内存区域(Log
Buffer)专门用来保存即将写入日志文件的数据，它一样可以节省磁盘IO。

查看Log Buffer大小

SHOW VARIABLES LIKE 'innodb_log_buffer_size';--默认16M

需要注意：redo log的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自buffer pool。redo log写入磁盘，不是写入数据文件。
在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush就是把操作系统缓冲区写入到磁盘。
Log buffer写入磁盘的时机，由一个参数控制，默认是1:

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; //RedoLog同步方案
默认设置为1

0 Log buffer将每秒一次地写入log file中，并且log file的flush操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。这个策略的性能是最佳的，但是会存在1s的数据丢失.
1 每次事务提交时MySQL都会把log buffer的数据写入log file，并且刷到磁盘中去。这个策略能保证强一致性，也是InnoDB默认的配置，为的是保证事务的ACID特性
2 每次事务提交时MySQL都会把log buffer的数据写入log file。但是 flush操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush操作。这种策略，如果操作系统出现崩溃，也可能会存在1s的数据丢失