MySQL数据库-索引

1、索引是什么

索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引本身也很大，不可能全部存储在内存中，因此索引往往是储存在磁盘上的文件中

索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引，没有特别的说明，默认是使用B+树结构组成（多路搜索树，并不一定是二叉树）的索引。

2、索引的有点和劣势

优势：

• 可以调高数据检索的效率，降低数据库的IO成本，类似于书的目录
• 通过索引列进行排序，降低数据排序的成本，降低了CPU的消耗
• 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，知识组合索引的排序更加复杂
• 如果按照索引列进行排序，对应order by语句来说，效率就会提高很多

劣势：

• 索引会占据磁盘空间
• 索引虽然会调高查询效率，但是会降低更新表的效率。因为每次对表数据进行增删改操作，mysql不仅要保存数据，还要保存或更新索引文件

3、索引常见类型

3.1、主键索引

索引列中的值必须是唯一的，不允许有空值

3.2、普通索引

mysql中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值

3.3、唯一索引

索引列中的值必须是唯一的，但是允许有空值

3.4、全文索引

只能在文本类型CHAR、VARCHAR、TEXT类字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引

3.5、空间索引

mysql在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。mysql在空间索引这方面遵循OpenGIS几何数据模型规则

3.6、前缀索引

在文本类型CAHR、VARCHAR、TEXT类型上创建索引时，可以指定索引列的长度，但是数值类型不能指定

3.7、其他

单列索引和组合索引

组合索引的使用，需要尊徐最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引代替多个单列索引使用

4、索引的数据结构

4.1、Hash表

Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式进行存储数据。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找还是只能通过全表扫描的方式。

4.2、二叉查找树

二叉树

二叉树特点：每个节点最多有2个分叉，左子树和柚子树数据顺序左小右大

这个特点爆照了每次查找都可以折半而减少IO次数，但是二叉树很难确定一个根节点的值，因为很容易在这个出现以下这种极端情况，导致IO次数并没有折半

虽然这种情况不稳定，但是我们在选择设计上就要避免这种情况

4.3、平衡二叉树

平衡二叉树是采用二分法思想，平衡二叉树查找具备了二叉树的特点，最主要的是树的左右两个字数的层级最多相差1。在插入删除数据的时候，通过左旋右旋操作保持二叉树的平衡。不会出现上面那种极端的情况

使用平衡二叉树查找的性能接近于二分查找法，时间复杂度是O(log2n)。

就这个特点上来看，可以达到二叉树的理想的情况了。但是依然会存在一些问题：

1. 时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次IO操作。树的高度对应查询磁盘IO操作的次数，在数据量大的情况下，查询性能依旧很差
2. 平衡二叉树不支持范围查找快速查找，范围查找时需要从根节点多次遍历，查询效率不高

4.4、B树：改造二叉树

MYSQL的数据是存储在磁盘中，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以优化的重点就是要尽量减少磁盘IO操作。访问二叉树的每个节点都会发生一次IO，如果想要减少IO操作，就需要尽量降低树的高度。

假设我们的key为8个字节，每个节点有两个指针，每个指针4个字节，那么一个节点占用的空间为16个字节（8+4*2=16）

因为在mysql中innoDB存储引擎一次IO会读取一页（默认为16K）的数据量，而二叉树一次IO操作有效数据量只有16字节，空间利用率极低。为了最大利用一次IO空间，可以在每个节点多存储一些数据。这样每个节点就可以存储1000个索引（16K/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变成矮胖。构建1百万条数据，树的高度就只需要两层就可以完成（1000*1000=1百万），也就是说利用2次IO操作就可以查询到数据。磁盘IO次数变少了，查询的效率自然就提高了。

这种数据结构我们称为B树，B树是一种多叉平衡查找树，主要特点：

1. B树的节点中存储着多个元素，每个节点内有多个分叉
2. 节点中的元素包含键值和数据，节点的键值也是从小到大排列。每个节点都存储着数据
3. 父节点当中的数值不会出现在子节点中
4. 所有的叶子节点都位于同一层，叶子具有相同的深度，叶子节点之间没有指针

B树查询数据的情况

假如查询ID为10的数据

1. 首先会将磁盘1加载到内存中，然后判断10<15，走P1
2. 然后将磁盘2加载到内存中，然后会判断7<10<12所以会选择走P2
3. 然后将磁盘6加载到内存中，然后找到key值为10的数据，查询终止

相比于平衡二叉树，数据的比较没有减少，但是IO次数明显的减少，加上是在内存中操作，耗时几乎可以忽略不计。B树一般构架两到三层，就可以满足需求，所以使用B树可以很好的提升查询效率

