mysql基础知识

mysql的执行过程

连接器

首先需要先登录账号密码，使mysql服务器和客户端进行连接，一个服务器可以连接多个客户端，和HTTP一样，连接都需要经过三次握手，都有默认开启长连接以节约多次请求反复连接带来的性能损耗。

查询缓存

这是mysql5.7的功能，到mysql8.0被废弃了。原因是因为缓存的使用效率极低，在一个频繁需要更新的表中，每进行更新一次，缓存就会全部清除，不管更新的和你缓存中的字段的关系大不大。所以mysql中查询缓存的命中率极低。在mysql8.0被废除。

mysql5.7查询缓存的主要流程为先判断客户端发送的sql语句的第一个关键字段，如果是select才进行查询缓存的操作，若不是则进行接下来的后续操作。缓存会以(key, value)记录先前的查询的sql语句以及查询的结果，如果缓存命中了，则直接返回其value值。如果查询不到缓存，则查询后将查询结果加入缓存中。

解析器

词法解析
mysql会分析sql语句，将每一个字段构建成为语法树。方便后续获取表名称，字段名称，where查询条件等。
语法分析
根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。
如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错。比如，我把 from 写成了 form，这时 MySQL 解析器就会给报错。
此时报错仅仅是sql关键字的鉴别报错，如果输入了不存在的表名或者字段名时，在此阶段不会报错

预处理器

判断语法树中的字段在表中字段中是否存在
将所有的* 解析为表中的所有字段

优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来

这一部分主要是索引的优化，sql优化器会通过对比，选择出主键索引，联合索引，二级索引中执行效率最高的方式，并在后面执行器中执行。

执行器

主键索引查询
全表扫描
索引下推

通过遍历的方式进行执行(while循环)，先去逐条查询所有的语句，之后再与条件进行对比，如果匹配条件的话，则将其加入结果集中，如果不匹配条件的话，则进行下一条的筛选，直至表中的字段被筛选结束。

没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，进行二次判断

存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。

所以我们可以认为，索引下推使用到了联合索引的特性，将联合索引中另一个索引的值在存储引擎层就已经进行查询了。

mysql记录存储结构

mysql数据存储文件

一个表对应了三个文件对数据库进行存储：

1
2
3

db.opt  
t_order.frm  
t_order.ibd

.opt 文件是对于文件字符集和默认字符校验规则的规定
.frm文件是对于对表结构格式的存储，存储的是表结构的元数据，如列名，表名，存储引擎等基本数据结构。
.ibd文件是对于表数据的存储。

表空间文件结构

从小到大，依次为段(segment) > 区(entend) > 页(page) > 行(row)

行

其中，行为最小的存储结构，可以看作对应的是表中的每个对象，一个对象即为一行，一行的大小规定最大为65535个字节，初始化定义的时候超过该大小则会报错。

页

但是数据表的读取和写入不以行为单位，为了避免传输的时候资源的浪费，以页为单位进行读取和写入，一页的大小为16kb，页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的

区

在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

段

索引段：存放 B + 树的非叶子节点的区的集合；
数据段：存放 B + 树的叶子节点的区的集合；
回滚段：存放的是回滚数据的区的集合，事务隔离 (opens new window)介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

COMPACT行格式

记录的额外信息
- 变长字段列表
- Null值列表
- 记录头信息
记录的真实数据
- row_id
- trx_id
- roll_ptr

变长字段列表

char(n) 和 varchar(n) 的区别为前者为不可变长的字符串类型，后者为可变长的字符串类型，后者的n表示的是该字符串允许的最大字符数。如果不存在可变长的类型，那么该额外信息字段将会被取消。存储的时候，存储的是其字符串的字符长度，比如存储aaa到 varchar(256) 的字段中，那么变长字段长度列表就会存储一个 3 。注意当出现多个可变长字段列表的时候，在表中的顺序越靠近左边的，在变长字段长度列表中存储的位置是靠右边的。这是因为记录头信息中有一个指针由该行指向下一行，他的位置是记录头信息的末尾，也就是接近真实数据和额外数据的分界线。那么他读取的时候，会左右都读，往左边读的时候可以理解成从后往前，所以变长字段长度列表从后往前进行记录也是符合底层存储引擎读取的顺序，能一定程度上提高性能。Null值列表也是同理的。

注意这里的n指的是字符数而不是字节数。比如在ascii字符集中，一个字符占一个字节，而在UTF-8字符集中，一个字符占了三个字节。

Null值列表

同理的，Null值列表也不是一定要存在的，其只有在某个或者某些字段规定允许为空的时候出现。所以我们平时设计数据库表的时候能设置非空就设置非空可以一定程度上节约数据库存储的额外性能。那么非空字段存储是通过二进制位来进行表示的，其非空字段必须为8的整数倍，高位用0补齐。如只有三个非空字段且其均为空，那么该行的非空字段可以表示为 00000111，注意，其也是逆序排序的。一行就是一个字节甚至根据非空字段的大小可能更多，所以尽可能的少用空字段。

当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

记录头信息

delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据

row_id

如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。

trx_id

事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。

roll_pointer

这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

varchar(n) 取值最大为多少

结论：varchar(n)中n的最大值为65533

首先我们知道一行最大能承受的字节数为65535，那么n代表了字符数，我们以最小兑换比例1：1的ascii进行兑换，那么可以存储65535个字符，但是不是这样的，我们从上面 COMPACT行格式 中可以看出，字段中除了记录的真实数据，还有记录的额外信息，那么这些额外信息也需要占用一定的字节。我们在考虑可变长字符串的最大长度的时候就要将其考虑进去。那么可变长字符串有一个分界线为255字节，当其小于255时候，长度为1个字节，大于时长度为2字节。所以需要减去记录其长度的两个字节，那么就是65533了

如果当我们设置该字段为允许为空时，我们还需要考虑Null值列表。即使存储的数据字段不为空，那么也会存储0表示存储的是空值，以区分数据传输时候丢失和传入的值本来就为空。所以此时，需要加上一个字节。

当然，前面考虑的都是一行只有一个varchar(n)字段，当有多个字段的时候，能够存储的空间就更加小了。

记得有一道面试题，数据库一行能存储下一本小说吗？其实就是说的这个，短篇小说应该还是可以的，还得是英文小说，不然ascii字符集没有对应的中文就不好了。如果是中文小说，那么最多只能存储65533/3的字节长度，也就是最多2w多字了。

行溢出后mysql如何处理

当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示。