高性能Mysql（一）

最近由于疫情，在家学习办公，阅读《高性能mysql》第三版这本书，深入了解下mysql的工作原理。

第一章主要介绍了mysql的基础架构

如上图，先了解一下mysql的基础架构：

1.第一层客户端，客户端的种类有很多，最简单常用的就是navicat，这一层和mysql的核心结构关系不大。

2.mysql的核心架构分为两层，第一层如上图的server层，第二层为存储存储引擎。

    ⑴ server层。

         mysql大多数的核心功能都在这一层实现，图中的连接器，查询缓存，分析器，优化器，执行器等，每个组件的作用如图中标注。另外，所有的内置函数，包括日期，时间，数学和加密函数等，还有我们自己写的存储过程，触发器，视图，都属于server层。

    ⑵ 存储引擎。

          存储引擎这一块我们可能平时接触的比较少，它会提供统一的API让sever层进行调用，所以这些接口屏蔽了不同存储引擎之间的差异，类似于面向接口编程。存储引擎主要做的工作就是负责数据的存储和提取。

         存储引擎：InnoDB（目前也是最常用的，MySQL5.5后默认存储引擎）、MyISAM、Memory等。create table 时不指定引擎类型，默认使用的就是InnoDB.如果使用其他类型，可以利用create table语句中使用engine=memory（旧版本中是type字段），等来指定使用内存引擎来创建表。不同的存储引擎数据存储方式不同，支持的功能也不同。

一.连接器

     1.在文档中，连接器负责跟客户端建立连接，获取权限，维持和管理连接，这个连接器位于server层，我们通过连接器可以让客户端和server层连接起来，从而登录到mysql。我们经常用的连接命令：

           mysql -h127.0.0.1 -P3306 -umysql -p

     2.连接过程：

           经典的TCP握手之后，服务器开始验证身份。我们这个时候要用到输入的用户名和密码：

           ①如果用户名和密码不对，我们会被反馈“Access denied for user”的错误，然后就死翘翘了，(客户端程序结束执行）。

           ②如果用户名和密码正确，连接器回到权限表里找到我们所拥有的权限。之后在这个连接面里的权限判断逻辑，都会依赖于此时读到的权限。（如果一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响到已经存在连接的权限，修改完成后，只有在新建的连接才会使用新的权限设置）。

      3.连接完成：       

          ①如果你没有后续的动作，这个连接就会空闲下来，使用show processlist 命令中看到它。

　　   ②客户端如果太长时间没用动静，连接器就会自动断开，这个时间是由wait_timeout控制的，默认是8小时。（我们在日志模块和鉴权模块遇到过该问题）。

　　   如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误的提醒：Lost connection to Mysql server during query。这时候如果要继续，就需要重连，然后在执行请求了。

      4.连接状态：

           说到连接，就会有长短连接问题，一般客户端连接至连接器，都是长连接，因为建立连接的过程很复杂，所以在使用中尽量减少建立连接的动作，但是会造成长连接列累积下来，可能会导致内存占用太大，被系统强制杀死，在MySQL 5.7或者更高的版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化链接资源，重置回话状态（这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态）。

二.查询缓存

         当mysql服务层拿到一个查询请求之后，会到查询缓存看看，之前是否执行过这样的一条语句，之前执行过的语句及其结果可能会以key-value对的形式，被直接存放到内存中，key是查询的语句，value是查询的结果，如果查询能够直接在这个缓存中找到key,那么这个value就会被直接返回给客户端。

          {key:select * from test, value:[1,2,3]}

如果语句不在查询缓存中，就会继续后面的执行阶段，执行完成后，执行结果会被存入到查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。                                    MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，如：

          select SQL_CACHE * from test

三.解析器

          在第二步查询缓存中没有命中，就会继续往下执行真正的SQL语句，我们一般的SQL如：

          select * from test where id = 1，输入的是由多个字符串和空格组成的一条SQL语句，Mysql需要识别出里面的字符分别是什么，代表什么。MySQL从输入的“select”这个关键子识别出来，这是一条查询语句，也要把字符串“test”识别成“表名test”,把字符串“id”识别成“列id”

          根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

三.优化器

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

            1.优化器是在表里面有多个索引的时候，决定使用哪个索引；

            2.在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

           这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪 一个方案。 优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段.（优化器后面会专门用一个章节讲解）

四.执行器

            在第三步优化器选择最优方式后，执行器会就会根据表的引擎定义，去使用这个引擎提供的接口，例如

            select * from test where id = 1

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

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

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

            对于有索引的表，执行的逻辑也差不多，第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这写接口都是引擎中定义好的。在数据库的慢查询日志中有一个rows_examined字段，表示这个语句执行过程中扫描了多少行，这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了很多行，因此引擎扫描行数跟rows_examined并不是完全相同的。