聚焦索引 与 非聚焦索引

1. 什么是数据库索引

        索引，被称之为数据库的目录，可以让我们快速地找到对应的数据。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引的本质就是空间换时间。

2. 索引种类

索引的实现通常采用B树或B+树，加快查询速度也消耗更多空间；

                           2.1.  唯一索引： 不允许任何两行具相同值

                           2.2.  主键索引 ：唯一索引的一种

                           2.3.   聚集索引： 行的物理顺序和键值的索引顺序相同

                           2.4.  普通索引： 无限制

                           2.5.  全文索引： 针对较大的数据生成全文索引很耗时间空间

                          2.6.   组合索引： 最左前缀原则：若对多列建立组合索引，若第二列未使用索引，则第三列也不会使用

                          2.7.   InnoDB主索引： InnoDB的数据文件本身；辅助索引：相应记录主键的值

                          2.8.   MyISAM 索引与数据分离；辅助索引：与主索引无区别；

 

3.索引的创建方式

                         3.1.普通索引

                            3.1.1. 创建索引：CREATE INDEX indexName ON tableName(username(length));

                            3.1.2. 修改表结构：ALTER tableName ADD INDEX [indexName] ON (username(length))

                            3.1.3.创建表的时候直接指定：

                                                           CREATE TABLE tableName (  

                                                                   ID INT NOT NULL,   

                                                                   username VARCHAR(16) NOT NULL,  

                                                                   INDEX [indexName] (username(length))  

                                                                    );

                         3.3.唯一索引

                             3.3.1.  创建索引： CREATE UNIQUE INDEX indexName ON tableName (username(length)) 

                         3.4.主键索引：一般在创建表时创建，PRIMARY KEY(ID)

                         3.5.组合索引：ALTER TABLE tableName ADD INDEX zuhe (name，age,sex);

                               根据MySQL组合索引“最左前缀”原则,建立这样一个索引相当于建立如下三个索引：

                                                  3.5.1.name，age，sex

                                                  3.5.2.name，age

                                                  3.5.3. name

 

4.索引原理

                         4.1.聚焦索引

                              主流的RDBMS都是把 平衡多路搜索树B树（B-tree） 当做数据表默认的索引数据结构的。如果给表加上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，整个表变成了一个索引，也就是所谓的「聚集索引」。主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

 

                    

                                           （此处画的是二叉树，但实际并不是二叉树。而是平衡多路搜索树）

                                  假如数据库的一张表上有一亿条数据，如果采用顺序查询的方式进行查询，那得花费很长的时间才能查询完成；但是如果采用聚焦索引来查询数据那就会快上特别多，因为查找方式是一层一层的查找的（父到子再到子，依次向下）。

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

 

                            4.2.聚焦索引

                                每个非聚焦索引，都会独立生成一个B-树，树上的节点数据复制自表中该索引列。  

                                非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段,每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。

                                 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

                                 非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据，不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径（覆盖索引除外（复合或组合索引））。

 

                                如：create index suoyin on user(age);

                                       select name from user where age= '19'

                                       这条SQL的执行过程如下,  首先通过  非聚焦索引 suoyin  查找索引 age= '19' 的主键ID值，然后通过主键ID值执行聚焦索引查找，找到主键ID对应的真实数据行的存储位置，最后取得 name 的值返回。

                                       但是，我们建立组合索引：

                                       create index suoyin on user(age，name);

                                       则SQL语句 ，select name from user where age= '19'

                                       执行过程如下：

                                      通过非聚集索引suoyin 查找  age= '19' 的叶节点的内容，然而， 叶节点中除了有 name表主键ID的值以外， name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中name的值返回即可。