索引的原理

索引的定义

数据库索引：是数据库管理系统中一个排序的数据结构，以协助快速查询、跟新数据库中的数据。
类似于字典的目录，在查找文字之前先通过目录来查找，能更快的找到目标，同理，数据库中数据通过文件的形式保存在磁盘，每一行数据有自己的磁盘地址，如何能在大量的数据中寻找查询的数据，这时索引能大大地提高效率。

索引的类型

• 普通索引：最普通的索引，没有任何限制
• 唯一索引：要求键值不能重复。主键是特殊的唯一索引，键值不能为null
• 全文索引：针对较大的文本类型创建

索引存储模型的推演

二叉查找树

特点：

1. 左子树小于根节点
2. 右子数大于根节点
3. 投影到平面是一个有序的线性表

缺点：
二叉树的查找耗时和树的深度有关，最坏情况能退化成O(n);既左右子树深度差很大，成为斜树，最极端情况就是退化成链表

平衡二叉树（AVL）

特点：

1. 基于二叉树的优化，继承二叉树所有特点
2. 通过左旋和右旋操作，保证左右子树的深度差不大于1
3. 在枝节点，叶子节点存放键值、数据磁盘地址、左右节点引用
   

在使用AVL作为索引，每个节点中的数据必须包含：键值、数据磁盘地址（数据存放在磁盘中）、左右子树节点的引用，而每访问一个节点就要和磁盘进行一次交互，innodb操作磁盘最小的单位是页（16k），然而一个节点就存放一个键值，数据磁盘地址（数据存放在磁盘中），左右子树节点的引用，显然是远远小于16k的，造成了空间极大的浪费
缺点：
1、单节点存放数据太少
2、查找耗时和树的深度有关，尽管优化了极端情况的出现，但是随着数据的增大，树的深度增加，查询性能急剧下降，因为每访问一个节点就要和磁盘进行交互

B树（多路平衡查找树）

如何优化AVL呢？
提高单节点空间利用率；减少树的深度；因此祭出了B树
特点：

1. 关键字（类似跳表的索引层），N
2. 节点拥有的子树数量成为度=N+1
3. 在枝节点，叶子节点存放键值、数据磁盘地址、子节点引用
4. 与AVL的左旋右旋不同，B树的节点操作时分裂、合并
   
   索引树节点的分裂、合并其实就是innodb数据页的分裂、合并；如果索引键值无序，存储时会造成频繁的page分裂、合并，从而导致形成大量的磁盘碎片；

B+树

优化后的B树；
特点：

1. 关键字数和度相等
2. 根节点和枝节点不存储数据，叶子节点存储数据
3. 每个叶子节点增加了指向相邻节点的指针（双向链表）
   

优化后的优点：

• 继承B树所有的优点：单节点存放更多关键字；路数更多；
• 扫库，扫表能力更强（只需要遍历子节点）；
• B+树读写能力更强，因为根节点、枝节点不存放数据，因此单节点可以存放更多的关键字，更多路数；
• 排序能力更强，叶子节点是有序的双向链表；
• 效率更加稳定，所有数据在子节点，所以所有的操作必须执行相同次数的IO；

举个例子：
假设一条记录的索引+指针大小为16byte,一个叶子节点（一页）可以存储10条记录，非叶子节点可以存储多少指针？

一个节点可以存放1000个（索引+指针），
当深度为2时，非叶子节点可以存储10001000=1000000个指针，每个叶子节点10条记录，所以总共可以索引10001000*10 = 10000000条数据，就可以达到千万级别的数据量；

红黑树为什么不适合

红黑树约束：

1. 节点分为红色、黑色
2. 根节点为黑色
3. 叶子节点都是黑色的null节点
4. 红色的子节点必须是黑节点
5. 从任一节点出发，到其每个叶子节点的路径上经过的黑色节点数一样

综上，特点就是根节点到最远叶子节点的距离不大于到最近叶子节点距离的2倍；
因此不用红黑树做索引，是因为不够平衡，每个节点只有两路；
但是红黑树适合在内存中使用，hashMap，TreeMap等

MYSQL中B+树的落地

聚集索引（聚簇索引）

一个innodb表可能有多个索引，但是数据肯定是只有一份，数据在哪个索引上呢？
答案就是在聚集索引上。聚集索引就是索引键值的逻辑顺序与表数据行的物理存储顺序一致；
通常主键索引就是聚集索引；
为什么只在聚集索引上存储完整的数据，而不是所有的索引都存储完整的数据呢？
因为这样会带来额外的存储空间浪费和计算消耗；

如果一张表没有主键索引，那完整数据会存储在哪里呢？

1. 有主键则存储在主键索引
2. 第一条不成立，选择第一不包含null值的唯一索引作为主键
3. 第一二条都不成立，innodb会选择内置6字节的ROWID作为隐藏的主键索引，他会随着行的写入而递增；

二级索引（辅助索引）

聚簇索引之外的索引成为二级索引；
二级索引存储二级索引的键值，叶子节点存储主键的值

因此根据二级索引查询数据时：

1. 只查询二级索引值或主键值，那就在二级索引中就可以获取
2. 如果查询包含索引值以外的，那就需要通过二级索引获取到的主键值再去聚簇索引中获取完整数据，这个从二级索引返回到聚簇索引查询的过程称为回表

索引下推

索引的使用原则

列的散列度

散列度 = 列的全部不同值/所有数据行
散列度高的字段适合简历索引
例如：性别就不适合建立索引，否则性能更差！！！

联合索引最左匹配

什么时候用到索引

用不用到索引有查询优化器决定，查询优化器会在众多的执行计划中筛选出一条cost开销最小的来执行。

覆盖索引

覆盖索引可以避免回表；

索引的创建

联合索引的创建

避免索引无效

1. 索引列上使用函数、表达式、计算
2. 字符串不加引号，出现隐士转换
3. like条件中前%匹配
4. 负向查询 NOT like 但是！=和not in在某些情况可以
   eg：

   select * from user where id  != 0;
   

   索引是有序，只要从0开始往后顺序读就ok；用不用索引，是查询优化器决定的，而查询优化器的判断规则是基于cost开销，而非规则。哪个开销小就是用哪个！！！