MySQL 索引教程 – 创建、添加和删除

作者： Marcus Allen Marcus Allen
时间 - 权威教程 更新于

什么是索引？

MySQL 中的索引以有组织、顺序化的方式对数据进行排序。它们在将用于过滤数据的列上创建。可以将索引想象成一本按字母顺序排序的列表。查找按字母顺序排序的姓名比查找未排序的姓名要容易。

对经常更新的表使用索引可能会导致性能下降。这是因为每次向表中添加或更新数据时，MySQL 都会创建一个新的索引块。通常，索引应用于数据不经常更改但经常用于 SELECT 搜索查询的表。

使用索引有什么用？

没有人喜欢慢速的系统。在几乎所有数据库系统中，高系统性能都至关重要。大多数企业在硬件上投入巨资，以加快数据检索和处理速度。但是，企业在硬件投资上存在限制。优化数据库是一种更便宜、更好的解决方案。

MySQL Index

响应时间缓慢通常是由于记录随机存储在数据库表中。搜索查询必须逐一循环遍历所有随机存储的记录才能找到所需数据。这会导致在从大型表中检索数据时数据库性能不佳。因此，使用索引对数据进行排序，以便于搜索。

语法：创建索引

索引可以定义在 2 种方式

  1. 在创建表时
  2. 在创建表之后

示例

对于我们的 myflixdb，我们预计将对数据库进行大量关于全名的搜索。

我们将把“full_names”列添加到名为“members_indexed”的新表中的索引。

下面的脚本将帮助我们实现这一点。

CREATE TABLE `members_indexed` (
  `membership_number` int(11) NOT NULL AUTO_INCREMENT,
  `full_names` varchar(150) DEFAULT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `contact_number` varchar(75) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`membership_number`),INDEX(full_names)
) ENGINE=InnoDB;

在 MySQL workbench 中针对“myflixdb”执行上述 SQL 脚本。

MySQL Create Index

刷新 myflixdb 会显示新创建的名为 members_indexed 的表。

“注意” members_indexed 表的索引节点中有“full_names”。

随着成员库的扩展和记录数量的增加，使用 WHERE 和 ORDER BY 子句的 members_indexed 表上的搜索查询将比在没有定义索引的 members 表上执行的查询快得多。

添加索引基本语法

上面的示例在定义数据库表时创建了索引。假设我们已经定义了一个表，并且对其进行搜索查询的速度非常慢。它们需要很长时间才能返回结果。在调查了问题之后，我们发现通过在 WHERE 子句中最常用的列上创建索引，可以大大提高系统性能。

我们可以使用以下查询来添加索引

CREATE INDEX id_index ON table_name(column_name);

假设对 movies 表的搜索查询速度非常慢，并且我们想在“movie title”上使用索引来加速查询，我们可以使用以下脚本来实现。

CREATE INDEX `title_index` ON `movies`(`title`);

执行上述查询将在 movies 表的 title 字段上创建索引。

这意味着所有使用“title”对 movies 表进行的搜索查询都将更快。

但是，对 movies 表中其他字段的搜索查询仍然比基于索引字段的查询慢。

注意：如有必要，您可以根据您打算在数据库搜索引擎中使用的字段，在多个列上创建索引。

如果要查看在特定表上定义的索引，可以使用以下脚本进行操作。

SHOW INDEXES FROM table_name;

现在让我们看一下 myflixdb 中的 movies 表上定义的所有索引。

SHOW INDEXES FROM `movies`;

在 MySQL workbench 中针对 myflixdb 执行上述脚本，我们可以看到创建的索引结果。

注意：表上的主键和外键已由 MySQL 索引。每个索引都有其唯一的名称，并显示了它定义的列。

语法：删除索引

DROP 命令用于删除表中已定义的索引。

有时，您可能已经在经常更新的表上定义了索引。您可能希望删除此类表上的索引，以提高 UPDATE 和 INSERT 查询的性能。用于删除表上索引的基本语法如下。

DROP INDEX `index_id` ON `table_name`;

现在让我们看一个实际的例子。

DROP INDEX ` full_names` ON `members_indexed`;

执行上述命令将从 members_indexed 表中删除 ID 为 `full_names` 的索引。

摘要

  • 索引在大大提高 MySQL 搜索查询性能方面非常强大。
  • 可以在创建表时定义索引，或者在表创建后稍后添加。
  • 您可以在表上为多个列定义索引。
  • SHOW INDEX FROM table_name 用于显示表中定义的索引。
  • DROP 命令用于删除给定表上已定义的索引。