MySQL学习笔记

学习小林coding的MySQL笔记

一条SQL语句如何查询？

MySQL查询时，首先进行的就是客户端连接上连接器。经历了TCP三次握手后建立连接。此时再修改该用户的权限，是不会对已经连接成功的用户造成影响的。 MySQL的架构分为Server层和存储引擎层，Server层负责建立连接、分析和执行SQL。存储引擎层负责数据的存储与提取。

MySQL的空闲连接会进行处理。默认是断开空闲超过八小时的连接。但是注意，断开之后，客户端是无法马上知道自己的连接已经被断开了的。只有在客户端发起下个请求的时候，才会收到报错 ERROR 2013 (HY000): Lost connection to MySQL server during query". 那么如何解决长连接占用问题？一个是定期断开长连接、另一种是客户端主动重置连接。

MySQL的连接数也有限制，默认是151个。

接着在MySQL收到SQL语句之后，假如是查询语句，那么在MySQL8.0之前的版本会默认使用缓存，查到了就直接返回，但是实际上很鸡肋，命中率不高。

下一步就是开始执行SQL了！首先会交接给解析器，进行词法分析和语法分析。

接着会交给预处理器，它会检查SQL语句中将要查询的表或字段是否存在，并将查询语句中的“ * ”扩展为表上所有的字段。

下一步就交给优化器，它会选择查询成本最小的一种方式执行SQL语句。

最后优化完成后，交给执行器执行。

执行器执行过程

有三种查询方式，分别是主键索引查询、全表扫描、索引下推。

索引下推(Index Condition Pushdown,ICP)

它能减少二级索引在查询时的回表操作，提高查询效率。因为回表后接着检查是否符合条件本来是server层处理的事情，索引下推后直接在存储引擎中处理，不需要跟server交互。

MySQL的一行记录是如何存储的？

• MySQL的NULL值会占用空间吗？
• MySQL怎么知道varchar(n)的实际占用大小？
• varchar(n)中n最大值是多少？
• 行溢出后，MySQL怎么处理？

MySQL的数据存放在哪个文件？

不同的存储引擎中，数据存放的位置不一样。最常用的是InnoDB。

假设一个数据库叫creeperbbs，其中一张users表.那么在/var/lib/mysql/creeperbbs/目录下，会有三个文件： db.opt, users.frm,users.ibd.

db.opt存储的是当前数据库的默认字符集和字符校验规则。 users.frm存储的是表结构信息。 users.ibd存储的是表数据。

表的空间是由段 segment,区 extent, 页 page 组成。记录是按行存储，但是数据库读取数据的时候，InnoDB是按照页为单位读取，不然磁盘IO就太多了。 默认每个页大小16KB。页的类型很多，有数据页、undo日志页、溢出页等。

段一般分为数据段、索引段和回滚段等。

InnoDB提供了四种行格式，MySQL5.7之后默认用Dynamic格式。

下面学习Compact格式，它跟Dynamic很像。

主要是两部分，记录的额外信息和记录的真实数据。

额外信息包含变长字段长度列表、null值列表、记录头信息。

注意，在变长字段长度列表中，实际的存储顺序是倒序的。因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的记录头信息和真实数据之间的位置。 同时，逆序存储也让变长字段的长度跟真实数据之间的物理间隔也更接近了，可以提高CPU缓存的命中率。

null值列表在InnoDB中用整数字节的二进制位表示，不满8位要补到8位。并且如果表的字段都是非null的话，就不会有null值列表了。它同样也是整数字节表示的。

记录的真实数据

这里除了我们定义的字段以外还有三个隐藏的字段，是row_id、trx_id和roll_pointer.

• row_id 它是6字节，自增的，如果表没有主键和唯一索引，那么InnoDB会自动生成row_id作为主键。 row_id并不是必须的。

• trx_id 事务id，6字节，表示最后一次修改这条记录的事务id。它是必须的，占用6个字节

• roll_pointer 回滚指针，7字节，表示指向这条记录的上一个版本，用于事务回滚。它是必须的，占用7个字节。

记录头信息

记录头信息包含内容很多，列举几个重要的：delete_mask标记数据是否被删除,next_record下一条记录的位置,record_type当前记录的类型，0表示普通记录，1表示B+树的非叶子节点记录，2表示最小记录，3表示最大记录。

varchar(n) 中 n 最大取值为多少？

我们要清楚一点，MySQL 规定除了 TEXT、BLOBS 这种大对象类型之 外，其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起 来不能超过 65535 个字节.

这个n的取值是最多存储的字符的数量，而不是直接指的是字节大小。它还跟字符集有关。 如ASCII字符集，一个字符是一个字节，那么n=100的时候代表最多存储100个字符，也就是100个字节。

所以在一个字符对应一个字节的情况下，varchar(n)中，n的最大值就是65535-变长字段长度列表-null值列表。 所以极限情况是，表中只有一个varchar字段，字符集是ASCII，无null值列表。同时变长字段长度列表是多少？ MySQL中的规则是，如果变长字段指定的存储大小<=255，则用1个字节表示；如果变长字段指定的存储大小>255，则用2个字节表示（最大65535，2个字节就可以表示，不可能出现3字节）。 所以n的最大值就是65535-2-1=65532。 为什么-1？

这里我也不太确定，小林coding的情况是他的字段可以是null，因此存在null字段列表。 但是如果不存在null呢？小林coding这里没有给出。

经过实际测试，发现不管是否允许为null,最大长度都是65532.

行溢出后，MySQL怎么处理？

有一些字段是大对象，如TEXT、BLOB，如果一个数据页存不了一条记录，InnoDB存储引擎会自动把溢出的数据存放到溢出页中，然后真实数据处用20字节存储指向溢出页的地址，从而找到剩余数据所在的页。