我有一个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)
我的问题是:如何在我的真实数据上实现类似的性能?(或者,这是不可能的?)
如果使用
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';
如果您的id列只包含数字,那么将其定义为int,因为int将为您提供更好的性能(它更快)
将表中的列(定义为key的列)设为整数并重试。通过在DB(工作台或简单命令行)中运行测试来检查第一次性能。你应该得到一个更好的结果。然后,只有在需要的情况下(我对此表示怀疑),在引用键列时修改python以从整数转换为字符串(和/或反之亦然)。