MySQL 面试：MySQL 的索引什么情况下会失效？

1月 3 2024 mysql 19 分钟读完 (约 2843 字)

详细分析MySQL中的索引。了解内部工作原理、好处和如何通过索引提高查询性能。

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

主题：MySQL索引深入分析

MySQL索引发挥着优化数据库搜索性能的重要作用。为了深入了解索引，让我们更详细地分析它们。

MySQL中的索引是什么？

简单来说，索引就像书的索引一样。当你需要找到信息时，你会参考索引，它会快速指导你到包含所需信息的页面。

在MySQL中，索引是数据结构（通常是B-Tree），它提高了数据库表的访问性能。索引用于快速定位数据，而不是每次访问数据库表时搜索整个数据库表。

它们可以使用数据库表的一个或多个列创建，为随机访问和记录的有序访问提供基础。

MySQL索引的优势：

更快的数据检索： 索引的主要优势是加快数据检索的速度。索引使数据检索变得更快。
ORDER BY 优化： 索引可以优化 ORDER BY 子句，以更快地对数据进行排序。
更好的性能： 它们显著提高了 SELECT 查询和 WHERE 子句的性能。

请记，虽然索引加快了数据检索，但它会慢下数据修改操作，例如 INSERT、DELETE 和 UPDATE。这是因为每次修改数据时，索引也需要更新。

主题：MySQL 中的索引类型

了解 MySQL 中不同类型的索引，例如 PRIMARY、UNIQUE、INDEX、FULLTEXT 以及它们的使用。

MySQL 中有多种索引类型。每种类型在特定场景下使用，并提供其特殊的优势。让我们来详细了解这些不同类型：

PRIMARY 索引： 这是表的主要索引。每张表都应该有一个主要索引。值在这个索引中是唯一的并不为空。在大多数情况下，这是表的主键。
UNIQUE 索引： 这个索引防止两条记录具有相同的值在特定列中。这帮助维护数据的完整性。它允许 NULL 值，但唯一性仍然被强制。
INDEX (或 KEY)： 这是最简单的索引类型。它允许重复和 NULL 值。它通常用于提高特定搜索的性能，并不需要强制唯一性或非 NULL 性。
FULLTEXT 索引： 这是用于全文搜索的索引。它是一个非常强大的索引类型，用于搜索文本值。然而，它是一个专门的索引类型，只适用于文本（不适用于数字或日期）。

每种索引类型都为特定目的服务。优化数据库的最佳使用是至关重要的。

了解何时和何处使用适当的索引是至关重要的。它取决于存储的数据、数据的完整性要求、执行的查询以及许多其他因素。

主题：MySQL索引管理

了解如何在 MySQL 中管理索引是优化数据库性能的关键技能。

当处理索引时，您可以执行以下主要操作：

创建索引：

要在 MySQL 中创建索引，您可以使用 CREATE INDEX 语句：

CREATE INDEX index_name
ON table_name (column1, column2, …);

例如，如果我们有一个名为 Students 的表，并且我们想创建一个索引在 LastName 和 FirstName 列上，我们将写：

CREATE INDEX idx_students_name
ON Students (LastName, FirstName);

删除索引：

要在 MySQL 中删除索引，您可以使用 DROP INDEX 语句：

DROP INDEX index_name ON table_name;

禁用索引：

在某些情况下，您可能希望暂时禁用索引。MySQL 允许您使用 ALTER TABLE 语句禁用索引：

ALTER TABLE table_name DISABLE KEYS;

要再次启用它，您将使用 ENABLE KEYS：

ALTER TABLE table_name ENABLE KEYS;

重命名索引：

MySQL 不支持直接重命名索引。要重命名索引，您需要先删除（删除）索引，然后再创建它们使用新名称。

请记，虽然索引有利并可能会加快数据检索，但它们也会带来其他成本。索引占用存储空间并会慢下数据更新操作（因为当数据更新时，索引也需要更新）。

主题：优化 MySQL 与索引

了解如何优化 MySQL 性能使用索引是管理数据库的关键部分。

一个关键因素，影响数据检索操作的效率是索引的正确使用。但是，怎样知道索引是有效的呢？请参考 EXPLAIN 命令。

EXPLAIN 命令是 MySQL 中的一个强大工具，可帮助了解 MySQL 如何执行查询。通过使用 EXPLAIN 命令来查询之前，MySQL 将返回有关如何处理语句以及表的读取顺序的信息。

下面是一个例子：

EXPLAIN SELECT * FROM Students WHERE LastName = “Smith”;

输出可能会返回大量数据，但您需要关注主要三列：

