mysql - 了解 MySQL 中的索引

Question

我想了解 MySQL 中的索引。我知道在表中创建的索引可以加快执行查询的速度，并且可以减慢行的插入和更新速度。

创建索引时，我在名为 authors 的表上使用了此查询，该表包含 (AuthorNum, AuthorFName, AuthorLName, ...)

Create index Index_1 on Authors ([What to put here]);

我知道我必须输入列名，但是哪个？

是否必须在用户查询表时将要比较的列名放在 Where 语句中？

Answer 1

索引剖析

索引是数据库中一种独特的数据结构，是数据冗余。它的主要目的是通过独立于物理排序的逻辑排序来提供索引数据的有序表示。我们使用双向链表和称为平衡搜索树(B 树)的树结构来执行此操作。 B 树很好，因为它们保持数据排序并允许在对数时间内进行搜索、访问、插入和删除。由于双向链表，我们可以轻松地根据需要在索引上向后或向前移动以进行各种查询。插入变得简单，因为我们只需要重新排列指向不同数据片段的指针。数据库使用这些双向链表连接叶子节点(通常在B+树或B-树中)，每个叶子节点存储在一个页面中，并建立叶子节点之间的逻辑顺序。 UPDATE 或 INSERT 等操作变得更慢，因为它们实际上是文件系统中的两个写入操作(一个用于表数据，一个用于索引数据)。

用 WHERE 定义一个最优索引

要定义最佳索引，您不仅必须了解索引的工作原理，还必须了解应用程序如何查询数据。例如，您必须知道出现在 WHERE 子句中的列组合。

LAST_NAME 和 FIRST_NAME 列查询的一个常见限制是区分大小写。例如，与其像 Hotinger 那样进行精确搜索，我们更愿意匹配所有结果，例如 HoTingEr 等等。这在 WHERE 子句中很容易做到:我们只需说 WHERE UPPER(LAST_NAME) = UPPER('Hotinger')

但是，如果我们定义一个 LAST_NAME 的索引并查询，它实际上会运行全表扫描，因为查询不是在 LAST_NAME 上，而是在 UPPER 上(姓氏)。从数据库的角度来看，这是完全不同的。因此，在这种情况下，您应该改为在 UPPER(LAST_NAME) 上定义索引。

索引不一定非要针对一列。例如，如果主键是复合键(由多列组成)，它将创建一个串联索引，也称为组合索引。请注意，连接索引的顺序对其可用性和可伸缩性有显着的影响，因此必须谨慎选择。基本上，排序应该与 WHERE 子句中的排序方式相匹配。

用 LIKE 定义一个最优索引

通配符的位置有很大的不同。 LIKE 子句在树遍历过程中只使用通配符之前的字符；其余的不缩小扫描的索引范围。 LIKE 子句的前缀越有选择性，扫描的索引就越窄。这使得索引查找更快。作为提示，避免 LIKE 以通配符开头的子句，例如 "%OTINGER%" 对于全文搜索，MySQL 提供了 MATCH 和 AGAINST 关键字。从 MySQL 5.6 开始，您可以拥有全文索引。看Full-Text Search Functions从 MySQL 获取关于索引这些结果的更深入的讨论。