[数据库]数据库索引

由于没有学过数据库这门课，所以使用的时候很多概念都不知道，前段时间学长让我学习一下索引相关的知识，因为面试的时候会经常问道，而且当时项目也确实用到了这个问题，在此简单记录一下，以后再慢慢学习慢慢整理。

我理解的数据库索引就是用空间换取时间，如果没有索引，查找数据库中某个字段的时候就会进行顺序查找直到找到符合条件的数据，如果数据量非常庞大的话，这个开销肯定是非常恐怖的，所以在某个经常进行查找排序等操作的字段上建立索引，这样会额外建立一个数据结构以使得改字段呈某种规律被持有，在查找的时候就能快速被找到，但是这个额外的数据结构肯定也需要维护。以上完全是个人理解，不一定保证正确。

通过学习慢慢学习了相关的知识，比如聚集索引与非聚集索引，Hash与B树等等，下面简单总结一下。

一、什么是索引

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。它是用于提高数据库表数据访问速度的数据库对象。

优点：

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

二、索引的类型

索引分为聚集索引与非聚集索引

• 聚集索引，表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
• 非聚集索引，表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。

1、聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

聚集索引中，叶结点也即数据结点，所有数据行的存储顺序与索引的存储顺序一致。

2、非聚集索引

非聚集索引与聚集索引相比：

1. 叶子结点并非数据结点
2. 叶子结点为每一真正的数据行存储一个“键-指针”对
3. 叶子结点中还存储了一个指针偏移量，根据页指针及指针偏移量可以定位到具体的数据行。
4. 类似的，在除叶结点外的其它索引结点，存储的也是类似的内容，只不过它是指向下一级的索引页的。

对于根与中间级的索引记录，它的结构包括：

1. 索引字段值
2. RowId（即对应数据页的页指针+指针偏移量）。在高层的索引页中包含RowId是为了当索引允许重复值时，当更改数据时精确定位数据行。
3. 下一级索引页的指针

对于叶子层的索引对象，它的结构包括：

1. 索引字段值
2. RowId

3、对比

聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。

对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

我个人的理解，由于每张表中只有一个聚集索引，并且聚集索引的逻辑顺序与表实际的物理顺序是相同的，所以索引的叶子节点可以直接指向数据页。

而非聚集索引只是逻辑上连续，与实际数据的物理顺序没有必然联系，所以非聚集索引的叶节点必须要建立对物理数据行的一一对应关系。

三、索引的实现

关于B树，B+树，这篇博客介绍发非常详细，不想复制了。

链接

四、MySQL相关操作

1、查看索引

mysql> show index from tblname;

其中的tbname为表的名称，比如我的数据库中news_table是一个用于存储新闻的表，其中的新闻地址会经常被查找，所以在该字段建立了索引，使用上述命令就可以看见在news_table中有几个索引，还可以看出索引的类型，下面显示的是BTREE。

关于各个参数的意义有详细的介绍：（参考地址：点击打开链接）

名称	含义
Table	表的名称
Non_unique	如果索引不能包括重复词，则为0。如果可以，则为1。
Key_name	索引的名称。
Seq_in_index	索引中的列序列号，从1开始。
Column_name	列名称。
Collation	列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality	索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed	指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null	如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

2、建立索引

CREATE INDEX index_name ON table(column(length))

由于接触的不是很深入，我直接创建了索引，没有指定length。

• index_name表示索引的名称，就是上面说到的Key_name。
• table表示列所在的表，对应上面的Table。
• column表示列名称，对应上面的Column_name。

CREATE INDEX address_index ON news_table(news_address);

3、删除索引

DROP INDEX index_name ON table

• index_name表示索引的名称，就是上面说到的Key_name。
• table表示列所在的表，对应上面的Table。

DROP INDEX address_index ON news_table;