Mysql索引篇

最近在很多网站上看了索引的相关知识，各种说法的都有，但是又不是很全，有的概念很模糊，下面是由小编整理的Mysql索引知识点。

一.首先我们说下什么是索引，为什么要用索引

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

二. 索引类型分为两类：

1.hash索引

2.bTree

三.下面我们简单分析一下bTree索引。

1. 哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

2. 说到bTree，就不得不提二叉树，二叉树分为很多，例：二叉查找树，平衡二叉树等。当然还有重点红黑树。
1） 二叉查找树的特点是： 父节点左子树所有节点的值小于父节点的值。右子树所有节点的值大于父节点的值。 下面以一张图为例来体现二叉查找树。

有一个需求，查找张三，如果不使用二叉查找树那么我们需要查找7次，使用二叉查找树我们只需要查找4次就可以找到我们想要的值。
根据上面说的使用二叉查找树的确可以减少查询次数，但是大家有没有想过，如果数据库的数据是 1，2，3，4，5，6，7这样依次递增的数据呢，继续使用二叉查找树就会变成一个链表了。那这样如果我们想要查找12那么需要查找7次，扫描表也是需要7次。这样跟没有建立索引没有区别，这也是弊端之一。下图为例说明。

2） 平衡二叉树：又被称为AVL树，它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树，AVL树是最早发明的自平衡二叉查找树。在AVL树中，任何节点的两个子树的高度最大差别只能为1，所以它又被称为高度平衡树。查询、增加和删除在平均和最坏情况下都是O(log n)。增加和删除会需要通过一次或多次树旋转来重新平衡这个树。
我们引入二叉树的目的是为了提高二叉树的搜索的效率,从而减少树的平均搜索长度，为此,就必须在每颗二叉树插入一个结点时调整树的结构,让二叉树搜索能够保持平衡,从而可能降低树的高度,减少的平均树的搜索长度。
平衡二叉树特点如下：
1.它的左子树和右子树都是AVL树
2.左子树和右子树的高度差不能超过1
例图：
3） 红黑树：可以理解为红黑树是凌驾于平衡二叉树之上的一棵树，红黑树不会追求“完全平衡 ”，它只会求部分达到平衡要求，降低了对旋转的要求，从而提高性能。此外，由于它的设计，所有不平衡都能够在三次旋转之内解决。在红黑树中，它的算法时间复杂度与AVL相同，并且统计性能会逼AVL树更高。所以红黑树相对于平衡二叉树来说，不是严格意义上的平衡二叉树，红黑树插入和删除效率更高一些，查询的效率比平衡二叉树来说相对低一些，但是二者查询效率差值做对比，基本可以忽略不计。红黑树特点如下：
1. 节点是红色或黑色。
2. 根节点是黑色。
3. 每个红色节点的两个子节点都是黑色。（红色节点的子节点必须是黑色节点）
4. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。
故红黑树是黑色平衡的树，左子树与右子树高度差不会超过2。红节点的父节点、子节点只能是黑节点。
例图：

4） BTree（B树）：当然上面说到了红黑树，性能非常高。以上图为例，树的高度最高才为4，共9条数据，但是对于Mysql数据库，动则几百万条数据，几千万条数据，那树的高度就不可估量了，比如说上百万条数据需要经过30-50次磁盘IO才能查询到数据，甚至更多的次数，显然不能满足Mysql索引高效的查询效率。那如果我们控制树的高度呢，那这样就会极大减少了请求磁盘IO的请求次数，如果高度控制在4，那只需要经过4次磁盘IO就可以查询到数据。
但是怎么样控制树的高度呢，红黑树是每个节点只存储一个元素，如果每个节点存多个元素呢，这样就可以解决高度问题了，肯定有同学有疑问，把所有的元素都放到一个节点上，那高度值就是1了，不是更快吗？这样想肯定是错的，Mysql每一次跟磁盘IO打交道是有大小限制的，Mysql限制每一个节点的大小是16K。 想查看自己Mysql限制节点大小的同学可以执行下面的sql。
show global status like ‘Innodb_page_size’
下面以图为例体现BTree
BTree特点如下：
1.所有索引元素不重复
2.节点的数据索引从左到右依次递增
3.叶节点具有相同的深度，叶节点的指针为空
4.叶子节点和非叶子结点都存储索引和数据

5） B+树：上面说到了BTree控制了树的高度的问题，可以满足Mysql对于索引的需求，但是最终Mysq索引实现不是BTree而是B+树，Mysql对B树做了一点点改造，得到了B+树，也可以理解为B+树是B树的升级版。
下面以图为例说明：

从这张图可以看到，我们的非叶子节点只存储了索引并没有存储data，而且叶子节点间用指针相连。B树的叶子节点和非叶子节点都存储了索引和数据，而且叶子结点的指针为空，B+树把数据放在了叶子节点上，这样非叶子节点就可以存放更多的索引，每次从磁盘IO也能获取更多的索引。
B+树特点如下：
1.非叶子节点不存储data，只存储索引(冗余)和下层指针，可以放更多的索引
2.叶子节点包含所有索引字段，和数据
3.叶子节点用双指针连接，提高区间访问的性能

四.索引分类

1.按照索引的存储关联分类：分为两大类
1.）聚集索义（聚簇索引）：叶节点包含了完整的数据记录，不需要回表。
2.）非聚集索引：需要回表，二次查树，影响性能。

1.1） 大家都知道Mysql常用的存储引擎有两种MyISAM和InnoDB，但是大家实际了解过两种存储引擎底层的数据存储结构吗？
下面以图为例为大家说明：
其中test.myisam表是MyISAM存储引擎，actor表是InnoDB存储引擎，可以看到MyISAM存储引擎有三个文件，分别是frm、MYD、MYI，很容易理解frm-frame的简称，存的是表的结构，MYD-MYData存的是数据，MYI-MYIndex存的是索引，索引和数据是分开存储的，再看InnoDB只有frm、IBD，其中frm一样也是存的表的结构，IBD文件存的是索引和数据，这点InnoDB和MyISAM不一样。
下面以图为例说明MyISAM存储引擎主键索引是需要回表操作（非聚集索引）
其中15存的是主键索引，0x07存的是15所在行记录的磁盘文件地址指针，比如我们想找到15的数据，那首先应该先通过主键索引树，找到15所对应的指针，然后找到了这个指针再去MyD文件中找具体的数据，需要进行二次查找，这个过程称为回表操作。
2.1） 下面以图为例说明InnoDB存储引擎主键索引不需要进行回表操作。（聚集索引）
InnoDB存储引擎子节点首先15那一行存放的是索引，15下面的那一列存放的是索引所在行的其他所有字段，如果我们想要查15的数据，直接就可以找到，不需要在经过二次查树。

2. 按照功能分类：主要分为五大类
2.1 主键索引：InnoDB主键索引不需要回表操作
2.2 普通索引（二级索引）：InnoDB普通索引需要回表操作，对于二级索引，会默认和主键做联合索引。
2.3 唯一索引
2.4 全文索引
2.5 联合索引：需要满足最左前缀原则

2.6 在2.2中提到了普通索引需要回表操作，那有没有不需要回表的普通索引呢，答案是有的，在某个查询里面，索引已经覆盖了我们的查询需求，我们称为覆盖索引。这时是不需要回表操作的。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。