MySQL 面试：聚簇索引和非聚簇索引有什么区别？

你曾被提过类似问题在面试中问过吗？或者将来会遇到，让我们一起探索和掌握它！

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

主题：1.1 聚集索引和非聚集索引的介绍

了解数据存储和检索的方式对数据库的性能有着重要的影响。特别是了解 MySQL 是如何使用索引的非常重要。这就是聚集索引和非聚集索引的概念发挥作用的地方。

索引是数据库的一个重要组成部分。它们是查找表，数据库引擎使用它们来加速数据检索，就像书的索引一样，它可以帮助你快速定位信息，而不必阅读每一页。

现在，让我们详细地讨论聚集和非聚集索引：

聚集索引：

就像名字所表明的那样，聚集索引决定了表中数据的物理排列顺序。为了更好地理解这一点，请考虑聚集索引是字典。在字典中，单词不是随机排列的，而是按字母顺序排列，这样你就可以快速跳到特定的字母部分并找到单词。在数据库的上下文中，这个“单词”是数据行。MySQL 就是按聚集索引的顺序组织数据行，以加快检索。请注意，表中只能有一个聚集索引。

非聚集索引：

非聚集索引不决定表中数据的物理排列顺序。然而，它们保存了数据的“指针”。为了更好地理解，如果聚集索引是字典，那么非聚集索引就是书的索引。书的索引指向包含信息的页面，但它们本身不包含信息。这意味着数据库引擎必须执行额外的工作，以“去”数据行，与聚集索引相比，非聚集索引的数据检索可能会慢一些。然而，你可以有多个非聚集索引，这可能对数据检索的多种场景有所帮助。

主题：1.2 聚集索引详解

聚集索引与数据的物理存储有关。当你为表创建一个聚集索引时，表中的行按索引键的顺序存储在磁盘上。表中只能有一个聚集索引，并且如果你没有显式定义一个聚集索引，SQL Server 将自动为你创建一个。这被称为堆。

聚集索引的结构也被称为 B+ 树结构。堆栈树的根节点位于树的顶部，然后分支出多个叶节点位于底部。这些叶节点包含数据行在索引的顺序中。

主键约束自动创建一个聚集索引，但在某些情况下，你可能希望手动创建一个聚集索引，这取决于你的要求。例如，如果你有一个员工数据表并经常按员工聘用日期进行查询，则可能会为聘用日期列创建一个聚集索引，以加快这些查询。

此外，在表中具有聚集索引的记录更新时，数据库可能会更慢，这是因为当记录更新在表中具有聚集索引时，数据库可能需要物理移动整行，以维护排序顺序。

主题：1.3 非聚集索引的介绍

现在我们已经对聚集索引有了深入的理解，了解它是如何排列和存储数据的。但是，并不总是想要根据单个聚集索引来检索数据。这就是非聚集索引的作用所在。

非聚集索引与聚集索引有着明显的区别。首先，创建非聚集索引不会重新排列表中数据的物理顺序。相反，它会创建一个独立的数据库对象，其中包含一个排序的列表，指向表中的数据。

为了说明这一点，请考虑一本书——相对于浏览整本书以找到特定主题，你通常会转到书的索引，是不是？它会直接将你导向包含指定主题的页面。这种快速导航就是非聚集索引所执行的功能！

非聚集索引的架构与聚集索引相似——B-树数据结构中的根节点、中间级节点和叶节点。然而，非聚集索引的叶节点仅包含索引列和指向数据表中相应行的指针。你可以为单个表创建多个非聚集索引，每个索引都为特定查询提供了加速。

在 MySQL 中，非聚集索引就是所有的辅助索引，其中每个索引都包含主键列的副本，用于指向搜索键匹配的行。

主题：1.4 聚集和非聚集索引的差异

现在我们已经对聚集和非聚集索引有了深入的理解，让我们来明确它们之间的主要差异：

1. 数据的排列顺序：聚集索引决定了表中数据的物理排列顺序。另一方面，非聚集索引不会改变数据记录的存储方式，但会创建一个数据库对象，该对象指向原始记录。
2. 索引数量：表中只能有一个聚集索引，但是可以有多个非聚集索引。请记，越多的索引，就需要越多的磁盘空间。
3. 数据检索速度：聚集索引可能会比非聚集索引更快地检索数据，但这并不总是如此。如果非聚集索引覆盖了查询（即，查询的数据可以从索引的叶节点中服务），它可能会更快地检索数据，尽管它需要一些额外的跳转。
4. 更新性能：聚集索引可能会降低更新的性能，而非聚集索引通常不会影响性能。
5. 存储空间：由于非聚集索引是与表数据分开存储的，因此它需要额外的存储空间。每个非聚集索引是一个独立的磁盘结构，其中包含一个排序的列表，其中包含列值，而聚集索引是表数据本身并形成了索引的最低级别。

在数据库的大图景中——数据检索速度、存储效率、更新速度等等——所有这些因素都依赖于良好的索引。清楚地了解何时和为什么使用聚集和非聚集索引可以帮助您优化数据库性能。

