mysql - 一起声明索引和单独声明索引有什么区别？

Question

我在处理 mysql 中的庞大数据集时遇到了麻烦，我正在探索许多不同的索引方法。如果我一起声明多个索引，谁能告诉我有什么区别

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

而不是单独声明它们？

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

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

Answer 1

我教MySQL培训类，在讨论多列索引时，我用电话簿来类比。电话簿基本上是姓氏索引，然后是名字索引。所以排序顺序由第一个“列”决定。搜索分为几类:

1. 如果您查找姓史密斯的人，您会很容易找到他们，因为这本书是按姓氏排序的。
2. 如果您查找名字是 John 的人，电话簿没有帮助，因为 John 分散在整本书中。你必须扫描整个电话簿才能找到他们。
3. 如果您查找具有特定姓氏 Smith 和特定名字 John 的人，这本书会有所帮助，因为您会发现 Smiths 排序在一起，并且在这组 Smiths 中，Johns 也按排序顺序找到。<

如果您有一本电话簿按名字然后按姓氏排序，则电话簿的排序将在上述情况 #2 和 #3 中为您提供帮助，但不会为情况 #1 提供帮助。

这解释了查找精确值的情况，但是如果您要按值范围查找怎么办？假设您想查找所有名字为 John 且姓氏以“S”开头的人(Smith、Saunders、Staunton、Sherman 等)。 Johns 在每个姓氏中按 J 排序，但如果您想要所有以 S 开头的姓氏的所有 Johns，则 Johns 不会分组在一起。他们又分散了，所以你最终不得不扫描所有姓氏以“S”开头的名字。而如果电话簿按名字然后按姓氏组织，您会发现所有 Johns 在一起，然后在 Johns 中，所有 S 姓氏将被组合在一起。

因此，多列索引中列的顺序绝对重要。一种类型的查询可能需要索引的特定列顺序。如果您有多种类型的查询，您可能需要多个索引来帮助它们，并且列的顺序不同。

有关更多详细信息和示例，请参阅我的演示文稿 How to Design Indexes, Really .或者观看我在 video 上的演示.

---

要阐明何时使用单列索引与多列索引，请考虑您是否正在使用电话簿通过姓氏和名字的组合来查找一个人。例如“莎拉·史密斯”。

如果您有两本电话簿，一个按姓氏排列，另一个按名字排列，您可以在姓氏簿中搜索“Smith”并在名字簿中搜索“Sarah”，然后以某种方式找到交集的两个结果。 MySQL 有时会尝试使用 index merge algorithm 执行此操作.

搜索一个索引会更好，如果它同时按姓氏和名字排序，就像真正的电话簿一样。然后搜索找到“Smiths”这本书的子集，并且在该子集中，它可以有效地搜索“Sarahs”，因为该子集是按名字排序的。