全部栏目

首页 操作系统 后端开发 数据库 前端开发 移动端开发
您的当前位置：首页正文

MySQL的查询执行全过程详解

2024-11-27 来源：个人技术集锦

1. MySQL的基础架构

1.1 Server 层

        Server 层是 MySQL 的核心部分，主要负责处理 SQL 查询的逻辑部分，包括解析、优化和执行。

组成部分及功能：

1.2 存储引擎层

        存储引擎层负责具体的数据存储和读取操作。MySQL 的设计允许通过插件式接口支持多种存储引擎。

常见存储引擎及功能：

  1. InnoDB

    • 支持事务，具有行级锁。
    • 采用聚簇索引，适合高并发场景。

  2. MyISAM

    • 不支持事务，但查询性能高。
    • 使用表级锁，适合读多写少的场景。

  3. Memory

    • 数据存储在内存中，速度极快。
    • 适合需要快速临时数据存储的场景。

存储引擎的工作：

  • 执行器通过接口与存储引擎交互。
  • 存储引擎负责完成：
    • 数据的磁盘读写。
    • 索引管理。
    • 数据的事务控制（如果存储引擎支持事务）。

第一步：连接器

        1. 连接器的作用是什么？

        MySQL 的连接器负责处理客户端和数据库之间的连接。就像一个守门人，客户端在访问数据库时，必须先经过它的“认证”和“安排”。

2. 连接数据库的过程是怎样的？

  1. 客户端发送请求：当你用工具（比如命令行、客户端程序）连接数据库时，首先会发送一个“请求连接”到 MySQL 服务器。

    • 比如运行：mysql -h 127.0.0.1 -u root -p。
    • 这一步实际上告诉 MySQL：“嗨，我要登录了！”

  2. 身份验证：
    MySQL 会检查你提供的用户名、密码是否正确，以及这个账号是否有权限访问这台服务器。

    • 用户名、密码的验证是通过 mysql.user 表中的记录完成的。
    • 如果验证失败，就会返回一个错误，比如：Access denied for user 'root'@'localhost'。

  3. 创建连接：
    验证成功后，MySQL 为这次会话分配一个线程，每个客户端连接都会对应一个独立的线程。这个线程会专门负责处理你的请求。

3. 连接的存活时间是多久？

  • 短连接：只执行少量 SQL 语句后，客户端就会主动断开连接。
  • 长连接：客户端和 MySQL 保持连接很久，期间可能会执行很多 SQL。

注意：长连接虽然省去了频繁创建连接的开销，但时间长了会导致内存占用增多。因为 MySQL 在执行过程中会给线程分配内存，线程结束时才释放。如果长连接不断开，内存不会及时回收。
解决方法：

  • 定期断开连接，用新连接代替。
  • 执行 mysql_reset_connection，重置线程状态，释放内存。

4. 连接池是什么？

        为了优化频繁连接带来的开销，很多系统会引入“连接池”。
连接池 的作用是提前创建一批连接，客户端请求时直接复用这些连接，而不是每次重新创建。

第二步：查询缓存

1. 查询缓存是什么？

        查询缓存 是 MySQL 用来提高查询性能的一个功能。
        它的原理很简单：把客户端的查询结果存储起来，下次再遇到同样的查询，直接从缓存中返回结果，而不用执行 SQL 语句了。

你可以把它想象成一个“记忆本”：

  • 上次问“1+1=？”记住答案是“2”。
  • 下次再问相同问题，直接给出“2”，不用重新计算。

2. 查询缓存的工作流程

        假设你在一个数据库中执行了 SQL 查询：

SELECT * FROM users WHERE id = 1;

查询缓存的流程如下：

  1. 收到 SQL 查询：当客户端发送这条查询语句时，MySQL 的连接器接收到了这个请求。

  2. 检查缓存：
    MySQL 会在查询缓存中查找这条 SQL 是否被缓存过：

    • 有缓存：如果缓存中已经存有这条 SQL 的查询结果，就直接返回结果给客户端，不需要执行后续的解析和操作。
    • 没有缓存：如果缓存中没有这条 SQL 的记录，MySQL 就会继续后面的步骤（解析 SQL、执行 SQL 等）。

  3. 存入缓存（如果缓存未命中）：当 SQL 查询执行完成后，MySQL 会把这次查询的结果存入缓存，方便下次查询时直接使用。

3. 查询缓存的优缺点

        查询缓存看起来很有用，但它有一定的局限性：

  1. 优点：

    • 加速查询：查询缓存直接返回结果，减少了 SQL 的执行时间。
    • 减少负载：避免重复执行相同的 SQL，降低服务器压力。

  2. 缺点：

    • 缓存易失效：一旦涉及到缓存的表被更新、插入或删除操作，整个表的缓存都会被清除。
      比如，你执行了以下操作：       
       
      sql
       
      复制代码
       
      UPDATE users SET name = 'John' WHERE id = 1; 
       即使这条更新语句跟缓存的查询无关，查询缓存仍会失效。这种策略导致缓存的使用效率大大降低。
    • 命中率低：如果你的查询语句总是带不同的参数（比如 id 不同），缓存几乎没有用。


