MySQL索引

学习链接

https://www.bilibili.com/video/BV1xh411Z79d?from=search&seid=2478256420456023172

参考：https://zhuanlan.zhihu.com/p/35811482 从数据库底层数据结构层面讲了索引以及数据真实的存储形态。非常好的补充。值得抽空读一读分解细化。

慢SQL优化的常规做法就是使用索引

索引的本质

本质上是一种排好序的数据结构

为什么使用索引：
数据库的查找本质是在与磁盘做IO交互
不走索引的话就是全表无序扫描，索引则是根据已经排序的数据结构能够迅速get到对应位置。如果走聚簇索引，搜到的位置就是直接的物理存储位置，如果做费聚簇索引，搜索到的就是指向真实物理位置的指针。

索引数据结构优化之路

二叉树：有序集合时退化为链表
红黑树：二叉平衡树，一定程度上可以平衡高度，但是还是偏向于一边
查找效率低下

效率与树的高度成正比，高度越低效率越高
对红黑树进行改造，减少高度，就是处理核心

横向越大，树的高度越低

B树（PGSQL的索引结构）：

B+树

一个索引旁边会出现下个节点的指针

B树与B+树之间的区别（高频）

B树与B+树之间的区别，见上图，实际上B+树与跳表比较相似

总结：
1，B+树索引有冗余，某些节点出现在每一层
2，非叶子节点不存放数据
3，叶子节点包含所有字段等
4，B树中叶子节点没有存指向周围节点的双向指针，之间无法互相查找，只能继续从上个节点去查询

B+树相比于B树，有什么优势呢：
1.单一节点存储更多的元素，使得查询的IO次数更少。
2.所有查询都要查找到叶子节点，查询性能稳定。
3.所有叶子节点形成有序链表，便于范围查询。
总结，B+树相比B树的优势有三：1.IO次数更少；2.查询性能稳定；3.范围查询简便。

B+树的数据量：树的高度可控的原因

B+树每个节点实际上是一页数据，每页大小大约是16KB，每个节点都是16KB，假如索引使用的是bigint8个字节，那么一个节点可以放置1170个索引，16kb/14

实际上每次存取数据也是存取一整页的数据

一个叶子节点，索引+data算作1kb，那么一个叶子节点可以放置16个数据

叶子节点可以存放2千多万的索引
一个B+树3层高度就可以存放2千多万数据

B+树查找

B+树查找：实际上就是折半查找

稀疏索引（非聚簇索引）与聚簇索引

从文件结构上区分聚簇索引与稀疏索引，立即InnoDB与MyISAM区别

数据库底层目录结构：
一个库对应MySQL安装目录/data/对应表名字的文件夹

非聚簇索引包括三个文件：
frm：表结构
MYD：表数据
MYI：表索引

非聚集索引：
参考
先去MYI找到索引，然后找到叶子，找到地址，去MYD文件中迅速定位到数据，拿到所有数据。MyISAM组织索引的形式用的是非聚簇的方式，即分为上述三个文件。

几乎都在用InnoDB：聚集索引
参考
一般来说，聚簇索引是根据主键生成的。除了聚簇索引之外的索引都可以称之为辅助索引/稀疏索引/非聚簇索引。与聚簇索引的区别在于辅助索引的叶子节点中存放的是主键的键值。一张表可以存在多个辅助索引，但是只能有一个聚簇索引，通过辅助索引来查找对应的完整行记录的话，需要进行两步，第一步通过辅助索引来确定对应的主键，第二步通过相应的主键值在聚簇索引中查询到对应的行记录，也就是进行两次B+树搜索。相反通过辅助索引仅仅来查询主键的话，遍历一次辅助索引就可以确定主键了，也就是所谓的索引覆盖，不用回表（查询聚簇索引）。
InnoDB用的是聚簇索引的形式管理索引与数据文件。

目录结构：

ibd文件：数据 + 索引

