MySQL的查询实行全过程详解

1. MySQL的根本架构

1.1 Server 层

        Server 层是 MySQL 的焦点部分，告急负责处置惩罚 SQL 查询的逻辑部分，包罗剖析、优化和实行。
构成部分及功能：

•         毗连处置惩罚器 (Connection Handler)
  
  
  
  • 负责管理客户端与 MySQL 的毗连，包罗身份认证、权限验证。
    
  • 为每个客户端分配线程，管理资源。
    
  
  
•         查询缓存 (Query Cache)
  
  
  
  • 如果同一 SQL 已经实行过且结果没有逾期，直接返回缓存的结果，进步服从。
    
  • 留意：MySQL 8.0 以后已经移除了查询缓存功能。
    
  
  
•         剖析器 (Parser)
  
  
  
  • 将 SQL 查询转化为内部语法树。
    
  • 如果查询语句存在语法错误，剖析器会抛堕落误。
    
  
  
•         查询优化器 (Optimizer)
  
  
  
  • 决定 SQL 查询的实行操持，包罗选择符合的索引、毗连序次等。
    
  • 优化器的目的是让查询只管高效。
    
  
  
•         实行器 (Executor)
  
  
  
  • 按照优化器天生的实行操持，一步步与存储引擎交互，完成数据的读取或写入。
    
  
  

1.2 存储引擎层

        存储引擎层负责具体的数据存储和读取操纵。MySQL 的操持允许通过插件式接口支持多种存储引擎。
常见存储引擎及功能：

•         InnoDB
  
  
  
  • 支持事件，具有行级锁。
    
  • 接纳聚簇索引，恰当高并发场景。
    
  
  
•         MyISAM
  
  
  
  • 不支持事件，但查询性能高。
    
  • 使用表级锁，恰当读多写少的场景。
    
  
  
•         Memory
  
  
  
  • 数据存储在内存中，速率极快。
    
  • 恰当须要快速暂时数据存储的场景。
    
  
  

存储引擎的工作：

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

第一步：毗连器

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

        MySQL 的毗连器负责处置惩罚客户端和数据库之间的毗连。就像一个守门人，客户端在访问数据库时，必须先颠末它的“认证”和“安排”。
2. 毗连数据库的过程是怎样的？

•         客户端发送哀求：当你用工具（好比下令行、客户端步调）毗连数据库时，起首会发送一个“哀求毗连”到 MySQL 服务器。
  
  
  
  • 好比运行：mysql -h 127.0.0.1 -u root -p。
    
  • 这一步现实上告诉 MySQL：“嗨，我要登录了！”
    
  
  
•         身份验证：
          MySQL 会查抄你提供的用户名、暗码是否准确，以及这个账号是否有权限访问这台服务器。
  
  
  
  • 用户名、暗码的验证是通过 mysql.user 表中的纪录完成的。
    
  • 如果验证失败，就会返回一个错误，好比：Access denied for user 'root'@'localhost'。
    
  
  
•         创建毗连：
          验证乐成后，MySQL 为这次会话分配一个线程，每个客户端毗连都会对应一个独立的线程。这个线程会专门负责处置惩罚你的哀求。
  

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

• 短毗连：只实行少量 SQL 语句后，客户端就会自动断开毗连。
  
• 长毗连：客户端和 MySQL 保持毗连很久，期间大概会实行很多 SQL。
  

留意：长毗连固然省去了频仍创建毗连的开销，但时间长了会导致内存占用增多。由于 MySQL 在实行过程中会给线程分配内存，线程竣事时才开释。如果长毗连不绝开，内存不会及时接纳。
办理方法：

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

4. 毗连池是什么？

        为了优化频仍毗连带来的开销，很多体系会引入“毗连池”。
毗连池 的作用是提前创建一批毗连，客户端哀求时直接复用这些毗连，而不是每次重新创建。
第二步：查询缓存

