MySQL进阶-索引

1、什么是索引？

在说明索引定义前，引入一个案例：

先假设有一张表,表的数据有10W条数据,其中有一条数据是name=‘zhangsan’,如果要拿这条数据的话需要些的sql是 SELECT * FROM user WHERE name = ‘zhangsan’。

一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在name上建立索引,那么mysql只需要通过找到索引后直接扫描一行数据及为我们找到这条name='zhangsan’的数据,是不是感觉性能提升了好多？！

在关系数据库中，索引是对表中一列或多列的值进行排序的一种存储结构，它包含一个表中某些列的值以及记录对应的地址，并且把这些值存储在一个数据结构中。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容，可以提高数据检索的效率，降低数据库的IO成本。

要注意的是，索引也是表的组成部分，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立索引。

MySQL官方对索引的定义为：
索引（Index）是帮助MySQL高效获取数据的数据结构。

2、索引的分类

mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.

单列索引:一个索引只包含一个列,一个表可以有多个单列索引.

组合索引:一个组合索引包含两个或两个以上的列,

2.1 主键索引

主键索引即主索引，根据主键建立的索引，不允许重复，不允许空值。

注意：
如果表中没有定义主键。InnoDB会选择一个唯一的非空索引替代。
如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

2.2 唯一索引

用来建立索引的列的值必须是唯一的，允许空指。

2.3 普通索引

普通索引是用表中的普通列构建的索引，没有任何限制。

2.4 全文索引

全文索引是用大文本对象的列构建的索引。

5.6之前的版本中，全文索引只能用MyISAM存储引擎
5.6及以后的版本，MyISAM和InnoDB均支持全文索引
在之前的MySQL中，全文索引只对英文有用，目前对中文还不支持
MySQL8的版本中支持了对中文分词的全文索引

为什么对中文不支持呢？因为中文和英文有本质上的区别。英文是单词语句组成，而且都有空格，好分辨，中文是有字组成的句，而且有不同的意思，因此不一样。
当然目前中文分词技术已经非常成熟了了，因此的mysql8中支持了中文的全文索引。

2.5 组合索引

组合索引是用多个列组合构建的索引，这多个列中的值不允许有空值。

组合索引的“最左”原则
就好比于过桥，必须踩着第一块木板才能踏上第二块木板。从左往右。

3、索引的原理与B+Tree

3.1 哈希索引

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能正常情况下，如果不指定索引的类型，那么一般是指B+Tree索引（或者B+Tree索引）。
存储引擎以不同的方式使用B+Tree索引。性能也各有不同，但是InnoDB按照原数据格式进行行存储。

3.2 了解数据结构

其实B+TREE 就是 B+树，而B+树呢就是通过B树演变过来的，所以如果想知道什么是B+树，那么就要先了解一下b树，要了解b树呢就得知道二叉树，这些都是数据结构的内容。

那数据结构又是啥呢？
数据结构是计算机存储、组织数据的方式。数据结构是指相互之间存在一种或多种特定关系的数据元素的集合通常情况下，精心选择的数据结构可以带来更更高的运行或者存储效率。数据结构往往同高效的检索算法和索引技术有关。

3.2.1 顺序查找

顺序查找也就是通常说的暴力查找，就是从第一个元素开始，按索引顺序遍历待查找序列，直到找出给定目标或者查找失败。
缺点：效率低 – 需要遍历整个待查序列列。
没有索引状态使用该方式查找数据，所以通常创建数据都会选择加上索引。

3.2.2 二叉树（二分查找）

二分法查找也称为折半法，是一种在有序数组中查找特定元素的搜索算法。

如何实现二分查找呢？
二叉树就是二分查找的典型数据结构。

二叉树也存在缺点，就是当数据是顺序插入时就会改变树的形态（在非完全二叉树的时候)

3.2.3 AVL树（平衡二叉树）

AVL树是带有平衡条件的二叉查找树，一般是用平衡因子差值判断是否平衡并通过旋转（左旋与右旋）来实现平衡，左右子树树高不超过1，和红黑树相比，AVL树是严格的平衡二叉树，平衡条件必须满足（所有节点的左右子树高度差的绝对值不超过1）。不管我们是执行插入还是删除操作，只要不满足上面的条件，就要通过旋转来保持平衡，而旋转是非常耗时的，由此我们可以知道AVL树适合用于插入与删除次数比较少，但查找多的情况。

3.2.4 红黑树

一种二叉查找树，但在每个节点增加一个存储位表示节点的颜色，可以是红或黑（非红即黑）。通过对任何一条从根到叶子的路径上各个节点着色的方式的限制，红黑树确保没有一条路径会比其它路径长出两倍，因此，红黑树是一种弱平衡二叉树（由于是弱平衡，可以看到，在相同的节点情况下，AVL树的高度低于红黑树），相对于要求严格的AVL树来说，它的旋转次数少，所以对于搜索，插入，删除操作较多的情况下，我们就用红黑树。

