懂底层，才能真正搞定面试官
我们在开发，或者面试过程中，经常被问到这些问题：
1：为什么要用索引，索引为什么失效了？那些字段适合建索引
2：遇到一条慢查询sql我怎么去分析？优化
3：索引的底层原理和存储结构是什么样的？是怎么支持千万级别表的快速查询的
4：innodb的表必须要有主键，而且推荐整型自增的主键，为什么我使用UUID被鄙视
5：同样的sql，为什么有时候走索引，有时候，又不走索引
1：索引的底层原理和存储结构
索引定义的理解
索引是帮助mysql高效获取数据的排好序的数据结构，首先阐述了索引的两个核心概念，一是他是一种数据结构，二是他是排好序的
我们知道mysql的数据都是放在磁盘上的，每一行数据都有它的磁盘地址，假设没有索引，mysql会从表的第一行数据进行扫描，扫描每一条数据，相当于和磁盘做了一次IO操作，但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构
索引存储模型推演：二叉-B+tree的演变
怎么实现快速检索？
有序数组？有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，
可能要挪动大量的数据（改变 index），所以只适合存储静态的数据
链表？查询效率也是不稳定的，要看数据在链表的什么位置
1.1.1：二叉树
有没有二分查找的链表？也就是我们所说的二叉查找树诞生
Select * from t where clo2=89；
假设是如上图的二叉树的结构Clo2这一列做索引，也不是不可以的，假设我要查询89这个数，假设我是没有索引，要查询6次才可以查询到想要的结果，有了索引，
经过2次磁盘IO，先加载34这条数据到内存和89进行比对，假设比34大，第二次加载就加载89这条数据到内存，就可以通过key找到索引所在行的指针，0x77，就查询到了这条数据在磁盘的位置
二叉树的特点
二叉查找树既能够实现快速查找，又能够实现快速插入，但是是它的查找耗时是和这棵树的深度相关
右子节点，永远比父节点大，左子节点，永远比父节点小，那么试想下，假设节点的数据特性是递增的关系
www.cs.usfca.edu/~galles/vis… 可以在这个网站上演示数据结构的演变过程
假设我现在是用Clo1这列来做索引，他的数据结构就会是下面这种形式
他就会变成一个类似线性列表的结构，树的高度不可控啊，磁盘的IO次数依然是很高的 因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡，有没有一种平衡的树，除了二叉树，还有红黑树
1.1.2：红黑树
同样是以上面的clo1来做索引，红黑树通过自身的自旋，平衡，似乎比二叉树的效率高了些，也解决了树的高度问题
但是在少数据量的情况下，这种是没有任何问题，但是是一千万条数据，或者更多，树的高度是不是依然不可控啊，哪能不能有一种数据结构
适合一个节点，存储更多的数据的数据结构？这样我树的高度不就降低了
1.1.3 B-tree
就是对红黑树的一个改造，一个大的节点存储更多的小的节点
但是B-树，其实也是有弊端的，我们的索引，其实是分key索引值和data索引元数据的
这个data，假设是在innodb的存储引擎下，他存储的是索引所在行除了主键之外的其他的所有字段
假设我现在一行数据里面，其实他存储的就是上面这些字段
那么换句话来说，这个data他是占内存空间的，mysql对一个节点的，推荐的大小是16kb，那么在这个大节点容量固定的情况下，data元素的大小是不是直接决定了
这个大的节点可以存储多少个小的节点，从而来说影响了树的高度啊
大家可以看下，如上是二叉树的结构，特点：
1：所有元素不重复
2：所有的节点，都存储了data元素
3：节点的数据索引，从左至右依次递增
1.1.4 B+tree
实际上mysql采用的是B+树，他是B树的一个变种
特点：
1：非叶子节点不存储data，只存储索引啊，可以放更多的索引
2：叶子节点包含了所有的索引字段，一张表的数据都在叶子节点上存储
3：他把中间叶子节点的数据，向上抽取做冗余，比如20
4：他的特点，本质来说，还是遵循二叉树的规则，右边子节点>=父节点，左边子节点<父节点，看指针的位置
5：它是根据左闭右开的区间 [ )来检索数据
6)排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
7)效率更加稳定（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）
Select * from t where id=49
先从磁盘加载第一个节点[15,56,77]的数据到内存和49进行比对，发现49在15和56之间，然后加载[15,20,49]
这个子节点的数据到内存，和49进行比较，发现49就在里面，再次加载子节点[49,50]这条数据到内存进行比较，获取到了49这条数据的data和key
Select * from t where id>18
前面查询的过程是一样的，到了叶子节点找到了18这条数据，由于叶子节点之间有双向指针，假设>18.下面的数据是不是跟着指针顺藤摸瓜就行了，这就是范围查询
把每个叶子节点中间节点位置冗余到叶子节点上
怎么支持千万级别的表数据的快速查询的？
存储能力取决叶子节点可以存储的索引个数
以主键索引分析，我们一般用的主键的类型是bigint，他占用的是8个字节，反映在索引上就是key
每个key都有一个向下的指针，是成对出现的，mysql对指针定义的大小是6字节，而每一个大的节点默认是16KB
16x1024/14=1170，第一层用1170个key索引可以存储，每个key指向下一个节点，总共第二层的节点就有1170个大的节点，每个节点又可以存储1170个key，1170x1170=1368900，每个key又指向叶子节点，叶子节点又data数据，假设一个叶子节点key+data是1kb，哪叶子节点可以存储的索引key就是1170x1170x16= 21902400
也就是这叶子节点，可以存储二千多万的个索引元素，查询任意的数据，3次磁盘IO，一般B+tree的高度就是2-4之间，不会再高了
为什么innodb表必须有主键，并且推荐使用整型自增主键？
在innodb下，必须要有主键来维持索引的存储结构，假设没有主键的表，mysql内部会帮忙维护这样一个主键索引树
1、如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。
2、如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引
3、如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增。
uuid的缺点：
1：key太大
2：主键自增，方便树的维护，uuid，sting的ASSIC码的值不连续，随机插入涉及到节点的分裂，平衡的过程
如上就是B+树的形式，Hash索引是什么情况呢？
比如要查询6，先对这个数值做一次hash(6),得到索引所在行的指针，比如0x77
而实际的这个索引指针，0x77是存储在hash表里面的，hash表映射，hash表这种数据结构，效率是非常高的。就算是这种表有一亿的数据
都是走的，hash(key)，我只要知道你这个数据的值，做一次hash，就可以找到这行记录所在行在hash表的指针
索引存储结构分析
1.2.1：MylSam存储引擎索引存储结构
MylSam存储引擎主键索引和普通索引的底层存储结构
特点：叶子节点存储的是索引所在行的指针
辅助索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据
1.2.2：InnoDB存储引擎索引存储结构
在 InnoDB 里面，索引类型有三种，普通索引、唯一索引（主键索引是特殊的唯一
索引）、全文索引
Innodb的主键索引
和myisam主键索引不一样的地方就是，叶子节点，存储的是索引所在行的其他的所有字段，一张表的所有字段都在叶子节点里面存储
聚集索引，聚簇索引，是一个意思，比方说innodb这种主键索引的方式就是聚集索引，聚集索引就是索引和表的数据全部放在一个文件里面去存储，在innodb里面普通索引就是非聚集索引，myisam的主键索引，索引文件在MYI文件，数据在MYD文件上，都是非聚集索引
Innodb的辅助索引
特点：非叶子节点，存储的是所在行的主键，这个是主键索引是不一样的地方
非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数 据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表
Innodb联合索引
本质来说，也是辅助索引树的一种吧，只是key是多个的
联合索引树的索引的存储规则是完全遵循创建索引的顺序去排列的，而在查询的时候，先是比较第一个字段，第一个字段确定了，才知道下一个节点的指针往哪走，如图，在第一个字段如果都是10001的情况下，第二个字段从左到右是有序的
好了，今天就分享那么多，改天分享下，sql的执行计划的分享相关的