1. 查询缓存是什么？

        查询缓存 是 MySQL 用来进步查询性能的一个功能。
        它的原理很简单：把客户端的查询结果存储起来，下次再碰到同样的查询，直接从缓存中返回结果，而不消实行 SQL 语句了。
你可以把它想象成一个“影象本”：

• 前次问“1+1=？”记取答案是“2”。
  
• 下次再问雷同标题，直接给出“2”，不消重新盘算。
  

2. 查询缓存的工作流程

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

2. SELECT * FROM users WHERE id = 1;

复制代码

查询缓存的流程如下：

•         收到 SQL 查询：当客户端发送这条查询语句时，MySQL 的毗连器吸收到了这个哀求。
  
•         查抄缓存：
          MySQL 会在查询缓存中查找这条 SQL 是否被缓存过：
  
  
  
  • 有缓存：如果缓存中已经存有这条 SQL 的查询结果，就直接返回结果给客户端，不须要实行后续的剖析和操纵。
    
  • 没有缓存：如果缓存中没有这条 SQL 的纪录，MySQL 就会继续背面的步调（剖析 SQL、实行 SQL 等）。
    
  
  
•         存入缓存（如果缓存未掷中）：当 SQL 查询实行完成后，MySQL 会把这次查询的结果存入缓存，方便下次查询时直接使用。
  

3. 查询缓存的优缺点

        查询缓存看起来很有效，但它有肯定的范围性：

•         优点：
  
  
  
  • 加快查询：查询缓存直接返回结果，淘汰了 SQL 的实行时间。
    
  • 淘汰负载：克制重复实行雷同的 SQL，低落服务器压力。
    
  
  
•         缺点：
  
  
  
  • 缓存易失效：一旦涉及到缓存的表被更新、插入或删除操纵，整个表的缓存都会被打扫。
                    好比，你实行了以下操纵：       
    1. [/code]                sql
    2.                 复制代码
    3.                 UPDATE users SET name = 'John' WHERE id = 1;
    4.                 纵然这条更新语句跟缓存的查询无关，查询缓存仍会失效。这种战略导致缓存的使用服从大大低落。
    5. [*][b]掷中率低[/b]：如果你的查询语句总是带差别的参数（好比 id 差别），缓存险些没有效。
    6. [/list]
    7. [/list][size=4][b]4. 查询缓存的现状[/b][/size]
    9.         由于查询缓存的限定，MySQL 从 [b]MySQL 8.0[/b] 开始，完全移除了查询缓存功能。
    10. [size=4][b]5. 查询缓存的替换方案[/b][/size]
    12.         固然查询缓存被移除，但可以用其他方式进步查询性能，好比：
    13. [list=1]
    14. [*]        [b]使用应用层缓存[/b]：
    15. [list]
    16. [*]在代码中本身管理查询缓存，好比 Redis、Memcached 等。
    17. [*]应用层缓存可以更机动地控制缓存粒度和失效战略。
    18. [/list]
    19. [*]        [b]优化 SQL 和索引[/b]：
    20. [list]
    21. [*]操持高效的 SQL 语句，公道使用索引，淘汰查询的时间本钱。
    22. [/list]
    23. [/list][size=5][b]第三步：剖析 SQL[/b][/size]
    25. [size=4][b]1. 什么是 SQL 剖析？[/b][/size]
    27.         SQL 剖析是 MySQL 实行查询前的一个告急步调。它的作用是[b]把你写的 SQL 语句“翻译”成数据库可以或许明白和实行的指令[/b]。
    28. 你可以把 SQL 剖析明白为一个“翻译器”，将 SQL 从人类可读的语言，转化为 MySQL 内部的数据结构。
    29. [size=4][b]2. SQL 剖析的步调是什么？[/b][/size]
    31. [list=1]
    32. [*]        [b]词法分析（Lexical Analysis）[/b]
    33. [list]
    34. [*][b]作用[/b]：把 SQL 语句中的关键字、表名、列名、符号平分割成“最小单位”，并辨认它们的范例。
    35.                 比方，SQL 语句：                [code]
    36. SELECT name FROM users WHERE id = 1;

    复制代码
            会被拆解成以下几个单位：
    
    
    
    • SELECT（关键字）
      
    • name（列名）
      
    • FROM（关键字）
      
    • users（表名）
      
    • WHERE（关键字）
      
    • id（列名）
      
    • =（符号）
      
    • 1（常量）
      
    
    
  
  