主题：1.5 选择正确的索引

恭喜！现在我们知道什么是聚集索引和非聚集索引，并了解了它们之间的主要差异，让我们深入探讨选择正确的索引进行性能优化。

在 MySQL 中选择正确的索引来进行性能优化，主要取决于了解将要执行的查询。它不仅仅是选择聚集或非聚集索引，还包括了列和其卡inality 的理解。

下面是一些关键点来帮助你决定：

1. 使用聚集索引来处理宽列查询：由于聚集索引本身就是表数据，因此对宽列查询来说非常有用，因为它可以减少读取的数量。
2. 使用非聚集索引来处理特定列查询：非聚集索引在需要检索较小的列集时非常有用。在这种情况下，为这些列创建非聚集索引可能会有好处。
3. 高卡inality列：当列具有高卡inality（每行都是唯一的）时，使用它作为聚集索引可能会导致更快的查找。
4. 低卡inality列：对于具有低卡inality（许多行共享相同值）的列，使用非聚集索引通常更有效。
5. 数据修改操作：如果应用程序包含频繁的插入、更新和删除操作，非聚集索引可能是更好的选择，因为它们不会影响磁盘上的数据排序。
6. 空间考虑：由于非聚集索引是独立的磁盘结构，它们会消耗额外的存储空间。如果存储空间是一个限制，聚集索引可能是更好的选择，虽然在某些情况下，它可能会影响速度。

记住，最佳策略总是取决于特定的工作负载。它是必要的进行持续的监控和分析，并根据性能进行索引策略的调整。

主题：1.6 示例和用例

好的！你已经做得很好了。为了巩固理解，让我们来看看一些实际的例子和用例。

开始时，让我们考虑一个基本的例子，假设你直接管理一个在线书店。你有一个名为 Books 的表，其中包含以下列：BookID、Title、Author、Genre、Price 和 PublicationDate。

1. 使用聚集索引：假设客户经常根据 BookID 在您的商店中搜索书籍。为了提高这些常见的拉取请求的速度，您可以使用 BookID 列上的聚集索引。由于聚集索引确定数据在表中的物理排序，行查找可能会显著加快。
2. 使用非聚集索引：如果客户经常根据 Genre 或 Author 搜索书籍，则可能有利于创建非聚集索引在这些列上。正如我们所了解的，非聚集索引特别有用当你需要检索较小的列集时。

用例：假设您的数据库具有名为 Customers 的表，其中包含数百万条记录，并经常需要检索客户信息根据 CustomerID。在这种情况下，使用 CustomerID 列上的聚集索引可以显著加快这些查找。然而，如果业务需求要求您根据 LastName 和 ZipCode 检索记录，则非聚集索引在 LastName 和 ZipCode 列上可能更有效。

请记住，这些只是例子，并且实际的实现可能会大大地变化，取决于因素如数据大小、查询复杂性和硬件能力。了解何时使用聚集和非聚集索引——基于智能数据库设计——是管理 SQL 数据库的重要方面。

主题：1.7 回顾和评估

恭喜！让我们回顾主要的概念并进行评估。

1. 聚集索引：这种类型的索引确定了表数据的物理排序。表只能有一个聚集索引。
2. 非聚集索引：这种类型的索引是独立的磁盘结构，引用表数据，帮助加速不在聚集索引范围内的查询。表可以有多个非聚集索引。
3. 高 vs. 低卡inality：高卡inality指的是具有独特值的列，在大多数，如果不是所有，行上。对高卡inality列使用聚集索引可能会导致更快的查找。低卡inality指的是具有多个行共享相同值的列。对于这些列，非聚集索引通常更有效。
4. 选择正确的索引：这取决于各种因素，包括查询类型、卡inality、数据修改需求和空间约束。

现在，让我们通过几个问题来评估我们的理解：

1. 聚集索引和非聚集索引之间的主要区别是什么？
2. 在哪种情况下，非聚集索引比聚集索引更适合？
3. 高卡inality和低卡inality是什么意思，并且如何影响索引类型的选择？

问题：聚集索引和非聚集索引之间的主要区别是什么？
答案：聚集索引和非聚集索引之间的主要区别在于存储和引用数据的方式。聚集索引确定了表数据的物理排序，本身就是表数据，而非聚集索引是独立的结构，指向数据位于数据库的其他地方。

问题：在哪种情况下，非聚集索引比聚集索引更适合？
答案：非聚集索引更适合在数据库需要支持大量搜索查询的列上，这些列不在聚集索引范围内。此外，非聚集索引还更适合在表经常更新时，因为更新不会导致整个表需要重新组织，与聚集索引相比。

问题：高卡inality和低卡inality是什么意思，并且如何影响索引类型的选择？
答案：卡inality是数据值在列中的独特性。高卡inality意味着列中具有大量独特值，低卡inality意味着列中具有多个重复值。对高卡inality列使用聚集索引可能会导致更快的查找，而对低卡inality列使用非聚集索引通常更有效，因为在这些列上，值是非常重复的。

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