MySQL索引如何工作？

Question

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

虽然这与主题无关，但如果有人能够详细解释给我听，我将非常感激。

- good_evening

---

http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html - a'r

这是一个非常广泛的问题。如果您有一个不会使用索引的查询的具体示例，并且您不知道原因，您可以发布它，人们可能会提供帮助。 - Hammerite

SELECT * FROM members WHERE id = '1' - 所以为什么使用索引会更快？这个索引在这里起到了什么作用？ - good_evening

这似乎是一个查询指定的索引记录（可能是由主键标识）。由于索引存储在内存中，相应的索引行可以被查看，它包含了一个指向实际数据存储位置的指针，因此索引能让查询更快。MySQL可以直接访问表格中准确的位置而不必扫描整个表格。 - Hammerite

非常好，谢谢！ - Lightness Races in Orbit

Answer 1

基本上，表中的索引就像书中的索引（这也是名称的由来）:

假设你有一本关于数据库的书，想要查找一些关于存储的信息。如果没有索引（假设没有其他辅助工具，如目录），你需要一页一页地翻阅，直到找到该主题（这就是全表扫描）。另一方面，索引有一个关键字列表，因此您可以查询索引并查看到 storage 出现在113-120页、231页和354页。然后，您可以直接翻到这些页面，而无需搜索（这是一种使用索引进行查找，速度较快的方法）。

当然，索引的实用程度取决于许多因素 - 以下是一些示例，使用上述比喻：

• 如果你有一本关于数据库的书，并索引了单词“数据库”，你会发现它在1-59页、61-290页和292-400页都提到过。在这种情况下，索引没有什么帮助，一页一页地查找可能会更快一些（在数据库中，这被称为“选择性差”）。
• 对于一本只有10页的书来说，制作索引没有意义，因为你最终可能会得到一本有5页索引的10页书，这很荒谬 - 只需浏览10页即可。
• 索引还需要有用 - 通常没有必要索引例如每页字母“L”的频率。

- Piskvor left the building

---

你正在解释它是什么，而不是它在技术上如何运作。 - Tutu Kumari

@Tutu Kumari：请查看问题的修订版本；随时根据当前问题进行答案修订（注意各种引擎和索引类型-例如，请参见此处的文档：https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html） - Piskvor left the building

请问您能否提供一些“范围”，例如以博客为例，考虑到“用户”表和“帖子”表，允许进行组合搜索。在大约多少数量级或者在“多少个用户”和/或“多少个帖子”的情况下，如果没有设置适当的索引，建议设置索引或必须设置索引？如果可能的话，请给出一个或一些想法。如果可以，请尝试给出开始担心索引的阈值（多少行/列）。 - Robert

Answer 2

首先，你必须知道的是索引是一种避免扫描整个表格以获取所需结果的方法。

有不同类型的索引，并且它们在存储层中实现，因此它们之间没有标准，也取决于您使用的存储引擎。

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 这样的操作。

由于它仅适用于内存后端，您可能不会经常使用它。我现在能想到的主要情况是，在内存中创建一个临时表格，其中包含从另一个 select 中获得的一组结果，并在该临时表格中使用哈希索引执行许多其他 selects。

如果您有一个很大的 VARCHAR 字段，当使用 B-Tree 时，可以通过创建另一个列并在其上保存大值的哈希值来“模拟”使用哈希索引。假设您正在将 url 存储在一个字段中，这些值相当大。您还可以创建一个名为 url_hash 的整数字段，并使用诸如 CRC32 或任何其他哈希函数来哈希插入的 url。然后，当您需要查询此值时，可以执行以下操作：

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+Tree是最常用的索引类型，哈希索引也是了解的好东西，但您可以在MySQL文档中找到其他索引。

我强烈推荐您阅读《高性能MySQL》一书，上述答案绝对是基于其中关于索引的章节。

- clarete

---

以下查询在上述情况下是否有优势？

1. SELECT last_name, first_name FROM person WHERE last_name= "Constantine"
2. SELECT last_name, first_name FROM person WHERE last_name LIKE "%Constantine"

- AkshayT

第一个查询将会，第二个查询将不会。使用EXPLAIN：http://dev.mysql.com/doc/refman/5.5/en/explain.html要在MySQL中为第二个查询建立索引，必须使用FULLTEXT INDEX：http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html - Emilio Nicolás

我给你点赞是因为你的得分是127，而排名第一的答案得分是256。我想让所有东西在二进制方面变得整洁漂亮，我无法忽略这一点。 - pbarney

最近我遇到了一个类似的问题，需要根据相对URL唯一标识记录。我通过在url字段上放置唯一索引来解决这个问题（该字段的字符长度为768个字符，由于MySQL utf8mb4，整个字段占用3072字节）。如果我使用建议的CRC32哈希，我可以通过在仅使用4个字节的整数字段（CRC32）上创建索引来实现类似的结果，而不是现在使用的每个字段3072字节！ - Rafay

@pbarney 三年后，它们分别接近256和512，这就是我所说的二进制增加！ - nanocv

Answer 3

索引基本上是一个按顺序排列的所有键的映射表。有了排序好的列表，它就可以执行以下操作而不是检查每个键：