两种驱动用的虽然都是B+树，但是InnoDB叶子节点存储的是完整的数据，而MyISAM存储的是地址

InnoDB索引和MyISAM索引的区别
一是主索引的区别：InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

聚簇索引

聚簇索引介绍
聚集索引/聚簇索引：叶子节点包含完整的数据记录。一张表只有一个聚簇索引，一般用主键生成。

为什么必须有主键且建议整形的自增主键（高频）

简化版回答：
核心要点：避免mysql替我们做许多不必要的操作；自增主键足够unique且比较时很高效；便于B+树的维护：迅速寻址，防止频繁的分裂页；不占空间。

1. 主键天然是区分度极高的索引，因为要生成ibd文件，所以必须至少有一个聚簇索引，否则无法组织B+树
2. 整型相对于字符串效率更高，占用空间小、自增是为了迅速插入，防止过程中查找到位置然后插入
3. 在无主键的情况下会去每一列搜索，查找到某一列满足作为索引的条件时就自动设置为索引，如果都没有就建立一个隐藏列，来组织索引。所以不要用MYSQL做这个事情，效率低而且浪费。

详细版回答：
MySQL的聚簇索引建立操作过程：
1、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。这个过程会发生很多操作并且低效，后续也很难直接使用row_id这个隐藏列。

数据页的考虑
2、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，定位寻址会非常快，当一页写满，就会自动开辟一个新的页。对于B+树的维护非常友好。
3、如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

相似问题：为什么使用自增主键，不适用UUID以及雪花算法形成的ID？直接看链接第二部分，还是从数据页的角度考虑。

UUID以及雪花算法的无序性，导致生成索引B+树时自增主键带来的所有优势都没有了。
自增的主键的值是顺序的，所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16，会留出1/16的空间留作以后的修改)：
①下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费
②新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
③减少了页分裂和碎片的产生

uuid相对顺序的自增id来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以innodb无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。
这个过程需要做很多额外的操作
①写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO
②因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上
③由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片
在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后，有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。

效率问题：1.unique但是长度太长，导致一个叶子节点存储量下降，树高度不可避免的上升。2.定位寻址缓慢，导致生成B+树时维护时间较长。3.比较的时候长度太长影响效率，一定长度下又会有重复的问题。

结论：使用innodb应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行

使用自增的id的坏处：
①主键可猜测，别人一旦爬取你的数据库，就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况
②对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争
③Auto_Increment锁机制会造成自增锁的抢夺，有一定的性能损失

4、自增ID无法处理分库分表的情境。
优化方案：可以使用redis缓存来分配自增ID从而维护ID唯一。

二级索引/非主键索引/辅助索引

二级索引树（非主键索引）：存储的不是全部的数据而是主键索引

联合索引：最左前缀原则。

B+Tree的构造时按照联合索引出现的顺序进行排序的，即先按照第一个参数排序，之后按照第二个参数排序，并向下组织。无法跳过前方的索引执行。
违背了索引是排好序的数据结构这一定义

索引部分参考链接：
https://juejin.cn/post/6844903924307247111

MySQL除了BTree进行存储之外还可以选择使用Hash进行存储，问题是不支持范围查找，无法处理> 2 < 3的场景

为什么非主键索引结构叶子结点存储的是主键值？

1. 保持一致性同时不想修改大量的索引文件：
   当数据库表进行DML操作时，同一行记录的页地址会发生改变，因非主键索引保存的只是主键的值，无需进行其他字段的更改。

2. 节省存储空间：
   Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。

唯一索引

只是unique而已，也就是说作为这类索引的列没有重复值，在insert值的时候会去做检测看是否违反唯一约定。除此之外没有其他特殊的。
可以是聚簇也可以是非聚簇。

索引实例

https://blog.csdn.net/li1325169021/article/details/114682931

最左原则是指：mysql会一直向右匹配直到遇到范围查询(>、<、between、like“%开头%”的，注意like"XX%结尾"不会断档)就停止匹配。索引用的时候可以是任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。