\(^_^)/ 数据库索引

参考：http://txf2004.iteye.com/blog/1729935

参考：http://guoyankun.iteye.com/blog/1765898

参考：http://yanqingluo.iteye.com/blog/1781671

参考：http://huoli.iteye.com/blog/456799

参考：http://oracledba.iteye.com/blog/448579

 

 

 

 

 

 

 

概念：

使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。

在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

 

 

 

 

 

 

 

优点：

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

 

 

 

 

 

 

 

 

索引的缺点：

1.索引需要占物理空间。建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

 

 

 

 

 

 

 

使用索引原则：

在大表上建立索引才有意义。

在where子句或是连接条件上经常饮用的列上建立索引。 

索引的层次不要超过4层。 

如果表主要是提供查询，那么在经常查询的字段上建立索引。

作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。

可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。

经常被用来搜索数据记录的字段

尽量不要为数据内容重复率很高的字段创建索引，它所带来的效益很少 

数据记录越多，索引提高数据访问的效率的幅度就越明显 

 

 

 

 

 

 

 

 

确定索引的有效性：

1.检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。

2.对新索引进行试验以检查它对运行查询性能的影响。

3.考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。

4.检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。

5.检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型。

6.查询操作中的order by 语句的数据排序，group by 语句的数据分组，连接表

 

 

 

 

 

 

 

 

 

索引的分类：

1.普通索引(INDEX)：这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。create index 索引名 on 表名(字段名，。。。)；

2.唯一性索引(UNIQUE)：在一个表上可以创建多个，索引值不能重复。允许有空值。create unique index 索引名 on表名(字段名，。。。)

3.主键索引(PRIMARY KEY)：在一个表上只能创建一个主键，主键字段不能出现重复值，不允许为空。

4.全文索引(FULLTEXT)：create fulltext index 索引名 on 表名(字段名，。。。)；

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

 

按存储结构分：聚集索引，非聚集索引 

按照数据存储方式：可以分为B*树、反向索引、位图索引。 

按照索引列的个数分类：可以分为单列索引、复合索引。 

按照索引列值的唯一性：可以分为唯一索引和非唯一索引。 

此外还有函数索引，全局索引，分区索引，函数索引

 

逻辑上：

Single column 单行索引

Concatenated 多行索引

Unique 唯一索引

NonUnique 非唯一索引

Function-based函数索引

Domain 域索引

 

物理上：

Partitioned 分区索引

NonPartitioned 非分区索引

B-tree：

Normal 正常型B树

Rever Key 反转型B树 

Bitmap 位图索引

 

 

 

 

 

 

 

 

 

 

主键一唯一索引的区别：

1 一个表的主键只能有一个，而唯一索引可以建多个。

2 主键可以作为其它表的外键。

3 主键不可为null，唯一索引可以为null。

 

 

 

 

 

 

 

 

 

适合使用聚集索引的情况： 

1 如果某字段所包含的有差别的数据的数目有限，则非常适合为该字段创建聚集索引 

2 使用betwwen，>,<,>=与<=等运算符返回介于特定范围的数据记录的查询 

3 按照特定次序访问的字段 

4 返回大量结果集的查询 

5 经常用于查询的连接条件或group by语句的字段 

6 使用聚集索引搜索为一键列的速度一非常快 

 

 

 

 

 

 

 

 

索引的结构：

 

B-tree索引：

基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。

默认情况下大多使用Btree索引，该索引就是通常所见 唯一索引、聚簇索引等等。

适合与大量的增、删、改（OLTP）；

不能用包含OR操作符的查询；

适合高基数的列（唯一值多）

典型的树状结构；

每个结点都是数据块；

大多都是物理上一层、两层或三层不定，逻辑上三层；

叶子块数据是排序的，从左向右递增；

在分支块和根块中放的是索引的范围；

但当检查的范围超过表的10%时就不能提高取回数据的性能。

反向索引,降序索引。

 

Bitmap索引:

适合与决策支持系统，OLAP（联机数据分析）方面，数据仓库方面，或静态数据，不支持行级锁定。

做UPDATE代价非常高；

非常适合OR操作符的查询； 

基数比较少的时候才能建位图索引，用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零）。

位图以一种压缩格式存放，节省存储空间。

在索引中使用位图而不是列值，使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。

通常在事实表和维表的键之间有很低的集的势（cardinality），使用位图索引，存储更为有效。

位图索引将比较,连接和聚集都变成了位算术运算,大大减少了运行时间，从而得到性能上的极大的提升。