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

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.

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

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.

看这个视频了解更多关于索引的细节

简单的索引 您可以在表上创建唯一的索引。唯一索引意味着两行不能有相同的索引值。下面是在表上创建Index的语法

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

您可以使用一个或多个列来创建索引。例如，我们可以使用tutorial_author在tutorials_tbl上创建索引。

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

您可以在表上创建一个简单的索引。只需从查询中省略UNIQUE关键字以创建简单的索引。简单索引允许表中有重复的值。

如果要按降序索引列中的值，可以在列名后添加保留字DESC。

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)

在答案列表中添加一些可视化表示。

MySQL使用了一个额外的间接层:次要索引记录指向主索引记录，主索引本身保存磁盘上的行位置。如果行偏移量发生变化，则只需要更新主索引。

注意:磁盘数据结构在图中看起来是平面的，但实际上是一个 B +树。

来源:链接

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语句。 索引将有助于以非常快的速度获取数据。如果没有索引，您将不得不从头开始查找数据，这是一项不必要的耗时且昂贵的任务。

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

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。桌子越大，花费就越多。如果表中存在相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比按顺序读取每一行快得多。

索引添加一个包含搜索条件列和指针的数据结构 对象所在行的内存磁盘地址 其他信息 对索引数据结构进行排序，优化查询效率 该查询查找索引中的特定行;索引指向将查找剩余信息的指针。 索引将查询必须搜索的行数从17减少到4。