数据库的索引是怎么工作的？

译文：
随着数据量逐渐增大，索引变得十分重要，有人可以解释一下索引到底是怎么工作的吗？

为什么需要

当数据存在硬盘型的存储设备上时，它是以数据块的形式存储的。这些数据块整个生命周期都会被访问，进行原子访问操作。磁盘的数据块被规划的和链表差不多；两者都含有数据的一部分，一个指向下一个节点（块）的指针，同时它们的存储也不是连续不断的。

由于一定数量的数据只能被存储在一个区域，我们可以说搜索一个没有排序的区域需要全局搜索也就是N/2数据库的访问量（平均），N指的是数据量的大小。如果这个区域是一个non-key区域（例：不包括特殊的值）那么全局搜索的数据访问量必须是N。

对于已排序的数据区域，二分查找可能会被用到，它的数据访问量为 log2N,同时因为数据是non-key型的，一旦大的值被找到，剩下的另一半的数据是不需要被查找的，因此它的性能提升很显著。

什么是索引

索引是一个对很多数据多个字段进行排序的方法。对一个字段进行索引的同时会创建另一个数据结构来存储该字段的值，还有一个指向对应数据的指针。然后这个索引结构会进行排序，使得二分查找可以运用其中。

创建索引的劣势是这些索引都需要硬盘上的额外空间，因为索引都被MyISAM引擎存储在了表中。如果有很多字段添加了索引的话，那么文件系统的存储空间很快就达到限制值。

是怎么工作的

首先，我们先列出一张数据库中的样表

Field name	Data type	Size on disk
id (Primary key)	Unsigned INT	4 bytes
firstName	Char(50)	50 bytes
lastName	Char(50)	50 bytes
emailAddress	Char(100)	100 bytes

提示：char是用来精确限制存储数据的实际大小的。这份样表包含了五百万行未被索引的数据。现在来分析一下它的查询性能。一个查询ID（一个已排序的字段）另一个查询firstName（一个未排序的字段）。

例1 - 已排序vs未排序字段
给定一个 r = 5,000,000 的数据样本，每一条数据的大小为 R = 204 bytes，它们都被MyISAM引擎进行了排序，默认的数据块大小为 B = 1024 bytes。每个块就是 bfr = (B/R) = 1024/204 = 5 条数据。那么这张表总共有 N = (r/bfr) = 5,000,000/5 = 1,000,000 条数据块。

针对ID进行全局搜索的复杂度平均大概是 N/2 = 500,000, 考虑到ID字段是一个key字段。因为id还是排序好了的，二分查找的查询复杂度大概是log2 1,000,000 = 19.93 = 20 个数据块访问量。我们可以立刻明显的看出它的性能提升。

而firstName既不是一个key字段也不是一个已排序的字段，那二分查找是不可能的，它的值也不是unique的，因此整张表的查询可能要访问 N = 1,000,000 个数据块。这便是索引的目的。

考虑到一个已索引的数据只包含了已索引字段和一个指向原数据的指针，这就确保了它会比它指向的多字段数据更小，所以索引本身会占用比原数据更小的磁盘空间，也导致了遍历查询时接入的数据块的量更小。关于firstName的索引结构如下；

Field name	Data type	Size on disk
firstName	Char(50)	50 bytes
(record pointer)	Special	4 bytes

提示：MySQL的指针大概是2~5bytes，根据表的大小的不同而定

例2 - 添加索引
给定一个 r = 5,000,000 的含索引 R=54 bytes 的数据样本,数据块大小默认为 B = 1024 bytes. 一个数据块可以存储的索引为 bfr = （B/R） = 1024/54 = 18 条记录。所有的数据块可以存储 的索引数据量为 N = (r/bfr) = 5,000,000/18 = 277778 个。

现在对firstName字段进行全局搜索，可以利用索引来增加性能。采用二分查找的平均数据块访问量为 log2 277,778 = 18.08 = 19 次。为了找到实际的数据地址，会增加一个数据块的访问也就是一共19+1 的数据块访问量，这比一个没有索引的1,000,000数据块的查询快多了。

什么时候应该被使用

考虑到创建索引需要占用额外的磁盘空间（277,778个数据块需要多占用28%的额外空间），太多的索引会导致文件系统出现很多文件大小限制的问题，选择正确的索引变得十分重要。

既然索引只是为了加快查询匹配数据的速度，如果只是为了输出数据而创建索引的话纯粹是一种磁盘空间浪费，而且为增加插入，删除数据的时间，因此这种情况应该被避免。而且考虑到二分查找的本质，数据的cardinality或者独特性是很重要的。一个cardinality为2的数据会将数据一分为二，而一个cardinality为1,000的数据大概会被分成1,000块。如果cardinality很低的话，查询的效率会减少到全局搜索，当cardinality小于数据量的30%时，应该避免用索引来进行查询优化，因为它只会浪费磁盘空间。

---

原文地址

https://stackoverflow.com/questions/1108/how-does-database-indexing-work

关于cardinality理解

https://stackoverflow.com/questions/10621077/what-is-cardinality-in-databases

在数学中，它的学名叫基数，具体含义可以自行查找。

在数据库中，Cardinality反应了一列数据的独特性大小，如果一列数据有很多重复的值，比如只有true或false，那么它的Cardinality就很低，但如果它的值有很高的独特性(比如身份证号码)，那么Cardinality就很高。