mysql索引

索引的定义：

索引是帮助mysql搞笑获取数据的排好序的数据结构。

索引的数据结构：

• 二叉树
  1.左子树上所有的节点的值均小于或等于其他的根节点的值。
  2.右子树上所有的节点的值均大于或等于其他的根节点的值。
  3.左右子树也分别为二叉排序树。
• 红黑树
  1.节点是红色或者黑色。
  2.根节点是黑色。
  3.每个叶子的节点都是黑色的空节点。
  4.每个红色节点的两个子节点都是黑色的。
  5.从任意节点到其每个叶子的所有路径都包含相同的黑色节点。
• Hash表
• B-Tree
  1.叶节点具有相同的深度，叶节点的指针为空。
  2.所有索引元素不重复。
  3.节点中的数据索引从左到右递增排列。
• B+Tree（B-Tree变种）
  1.非叶子子节点不存储data，只存储索引（冗余），可以放更多的索引
  2.叶子节点包含所有索引字段。
  3.叶子节点用指针连接，提高区间访问性能。
  二叉树：
  

select * from t where t.col2 = 89;

按照没加索引去查询，是会从表格中自上而下去查找，这样没查找一行记录，都会和磁盘有一次io交互。这样是很费性能的。
如果给col2加一个索引，假设底层是二叉树的索引，那么按照二叉树特性，右边的元素大于左边的元素。数据从根查询查找89会发现比34大，那么两次就查找到了。但是二叉树也是有一些缺陷的。
假如查询字段col1

select * from t where t.col1 = 6;

那么二叉树将会是这样：

那么其实它退化成一个链表，和自上而下查询是一样的。所以对于这种递增数据来说，二叉树是有缺陷的。
红黑树：
其实红黑树是对二叉树作出了一次优化，自平衡二叉树。在单边增长的情况下，一边的高度明显大于另一边的高度。他会在内部结构帮助作出平衡。它希望树的一边不要比另一边大出太多。

这样查询的话，需要查找3次。这样会比二叉树性能高一点。但是，当数据量很大的时候，树的高度会非常的高，那么红黑树在大数据情况下，它的高度不可控，还是有点不合适。
B+Tree

每个叶子节点的内存大小是有限制的大约为16k；我们执行以下sql可以看一下

show global status like 'innodb_page_size';

结果是；
那么B+Tree树是如何查找数据的呢，假如我们查找30。他会先在根节点进行查找，将根节点放在内存中，在内存中进行比对。因为我们任意一个节点都是从左到右依次递增的。所以我们会发现30是在14～56这个区间中的。而15～56中间的空白处并不是什么都没有放的，它放置的是下一个根节点指针。当我们在第二层节点中发现，30是在20～49中间的节点上的。依次查找，我们会很容易的查找到数字30。
它的效率为什么这么快呢？是因为，其他情况查找的情况下，每查找一次，都会与磁盘交互多一次io，但是B+Tree他会在查找每一个节点的时候，将该节点放在内存中进行比对，这样的话，它的查找速度是很快的。
Hash
那么hash是怎么来查找数据的呢假如来查找5那么它对经过计算hash（5）来得到一串地址来和Hash表对应表中的地址，这样来查找数据。

照这样来看，感觉hash是比B+Tree快的那么为什么我们不选择Hash查找呢。是因为hash是不能很好的支撑范围查找的。

select * from t where t.col2 > 5;

当我们去查找大于5的数据的时候，Hash是没办法去计算匹配的只能自上而下的去一个个比对。这样效率是没有BTree高的。那为什么B+Tree效率高呢，是因为B+Tree有一个双向指针，叶子节点用双向指针连接，提高区间访问的性能。而B-Tree是没有没有双向指针的，他的查找效率是远远没有B+Tree效率快的。

存储引擎

• MyISAM索引（非聚集）
  索引文件和数据文件是分离的
• InnoDB索引（聚集）
  表数据文件本身就是按B+Tree组织的一个索引文件
  聚集索引-叶节点包含了完整的数据记录。

从表面来看，我们很清楚的就可以知道，聚集索引的查询效率是高于非聚集索引的。
我们要知道，InnoDB表必须有主键，并且推荐使用整形的自增主键。这是为什么呢？因为B+Tree是需要索引来组织的，如果我们不设置主键的话，那么mysql是会默认添加一个虚拟主键的，对于资源本来就紧张的mysql来说。这样是更加耗费资源的。那么为什么要设置整形的主键呢？而不是使用uuid呢？是因为B+Tree树在查找数据的时候，在每个节点需要比对数据。那么整形的数据比对肯定是远远大于字符串的uuid的效率的。那为什么mysql还推荐使用自增主键呢，是因为B+Tree树自左向右依次递增的特性。当我们在插入自增主键的时候，B+Tree只需要向叶子后面依次添加就好，如果不是递增的情况下我们有可能向中间插入，当中间空间满了的情况下，我们是没法向中间插入的，BTree只能进行分裂插入，这样的效率是非常低的。

联合索引

联合索引的数据结构其实也是B+Tree，现在假设我们有三个字段有索引，分别是name，age，position。那么它的数据结构是这样的。
现在有三条sql

select * from employees where name = 'Bill' and age = 31;
select * from employees where age = 31 and position - 'dev';
select * from employees where position = 'manager';

那么以上三条sql哪条成功走索引了呢？显而易见，根据最左前缀原则，只有第一条sql走索引了，但是这是为什么呢？看我们的联合主键的数据结构图，我们第一条数据姓名针对于全表来说是已经排好顺序的，而年龄只是针对与当前节点来进行的排序，所以如果我们在查询的时候，没有遵循最左原则，先查找age的话，那么age是没有针对与全表进行排序，所以索引失效了。