MySQL 在包含 MEDIUMTEXT 的大约 300 万行上出现性能问题？

Question

我有一个包含 3 列和 3600K 行的表。使用 MySQL 作为键值存储。

第一列id是VARCHAR(8)并设置为主键。第二列和第三列是MEDIUMTEXT。当调用SELECT * FROM table WHERE id=00000时，MySQL花费了大约54秒~3分钟。

为了测试，我创建了一个包含 VARCHAR(8)-VARCHAR(5)-VARCHAR(5) 的表，其中数据是从 numpy.random.randint 随意生成的。如果没有主键，SELECT 需要 3 秒。与 VARCHAR(8)-MEDIUMTEXT-MEDIUMTEXT 相同的随机数据，在没有主键的情况下 SELECT 的时间成本为 15 秒。(注意:在第二次测试中，第二列和第三列实际上包含非常短的文本，如 ' 65535'，但创建为 MEDIUMTEXT)

我的问题是:如何在真实数据上实现类似的性能？ (或者，这是不可能的吗？)

Answer 1

如果您使用

SELECT * FROM `table` WHERE id=00000

而不是

SELECT * FROM `table` WHERE id='00000'

您正在查找等于整数0的所有字符串，因此MySQL必须检查所有行，因为'0'，'0000 ' 甚至 '0' 都会被转换为整数 0。因此，id 上的主键将无济于事，您最终会得到一个缓慢的完整表。即使您不以这种方式存储值，MySQL 也不知道这一点。

正如所有评论和答案所指出的那样，最好的选择是将数据类型更改为 int:

alter table `table` modify id int;

只有当您的 id 转换为整数是唯一的时(因此您的表中没有 '0' 和 '00' )，这才有效。

如果您有任何引用 id 的外键，则必须先删除它们，然后在重新创建它们之前，也更改其他列中的数据类型。

如果您存储值的格式已知(例如，没有零，或用 0 填充，长度最多为 8)，第二个最佳选择是使用这种确切的格式进行查询，并包含 ' 不将其转换为整数。如果你例如始终将 0 填充到 8 位数字，使用

SELECT * FROM `table` WHERE id='00000000';

如果您从不添加任何零，仍添加 ':

SELECT * FROM `table` WHERE id='0';

使用这两个选项，MySQL 都可以使用您的主键，您将在几毫秒内得到结果。