数据库 索引

数据库 索引

1、概述

1.1 什么是索引
索引（Index）是帮助MySQL高效获取数据的数据结构。

1.2 创建索引的优点

（1）创建唯一性索引，保证数据库表中每一行数据的唯一性。

（2）加快数据的检索速度，这也是创建索引的最主要的原因。

（3）减少磁盘IO（向字典一样可以直接定位）。

（4）通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

（5）加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

1.3 数据库中创建索引的缺点

（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

（2）索引需要占用物理空间，特别是聚集索引，需要较大的空间。

（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度

2、索引的种类

索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引。这篇文章会总结SQL Server以及MySQL的InnoDB和MyISAM两种SQL的索引。

SQL Sever索引类型有：唯一索引，主键索引，聚集索引，非聚集索引。

MySQL 索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引。

（1）普通索引
使用字段关键字建立的索引，主要是提高查询速度，单列允许重复。

创建：
create index index_name on table_name(index(length));
如：
create index myindex on User (name(10));

（2）主键索引
数据记录里面不能有 null,数据内容不能重复，在一张表里面只能有一个主键索引。

（3）组合索引
多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

（4）唯一索引
字段数据是唯一的，数据内容里面能否为 null,在一张表里面，是可
以添加多个唯一索引

创建：
create unqiue index index_name on table_name(index(length));
如：
create unqiue index myindex on user(name(10));

（5）全文索引
对文本的内容进行分词，进行搜索。

3、索引的底层实现原理

3.1 索引的基础知识

首先Mysql的基本存储结构是页(记录都存在页里边)：


（1）各个数据页可以组成一个双向链表
（2）而每个数据页中的记录又可以组成一个单向链表
每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

很明显，在数据量很大的情况下这样查找会很慢

3.1 索引提高检索速度

索引做了些什么可以让我们查询加快速度呢？

其实就是将无序的数据变成有序(相对)：

例如要找8的记录：

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

参考资料：
MYsql索引

3.3 哈希索引

除了B+树之外，还有一种常见的是哈希索引。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。

哈希索引有好几个局限(根据他本质的原理可得)：

（1）哈希索引也没办法利用索引完成排序
（2）不支持最左匹配原则
（3）在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
（4）不支持范围查询

注意：
主流的数据库还是使用B+树索引比较多，对于哈希索引，InnoDB是自适应哈希索引的（hash索引的创建由InnoDB存储引擎引擎自动优化创建，我们干预不了）！

4、聚集索引与非聚集索引

4.1 聚集索引

（1）Innodb存储引擎表是索引组织表，表中数据按照主键顺序存放。其聚集索引就是按照每张表的主键顺序构造一颗B+树，其叶子结点中存放的就是整张表的行记录数据，这些叶子节点成为数据页。

（2）聚集索引的存储物理上连续的叶子结点间按照主键顺序排序，通过双向链表连接。多数情况下，查询优化器倾向于采用聚集索引，因为聚集索引能在叶子结点直接找到数据，并且因为定义了数据的逻辑顺序，能特别快的访问针对范围值的查询。

（3）聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。由于表里的数据只能按照一颗B+树排序，因此一张表只能有一个聚簇索引。
（4）由于主键使用了聚簇索引，如果主键是自增id，那么对应的数据也会相邻地存放在磁盘上，写入性能较高。如果是uuid等字符串形式，频繁的插入会使innodb频繁地移动磁盘块，写入性能就比较低了。
（5）在Innodb中，聚簇索引默认就是主键索引。如果没有主键，则按照下列规则来建聚簇索引:
1、没有主键时，会用一个非空并且唯一的索引列做为主键，成为此表的聚簇索引;
2、如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

4.2 非聚集索引

非聚集索引，其叶子节点不包含行记录的全部数据，叶子结点除了包含键值以外，每个叶子结点中的索引行还包含一个书签，该书签就是相应行的聚集索引键。

当通过非聚集索引来寻找数据时，Innodb存储引擎会通过辅助索引叶子节点获得只想主键索引的主键，既然后再通过主键索引找到完整的行记录。

例如在一棵高度为3的辅助索引树中查找数据，那需要对这颗辅助索引树进行2次IO找到指定主键，如果聚集索引树的高度同样为2，那么还需要对聚集索引树进行2次查找，最终找到一个完整的行数据所在的页，因此一共需要4次IO访问来得到最终的数据页。

