如何通过使用哈希表来优化查询速度

Question

这里有大约1百万条记录。需要查询来支持分页系统。

查询语句应该是这样的：

SELECT field1, field2, field3 FROM table 
WHERE field4 = '$value' 
ORDER BY field5 ASC limit $offset, 30;

字段4和字段5上有索引。

我听说：

创建另一个表（表6），它是表4的索引哈希？

搜索数字而不是文本会更快，因此查询类似于：

SELECT field1, field2, field3 Force
Index(Table6) FROM table WHERE field 6
= '$hashvalue' AND field4 = '$value' ORDER BY field5 ASC limit $offset, 30;

它应该能够在进行文本搜索之前消除99.99%的数据，并且无论偏移量如何，都应该加快您的查询速度。

我应该如何实现它？您能帮我理解这个例子中哈希表的思想吗？

- Newbie1

---

字段4和字段5上有索引吗？是index(field4, field5)还是index(field4) index(field5)？ - knittl

我尝试了两个版本。观察到相似的加载时间。目前正在使用index(field4, field5)，因为我知道这应该工作得更好，对吗？ - Newbie1

使用 EXPLAIN 并查看查询执行计划是否不同。 - knittl

如果高偏移量导致减速，您可能会对MySQL中的延迟行查找技巧感兴趣，该技巧在http://explainextended.com/2011/02/11/late-row-lookups-innodb/中有所解释。 - Michael J.V.

Answer 1

我认为作者指的是哈希索引，而不是哈希表：

ALTER TABLE mytable ADD field4_hash BINARY(16);

UPDATE mytable SET field4_hash = UNHEX(MD5(field4));

CREATE INDEX ix_mytable_field4hash_field5 ON mytable (field4_hash, field5)

SELECT  field1, field2, field3
FROM    mytable 
WHERE   field4_hash = UNHEX(MD5('$value'))
        AND field4 = '$value'
ORDER BY
        field5 ASC
LIMIT   $offset, 30;

这个思路是，一个字符串的MD5哈希值通常比字符串本身更短，因此在哈希值上进行索引查找比在字符串上效率更高。

- Quassnoi

---

谢谢，我会尝试的。它是否有助于加速查询，无论偏移量如何，还是只是提供一般性的改进？ - Newbie1

Answer 2

哈希交替可能有所帮助，但主要瓶颈在于当用户请求高$offset时。
在这种情况下，最好拥有id自动递增的主键，您可以利用它来执行分页。请参考此示例

select id, name
FROM table
LIMIT 0, 3;

返回类似以下内容的结果

+----+-----------------------------------+
| id | name                              |
+----+-----------------------------------+
|  1 | Beauty                            |
|  4 | Goodbye Country (Hello Nightclub) |
|  5 | Glee                              |
+----+-----------------------------------+

您可以看到最后一个ID是5。当用户请求第2页时，与其执行查询

select id, name
FROM table
LIMIT 3, 3;

你可以编写这个查询

select id, name
FROM table
WHERE id > 5
LIMIT 0, 3;

由于上一个 ID 是 5，写 WHERE id > 5 返回直接在第一页之后的行，这些行代表用户想要在第二页上看到的行。

如果您使用第一个查询 (LIMIT 3,3)，MySQL 将不得不找到第一页上的前三行，以确定第 4-6 行将是什么。

您没有提及 field5 的类型，但您可能能够应用相同的方法来加速分页。请记住，该字段必须被索引才能起作用。

- mike

---

我目前正在使用 field4 = '$value' and id > $offset limit 30。这对于像1000、2000、3000等非常高的偏移量来说有一定帮助（加载时间在那里需要几秒钟），第一页加载需要0.01秒。 - Newbie1

@Newbie1 请确保id已被索引。 - Johan

另一件事是，当使用 field4 = '$value' and id > $offset limit 30 时，无法使用 order by 并获得排序结果。 - Newbie1

即使您执行id > $offset limit 30，如果需要，仍然可以通过order by id进行排序。没有限制。但是正如Johan所说，确保ID是自增的主键。您用哪个引擎来处理表？InnoDB还是MyISAM？ - mike