这学习MySQL，多是一件美逝啊_read view跟非聚簇记录-CSDN博客

本文链接：https://blog.csdn.net/weixin_45433191/article/details/122924519

前言：主讲InnoDB存储引擎，除非文中另行说明，本篇采取边学习边理解，使用CV大法的同时加点自己理解的学习方法，有问题的话可以留言扯皮或者保留你的问题。

啥是索引？

啊我就一句话，索引即目录，你说我说了等于没说？那我去研究研究你说的索引是啥。

MySQL的索引分三类：单列索引（普通索引、唯一索引、主键索引）、组合索引、全文索引

一、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。
（1）普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹是为了查询数据更快一点。
（2）唯一索引：索引列中的值必须是唯一的，但是允许为空值。
（3）主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

主键索引与唯一索引的区别：
（1）主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
（2）主键创建后一定包含一个唯一性索引，唯一性索引并不一定是主键。
（3）唯一性索引列允许空值，而主键列不允许为空值。
（4）主键索引在创建时，已经默认为非空值+唯一索引了。
（5）一个表最多只能创建一个主键索引，但可以创建多个唯一索引。
（6）主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
（7）主键可以被其他表引用为外键，而唯一索引不能。

二、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
例如：由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面的字段组合（id，name，age）、（id，name）或者（id）。如果要查询的字段不构成索引最左面的前缀，就不会使用索引，比如，age或者（name，age）组合就不会使用索引查询。

三、全文索引：在大量数据中，通过其中的某个关键字，就能找到该字段所属的记录行。全文索引在开发中很少用，因为其占用很大的物理空间和降低了记录修改性。

MySQL索引

索引和实际的数据都是存放在磁盘中的，在进行数据读取时，会优先将索引加载到内存中。

数据结构的选择

K-V格式的数据有很多种数据结构可以支持，为什么需要使用B+树来存？

hash表：为什么不用hash表来存储？hash表的底层是数组加链表的形式，当hash值发成冲突的时候，会将数据串联到相应的链表上，而链表的查询我们都知道它的诟病，需要全链路的去查询数据，这样就需要一个好的hash算法来使数据分布均匀，以防止链表过长。另一个方面，hash表是没有顺序性的，无法进行排序，它比较的是进行hash运算之后的hash值，无法保证运算前后的大小关系，导致无法进行区间范围查询
B树：B+树是由B树衍生而来的，B树会将索引和数据都放到各个节点上，举个例子，一个人，一栋楼，他需要去里面找东西，他在找东西的时候会获得关于这件东西的线索，同时也会找到他不需要的东西，线索与其他不需要的东西放在一起，增加了寻找时间不说，也浪费了空间，为什么不将线索房间里的东西都放在仓库里呢？让线索房间只放线索，将所需的物品统一按规则排好顺序丢进仓库，B+树就这么做了，相对于B树来说，B+树更加的矮胖，非叶子节点只存放索引，使得索引的存放量增多，叶子节点包含所有的索引和数据，且每个叶子节点都有指针做为连接，提高了区间访问的性能，一般来说千万级数据量只需要一颗3-4层的B+树就可以了。

聚簇索引和非聚簇索引

数据跟索引存储在一起的叫做聚簇索引,没有存储在一起的叫做非聚簇索引
说人话：innodb下，主键索引（聚簇索引）是和行数据绑定，并且存储在B+树的叶子节点上，二级索引（非聚簇索引）就是为了寻找主键索引的辅助索引，存储在B+树的非叶子节点上，找到主键索引意味着就找到了行数据。
InnoDB存储引擎在进行数据插入的时候,数据必须要跟某一个索引列存储在一起,这个索引列可以是主键,如果没有主键,选择唯一键,如果没有唯一键,选择6字节的隐藏主键rowid来进行存储
数据必定是跟某一个索引绑定在一起的,绑定数据的索引叫做聚簇索引
其他索引的叶子节点中存储的数据不再是整行的记录,而是聚簇索引的id值
如果是聚簇索引的话在找到索引的同时也能找到数据，因为索引和数据是聚合在一起存放的
InnoDB中既有聚簇索引，也有非聚簇索引
myisam中只有非聚簇索引，磁盘上索引与数据是分开存放的
概括一下：聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

由索引衍生出的一些问题

