数据库索引相关知识点

参考：
Java技术栈公众号
Cyc

什么是索引？

索引是存储表中一个特定列的值数据结构。B+是大多数 MySQL 存储引擎的默认索引类型。因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键,联合索引。
把数据库索引看作是书的索引

数据库索引是怎样提升性能的？

避免全表扫描，通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。
如B+Tree中，一个节点中的 key 从左到右非递减排列其中的数据是有序的,已经排序意味着查询会快很多。
索引存储了表中相应行的指针可以获取当前行其他列的数据。

数据库索引里究竟存的是什么？

存储了这一列的所有值和指向表中的相应行的指针（指针是指一块内存区域），并不存储其他列的值。
B+树索引:

• B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。B+ Tree 是基于 B Tree 和叶子节点顺序访问指针(指向下一个叶子节点的指针)进行实现，它具有 B-Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。
• 叶子节点有顺序访问指针。在每一个Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode的指针信息（增加了顺序访问指针），这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。
• 每个节点中的关键字是有序的。在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是
  keyi 和 keyi+1，且不为 null，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

数据库怎么知道什么时候用了索引？

比如当select from where时，数据库会检查在查询的列上是否有索引.如果确实创建了索引，会接着检查使用这个索引做查询是否合理。

如何使用索引进行查找的？

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。
B+树的每个节点都是存放在磁盘块上的。每个非叶子节点包含着几个关键字以及指向下一层的指针。

从根节点开始，将所在磁盘块由磁盘载入到内存中，发生一次IO，用二分查找锁定区间，确定指向下一层节点的指针。然后再将下一层节点的磁盘块载入到内存，发生第二次IO,再二分查找…逐层搜索直到找到叶子节点。

(真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。)

如何使用SQL创建索引？

CREATE INDEX name_index
ON Employee (Employee_Name)
联合索引：
CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)
查看为表设置的索引:
SHOW INDEX FROM

哈希表索引怎么工作的？

哈希索引。在寻找值时哈希表效率极高。所以使用哈希索引，对于比较字符串是否相等的查询能够极快的检索出值，将列的值作为key,指向该表中相应行的指针作为value
哈希表是无序的。对于查询一个范围的数据不可行。只适合查询键值对。不像B-那么灵活

索引的优点：

1.通过创建唯一索引，可以保证数据库表中每一行的唯一性
2.大大加快数据的检索速度
3.用在外键上，可以加速表和表之间的连接，可别是在实现数据的参考完整性方面
4.使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间（为什么分组时用索引减少时间）
5.通过使用索引，在查询的过程中，使用优化隐藏器，提高系统性能

索引的缺点：

索引会占用空间，表越大，索引占用的空间越大
性能损失：值更新操作时，如增加、删除、跟新行数据的时候，在索引中也会有相同的操作。建立在某列或多列索引需要保存该列最新的数据。
创建和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占物理空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，如果建立聚集索引，那么需要的空间会更大
当对表中的数据进行增加、删除、修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

使用索引的技巧：

应该使用索引：
1.在经常需要搜索的列上（该列在查询过程中使用的非常频繁）
2.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
3.在经常需要排序的列上创建索引，因为索引已经排序，加快排序查询时间
4.在经常使用在where子句中的列上创建索引，加快条件的判断速度
5.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
不该创建索引：
1.查询中很少使用或者参考的列（既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。）
2.只有很少数据值的列也不应该增加索引。由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3.对于那些定义为text，image,bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少
4.当修改性能远远大于检索性能时，不能创建索引。修改性能和检索性能是互相矛盾的，当增加索引，会提高检索性能，但会降低修改性能，当减少索引时，会提高修改性能，降低检索性能。
什么情况下设置了索引但无法使用 ：
以“%”开头的LIKE语句，模糊匹配
OR语句前后没有同时使用索引
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

主键和唯一索引都要求值唯一，但是它们还是有区别的：

①.主键是一种约束，唯一索引是一种索引；
②.一张表只能有一个主键，但可以创建多个唯一索引；
③.主键创建后一定包含一个唯一索引，唯一索引不一定是主键；
④.主键不能为null，唯一索引可以为null；
⑤.主键可以做为外键，唯一索引不行；

聚集索引和非聚集索引的区别：

聚集索引(Innodb)：主索引（主键）的叶子节点data域记录着完整的数据记录。一个表只能有一个聚集索引，因为无法把数据行存放在两个不同的地方。索引文件就是数据文件。
非聚集索引(myisam)：索引文件存储着指向数据记录的地址。索引文件不是数据文件。

联合索引，最左前缀匹配原则：

首先根据联合索引最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中，后面第二个字段进行排序，依次类推。
第一个字段是绝对有序的，从第二个字段开始是无序的，这就解释了为什么直接使用第二个字段进行条件判断用不到索引了（从第二个字段开始，无序，无法走B+Tree索引）
这也就是联合索引中强调最左前缀匹配原则的原因。
特性：
1.对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
2.= 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。
3.如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

为什么推荐使用自增ID作为主键：

B+树每个节点的关键字是有序的且叶子节点还存储着顺序访问指针。因为B+树的有序性，我们希望保证每次插入时B+树索引都是从右边扩展，避免频繁的合并和分裂，如果使用字符串主键和随即逐渐，会使得数据随即插入，效率低下。