•         语法分析（Syntax Analysis）
  
  
  
  •                 作用：查抄 SQL 语句的语法是否准确，确保语句能被明白。
                    好比：
           
    2. SELECT FROM users;

    复制代码
            这句语法上是错误的，由于缺少列名，语法剖析会报错。
    
  •                 MySQL 在这一步会使用一个“语法树”来表现语句的结构。
                    好比，语句 SELECT name FROM users 的语法树大概是如许的：
           
    2. SELECT
    3. ├── name
    4. └── FROM users

    复制代码
  
  
•         语义查抄（Semantic Check）
  
  
  
  • 作用：查抄 SQL 语句中涉及的表、字段是否存在，是否有权限访问这些对象。
    
    
    
    • 如果语句中引用了不存在的表或字段，语义查抄会报错：               
      2. ERROR 1146 (42S02): Table 'test_db.nonexistent_table' doesn't exist

      复制代码
    • 如果用户没有权限访问某个表，也会报错：               
      2. ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'test_db'

      复制代码
    
    
  
  
•         天生实行操持（开端）
  
  
  
  • 作用：天生一个开端的实行操持，这个操持会告诉 MySQL 后续怎样去实行语句。
    
  • 这一步会纪录查询的目的表、目的列等根本信息。
    
  
  

3. 为什么剖析是须要的？

        剖析的目的是让 MySQL 确保你写的 SQL 是准确的，而且可以或许翻译成数据库能明白的情势。
没有剖析，就像你对一个外国人语言却没有翻译员，中心完全对不上。
4. SQL 优化器与剖析的关系

• 剖析完成后，SQL 会被交给 MySQL 的优化器进一步处置惩罚。
  
• 优化器会在实行前对语句做更多的优化，好比选择最佳的索引、确定表的毗连序次等（这个属于下一步 “实行 SQL” 的内容）。
  

第四步：实行SQL

        我们分为三部分具体解说 MySQL 实行 SQL 的过程：预处置惩罚阶段 (Prepare)、优化阶段 (Optimize) 和 实行阶段 (Execute)。深呼吸，渐渐讲清楚每个步调。
第一部分：Prepare 阶段（预处置惩罚阶段）

1. 什么是预处置惩罚？
        在这一步，MySQL 会根据剖析器天生的语法树，进一步查抄和转换 SQL 语句，确保它符合语义规则，并为实行做好准备。预处置惩罚可以明白为“查抄细节”和“准备数据”的阶段。
2. 预处置惩罚的具体内容

•         权限校验：
          查抄当前用户是否有权限访问所涉及的表和字段。
  
  
  
  • 好比，当查询 SELECT name FROM users; 时，MySQL 会查抄你是否有对 users 表的 SELECT 权限。
                    如果没有权限，会返回错误：
    
  2. ERROR 1142 (42000): SELECT command denied to user 'user1'@'localhost' for table 'users'

  复制代码
  
  
•         表和字段的存在性查抄：
          MySQL 会查抄 SQL 中引用的表、字段是否存在。比方：
  
  2. SELECT age FROM users;

  复制代码
  如果 users 表中没有 age 字段，预处置惩罚阶段就会报错：
  
  2. ERROR 1054 (42S22): Unknown column 'age' in 'field list'

  复制代码
•         列名剖析和别名处置惩罚：
          如果 SQL 中有别名，MySQL 会将它更换为真实的列名。
          比方：
  
  2. SELECT u.name AS username FROM users u;

  复制代码
  在这一步，username 会被映射到表 users 的字段 name。
  
