数据库索引是如何工作的？以及，索引有什么帮助呢？

我们通过一个简单的例子来开始教程，解释为什么我们需要数据库索引。假设我们有一个数据库表 Employee ，这个表有三个字段（列）分别是 Employee_Name、Employee_Age 和 Employee_Address 。假设表 Employee 有上千行数据。

现在假设我们要从这个表中查找出所有名字是 “Jesus” 的员工信息。我们决定使用下面的查询语句：

SELECT * FROM Employee 
WHERE Employee_Name = 'Jesus'

如果表中没有索引会发生什么

一旦我们运行这个查询，在查找名字为 “Jesus” 的员工的过程中，究竟会发生什么？数据库不得不遍历 Employee 表中的每一行并确定员工的名字（Employee_Name）是否为 “Jesus” 。由于我们想要得到每一个名字为 “Jesus” 的员工信息，在查询到第一个符合条件的行后，不能停止查询，因为可能还有其他符合条件的行。所以，必须一行一行的查找直到最后一行，这就意味着数据库不得不检查上千行数据才能找到所有名字为 “Jesus” 的员工。这就是所谓的全表扫描。

数据库索引是怎样提升性能的

你可能会想，做一个全表扫描对于如此简单的事情来说似乎效率低下 – 数据库不应该更聪明吗？这就像用人眼从头到尾浏览整张表一样 – 很慢也不优雅。但是，你可能根据文章标题已经猜到，这就是索引派上用场的时候。使用索引的意义就是通过缩小一张表中需要查询的记录（行）的数目来加快搜索的速度。

什么是索引

索引是存储表中一个特定列的值的数据结构（最常见的是B-tree）。索引是在表的列上创建的。因此，要记住的关键点是索引由一个表中的列值组成，这些值存储在数据结构中。请记住这一点：索引是一个数据结构。

什么样的数据结构可以作为索引

B-trees 是索引最常用的数据结构。因为它的时间复杂度低，查找、删除、插入操作都可以在对数时间内完成。另外一个重要的原因是存储在 B-trees 中的数据是有序的。数据库管理系统（RDBMS）通常决定索引应该用哪个数据结构。但是，在某些情况下，你在创建索引时可以指定索引要使用的数据结构。

哈希索引是怎样工作的

哈希表是另外一种你可能看到用作索引的数据结构 – 这些索引通常被称为哈希索引。之所以使用哈希索引，是因为哈希表在查找值时非常有效。因此，如果使用哈希索引，对于比较字符串是否相等的查询能够极快的检索出值。例如之前我们讨论过的这个查询：

SELECT * FROM Employee 
WHERE Employee_Name = 'Jesus'

它可以受益于创建在 Employee_Name 列上的哈希索引。哈希索引的工作方式是将列的值作为索引的键值（key），和键值相对应的实际的值（value）是指向该表中相应行的指针。由于哈希表基本上可以看作是关联数组，因此一个典型的数据项就像 “Jesus => 0x28939” ，其中 0x28939 是对内存中表中包含 “Jesus” 这一行的引用。在哈希索引中查询一个像 “Jesus” 这样的值，并得到对应行在内存中的引用，明显要比扫描全表获得值为 “Jesus” 的行的方式快得多。

哈希索引的缺点

哈希表是无序的数据结构，对于很多类型的查询语句，哈希索引都无能为力。举例来说，假如你想要找出所有小于 40 岁的员工。你怎么使用哈希索引进行查询？这不可行，因为哈希表只适合查询键值对 – 也就是说检查相等的查询（例：like "WHERE name = ‘Jesus’ "）。哈希表的键值映射也暗示着其键的存储是无序的。这就是为什么哈希索引通常不是数据库索引的默认数据结构 – 因为在作为索引的数据结构时，其不像 B-trees 那样灵活。可以去看看二叉树与哈希表。

还有什么其他类型的索引

使用 R-tree 作为数据结构的索引通常用于帮助解决空间问题。例如，一个查询要求“查询出所有距离我两公里之内的星巴克”，如果数据库表使用 R-tree 索引，这类查询的效率将会提高。
另一种类型的索引是位图索引（bitmap index），这类索引适合放在包含布尔值（ true 和 false ）的列上，但是这些值（ true 或 false ）的许多实例 – 基本上都是选择性（selectivity）低的列。

