MySQL数据库

MySQL分为两层Server层和存储引擎层

数据的执行过程是在Server层进行，Server层负责建立连接、分析和执行SQL语句；存储引擎以及数据存放在存储引擎层，存储引擎层负责数据的存储和提取。

MySQL的执行流程

1.连接器

职责

1. 通过TCP三次握手使客户端与MySQL服务端进行连接；
2. 认证：检验登录合法性，如果用户名或密码不对，则会报错；
3. 鉴权：如果用户名和密码正确，登录合法，读取该用户权限，根据权限允许一些操作，权限的管理能够细化到表级别；

如何查看MySQL服务被多少个客户端连接：show processlist命令，命令执行后会显示客户端的ID、用户名、主机、状态等信息。

空闲连接会一直占用吗：MySQL定义了空闲连接的最大空闲时长：wait_timeout参数，默认8小时，超过这个时长连接会自动断开；或手动断开，kill connection +id命令。

MySQL的连接数有限制吗：最大连接数由max_connections参数决定，超过max_connections会拒绝接下来的连接，返回报错信息：“Too many connections”

长连接与断连接

长连接

通过一次的 TCP三次握手 建立连接，可以执行多条sql语句，待sql语句执行完，通过 TCP四次挥手 断开连接；避免了连接多次建立和断开的过程，但是由于断开连接时资源才会释放，所以长连接累积过多或导致MySQL服务占用内存太大，可以通过服务端定期断开长连接与客户端主动重置连接的方式改善内存占用太大的问题。

优点：减少连接建立和断开的开销

缺点：长时间会占用服务器端的资源，导致资源浪费，如果应用程序没有正确释放长连接会导致内存泄漏。

解决方法：心跳机制，在较短的时间间隔内客户端主动将自己的情况报告给服务端（定时发送心跳包），以便于服务端维持或断开长连接。

短连接

一次的 TCP三次握手 建立连接后，只允许一条 sql语句执行，该条sql语句执行完，立即通过 TCP四次挥手 断开连接，执行下一条sql语句时仍然要建立连接，连接及时释放，不会导致资源浪费；

优点：资源释放快速，短连接在每次请求完成后立即释放连接资源，不会占用服务器资源。

缺点：频繁建立和断开连接开销大，服务器压力大。 

2.查询缓存

连接器工作完成后，客户端就可以向MySQL服务发送SQL语句，如果解析出来的是select查询语句：

MySQL查找服务端建立的缓存表(Query Cache)中是否有对应的缓存数据，如果有则直接返回value结果，如果没有则继续执行,执行完成后，查询的结果就会放入缓存表中。

8.0版本已废除这项功能，若更新表的操作太过频繁，在缓存表中命中结果的概率会降低。原因：一个表有更新操作，那么这个表的查询缓存就会被清空。并且更新缓存时磁盘中的表数据会向缓存中加载，造成磁盘消耗大。

例如刚刚缓存了一个数据，但是还未使用，表就更行了， 查询缓存就被清空了，前一步的缓存操作就很多余。

3.解释器

在执行sql语句之前，要先进行解析，解释器中主要进行词法分析和语法分析

对于词法分析是将语句分割为一个个关键词，整理关键词与非关键次的数量，为语义分析做铺垫；对于语法分析，根据词法分析的结果，根据语法规则，检查语句的语法是否正确，并构建语法树，关键词是否正确、关键词位置和匹配信息是否正确、逻辑意义是否正确。

4.执行器

经过解析器后，接着就要执行sql语句了。

预处理阶段

对语句进行预处理，检查表或字段是否存在，是在这一阶段完成的。

• 检查SQL语句的表或者字段是否存在（调用get_table_share()函数）
• 将select *中的 * 扩展为数据库表中的所有列

优化阶段

为SQL语句选择最佳的索引方式，使其效率最大。

执行阶段

根据优化阶段选择的最佳的索引方式直接执行sql语句

文件的存储结构

底层是B+树，节点中只存放索引key值，具体信息的索引存放在叶子结点的索引中，MySQL采用这样的存储结构可以是的以页为单位的索引中可以存放更多的结点;

 为什么MySQL采用B+树作为存储结构？

磁盘的I/O效率高：B+树的存储结构中非叶子结点存放索引，叶子结点存放真实数据，叶子结点间是双向循环链表，能存储大量数据，尽管记录几万条，B+树的存储结构也是3或4层，MySQL与磁盘的I/O操作也就需要3或4次，磁盘的I/O效率高；

可以范围查询：B+树叶子结点间是双向循环链表，既可以左查询又可以右查询；

插入删除效率高：B+树有大量的冗余节点（所有的非叶子结点都是冗余索引），这些冗余结点让B+树在插入删除效率都很高；比如删除根节点，不会像B树那样会发生复杂树的变化。

