MySQL索引是如何工作的？

19 浏览
0 Comments

MySQL索引是如何工作的？

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

我知道这是离题了，但如果有人能够详细解释给我听，我会非常非常感激。

admin 更改状态以发布 2023年5月20日
0

2 答案

0 Comments

首先，您必须了解索引是避免扫描整个表以获取您要查找的结果的一种方法。

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

InnoDB和B+Tree索引

对于InnoDB，最常见的索引类型是基于B+Tree的索引，它以排序顺序存储元素。此外，您无须访问实际表格即可获得索引值，这使得查询返回得更快。

这种索引类型的“问题”是必须查询左侧值以使用索引。因此，如果您的索引有两列，例如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的结果集，并在此临时表格中使用哈希索引执行许多其他select操作。

如果您有一个大的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+树是最常用的索引，哈希是值得了解的好东西，但您可以在MySQL文档中找到其他索引。

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

0
0 Comments

基本上，一个表的索引就像一本书中的索引（那也是这个名字的来源）：

假设你有一本关于数据库的书，你想找一些关于存储的信息。如果没有索引（假设没有其他的辅助手段，比如目录），你就只能一页一页地翻，直到找到主题（这就是“全表扫描”）。

另一方面，一个索引有一个关键字列表，所以你可以查看索引，看到“存储”出现在第113到120页、231页和354页。然后你可以直接翻到这些页面，而不用搜索（这是一个使用索引的搜索，比较快）。

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

  • 如果你有一本数据库书，并索引“数据库”这个词，你会发现它出现在第1-59页，61-290页和292-400页。在这种情况下，索引并没有多大的帮助，一页一页地翻可能更快（在数据库中，这叫“选择性差”）。
  • 对于一本只有10页的书，制作索引毫无意义，因为你可能最终得到一个前缀为5页索引的10页书，这简直太傻了-直接扫描这10页就行了。
  • 索引也需要有用 - 没有必要索引例如每页字母“L”的频率。
0