MySQL架构体系设计解析(1) [不完整]

一. SQL执行过程简介及执行引擎介绍

MySQL包含的组件

如图中所示主要包含了这几个部件

• （1）MySQL向外提供的交互接口（Connectors）

  Connectors组件，是MySQL向外提供的交互组件，如java,.net,php等语言可以通过该组件来操作SQL语句，实现与SQL的交互。

• （2）管理服务组件和工具组件(Management Service & Utilities)

  提供对MySQL的集成管理，如备份(Backup),恢复(Recovery),安全管理(Security)等

• （3）连接池组件(Connection Pool)

  负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。

• （4）SQL接口组件(SQL Interface)

  接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户。

• （5）查询分析器组件(Parser)

  首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。

• （6）优化器组件（Optimizer）

  对SQL命令按照标准流程进行优化分析。

• （7）缓存主件（Caches & Buffers）

  缓存和缓冲组件

• （8）插件式存储引擎（Pluggable Storage Engines）

  1.什么是MySQL存储引擎

  ​ MySQL属于关系型数据库，而关系型数据库的存储是以表的形式进行的，对于表的创建，数据的存储，检索，更新等都是由MySQL

  存储引擎完成的，这也是MySQL存储引擎在MySQL中扮演的重要角色。

  研究过SQL Server和Oracle的读者可能很清楚，这两种数据库的存储引擎只有一个，而MySQL的存储引擎种类比较多，如MyISAM存储

  引擎，InnoDB存储引擎和Memory存储引擎.

  ​ MySQL之所以有多种存储引擎，是因为MySQL的开源性决定的。MySQL存储引擎，从种类上来说，大致可归结为官方存储引擎和第三

  方存储引起。MySQL的开源性，允许第三方基于MySQL骨架，开发适合自己业务需求的存储引擎。

  2.MySQL存储引擎作用

  ​ MySQL存储引擎在MySQL中扮演重要角色，其作比较重要作用，大致归结为如下两方面：

  ​ 作用一：管理表创建，数据检索，索引创建等

  ​ 作用二：满足自定义存储引擎开发。

  3.MySQL引擎种类

  ​ 不同种类的存储引擎，在存储表时的存储引擎表机制也有所不同，从MySQL存储引擎种类上来说，可以分为官方存储引擎和第三方存储引擎。

  ​ 当前，也存在多种MySQL存储引擎，如MyISAM存储引擎，InnoDB存储引擎，NDB存储引擎，Archive存储引擎，Federated存储引擎，Memory

  存储引擎，Merge存储引擎，Parter存储引擎，Community存储引擎，Custom存储引擎和其他存储引擎。

  ​ 其中，比较常用的存储引擎包括InnoDB存储引擎，MyISAM存储引擎和Momery存储引擎。

• （9）物理文件（File System）

  实际存储MySQL 数据库文件和一些日志文件等的系统，如Linux，Unix,Windows等。

一条SQL语句的执行过程

一条SQL语句的执行流程如下
select customer_id, first_name, last_name from customer where customer_id= 14;
其中主要分为两个部分 : sql关键字和条件字段(属性名, 表, 参数等)

数据库的存储位置

数据库的数据最终还是存在了磁盘上的，　具体的位置可以查看/etc/my.cnf文件, 其中的这个字段就展示了mysql文件存储的地址
可以到对应的目录查看文件, 其中一个库就是一个文件夹, 文件夹中如果使用了InnoDB的话, 每个表会是两个文件(*.frm, *.idb)

二. 数据库信息是如何在磁盘上存储的

操作系统在磁盘上是以页为单位进行存储的, 读取数据到内存中的时候也是以页为单位进行读取的, 关于这部分的学习可自行查看操作系统的相关知识, 而InnoDB存储引擎是是已每页16K的容量进行管理的, 同时存在一个规定, 每页最少存储的记录条数为两条, 一页中可能存在几条数据, 也可能存在几十条数据. 如果又一条数据的容量大于16K了怎么办呢, 又要保证一页最少碰存两条数据的限制 .如下图所示
可参考MySQL 行溢出数据

InnoDB页结构

可参考文章 MySQL · 引擎特性 · InnoDB 数据页解析 – 【MySQL】InnoDB行格式、数据页结构以及索引底层原理分析
一个页包含如图所示的几个结构,

