一、MySQL架构分析与sql执行流程
1.1 发展历史
1.2 流行分支/存储引擎
上面看到2000年开源，那么自然就有了许多分支产品
1.3 一条查询语句是如何执行的
比如你去餐厅点菜，你只跟服务员说了一道菜名，不久后菜就端到你桌上了
从你点击查询按钮，到返回数据 后面到底做了些啥，有哪些角色 下面来看一看？
1.3.1 客户端 连接服务端
首先第一步当然是建立连接，
通信类型通常用同步，异步的话编程会比较复杂，而且可能产生数据问题，这个由你客户端代码决定
连接方式通常用长连接,长连接好处就是这个客户端用完了，别的客户端还可以继续用，短连接就是用完就关了，后面要用还得重开
协议一般使用TCPIP协议，在linux环境上，本机客户端连接本机服务端 不指定host时这时候使用一个Unix Socket的文件来进行连接
服务端的线程与客户端的连接有什么关系，客户端没创建一个连接，服务端需要一个线程处理，
那么长时间不活跃的连接，是需要被回收的，毕竟连接数有限，如下为超时时间，如果需要尽快回收连接，那么是可以将他们调小的
那么服务端允许的最大连接数是多少呢？
从mysql官网中可以看到，默认是151，最大10万，然后他的作用域是Global，全局的，
说到作用域，还有个级别是会话(session)，比如下面这个窗口，你想要在当前会话(session) 也就是当前窗口修改某些配置时，是不需要在 set 后加 global的
当你想要修改全局变量时，这个时候需要加上global，修改后所有客户端都会生效，生产环境此操作需谨慎
1.3.2 mysql自带的缓存
500万条数据
第一次查询，用时4秒左右
第二次查询 时间与第一次相差无几 那么，缓存呢？
可以看到mysql默认的缓存是关闭的，并且mysql也不推荐大家用，当一张表一条数据发生改变，这张表的数据相关的所有缓存都会被清空，另外 sql语句中大小写 或者是空格的添加也会导致无法命中缓存，所以 不建议用，且因此功能极度鸡肋，8.0版本被移除
1.3.3 解析器 与 预处理器
当我随便输入点啥然后执行的时候，如下，mysql怎样判断我是否输入正确的呢
解析器会进行词法分析与语法分析
然后 预处理器 会做预处理
比如你查询一张不存在的表，比如你查询的属性名写错了
进行上面的分析与预处理后会生成下面这样一个解析树
1.3.4 优化器
查询条件中的某字段有多个索引时，使用哪个，比如你查询中有多张表关联，先查询哪个？
这就是我们说的一条sql语句是多执行路径的，这个时候优化器就派上用场了
主要就如下这些优化
在mysql中它的这些优化叫做基于cost(成本)的优化器
在mysql中，生成执行的路径后，会计算每条路径成本的数量，来比较选择成本最小的路径也可以
说执行计划，来执行它，但是这并不代表你sql可以随便写，一切交给有优化器，这也是我们后面要学习优化的主要原因
怎样看执行计划呢，只需在sql后面加上explain关键字即可模拟一个执行的路径,路径信息
大概包含查询的类型，表名，type(这个很关键)怎样访问表的，可能用到的索引，索引，索引的长度，预估扫描的行数 ，预估过滤的百分比等等，这里只大概介绍下，后面会讲
如果想要看json版本的，如下即可
如何开启optimizer_trace
为什么要开启它，如下图，可以知道它优化过程中有哪些行为
用完了记得关，毕竟还是会耗点性能
1.3.5 存储引擎
数据在数据库存哪，表？ 好，下面对它动手了
存储引擎？可能这玩意很多人都听过，但是说不出个所以然，不急，往下看
首先，我这里创建了三张表，表结构一样，但请看下面三张图黄色部分，他们的表类型不同，分别
是InnoDB，MEMORY,MyISAM 三种
那么这三种表类型（存储引擎）怎么来的？有什么区别？最常用的是哪一个？我什么场景选择哪一个呢？表类型能修改吗
输入如下语句查看表结构存放的位置
可以看到不同表类型(存储引擎)的表的文件数量是不同的分别是2，1，3个文件
默认的存储引擎自5.5版本后就是InnoDB了，包含字符编码也是有默认值
关键问题来了，为什么要有这些个存储引擎，一个不行吗，这还是根据业务场景来的
比如我有如下三种业务场景
1.想要快速访问数据，不考虑持久化
2.存历史数据，比如银行的账户的交易，不做物理删除，只做逻辑删除，绝对不可能修改，甚至查询都很少，不需要索引支持，数据量太大所以需要支持压缩的特性
3.常规的业务，有读写并发 保证数据的一致性
现在大致了解了来源么，我们在不同的业务场景，对数据的访问的要求，存储的要求，管理的要求，都可能不同，所以才会有这么多存储引擎
下面来看看官网上对这些存储引擎的介绍
我简要概括下
InnoDB: 事务安全ACID，提供提交，回滚，崩溃恢复等能力来保证数据安全。支持行级锁。
一致性非锁定读MVCC。聚集索引，大大降低使用主键的查询的IO。支持外键
MyISAM: 只支持表级锁，全部为读，或者大部分场景为读时候可以用它
MEMORY:数据放在内存中存储，所以读写快，但是数据崩溃无法恢复，所以一般可以用于一些临时数据的存储
CSV：纯文本或者用逗号换行的数据
后面的就不多说了
存储引擎介绍链接 不同的存储引擎支持的索引啊，锁啊也基本不同
如果以上都不满足你的业务需求，你也可以用C语言自己写一个，具体步骤可看mysql官网...
为什么不同的存储引擎可以切换：这是由于存储引擎的开发都有一套规范，跟插件样，
记住，存储引擎只负责管理数据，你一坨数据在那，放哪，以怎样的形式放这些东西是存储引擎的活
1.3.6 执行引擎
故名思意，它就是执行sql的东东，它会调用执行引擎的接口得到数据，再根据执行计划进行一顿操作，最后返回结果
好，到这里应有个小结，关于一条sql语句从输入到返回数据，这中间到底经历了啥，看到这你应该多少能说点啥了，
总结： 先是客户端选择连接方式（见前面三个参数）建立连接，然后解析器对你输入的slelect语句进行词法分析与语法分析通过了的话再做预处理 预处理也通过就生成了解析树，然后是优化器对解析树进行优化，优化后这个时候就生成执行计划，然后给执行器，执行器从存储引擎中拿到数据，一般是从磁盘中一次性获取到内存，然后通过执行计划进行处理，处理完后就返回我们想要的数据了，当然如果你开了mysql自带的缓存，那么有可能在解析器阶段就判断命中缓存然后直接给你返回缓存数据，当然这个缓存一般不开启
下图与本文无关，可以看到mysql跟innoDB都被Oracle收购了，而上面说的mysql分支marialDB的推出也是mysql作者防止mysql被收购后不再开源而采取的措施
1.4 一条更新语句是如何执行的
这里为什么我只说更新，因为更新就包含了删除与新增，了解过mybatis源码（只有doUpdate）的人或许能比较理解，不理解也没关系，继续看下去就懂了
这样一条sql语句，大致的流程与上面查询都是一致的，不同的只有执行引擎处理的那小部分
1.4.1 预读取
关于预读取的概念，这里有必要提一提，如下图 使用innodb管理的数据放在磁盘，在磁盘中处理数据是很慢的，所以要把它加载到内存来处理，从磁盘获取数据到内存也是很慢的，所以一般一次性获取固定量 那么一次性获取多少数据呢，比如我们需要10kb的数据，那么一次性只会加载10kb么，不是，当我读取到磁盘上的一块数据时，我认为下一块相邻的数据也会马上被读取到，所以一起给你读取到内存来，这就是预读取（思想：局部性原理）
那么不管你要读取多少，每次操作系统会给你加载一定的数量，就比如说，你要一瓶啤酒，但是这店只论箱卖
那么这个量是多少呢，这个时候又牵扯到 一个数据页的定义，默认16KB大部分情况都够用
这玩意是可以修改的（表格后面五个数为其他有效值），但是修改比较麻烦，需要数据清空然后初始化mysql服务
1.4.2 内存缓冲区
放在内存中的一个缓存，已经从磁盘中获取过某数据页的数据，会缓存到缓冲区中，下次再次获取时看看缓冲区中有么有，有的话从缓冲区中拿，写入的时候也一样，放在缓冲区中的数据可能因为断电等原因而丢失，所以缓冲区中的数据又称为脏页，而同步到磁盘后的才称为干净的页，这个动作（刷脏）是由一个后台线程 默默的处理的，如果刷脏完成之前由于重启宕机等原因数据丢失，那么咋办呢 看下一个概念
InnoDB_buffer_pool的内存越大，对我们系统读写性能提升也就越大，所以生产环境一般都要尽可能调大
那InnoDB_buffer_pool 内存这么重要，那它满了咋办，没办法还是要回收，
这里使用LRU算法（redis也是用的这），我简单介绍下 传统的LRU包含一个双向链表与一个哈希表，哈希表放双向列表的下标，然后在双向链表中最新访问或者新增的value都会放到双向链表的head上，然后这链表长度又是设计的有限的 对此算法感兴趣的话可以看看别的资料
然后innodb的bufferpool采用的是优化后的LRU算法，它不想来的一大块数据一下就把尾部的大块数据给挤掉，优化细节：
如下图，看起来跟jvm那张图是不是有点像，哈哈 简要来说就是，数据分为了俩部分 热数据区，与冷数据区 ，热数据区是在前面，新来的数据是放在冷数据区的head中，当访问后才会被移入热数据区的头中，分区比例大小默认为5:3
1.4.3 redo log
重启服务的时候看看redo log中 有哪些数据没有被刷脏，然后刷下，那这样的设计有啥毛病么，想着是不是有点麻烦，为啥我不直接写到磁盘呢，而要又写内存缓冲区，又写磁盘的redo log
这个时候又要说到一个概念了，顺序IO与随机IO，我们写数据或者加载数据都是磁壁转到某个扇区后获取（寻址）的，如果操作的数据存在随机的不连续的扇区那么寻址花费的时间会比较长，而将数据先写到缓冲区这样提升了操作的效率，而再写到redo log 又保证了持久性，不会丢失，同时因为顺序的写入redo log 又保证了顺序IO 这样看来这种设计反而很巧妙
可以看到redo log文件大小默认是 48兆的不会变 如果满了的话就不能提供其功能而必须先刷盘了，所以有时候可以适当调大点
最低0.47元/天 解锁文章
扫一扫
4728
到【灌水乐园】发言