4. 查询缓存的现状


        由于查询缓存的限制，MySQL 从 MySQL 8.0 开始，完全移除了查询缓存功能。


5. 查询缓存的替代方案


        虽然查询缓存被移除，但可以用其他方式提高查询性能，比如：


  1.  
    使用应用层缓存：
    

    • 在代码中自己管理查询缓存，比如 Redis、Memcached 等。
    • 应用层缓存可以更灵活地控制缓存粒度和失效策略。
  2.  
    优化 SQL 和索引：
    

    • 设计高效的 SQL 语句，合理使用索引，减少查询的时间成本。


第三步：解析 SQL


1. 什么是 SQL 解析？


        SQL 解析是 MySQL 执行查询前的一个重要步骤。它的作用是把你写的 SQL 语句“翻译”成数据库能够理解和执行的指令。


你可以把 SQL 解析理解为一个“翻译器”，将 SQL 从人类可读的语言，转化为 MySQL 内部的数据结构。


2. SQL 解析的步骤是什么？


  1.  
    词法分析（Lexical Analysis）
    

    • 作用：把 SQL 语句中的关键字、表名、列名、符号等分割成“最小单元”，并识别它们的类型。
       例如，SQL 语句：       
      SELECT name FROM users WHERE id = 1;
       会被拆解成以下几个单元：

      • SELECT（关键字）
      • name（列名）
      • FROM（关键字）
      • users（表名）
      • WHERE（关键字）
      • id（列名）
      • =（符号）
      • 1（常量）
  2.  
    语法分析（Syntax Analysis）
    

    •  
      作用：检查 SQL 语句的语法是否正确，确保语句能被理解。
       比如：
       
      SELECT FROM users;
       
      这句语法上是错误的，因为缺少列名，语法解析会报错。
       
    •  
      MySQL 在这一步会使用一个“语法树”来表示语句的结构。
       比如，语句 SELECT name FROM users 的语法树可能是这样的：
       
      SELECT
 ├── name
 └── FROM users

       
  3.  
    语义检查（Semantic Check）
    

    • 作用：检查 SQL 语句中涉及的表、字段是否存在，是否有权限访问这些对象。

      • 如果语句中引用了不存在的表或字段，语义检查会报错： 
        ERROR 1146 (42S02): Table 'test_db.nonexistent_table' doesn't exist
         
      • 如果用户没有权限访问某个表，也会报错： 
        ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'test_db'
         
  4.  
    生成执行计划（初步）
    

    • 作用：生成一个初步的执行计划，这个计划会告诉 MySQL 后续如何去执行语句。
    • 这一步会记录查询的目标表、目标列等基本信息。


3. 为什么解析是必要的？


        解析的目的是让 MySQL 确保你写的 SQL 是正确的，并且能够翻译成数据库能理解的形式。
 没有解析，就像你对一个外国人说话却没有翻译员，中间完全对不上。


4. SQL 优化器与解析的关系


  • 解析完成后，SQL 会被交给 MySQL 的优化器进一步处理。
  • 优化器会在执行前对语句做更多的优化，比如选择最佳的索引、确定表的连接顺序等（这个属于下一步 “执行 SQL” 的内容）。


第四步：执行SQL


        我们分为三部分详细讲解 MySQL 执行 SQL 的过程：预处理阶段 (Prepare)、优化阶段 (Optimize) 和 执行阶段 (Execute)。深呼吸，慢慢讲清楚每个步骤。


第一部分：Prepare 阶段（预处理阶段）


1. 什么是预处理？


        在这一步，MySQL 会根据解析器生成的语法树，进一步检查和转换 SQL 语句，确保它符合语义规则，并为执行做好准备。预处理可以理解为“检查细节”和“准备数据”的阶段。


2. 预处理的具体内容


  1.  
    权限校验：
     检查当前用户是否有权限访问所涉及的表和字段。
    

    • 比如，当查询 SELECT name FROM users; 时，MySQL 会检查你是否有对 users 表的 SELECT 权限。
       如果没有权限，会返回错误：
    ERROR 1142 (42000): SELECT command denied to user 'user1'@'localhost' for table 'users'
     
  2.  
    表和字段的存在性检查：
     MySQL 会检查 SQL 中引用的表、字段是否存在。例如：
     
    SELECT age FROM users;
     
    如果 users 表中没有 age 字段，预处理阶段就会报错：
     
    ERROR 1054 (42S22): Unknown column 'age' in 'field list'
     
  3.  
    列名解析和别名处理：
     如果 SQL 中有别名，MySQL 会将它替换为真实的列名。
     例如：
     
    SELECT u.name AS username FROM users u;
     
    在这一步，username 会被映射到表 users 的字段 name。
     
  4.  
    查询结构校验：
     如果查询中涉及多个表，MySQL 会检查它们的关联是否合理。
     比如，如果表之间缺少 JOIN 条件，MySQL 会警告或者报错。
     


第二部分：Optimize 阶段（优化阶段）


1. 什么是优化？


        优化器的任务是生成一份“最优执行计划”，选择高效的方式执行 SQL 语句。
         数据库在处理查询时，往往有多种执行方式，优化器会选择成本最低的那种方式。


