多列索引 vs 多个索引

Question

我在MySQL数据库中有如下表：

CREATE TABLE `secondary_images` (
  `imgId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `primaryId` int(10) unsigned DEFAULT NULL,
  `view` varchar(255) DEFAULT NULL,
  `imgURL` varchar(255) DEFAULT NULL,
  `imgDate` datetime DEFAULT NULL,
  PRIMARY KEY (`imgId`),
  KEY `primaryId` (`primaryId`),
  KEY `imgDate` (`imgDate`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

SQL 语句如下：

SELECT imgURL, view FROM secondary_images 
WHERE primaryId={$imgId} ORDER BY imgDate DESC

你可以看到我将primaryId和imgDate都设置为索引键。我的想法是因为WHERE子句使用primaryId查询结果，而ORDER子句使用imgDate。

我的问题是，像我现在这样使用多个索引是否更好？还是应该使用多列索引（这是我目前不太理解的东西）？

这是我从EXPLAIN得到的：

id = 1   
select_type = simple      
table = secondary_images         
type = ref
possible_keys = primaryId
key = primaryId
key_len = 5
ref = const
rows = 1
extra = Using where; Using filesort

注意: 这并不使用多列索引，而是通过使用上述表描述的结果。

- stefmikhail

---

请记住，索引不是免费的。如果您有多个索引，这意味着在每次插入或更新时，需要更新每个索引。您需要权衡这些更新对检索性能提高的影响和性能损失之间的平衡。 - Marvo

@Marvo - 这个数据库纯粹用于检索。只有我一个人会向数据库中添加数据。我认为这解决了你提到的任何问题，对吗？ - stefmikhail

@stef: 哈哈！你就要学到一些非常有用的东西了。执行SELECT查询，但在前面加上explain关键字：explain SELECT imgURL, view FROM secondary_images WHERE primaryId={$imgId} ORDER BY imgDate DESC。阅读使用Explain优化查询。 - Konerak

这真的取决于您的表的大小。需要注意的是运行EXPLAIN并查看MySQL是否实际使用了您的索引。如果有许多重复项，它可能决定不使用它。我有一本书称之为“扫描vs搜索”问题。基本上，您会执行类似SELECT COUNT(DISTINCT primaryId) FROM secondary_images和SELECT COUNT(*) FROM secondary_images的操作，并计算比率。数字越大，“选择性”就越高。如果数字太低，则不会产生有用的索引。 - Poodlehat

类似于 ALTER TABLE secondary_images DROP INDEX primaryId, DROP INDEX imgDate, ADD INDEX IdNDate (imgId DESC, primaryId ASC) ; 这样的语句应该可以解决问题。 - Poodlehat

Answer 1

您应该在（primaryId，imgDate）上使用多列索引，以便MySQL可以将其用于选择行和排序。

如果用于排序的所有列都不在用于选择的索引中，则MySQL使用“filesort”策略，这包括对所有行进行排序（如果没有太多行，则在内存中；否则在磁盘上）。

如果用于排序的所有列都在索引中，则MySQL使用索引来获取行的顺序（有一些限制）。

MySQL使用树结构来管理索引。这允许直接按照顺序访问键，而无需进行排序。

多列索引基本上是列连接的索引。这使得MySQL可以找到与primaryId={$imgId}匹配的第一行，然后以正确的顺序直接访问所有其他行。

对于primaryId的单行索引，MySQL可以找到所有与primaryId={$imgId}匹配的行，但它不会按特定顺序找到行；因此它会在之后对它们进行排序。

请参见EXPLAIN和ORDER BY Optimization。

- Arnaud Le Blanc

---

我不明白为什么它要比较一列与另一列，而不是同时比较两列与另外两列。你能解释一下吗？ - Poodlehat

@arnaud576875 - 非常感谢！您能否简要解释一下为什么这是在这种情况下最佳选择？此外，我该如何使用 SQL 将我的当前表更改为在 (primaryId, imgDate) 上使用多列索引？ - stefmikhail

@arnaud576875 - 解释得非常好。然而，primaryId 不是唯一的。最多可以有十个不同的条目具有相同的 primaryId。这会改变什么吗？ - stefmikhail

Answer 2

您的解释看起来像这样：

[id] => 1 
[select_type] => SIMPLE 
[table] => secondary_images 
[type] => ref 
[possible_keys] => primaryId 
[key] => primaryId 
[key_len] => 5 
[ref] => const 
[rows] => 1 
[Extra] => Using where; Using filesort 

让我们一起来看看它。

[id] => 1 

这意味着我们正在谈论第一个表。您的语句中只调用了一个表。

[select_type] => SIMPLE 

我们正在执行一个简单的SELECT查询。

[table] => secondary_images 

问题中涉及的表名。

[type] => ref 

选择类型，在连接操作中非常重要。

[possible_keys] => primaryId 

这是一个重要的字段：它显示可以用于帮助查询更快地执行的键。在这种情况下，只有您的主键被认为是有用的。

[key] => primaryId 

这是一个重要的字段：它显示最终使用了哪个键或键组，本例中为主键。

[key_len] => 5 
[ref] => const 
[rows] => 1 

猜测查询所检查的行数。

[Extra] => Using where; Using filesort 

最重要的字段(imho)。 - 使用where: 您正在使用where语句。相当好。 - 使用filesort: 您的查询结果非常大，无法在内存中进行排序。MySQL必须将其写入文件，对文件进行排序，然后输出。这意味着需要磁盘访问，并且会减慢所有操作。通常情况下，添加一个可以帮助排序的索引是有用的，但解决"using filesort"是一个单独的章节。

- Konerak

---

哇，非常感谢您。更容易理解了。那么，我该如何使用这些信息来决定是否采用多重索引键呢？我不会要求您讨论文件排序问题，因为您自己说那是另一个完全不同的问题，但是改用多重索引键能帮助解决这个问题吗？ - stefmikhail

你应该首先了解关于explain的知识。MySQL网站是一个很好的起点，而《高性能MySQL》是我读过的最好的MySQL书籍。然后，要理解它非常依赖于你的表、存储引擎、配置（缓存大小等）和表中的数据。因此，测试的最佳方法是：复制表格，并在副本上添加所需的索引。然后，比较这些解释。这就是为什么你需要理解explain的原因 :) - Konerak

我从未查看过那些互联网资源。感谢提供链接。我有几个iPhone应用程序，其中包括O'Reilly Media出版的MySQL书籍，我应该多读一些。 - stefmikhail

@Blossoming_Flower，using filesort 的出现是否意味着查询效率低下？我有一些查询从大约 30 行的表中返回几个 ~16Kb 的结果，但它们总是使用 filesort... - Martin

一般来说，使用文件排序意味着它会使用更多的资源。然而，对于像你这样的小数据集，你不会看到任何差异。要在性能上有所区别，您需要涉及更多的数据。 - timetofly