MySQL索引是如何工作的?

我对MySQL索引的工作原理非常感兴趣，更具体地说，它们如何在不扫描整个表的情况下返回所请求的数据?

我知道这离题了，但如果有人能给我详细解释一下，我会非常非常感谢。

215437 次浏览
小开

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

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

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

小开

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

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

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

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

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

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

小开
最佳答案

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

假设你有一本关于数据库的书，你想找到一些关于存储的信息。如果没有索引(假设没有其他帮助，比如目录)，你必须一个一个地浏览页面，直到你找到主题(那是full table scan)。 另一方面，索引有一个关键字列表，所以你可以查阅索引并看到storage在113-120,231和354页被提到。然后你可以直接翻到那些页面，不需要搜索(这是一个有索引的搜索，稍微快一些)

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

  • 如果你有一本关于数据库的书，并索引了“database”这个词，你会看到它在第1-59页、61-290页和292 - 400页都有提到。在这种情况下，索引没有太大帮助，一个一个地浏览页面可能会更快(在数据库中，这是“低选择性”)。
  • 对于一本10页的书，做索引是没有意义的，因为你最终可能会得到一本10页的书，前缀是一个5页的索引，这很愚蠢——只需要扫描10页就可以了。
  • 索引也需要有用——索引通常没有意义，比如每页出现字母“L”的频率。
小开

您必须知道的第一件事是，索引是一种避免扫描整个表以获得您正在寻找的结果的方法。

有不同种类的索引，它们是在存储层实现的，所以它们之间没有标准，它们也取决于你使用的存储引擎。

InnoDB和B+树索引

对于InnoDB，最常见的索引类型是基于B+树的索引，它以排序的顺序存储元素。此外，您不必访问实际表来获取索引值，这使得您的查询返回速度更快。

关于这种索引类型的“问题”是，您必须查询最左边的值才能使用索引。因此，如果你的索引有两列，比如last_name和first_name，你查询这些字段的顺序这很重要。

给定下面的表格:

CREATE TABLE person (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
INDEX (last_name, first_name)
);

这个查询将利用索引:

SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"

但接下来的一个则不然

SELECT last_name, first_name FROM person WHERE first_name = "Constantine"

因为你首先查询的是first_name列，而它不是索引中最左边的列。

最后一个例子更糟糕:

SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"

因为现在，你比较的是索引中最右边字段的最右边部分。

哈希索引

这是一种不同的索引类型，不幸的是，只有内存后端支持。它速度极快，但只对完全查找有用，这意味着你不能将它用于>、<或LIKE这样的操作。

因为它只适用于内存后端，所以您可能不会经常使用它。我现在能想到的主要情况是，您在内存中创建一个临时表，其中包含来自另一个选择的一组结果，并使用散列索引在这个临时表中执行许多其他选择。

如果你有一个很大的VARCHAR字段，你可以在使用B-Tree时“模拟”哈希索引的使用，方法是创建另一列并保存该列上大值的哈希值。假设您要在字段中存储一个url，值相当大。您还可以创建一个名为url_hash的整数字段，并在插入url时使用类似CRC32的哈希函数或任何其他哈希函数来哈希。然后，当你需要查询这个值时，你可以这样做:

SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");

上面例子的问题是，由于CRC32函数生成了一个相当小的散列，你最终会在散列值中产生很多冲突。如果你需要精确的值，你可以通过以下方法修复这个问题:

SELECT url FROM url_table
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";

即使碰撞数很高，哈希仍然是值得的，因为你只会对重复的哈希执行第二次比较(字符串一)。

不幸的是，使用这种技术，你仍然需要点击表来比较url字段。

总结

每当你想谈论优化时，你可能会考虑一些事实:

  1. 整数比较比字符串比较快得多。可以用InnoDB中哈希索引模拟的例子来说明。

  2. 也许，在流程中添加额外的步骤会使其更快，而不是更慢。这可以通过以下事实来说明:你可以将SELECT优化为两个步骤，使第一个步骤将值存储在一个新创建的内存表中，然后在第二个表上执行更重的查询。

MySQL也有其他索引，但我认为B+树索引是最常用的，哈希索引是一个很好的了解，但你可以在MySQL文档中找到其他索引。

我强烈推荐你去读“高性能MySQL”这本书，上面的答案肯定是基于它关于索引的章节。

小开

有关索引的更多细节，请参阅这视频

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

上创建索引的语法 
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)
小开

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

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

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

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

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

小开

在答案列表中添加一些可视化表示。enter image description here

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

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

来源:链接

小开

在MySQL InnoDB中，有两种索引类型。

  1. 主键，称为聚类索引。索引关键字存储

  2. 非聚集索引的辅助键。这些索引只在B+Tree叶子节点中存储主键的关键字以及它们自己的索引关键字。所以在从二级索引进行搜索时，首先会找到它的主键索引关键字，然后扫描主键B+树，找到真实的数据记录。这将使二级索引比主索引搜索慢。然而，如果select列都在辅助索引中，则不需要再次查找主索引B+Tree。这叫做覆盖指数。

小开
让我们假设你有一本书，可能是一本小说，一本厚厚的书，有很多东西要读，因此有很多单词。 现在，假设你带来了两本词典，只包含了在小说中至少一次只用过的词。两本词典中的所有单词都以典型的字母顺序存储。在假设字典A中，单词被打印为只有一次，而在假设字典B中，单词被打印为在小说中出现了很多次。记住，在这两本词典中，单词都是按字母顺序排序的。 现在你在阅读一本小说时遇到了瓶颈，需要从那些假设的字典中找到这个词的意思。你会怎么做?当然，你会在几个步骤中跳到那个词来寻找它的意思，而不是在小说中寻找每个词的意思，从头开始，直到你读到那个烦人的词 这是SQL中索引的工作方式。假设字典A是PRIMARY INDEX，字典B是KEY/SECONDARY INDEX，并将获取单词含义的愿望作为QUERY/SELECT语句。 索引将有助于以非常快的速度获取数据。如果没有索引，您将不得不从头开始查找数据，这是一项不必要的耗时且昂贵的任务

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

小开

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

  • 索引添加一个包含搜索条件列和指针的数据结构

  • 指针为
    所在行的内存磁盘地址 其余的信息

  • 对索引数据结构进行排序，优化查询效率

  • 查询查找索引中的特定行;索引指向将查找剩余信息的指针。

  • 索引将查询必须搜索的行数从17减少到4。


提交答案