揭开MySQL的秘密：从查询解析到执行引擎的全面探秘与优化秘诀

mysql执行流程

MySQL总体可以分为Server层和存储引擎层

Server层含有 连接器、分析器、优化器、执行器等

存储引擎个人理解是，存储的物理介质？支持InnoDB、MyISAM、Memory等多个存储引擎

建立连接

按照正常的使用流程

在使用之前，程序作为客户端首先需要通过连接器连接到MYSQL的数据库当中，即经典的

mysql -h$ip -P$port -u$user -p

输入后，客户端与服务器建立TCP连接，连接器确认身份，整个确认的过程可以分为以下多步：

1. TCP三次握手建立连接成功

2. 服务器向客户端发送初始握手包，其中包含服务器版本，认证数据等信息，（详见附）

3. 客户端向服务器发送响应响应包，其中包含用户名 & 加密密码，使用sha1 & 盐值进行加密

4. 服务器收到密码，在mysql.user表中查找对应的主机及用户名。并且将存储在数据库中的用户密码再次加密后与用户所发送的密码进行比较。（存储在服务器中的密码本身是经过加密后的，比较之时，需要再次进行加密）

5. 服务器比较后，根据验证情况返回：

   • 用户名密码错误：返回错误**“Access denied for user”** ，客户端程序结束执行

   • 用户名密码正确：返回用户权限（在上一步验证密码时，如果验证通过，会同时返回对应用户的权限）

在成功验证，读取权限之后，MySQL会为此创建一个会话上下文，每个连接都会有一个对应的会话上下文信息，其中包含该连接的所有状态信息，包括权限信息。

而在此次会话当中，需要的所有状态信息都将从这次缓存中读取。这也是为什么一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。

综上，成功通过连接器连接上了数据库。连接之后，可通过show processlist命令，查看MySQL当前所有的服务连接。processlist是位于information_schema的表，记录连接状态信息。

mysql> SHOW PROCESSLIST;
+----+-------+------------------+---------+---------+-------+-------------+-----------------------+
| Id | User  | Host             | db      | Command | Time  | State       | Info                  |
+----+-------+------------------+---------+---------+-------+-------------+-----------------------+
|  1 | root  | localhost        | testdb  | Sleep   | 10    |             | NULL                  |
|  2 | alice | 192.168.1.50:34567 | sales   | Query   | 0     | Sending data| SELECT * FROM orders  |
|  3 | bob   | 192.168.1.51:34568 | NULL    | Sleep   | 5     |             | NULL                  |
|  4 | carol | 192.168.1.52:34569 | hr      | Query   | 20    | Locked      | UPDATE employees SET salary = 5000 WHERE id = 1 |
+----+-------+------------------+---------+---------+-------+-------------+-----------------------+

Command和State记录当前连接的状态和信息。

MySQL服务器中有一个专门的后台线程管理连接和资源，他会定期运行，并检测所有连接是否健康。负责清理空闲连接，释放资源，和其他定期维护任务

如上方bob的连接状态是Sleep，如果此客户端太久没有操作，连接器就会将其断开，以释放服务器资源。具体时间由参数wait_timeout控制，默认为8小时。

此处所说的断开是服务器端单向断开连接，而客户端不会主动断开连接。假如在服务器端断开连接之后，客户端会遇到“Lost connection to MySQL server during query”错误，并需重新连接来继续操作。

MySQL中连接还区分有 长连接 & 短连接。此处的长连接指的是，使用同一个连接来处理客户端的持续请求。短连接则是每次执行完少量语句则断开，并再次新建连接。

在实际的使用过程当中，建立连接的过程事件开销比较大，所以相比之下，长连接更占优势。

但是MYSQL具有一个特点：将执行过程中临时使用的内存管理、存储在会话上下文中。这些资源的释放时机为连接断开。这就导致了一个问题：长时间使用同一长连接，从而导致MySQL占用内存急剧增大。

而卖你对这一问题，可以有以下两种解决方法：

1. 定期断开长连接，依据查询的次数 & 查询数据的多少，合理分配断开的时机。
2. 在执行大量操作后，通过执行mysql_reset_connection来初始化连接资源。相当于将该连接重置为服务器 & 客户端刚连接成功时状态。(mysql-version >= 5.7)

查询缓存

查询缓存以k-v的形式，将执行过的语句 & 结果缓存在内存中。

MySQL在执行语句的时候，会先到查询缓存观察是否有该值，若有则直接返回。若无则进入之后步骤，并在语句处理完毕之后，将该执行结果放入查询缓存当中。（缓存的基本用法）

也就是说，若执行的sql语句执行频率高，则不需要执行后面复杂操作便可直接返回结果——效率高

但是，当数据需要更新时，该数据的所在表的所有查询缓存都会被清空。这一特点也就决定了，对于修改频率不高的静态表，查询缓存适合。而对于频繁更新的表，此查询起到的作用微乎其微。

而且，在当前缓存相关中间件完善程度较高的情况下，查询缓存相比逊色。于是在MySQL8.0以上的版本，此功能被官方移除了。

分析器

若MySQL版本大于8.0 或 查询缓存没有命中，则从此处真正开始执行语句了。

假设现在输入了这么一条语句

select * from T where ID = 1

分析器中的词法分析器根据预定义的SQL语法规则，将原语句拆分为多个词法单元（Token）。

该此法分析器由有限状态机实现，它最鲜明的特点是在识别时，根据当前状态，转移至下一状态

• 若当前状态合法，进行下一Token的识别

• 若当前状态不合法，直接返回错误

在进行识别的后，每个token都会包含以下三字段：

1. token在sql语句中的类型 （关键字 标识符 操作符 etc）

2. token的具体值 (select T ID)

3. token在原始输入中的位置，包括行号和列号

回到上文，若状态不合法，会收到报错如下:

mysql> del * from T

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘del’ at line 1

上方有限状态机的特点决定了，语法错误时会提示且仅提示第一个出现错误的位置。

优化器

经过分析器后，MySQL分析出语句的作用。在真正执行前，需经过优化器处理

所谓优化器便是在具有多个索引时或语句有多表关联时（join），根据不同方案的执行顺序，决定出选择使用的最终方案。

执行器

终于，MySQL通过分析器知道了做什么(what)，通过优化器知道怎么做(how)。

在执行前，执行器会检查该用户（客户端）是否有执行的权限。如果没有，就会返回以下错误：

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，则进入该表继续执行。进入表时，会根据表的引擎（InnoDB等存储引擎）定义，使用对应的接口API。

此处以InnoDB为例：

• 若没有索引，调用InnoDB引擎接口取表的第一行，判断ID值是不是10。若不是则跳过，遍历下一行；若是则将这行存在结果集中。继续调用引擎接口，重复相同的判断逻辑，直至表末尾
• 若有索引，调用InnoDB引擎接口取满足条件的第一行

将遍历完毕后的结果集数据返回给客户端

至此，语句执行完成