理解MySQL中的索引：第二部分

Hudson译原文

这篇博文是MySQL索引系列博客的第二部分。在系列的第一部分，我们介绍了很多内容，包括它们是什么、它们做什么、它们的类型是什么、如何选择最佳数据类型以及索引的MySQL字符集，还介绍了使用索引的优点和缺点；如何选择最佳索引，如何提高查询性能并确保MySQL使用了索引，应该有多少索引。我们还讨论了与存储引擎相关的一些注意事项。本篇博文将更详细地介绍第一部分中讨论的一些内容。我们将从MySQL中索引和存储引擎之间的关联开始。

MySQL中的索引和存储引擎

正如我们在前一篇博文中提到的，如果在MySQL中使用某些存储引擎，索引和其他东西可能会受到一些限制。下面是其中的一些 —— 我们现在将定义其中的一些是什么（其中一些已经在博客系列的第一部分中介绍过了，所以如果我们遗漏了一些东西，可能就在这里），然后用更深入的分析来涵盖它们：

根据MySQL文档，每个存储引擎定义了最大索引数、最大键长度和最大索引长度。正如我们前一篇博文中提到的那样，每个MyISAM和InnoDB表的最大索引数为64，两个存储引擎中每个索引的最大列数为16，InnoDB的最大键长度为3500字节，MyISAM的最大键长度为1000字节。
不能使用CREATE INDEX创建PRIMARY KEY–请改用ALTER TABLE。
BLOB和TEXT类型的列只能在InnoDB、MyISAM和BLACKHOLE存储引擎的表上创建索引。
如果只索引列的前缀，请记住前缀支持及其长度也取决于存储引擎。对于使用REDUNDANT或COMPACT行格式的InnoDB表，前缀最长可达767字节，但对于DYNAMIC或COMPRESSED行格式，前缀长度增加到3072字节。对于MyISAM表，前缀长度限制为1000字节。NDB存储引擎根本不支持前缀。
如果启用了严格的SQL模式，并且索引前缀超过了最大列数据类型大小，CREATE INDEX 将抛出错误。如果未启用严格SQL模式，CREATE INDEX将生成警告。如果创建了UNIQUE INDEX，则会发生错误。
通常，MySQL只允许在给定的表上创建最多16个索引。
如果使用的是PRIMARY KEY索引，则每个表只能有一个主键。FULLTEXT、UNIQUE INDEX和INDEX没有此限制。
如果使用的是FULLTEXT索引，请记住，它们只能用于InnoDB或MyISAM存储引擎以及CHAR、VARCHAR或TEXT列。还请记住，MySQL仅在使用MATCH() AGAINST()子句时使用FULLTEXT索引，如果您愿意，实际上可以在同一列上同时使用索引和全文索引，并且FULLTXT索引具有各自特定于所使用的存储引擎的停用词字集。
如果使用以通配符开头的LIKE查询，B-Tree索引可能很有用，但仅在某些情况下。

如果您想了解MySQL中的索引是如何工作的，那么了解这些索引限制应该会很有用。但更重要的是，您必须确认您的索引实际上被MySQL使用。我们在本系列的第一部分（“如何选择要使用的最佳索引？”）中简要介绍了这一点，但我们还没有告诉您如何验证您的索引是否被MySQL实际使用。为此，请使用EXPLAIN验证它们的用法–当EXPLAIN与可解释语句一起使用时，MySQL会显示优化器关于语句执行计划的信息。

主键注意事项

与MySQL中的PRIMARY KEY索引相关的一些基本注意事项包括，它们主要用于唯一标识表中的记录，并且经常与AUTO_INCREMENT 子句一起使用，这意味着如果您创建ID字段，它们可能非常有用。PRIMARY KEY字段必须包含唯一值，并且不能包含空值。

匹配列前缀

索引还可以匹配列前缀。如果您的列是字符串列，并且您认为在整个列上添加索引可能会占用大量磁盘空间，那么这种索引方法非常有用。索引可以与列前缀匹配，如下所示：

ALTER TABLE demo_table ADD INDEX index_name(column_name(length));

上述查询将仅为定义的列前缀在名为column_name的列上添加索引index_name。要选择足够的长度进行索引，请确保使用前缀最大化列中值的唯一性：查找表中的行数并估算不同的前缀长度，直到达到所需的行唯一性。

MySQL中的FULLTEXT索引

MySQL中的FULLTEXT索引完全不同。它们有许多独特的限制（例如，InnoDB有一个由36个单词组成的停用词列表，而MyISAM停用词表由143个单词组成），它们也有独特的搜索模式。其中一些包含自然语言模式（要激活这种搜索模式，请运行不带修饰符的FULLTEXT搜索查询），您还可以扩展搜索（为此，请使用WITH QUERY EXPANSION修饰符-这样的搜索模式会执行两次搜索，但当搜索第二次运行时，它会包含第一次搜索中的一些最相关的记录-通常在用户有隐含知识时使用），要使用布尔运算符进行搜索，请使用IN BOOLEAN MODE修饰符。仅当搜索查询由InnoDB的至少三个字符和MyISAM的至少四个字符组成时，才会使用FULLTEXT索引。

使用带通配符的B树索引

如果您正在构建类似于搜索引擎的东西，索引也经常使用。为此，您经常希望只搜索值的一部分并返回结果——这里是通配符的作用。下面是一个使用通配符的简单查询，它使用 LIKE 和 % 符号表示文本后面的“任何内容”。该查询将搜索以单词“search”开头并在其后面有任何内容的结果：

SELECT * FROM … WHERE demo_column LIKE ‘search%’;

下面的查询将搜索以任何内容开头、包含单词“search”并在其后包含任何内容的结果：

SELECT * FROM … WHERE demo_column LIKE ‘%search%’;

但这里有一个问题——上面的查询不会使用索引。为什么？因为它在自己的开头有一个通配符，而MySQL无法确定列需要以什么开头。这就是为什么我们说通配符索引有自己的位置，但只有在特定的场景中，也就是说，在搜索查询开始位置没有通配符的场景

小结

在这篇博文中，我们介绍了MySQL索引的一些限制和优点，还简单介绍了ClusterControl如何帮助您实现数据库性能目标。请记住MySQL索引确实有自己的位置——要充分利用它们，了解它们如何与存储引擎交互，它们的优点和局限性，如何以及何时使某些类型的索引，并明智地进行选择。本系列第三部分，我们还将对MySQL中的索引进行的深入探讨。