mysql索引原理及多版本控制(MVCC)原理

索引常用的数据结构：

         二叉树:取第一个值作为根节点，取第二个值和第一个值比较，小的放左边、大的放右边，如果是顺序排列的数字，则全部放在右边就像链表一样了
        红黑树:在二叉树上做了优化，连续出现两个都是大于或小于的数，则会自旋一次，如5后面插入6、7，插入后自旋一次6的左边是5、右边是7
        Hash表：每个值都有一个hash值，hash值索引记录这个hash值和数据的对应关系，对于 =、in等查询效率很高，对于范围查询如 大于、小于、like则很慢，只能全表扫描
            B-Tree:根节点不再是一个节点，而是多个节点的集合，这样可以较少树的深度
            B+Tree(B-Tree的变种)：
                   非叶子节点不存储data,只存储索引(冗余)
                   叶子节点包含所有的索引字段(数据库表数据)
                   叶子节点用指针连接，提高区间访问的性能

B+Tree和B Tree：

        1、由于索引文件很大并存储在磁盘上，B+Tree的非叶子节点存储的都是索引关键字，单个页可以存储更多的关键字；B Tree非叶子节点也存储数据、单页存储的索引关键字少。所以B+Tree需要读取索引关键字的次数会少一些，减少IO提升效率；

        2、B+树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+树的效率更高。

    B+Tree 和 B Tree的结构：       

Mysql的索引结构：

    B+Tree 和 hash值两种方式，现在索引基本上都是使用B+Tree(也是默认的)，hash也只在某些特殊场景下使用；

        1、B+Tree索引:

            (1)、索引都是有序的，；
            (2)、一张表中只有一个聚集索引：通过主键(没有主键的话数据库会自动生成一个rowid)进行排序的索引；
            (3)、其他的非聚集索引的叶子节点存储的是主键的值，查询的时候首先在这个非聚集索引中定位到主键，然后通过主键在聚集索引中找到对应数据，这种情况叫做回表查询。如果非聚集索引中筛选出的主键比较多，此时回表查询的效率会比较低，mysql的优化器会选择全表扫描而不走索引，所以不是走了索引就表示效率高；
            (4)、如果查询的条件及结果都在一个非聚集索引中，此时不需要回表查询，这种情况叫做覆盖索引查询，效率比较高；

    非聚集索引的数据结构，以联合索引为例，最下面的数据为主键：

        (5)、B+树的深度：数据的存储是按照页来存储的，每一页的大小默认是16kb

               1、对于非叶子节点每一层级能容纳多少个节点和索引的大小有关系，如果索引列是int类型(范围从-2^31到2^31-1，大约正负20亿)占用4个字节，每个节点有一个指针是6个字节(固定大小)，那么一个节点占用10个字节，则非叶子节点一层的节点数是 16*1024/10 = 1638 个，如果索引列是bigint占用8个字节，则非叶子节点一层的节点数是 16*1024/14 = 1170个；

               2、聚集索引的叶子节点一页能存储多少条数据跟每一行的数据大小有关，假如叶子节点一行占用1kb，则一页能存储16行数据；

               3、B+树的深度计算，按bigint、一行1kb计算，2层结构能存储1170*16 = 18720条数据，三层结构能存储 1170*1170*16 = 21902400 也就是大约2000万条数据；

         (6)、索引字段的长度：

                 1、不同的编码格式每个字符占用的字节数也是不一样的，我这里是utf8mb3，每个字符占三个字节，另外不同版本的mysql支持的索引长度是不一样的，我这里是8.0.29索引长度不能超过1000

                 2、查看索引的长度可以通过explain查看执行计划中的key_len，如果是复合索引在每个索引值都匹配了查询条件的情况下key_len即为索引的长度

                 3、如果列可以为空，则索引长度加1，如果是可变字段长度如varchar则索引长度加2，可参考：SQL优化（五）索引长度_sql索引字段过长_万里归来少年心的博客-CSDN博客

    2、hash值索引：

          每个值都有一个hash值，hash值索引记录这个hash值和数据的对应关系，对于 =、in等查询效率很高，对于范围查询如 大于、小于、like则很慢，只能全表扫描；

索引失效的三种情况：

        1、查询条件导致索引失效

             1)、like前面有 %;

             2)、对索引字段进行了类型转换：date(clo1)='2022-01-01'，或给字符串是varchar类型的列传入数字类型条件；

            3)、在索引上使用了函数 如 left(col1,2)='zs' 或 使用计算的表达式 如col1+col2 > 100;

            4)、创建索引时对索引列使用了函数，必须使用这个函数作来匹配条件才有效

                   如： create index a_index on table1(to_char(column1));

            5)、不等于、not in、not exists 也会是索引失效；

        2、不满足最左匹配原则

                复合索引的左边字段没有使用索引，右边字段的索引都不能用；

        3、回表查询时需要传给 聚集索引 的值太多，数据库解释器会觉得全表扫描更快

索引合并：

        一个表的多个查询条件都有索引，查询的时候用到了两个或多个索引，叫做索引合并；

        索引合并一般不会出现，只有查询条件是全词匹配或者其中一个条件是id、并且每个索引查询的数据都比较多但是交集后数据比较少的情况下才会出现；

        参考：Mysql——》index merge 索引合并_mysql index merge-CSDN博客      

 多版本控制机制(MVCC)：

          数据库隔离级别中读已提交、可重复读 使用了MVCC；

         读已提交 和 可重复读的区别是生成read-view(记录库中未提交的事物ID 及 已提交的最大事物ID)的方式不一样，读已提交 是每次查询都生成一次，可重复读是在一个事物中第一次查询就生成一次、后面不再生成；

        MVCC的实现原理：

               1、数据库中的事物id是递增的；

               2、对数据库表的每一次操作都会记录undolog；

               3、根据读已提交 和 可重复读 生成的read-view从undolog获取对应的数据;                 

课件相关资料：链接：https://pan.baidu.com/s/1peEnotKgJ_tjFbY4escZjg?pwd=f67n