索引的原理和实现的方式

建立索引 需要有索引的类型和索引的方法。

索引的类型包括了Normal  Unique Full text

而索引的方法包括了BTREE 和HASH

转载文章：https://www.cnblogs.com/aspwebchh/p/6652855.html  博主写的真好~

 

一、什么是Btree B+ tree （非二叉）

了解一个小知识：RDBMS即关系数据库管理系统(Relational Database Management System)

1.avl树

平衡二叉树，特点任何节点的两个子树的最大高度差为1

如果进行插入或者删除操作，可能会导致avl树失去平衡，需要进行左旋右旋

2.红黑树（自平衡二叉查找树）（需要知道红黑树和TreeMap排序原理）

学习地址：https://www.cnblogs.com/CarpenterLee/p/5503882.html

需要进行细化

 红黑树和avl树类似，都是在进行插入和删除操作时通过特定的操作保持二叉树的平衡，从而获得较高的查找性能。（如果平衡二叉树的话有着较高的性能优势）

特点：

        1.节点是红色或黑色

        2.根节点是黑色

        3.叶子节点（nil,空节点）是黑色

        4.每个红色节点的两个子节点都是黑色
3 B-tree

B通常理解成是Balance的意思，B- tree 就是B树，简称平衡树。

        B树是平衡多路查找树（有多个查找路径，不止2个），是一种平衡的多叉树。因为B树是平衡树，每个节点到叶子节点的高度都是相同的，这样可以保证B树的查询是稳定的。

        使用B tree 可以显著减少定位记录时所经历的中间过程，从而快速定位，加快存取速度。（建立索引快的一个原因）

        与二叉树相比，B-tree利用多个分支（二叉树只有2个分支）节点，减少了获取记录时所经历的节点数，从而达到节省存取时间的目的。（多个分支所经历的节点数少）

特点：

        1.每个节点的关键字增多了。

        数据库充分利用了磁盘块的原理（磁盘数据的存储采用的是块的形式进行存储，每个块的大小一般为4k,每次去取数据的时候，就是取出这个4k的大小，而不是只取出你想要的大小。就是说每次IO的时候，同一磁盘块的数据都是一次性提取出来）。把树的节点关键字增多后，树的层级比原来二叉树的层级少了，这样就可以减少数据查找的次数 ，降低复杂度了。

（磁盘的数据是成块的存放的）

        2.所有的叶子节点都在同一层上（要么就没有叶子节点要么都在同一层中）

4.B+ tree

B- tree的结构中，每个节点不仅包括数据的key值，也包括data值。而每一页的存储空间都是有限的，如果data数据较大的时候，会导致，每一页中存储的key比较少，当存储的数据量比较大时，同样会导致B- tree的查询深度很大，增加磁盘IO次数，进而影响查询效率

 B+ tree中，非叶子节点上只存储key的信息，这样可以加大每一页中存储key的数量，降低B+ tree的高度。

   1.非叶子节点只存储key信息

    2.所有叶子节点之间有一个链指针

    3.B+的非叶子节点只进行数据的索引，不会存实际的关键字记录的指针，所有数据地址必须要到叶子节点才能获取到，所以每次数据查询的次数都一样。

 

二、索引的原理

一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。

这就是所谓的聚集索引，所以一个表只能有一个主键，一个表只能有一个聚集索引

一个表只能有一个主键的~！！至于平时多个主键的原因是因为联合主键！！！（多个字段一起作为一张表的主键）

主键的主键的作用是保证数据的唯一性和完整性，同时通过主键检索表能够增加检索速度。

转自：https://www.cnblogs.com/aspwebchh/p/6652855.html

因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

下面聊聊怎么创建联合主键：

1、GUI中同时选中多列，点击设置为主键。（比如平时用的navicat）

2、sql语句将多列设置为主键：

 一种是在建表时就写出，语句如下：

Create Table 表名 (字段名1 Int Not Null,
                   字段名2 nvarchar(13) Not Null Primary Key (字段名1, 字段名2),
                    字段名3…………
                    字段名N………… )
另一种是在建表后更改，语句如下：

ALTER TABLE 表名 WITH NOCHECK ADD 
CONSTRAINT [PK_表名] PRIMARY KEY  NONCLUSTERED 
(
  [字段名1],
  [字段名2]
)
通过以上两种方式就解决了联合主键的问题。

更多的公司选择id+type作为联合主键（暂时没有想到这样做的优势= =）

clustered index 聚集索引，这类索引是在数据存在一起的。
non-clustered 非聚集索引，这类索引是通过找聚集索引来找数据的。
原文：https://blog.csdn.net/for12/article/details/49300843 

假如一张表有一亿条数据 ，需要查找其中某一条数据，按照常规逻辑， 一条一条的去匹配的话， 最坏的情况下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条数据显然不能一次性读入内存供程序使用， 因此， 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销，以现在磁盘的IO能力和CPU的运算能力， 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据， 速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。换言之，查找次数是以树的分叉数为底，记录总数的对数，用公式来表示就是

索引的缺点：

查询数据的速度上升， 写入数据的速度下降，原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销

三：非聚集索引

非聚集索引， 也就是我们平时经常提起和使用的常规索引。

非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 如下图

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据，如下图）

不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。

（也就是最后无论如何都要通过主键进行找到数据的）

然而， 有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种方法 称之为「覆盖索引」查询， 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出， 当为字段建立索引以后， 字段中的内容会被同步到索引之中， 如果为一个索引指定两个字段， 那么这个两个字段的内容都会被同步至索引之中。

 

执行过程：

使用CREATE 语句创建索引

CREATE INDEX index_name ON table_name(column_name,column_name) include(score)

//建立索引

create index index_birthday on user_info(birthday);

//查询生日在1991年11月1日出生用户的用户名

select user_name from user_info where birthday = '1991-11-1'

这句SQL语句的执行过程如下

1.首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

2.然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就是真实数据（数据行）存储的位置

最后， 从得到的真实数据中取得user_name字段的值返回， 也就是取得最终的结果

我们把birthday字段上的索引改成双字段的覆盖索引

create index index_birthday_and_user_name on user_info(birthday, user_name);

这句SQL语句的执行过程就会变为

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而， 叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能