MySQL 面试：唯一索引与普通索引的区别是什么？使用索引会有哪些优缺点？

1月 3 2024 mysql 22 分钟读完 (约 3296 字)

让我们深入探讨 MySQL 中的“唯一索引”和 “普通索引”的评论和评估。

感谢您阅读这篇文章。更多面试问题:
https://programmerscareer.com/zh-cn/software-interview-set/

主题：了解 MySQL 中的索引

在任何关系数据库管理系统中，如 MySQL，数据的高效访问是关键方面之一，特别是在处理大量数据时。我们所关心的效率是系统如何快速地定位并检索所需的数据。这就是索引发挥作用的地方。

你可以将索引比作书的索引。假设你想找到某个特定主题。你有两种选择：

你可以逐页扫描，直到找到主题。
你可以去索引，找到主题的页码，然后直接翻到那一页。

后者更快，是不是？在数据库世界中，逐页扫描被称为 全表扫描。如果你有 millions 行，这可能会花费很长时间。但是如果你有一个索引，MySQL 可以使用它来更快地定位数据——就像你使用书的索引来找到主题一样。

索引创建一个条目，并因此可以更快地检索数据。请记，然而，虽然索引加快了查询，但它们可能会慢下来写操作（INSERT、UPDATE、DELETE）的速度，因为每次写操作都要更新索引。因此，我们需要维持平衡并只在经常搜索的列上使用索引。

主题：MySQL 中的普通索引

现在我们对索引有了良好的理解并了解了它在 MySQL 中的角色，让我们来深入探讨一种特定的索引类型，即 普通索引（也称为 非唯一索引）。

普通索引（Non-unique Index）允许你通过数据库表的一个或多个列来加速查询过程。与唯一索引不同，普通索引不会对值的唯一性进行约束。换句话说，普通索引允许在索引列上重复的值。

例如，假设我们有一个 Students 表，其中包含列 ID、Name、Age 和 Address。当我们正在查询数据时，我们通常使用 WHERE 子句来过滤数据。例如：

1	`SELECT * FROM Students WHERE Age = 20`

在没有索引的情况下，MySQL 将需要浏览 Students 表的每一行，以找到 Age 等于 20 的行。这可能会耗时且不高效。如果我们为 Age 列创建一个普通索引，MySQL 可以使用这个索引来快速定位相关的行。

在 MySQL 中创建一个普通索引非常简单，你可以使用 CREATE INDEX 命令，然后提供索引的名称、表和列。

下面是如何在 Students 表上为 Age 列创建一个普通索引的例子：

1	CREATE INDEX age_index ON Students (Age);

请记，虽然普通索引可能会加快读操作，但它们也会占用存储空间并可能会慢下来写操作（INSERT、UPDATE、DELETE），因为它们需要更新每次写操作时。因此，它们应该被谨慎和策略地使用。

主题：MySQL中的唯一索引

我们已经有了关于正常索引的深入了解，现在是时候讨论MySQL中的唯一索引了。

唯一索引是一种索引，它强制约束，要求所有值在索引中都不相同。这意味着，唯一索引不允许在索引所在的列（或列组合）上出现重复值，使它们非常有用，当你想要防止某些字段的重复时。

例如，考虑一个名为Users 的表，其中每个用户都有一个唯一的电子邮件地址。在这种情况下，在电子邮件列上创建一个唯一索引将确保两个用户不能具有相同的电子邮件地址。

创建唯一索引的语法与创建正常索引的语法略有差异：

1	CREATE UNIQUE INDEX index_name ON table_name (column_name);

请替换 index_name 为您希望为索引命名的名称，table_name 为要在其上创建索引的表的名称，并将 column_name 替换为要在其上创建索引的列的名称。

例如，要在 Users 表上创建一个唯一索引，您可以使用以下语法：

1	CREATE UNIQUE INDEX email_index ON Users (Email);

每次在 Users 表中插入或更新一个电子邮件时，MySQL都会检查唯一索引，并如果发现另一行具有相同的电子邮件值，就不会允许更改。

请记住，唯一索引不仅可以帮助保持数据完整性，还可以帮助提高数据检索操作的性能。

主题：正常索引与唯一索引的差异

我们已经详细讨论了数据库中的索引，并讨论了两种特定类型的索引：正常索引（或非唯一索引）和唯一索引。两者都有不同的角色，了解差异是至关重要的。

唯一性：根据名称本身就可以看出差异——正常索引在列或列组合上允许重复值。相反，唯一索引不允许插入具有重复索引列值的新行。
用途：正常索引主要用于提高MySQL的操作效率。唯一索引，然而，具有双重目的。它们可以同时提高操作效率并维护数据一致性，因为它们会拒绝具有重复值的新行。
约束：当您在具有唯一索引的表中插入一行时，MySQL先检查是否会违反唯一性约束。如果是这样，MySQL就会拒绝更改并发出错误。与正常索引相比，MySQL不会执行这些检查。

了解何时使用哪种类型的索引是至关重要的。当您想要加速大型数据集上的查询时，正常索引就足够了。但是，如果您需要确保列中的每个值都是唯一的，则应使用唯一索引，尽管它会消耗更多的资源来强制唯一性约束。

