Let's suppose you have a book, probably a novel, a thick one with lots of things to read, hence lots of words. Now, hypothetically, you brought two dictionaries, consisting of only words that are only used, at least one time in the novel. All words in that two dictionaries are stored in typical alphabetical order. In hypothetical dictionary A, words are printed only once while in hypothetical dictionary B words are printed as many numbers of times it is printed in the novel. Remember, words are sorted alphabetically in both the dictionaries. Now you got stuck at some point while reading a novel and need to find the meaning of that word from anyone of those hypothetical dictionaries. What you will do? Surely you will jump to that word in a few steps to find its meaning, rather look for the meaning of each of the words in the novel, from starting, until you reach that bugging word.

这就是SQL中索引的工作方式。假设字典A是PRIMARY INDEX，字典B是KEY/SECONDARY INDEX，并将获取单词含义的愿望作为QUERY/SELECT语句。 索引将有助于以非常快的速度获取数据。如果没有索引，您将不得不从头开始查找数据，这是一项不必要的耗时且昂贵的任务。

有关索引和类型的更多信息，请看这个。

基本上表上的索引就像书中的索引一样(这就是这个名字的由来):

Let's say you have a book about databases and you want to find some information about, say, storage. Without an index (assuming no other aid, such as a table of contents) you'd have to go through the pages one by one, until you found the topic (that's a full table scan). On the other hand, an index has a list of keywords, so you'd consult the index and see that storage is mentioned on pages 113-120,231 and 354. Then you could flip to those pages directly, without searching (that's a search with an index, somewhat faster).

当然，索引的有用程度取决于许多事情——举几个例子，使用上面的明喻:

if you had a book on databases and indexed the word "database", you'd see that it's mentioned on pages 1-59,61-290, and 292 to 400. In such case, the index is not much help and it might be faster to go through the pages one by one (in a database, this is "poor selectivity"). For a 10-page book, it makes no sense to make an index, as you may end up with a 10-page book prefixed by a 5-page index, which is just silly - just scan the 10 pages and be done with it. The index also needs to be useful - there's generally no point to index e.g. the frequency of the letter "L" per page.

基本上，索引是所有按顺序排序的键的映射。有了一个按顺序排列的列表，它就不需要检查每个键，而是可以这样做:

1:去列表的中间-比我想要的高还是低?

2:如果高，就去中间和底部的中间点，如果低，就去中间和顶部的中间点

3:是高还是低?再次跳转到中间点，等等。

使用该逻辑，您可以在大约7步的时间内在排序列表中找到一个元素，而不是检查每一项。

显然，这里有很多复杂的东西，但这给了你基本的概念。

Let's suppose you have a book, probably a novel, a thick one with lots of things to read, hence lots of words. Now, hypothetically, you brought two dictionaries, consisting of only words that are only used, at least one time in the novel. All words in that two dictionaries are stored in typical alphabetical order. In hypothetical dictionary A, words are printed only once while in hypothetical dictionary B words are printed as many numbers of times it is printed in the novel. Remember, words are sorted alphabetically in both the dictionaries. Now you got stuck at some point while reading a novel and need to find the meaning of that word from anyone of those hypothetical dictionaries. What you will do? Surely you will jump to that word in a few steps to find its meaning, rather look for the meaning of each of the words in the novel, from starting, until you reach that bugging word.

这就是SQL中索引的工作方式。假设字典A是PRIMARY INDEX，字典B是KEY/SECONDARY INDEX，并将获取单词含义的愿望作为QUERY/SELECT语句。 索引将有助于以非常快的速度获取数据。如果没有索引，您将不得不从头开始查找数据，这是一项不必要的耗时且昂贵的任务。

有关索引和类型的更多信息，请看这个。

我想发表我的意见。我还远不是数据库专家，但我最近读了一些关于这个主题的文章;足以让我试着申请ELI5。所以，这是一个外行的解释。

我的理解是，索引就像你的表的迷你镜子，很像一个关联数组。如果你给它一个匹配的键，那么你可以在一个“命令”中跳转到那一行。

但是如果没有索引/数组，查询解释器必须使用for循环遍历所有行并检查是否匹配(全表扫描)。

拥有索引的“缺点”是额外的存储空间(用于迷你镜像)，而“优点”是更快地查找内容。

请注意(依赖于您的db引擎)创建主键、外键或唯一键会自动设置各自的索引。同样的原理基本上就是这些钥匙为什么以及如何工作。

看看这个链接:http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

它们是如何工作的，这是一个太宽泛的主题，无法在一篇SO帖子中涵盖。

下面是我所见过的关于索引的最好的解释之一。不幸的是，它是SQL Server而不是MySQL。我不确定这两者有多相似……