1、逻辑架构解析

1.1、服务端处理客户端请求

  1. MySQL是典型的C/S架构，即 Client/Server 架构。
  2. 不论客户端进程和服务器进程采用的是哪种方式进行通信，最后的效果都是：客户端进程向服务器发送一段文本（SQL语句），服务器进程处理后再向客户端进程发送一段文本（响应结果）。

服务器对请求进行的处理如下图：
image.png

具体的逻辑架构如下图所示：
MySQL逻辑架构.png

一个请求过来的大致处理步骤如下：

  1. 客户端通过第三方接口（如JDBC）向MySQL服务器发起一个SQL查询请求。
  2. 当请求到达服务器时，先进入连接层，获取一个线程处理请求以及对请求的认证等。
  3. 随后进入到SQL接口
  4. 接着根据SQL查询缓存中是否有数据，有则直接返回，没有则进入到下一步。（在8.0版本已弃用）
  5. 然后进入解析器，对SQL进行语法、词法等解析，验证请求的SQL是否有误，如有误，则返回错误信息（如常见的：ERROR 1064 (42000): You have an error in your SQL syntax）
  6. 正常来说，解析器执行完成后，即可进入下一层进行查询。但查询的SQL可能不是最优的方法，因此会先进入优化器，对解析后的SQL进行逻辑优化或物理优化。
  7. 随后进入到存储引擎，调用相应的API对底层文件系统进行操作，从磁盘上读取数据，然后将数据加载到内存里。
  8. 将查询后的结果存到缓存中。（8.0版本已弃用）
  9. 返回到SQL接口，将结果返回给客户端。

1.2、连接层

在与客户端成功建立连接后，MySQL服务器会对客户端传过来账号密码做身份认证、获取权限等。

  • 用户名或密码不对时，会返回一个Access denied for user的错误给客户端。
  • 当认证通过后，会从权限表中查出账号拥有的权限与连接关联，之后的权限判断逻辑，都依赖于此时读取到的权限

因此，连接层的主要职责是进行认证、管理连接、获取权限信息。

1.3、服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：如确定表的顺序，是否利用索引等最后生成相应的执行操作。

  • SQL Interface
    • 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface
    • MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL接口。
  • Paeser
    • 在解析器中对SQL语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么久说明这个SQL是不合理的。
    • 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建后语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。
  • Optimizer。
    • 在SQL解析后，查询之前会使用优化器确定SQL语句的执行路径，生成一个执行计划。
    • 这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的犯法来真正的执行查询。并将结果返回给用户。
    • 使用”选取-投影-连接”策略进行查询，如：
      1. SELECT id,name FROM student WHERE gender = '女';
      SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤。
      SELECT查询先根据WHERE语句进行投影，而不是将属性全部取出以后再进行过滤
      将这两个查询条件连接起来生成最终的查询结果。

1.4、引擎层

插件式存储引擎层（Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，我们可以根据自己的实际需要进行选取。

MySQL8默认支持的存储引擎：
image.png

1.5、小结

总的来说分三层：

  1. 连接层：客户端和服务端建立连接
  2. 服务层：对SQL语句进行查询处理，与数据库文件的存储方式无关
  3. 引擎层：与数据库文件打交道，负责数据的存储和读取

    2、SQL的执行流程

    2.1、MySQL中的SQL执行流程

    image.png

1、查询缓存：Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，就会进入到解析器阶段。因为查询缓存往往效率不高，在MySQL8.0后便弃用了这个功能。

2、SQL解析：

  • 分析器会先做 “词法分析”，提取SQL语句的字符串，识别出每个字符串分别是什么，代表着什么。如 ‘SELECT age FROM student’ ，解析器会通过 ‘SELECT’ 字符串识别出这是一个查询语句，后面跟着的 ‘age’ 字符串是列名，而 ‘FROM’ 字符串后面的 ‘student’ 字符串会识别成表名。
  • 接着会进行 “语法分析”。根据上面的词法分析结果，语法分析器会根据语法规则，判断输入的SQL是否 满足SQL语法，如果分析出的语句不正确，会返回一个错误信息给客户端（如：ERROR 1064 (42000): You have an error in your SQL syntax）
  • 如果SQL语句正确，就会生成类似下图的语法树：

image.png

  • 至此，解析器工作完成，接着进入优化器

3、优化器：

  • 在经过解析器后，MySQL就知道你要做什么了，但在执行前还是需要经过优化器的处理。
  • 一条查询可以有多种的执行方式，返回相同的结果。优化器的作用是找到其中最好的执行计划。
  • 如：优化器会决定使用哪个索引；或者在一个语句有多表关联（join）的时候决定各个表的连接顺序；还有表达式简化、子查询转为连接查询等。 ```sql SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id WHERE t1.name = ‘xx’ AND t2.num = 1;

方案一：可以先从t1表里面取出name为xx的记录的id值，再根据id值关联t2表，找出t2表里num为1的记录

方案二：可以先从t2表里面取出num为1的记录的id值，再根据id值关联t1表，找出t1表中name为xxde记录

这两种方案的执行结果都是一样的，但是执行效率会有所不一样，优化器的作用是决定使用那一个方案

  2. - 优化器会在确定好方案后，交出一个 **执行计划**。至此，优化器的工作已完成。
  4. **4、执行器：**
  5. > 到目前为止，还没有真正的去读写表，仅仅知识产出了一个执行计划。于是就进入 **执行器阶段。**
  7. 1. 在执行之前先要判断该用户是否**具备权限**，如果没有，就会返回权限错误。
  8. 1. 如果具有全新，就会打开表继续执行。打开表的时候，执行器会根据表定义的引擎，调用存储引擎API对表进行读写。**存储引擎API只是抽象接口，下面还有个存储引擎层，具体的实现还是要看表选择的存储引擎。**
  10. 总结：<br />SQL语句的执行流程：SQL语句-->查询缓存-->解析器-->优化器-->执行器
  12. <a name="j7rEM"></a>
  13. ## 2.2、执行流程演示
  14. 简单通过profile查看SQL语句在不同阶段的资源消耗，后续章节在具体分析。
  15. <a name="gQlD4"></a>
  16. ### 1、确定profile是否已经开启
  17. ```shell
  18. #通过下列查询确定MySQL是否开启了计划
  19. mysql> select @@profiling;
  20. +-------------+
  21. | @@profiling |
  22. +-------------+
  23. |           0 |
  24. +-------------+
  25. 1 row in set, 1 warning (0.01 sec)
  27. mysql> show variables like 'profiling';
  28. +---------------+-------+
  29. | Variable_name | Value |
  30. +---------------+-------+
  31. | profiling     | OFF   |
  32. +---------------+-------+
  33. 1 row in set (0.01 sec)
  35. mysql> 
  39. # profiling=0代表关闭 1为打开
  40. mysql> set profiling=1;
  41. Query OK, 0 rows affected, 1 warning (0.00 sec)
  43. mysql>