回表：什么是回表？回可以理解为折返，肯定是到过一个地方在才有回去的概念，那么回的链路是不是可以理解为->A点->B点->A点，为什么会回表？跟数据存储有关，比如我有一张表a，其中有id，name，age字段，id为主键，name为普通索引，sql如下：select * from a where name = "李四"，查找过程为：根据name到B+树上匹配到对应的叶子节点，然后获取到对应的id值，最后通过id值查询到所需的数据，这一个过程就产生了回表。简而言之，就是需要通过寻找多次的索引间的关联才能定位到所要数据。回表必然是浪费效率的操作，所以要避免回表。
索引覆盖：什么是索引覆盖？覆盖一词，对于B+树来说，覆盖的必然是索引及索引下的数据，因为B+树的结构是叶子结点上存储了全部的索引和数据，那么索引覆盖是好还是坏呢？剖析索引覆盖的本质：比如我有一张表a，其中有id，name，age字段，id为主键，name为普通索引，sql如下：select id,name from a where name = "李四"，此时，不会产生回表操作，为什么？查找过程为：根据name到B+树上匹配到对应的叶子节点，然后获取到对应的id值，此时，查询到的id,name的叶子节点已经包含了所有列的数据，直接返回即可。简而言之，已知三处宝藏点只包含三千万人民币，在寻找到两处时已经得到了三千万，你可以回去躺着不用再卷了。如果索引找完后无法得到所需的全量数据，则会产生回表，在某些场景下，可以选择将所需列建立组合索引来达到索引覆盖的效果以提升查询效率。
最左匹配：为什么是最左？最左是否可以理解为多个，什么索引能创建多个？见名知意来看，最左匹配说的该是组合索引了，组合索引的创建是有顺序的，这么来看最左匹配已经很明显了，依照组合索引的创建顺序来做查询，能使查询效率变快。在A,B,C三列上依次建立组合索引，A为引导列，就是引线，如果点不燃引线，那么后面的都烧不到，就是这个意思，但是MySQL有优化器，即使查询条件里ABC乱序的情况下（不考虑其他因素导致的索引失效），依旧会走索引。可以看看这篇讲最左匹配的
索引下推：在讲这个点的时候，需要对mysql的架构有一些概念，先看看这个->mysql的架构，再回来看索引下推是啥。如果你看了你大致能知道mysql的架构是：client->service->存储引擎。那索引下推是什么意思呢？举个例子，service是你的家，存储引擎是你家门口的鱼塘，当你去做查询时，就好比是去鱼塘中网鱼，你把鱼网起来后是要吃的，但你鱼塘里很多鱼，有几两几斤的，今天只吃3两重的，你会网起来各种重量的鱼，那么问题来了：你选择去家里筛选还是在池塘边筛选呢？当然，这只是一个比喻，但结果也很明显了。基于联合索引的条件下，如果有一条sql：select * from a where name = "鱼" and weight = “3”，在没有索引下推的情况下，先根据name去匹配，将满足name条件的数据从存储引擎中捞到service中进行weight条件的筛选，如果有索引下推，那么存储引擎会直接根据name和weight的条件进行筛选，将筛选后的结果返回给service，索引下推是一个配置。
现在我们基本都使用的5.6以上的版本了，默认就是开启索引下推的，想关闭的话可以通过命令

set optimizer_switch = 'index_condition_pushdown=off';

前缀索引：这个我就直接说了，索引是不是数据？是不是也需要磁盘IO？先加载索引到内存中，然后通过索引来对数据进行寻址获取，如果索引建立在一个很长的字段上（比如文本，字符串），势必会增加索引的大小导致索引臃肿，从而影响到查询效率，对于这种长索引，如果不能避免，则需要采取截取的方法，但在截取时需要注意的是，前缀的选择性上要接近索引的整个列，换句话说就是：你截取出的前缀要能最大程度的描述出这个列原本的样子，而不能说从人身上砍下来的东西让人看了不像是从人身上砍下来的（内心逐渐变态）

索引的设计

基于索引的某一些特性，我们很容易就能推断出索引设立的一些要点。

选择唯一性的索引，一般都会有主键
频繁查询的字段建立索引，以增加查询效率
索引不是越多越好，索引是需要IO的，索引也是数据，占用磁盘空间，修改表时，索引也需要修改
不宜建立在经常修改的字段上，如果字段经常被修改，那么会徒增索引的维护量
在查询多的表上，多建立几个索引无伤大雅，但要注意量
如果索引列数据过长，就需要考虑做前缀索引

SQL优化（面试福利篇）

sql优化呢，属于老生常谈的话题了，无论是面试还是工作，啊，基本都得会，不是说会，是要能够知道，什么样的坑不能去踩。
所谓优化在我看来无非就是去遵循某些规则，或者说是避免设计者遗留下的当时难以解决的问题，我是设计不了大牛搞出来的东西哈，所以唯唯诺诺用着大牛们的东西混口饭吃，也不能说啥，只能说：落后就要落后，所以大牛的东西，拿来吧你！
sql语句的优化，是基于大量数据后所产生的问题，10w级的表你跟我谈优化大可不必，但不能说10w级的表你就不考虑优化了，毕竟人的岁数会长，数据量也会膨胀，在sql编写的时候你就得考虑sql的性能了，或者格局打开一点，数据库设计的时候就该考虑考虑，比如字段是否过度冗余（曾经见过一个用户拥有6个电话的表），索引的建立是否合理，表与表的边界是否清晰等等，当然了这些都是题外话，我就不再扯些有的没的了。
sql的执行效率，一大点，就是索引的问题，或者说是在sql里一些语法使用会导致不走索引的情况发生，在工作中去尽量规避这些失效情况。

