一起声明索引和分别声明索引有什么区别？

Question

我在MySQL中处理大数据集时遇到了困难，正在尝试许多不同的索引方式。请问如果我同时声明多个索引有什么区别。

ALTER TABLE `db`.`test` ADD INDEX `someindex` (field1, field2, field3);

与其分别声明它们？

ALTER TABLE `db`.`test` ADD INDEX `f1` (field1), ADD INDEX `f2` (field2);

为什么要一起声明或分开声明？

- jwillis0720

---

在第一条语句中，你只创建了一个索引。这就像根据姓氏、名字和首字母在电话簿中进行搜索一样。而第二条语句则相当于两本电话簿：一本按照姓氏排序，另一本按照名字排序。 - Glenn

查看这个问题的答案。它提到了索引的相关内容。 - John Woo

Answer 1

我教授MySQL培训课程，当讨论多列索引时，我使用电话簿的比喻。电话簿基本上是按姓氏，然后是名字排序的索引。因此，排序顺序由先出现的“列”决定。搜索可以归为以下几类：

1. 如果您查找姓氏为Smith的人，您可以轻松地找到他们，因为电话簿是按姓氏排序的。
2. 如果您查找名字为John的人，则电话簿无助于您，因为John分散在整个电话簿中。您必须扫描整个电话簿才能找到他们所有人。
3. 如果您查找具有特定姓氏Smith和特定名字John的人，则电话簿会帮助您，因为您可以找到已排序在一起的Smith，而在这群Smith中，John也按排序顺序排列。

如果您有按名字排序再按姓氏排序的电话簿，则书的排序将帮助您解决上述情况#2和#3，但不适用于情况#1。

这解释了查找精确值的情况，但是如果您正在查找值的范围怎么办？ 假设您想查找所有名字为John且姓氏以'S'开头的人（Smith，Saunders，Staunton，Sherman等）。 在每个姓氏下，John被排序为J，但是如果您想要所有名字为John且姓氏以S开头的人，则John再次分散。因此，您最终不得不扫描以“S”开头的所有姓氏名称。而如果按名字然后按姓氏组织电话簿，则可以找到所有John在一起，然后在John内，所有以S开头的姓氏将被分组在一起。

因此，在多列索引中，列的顺序确实很重要。一种类型的查询可能需要索引的某个列顺序。如果您有几种类型的查询，则可能需要几个索引来帮助它们，并且列的顺序也不同。

想要了解更多详情和示例，请参阅我的演示文稿《如何真正设计索引》。或者在视频上观看我的演示。

---

为了澄清何时使用单列索引与多列索引，请考虑使用电话簿通过姓氏和名字的组合查找一个人，例如“Sarah Smith”。

如果你有两本电话簿，一本按姓氏排序，另一本按名字排序，你可以在按姓氏排序的电话簿中搜索“Smith”，在按名字排序的电话簿中搜索“Sarah”，然后以某种方式找到两个结果的交集。MySQL有时会尝试使用索引合并算法来实现这一点。

如果存在按姓氏和名字排序的单个索引，就像真正的电话簿一样，那么搜索将会在该索引的子集中找到“Smiths”的结果集，然后在该子集内部高效地查找“Sarahs”，因为子集是按名字排序的。

- Bill Karwin

---

这个答案解决了将事物放入多列索引中的顺序，而不是何时使用单列索引与多列索引。它应该澄清使用单列索引的任何限制/优势。 - Eric G

为了帮助理解我的意思，在另一个答案中，暗示多个单列索引可以提供多列索引的大部分好处：https://dev59.com/_Wct5IYBdhLWcg3wQ7Qy#12222699。在这种情况下，如果我还需要单独引用这些列，我可能需要三个单列索引？ - Eric G

Answer 2

数据库通常只能在每次查询中使用一个索引，因此假设您的“where”子句中包含所有三个列，则需要单个复合索引。

但是，复合索引只能从左到右部分使用，因此如果您有另一个仅针对field1的查询，那么复合索引仍将被使用。但是，对于仅在“where”子句中具有field2的查询，该索引无法使用，您需要一个仅在field2上或以field2开头的复合索引。

这在[MySQL文档]中有详细说明。

- GreyBeardedGeek