MySQL 整体架构介绍

MySQL 在整体架构上分为 Server 层和存储引擎层。其中 Server 层，包括连接器、查询缓存、分析器、优化器、执行器等，存储过程、触发器、视图和内置函数都在这层实现。数据引擎层负责数据的存储和提取，如 InnoDB、MyISAM、Memory 等引擎。在客户端连接到 Server 层后，Server 会调用数据引擎提供的接口，进行数据的变更。

连接器

负责和客户端建立连接，获取用户权限以及维持和管理连接。

通过 show processlist; 来查询连接的状态。在用户建立连接后，即使管理员改变连接用户的权限，也不会影响到已连接的用户。默认连接时长为 8 小时，超过时间后将会被断开。

简单说下长连接：

优势：在连接时间内，客户端一直使用同一连接，避免多次连接的资源消耗。

劣势：在 MySQL 执行时，使用的内存被连接对象管理，由于长时间没有被释放，会导致系统内存溢出，被系统kill. 所以需要定期断开长连接，或执行大查询后，断开连接。MySQL 5.7 后，可以通过 mysql_rest_connection 初始化连接资源，不需要重连或者做权限验证。

查询缓存

当接受到查询请求时，会现在查询缓存中查询（key/value保存），是否执行过。没有的话，再走正常的执行流程。

但在实际情况下，查询缓存一般没有必要设置。因为在查询涉及到的表被更新时，缓存就会被清空。所以适用于静态表。在 MySQL8.0 后，查询缓存被废除。

分析器

词法分析：

如识别 select，表名，列名，判断其是否存在等。

语法分析：

判断语句是否符合 MySQL 语法。

优化器

确定索引的使用，join 表的连接顺序等，选择最优化的方案。

执行器

在具体执行语句前，会先进行权限的检查，通过后使用数据引擎提供的接口，进行查询。如果设置了慢查询，会在对应日志中看到 rows_examined 来表示扫描的行数。在一些场景下（索引），执行器调用一次，但在数据引擎中扫描了多行，所以引擎扫描的行数和 rows_examined 并不完全相同。

不预先检查权限的原因：如像触发器等情况，需要在执行器阶段才能确定权限，在优化器阶段无法验证。

使用 profiling 查看 SQL 执行过程

打开 profiling 分析语句执行过程：

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|      0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

执行查询语句：

mysql> SELECT * FROM s limit 10;
+------+--------+-----+-----+
| s_id | s_name | age | sex |
+------+--------+-----+-----+
|  1 | z   | 12 |  1 |
|  2 | s   | 14 |  0 |
|  3 | c   | 14 |  1 |
+------+--------+-----+-----+
3 rows in set (0.00 sec)

获取 profiles;

mysql> show profiles;
+----------+------------+--------------------------+
| Query_ID | Duration  | Query          |
+----------+------------+--------------------------+
|    1 | 0.00046600 | SELECT * FROM s limit 10 |
+----------+------------+--------------------------+

mysql> show profile;
+----------------------+----------+
| Status        | Duration |
+----------------------+----------+
| starting       | 0.000069 |
| checking permissions | 0.000008 | 权限检查
| Opening tables    | 0.000018 | 打开表
| init         | 0.000019 | 初始化
| System lock     | 0.000010 | 锁系统
| optimizing      | 0.000004 | 优化查询
| statistics      | 0.000013 |
| preparing      | 0.000094 | 准备
| executing      | 0.000016 | 执行
| Sending data     | 0.000120 |
| end         | 0.000010 |
| query end      | 0.000015 |
| closing tables    | 0.000014 |
| freeing items    | 0.000032 |
| cleaning up     | 0.000026 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

查询具体的语句：

mysql> show profile for query 1;
+----------------------+----------+
| Status        | Duration |
+----------------------+----------+
| starting       | 0.000069 |
| checking permissions | 0.000008 |
| Opening tables    | 0.000018 |
| init         | 0.000019 |
| System lock     | 0.000010 |
| optimizing      | 0.000004 |
| statistics      | 0.000013 |
| preparing      | 0.000094 |
| executing      | 0.000016 |
| Sending data     | 0.000120 |
| end         | 0.000010 |
| query end      | 0.000*4(7"zCb>Cj4(rdbS"j7BB;G:g{*4(4(zsrd^S7BB;G:rg{N74(4(g3r>Cj*^>GR|4(4(zp*3V':>Czp>r'3Vj"ZPbB3VVC*4+3V{*4(4(ZbB3V4(4(kr$
=55%Pjkr$a%иrB;cr$>V34(4(J0S3N7|4(zr'ǖB3jV6_r7^k2'?&><4(4(zsr$#r$j:>Czs>r$r$j.:&j*k"Z4(4(V6gB;BJ0r'^4(4(;jvcB;3ncjV6?3Bj*+cjV6gnc8r"ZV6bBǒnZkcB;nZcnZ3"CB;c>c"C?B;n{"7j*4(J0j4(r*jnZcrkME0>bgk^4+gjj^#vr&77:g"4+&kc#`j^r^jg4(4(4(rZ/"b;50jVz"MJ3;b;v>j&3:v50rB;'R%cj;ǚbnS:Rj54k*>(j4(0bRā{:j^M;j^RJ3V64(rB;>C"V6jJ0Rn3j*b>C"jb>CN74(b50Vzj3nk0VzjZgΣnZ