为什么不采用Hash/HashMap这种存储结构而选择B+树呢？

答出以上内容的同时答出：

B+树支持按顺序存储和范围查询，而hash结构不支持范围查询，因为Hash是基于Hash函数计算的无序存储结构；B+树的内部节点和叶子节点形成有序链表，这使得在执行顺序访问时非常高效，Hash机构没有内在顺序，无法提供顺序访问性能。

HashMap本质上是数组，不能够实现范围查询，并且插入删除效率低。

为什么不采用B树这种存储结构而选择B+树呢？

B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

为什么不采用二叉树这种存储结构而选择B+树呢？

B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据，而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

新建表后的存储（非重点）

例如新建了表my_test，则它的存储目录为/var/lib/mysql/，在/var/lib/mysql/my_test目录中有

[root@xiaolin ~]#ls /var/lib/mysql/my_test
db.opt  
t_order.frm  
t_order.ibd

db.opt存放数据库默认字符集和字符校验规则

t_order.frm会存放t_order的表结构

t_order.ibd会存放t_order表数据

表空间的文件结构

表空间由段->区->页->行组成，下面是InnoDB的存储结构：

逐级管理；

MySQL整体的数据结构

数据页/索引页

页格式示意图

文件头

页号：页的标识

校验和：

•         形成过程：用hash算法将文件头到文件尾进行Hash计算，得出的值就是校验和，保存在文件头中。
•         作用：检验文件中数据的完整性。当MySQ访问文件时，会先读取文件头部的校验和，并对文件内容重新计算校验和，如果两者匹配，则文件未被损坏或篡改，数据是完整的，如果不匹配，说明文件是存在问题的。

注意：上一个页的页号和下一个页的页号组成B+树的双向链表。

文件尾

与页头一起校验页的完整性；

页头

第一个标记为删除的记录地址（作用是找到垃圾链表，插入数据）；

还未使用空间的最小地址，及最小记录所在的地址；

本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）；

页类型；

用户记录

存放实际存储的记录的内容，既有有效记录又有已删除的记录；

用户记录插入数据页中的组织形式：每条记录都有主键，这些记录按照主键顺序排序，这种排序通过页目录和记录的物理位置间接形成有序的数据链表；

slot区（页目录）

基于二分查找的思想设计的，记录页中某些记录的详细的相对位置，为了快速查询。

1. slot区通过在数据页中选取 特定间隔的记录形成“槽”，并标记序号，这些序号覆盖了整个数据页的范围。每个槽包含了一个指向页中相应记录的指针（槽中起始记录的主键值）。
2. 查找数据页中的特定记录时，会首先利用二分查找法来搜索slot区，通过比较目标值与页目录中各个槽记录的主键值，迅速缩小查找范围，定位最有可能包含目标记录的槽。
3. 找到了目标槽，该槽指向的记录开始，通过链表遍历的方式进一步查找并定位到具体的目标记录。

二分查找：是对有序且存储结构为数组的数据进行查找，其步骤：

1.确定数组的起始位置（low）和结束位置（high）；

2.计算中间位置mid=(low+high)/2；

3.比较并缩小范围：

中间位置的值等于待查找的值，则查找成功，返回该位置

中间位置的值大于待查找的值，说明数据在左半边，更新查找范围，high=mid-1

中间位置的值小于待查找的值，说明数据在右半边，更新查找范围，low=mid+1

4.重复23步骤，若low>high,查找范围为空，数组中不存在该值，查找失败；

一行sql记录在Mysql中是如何存储的？ 行格式

记录的额外信息

变长字段长度列表：存放记录的真实数据的中变长字段（varchar）的实际长度，以列的顺序的逆序存放。

NULL值列表：存储被NULL修饰的列（XXX is NULL）;真实记录数据中每一列对应一个二进制位，二进制位为1时代表该列的值为NULL,为0代表不为NULL;二进制位按照排列顺序的逆序存放;必须使用整数个字节位表示（1byte = 8bit）,不足整个字节，高位补0。

记录的头信息

• 标识此条记录是否被删除：delete_make,若这个记录被删除，delete_make=1
• 下一条记录的位置：next_record,指向下一个记录的 记录头信息 与 真实数据 之间
• 当前记录的累心：record_type,0表示普通类型，1表示B+树非叶子结点的记录，2表示最小记录，3表示最大记录 

记录的真实数据

隐藏列：

row_id :6字节，若建表的时候没有指定主键或者唯一约束列，用row_id标记行

trx_id:6字节，表示数据由哪个事务生成，事务id

roll_pointer:7字节，记录上一个记录的指针

数据列：存储真实的数据