MySQL深入原理_mysql 如何查看复合索引用到了那几个-CSDN博客

本文链接：https://blog.csdn.net/2302_80742310/article/details/142418743

MySQL深入原理

索引、事务、日志原理、InnoDB引擎、缓存、锁

有4个数据库是属于MySQL自带的系统数据库：

mysql
MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等
information_schema
MySQL 系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。在系统数据库 information_schema 中提供了一些以innodb_sys 开头的表，用于表示内部系统表。
performance_schema
MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
sys
MySQL 系统自带的数据库，这个数据库主要是通过视图的形式把 information_schema 和performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

逻辑架构：

MySQL 的架构共分为两层：Server 层和存储引擎层

Server 层：

负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接池，执行器、优化器、解析器、预处理器、查询缓存等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现。

存储引擎层：
负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的。

SELECT 语句执行原理

连接器
当我们通过客户端访问 MySQL 服务器前，要做的第一步就是需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的

TCP 网络连接建立成功后，服务端与客户端之间会建立一个 session 会话，紧接着会对登录的用户名和密码进行效验，首先会查询自身的用户表信息，判断输入的用户名是否存在，如果存在则会判断输入的密码是否正确。密码正确后，会从连接池中分配一条空闲线程维护当前客户端的连接；如果没有空闲线程，则会创建一条新的工作线程。之后线程会查询用户所拥有的权限，并对其授权，后续 SQL 执行时，都会先判断是否具备相应的权限。

空闲连接在超过最大空闲时长（wait_timeout）之后，连接器会自动将它断开。

一个处于空闲状态的连接被服务端主动断开后，客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到报错。

连接池
Connection Pool，是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请、使用、释放。主要是为了复用线程、管理线程以及限制最大连接数。

当一个客户端尝试与 MySQL 建立连接时，MySQL 内部都会派发一条线程负责处理该客户端接下来的所有工作。

线程的频繁创建和销毁都会耗费大量资源，通过复用线程的方式，不仅能减少开销，还能避免内存溢出等问题。

数据库连接池可以设置最小连接数和最大连接数：

最小连接数：是连接池一直保持的数据库连接，如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费
最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中

查询缓存
如果查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句的哈希值，value 为 SQL 语句查询的结果。

如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

查询缓存往往弊大于利，因为只要有对表的更新，就会导致表上的所有查询缓存被清空。所以，MySQL8.0 版本直接将查询缓存删掉了。

这里说的查询缓存是 server 层的，也就是 MySQL8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer poll。

自我总结：查询缓存在8.0版本被删掉的原因：

首先是这种机制是消耗数据库性能的
其次是，查询缓存是K - V存储的，key是SQL查询语句的哈希值，每当表有一点修改，就会导致表上的查询缓存被清空

解析 SQL
在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由解析器来完成。解析器可以将输入的 SQL 语句转换为计算机可以理解的形式（语法树，Syntax Tree）。

解析器会做如下两件事情：

词法解析：MySQL 会根据输入的字符串识别出关键字出来，构建出 SQL 语法树；
语法解析：根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足语法规则。

当词法分析和语法分析出错时，分析器会抛出异常。比如语法结构出错、出现了无法识别的字符等。

表或者字段不存在，并不是在分析器里做的，而是在预处理阶段完成。

执行 SQL
每条 SQL 语句主要可以分为以下这三个阶段：① prepare ，预处理阶段；② optimize ，优化阶段；③ execute ，执行阶段。

预处理器：检查 SQL 查询语句中的表或者字段是否存在；将 select 中的 .* 符号，扩展为表上的所有字段

优化器：化器会根据语法树制定多个执行计划，然后确定最优的执行计划。

在表里存在多个索引的时候，决定使用哪个索引；
在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器：判断用户权限，然后根据执行计划执行 SQL 语句。

总结一下一条查询 SQL 语句的执行流程：

客户端通过连接器连接 MySQL 服务；
连接成功后向 SQL 接口发送 SQL 语句请求；
SQL 接口接收到 SQL 查询语句会先去缓存查询，如果命中返回给客户端，否则交给解析器；
解析器在拿到 SQL 语句后会判断语法是否正确，正确会生成 SQL 语法树交给优化器，否则报错给客户端；
优化器会根据 SQL 语法树生成一个最优的执行计划交给执行器执行；
执行器拿到执行计划调用存储引擎来获取数据响应给客户端；
完成！！！

UPDATE 语句执行原理

在数据库里面，我们说的 update 操作其实包括了更新、插入和删除。如果大家有看过 MyBatis 的源码，应该知道 Executor 里面也只有 doQuery() 和 doUpdate() 的方法，没有 doDelete() 和 doInsert()。

缓冲池
首先，InnnoDB 的数据都是放在磁盘上的，InnoDB 操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫 Buffer Pool.

下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。

修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB 里面有专门的后台线程把 BufferPool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

BufferPool 是 InnoDB 里面非常重要的一个结构，它的内部又分成几块区域。