包含MEDIUMTEXT的~300万行的MySQL性能问题？

Question

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

第一列id为VARCHAR(8)，设为一级key.The，第2、3列为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;

只有当您的ids作为整数类型是唯一的(因此您的表中没有'0'和'00' )时，这才能工作。

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

如果您有一个已知的格式，您正在存储您的值(例如，没有零，或者填充0到8)，第二个最好的选择是使用这种确切的格式来执行您的查询，并包含'以不将其转换为整数。如果您总是将0填充到8位数字，请使用

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

如果您从未添加任何零，则仍然添加'

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

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

Answer 2

如果id列只包含数字，那么将其定义为int，因为int将提供更好的性能(更快)

Answer 3

使表中的列(定义为键)为整数，然后重试。通过在DB (工作台或简单命令行)中运行测试来检查第一性能。你应该得到更好的结果。然后，只有当需要时(尽管我对此表示怀疑)，在引用键列时，修改python以将整数转换为字符串(反之亦然)。