此文是极客时间 Mysql实战45讲 (opens new window)的读书笔记,仅供学习,切勿用于商业。

# 基础架构

在MySql中一条语句是怎么执行的呢?请看下图:

MySqlExecFlow

MySql总体分为两部分:

  • Server层:连接器,查询缓存,分析器,优化器,执行器。涵盖MySql的大多数核心服务功能,以及所有内置函数,比如日期,时间,数学和加密函数等,所有跨存储引擎功能在这一层实现,比如存储过程,触发器,视图等。不同的存储引擎共用一个Server层。
  • 存储引擎层:负责数据的存储和提取,架构模式是插件式的,支持多个存储引擎。最常用的是InnoDB,它从MySql 5.5.5开始成为默认存储引擎。可以在使用create table时使用engine=InnoDB来指定存储引擎。

# Server层

# 连接器

连接器负责跟客户端建立连接,获取权限,维持和管理连接。如果用户名和密码不对,会有"Access denied for user"错误,如果用户名密码认证通过,连接器会到权限表里查出你拥有的权限,之后这个连接里面的权限判断逻辑,都将依赖此时读到的权限,这意味着即使用管理员对这个用户的权限做了修改,也不会影响已经存在连接的权限,只有重新连接的客户端才使用新的权限。

可以使用 "show processlist" 来查看客户端的状态,空闲状态的连接其Command列会显示为"Sleep"。客户端如果太长时间没动静,连接器就会自动将它断开,这个参数是由wait_timeout控制的,mariadb默认是10分钟,如果超过这个时间客户端再次发送请求的话,会收到错误提醒 "Lost connection to MySQL server during query",这就需要重新连接了,可以在mysql命令行中使用命令 "show variables like '%timeout%';" 来查询。

在数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接,短连接是指每次执行完很少几次查询就断开连接,下次查询需要重新建立一个。因为连接的过程比较复杂,尽量使用长连接来减少建立连接的动作。

如果全部使用长连接后,你会发现,有些Mysql占用内存涨的特别快,因为Mysql在执行过程中临时使用的内存是管理在连接对象里面的,这些资源只有在断开的时候才会释放,所以如果长连接累积下来,会导致内存占用太多,被系统强行杀掉(OOM),从现象看就是Mysql异常重启了。

要解决这个问题,有两种方案:

  1. 定期断开长连接。
  2. 如果是Mysql 5.7或更高版本,可以在每次执行完一个比较大的操作后,通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。

# 查询缓存

建立连接后,拿到一个查询请求,这时会查询缓存,直线的查询结果可能会以key-value的形式,被直接缓存在内存中,如果查询能够在这个缓存中找到key,那么value会直接被返回给客户端。如果不在查询缓存中,就会继续后面的执行阶段,执行完成后,执行结果会被存入查询缓存中。

可以看出如果查询命中缓存,Mysql不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高,但是大多数情况下,建议不要使用查询缓存,因为查询缓存往往弊大于利:查询缓存的失效非常频繁,只要有对一个表的更新,这个表上的所有查询缓存都会被清空,对于更新压力大的数据库来说,查询缓存的命中率会非常低,除非你有一张静态表,很长时间更新一次,才适合使用查询缓存。Mysql提供了一种"按需使用"的方式,将参数query_cache_type设置为DEMAND,对默认的SQL语句都不使用查询缓存,对于要使用查询缓存的语句,可以用SQL_CACHE显式指定 "select SQL_CACHE * from targetTable"。

Mysql 8.0直接将查询缓存整个功能删除了。

# 分析器

如果没有命中查询缓存,就要开始真正执行语句了,首先Mysql需要知道要做什么,需要对SQL语句做解析。分析器先会做词法分析,即对sql语句识别出里面的字符分别代表什么含义,做完这些识别后,要做语法分析,根据词法分析的结果,词法分析器会根据语法规则,判断sql语句是否满足Mysql语法。

# 优化器

经过分析器后,Mysql知道你要做什么,在开始执行之前,还要先经过优化器的处理,它的任务时:

  • 表里面有多个索引的时候,决定使用哪个索引。
  • 一个语句有多表关联(join)的时候,决定各个表的连接顺序。会根据效率分析。

优化器完成后,这个语句的执行方案就确定下来,然后进入执行器阶段。

# 执行器

Mysql通过分析器知道要做什么,通过优化器知道了该怎么做,于是就进入了执行阶段,开始执行语句:

  1. 判断对该表有没有执行查询的权限,如果没有,则返回没有权限的错误。
  2. 如果有权限,打开表的时候,执行器根据表的引擎定义,去使用这个引擎提供的接口,进行查询。

会在数据库的慢查询日志中看到一个rows_examined的字段,表示这个语句执行过程中扫描了多少行,这个值是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下,执行器调用一次,在引擎内部则扫描多行,因此引擎扫描行数跟rows_examined并不是完全相同。