MySQL架构

开篇文章复习一下MySQL的架构组成部分。

下图展示了MySQL执行一条SQL查询语句的流程，MySQL架构可分为Server层和存储引擎层。

MySQL架构
  • Server层：负责建立连接、分析和执行SQL。除了上图中的核心功能模块以外，所有的内置函数（如日期、数学和加密函数等）和所有的跨存储引擎的功能（如存储过程、触发器、视图等）都在Server层实现。
  • 存储引擎层：负责数据的存储和提取。不同的存储引擎共用一个Server层，但支持的索引类型不同。从MySQL 5.5版本开始，默认的存储引擎是InnoDB。

下面就SQL查询语句执行流程进行深入解析。

Step 1 连接器

用户通过mysql -u $username -p发起连接请求，若MySQL正常运行且用户名密码验证成功，则连接建立完毕。连接器此时会获取该用户的权限并保存，后续该用户在此连接中所做的任何操作都是基于该权限进行逻辑判断，即便管理员中途修改了这个用户的权限，也不会影响该连接。

  • 如何查看MySQL服务被多少客户端连接

    通过show processlist进行查看

    processlist

    图中可看到id为6的root用户状态为Sleep，即该用户连接MySQL后没有再执行过任何命令，且空闲时长为736秒。

  • 空闲连接持续多久

    wait_timeout参数规定了空闲连接的最大空闲时长，默认是8小时；也可以通过kill connection +$id手动断开。

    当空闲连接被服务端断开后，客户端并不会立即得知，而是当客户端发起下一个请求时，收到报错

  • 连接数有限制吗

    max_connections参数规定了MySQL服务支持的最大连接数

  • MySQL长连接占用内存问题如何解决

    有两种解决方案：

    • 定期断开长连接
    • 客户端主动重置连接：通过接口函数mysql_reset_connection()释放内存，重置连接，且不需要重连和校验权限

Step 2 查询缓存

这一步仅存在于MySQL 8.0之前的版本，后期版本已将查询缓存的工序删除。

当MySQL收到SQL语句后，就会解析SQL的第一个字段，判断是什么类型的SQL；

如果是select语句，MySQL就会先去查询缓存（Query Cache）中查找缓存数据，看之前是否执行过这一条命令；查询缓存是以key-value形式存储在内存中的，key是SQL查询语句，value是SQL查询的结果。

如果命中查询缓存，则返回value给客户端；如果没有命中，则继续执行，并将查询的结果存入查询缓存以便后续使用。

缺点：命中率很低，且每当一个表更新操作时，这个表的查询缓存就会清空。

Step 3 解析SQL

执行SQL之前，解析器会先对SQL语句做解析。

  • 词法分析

    MySQL根据输入的字符串识别出关键字，构建出SQL语法树，以便后面模块获取SQL类型、表名、字段名、查询条件等。

  • 语法分析

    根据词法分析的结果，语法解析器会根据语法规则，判断该SQL语句是否符合MySQL语法。但是表或者字段是否存在的检查，并不是在解析器中完成的。

Step 4 执行SQL

每条select语句的执行分为以下三个阶段：预处理、优化、执行

预处理器

  • 检查SQL查询语句中的表或者字段是否存在
  • 将select *中的*扩展为表上的所有列

优化器

  • 主要负责确定SQL查询语句的执行方案，比如表里存在多个索引时，优化器会基于查询成本的考虑，来决定使用哪个索引。
  • 可以在查询语句最前面加explain来查看优化器对这个SQL语句制定的执行计划，包括选用的索引等。

执行器

执行过程执行器会与存储引擎以记录为单位进行交互，具体有三种执行方式

主键索引查询

1
select * from mytable where id = 1;

这条查询语句用到了主键索引，而且是等值查询，因此优化器决定用主键索引执行该SQL。那么执行器与存储引擎的交互流程如下：

  • 执行器第一次查询，会调用read_first_record函数指针指向的函数，优化器选择的访问类型为const，这个函数指针被指向为InnoDB引擎索引查询的接口，把条件id=1交给存储引擎，让存储引擎定位符合条件的第一条记录；
  • 存储引擎通过主键索引的B+树定位到id=1的第一条记录，若记录不存在，则向执行器报告错误，结束此次查询；若记录存在，则返回给执行器；
  • 执行器从存储引擎读到记录后，判断记录是否符合查询条件，若符合则返回给客户端，否则跳过该记录；
  • 执行器查询的过程是一个while循环，所以仍会继续查询，但由于不再是第一次查询，因此会调用read_record函数指针指向的函数，因为优化器选择的访问类型为const，所以该函数指针被指向为一个永远返回-1的函数，调用该函数后，执行器就会退出循环，结束此次查询。

全表扫描

1
select * from mytable where name = 'aaa'

这条查询语句没有用到索引，因此优化器会选择ALL的访问类型，即全表扫描。那么执行器与存储引擎的交互流程如下：

  • 执行器第一次查询，会调用read_first_record函数指针指向的函数，因为优化器选择的访问类型为all，这个函数指针被指向为InnoDB引擎全扫描的接口，让存储引擎读取表中的第一条记录；
  • 执行器会判断读到的这条记录的是不是aaa，如果不是则跳过；如果是则将记录发给客户端（Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）。
  • 执行器查询的过程是一个while循环，所以还会再查一次，会调用read_record函数指针指向的函数，因为优化器选择的访问类型为all，该函数指针指向的还是InnoDB引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器，执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；
  • 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器返回读取完毕的信息；
  • 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

索引下推（MySQL >= 5.6）

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将Server层负责的事情，交给存储引擎层去处理了。

1
select * from mytable where age > 20 and reward = 10000;

假设mytable表中，age和reward字段建立了联合索引(age, reward)。

当联合索引碰到范围查询时就会停止匹配，即age字段能走索引，但reward字段无法利用索引。

在MySQL 5.6之前，不使用索引下推，执行器与存储引擎的交互是这样的：

  • Server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到age > 20的第一条记录；
  • 存储引擎根据二级索引的B+树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给Server层；
  • Server层在判断该记录的reward是否等于10000，如果成立则将其发送给客户端；否则跳过该记录；
  • 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给Server层；
  • 如此往复，直到存储引擎把表中的所有记录读完。

没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给Server，接着Server再判断该记录的reward是否等于10000。

使用索引下推后，判断记录的reward是否等于10000的工作交给了存储引擎层，过程如下：

  • Server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到age > 20的第一条记录；
  • 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward是否等于10000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给Server层。
  • Server层再判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
  • 如此往复，直到存储引擎把表中的所有记录读完。

使用了索引下推后，虽然reward列无法使用到联合索引，但是因为它包含在联合索引(age, reward)里，所以直接在存储引擎过滤出满足 reward = 10000的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

MyISAM和InnoDB的区别

  • MyISAM不支持事务，InnoDB支持事务
  • MyISAM只有表级锁，影响性能；InnoDB支持行级锁，并发性能更好
  • MyISAM不支持外键，InnoDB支持外键