MySQL面试问题:假设创建一个复合索引(a, b, c)，如果查询字段a和c，会使用这个复合索引吗?

让我们创建一个以MySQL为重点的课程计划，特别是理解复合索引的应用

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

主题：1.1 MySQL的复合索引介绍

复合索引，也称为联合或多列索引，是数据库表中两个或多个列组成的索引。这些列按照特定的顺序排列，并由此列的顺序所导向。

以图书馆为例，单列索引就像根据作者的名字来排列书籍。而复合索引就像根据作者和书名来排列书籍。

在 MySQL 中，复合索引起着非常重要的作用，帮助提高数据库操作的效率。要了解的是，MySQL 可以使用复合索引来加速包含任何列的查询，但是它的效率最高是在使用左侧前缀的索引列表中。

MySQL 复合索引的一个重要特性是“左侧前缀”。在复合索引中，列的顺序很重要。例如，在复合索引 (a, b, c) 中，MySQL 可以使用这个索引来加速包含 ‘a’ 或 ‘a 和 b’ 的查询，但不能使用 ‘b 和 c’，因为 ‘b 和 c’ 不是复合索引的左侧前缀。

复合索引可以大大提高查询的性能，但是它们的有效使用取决于如何查询数据。

主题：1.2 创建复合索引

在 MySQL 中创建复合索引与创建单列索引的语法有些差异。

下面是创建复合索引的基本语法：

CREATE INDEX index_name  
ON table_name(col1, col2, ...);

• index_name 是您想要为索引命名的名称。
• table_name 是要创建索引的表的名称。
• (col1, col2, …) 是要包含在索引中的列的名称，按照您希望它们出现的顺序排列。

让我们考虑一个具体的例子。 假设有一个 sales 表，其中包含 ProductID、OrderDate 和 Region 列。 如果您经常执行过滤或排序操作，并使用 ProductID 和 OrderDate，复合索引将会有所帮助。 下面是如何创建它：

CREATE INDEX idx_sales  
ON sales(ProductID, OrderDate);

在上面的例子中，idx_sales 是一个复合索引，在 sales 表上包含 ProductID 和 OrderDate。 索引的顺序很重要，因为 MySQL 将使用索引来加速包含 ProductID 的查询，或者包含 ProductID 和 OrderDate 的查询，但不是 OrderDate 的查询。

要记住的是，使用复合索引来提高查询性能是有代价的。 虽然它们可以大大提高查询性能，但它们可能会慢下来写操作（例如，INSERT、UPDATE、DELETE），因为 MySQL 必须更新索引。 要优化数据库性能，需要找到一个合理的平衡。

主题：1.3 查询字段 A 和 C

本主题讨论了一个重要的问题：如果你只查询字段 A 和 C，MySQL 会使用复合索引 (a, b, c)？

答案主要取决于 SQL 查询和索引的结构。如果 WHERE 子句中使用的列是复合索引的前 N 个连续列，则可以使用索引。因此，如果你在复合 (a, b, c) 索引中查询字段 A 和 C，但不查询字段 B，索引可能不会很有效。

例如，考虑下面的复合索引：

CREATE INDEX comp_index  
ON table(a, b, c);

并且假设我们有这样的查询：

SELECT *  
FROM table  
WHERE a = 1 AND c = 3;

在这种情况下，MySQL 将只使用字段 a 的索引，并忽略字段 c 的索引，因为字段 c 不是与字段 a 在复合索引的左侧前缀中连续的。

为了确保字段 a 和 c 的索引被使用，您可以重构复合索引为 (a, c, b) 或 (c, a, b)，并相应地调整查询。主要是 WHERE 子句中的字段应该与复合索引的左侧前缀中的字段对齐。

请记，总是一个好主意定期分析查询的性能并根据需要调整索引。MySQL 的 EXPLAIN 语句是一个有用的工具来了解如何查询与索引的交互。

在下一节中，我们将学习如何优化 MySQL 中的复合索引来获取更好的结果。

主题：1.4 复合索引优化

