优化SELECT ... WHERE IN(...) [英] Optimize SELECT ... WHERE IN (...)

问题描述

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

I'm receiving a sequence of product IDs from external system. I have to show the product information preserving the sequence.

我正在使用以下选择来这样做:

I'm using the following select to do so:

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

序列可以包含20个ID. prodid具有b树索引.

Sequence can consist of 20 IDs. prodid has b-tree index.

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

It's very frequent query, and I'm trying to find ways to improve performance of that part of the system. Now the average time for this query is 0.14-0.2 sec I would like decrease time to 0.01-0.05 sec.

做到这一点的最佳方法是什么? MySQL HASH索引，将产品ID存储在memcached中，还是其他?

What is the best way to do so? MySQL HASH index, store product id in memcached, or something else?

推荐答案

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的答案.

First put an index on prodid see @Anthony's answer.

将查询更改为:

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(...

If you make sure your IN list is sorted ascending before offering it to the IN clause, the order by prodid will yield the same result als order by field(...

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

• Using a function instead of a field kills any chance of using an index, causing slowness.
• select * will fetch data you may not need, causing extra disk access, and extra memory usage and extra network traffic.
• On InnoDB, if you only select indexed fields, MySQL will never read the table, but only the index saving time (in your case this is probably not an issue though)

做到这一点的最佳方法是什么? MySQL HASH索引，将产品ID存储在memcached或其他内容中?

What is the best way to do so? MySQL HASH index, store product id in memcached, or something else?

您可以使用一些技巧.

• 如果products表不是太大，可以将其设为memory表，该表存储在RAM中.不要在大桌子上这样做，这会使其他事情变慢.
  您只能在内存表上使用hash索引.
• 如果产品连续，则可以使用BETWEEN 1000 AND 1019代替
  IN (1000, 1001 ..., 1019)

• If the products table is not too big, you can make it a memory table, which is stored in RAM. Don't do this for big tables, it will slow other things down.
  You can only use hash indexes on memory tables.
• If the prodid's are continuous, you can use BETWEEN 1000 AND 1019 instead of
  IN (1000, 1001 ..., 1019)

这篇关于优化SELECT ... WHERE IN(...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！