数据库----索引原理

解决四个问题，即可理解索引原理，答案都在下文。

• 为什么要加上主键?
• 为什么加上索引就会使查询变快？
• 为什么加上索引后写入、修改、删除就会变慢？
• 什么情况下同时在两个字段上建立索引？

在某些关系数据库中，如果建表时没有主键，则不允许建表，但有些数据库是允许可以没有主键的，oracle数据库建表就是可以没有主键。无主键、有主键是两种不同的情况。

①、无主键：若一个数据表没有主键，则它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐，这才是真正的‘表’形式。

②、有主键：若一个数据表加了主键，那么表在磁盘上的存储结构就由整齐的排列结构变成了树状结构（很重要的平衡树）。换句话说，加入了主键，整个表就变成了一个索引。也就是所谓的 “聚集索引”，这也是为什么一个表只能有一个主键，一个表只能有一个 “聚集索引”。

总结：主键的作用就是将 “表” 转换成 “索引（平衡树）”的格式放置。

 其中，树中的子节点（除叶子节点外）数据都是由主键字段中的数据构成。也就是通常我们指定主键的id字段。叶子节点才是真正表中的数据。例如：select * from table where id = '12345'

首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。平衡树的运行过程细节在下文解释。总结就是先用索引找到叶节点，再用叶节点找数据行。树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。

 第二个例子：如果有一亿条数据，最坏的情况下要匹配一亿次才能完成，即最坏情况 O(n) 才可处理，而且这一亿条数据肯定不能一次性读入内存，想要完成这次查询，个人PC上基本完成不了。而如果将它换成平衡树结构（一棵非常茂盛、子节点非常多的树）。假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据，即 O(logn) ，n是记录总树，底数是树的分叉数，结果就是树的层次数。

我们可以发现，查找次数呈指数型下降，利用索引可以使得数据库查询性能提升非常多。

然而，索引有优点也有缺点，我们发现它是平衡树的结构，如果我们增加、删除、修改一些数据，为了保持平衡树的结构，DBMS必须重新梳理树（索引）的结构，这会带来不小的性能开销。总结一下：索引能使数据库查询数据的速度上升，而使写入数据的速度下降，有主键的表就相当于 ‘聚集索引’ ，下面介绍非聚集索引。

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

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

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

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

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

eg:

//创建索引

create index index_birthday on user_info(birthday);  

创建了 birthday 字段的索引就相当于复制了一遍 birthday 字段中的值。(重点，用于理解）

//查询表中生日在1991-11-11的用户名

select name from user_info where birthday = '1991-11-11'

上述SQL语句执行过程：①先通过索引 index_birthday 找到 birthday = ‘1991-11-11’的所有记录的主键。②然后通过主键 ID 执行聚集索引查找，找到主键 ID 对应的真实数据（数据行）存储的位置。③ 在真实数据中取得 user_name 字段的值返回。

----------------------------------------------------------------------------------------------------------

eg：将 birthday 字段上的索引修改为双字段的覆盖索引。

create index index_birth_username on user_info (user_name, birthday);

再次做上述的查询语句时，SQL的执行过程就会改变。

①通过非聚集索引 index_birth_username 查找 birthday 等于1991-11-1的叶节点的内容，然后，叶节点中除了user_info表的主键 ID 值外，user_name字段的值也在里面。所以不用再绕一大圈，可以直接取得叶节点中 user_name 的值返回即可。

 学习链接：http://www.cnblogs.com/heapStark/p/8336492.html