在 MySQL 中创建索引：MySQL 索引教程 [2022]

已发表: 2021-01-01

数据科学家或程序员将不得不处理大量数据，并且需要能够有效地处理它以更快地执行。他们还需要了解数据的组织方式以及轻松访问所需特定数据的最快方法。

由于 MySQL 是一个关系数据库管理系统，在 Web 数据库、电子商务应用程序、社交媒体等各个领域都有应用。数据科学家需要了解与 MySQL 相关的所有技术，其中之一就是索引。本文介绍了如何在 MySQL 中创建索引。

什么是索引？

在 MySQL 中，索引用于以有组织的顺序方式对数据进行排序，并有效快速地检索数据。它们是通过使用一个或多个用于过滤数据的列创建的。索引是一种表，它保留主表或索引字段以及指向主表中每条记录的指针。

为了更好地理解，可以使用书籍索引作为示例。不是通过浏览书中的所有页面来查找页面，而是可以检查书籍索引以找到正在查找的页面。

在大多数数据库系统中，需要高性能和快速的性能。许多企业在硬件上投入巨资，以实现更快的数据检索和操作。但是通过优化数据库，可以降低成本。

响应时间通常较长，因为记录是随机存储的，搜索查询必须通过循环遍历整个存储的记录来找到所需的数据。索引使这种搜索更容易和更快。

阅读：重命名 SQL 中的列名

创建索引

您可以使用以下基本语法在 MySQL 中为表创建索引。

CREATE INDEX index_name table_name (column_name);

您还可以在表上创建唯一索引。这意味着名称是唯一的，任何两行都不能有相同的名称。可以为此使用以下语法。

CREATE UNIQUE INDEX index_name ON table_name (column_name);

如果为列创建了二级索引，MySQL 会将值存储在单独的数据结构中，例如 B-Tree 和 Hash。在 MySQL 中，如果列是字符串列，您可以使用以下语法为字符串列的列值的前导部分创建索引：

列名（长度）

要在创建表时创建列前缀键部分，可以使用以下语法：

创建表表名（

列列表，

索引（列名（长度））

);

并向现有表添加索引：

CREATE INDEX index_name ON table_name (column_name(length));

如果是非二进制字符串类型（如 CHAR、VARCHAR 和 TEXT），此处的长度是字符数；如果是二进制字符串类型（如 BINARY、VARBINARY 和 BLOB），则此处的长度是字节数。您还可以为 MySQL 中的 CHAR、VARCHAR、BINARY 和 VARBINARY 列创建列前缀键部分。如果您已为 BLOB 和 TEXT 列创建索引，则必须指定列前缀键部分。

对于仅 InnoDB 和 MyISAM 表支持的 FULLTEXT 索引，它们只能包含 CHAR、VARCHAR 和 TEXT 列。它不支持列前缀索引，如果指定了任何前缀长度，则会被忽略。

必读： SQL 项目理念

更改表以添加或删除索引

要将索引添加到表中，可以使用四种类型的语句：

添加主键：

ALTER TABLE table_name ADD PRIMARY KEY (column_list);

这里的索引值应该是唯一的，不能为 NULL。

对于具有唯一值的索引：

ALTER TABLE table_name 添加唯一的 index_name (column_list);

这些值应该是唯一的，但 NULL 除外，它可以出现多次。

添加普通索引：

ALTER TABLE table_name ADD INDEX index_name (column_list);

这里的值可以出现不止一次。

要创建特殊的 FULLTEXT 索引：

ALTER TABLE table_name ADD FULLTEXT index_name (column_list);

FULLTEXT 索引可用于文本搜索。

在现有表中

要在任何现有表中添加索引，可以使用以下语法：

ALTER TABLE table_name ADD INDEX (index_name);

要删除表中的索引，请使用 DROP INDEX 语句：

ALTER TABLE table_name DROP INDEX (index_name);

显示索引信息

要列出与任何表关联的所有索引，请使用 SHOW INDEX 命令。

显示来自 table_name\G 的索引

'\G' 用于以垂直格式创建列表，这避免了长行环绕。

另请阅读： SQL 面试问答

降序索引

当索引为降序索引时，InnoDB 可以按降序存储条目。因此，当查询中请求降序时，优化器会选择此索引。这对于使用 ORDER BY 子句的查询更有效。这些仅由 InnoDB 存储引擎支持。

创建或添加降序索引的语法类似于上面使用的 alter 或 create 语法。

ALTER TABLE table_name ADD index_name (col1_name desc, col2_name asc);

CREATE INDEX index_name ON table_name (col1_name desc, col2_name asc);

隐形索引

要将索引标记为查询优化器不可用，可以使用不可见索引。当与索引关联的列中的数据发生变化时，MySQL 会更新不可见的索引。索引默认是可见的，要使其不可见，您必须在创建时或使用 ALTER TABLE 命令显式声明可见性。

要创建不可见索引，使用以下语法：

CREATE INDEX index_name ON table_name (c1, c2…) INVISIBLE;

要更改现有索引的可见性，请使用以下语法：

ALTER TABLE table_name ALTER INDEX index_name [VISIBLE | 无形的];

报名参加世界顶尖大学的在线软件开发课程。 获得行政 PG 课程、高级证书课程或硕士课程，以加快您的职业生涯。

结论

使用索引可以提高 MySQL 搜索查询的性能。这些可以在创建表时定义，或者如果表已经创建，则可以稍后添加。

本文简要介绍了索引是什么以及使用它的原因。下一节将解释如何在 MySQL 中创建索引，如何将其添加到现有表中以及如何创建唯一索引。

还描述了更改表和创建或删除索引的不同命令以及显示索引信息。还简要解释了降序和不可见索引。

总而言之，在本文中提到的所有数据库上学习和实践将增加您的经验。浏览与它相关的课程概述、学习和工作机会——像 upGrad 这样的平台提供由 IIIT-B 等知名机构在 Executive PG Program Full Stack Development 中设计的功能强大的课程。

为未来的职业做准备

立即申请软件工程硕士