•         查询结构校验：
          如果查询中涉及多个表，MySQL 会查抄它们的关联是否公道。
          好比，如果表之间缺少 JOIN 条件，MySQL 会告诫大概报错。
  

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

1. 什么是优化？
        优化器的任务是天生一份“最优实行操持”，选择高效的方式实行 SQL 语句。
        数据库在处置惩罚查询时，每每有多种实行方式，优化器会选择本钱最低的那种方式。
2. 优化器的具体工作

•         选择访问路径（索引的选择）：
          如果表有多个索引，优化器会决定使用哪个索引。
  
  
  
  • 好比，你查询：       
    2. SELECT * FROM users WHERE id = 10;

    复制代码
            优化器会判断 id 字段上是否有索引，如果有，会直接使用索引查询，而不是全表扫描。
    
  
  
•         确定表的毗连序次：
          当查询涉及多张表时，优化器会决定表的毗连序次。
  
  
  
  • 好比查询：       
    2. SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

    复制代码
            如果 users 表比 orders 表小，优化器大概会优先扫描 users 表，以淘汰查询的开销。
    
  
  
•         优化子查询：
          如果 SQL 中有子查询，优化器会实验将子查询“改写”为更高效的毗连操纵（JOIN）。
          好比：
  
  2. SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

  复制代码
  大概会被改写为：
  
  2. SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;

  复制代码
•         天生实行操持：
          优化器会根据分析结果天生一份实行操持，这是 MySQL 用来实行 SQL 的具体方案。
  
  
  
  • 你可以通过 EXPLAIN 下令查察优化器天生的实行操持：       
    1. EXPLAIN
    2. SELECT * FROM users WHERE id = 10;

    复制代码
           
    
  
  

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

1. 什么是实行阶段？
        在这一阶段，MySQL 会按照优化器天生的实行操持，渐渐访问数据并返回结果。
        这一阶段是真正实行查询的地方，涉及到表的存储引擎。
2. 实行的具体流程

•         调用存储引擎接口：
          MySQL 会根据表的存储引擎（好比 InnoDB 或 MyISAM），调用对应引擎的 API 来读取数据。
  
  
  
  • 如果是 SELECT 查询，存储引擎会返回匹配的行。
    
  • 如果是 UPDATE 或 DELETE，存储引擎会修改或删除行数据。
    
  
  
•         过滤数据：
          如果查询有 WHERE 条件，MySQL 会过滤掉不符合条件的行。
  
•         排序和分组：
          如果查询有 ORDER BY 或 GROUP BY 子句，MySQL 会对结果集举行排序或分组。
  
  
  
  • 排序时，MySQL 大概会用到内存或磁盘（如果数据量太大）。
    
  
  
•         返回结果：
          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 语句，分为三个阶段：

•         Prepare 阶段（预处置惩罚阶段）：
  
  
  
  • 查抄权限、表和字段的存在性。
    
  • 对列名、别名举行剖析。
    
  • 确认查询结构是否公道。
    
  
  
•         Optimize 阶段（优化阶段）：
  
  
  
  • 优化器选择最佳实行路径，包罗索引选择、表毗连序次、子查询优化等。
    
  • 天生最优实行操持，可以通过 EXPLAIN 查察。
    
  
  
•         Execute 阶段（实行阶段）：
  
  
  
  • 按实行操持调用存储引擎接口访问数据。
    
  • 过滤不符合条件的行，完成排序或分组操纵。
    
  • 终极将结果返回给客户端。
    
  
  

总结焦点要点

• 毗连器管理毗连和权限。
  
• 查询缓存用于加快常用查询（MySQL 8.0 后已移除）。
  
• SQL 剖析查抄语法、语义并天生开端实行操持。
  
• SQL 实行细分为预处置惩罚、优化、现实实行三个阶段，每一步都至关告急。