MySQL数据库再次复习【20220421】-CSDN博客

本文链接：https://blog.csdn.net/weisubao/article/details/124318368

阅读书籍：《MySQL是怎样运行的》

1、row的一些知识

row_format有不同的格式，大致分为记录头+真实数据区。在额外信息区有个标记是否删除记录的位，这意味着记录被删除时没有直接从磁盘中移除，而是维护在删除链中，当下次有新记录时，就会可能直接复用这些已被删除记录的空间。
默认页大小16K，主要存放记录的叫user records区，有新纪录了，就从free space中申请一个区域放数据然后这块就归属到user records中了，所以user records中放了很多条row_fromat格式的记录，每条记录都会指向下一个记录，这个指向位置是下一条记录row_format的记录头+真实数据区之间，真实数据是按照列的正方向排列的，记录头有个是否地方存放变长列的真实长度是逆序放的，所以指向位置在这个中间，向左向右读取距离比较近，且向左向右读取，列的顺序也能保持一致，这就是设计的细节。
user records中其实还隐藏了两条默认记录，最小最大记录，所以真实的指向下一条记录也是包括这2条记录的，顺序就是最小记录Infimum下一条就是我们普通数据的最小一条，然后依次指向下一条，我们最大的那条记录的下一条就是最大记录Supremum，最大记录Supremum的下一条记录是0，也就是没有指向下一条。
所以，我们插入一条记录和删除一条记录的话，user records中的记录的下一条指向链表是会发生变化的。删除的记录下一条指向位数字设置为0，删除标记头设置为1，链表重新链接。新记录来，如果复用这条删除记录的位置，则复用后，根据主键或者其他排序方式确定大小后，更新自己下一条记录位的数字并链接到单向链中。

2、page页的一些知识

上面说page除了user records和free space区外，还有3块主要的区域。第一个就是page directory，这个是放slot槽的地方，默认一个page有2个slot，Infimum一个slot，supremum一个槽，当有记录来了之后，会放在supremnum槽里，每个slot的最后一条记录存放了n_owned表示这个slot有几条记录，所以此时supremnum的n_owned数字会变化，当一个slot超过8条，即9条的时候就会分裂，自己留下5条，另外4条单独组成一个slot，所以一个page directory中有很多个slot。为什么要这么多slot呢？为了查找方便。因为这些记录是按照顺序排列的，所以slot也是有顺序的，查找的时候会先找到中间的slot，然后slot对应的是slot中最大那条记录，这个时候取出这条记录比较一下，如果不是则继续二分法找。需要注意的是，如果找到在第n个slot，那么需要通过第n-1的slot才能实现从第n个slot最小到最大的遍历。
page除了上面这个区域，还有两个通用的区域，叫File header和File trailor，File header中存放了这个page的很多信息，比如slot的数量、向左还是向右的插入顺序、已删除记录记录占用字节、最后插入记录的位置等等，还有个校验和。File Trailor也有个校验和，这两个校验和是用于检验这个page是否正确的关键，如果先更新了Header中的校验和，此时断电了，那么头尾的校验和不一致，说明这个page有问题。
页里面user records中每个记录都有单向链表链接起来。Page之间也是链接链接起来的，这个是双向链表。

3、Index的一些知识