B树其实还可以进行优化

1. B树不支持范围查找，每次查找对应的数据，都需要从头开始寻找，对于范围查询效率有待提高
2. 行数据直接存放在data中，随着列数的增多，data占用空间也会越来越大，导致页的数据存储变少，树的高度就会增加。树的高度增加，意味着IO次数也就会变多

4.5、B+树（对B树的优化）

B+树作为对B树的升级，在B树的基础上，继续进行改造。B+树和B树最大的却别在于非叶子节点是否存储数据的问题

• B树：飞叶子节点和叶子结点都会存储数据
• B+树：只有叶子节点才会存储数据，非叶子节点只存储键值对。叶子节点之间用双向链表连接

由于数据存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的IO跟树的高度有直接关系。

B+树的等值查询

如果我们查询的ID=9

1. 先将磁盘1加载到内存中，9<15，所以会寻址到磁盘2
2. 将磁盘2加载到内存中，9>7,9<12，所以会寻址到磁盘6
3. 将磁盘6加载到内存中，在内存中遍历，找到索引为9的数据，取出data，查询结束。如果储存的是磁盘地址，还需要根据磁盘地址去取出数据（InnoDB中data存储的是行行为，而MyIsam中存储的是磁盘地址）

B+树的范围查询

假如需要查询9到26之间的数据

1. 首先查找索引为9的值，这个和上面的等值查询相同，发送了3次IO操作
2. 开始查找15的时候，会根据叶子节点的链表，遍历查找，需要ID为26的索引
3. 最后将查询到的所有索引数据存储到data结果集中
4. 因为是主键具有唯一性，所以后面不会有小于26的数据，查询结束

由于B+树可以保证等值和范围查找的速度，所以MYSQL的索引就是采用了B+树这种结构

5、MySQL的索引实现

mysql有两种存储引擎，分别是：MyISAM索引和InnoDB索引

5.1、MyISAM索引

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

表索引存储在.MYI文件中，数据文件存储在数据文件.MYD中

因为索引中存储的是数据的地址，索引会根据地址再进行一次IO操作，获取数据

范围检索，会将满足条件索引的地址，一起返回，在去进行IO操作读取数据

5.1.1、辅助索引

在MyISAM中，辅助索引和主键索引的结构是一样的，没有区别。叶子节点的数据存储都是记录的磁盘地址。知识主键索引是唯一的，辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，可能存在多个相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索

5.2、InnoDB索引

5.2.1、主键索引（聚簇索引）

每个InnoDB表都有一个聚簇索引，聚簇索引使用的是B+树，叶子结点存储的是数据的整行记录。聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的规则如下：

1. 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引
2. 如果表没有定义主键，InnoDB会选择一个不为NULL的唯一索引作为聚簇索引
3. 如果以上两个都没有，InnoDB会使用一个6字节长的隐式字段ROWID字段来构建聚簇索引。该字段会在插入行时自动递增

等值查询

5.2.2、辅助索引

除了聚簇索引，其他的所有索引都称为辅助索引。辅助索引中的叶子接单存储的数据是该行的主键值。在检索时，InnoDB使用此主键在聚簇索引中搜索行记录

InnoDB的数据和索引都会存储在一个.ibd文件中。

等值查询

根据在辅助索引树中获取的主键id，到主键索引检索数据的过程称为回表查询。

5.2.3、组合索引

组合索引的查询过程

最左匹配原则：

最左匹配原则和联合索引的索引存储和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排序，但是b和c是无序的，b列只有在a列值相等的情况下小范围递增有序，而c列只能在a,b两列相等的情况下小范围内递增有序。

像上面的查询，B+树先会比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果没有检索到a列，B+树就不知道第一步应该从哪个节点查起。

可以说创建组合索引a,b,c索引，相当于创建了三个索引，（a），（a,b），（a,b,c）三个索引

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

5.2.4、覆盖索引

覆盖索引并不是一个索引结构，覆盖索引是一种常见的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值。相当于获取数据还需要在根据主键索引查询才可以获取数据。但是有这么一种情况，在查询组合索引时，如果只需要abc三个字段，那就意味着，查询组合索引的叶子节点就可以直接返回了，而不需要回表，这种情况就是组合索引。

6、总结

避免回表

如果在一个场景下，select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。

联合索引的使用

联合索引，在创建索引的时候，尽量在多个单列索引撒花姑娘判断是否可以使用联合索引。联合索引的值不仅可以节省空间，还可以更容易的使用到索引覆盖。

联合索引的创建原则，在创建联合所以你的时候应该把频繁使用的列放在前面。

1. 考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
2. 当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。