数据库-索引

数据库-索引

概述

定义

索引本质是数据库管理系统用于加快查询速度，使用的一种与表有关的数据库结构。

那么索引到底是什么呢？

索引有两个层次的概念：

• 数据库层次:比如Mysql里边的 普通索引，主键索引(无重复，非空)，唯一索引(无重复)，全文索引(对于文本很有用)
• 实现层次:BTree，Hash索引都是将数据库概念的索引实现的具体方式，是一种数据库结构

好处

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

坏处

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

适用条件

• 在经常需要搜索，范围查询，排序的列上
• 主键和外键

不适用条件

• 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
• 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，在建立BTree的时候没有区分度，对于Hash则大量重复
• 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

具体实现

根据定义，索引是一种数据结构，那么通常有哪几种呢？

• 一类是与储存数据结构无关的，包括BTree索引和Hash索引
• 另一类是改变储存数据结构的，现有聚集索引

BTree索引

介绍

BTree在原理上有点类似于二叉排序树，如上图，树的节点包括索引的键值，左右指针，键值对应列储存的指针，在查询的时候，我就可以对当前的节点进行判断，看需要查询的数据在我的左边还是右边。

适用范围

建立索引范围

• 可以对多个键值建立索引
• 适用于所有类型的键值

查询范围

• 可以进行全关键字(在多个关键字的情况下)，关键字范围的查询
• 可以进行关键字最左前缀的关键字查询

例如一个以 lastname,firstname,birth 的Btree索引,索引组织结构如下(上边是索引，下边是全部的储存的数据，裂痕代表有数据被省略)：

那么可以索引适用范围如下:

• 匹配全值：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。
• 匹配最左前缀：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。
• 匹配列前缀：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。
• 匹配值的范围查询：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。
• 匹配部分精确而其它部分进行范围匹配：可以利用索引查找last name为Allen，而first name以字母K开始的人。
• 仅对索引进行查询：如果查询的列都位于索引中，则不需要读取元组的值。

Hash索引

介绍

就如同数据机构上的hash一样，对于Hash索引左边是键值的hash值，右边是键值对应的地址

限制

• 不能使用hash索引排序。
• Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。
• Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

聚簇索引

介绍

左边是聚簇索引，右边为非聚簇索引，对于聚簇索引，主要是利用了局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用，程序运行期间所需要的数据通常比较集中。
聚簇索引对数据储存的数据进行了排序，在进行访问的时候，减小了数据库进行磁盘IO的次数。

限制

• 只有InnoDB支持聚簇索引
• 为了维持储存有序，主键不应该具有随机性，最好是按序增长的数字，如字符串这种不适合使用聚簇索引

参考

数据库索引的实现原理
数据库索引
SQL索引学习-索引结构
理解MySQL——索引与优化