1：进入列表中间位置——比我要查找的键高还是低？

2：如果高，前往中间点和底部之间的中点；如果低，前往中间点和顶部之间的中点。

3：高还是低？再次跳转到中间点，并重复上述步骤。

使用这种逻辑，你可以在大约7个步骤内找到已排序列表中的元素，而无需检查每个项。

显然，还有一些复杂性，但这给出了基本想法。

- Joshua

---

这被称为二分查找。 - Cameron Martin

谢谢，终于有一个回答解释了为什么它更快，而不仅仅是数据库如何使用索引的功能。 - Gershon Herczeg

实际步骤次数高度依赖于数据-唯一值的数量和在您的范围内的分布。对于100个值，理论上最多为7步。如何计算步骤数的完整讨论可在此处查看：https://dev59.com/p2kv5IYBdhLWcg3wewtd - Joshua

最常见的MySQL索引是B+树，其工作方式类似于二进制搜索，但并不完全相同。算法复杂度相同，但搜索方式不同。请参阅https://en.wikipedia.org/wiki/B-tree。 - Matt

Answer 4

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

1. 主键，也称为聚集索引。索引关键字与B+树叶节点中的实际记录数据一起存储。

2. 次要键，它是非聚集索引。这些索引仅在B+树叶节点中存储主键的关键词和它们自己的索引关键词。因此，在从次要索引进行搜索时，它将首先查找其主键索引关键词并扫描主键B+树以查找实际数据记录。这将使次要索引比主索引搜索慢。但是，如果select列都在次要索引中，则无需再查找主索引B+树。这被称为覆盖索引。

- sendon1982

Answer 5

请看这个链接：http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html 关于它们如何工作的问题太广泛了，无法在一篇SO文章中涵盖。 这里是我见过的最好的索引解释之一。不幸的是，它是为SQL Server而非MySQL准备的。我不确定两者有多相似…

- Abe Miessler

---

不错的文章。我不懂SQL Server，但基本的工作原理看起来非常相似。（元注：在第二个链接的文章中禁用CSS样式可以显示内容） - Piskvor left the building

Answer 6

为答案列表添加一些可视化表示。 MySQL使用额外的间接层：二级索引记录指向主索引记录，而主索引本身保存在磁盘上的行位置。如果行偏移量更改，则只需更新主索引。

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

来源：链接

- Anurag Sharma

Answer 7

点击此链接查看有关索引的更多详细信息。

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

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)

- shahirnana

---

欢迎来到 Stack Overflow！我注意到你所有的答案都链接到你自己的视频。请注意，公开自我推广是不允许的。 - S.L. Barth

他希望推广他的视频。LOL - Ilyas karim

更不用说他链接的视频已经无法访问了... - goulashsoup

这篇文章根本没有回答问题。问题是关于索引如何工作的，而不是如何创建它们... - goulashsoup

Answer 8

我想要发表我的意见。虽然我不是一个数据库专家，但最近我阅读了一些相关的文章；这让我有足够的知识尝试用通俗易懂的语言来解释。以下是我个人的解释。

---

我理解索引就像是表的迷你镜像，很像一个关联数组。如果你使用匹配的键来填充它，那么你可以通过一次“命令”跳转到该行。

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

拥有索引的“缺点”是额外的存储空间（用于迷你镜像），以换取更快地查找内容的“优势”。

请注意（取决于您的数据库引擎），创建主键、外键或唯一键也会自动设置相应的索引。这个原理基本上就是这些键起作用的原因和方式。

- WoodrowShigeru

Answer 9

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本就越高。如果表中有涉及的列的索引，MySQL可以快速确定要在数据文件中寻找的位置，而无需查看所有数据。这比按顺序阅读每一行要快得多。

• 索引添加了一个带有搜索条件和指针列的数据结构

• 指针是具有其余信息的行的内存磁盘地址

• 索引数据结构已排序以优化查询效率

• 查询在索引中查找特定行；索引引用指针，该指针将找到其余信息。

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

- Kroonal

Answer 10

假设你有一本书，可能是一本小说，一本厚厚的书，很多内容需要阅读，因此很多单词。 现在，假设你带了两本字典，其中只包含小说中至少出现一次的单词。这两本字典中的所有单词都按照字母顺序排列。在假想字典A中，单词仅被打印一次，而在假想字典B中，单词则被打印与其在小说中出现的次数相同的次数。请记住，这两个字典中的单词都按字母顺序排列。 现在，当你阅读小说时遇到某些困难且需要查找该单词在这两个字典中的含义时，你会怎么做呢？当然，你会迅速跳转到那个单词并在几步内找到它的含义，而不是从开头开始查找小说中的每个单词，直到找到那个让你困扰的单词。

这是SQL中索引的工作原理。将字典A视为主索引，字典B视为键/次要索引，并通过查询/选择语句获取单词含义。 索引将有助于以非常快的速度获取数据。如果没有索引，您将不得不从头开始查找数据，这是一个不必要的费时昂贵的任务。

了解更多关于索引和类型的信息，请查看此处。

- saint_sharan