把学习当成一种习惯
选择往往大于努力,越努力越幸运
MySQL
Aug 18, 2020

MySQL基础架构分析

总体

MySQL的总体分为Server层和存储引擎层 :

  Server层包括:连接器、查询缓存、解析器(分析器)、优化器、执行器等.当然还包括一些内置的函数(如数学、时间、加密等),所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等.
  存储引擎层:存储引擎层的作用就是负责数据的存储和提取,而且存在不同的存储引擎(各有各自的不同实现):InnoDB、MyISAM、CSV等,现在最常用的存储引擎是InnoDB,MySQL从5.5.5版本开始默认存储引擎就是InnoDB.

连接器

负责与客户端建立连接,获取权限、维持和管理连接:

// 执行连接命令 不建议在-p后面输入密码,不安全
mysql -h$ip -P$port -u$user -p
// 查询所有连接 如下图中的Command列的Sleep就是空闲状态
show processlist

  如果一个空闲连接超过8小时(默认值),会自动断开连接,这个时间是由参数wait_timeout控制的，默认值是8小时.
  MySQL存在长连接和短连接,长连接是指客户端持续请求,一直使用同一个连接;而短连接是指每次执行几次请求后就断开连接,下次再次请求时又需要重新建立连接.MySQL的建立连接是一个复杂的过程,所有建议使用长连接.
  使用长连接代表着长时间不断开,MySQL通过临时内存来管理这些长连接,所以会占用内存,当释放连接的时候才会释放资源.如果许多长连接积累下来,可能会导致内存占用大,被系统强行杀掉(OOM),从现象看就是MySQL异常重启了.
  MySQL 5.7或更新的版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection来重新初始化连接资源.这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态.

查询缓存

  当客户端成功建立起了连接、权限验证(用户身份验证)后,如果执行的是一条select语句,第一步走的就是先到查询缓存看看,之前是不是执行过这条语句,因为之前select的语句可能会被缓存到内存中.查询缓存是以key-value的映射结构存储的,key存的是select语句,对应的value是查询的结果.
  查询缓存的好处 : 如果查询缓存命中,直接返回给客户端,MySQL无需再执行解析器-->优化器-->执行器-->存储引擎层-->执行器-->客户端这些复杂操作,就可以直接返回结果,这个效率会很高.

查询缓存的弊大于利

  如果一个表频繁更新数据,只要一更新数据,对应的查询缓存就会被清空,对于数据库有很大的压力,查询缓存的命中率会非常低.除非表中存储的是静态数据,很久时间才更新一次,才推荐使用查询缓存.
  MySQL 8.0版本直接将查询缓存的整块功能剔除了.

解析器(分析器) : 要做什么操作

  如果查询缓存没命中,就需要走解析器.解析器有两个阶段:第一阶段就是先做"词法分析"后,再进行第二阶段"语法分析".
  "词法分析" : 先识别 select | insert | update |delete ,然后继续对查询语句中的表名、列识别.
  "语法分析" : 根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个SQL语句是否满足MySQL语法.如果语句存在错误,则会报错.
  《高性能Mysql》中:解析器会处理语法和解析查询, 生成一课对应的解析树,预处理器进一步检查解析树的合法.比如: 数据表和数据列是否存在, 别名是否有歧义等.如果通过则生成新的解析树,再提交给优化器.

优化器 ：要怎么做才是最优

  通过了解析器的"词法分析"和"语法分析",MySQL终于知道我要做什么操作了.
  优化器的作用就是走最优的路径,例如当前的查询语句(可能有多个索引的情况下)中是要决定使用哪个索引,或者多表连接查询中,决定各个表的查询顺序等等(优化器已经很智能了,但是有时候走的优化路线不是最优的,此时就需要我们自己去优化语句或者建立最优的索引等优化手段).

select t1.*,t2.* from t1 left join t2 on t2.id = t1.t2Id
where t1.name = 'abin' and t2.age = 22;
  • 既可以先查询t1表中 name = 'abin' 的 t2Id,然后再根据拿到t1表的t2Id去左连接查询t2表id,如果连接存在数据,然后再过滤t2表的age = 22 条件;
  • 既可以先查询t2表中 age = 22 的 Id,然后再根据拿到t2表的Id去左连接查询t1表的t2Id,如果连接存在数据,然后再过滤t1表的name = 'abin' 条件;
  • 这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案.

执行器 ：开始执行语句

  优化器已经找到最优的方法,然后告诉执行器要怎么做,执行器收到优化器的指令后就开始执行.执行器表示已经知道要对哪个表进行操作,开始执行前会对该表进行权限验证(这里的权限验证是对表,而连接器是对用户的身份进行权限验证),如果没有,就会返回没有权限的错误.如果有权限,则打开表开始执行,开始执行的时候会根据表的存储引擎类型去使用这个存储引擎的接口(默认是InnoDB).

接下来看看存在以下执行SQL语句:

// studentNum 列没有创建索引,只是一个普通列
select s.name from tb_student s where s.studentNum = 10001;

因为 studentNum列 不是一个索引列,所以执行器会做全表扫描:
1.调用InnoDB存储引擎接口,取到第一行数据,判断 studentNum = 10001 ,如果不是则跳过,如果是则将这行存在结果集中;(通常索引查询的话存储引擎是按照页来读取数据,而在这里不是索引查询,所以执行器还是按照存储引擎读取行的接口来读的,也就是说执行器进行一行一行的全表扫描).
2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行.
3.执行器有需要做缓存的，执行缓存操作
4.执行器将结果集返回给客户端.(增量的返回结果)
5.这条查询语句就执行完毕了.

结束语

  • 原创不易
  • 希望看完这篇文章的你有所收获!

目录