• 页头
  其中页头中保存了下一个页面和上一个页面的地址, 页与页之间属于是一个双向链表, 每个页中的记录又可以组成一个单向链表
  记录页面的控制信息，共占56字节，包括页的左右兄弟页面指针、页面空间使用情况等
• 虚记录
  比如这一页实际保存的数据范围是从30 – 60 之间的数据, 虚记录保存的就是29 – 61的数据, 来保证如果理来比对可以判断这个数据是否存在这一页之中.
  最大虚记录：比页内最大主键还大
  最小虚记录：比页内最小主键还小
• 记录堆
  行记录存储区，分为有效记录和已删除记录两种
• 自由空间链表
  已删除记录组成的链表
• 未分配空间
  页面未使用的存储空间；
• 页尾
  页面最后部分，占8个字节，主要存储页面的校验信息；

其中自由空间链表的存在是因为一个位置之前是存在数据的, 后面这个数据删除之后,位置空出来了, 然后就在记录堆中进行注册, 下次有数据需要进行存储的时候,就会找个位置进行存放, 让资源空间的利用最大化.在页中是分散存储的.

三. 页内信息维护

顺序保证

其中顺序保证包含物理有序和逻辑有序
物理有序是指页在磁盘中是顺序存储的, 但是会存在一个问题, 就是如果两个页之间的某个页被删除了, 这个时候后面的页数据需要移动到前面被删除的页的位置处, 比如如果不是规则的自增, 可能会造成频繁的数据移动影响性能.如果是有序自增的话, 会默认在页的后面新增页数据, 物理有序指的是页与页之间的顺序
逻辑有序指的是页之中数据的排序方式, 前面我们已经看过了, 一个数据在页中被清除之后, 空间并不会被回收, 而是会给后面新的数据使用, 在物理上页中的数据可以是不连续的, 只不过每条数据都维护了下一条数据的地址, 是一条单向链表.

为什么InnoDB要使用 B+树进行存储

关于B树和B+树的详细讲解可以参考这篇文章 B树、B+树详解

树

左小右大
普通是树和平衡二叉树中, 每次比较的数据只能是一条数据, 这样子如果数据量非常大的话, 需要比较的次数还是非常的多

B树

这个时候B树就出现了, 可以看到, 在B树中, 每个节点的数据可以不是一条, 比如我需要找到数字17的话, 首先和根节点10比较,小于10 就访问右孩子, 首先和13和16比较, 都大于这两个数, 但是比19小, 这个时候就访问16和19之间的这个子孩子节点, 然后就可以找到17这个数据了,可以看到, 和普通的平衡二叉树相比, 我们在大量数据的查找中, 访问树的层级会少很多, 由此节省了大量的时间

为什么使用B+树

其中, B+树的所有非叶子节点都不保存具体的数据, 只是用来索引.
所有的叶子节点都包含了所有的关键字信息, 和指向这些关键字信息的指针

1. 所有的非叶子节点都可以看成是索引, 由于所有的非叶子节点都不真正存储数据, 所以每个节点的数据占用量都是非常的小, 那么每一页就可以存储大量的节点数据, 比如一页可以存储1千个纯节点信息, 那么在内存进行数据读入的时候, 就一次只需要读取少量的页, 就可以找到数据所在, 大大减少了和磁盘的交互, 还减少了内存的占用,
2. 由于所有的非叶子节点都不是查找的终点, 那么我们找任何一条数据的路径长度都是一样的, 导致每一条数据的查询效率都是一样的
3. 并且由于所有的叶子节点都是连在一起的, 所以非常的适合进行范围查询, 如果需要查询某个范围的数据,只需要遍历叶子节点即可, 而B树则不支持.

注: B树使用的是中序遍历, 而B+树使用的是链表遍历

插入策略

数据首先会寻找自由空间链表进行存储, 然后寻找未分配空间进行存储, 如果存不下会申请新的页进行存储, 每一个数据都会有后面数据的地址.

页内查询

类似于跳表的变化, 存在一个分层的概念, 本质还是一个单向链表.

四. MySQL InnoDB存储引擎内存管理

前面我们大致看了数据在磁盘中是如何存储的, 现在我们来看下数据在内存中是如何存储的.

内存中读取数据也是以页为单位进行读取的, 在内存中单独开辟2了一块空间当作磁盘读取页和程序读取的缓冲, 提高读取IO的性能, 内存中存在一个内存池, win中默认实例 128M, 也可以自己根据需要进行设置. 即是下图中的预分配内存空间