我忘了在哪记到的一个关于索引失效的七字真言，当然出处不重要，重要的是get到。

模丶型丶数丶空丶运丶最丶快

详解：

模：模糊查询like以%开头，无头有尾，我只好看每一条数据是否都有你说的这种尾巴了，怎么就没有后缀索引呢233
型：数据类型的错误，比如一个char类型的字段用int类型去查，会导致索引失效
数：对索引字段使用了内部函数
空：索引的列有null值
运：对索引列进行运算
最：最左匹配原则，需要按索引建立顺序进行查找
快：这个是根据数据库的优化自动实现的，如果数据库觉得走全表更快，就不会走索引了，比如说数据量小的情况下

还有一些其他的什么不要用select *啊，避免子查询啊，联合查询时小表在前大表在后啊，限制使用什么or筛选啊等等，这些网上比较多，自己去看看吧，我没梳理过，比较杂乱，等哪天心血来潮再补点上来

MySQL的隔离级别

隔离级别的作用是为了解决事务并发中可能导致的问题，说白了：就是多个事务的开启和提交的时机交织在一起后产生的一些问题。
具体来说有以下几种：

脏读（一个事务读取到另一个事务未提交的数据）
不可重复读（重点在于update和delete 一个事务内多次读取的数据不一样）
幻读（重点在于insert 一个事务内多次读取的记录数不一样）

扣张图看看各个隔离级别是如何解决或未解决上面这些问题的：
在这里插入图片描述

MySQL的日志

MySQL中有以下日志文件，分别是：
　　1. 重做日志（redo log）
　　2. 回滚日志（undo log）
　　3. 二进制日志（binlog）
　　4. 错误日志（errorlog）
　　5. 慢查询日志（slow query log）
　　6. 一般查询日志（general log）
　　7. 中继日志（relay log）
重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。
MySQL中的几种日志了解
其中比较重要的是 bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

bin log
bin log是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。

redo log
redo log是innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit设置为1，那么在执行commit时会将redo log同步写到磁盘。

undo log
除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

bin log和redo log有什么区别？

bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB自身的事务日志。
bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。
bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

事务相关：

MVCC（多版本并发控制）

作用：MVCC 主要解决事务并发时数据一致性的问题
MySQL 的 InnoDB 引擎实现了 MVCC

redo和undo：
redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
undo log用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

了解以上两个日志的作用后，开始梳理MVCC的一些点：

隐藏字段：MVCC 的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。

DB_TRX_ID：当前事务id，通过事务id的大小判断事务的时间顺序。
DB_ROLL_PRT：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链
DB_ROLL_ID：主键，如果数据表没有主键，InnoDB会自动生成主键。
每条表记录大概是这样的：

使用事务更新行记录的时候，就会生成版本链，执行过程如下：

用排他锁锁住该行；
将该行原本的值拷贝到undo log，作为旧版本用于回滚；
修改当前行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就形成一条版本链。

举个例子方便大家理解。
1、初始数据如下，其中DB_ROW_ID和DB_ROLL_PTR为空。
在这里插入图片描述
2、事务A对该行数据做了修改，将age修改为12，效果如下：

undo log
大彬 18 1 1 null

3、之后事务B也对该行记录做了修改，将age修改为8，效果如下：
在这里插入图片描述
4、此时undo log有两行记录，并且通过回滚指针连在一起。

read view的概念。

read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。

在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。
不同隔离级别创建read view的时机不同。

read committed：每次执行select都会创建新的read_view，保证能读取到其他事务已经提交的修改。
repeatable read：在一个事务范围内，第一次select时更新这个read_view，以后不会再更新，后续所有的select都是复用之前的read_view。这样可以保证事务范围内每次读取的内容都一样，即可重复读。

read view的记录筛选方式

前提：DATA_TRX_ID 表示每个数据行的最新的事务ID；up_limit_id表示当前快照中的最先开始的事务；low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。

如果DATA_TRX_ID < up_limit_id：说明在创建read view时，修改该数据行的事务已提交，该版本的记录可被当前事务读取到。
如果DATA_TRX_ID >= low_limit_id：说明当前版本的记录的事务是在创建read view之后生成的，该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本，然后重新判断该版本的记录对当前事务的可见性。
如果up_limit_id <= DATA_TRX_ID < low_limit_i：
i. 需要在活跃事务链表中查找是否存在ID为DATA_TRX_ID的值的事务。
ii. 如果存在，因为在活跃事务链表中的事务是未提交的，所以该记录是不可见的。此时需要通过版本链找到上一个版本，然后重新判断该版本的可见性。
iii. 如果不存在，说明事务trx_id 已经提交了，这行记录是可见的。
总结：InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保存有历史版本记录，通过read view 判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。