2、执行查询SQL，查看profiles

  1. mysql> use mysql;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> select * from help_category;
  9. mysql> show profiles;
  10. +----------+------------+-----------------------------+
  11. | Query_ID | Duration   | Query                       |
  12. +----------+------------+-----------------------------+
  13. |        1 | 0.00017475 | SELECT DATABASE()           |
  14. |        2 | 0.00112475 | show databases              |
  15. |        3 | 0.00103850 | show tables                 |
  16. |        4 | 0.00032325 | select * from help_category |
  17. |        5 | 0.00025850 | select * from help_category |
  18. +----------+------------+-----------------------------+
  19. 5 rows in set, 1 warning (0.04 sec)
  21. mysql> 
  24. #===============================================================
  26. # 可以根据show profile查询最近一条的query，也可以带上id查询
  27. mysql> show profile;
  28. +--------------------------------+----------+
  29. | Status                         | Duration |
  30. +--------------------------------+----------+
  31. | starting                       | 0.000070 |
  32. | Executing hook on transaction  | 0.000003 |
  33. | starting                       | 0.000007 |
  34. | checking permissions           | 0.000006 |
  35. | Opening tables                 | 0.000033 |
  36. | init                           | 0.000004 |
  37. | System lock                    | 0.000008 |
  38. | optimizing                     | 0.000003 |
  39. | statistics                     | 0.000016 |
  40. | preparing                      | 0.000014 |
  41. | executing                      | 0.000061 |
  42. | end                            | 0.000002 |
  43. | query end                      | 0.000003 |
  44. | waiting for handler commit     | 0.000007 |
  45. | closing tables                 | 0.000007 |
  46. | freeing items                  | 0.000008 |
  47. | cleaning up                    | 0.000008 |
  48. +--------------------------------+----------+
  49. 17 rows in set, 1 warning (0.00 sec)
  51. mysql> show profile for query 1;
  52. +----------------------+----------+
  53. | Status               | Duration |
  54. +----------------------+----------+
  55. | starting             | 0.000061 |
  56. | checking permissions | 0.000006 |
  57. | Opening tables       | 0.000012 |
  58. | init                 | 0.000005 |
  59. | optimizing           | 0.000022 |
  60. | executing            | 0.000008 |
  61. | end                  | 0.000002 |
  62. | query end            | 0.000005 |
  63. | closing tables       | 0.000003 |
  64. | freeing items        | 0.000010 |
  65. | cleaning up          | 0.000042 |
  66. +----------------------+----------+
  67. 11 rows in set, 1 warning (0.00 sec)
  69. mysql> 
  73. # 除了查询执行时间Duration外，还可以带上参数查询其他信息
  74. mysql> show profile cpu, block io for query 5;
  75. +--------------------------------+----------+----------+------------+--------------+---------------+
  76. | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
  77. +--------------------------------+----------+----------+------------+--------------+---------------+
  78. | starting                       | 0.000070 | 0.000064 |   0.000000 |            0 |             0 |
  79. | Executing hook on transaction  | 0.000003 | 0.000002 |   0.000000 |            0 |             0 |
  80. | starting                       | 0.000007 | 0.000007 |   0.000000 |            0 |             0 |
  81. | checking permissions           | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
  82. | Opening tables                 | 0.000033 | 0.000033 |   0.000000 |            0 |             0 |
  83. | init                           | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
  84. | System lock                    | 0.000008 | 0.000007 |   0.000000 |            0 |             0 |
  85. | optimizing                     | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
  86. | statistics                     | 0.000016 | 0.000016 |   0.000000 |            0 |             0 |
  87. | preparing                      | 0.000014 | 0.000014 |   0.000000 |            0 |             0 |
  88. | executing                      | 0.000061 | 0.000062 |   0.000000 |            0 |             0 |
  89. | end                            | 0.000002 | 0.000002 |   0.000000 |            0 |             0 |
  90. | query end                      | 0.000003 | 0.000002 |   0.000000 |            0 |             0 |
  91. | waiting for handler commit     | 0.000007 | 0.000007 |   0.000000 |            0 |             0 |
  92. | closing tables                 | 0.000007 | 0.000006 |   0.000000 |            0 |             0 |
  93. | freeing items                  | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
  94. | cleaning up                    | 0.000008 | 0.000009 |   0.000000 |            0 |             0 |
  95. +--------------------------------+----------+----------+------------+--------------+---------------+
  96. 17 rows in set, 1 warning (0.00 sec)
  98. mysql>