Py学习  »  DATABASE

MySQL索引是如何工作的?

good_evening • 7 年前 • 2403 次点击  

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

我知道,这是离题的,但如果有人能详细地向我解释,我会非常非常感谢。

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/31417
文章 [ 5 ]  |  最新文章 7 年前
Abe Miessler
Reply   •   1 楼
Abe Miessler    16 年前

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

他们的工作方式太宽泛了,一篇这样的文章无法涵盖。

Here 是我见过的最好的索引解释之一。不幸的是,它是针对SQL Server而不是MySQL的。我不知道这两者有多相似…

shahirnana
Reply   •   2 楼
shahirnana    9 年前

采取 this 有关索引的详细信息,请观看视频

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

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

可以使用一列或多列创建索引。例如,我们可以在 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)
Joshua
Reply   •   3 楼
Joshua    16 年前

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

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

2:如果高,转到中间和底部之间的中间点,如果低,中间和顶部

3:是高还是低?再跳到中间点,等等。

使用该逻辑,您可以通过大约7个步骤在排序列表中找到一个元素,而不是检查每个项目。

显然有很多复杂的东西,但这给了你基本的想法。

clarete
Reply   •   4 楼
clarete    9 年前

您必须知道的第一件事是,索引是一种避免扫描整个表以获得所需结果的方法。

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

InnoDB和B+树索引

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

关于这个索引类型的“问题”是您必须查询最左边的值才能使用索引。所以,如果索引有两列,比如姓和名,那么查询这些字段的顺序 事情很多 .

因此,给出下表:

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的一组结果创建一个临时表,并使用哈希索引在这个临时表中执行许多其他选择。

如果你有一个大 VARCHAR 字段,您可以在使用B树时“模拟”哈希索引的使用,方法是创建另一列并在其上保存大值的哈希。假设您在一个字段中存储一个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";

即使碰撞次数很高,仍然值得散列,因为您将只对重复散列执行第二次比较(字符串1)。

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

包扎

每次你想谈论优化时,你都会考虑到一些事实:

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

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

MySQL也有其他索引,但我认为B+树索引是有史以来使用最多的,哈希索引是一个好消息,但是您可以在 MySQL documentation .

我强烈建议您阅读“高性能MySQL”这本书,上面的答案肯定是基于它关于索引的章节。

Piskvor
Reply   •   5 楼
Piskvor    16 年前

基本上,表上的索引工作起来就像书中的索引(这就是名称的来源):

假设你有一本关于数据库的书,你想找到一些关于存储的信息。如果没有索引(假设没有其他帮助,比如目录),你就必须逐个浏览页面,直到找到主题(这是一个 full table scan ) 另一方面,索引有一个关键字列表,所以您可以参考索引并看到它。 storage 参见第113-120231页和第354页。然后您可以直接翻到这些页面,而不需要搜索(这是一个带有索引的搜索,速度更快)。

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

  • 如果你有一本关于数据库的书,并且为“数据库”这个词编制了索引,你会在1-59、61-290和292-400页上看到它。在这种情况下,索引没有多大帮助,而且逐个浏览页面可能会更快(在数据库中,这是“较差的选择性”)。
  • 对于一本10页的书来说,做一个索引是没有意义的,因为你最终可能会得到一本10页的书,前面加上一个5页的索引,这很愚蠢——只需扫描10页就可以了。
  • 索引也需要有用-通常没有索引点,例如每页字母“L”的频率。