索引是怎么提升性能的

因为索引基本上是用于存储列值的数据结构，所以查找这些值的速度会更快。如果索引使用的是最常用的数据结构 B-trees ，那么其中的数据会是有序的。有序的列值可以极大的提升性能。下面解释原因。
假设我们在 Employee_Name 这一列上创建一个 B-trees 索引。这意味着当我们使用之前的 SQL 查找姓名是 “Jesus” 的员工时，不再需要扫描全表。而是通过索引去查找名字为 “Jesus” 的员工，因为索引已经按照按字母顺序排序。索引已经排序意味着查询一个名字会快很多，因为名字首字母为 ‘J’ 的员工都是排列在一起的。另外重要的一点是，索引同时存储了表中相应行的指针，以便获取其他列的数据。

数据库索引里究竟存的是什么

你现在已经知道数据库索引是在表中的某一列上创建的，并且存储了这一列的所有值。但是，需要理解的重点是数据库索引并不存储这个表中其他列（字段）的值。举例来说，如果我们在 Employee_Name 列创建索引，那么列 Employee_Age 和 Employee_Address 上的值并不会存储在这个索引当中。如果我们确实把其他所有字段也存储在个这个索引中，那就成了拷贝一整张表做为索引 – 这样会占用太多的空间，而且效率非常低。

索引存储了指向表中某一行的指针（MySQL - MyISAM）

如果我们要寻找的值是在一个索引中找到的（比如 “Jesus” ），那么如何找到同一行中的其他值呢（比如 “Jesus” 的 Employee_Address 和 Employee_Age ）？很简单 – 数据库索引同时存储了指向表中相应行的指针。指针是对内存中行数据存储在磁盘上的位置的引用。因此，索引中除了存储列的值，还存储着一个指向行数据的指针。也就是说，索引中的 Employee_Name 这列的某个值（或者节点）可以描述为 (“Jesus”, 0x82829) ，其中 0x82829 是存储 “Jesus” 行数据的磁盘上的地址（指针）。如果没有这个指针，你就只能访问到一个单独的值（“Jesus”），这将是毫无意义的，因为你不能获取这一行记录的其他值 – 例如地址（address）和年龄（age）。

数据库怎么知道什么时候使用索引

当这个SQL：

SELECT * FROM Employee 
WHERE Employee_Name = 'Jesus'

查询时，数据库将检查所查询的列上是否有索引。假设 Employee_Name 列上确实创建了一个索引，数据库会接着检查使用这个索引做查询是否合理 – 因为在某些情况下，使用索引比起全表扫描会更加低效。如果想要了解更多这样的场景，请阅读这篇文章：Selectivity in SQL。

能强制数据库使用索引吗

一般来说，你不会告诉数据库什么时候使用索引 – 这将由数据库自己决定。然而值得注意的是，在大多数数据库中（如 Oracle 和 MySQL ），你实际上可以指定要使用的索引。

如何在 SQL 中创建索引

之前的例子中，在 Employee_Name 列上创建索引的 SQL 如下：

CREATE INDEX name_index
ON Employee (Employee_Name)

如何在SQL中创建联合索引

我们可以在员工表上创建两个列的联合索引，SQL 如下：

CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)

把数据库索引类比成什么比较好呢

一个很好的类比是将数据库索引看作是书中的索引。如果你有一本关于狗的书，而且你正在寻找关于金毛寻回犬的部分，那么你为什么要翻阅整本书呢？这相当于数据库术语中的全表扫描，你只需要转到书后面的索引，它会告诉你在哪里可以找到金毛寻回犬的信息。类似地，当图书索引包含页码时，数据库索引包含指向包含你在 SQL 中搜索的值的行的指针。

使用数据库索引会有什么代价

那么，使用数据库索引有哪些缺点呢？首先，索引会占用空间 – 你的表越大，索引占用的空间越大。其次，性能损失（主要是值的更新操作），当你在表中添加、删除或者更新行数据的时候， 在索引中也会有相同的操作。记住：建立在某列（或多列）的索引需要保存该列最新数据。

一般来说，只有在频繁查询索引列中的数据时，才应在表上创建索引。

原文链接：http://www.programmerinterview.com/database-sql/what-is-an-index/