进行查询的时候首先查看数据页是否存在缓存池中, 那么怎么判断我需要的这一页是否存在缓存池中呢, 这个内存管理中的页面映射就起作用了, 存在一个pageHash, 可以判断需要的页是否在内存池中(bufferPool),

空闲页是系统启动之后还没有开始使用的一些空间, 当开始进行查询之后, 将一些数据已经读到内存池中之后, 存储数据的就是数据页(LRU页),如果 有些数据被修改了, 称为脏页,(不是垃圾数据, 而是需要进行落盘的数据)

如果有新数据来了的话, 就会加到队列的头部, 如果最后放不下了,就会有限淘汰尾部的数据

LRU实现的不够完美的话可能会淘汰热数据, 比如一下子加载大量的数据,
可以将内存池, 分成两堆, 一堆是old, 一堆是new, 类似于JVM的划分, 如果某个数据的访问次数达到了设定的数目的话, 就放到new区, 就可以防止热点数据被淘汰了

首先会从空白页中放, 没空间了就去淘汰LRU列表, 在不够就要淘汰脏页了.

注: lru队列移动数据时, 前1/4 不进行交换, 防止几个数据来回变的时候频繁的移动队列数据

---

索引

一级索引和二级索引, 主键索引就是主键索引, 除此之外的都是二级索引, 主键索引也是聚簇索引, 其他的二级都是非聚簇索引, 什么是聚簇索引, 就是索引和数据在一棵树上面, 只要可以找到索引就可以找到这条数据, 而其他的二级索引建的树则是非聚簇索引, 即是叶子节点只会保存主键的信息, 还需要根据主键的信息去聚簇索引即一级索引中去查询到具体的数据, 此种查询即是回表查询.
如果用户没有主键怎么办, 会用一个唯一列做一级索引, 如果连为一列都没有怎么办呢, InnoDB的表中会存在一个隐藏列, 会使用这个隐藏列做一个一级索引, 反正肯定需要有一个聚簇索引树.

回表查询的优化方案,

where 后面的东西回去查询索引, 可能几个参数都有索引, InnoDB(语法树, 优化器)只会去找一颗索引树

联合索引可以节省空间, 联合索引根据最左前缀原则, 可以将一个联合索引当成多个索引来使用

联合索引的查找方式如下, 比如需要查找 where age = 9 and name = "T", 首先比较根节点, 9比50小, 查左节点, 比较比10小, 查左节点即P1, 然后找到9之后就开始向右查找name, 可以找到9, t的树, 即可找到主键的信息, 然后进行回表查询即可

覆盖索引 : 所以为什么不用select * , 会使索引覆盖失效

索引下推 :
首先存在一个业务层和存储引擎层, 还存在三种过滤, 业务层和执行引擎层(InnoDB)是根据接口进行调用的, 这个时候存在三种过滤关系 条件 index_key(InnoDB) index_filter (业务层) table_filter(业务层), 比如在InnoDB查到经过条件过滤的1000条数据, 在通过了接口传到业务层之后被indexfilter过滤的只剩10条了, 就很浪费时间

索引排序 : 如果可以使用组合索引解决order by的优化问题的话就尽量使用组合索引解决, 可能存在一个情况, 组合索引的字段是a, b, c , 这个时候order by a, b 的话查到的数据就是正好按照需要排序的, 如果ab不是索引的话, 还需要进行一个文件排序(file sort)需要姜一个数据放在一个文件缓存中, 在缓冲区中进行一个排序,这个时候就涉及到了缓冲区的大小问题,也许缓冲区放不下, 所以这个时候我们能用组合索引的话就尽量使用解决order by的问题

索引失效 :

什么情况会使索引失效 : 有那些情况 使用order by 进行一个索引的优化.

脏读: 读到了未提交的数据
不可重复读, AB 两个事务同时进行, b查询了两次数据, 在这两次查询之间, A事务修改了这个数据,导致这两次读取的数据不一致, 一般发生在update语句
幻读 : 主要发生在insert 和 delete语句之间, 导致查询到的记录的条数不一致.

串行化是基于(LBCC)读写锁的, 可同时加读锁, 其他不行, 存在等待和阻塞问题 导致性能差
可重复读和读取提交内容是基于MVCC的(版本控制, 类似于Git乐观锁) 读不加锁, 写加锁, 所以不冲突
然后MVCC是如何实现的 ? :

MVCC底层的实现使用了undo log 和redo log 这两个日志文件