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

Question

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

3

我从外部系统接收到一系列产品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中，还是其他什么方法？

- Andre

“prodid”列的数据类型是什么？通常在“IN”子句中有多少个产品ID？ - Jeff

prodid 是 INT 类型（prodid < 100,000），IN 通常包含 20 个 ID。 - Andre

直接使用ID选择的数据之间是否存在其他关系？瓶颈可能在于每次查询都要跳过（可能很大的）数据库20次，以选择微小的数据片段，因此通过某些其他参数对其进行聚类可能是一个选项。 - Lyth

不，这个表与其他表没有关联。这个表和SELECT语句都很简单。外部系统会完成所有的繁重工作 - 我只需要选择这些ID并显示产品信息即可。 - Andre

3个回答

1

你可以尝试创建一个结果的联合：

SELECT * FROM  products WHERE prodid = 10331
UNION ALL
SELECT * FROM  products WHERE prodid = 11639
UNION ALL
.
.
UNION ALL
SELECT * FROM  products WHERE prodid = 12127

- Karolis

0

您可以尝试使用以下查询在prodid列上放置索引：

CREATE INDEX index_name
ON products (prodid);

如需更多信息，请阅读此篇文章。

- Anthony Claeys

1

prodid已经有B-Tree索引了，我已经提到过这一点。 - Andre

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Johan · Accepted Answer

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 上创建一个索引，参考 @Anthony 的答案。

然后修改查询语句为：

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上，如果您只选择索引字段，MySQL永远不会读取表，而只会读取索引，从而节省时间（在您的情况下可能不是问题）。

对于最佳方法，有几个技巧可供使用：

- 如果产品表不太大，则可以将其设置为存储在RAM中的memory表。请勿针对大型表执行此操作，否则会降低其他事务的速度。您只能在内存表上使用hash索引。 - 如果prodid连续，则可以使用BETWEEN 1000 AND 1019而不是IN (1000, 1001 ..., 1019)。