如果没有索引的话，不管是在一个页中查找，还是从很多页中定位到目标页，都是一件体力活。有索引的话，如果查找的条件也符合，那么就容易查找（比如使用上面说的二分法）。
我们有很多页存放了用户记录，叫用户记录页。这个时候怎么定位到具体哪个页，除了一个个通过链表遍历，有索引的情况下就可以根据排列好的顺序快速定位，那么这就必须有个前提，就是有个地方存放了这些用户记录页的全部情况，就像在page内有很多个slot一样。这个地方也是一个page页，我们叫目录项页，本质上也是页，只不过这个page的record_type取值和用户记录页不同，代表存放不同的内容。既然是索引，那么这个目录项页中不仅记录了所有的用户记录页还记了这个用户记录页最小的值。这样我们就可以快速定位到目标用户记录页，然后找到这个page后再page内部再根据slot再次快速定位找到想要的记录。这个时候就有问题了，一个目录项page记载有限，所以就会发展出很多层级。所以我们看到的B+树就怎么产生了，B+树除了最下面的0层是用户记录页之外，上面的都是目录项页。我们一般的B+树有3-4层就足够使用了。页的Page Header中会记录这个Page是属于第几层Page_Level。
聚簇索引、二级索引的回表查询（新建的B+树的叶子节点存放的是索引列和主键值）、联合索引的顺序为什么重要（因为是先按照第一个排序然后相同的情况下再根据第二个进行排序，以此类推）。
当创建一个索引时，没有数据的时候那只有一个根节点，这个根节点所在的page地址在随后B+树的更新中是一直不变的。
如果是普通索引，那么目录项的索引值可能会重复，这时候怎么定位到合适的用户记录页？其实目录项页中除了page号码和索引值外，还有个主键值，也就是说当索引值一样的时候，还会比较主键值，然后取一个page页继续往下层查找。所以当创建一个二级索引的时候本质上是创建了一个该索引+主键的联合索引（Unique Index也是有主键的联合索引，因为unique可能有多个null值）。
InnoDB的聚簇索引相当于索引即数据，找到相应的索引就找到相应的数据了。其他的二级索引需要一次回表操作查询。MyISAM有单独的数据文件（一行行的记录），索引文件（存放的是索引值和行号），所以MyISAM天然的相当于二级索引，但这个查找也相当快，因为row长度一定，所以通过计算可以很快得到该行所在的文件的偏移量可以快速从数据文件中查找出数据。
确定查询边界条件这个部分讲解的很好，当有很多个索引的时候，如果查询条件很复杂可以挨个用不同的索引来分析确定最终的查询边界，如果最终分析出来的边界是（负无穷，正无穷）那么还有一次回表，还不如直接全表扫描，所以这个时候就不会用这个索引。挨个分析完，评估哪个边界条件最小（当然这个有时候需要大约评估时间）。

--下面这个联合索引其实起作用的边界条件就是key-part1的(负无穷,'b')
--因为只有联合索引第一个相同时第二个才会按照顺序排列，这里的<'b'包含了不同的第一个列值
--所以第二个列值没有排序，所以第二个条件不会发挥作用
SELECT * FROM single_table WHERE key-part1 < 'b' AND key-part2 = ' a ';
--下面这个和上面的稍有不同，因为当key-part1=‘b’的时候第二个条件就发挥作用了，所以有一点点不同而已
SELECT * FROM single_table WHERE key-part1 < 'b' AND key-part2 = ' a ';

如果确定的查询边界发现查询的记录占比很大，几乎查了整个表，那么这时候大概率不用索引，因为还有一次回表，那还不如直接全表扫描了。一般加上limit的话会倾向于使用索引+回表。如果没有limit，查询数量很大或者针对索引列全部记录做排序，那么就会倾向于全表扫描。
自增整数主键和UUID的另一个好处就是索引更新时Page不用过多的分裂和重组，因为主键是依次增加的，这样主键不会忽大忽小，导致记录总是插入在中间，然后导致现有的page记录超出需要拆解并新增page插入在其中，影响性能。

4、表空间的一些知识

表空间是一个逻辑概念，可能（只是可能）对应于一个实体文件。表空间其实本质上是有很多页组成的，只不过和组织管理类似，组织人太多了就要分层级，所以64个页会组成一个区（1M大小），256个区会组成一个组，所以一个表空间有很多个组组成，每个组有自己的区管理信息，每个表空间也有自己的表空间管理信息，这些信息也都要存在某个page页中，也就是表空间最开头的3个页以及每个段自己段最前面的2个page页中。因为要考虑到数据的增删改查等操作，所以这些表空间以及其中的组都维护了共用或者各自的链表。如果区是独立的，则可以维护进表空间的FREE（空闲）/FREE_FRAG（有碎片空闲页）/FULL_FRAG（没有碎片页的区）的链表中。如果区依附于一个组，那么这个组有自己的链表记载这些区的信息（比如FREE/NOT FULL/FULL）等。表空间就是依靠这些层层的数据结构记载了各种信息后，才能完成高效的数据操作。
第九章讲解的十分详细，理所当然地也十分琐碎，但结构还是很清晰的。这一章节需要反复阅读才能完全记住。

5、查询的一些知识

