优化 SELECT ... WHERE IN (...)

我从外部系统收到一系列产品 ID。我必须显示保留序列的产品信息。

我使用以下选择来执行此操作：

SELECT * FROM  products
WHERE  prodid in (10331,11639,12127..) ORDER BY Field(prodid, 10331,11639,12127...);

序列可以包含 20 个 ID。 prodid 有 B 树索引。

这是非常频繁的查询，我正在尝试寻找提高系统该部分性能的方法。现在这个查询的平均时间是0.14-0.2秒 我想将时间减少到 0.01-0.05 秒。

最好的方法是什么？ MySQL HASH索引，将产品ID存储在memcached中，还是其他什么？

SELECT * FROM  products                         <<-- select * is non-optimal
WHERE  prodid in (10331,11639,12127..) 
ORDER BY Field(prodid, 10331,11639,12127...);   <<-- your problem is here

首先放一个索引prodid参见@安东尼的回答。

然后将查询更改为：

SELECT only,the,fields,you,need FROM  products
WHERE  prodid in (10331,11639,12127..) 
ORDER BY prodid

如果您确定您的IN列表在提供给之前按升序排序IN条款，order by prodid会产生相同的结果order by field(...

• 使用函数而不是字段会消除使用索引的任何机会，从而导致速度缓慢。
• select *将获取您可能不需要的数据，从而导致额外的磁盘访问、额外的内存使用和额外的网络流量。
• 在 InnoDB 上，如果你只select索引字段，MySQL永远不会读取表，而只会读取索引节省时间（在你的情况下，这可能不是问题）

最好的方法是什么？ MySQL HASH索引，将产品ID存储在memcached中，还是其他什么？

您可以使用一些技巧。

• 如果产品表不是太大，您可以将其设为memory表，存储在 RAM 中。不要对大表执行此操作，它会减慢其他操作的速度。
  您只能使用hash内存表上的索引。
• 如果 prodid 是连续的，您可以使用BETWEEN 1000 AND 1019代替
  IN (1000, 1001 ..., 1019)