行数vs大小vs有效访问的查询模式

Question

表如下:

CREATE TABLE Associations (
obj_id int unsigned NOT NULL,
attr_id int unsigned NOT NULL,
assignment Double NOT NULL
PRIMARY KEY (`obj_id`, `attr_id`),
);   

应该每行占用16字节。所以每行的开销很小。
我需要使用这作为一个查找表，其中主要的查询将是:

SELECT WHERE obj_id IN (... thousands and thousands of ids....). 

考虑到这些以及表将有~5亿行这一事实，为了获得良好的性能，还有什么需要考虑的吗?具有此行数的表将占用约8GB，这似乎是合理的大小。
这里还有什么需要改进的地方吗?

Answer 1

使用具有数千个id的IN()谓词有一些限制。可能的结果是，优化器放弃尝试分析这么长的列表，转而对所有5亿行进行表扫描。

你应该在这里阅读range_optimizer_max_mem_size: https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-optimization-memory-use

有时最好将数千个id加载到临时表中，然后从临时表连接到关联表。您应该测试它，看看断点在哪里，给定您的数据和您的MySQL版本。

Answer 2

假设您使用obj_ids构建另一个表(参见Bill's Answer)，下面是将影响性能的其他因素。

如果buffer_pool_size小于满足查询实际需要的数据，可能会有额外的I/O抖动。

如果obj_ids是"邻接的";值和对于每个obj_id没有很多行，那么在每个块中可以找到多个结果。这可以减少I/O并减少缓存(buffer_pool)上的压力。一个块是16KB。根据经验，一个块持有人有100行。在你的情况下，它可能在400左右。

因此，如果每个obj_id通常有超过400行，每个obj_id将读取一个或多个需要的块。

另一方面，如果obj_id值和每一个只聚集几行，一个块提供多个结果。

你会怎么处理"千千万万"呢?的行吗?也许一些分块是有益的——让我们看看接下来会发生什么。