const表示直接主键等值查询，找到唯一的节点就是找到数据了（id = 100）。ref是要回表查询的那种等值查询，比如（key1 = ‘a’），又比如is null最多就属于这种，因为is null的列不可能是主键。ref_or_null其实和ref类似只是二级索引的时候不仅找到等值查询还找到是null的比如（key1 = ‘a’ or key is null）。方便记忆的话，以上的查询边界都是一个点，比如[‘a’,‘a’]或者[null,null]都是一个点。
如果查询边界不是一个点，而是多个点，这个时候可能就是范围查询条件了，成为range，比如key1 < ‘a’。
index类型的查询，说明是在索引上做的查询，但它是全表的查询，为什么在索引上做全表查询呢？因为在聚簇索引上全表扫描效率慢，在某个小的索引上会快一些，第二个原因是这个小索引大概率是个联合索引，可以直接返回要的结果，不用回表，索引查询优化的时候就让这个查询直接在这个小索引上进行查询，虽然也是全表扫描，但毕竟会好一点点。所以称为index。比如有key1/key2/key3组成的索引，用select id,key1,key2,key3 from aaa where key2 = 'xxx'就走了这个联合所以取查，查完直接返回结果不用回表。此外，比如order by id的话，也是被认为是index方式，因为虽然是聚簇索引上的，但也是全表扫描。all的方式就是全表扫描。
回表操作是查到一条就回表一次，不是一次性查到所有再回表。
这里有个特殊的index merge索引合并（intersection）的查询。比如where key1 = 'a' and key2 = '3'这个时候是两个索引各自按顺序找出结果然后比较，如果id是一样的，那么说明满足条件就去回表，如果不一致，则扔掉id小的那个，扔掉的那个索引继续再查一条记录。这里的关键就是他们的id要是按顺序排列的，如果不是按顺序则不能index merge索引合并（二级索引都会带着主键id，而且当索引值一致的时候id是按顺序排列的），所以条件改成where key1 > 'a' and key2 = '3'的时候就不可以索引合并了，因为第一个索引查出来的主键id没有排序，没法比较。但聚簇索引比较特殊，因为他本身就是id排序好的，所以where id > 100 and key2 = '3'这种查询也可以索引合并，很像取两个索引结果的交集。
下面很自然的想到索引的union并集，前面是and，那么这里就是where key1 = 'a' or key2 = '3'，同样的也要求这查出的主键能排序才满足条件。
还有一种叫sort-union，听这个名字就是对上面union的一种改进。因为不然的话，很多查出结果不满足主键排序的都没法使用union方法提高查询效率，所以这个sort-union就是先查出不同索引的结果集，如果主键id没有排序，那就先排序，排序后，你就没话说了吧，union方法就可以使用了。不过好像是没有sort-intersection的。

6、多表查询的知识

多表时，要区分驱动表和被驱动表，驱动表一般就是左连接的左边的那张表。驱动表根据查询的条件确定使用一种查询方法，一般最多只查询一次表。查出结果后，有多少条结果就去被驱动表中查几次，所以如果join太多表，第二个表查出来的结果又当成驱动表，再去查下一个的被驱动表，这样就是为什么不建议join太多表的原因。
其次，查询被驱动表效率如此低，于是就想开辟一块内存先存放驱动表结果集，然后尽量加载更多的被驱动表数据，可以在内存中进行多次比较获得最终结果，所以我们可以通过优化查询或者调整这个join_buff_size内存大小来提高效率。

7、查询优化的知识

查询到底怎么优化，是通过计算成本来决定的。先计算出全表扫描的成本，然后再看看用到哪些索引，各个索引再估计其成本，然后选择成本最小的那种。关于成本，包括IO成本和CPU计算成本。每种操作都有一个成本常数，可以通过数据库设置，如果不设置的话它默认是NULL就是采用默认的数值进行计算。
计算上面这些成本会用到表的一些统计数据show table status like '表名'，比如大约有多少数据，数据总长度是多少（为了计算涉及多少个页Page）。
那么问题来了，以上这些统计数据存在哪里？啥时候收集的？这些数据存在innodb_table_stats和innodb_index_stats中，比如存放了表的大约总记录数（因为是在页中抽样然后估算了每个页平均值后算出来的）、聚簇索引占用的size和其他索引占用的size（这个就涉及到表空间的那些知识了，表空间那边有很多的段区和页还有很多FREE/FREE_FRAG之类的链，就是利用这些综合计算出来然后存在这两个表中的）。系统中有个inndodb_stats_auto_recalc默认是开启，大约是记录变更数量超过总表10%就会触发一次异步更新。也可以用flush table xxx直接手动更新。
优化器其实相当于会重写查询sql语句。这里面主要是去除一些不必要的括号，对all转成max()、对any转成min()等。其中有个主要的in子查询需要注意一下：一般的结果是一个值的那种子查询，就会先查出子查询，然后变成正常的查询语句再次查询。但是in不同，它有可能被优化，优化的方法就是，可能是把子查询的结果生成临时表，然后用这个临时表和父查询join。或者干脆不生成临时表，只是改写这个查询语句叫做半链接的形式，这个也是取决于具体查询的条件。

SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM  s2 WHERE key3 = 'a' );
--上面的in可以优化成下面的这个sql
SELECT s1.* FROM s1 INNER JOIN s2. ON s1.key2 = s2.key2 WHERE s2.key3 = 'a' ;

用explain + sql可以查看执行计划的信息，explain format=json + sql可以格式化查看更多信息。索引条件下推（lndex Condition Pushdown）的意思是正常情况，下面的语句前面条件可以形成边界条件，后面的条件不能，所以会用前面的条件在索引中找，找到一条就回表一次，然后回表找到的结果给server后，server层判断是否满足替他条件（这里就是判断是否满足like），所以这里是每一条都要回表。

--索引条件下推就是这里的另一个条件能在索引中直接判断掉，所以根据范围边界获得一个记录后先不着急回表
--而是直接判断是否满足like条件，不满足则继续下一条，满足则再回表
--回表获取记录给server，server再判断其他条件（这里没有其他条件了）
--这样其实就是减少回表操作
SELECT * from s1 WHERE key1 > 'z' AND key1 LlKE '%a';

优化信息查询optimizer_trace。默认关闭，需要开启。

--1、开启
set optimizer_trace = "enabled=on";
--2、执行个正常的select语句
--3、到系统表中查询优化信息，这里面包括和十分详细的准备阶段、成本分析阶段以及执行阶段等信息
select * from information_schema.OPTIMIZER_TRACE;

8、缓存的一些知识

查询的结果其实会缓存起来，这个缓存其实就是从磁盘加载到内存中。这个内存其实就是一片连续的内存空间，叫Buffer Pool。之前如果要修改这个大小，那么会将旧内存中的数据拷贝到新内存中，这会影响性能，后来内存分为很多个chunk，如果要修改大小的话，只是增加几个chunk，避免复制。
缓存的内容本质上是Page页，所以Buffer Pool中也按照页大小切分了很多页，为了管理这些切分的页，还引入了控制块记录页的信息。还引入了FREE链记录哪些页是空闲的用于分配。还用flush链表记录哪些页也被修改下次可以flush进磁盘。还是用LRU链表记录到时候内存不足时替换掉哪些页，最简单的就是每次访问到一个页了就把这个也挪到LRU最头部，以后要删的话就删尾部，但是这样操作太频繁，所以做了优化：比如把LRU分成old区和yong区（热区），有些全表查询的和预读的那些数据线放在old区域，如果后续频繁访问到了再放到yong区域，而且yong区域也优化了比如访问的页在yong区域的后1/4时才挪到LRU前头部，这一切都是为了尽量减少LRU的操作，也避免频繁更新LRU导致命中率下降。提高命中率，是怎么判断这个页在不在的？是通过哈希，表空间+页号作为key存在哈希表中，下次比对就知道这个页在不在内存中了。

9、日志文件的知识

redo日志相当于重做的一个个步骤，文件是循环覆盖使用，但这个覆盖还结合checkpoint保证覆盖掉的都是已经刷到磁盘不需要的那些redo日志。redo日志是为了保证持久性。
但是如果事务的redo日志记录到一半断电了，这时候其实这个事务没有成功，用redo的话最后几步也会被执行，无法回滚。这时候undo日志就出现了，undo日志主要是为了保证事务的原子性，通过维护了回滚段、回滚段中的slot、slot对应了链表、找到active的slot、然后找到最后一条log的位置，找到里面的事务以及详细信息，执行undo回滚即可。

10、事务隔离的知识

不同事务读写相同数据，最终读到的是什么数据？这其中有个重要概念：版本管理MVCC。类似于一条记录有一个修改版本记录，其中记录了每个事务id，如果一个事务读的时候先生成一个ReadView，这个ReadView厉害了，其中的m_ids记载了活跃的事务id（就是还没提交的事务id，没提交就意味着这些数据比如在read committed隔离级别下是不可见的），这样到底读取那个版本的数据，就从最新的版本结合这个m_ids倒推，一直找到不在这个m_ids中的那个版本号的数据即可。那么repeatable read是怎么保证先读一个版本，其他事务提交，再读还是这个版本呢？因为它这个事务只生成一次ReadView，因为只生成一次ReadView所以不管读多少次都只根据这个ReadView中的m_ids来判断，那么读到的数据当然就一直是相同的，就是这个m_ids最小id之前的那个版本号对应的数据。
除了使用MVCC解决一致性的问题，还可以使用锁。锁分为共享锁S和独占锁X。S和S兼容，S和X、X和X不兼容。还有表级锁和行级锁（Record Lock、Gap Lock、Next-Key Lock等）。
锁这一章节还需要再复习一遍，这一次太仓促没有耐心看仔细。