MySQL语句是怎么实现的？需要进行合理的优化分析

2019-06-11

想要深入的了解MySQL，首先要了解MySQL语句是怎么实现的，了解了MySQL里语句的执行过程可以更加快速的分析问题的原因，或者进行合理的优化。

MySQL的架构

MySQL的架构图如下所示，主要由以下几个部分组成：连接器，缓存，分析器，优化器，执行器和存储引擎。

MySQL可以分为server层和存储引擎层，server层包括连接器、分析器、优化器和执行器，主要负责SQL语法的解析，内置函数的实现，触发器，视图等。存储引擎层负责数据的存储和提取，存储引擎是插件式的，MySQL支持的存储引擎就有InnoDB、MyISAM、Memory等。目前，InnoDB是mysql默认的存储引擎。

连接器

连接器负责与客户端建立网络连接、校验用户名密码、校验用户权限、维持和管理连接等。

网络连接建立后，首先验证用户名和密码，用户名和密码验证通过以后连接器会到权限表里查询该用户的权限。之后，这个连接里的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功连接后，再去修改该用户的权限，也不会影响到已经建立好的连接，只有重新建立连接权限才会生效。

MySQL的网络连接采用的是多线程模型，维护一个线程池，每当有一个新的连接请求时，就从空闲的线程池中选择一个线程进行处理。可以使用 show processlist 命令看到当前所建立的所有连接。

+------------+--------------+--------------------+------------------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------------+--------------+--------------------+------------------+---------+-------+-------+------------------+
| 1801071833 | user_name | 10.1.1.1:49788 | test_db | Sleep | 131 | | NULL | 
| 2309292411 | user_name | 10.1.1.1:57642 | test_db | Query | 0 | NULL | show processlist |
复制代码

ID 表示建立连接的线程 ID 。客户端如果一段时间没有动作， Command 一栏就会显示Sleep，表示该连接处于空闲状态。多线程的模型必然存在连接数有限的问题，因此客户端如果太长时间没有动静，连接器就会自动断开，回收线程。

缓存

连接建立后，就可以执行查询语句。查询语句首先会查询缓存中是否该语句的缓存结果，因为MySQL查询语句的执行结果可能会已K-V的形式存储在缓存中，SQL语句做KEY，查询的结果做值。

但是MySQL自带的缓存不建议使用，因为MySQL的缓存失效的非常频繁，只要对一个表有更新，那么这个表上所有的缓存都会失效，因此缓存命中率很低。不如在业务层用redis或者Memcached做缓存来的灵活高效。

分析器

如果缓存没有命中或者没用使用缓存，查询语句就会到达分析器，分析器就是一个编程语言的解析器，解析的是SQL语言。分析器的工作主要分为两个部分：

1 词法分析：词法分析时分析器会分析SQL语句中每个用空格或者逗号分割的字符串，把SELECT关键词提取出来，把语句里的标识为表名的字符串对应到MySQL的表，把每一个column对应到表里的字段。

2 语法分析：语法分析就是整个SQL语句是否满足语法要求，满足则能执行成功，不满足则报错。

优化器

优化器的功能一句话就能描述，却非常重要，决定了查询的性能。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在联表查询时决定用哪一张表关联哪一张表。

执行器

执行器的功能就是调用存储引擎的API存入数据或者取出数据。在调用存储引擎的API之前会先进行权限校验，校验该用户是否有对该表相应的操作权限。存储引擎如果索引没有命中，存储引擎就一条条扫表，直到查到指定的数据，然后返回给server层。如果命中了索引，存储引擎就在索引命中的数据中一条条扫描，直到查到指定的数据。如果索引类型为const类型，那么存储引擎会直接命中，然后返回。

查询语句如何实现

说完了MySQL的架构，我们用一个例子来总结一下一条查询语言是如何实现的

select * from t where id = 123 and name = 'tom'
复制代码

1 客户端与MySQL服务端建立网络连接，连接语句譬如：

mysql -h 127.0.0.1 -P 3306 -u 'name' -p'password!' database_name -A --default-character-set=utf8
复制代码

这条语句指定了MySQL服务器的地址为 127.0.0.1 ，也就是本机，端口号为3306，用户名为name，密码为password。指定库名为database_name，指定默认字符集为utf8。

2 完成连接后，如果开启了MySQL的缓存机制，这时候会先去查询缓存是否命中，如果缓存命中则直接返回缓存中的数据，如果缓存没有命中则继续向下执行。

3 分析器会分析每个词是否是有意义的，比如会解析到 select 是SQL的关键词， t 是表名， id 和 name 是表名中的字段.然后分析SQL的语法是否正常，该条语句可以正常执行。

4 优化器会分析在字段 id 和 name 上是否有索引，应该选择哪个索引。如果表 t 是以 id 为主键，那么分析器就会直接走主键索引了。

5 执行器开始执行前会先校验该用户是否有对该的读权限。通过权限校验后，执行器会调用存储引擎的API查询出这条数据，返回给客户端。

更新语句如何实现

一条更新语句的执行也要经历一条查询语句所要经历的几个阶段，连接器建立连接、分析器分析语法、优化器选择索引，执行器调用存储引擎的API，与查询语句相比，更新语句更为复杂，因为MySQL的InnoDB引擎要保证在数据库机器宕机以后数据不丢失。

同样以一个例子来总结查询语句是如何实现的

update t set name = 'tom' where id = 123
复制代码

1 客户端与MySQL服务端建立网络连接

2 分析器解析出这是一条更新语句

3 优化器选择主键索引，假设以 id 做该表的主键

4 执行器首先查询内存中是否有表 t 中 id 等于123的这一行数据，如果没有则通过存储引擎将这行数据取到内存中

5 执行器修改 name 字段为tom，得到一个新的行

6 存储引擎将新行的数据写入内存，并写redo log日志，此时 redo log 处于 prepare 状态

7 执行器写bin log日志

8 存储引擎修改redo log日志为commit状态

以上步骤就是一个完整的更新语句执行过程，细心的读者会发现更新的数据只写入到内存，还没有持久化到磁盘，mysql异步定期将内存中的数据写入到磁盘，这一过程和操作系统的文件系统读写很像，文件系统中有一个page cache，写文件时先写cache然后用一个独立的进程将数据刷到磁盘。mysql使用了redo log日志，因此即使服务器宕机，数据也不会丢失，可以从redo log日志中恢复。

redo log日志与bin log日志

1 redo log日志是由server层来写，bin log日志由存储引擎来写的；

2 redo log 是物理日志，记录的是“在某个数据页上做了什么修改"，bin log用于记录逻辑操作。在statement模式时，bin log记的就是SQL语句；

3 redo log日志循环写的，空间用完后，要先将数据刷到磁盘，然后清理空间。bin log日志是追加写入的；