【mysql基础学习篇】mysql服务器架构简介

     出于对mysql和大数据的热爱，又开始了mysql的学习之路，测试工作经常会接触到mysql，以前也遇到过一点坑总结过，但是都是一些零散的点，没有形成知识块，理解也不够深入。这次趁着这股热情，重温下《高性能mysql》，同时也在技客上学习《从原理到实战，丁奇带你搞懂MySQL》，

加上姜老师的《MySQL技术内幕：InnoDB存储引擎》，再边学边写边实践边总结下mysql。希望可以从点到线，再到面，形成自己的MySQL知识网络。

 一、MySQL架构简介
        
     1.1 基本架构
     
     1.2 一条查询SQL语句执行过程
      
     1.3 一条更新SQL语句执行过程

 二、事务，常见锁类型

 三、长短连接的利弊

一、MySQL 的基本架构简介

1.1 基本架构

                                                                                           

        总体来说分2层：

•   server层：mysql的核心服务层，里面包含连接器，查询缓存，分析器，优化器，执行器。

         1、 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。

• • 每个客户端在mysql服务器内都会有一个线程连接。
  • 一个用户成功建立连接后，即使用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
  • 建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

        2.、查询缓存：MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

• • MySQL 8.0 版本直接将查询缓存的整块功能删掉
  • 查询缓存，不会提高查询性能，对于更新压力大的数据库来说可能还会增加更多的压力。因为只要有对一个表进行更新，这个表上所有的查询缓存都会被清空。有可能之前存储的大数据还未使用就已清理。所以一般业务侧不会选择查询缓存。
  • 关闭缓存，查看：SELECT @@query_cache_type;

                         

 

        3、分析器：MySQL 需要知道做什么-->识别sql里的关键字，判断 SQL 语句是否满足 MySQL 语法。

        4、优化器：MySQL 需要知道怎么做-->生成对应的执行计划。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

        5、执行器：MySQL 调用执行计划，调用存储引擎的接口执行查询

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

               所有的内置函数，存储过程，触发器，视图都是在这一层实现的。

• 存储引擎层：mysql负责数据存在和提取层，server层通过api与存储引擎进行通信。支持不同的存储引擎。

1.2 一条SQL查询语句执行过程

     以最简单的select * from T where ID=10语句来分析执行过程：

1. 连接器连接验证，基于用户名，原始主机信息和密码认证。
2. 分析器做词法和语法分析，确定sql “合理合法 “”   （有表，有字段，语法正确）输入的"select"这个关键字识别出来，这是一个查询语句。把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
3. 优化器确定有ID 字段没有索引
4. 执行器:

• • 要先判断一下用户是否有操作表 T 的查询权限（如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证）
  • ID 字段没有索引：调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
  • ID 字段有索引：调用 InnoDB 引擎接口取满足条件的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

二、事务，常见锁类型

        之前学习过这一块总结过：https://www.cnblogs.com/loleina/p/10344742.html

三、长短连接的利弊

        1、   短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

•  弊端：建立连接的过程通常是比较复杂的，每操作一次db连接一次，会影响业务的整体性能。

         2、  长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

•  弊端：MySQL 占用内存涨得特别快，因为 MySQL 在执行过程中需要临时内存来管理在连接对象。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

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

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

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