主题：优化 SQL 查询使用索引

使用索引来提高数据库的性能并组织数据是使用 MySQL 索引的主要目标之一。当正确使用时，索引可以显著加快数据检索操作的速度。下面是一些提示，可帮助您优化 SQL 查询使用索引：

索引搜索字段：这似乎很简单，但值得重复。如果您经常在表中搜索特定字段，请考虑索引该字段。这可能会大大提高数据库的性能。
考虑索引大小：索引的数据量越小，速度越快。因此，具有较小数据类型的索引列通常会更快。例如，INT 比 VARCHAR 更快，VARCHAR 比 TEXT 更快。
限制写操作上的索引：索引可能会慢下写操作（例如 INSERT、UPDATE 和 DELETE 语句），因为每次修改数据时，索引也需要更新。如果表经常更新，请考虑减少索引的数量。
复合索引：它们由多个列组成，并可以加速在 WHERE 子句中过滤多列时的数据检索。注意，它们按左侧前缀的顺序工作。这意味着索引列的顺序很重要。
使用 EXPLAIN 计划：MySQL 的 EXPLAIN 语句可以显示 MySQL 优化器如何执行您的查询，帮助您了解数据库是否能使用索引，并允许您进一步优化您的查询。

这就完成了关于如何优化 SQL 查询使用索引的课程。

主题：索引的常见陷阱

MySQL 中的索引是强大的工具，可以显著加快查询的速度。然而，您应该注意以下几点，在使用它们时要小心。

过多的索引：有过多的索引可能会反效果。每个索引都会增加 MySQL 更新和管理这些索引所花费的时间。这可能会慢下写操作。因此，要确保只有必要的索引。
不了解卡尔数：卡尔数是索引中的唯一值数量。如果卡尔数低（意味着有许多重复值），索引可能不会非常有效。您应该关注索引的卡尔数并考虑是否应该使用另一列作为索引。
索引错误的列：索引应该基于经常搜索或排序的列。索引错误的列可能会导致不高效的查询。
忽略查询执行计划：MySQL 的 EXPLAIN 语句可以提供有价值的信息，帮助您了解如何执行您的查询并识别使用的索引。忽略这些信息可能会导致不高效的索引或优化的机会被错过。
使用大 VARCHAR 或 TEXT 索引：大 VARCHAR 或 TEXT 列的索引可能会消耗大量内存并慢下查询。在这种情况下，索引前缀（INDEX(column(10)))可能会有用。

记住，有效的索引的秘诀在于了解数据并了解应用如何查询它。有适当数量的良好选择的索引可以使数据库的性能显著提高。

主题：回顾和评估

我们已经学习了 MySQL 索引的许多内容，包括它们是什么、它们的类型（普通和唯一索引）、它们是如何用于查询优化的、以及一些常见的误解和陷阱。

现在是时候进行快速评估了。这将帮助强化你的学习并帮助突出任何我们可能需要重新访问的地方。

例题：

为了测试你的理解，让我们通过一个例题来进行。

我们有一个名为 students 的 MySQL 数据库表，其结构如下：

1	\| id (INT) \| name (VARCHAR) \| class (VARCHAR) \| age (INT) \|

你经常需要查找来自特定 class 的学生。如何优化这个查询？

解决方案：

为了优化这个查询，我们可以为 class 列添加一个索引。由于我们经常搜索这个字段，为其添加索引可以显著增加查询的性能。

下面是创建索引的 SQL 语句：

1	CREATE INDEX idx_students_class ON students (class);

现在，让我们测试你的理解：

简单问题（难度：3/10）：要为 age 列添加索引，你将使用哪个 SQL 语句？

1	CREATE INDEX idx_students_age ON students(age);

复杂熟问题（难度：6/10）：在添加索引之前，你应该考虑哪些因素？

列的卡尔伴：高卡尔伴列（具有许多独特值的列）最适合索引化。
应用程序的读写比率：如果应用程序执行更多的读操作，索引化是有益的。但是，如果应用程序执行更多的写操作（插入、更新、删除），索引的维护成本可能会超过好处。
列的数据类型：索引小数据类型列的速度更快。

复杂未知问题（难度：9/10）：数据库表 students 还有一个名为 registration_date 的列（日期类型），并且你正在运行查询来找到注册于特定年份的学生。你可以使用哪种索引来优化这个查询，并且怎样创建它？

在这种情况下，你可以创建一个函数或表达式的索引，在 MySQL 8.0 或更高版本中称为函数索引。

要在 MySQL 8.0 或更高版本中创建函数索引，可以使用以下语句：

1	CREATE INDEX idx_students_registration_year ON students((YEAR(registration_date));

这样，MySQL 就可以直接将年份映射到索引中的行，从而加快查询的速度。

请注意，在 MySQL 8.0 或更高版本中创建函数或表达式索引是受支持的。如果你使用的是早期版本的 MySQL，你将需要添加一个单独的列来存储年份，然后索引该列。

English post: https://programmerscareer.com/mysql-interview11/
作者：Wesley Wei – Twitter Wesley Wei – Medium
注意：本文为作者原创，转载请注明出处。

#interview