红黑树相对于平衡二叉树旋转次数较少，是因为红黑树通过自身的限制从而更容易达到一种弱平衡。

1. 每个节点非红即黑
2. 根节点是黑的;
3. 每个叶节点（叶节点即树尾端NULL指针或NULL节点）都是黑的;
4. 如图所示，如果一个节点是红的，那么它的两儿子都是黑的;
5. 对于任意节点而言，其到叶子点树NULL指针的每条路径都包含相同数目的黑节点;

3.2.5 B-Tree（B树）

当数据存在内存中，红黑树效率是非常高的，但是文件系统和数据库都是存在硬盘上的，如果数据量大的话，不一定能一次性加载到内存。所以一棵树都无法一次性加载进内存，又谈何查找。
因此就出现了专门为磁盘等存储设备而设计的一种平衡多路查找树，也就是B树。
与红黑树相比，在相同的节点的情况下，一颗B/B+树的高度远远小于红黑树的高度（因为一个节点存的多，所以高度自然小，高度小了IO次数自然就少了）

一个M阶的b树具有如下几个特征：
1.定义任意非叶子结点最多只有M个儿子，且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]，向上取整；
4.非叶子结点的关键字个数=儿子数-1；
5.所有叶子结点位于同一层；
6.k个关键字把节点拆成k+1段，分别指向k+1个儿子，同时满足查找树的大小关系。

通过上面的三个案例例就发现了了几个问题
1.查找7和10 所用步数不一样，就意味着时间不同，效率就不均衡，有的可能快，有的可能慢
2.在进行范围查找时，需要反复的返回上一节点，在进入下一节点，这种情况其实是树的遍历，叫做中序遍历，消耗了了时间
3.还有重要的一点，在B树中，由于每一个节点就是一行数据，那么就是一次IO读区的节点更更少。

可以把B树的缺点归结为：
1.查找效率不均衡
2.范围查找需要中序遍历
3.每一个叶子结点上都带有数据

3.2.6 B+Tree（B加树）

在计算机中,所有与空间相关的东西都是按照块(block)进行存取和操作的

每次读取都意味着一次I/O，假设计算机中每个块的大小为4K,行的大小为1k,索引的大小0.06K，如果需要寻址遍历的次数多，就意味着更多的IO

就上面B树的缺点，进而演进出B+树数据结构
1.B+树只有叶子节点存储数据
2.非叶子结点起到了索引的作用
3.所有叶子结点使用链表相连

B+树优势：
1.磁盘读写代价更低
B树的数据和索引都在一个节点上，那么每个块中包含的索引是少量的，如果想要取出比较深层的数据，意为着要读取更多的块，才能得到想要的索引和数据，那么就增加了IO次数
而B+树中每个块能存储的索引是B树的很多倍，内部结点并没有指向关键字具体信息的指针，那么获取比较深层的数据，也只需要读取少量的块就可以，那么就减少了磁盘的IO次数

2.随机IO的次数更少
随机IO是指读写操作时间连续，但访问地址不连续，时间约为10ms
顺序IO是指读取和写入操作基于逻辑块逐个连续访问来自相邻地址的数据，时长约为0.1ms
在相同情况下，B树要进行更多的随机IO，而B+树需要更多的顺序IO，因此B+树效率更快

3.查询速度更稳定
由于B+树非叶子节点不存在数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都相同的，因此所有数据的查询速度都是一样的。

数据库索引采用B+树的主要原因是，B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作(或者说效率太低)。

4、聚簇索引和非聚簇索引

索引分为聚簇索引和非聚簇索引两种。

在索引的分类中，我们可以按照索引的键是否为主键分为“主键索引”和“辅助索引”
使用主键键值建立的索引称为“主索引”，其他的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。（注意：非聚簇索引叶子节点存储的是数据行地址，辅助索引存储的是主键值）

回表：将在非聚簇索引中找到的key值再回到聚簇索引中查找对应数据。

至此，我们介绍的都是InnoDB存储引擎中的索引方案，为了内容的完整性，我们有必要再简单介绍一下MyISAM存储引擎中的 索引方案。我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了了，而MyISAM的索引方案虽然也使用树形结构，但 是却将索引和数据分开存储：

我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的数据都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：也就是把索引信息单独存到一个文件中，这个文件称为索引文件
MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的数据记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！其它非主键索引也是一样的，这种情况我们称为’回行’。所以在MyISAM中所有的索引都是非聚簇索引，也叫二级索引。

MyISAM和InnoDB的区别

数据存储方式：
• InnoDB由两种文件组成，表结构，数据和索引
• MyISAM由三种文件组成，表结构、数据、索引

索引的方式：
• 索引的底层都是基于B+Tree的数据结构建立
• InnoDB中主键索引为聚簇索引，辅助索引是非聚簇索引
• MyISAM中数据和索引存在不同的文件中，因此都是非聚簇索引

事务的支持：
• InnoDB支持事务
• MyISAM不支持事务