优化 MySQL 中的复合索引可以显著提高数据库查询的效率和速度。记住，有效地实现索引可以节省时间、资源并提高整个应用的性能。

下面是优化复合索引的几个关键点：

1. 索引列的顺序： 索引列的顺序可能会有显著的差异。如果 WHERE 子句中使用的列与复合索引的左侧前缀中的列对齐，MySQL 可以高效地使用索引。如果 WHERE 子句中使用了多个列，您可能会得到多个索引或复合索引，选择这些选项取决于特定的应用要求。
2. 索引卡 Cardinality： 索引卡 Cardinality 是索引值的多样性。索引列中具有更高卡 Cardinality 的列会导致更少的行扫描并提高查询性能。因此，在复合索引中，列的优先级应该是从左到右的。
3. 等式与范围条件： 在复合索引中，MySQL 可以对所有列进行等式检查，并对最后一列进行范围检查。如果 WHERE 子句中的范围条件位于中间，MySQL 不能使用右侧的索引部分。
4. 过度索引： 虽然索引可以加速数据检索，但它会慢下数据修改操作（如 INSERT、UPDATE 和 DELETE）的速度，因为每次修改索引列数据都需要更新索引结构。确保您不会过度索引表格——每个索引都应该有目的。
5. 使用 EXPLAIN： MySQL 的 EXPLAIN 关键字显示优化器如何选择索引来执行查询。定期使用 EXPLAIN 来了解如何查询与索引的交互。

主题：1.5 回顾和评估

在会话中，我们学习了 MySQL 中的复合索引的结构和区别，并学习了创建复合索引的语法并演示了一个例子。我们然后探讨了如何通过特定的字段，例如 A 和 C，来查询复合索引。我们进一步深入了解了如何优化使用复合索引的方法。

我们学习的关键概念包括：

1. 复合索引：它是多列的索引，可以显著加快查询执行的速度。
2. 创建复合索引：我们学习了语法并演示了一个例子。
3. 查询 A 和 C 字段：我们观察到 MySQL 只能使用索引中的列的左到右的部分，并且不会跳过任何列，只要 WHERE 子句中的列被引用。
4. 复合索引优化：我们学习了优化复合索引的有效技巧和技巧，包括索引列的顺序、索引卡inal性、区分等式和范围条件、避免过索引和使用 EXPLAIN 关键字。

现在是时候评估你对复合索引的了解了。

例子问题：

假设你是运营一个线上书店，书库中有千千本书。主要的 books 表在 MySQL 数据库中具有以下列：id, title, author, genre, publication_year 和 price。你发现自己经常执行以下查询：

SELECT *  
FROM books  
WHERE genre = '科幻' AND publication_year = 2018;

为了优化这个查询，你创建了以下复合索引：

CREATE INDEX genre_year_index  
ON books (genre, publication_year);

这个复合索引应该会使你的常用查询变得更快。

现在，它是你的回合！

问题：圣诞节到来了，你的商店正在为许多书籍提供折扣。你打算通过电子邮件向所有购买了《科幻》书籍发表后 2010 年的用户发送邮件。从 orders 表中获取用户 ID 列表，该表具有以下列：order_id, user_id, book_id, order_date。假设还有一个名为 orders_books 的中间表具有 order_id, book_id。

---

要从 orders 表中获取购买了《科幻》书籍发表后 2010 年的用户 ID，我们可以使用 JOIN 来合并 orders、orders_books 和 books 表中的信息。

您的 SQL 查询可能会像下面这样：

SELECT DISTINCT o.user_id  
FROM orders AS o  
JOIN orders_books AS ob ON o.order_id = ob.order_id  
JOIN books AS b ON ob.book_id = b.id  
WHERE b.genre = '科幻' AND b.publication_year > 2010;

这个查询检查每个订单中的书的类别和出版年份，并返回用户 ID，其中匹配了条件。 DISTINCT 关键字用于删除结果集中的重复 user_id。

记住，了解数据的结构和如何相互关联是在使用 SQL 和数据库时至关重要的。同时，确保为您的查询设置了正确的索引是至关重要的。

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