我有一个非常简单的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。

09-20 11:49