首页 文章

MySQL索引如何工作？

提问于
浏览 次
349

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

我知道，这是偏离主题的，但如果有人可以向我详细解释这一点，我将非常，非常感谢 .

mysql indexing

5 回答

  • 219

    基本上，表上的索引就像书中的索引（名称来自的位置）：

    让'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必须逐个浏览页面，直到找到主题（那是 full table scan ） . 另一方面，索引有一个关键字列表，所以你可以参考索引，看看在第113-120,231和354页上提到了 storage 然后你可以直接翻到这些页面而不进行搜索（这是一个搜索一个索引，有点快） .

    当然，索引的有用性取决于很多东西 - 一些例子，使用上面的明喻：

    • 如果你有一本关于数据库的书并且索引了"database"这个词，你在第1-59,61-290页和292到400页提到了'd see that it' . 在这种情况下，索引没有多大帮助，它可能会更快通过页面一个接一个（在数据库中，这是"poor selectivity"） .

    • 对于一本10页的书，制作一个索引是没有意义的，因为你可能会得到一个10页的书，前面有一个5页的索引，这只是愚蠢的 - 只需扫描10页并完成它 .

    • 索引也需要有用 - 通常没有必要对索引进行索引，例如每页的字母"L"的频率 .

    回复于
  • 2

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

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

    InnoDB和B树索引

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

    关于此索引类型的"problem"是您必须查询最左边的值才能使用索引 . 因此，如果索引有两列，例如last_name和first_name，则查询这些字段的顺序为 matters a lot .

    因此，如下表所示：

    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"

    因为现在，您正在比较索引中最右边字段的最右边部分 .

    哈希索引

    这是一种不同的索引类型，遗憾的是，只有内存后端支持 . 它's lightning fast but only useful for full lookups, which means that you can'用于 > ， < 或 LIKE 等操作 .

    由于它仅适用于内存后端，因此您可能不会经常使用它 . 我现在能想到的主要情况是你在内存中创建一个临时表，其中包含来自另一个select的一组结果，并使用哈希索引在此临时表中执行许多其他选择 .

    如果你有一个很大的 VARCHAR 字段，你可以在使用B-Tree时使用哈希索引，通过创建另一个列并在其上保存一个大值的哈希值 . 让's say you'在字段中存储一个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 字段 .

    总结

    您每次想要谈论优化时可能会考虑的一些事实：

    • 整数比较比字符串比较快 . 可以通过关于在 InnoDB 中模拟哈希索引的示例来说明它 .

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

    MySQL也有其他索引，但我认为B Tree one是有史以来最常用的，哈希值是一件好事，但你可以在_620106中找到其他的 .

    我强烈建议你阅读“高性能MySQL”一书，上面的答案肯定是基于其关于索引的章节 .

    回复于
  • 33

    基本上，索引是按顺序排序的所有键的映射 . 按顺序列表，然后不是检查每个键，它可以做这样的事情：

    1：进入列表中间 - 是高于还是低于我正在寻找的？

    2：如果更高，则进入中间和底部之间的中间位置，如果是低位，中位和顶部

    3：更高还是更低？再次跳到中间点等

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

    显然有复杂性，但这给了你基本的想法 .

    回复于
  • 451

    有关索引的详细信息，请参阅this视频

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

    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)
    回复于
  • 4

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

    他们的工作方式过于宽泛，无法涵盖一篇SO帖子 .

    Here是我见过的索引的最佳解释之一 . 不幸的是，它适用于SQL Server而不是MySQL . 我不确定两者有多相似......

    回复于

相关问题