mysql - 哪个索引会加快查询速度？

Question

在3.5亿条记录的表中，结构为:

CREATE TABLE `table` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `job_id` int(10) unsigned NOT NULL,
    `lock` mediumint(6) unsigned DEFAULT '0',
    `time` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `job_id` (`job_id`),
    KEY `lock` (`lock`),
    KEY `time` (`time`)
) ENGINE=MyISAM;

我应该创建什么索引来加速查询:

UPDATE `table` SET `lock` = 1 WHERE `lock` = 0 ORDER BY `time` ASC LIMIT 500;

Answer 1

lock 被声明为 NULLable。这是否意味着该值通常为NULL？如果是这样，那么 MyISAM(不是 InnoDB)中存在一个严重的问题，可能会导致 500 次额外的碎片命中。

当 MyISAM 行更新并且它变得更长时，该行将不再适合它所在的位置。 (现在我的详细知识变得模糊。)新行将被放置在其他地方和/或它将被分成两部分，并在各部分之间有链接。这意味着在两个地方写入。

正如 Gordon 指出的那样，对任何索引列(在您的情况下为 lock)的任何更改都会涉及代价高昂的索引更新 - 从索引 BTree 中的一个位置删除“行”并添加一行在另一个地方。

lock 的值只有 0 或 1 吗？然后使用 TINYINT(1 字节)，而不是 MEDIUMINT(3 字节)。

您应该检查MAX(id)。如果是干净的，id的最大值约为350M(不太接近4B的限制)。但如果出现任何流失，则可能更接近极限。

我也主张改用InnoDB。但是，您的 10GB(数据+索引)将在转换中增长到 20-30GB。

您是否“锁定了最早解锁的”东西？然后您会进行选择以查看锁定的内容吗？

如果这太慢，请不要一次执行 500，选择一个较小的数字。

使用InnoDB，可以避免锁定吗？ 也许事务锁定就足够了？

我认为我们需要查看环境的其余部分——其他表、作业“流程”等。我们可能还可以提出其他建议。

我附议 INDEX(lock, ti​​me) 动议。但这样做时，DROP 只是lock 上的索引，因为它是多余的。

当转换为 InnoDB 时，在同一个 ALTER 中进行所有索引更改。这将比单独的传递运行得更快。