我有一个非常简单的MYSQL数据库,只有3列,但有几百万行。
其中两个列(hid1,hid2)描述了研究对象(其中约50,000个),第三列(得分)是hid1与hid2比较的结果。因此,行数是max(hid1)* max(hid2),这是一个很大的数字。因为该表仅需写入一次并读取数百万次,所以我选择了MyISAM表(我希望这是一个好主意)。最初,我计划为给定的一对hid1,hid2检索“分数”,但事实证明为给定的hid1检索所有分数(和hid2)更为方便。
我的表格(“结果”)如下所示:
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| hid1 | mediumint(8) unsigned | YES | MUL | NULL | |
| hid2 | mediumint(8) unsigned | YES | | NULL | |
| score | float | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
一个典型的查询是
select hid1,hid2,score from result where hid1=13531 into outfile "/tmp/ttt"
这是问题所在:查询只是花费了太长时间,至少有时是这样。对于某些“ hid1”值,我在一秒钟之内就能得到结果。对于其他hid1(尤其是大数字),我必须等待长达40秒的时间。就像我说的那样,我必须运行成千上万个这样的查询,所以我对加快速度很感兴趣。
让我重申一下:查询约有50,000次匹配,我不需要按任何特定顺序进行匹配。我在这里做错了吗,还是像MySQL这样的关系数据库不能胜任此任务?
我已经尝试过的是增加/etc/mysql/my.conf中的key_buffer
这似乎有帮助,但作用不大。 hid1上的索引只有几个GB,key_buffer是否必须大于索引大小才有效?
任何提示将不胜感激。
编辑:这是一个带有相应“解释”输出的示例:
select hid1,hid2,score from result where hid1=132885 into outfile "/tmp/ttt"
Query OK, 16465 rows affected (31.88 sec)
如下所示,实际上正在使用索引hid1_idx:
mysql> explain select hid1,hid2,score from result where hid1=132885 into outfile "/tmp/ttt";
+----+-------------+--------+------+---------------+------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------------+---------+-------+-------+-------------+
| 1 | SIMPLE | result | ref | hid1_index | hid1_index | 4 | const | 15456 | Using where |
+----+-------------+--------+------+---------------+------------+---------+-------+-------+-------------+
设置1行(0.00秒)
我确实感到困惑的是,对于hid1而言,低数字查询总是比高数字查询快得多。这不是我期望使用索引的结果。
最佳答案
基于始终在hid1
上包含相等过滤器的查询模式的两个随机建议:
请改用InnoDB表,并利用(hid1, hid2)
上的聚集索引。这样,属于同一隐藏的所有行将在物理上位于一起,这将加快恢复速度。
将表散列在hid1上,并使用适当的分区nr。