2. 优化器的具体工作


  1.  
    选择访问路径（索引的选择）：
     如果表有多个索引，优化器会决定使用哪个索引。
    

    • 比如，你查询： 
      SELECT * FROM users WHERE id = 10;
       优化器会判断 id 字段上是否有索引，如果有，会直接使用索引查询，而不是全表扫描。
  2.  
    确定表的连接顺序：
     当查询涉及多张表时，优化器会决定表的连接顺序。
    

    • 比如查询： 
      SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
       如果 users 表比 orders 表小，优化器可能会优先扫描 users 表，以减少查询的开销。
  3.  
    优化子查询：
     如果 SQL 中有子查询，优化器会尝试将子查询“改写”为更高效的连接操作（JOIN）。
     比如：
     
    SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
     
    可能会被改写为：
     
    SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;
     
  4.  
    生成执行计划：
     优化器会根据分析结果生成一份执行计划，这是 MySQL 用来执行 SQL 的具体方案。
    

    • 你可以通过 EXPLAIN 命令查看优化器生成的执行计划： 
      EXPLAIN SELECT * FROM users WHERE id = 10;
       
       


第三部分：Execute 阶段（执行阶段）


1. 什么是执行阶段？


        在这一阶段，MySQL 会按照优化器生成的执行计划，逐步访问数据并返回结果。
         这一阶段是真正执行查询的地方，涉及到表的存储引擎。


2. 执行的具体流程


  1.  
    调用存储引擎接口：
     MySQL 会根据表的存储引擎（比如 InnoDB 或 MyISAM），调用对应引擎的 API 来读取数据。
    

    • 如果是 SELECT 查询，存储引擎会返回匹配的行。
    • 如果是 UPDATE 或 DELETE，存储引擎会修改或删除行数据。
  2.  
    过滤数据：
     如果查询有 WHERE 条件，MySQL 会过滤掉不符合条件的行。
     
  3.  
    排序和分组：
     如果查询有 ORDER BY 或 GROUP BY 子句，MySQL 会对结果集进行排序或分组。
    

    • 排序时，MySQL 可能会用到内存或磁盘（如果数据量太大）。
  4.  
    返回结果：
     MySQL 将最终的查询结果返回给客户端。
    

    • 对于 SELECT 查询，返回的结果是匹配的行。
    • 对于 INSERT、UPDATE 或 DELETE，返回的是影响的行数。


总结：执行 SQL 的全过程


  • Prepare 阶段：检查权限、表结构、语义规则，并做好查询前的准备。
  • Optimize 阶段：生成最优的执行计划，确定索引、连接顺序等细节。
  • Execute 阶段：根据执行计划，调用存储引擎接口读取或修改数据，并返回结果。


MySQL 查询的执行全过程总结


·MySQL 执行一条 SQL 查询的全过程可以分为以下四个主要步骤：


第一步：连接器


作用：负责建立与客户端的连接，并管理用户权限。


  • 连接建立：客户端通过 TCP/IP 连接 MySQL，连接器验证用户的用户名和密码。
  • 权限验证：根据用户的权限，决定是否允许执行操作。
  • 连接管理：维护连接状态。如果客户端长时间无操作，连接器可能会断开连接。


第二步：查询缓存


作用：在执行 SQL 之前，检查查询缓存中是否已有结果。


  • 缓存命中：如果缓存中有结果，直接返回，不再执行后续步骤。
  • 缓存未命中：继续执行 SQL 并更新缓存（如果查询缓存已开启且查询结果符合缓存规则）。
    注意：MySQL 8.0 已移除查询缓存功能。


第三步：解析 SQL


作用：对 SQL 语句进行语法和语义检查，为后续执行做好准备。


  • 词法分析：将 SQL 分解成最小单元，识别关键字、表名、字段等。
  • 语法分析：检查 SQL 语句的结构是否正确，生成语法树。
  • 语义检查：确认表和字段是否存在、用户是否有权限访问。
  • 生成初步执行计划：标记目标表、目标字段等执行信息。


第四步：执行 SQL


作用：最终执行 SQL 语句，分为三个阶段：


  1.  
    Prepare 阶段（预处理阶段）：
    

    • 检查权限、表和字段的存在性。
    • 对列名、别名进行解析。
    • 确认查询结构是否合理。
  2.  
    Optimize 阶段（优化阶段）：
    

    • 优化器选择最佳执行路径，包括索引选择、表连接顺序、子查询优化等。
    • 生成最优执行计划，可以通过 EXPLAIN 查看。
  3.  
    Execute 阶段（执行阶段）：
    

    • 按执行计划调用存储引擎接口访问数据。
    • 过滤不符合条件的行，完成排序或分组操作。
    • 最终将结果返回给客户端。


总结核心要点


  1. 连接器管理连接和权限。
  2. 查询缓存用于加速常用查询（MySQL 8.0 后已移除）。
  3. SQL 解析检查语法、语义并生成初步执行计划。
  4. SQL 执行细分为预处理、优化、实际执行三个阶段，每一步都至关重要。


        
        
            
            
            
    
显示全文