创建的索引，如联合索引、唯一索引、普通索引等，都属于非聚簇索引。

聚集索引与非聚集索引的区别：
（1）聚集索引在叶子节点存储的是表中的数据
（2）非聚集索引在叶子节点存储的是主键和索引列
（3）使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

4.3 覆盖索引

非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。
创建多个单列(非聚集)索引的时候，会生成多个索引树(所以过多创建索引会占用磁盘空间)

即从非聚集索引中就可以得到查询所需要的所有字段值，而不需要查询聚集索引中的记录。覆盖索引的好处是非聚集索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

5、索引的最左分配原则

顾名思义就是最左的索引优先查找。
例如:

当b+树的数据项是复合的数据结构，比如一张表创建了组合索引(id,name,sex)，b+数是按照从左到右的顺序来建立搜索树的，
（1）比如当(10，李四，男)这样的数据来检索的时候，b+树会优先比较id来确定下一步的所搜方向，如果id相同再依次比较name和sex，最后得到检索的数据；
（2）但是若(李四,女)这样的没有id的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候id就是第一个比较因子，必须要先根据id来搜索才能知道下一步去哪里查询。
（3）比如当(10,女)这样的数据来检索时，b+树可以用id来指定搜索方向，但下一个字段name的缺失，所以只能把id等于10的数据都找到，然后再匹配性别是女的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

6、总结

1、hash 与 b+ 索引的区别

（1）Hash索引在不存在hash碰撞的情况下，之需一次读取，查询复杂度为O（1），比B+树快。

（2）但是Hash索引是无序的，所以只适用于等值查询，而不能用于范围查询，自然也不具备排序性。根据hash索引查询出来的数据，还有再次进行排序

（3）B+树索引的复杂度等于树的高度，一般为3-5次IO。但是B+树叶子节点上的数据是排过序的，因此可以作用于范围查找，而且查询的数据是排过序的，无需再次排序。对于像“SELECT xxx FROM TABLE WHERE xxx LIKE ‘aaa%’”这样涉及到模糊匹配的查询，本质上也是范围查询。

（4）数据库中的多列索引中，只能用B+树索引。数据在B+树的哪个结点上，只取决于最左边的列上的key，在结点中在一次按照第二列、第三列排序。所以B+树索引有最左原则的特性。

2、聚集索引与非聚集索引的区别

（1）聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
（2）聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
（3）聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序
（4）非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序.
（5）索引是通过b+树的数据结构来描述的，我们可以这么理解
聚簇索引：索引的叶节点就是数据节点。
而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

3、使用索引的优缺点

优点：
（1）提高数据检索的效率，降低数据库的IO成本
（2）通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点：
（1）索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
（2）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
（3）索引只是提高效率的一个因素，如果你的MYSQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

4、适合建索引的情况
（1）主键自动建立唯一索引
（2）频繁作为查询条件的字段应该创建索引
（3）查询中与其它表关联的字段，外键关系建立索引
（4）where条件里用不到的字段不创建索引
（5）单键/组合索引的选择问题，(在高并发下倾向创建组合索引)
（6）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
（7）查询中统计或者分组字段

5、不适合建索引的情况
（1）表记录太少
（2）经常增删改的表（提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，加重了IO负担）
（3）数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。（注意如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）

6、什么是回表，什么是索引下推

（1）回表就是在使用非聚集索引时，先根据组合索引得到主键，然后再由主键进行一个回表操作，得到真正的行记录，这个过程就是回表。当然，根据主键查询是不存在回表查询操作的，或者如果用到了覆盖索引，也不需要回表查询操作。

（2）索引下推就是在利用组合索引进行查询时，必须是遵循最佳左匹配原则，但当部分索引失效时，5.6进行优化后，会继续对失效的索引进行条件的筛选，减少回表的次数，提高数据库的性能。

7、B树，B-树，B+树
（1） B树：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于

走右结点；

（2）B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键

字范围的子结点；

所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；

（3） B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点

中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

（4）B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率

参考：
https://juejin.im/post/5b55b842f265da0f9e589e79#heading-8
https://www.cnblogs.com/aligege/p/11593587.html