Mysql，从头到脚再来一遍

这篇是mysql从头到脚系列的第一部，后续更新后，会同步update这边的blog~也可以点一波原作者的关注哈。

原文地址：https://mp.weixin.qq.com/s?__biz=MzU1NzgxMDYxMA==&mid=2247483673&idx=1&sn=af9dcd30ab1e88ad1d3fd641d2b743d2&chksm=fc3157c4cb46ded29277c6bafa4102aafc3426e90bc5c33e72479d89ef4adae313aa72ae466b&token=1008080372&lang=zh_CN#rd

mysql的从头到脚

    先看下面你这条究极简单的sql，后面也围绕这个例子展开：

mysql> select * from table where id=7;

    例子先抛出来，我们先从整体看下，mysql的全身长什么样。下图是mysql的分层，从功能来看，分成server层和存储引擎层：

    Server 层的组件包括连接器、查询缓存、分析器、优化器、执行器等，他们覆盖了 MySQL 的大多数核心服务功能以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

    存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎（因为innoDB有这很多的优势，比如对事务性的支持等等）。建表时如果不指定存储引擎，默认为innoDB，也可以显式的指定用哪个存储引擎。不同存储引擎支持的功能不同，数据读取也会有不同。

连接器

    要使用mysql的能力第一步就是建立应用和mysql数据库的连接。连接器负责完成与客户端的链接、获取权限以及维持和管理连接，脚本连接语句如下：

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

通过tcp网络通信后，连接器开始认证身份，即验证用户名和密码。链接成功之后如果没做别的后续操作，会维护一个空闲链接。如果太长时间没操作连接会自动断开，这个操作由wait_timeout来控制。

    一般来说数据库的连接分长连接和短连接。短连接一般是经过少数几次读写后就断开，如果有需求再发起连接。长连接的话，多次请求始终用一个链接。但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

怎么解决这个问题呢？你可以考虑以下两种方案。

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

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

     因此，对于长连接来说，一般都是不太推荐的，不仅仅有长连接累计导致OOM的问题，同样还会导致长事务下回滚段过长，导致数据库变得非常“臃肿”的情况，后面在日志系统和事务隔离章节会细说。

查询缓存

    嗯…个人感觉是个没什么大用的东西。提到缓存功能自然是减少对底层存储介质的读写调用。

    mysql在得到一个查询请求之后，会首先看这条语句是否在查询缓存中，即之前执行过的语句和结果会以键值对的形式缓存在内存中，如果缓存里有这条查询语句，会直接把缓存的查询结果返回给客户端。

    但这样做往往吃不到好果子。首先对于这种查询的缓存值，本身失效是非常快的，因为db的数据基本上变化是非常快的，一张表如果有更新，会将所有的查询缓存失效掉。对于业务系统，数据并发更新是家常便饭，查询缓存的失效频率非常高，命中率极低。mysql8.0已经把这个功能移除，这里也就不多说了。

分析器

    简而言之，就是把SQL语句翻译成可执行的语句。比如上面的那条简单的sql，它会识别出“select”，判定是一条查询语句，“table”识别成表名，“id”识别成列等等

优化器

    经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

    优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

 

执行器

首先判断这个表有没有执行权限。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如在文章最开始的例子中的表 table 中，id 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 id 值是不是 7，如果不是则跳过，如果是则将这行存在结果集中；

2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

   至此，这个语句就执行完成了。

   对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

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