jarycaling's blog

首页学习日记关于网站

MySQL学习笔记

2025-09-27

1.MySQL的体系架构介绍

1.1 连接层

连接层的目的有四个：管理连接、安全认证、权限管理、管理连接。

  • 管理连接：连接层有一个线程池，会对多个申请访问服务器的客户端分配线程，并且对客户端进行连接。

  • 安全认证：连接层对于申请的客户端通过账号密码登身份信息进行认证。

  • 权限管理：连接层对进行申请操作数据的客户端进行该权限的认证。

  • 管理连接：连接层会传递客户端的操作到服务器，并向客户端返回服务器的操作结果。

1.2 服务层

服务层主要负责服务器的SQL接口、查询解析、SQL优化和缓存的处理。

与存储引擎无关的任何操作都在这一层实现，同时负责MySQL所有的逻辑操作。

当执行SQL语句时，服务层会针对该语句建立内部解析树，并对其进行优化。

服务层分为以下的组件：

  1. 系统管理和实用程序：负责备份操作、安全管理、集群管理服务和工具。

  2. SQL接口：接受客户端发送的各种SQL语句，进行优化并调用相关命令的API接口，最后返回结果。

  3. 缓存：MySQL中会有一段缓存空间，每一次执行SQL语句时，都会将结果写入缓存中，如果之后出现相同的语句，需要返回相同的结果时，进行哈希值匹配，如果哈希值返回相同时，服务层将直接从缓存中返回数据。但是值得注意的是：

    1.SQL语句的匹配是十分的严格的，包括字符大小写和空格等，因为字符大小写和空格等一旦更变，相对应的哈希值就会发生变化，导致匹配失效。

    2.如果缓存中存储的数据对应的数据表发生了更变，无论更变是否会影响缓存中的数据结果，缓存中储存的该数据将被全部清空。所以该操作只适合静态表，并不适合有大量更删改操作的表，这样会导致内存的消耗非常的大。

  4. 解析器：会针对输入的SQL语句进行解析，如果SQL语法不对，则会返回相应的提示值，解析器会将SQL语句转化成解析树，区分SQL语句中不同的关键字，并根据关键字进行相应功能API的调用。

  5. 优化器：优化器会对SQL语句进行优化，以保证使用最优的执行计划进行执行。

    1，等价变换策略

     where 5=5 and a>6    =>    where a>6

    2,min（）、max（）函数的优化

    针对于min(),只用查找索引的最左边

    针对于max（），只用查找索引的最右边

    3，提前终止查询

    limit查询，当满足了LIMIT的查询之后，之后的数据就不会再去查询了

    4，in的优化

    针对in，会进行排序，再用区分查找法进行查找 in（2,1,3）-》in(1,2,3)

5,查询条件

针对查询条件的时候，会采用”选取-投影-连接“的方法进行查询

 select id,name from user where gender=1

首选会执行where条件，直接在表中提取满足where条件的行，再会选取id，name等字段，最后将两者连接起来。

6，连接查询

 select * from t1 join t2 where t1.c=10 and t2.d=20

可以从t1中找出c=10的行，再连接到t2表，查询d=20。或者可以从t2中找到d=20的行，再连接到t1表，查询c=10的行。 两种情况都可以，依赖于优化器认为哪一种执行 的效率更高，才会执行哪一个。

6.执行器：对SQL语句进行执行。

1，判断是否有权限执行该SQL语句。

2，如果有，则调用API接口进行执行，没有则返回错误提示。

3，调用存储引擎进行执行。

4，返回查询结果。

1.3 存储引擎层

存储引擎负责对数据进行存储和提取，服务器通过不同存储引擎的API接口的调用来实现。也通过接口来屏蔽不同的存储引擎之间的影响，同时值得注意的是，一个数据库内，可以不同的表使用不同的存储引擎。

1.4 物理存储层

MySQL把文件进行物理存储（磁盘），包括了二进制日志、数据文件、错误日志、慢查询日志、全日志、redo/undo日志。

  • mysql：MySQL自带的核心数据库，里面包含了MySQL的用户账户和权限信息、存储过程、事物的信息、产生的日志信息

  • information_schema:保存着MySQL中存储的其他的数据库的信息。

  • performation_schema：保存着服务器运行过程中的信息状态，服务器的性能指标。

  • sys：通过视图的方式把information_schema和performation——schema结合起来

2.MySQL的运行机制

2.1 C/S通信协议

服务端与客户端通过C/S通信协议来进行连接，在这个连接过程中，这个连接是用半双工的通信方式来进行连接的，一端开始发送消息，另一端需要完整接收全部的消息之后才能进行响应，两者不能同时存在，所以不能去将消息进行区分成单独的小块，也没有办法进行流量控制。

当发送的消息数据过大，超出了max_allowed_packet的参数值，那么服务器会拒绝接收消息并返回错误提示。

2.2 查询缓存

从MySQL 5.7.20起，不再支持查询缓存

查询一张表的数据，如果查询缓存打开，那么MySQL会检查这个查询语句是否能命中缓存中的数据。如果命中，那么MySQL将直接返回缓存中的结果，将不会解析查询语句，更不会执行。

MySQL中的缓存模式是类似于HashMap的数据结构，通过哈希值索引来连接结果数据。哈希值索引根据查询语句、查询表、MySQL的相关版本等进行生成，具有很强的独特性。

缓存的相关字段设置：

  • have_query_cache：该MySQL版本是否支持查询缓存。

  • query_cache_limit：MySQL能缓存的最大查询结果，如果查询结果大于该值，将不会被缓存。

  • query_cache_min_res_unit：缓存分配最小块的大小。如果出现查询结果大于该数值，MySQL会一边进行查询一边进行内存的申请分配。

  • query_cache_size：查询缓存的大小。

  • query_cache_type：设置查询缓存。

  • query_cache_wlock_invalidate：如果某一个表被锁，是否从缓存中返回结果。（不建议，容易出现“脏读”、“幻读”）