select_type: 这告诉我们查询的类型。
key: 这告诉我们 MySQL 使用哪个索引来运行查询。
rows: 这告诉我们 MySQL 考虑了多少行来给出查询的结果。

另外一个重要的事情是，不同的查询需要不同的优化。例如，一个 SELECT 查询，用于检索数据可能会被优化不同于一个 UPDATE 查询，用于更新数据。

作为一般规则，索引列在 WHERE、JOIN、ORDER BY、和 GROUP BY 子句中经常使用的列上的索引可以显著增加 SELECT 查询的速度。

然而，请记，索引会为数据库添加额外的负担，特别是对 INSERT、UPDATE、和 DELETE 语句的处理。因此，索引的数量应该尽可能地保持在最小的水平上，以减少负担。

主题：何时MySQL不使用索引

虽然索引是为了优化数据检索而设计的，但在某些场景下，MySQL可能会忽略已存在的索引。让我们深入探讨这些场景：

小表： 在具有较少行数的表中，MySQL优化器可能会忽略索引并执行全表扫描。这是因为在索引中先读取索引，然后再读取行可能会涉及更多的开销，而直接读取每一行可能更有效。
低选择性： 具有多个重复值的索引具有低选择性。如果索引列不足以有效地缩小行数，MySQL可能会跳过使用索引并执行全表扫描。
NULL值： 如果索引列包含 NULL 值并且 WHERE 子句是 IS NULL 或 IS NOT NULL，MySQL不能使用索引，因为比较操作不能与 NULL 值一起使用。
不使用索引的左侧前缀： MySQL可以有效地使用索引，当 WHERE 子句使用左侧部分的多列索引时。然而，当查询不涉及左侧部分时，MySQL可能无法有效地使用索引。
LIKE 操作符的通配符开头： 当使用 LIKE 操作符在 WHERE 子句中时，如果模式开头有通配符，MySQL不能利用索引。

记住，索引是优化数据检索的有用工具，但它们不总是被使用。深入了解 MySQL 如何和何时使用索引可帮助您的数据库设计和查询构造。

主题：回顾和评估

已经到了课程的尾端。在我们结束之前，让我们先做一个快速回：

我们深入探讨了 MySQL 索引的概念。
我们学习了 MySQL 中的两种索引类型。
我们了解了如何管理 MySQL 中的索引。
我们看到了如何使用 EXPLAIN 命令来评估 MySQL 性能并优化数据检索。
我们探讨了 MySQL 可能不使用索引的场景。

现在，来试试你的知识！下面是一些思考性的问题：

MySQL 索引的主要目的是什么，并且如何提高数据库性能？
在 MySQL 中可以描述两种不同类型的索引，并且在哪些场景下使用它们？
如何禁用并重新启用 MySQL 中的索引？
MySQL 可能会在哪些场景下不使用索引，即使它可用？
如何使用 EXPLAIN 命令在 employees 表中基于 employee_id 进行数据检索？

试着写下你的答案，然后与你的笔记或者你使用的其他资源进行比较。回和复习是强化学习的好方法。

记住，掌握任何主题都是时间、练习和持久努力的事情。保持坚持！

MySQL 索引的主要目的是什么，并且如何提高数据库性能？ An index in MySQL 是一种数据结构，可以显著提高数据库表上的数据检索操作的速度。它类似于书的索引，可以使数据库在每次访问数据库表时找到数据，而不是扫描整个数据库中的每一行。索引可以极大地提高数据库性能。
可以描述两种不同类型的索引在 MySQL 中，并在哪些场景下使用它们？

PRIMARY index: 这是一种唯一索引，其中列必须包含唯一且不为 NULL 的值。这通常用于主键。
FULLTEXT index: 这是用于全文搜索的索引。它专门用于通过 SQL 查询中的字符串定位模式进行文本搜索。

如何禁用并重新启用 MySQL 中的索引？

禁用非唯一索引: ALTER TABLE table_name DISABLE KEYS;
重新启用非唯一索引: ALTER TABLE table_name ENABLE KEYS;

在哪些场景下 MySQL 可能会不使用索引，即使它可用？ MySQL 可能会在以下场景下不使用索引，即使它可用：如果 MySQL 估计使用索引会 slower 于全表扫描，例如，如果表很小或索引列具有很低的选择性（许多重复值）。
可以使用 EXPLAIN 命令在 employees 表中基于 employee_id 进行数据检索吗？ 下面是一个简单的例子：

EXPLAIN SELECT * FROM employees WHERE employee_id = 101

记住，练习是学习这些概念的关键。不要害惕深入探讨并在真实的 MySQL 环境中试用这些命令来进一步探索！

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

#interview