MySQL"IN"运算符的性能(大?)值的数量 [英] MySQL "IN" operator performance on (large?) number of values
问题描述
我最近一直在尝试使用Redis和MongoDB，似乎经常出现在MongoDB或Redis中存储 id 数组的情况.因为我要询问MySQL IN 运算符，所以我会坚持使用Redis.
我想知道在IN运算符中列出大量(300-3000) id 的效果如何，
SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)
想象一下一个简单的产品和类别表，您通常可以将它们结合在一起以从某个特定的中获得产品类别.在上面的示例中，您可以看到在Redis(category:4:product_ids)中的给定类别下，我返回了ID为4的类别中的所有产品ID，并将它们放在上述SELECT查询中的IN运算符内. /p>
这表现如何?
这是取决于情况"的情况吗?还是有一个具体的不可接受"，快速"或缓慢"，还是我应该添加一个LIMIT 25，还是没有帮助?
SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)
LIMIT 25
还是应该修剪Redis返回的产品ID的数组以将其限制为25，并且仅将25个ID添加到查询中而不是添加3000，然后从查询内部LIMIT将其添加到25?
SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 25)
任何建议/反馈都非常感谢！
通常来说，如果IN列表太大(对于某些定义不正确的太大"值，通常在100左右或较小)，使用连接并创建临时表(如果需要保留数字)会变得更加高效.
如果数字是一个密集的集合(没有空隙-样本数据表明)，那么使用WHERE id BETWEEN 300 AND 3000甚至可以做得更好.
但是，可能在集合中存在缺口，这时最好还是列出有效值列表(除非缺口的数量相对较少，在这种情况下，您可以使用:
WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836
或者有什么差距.
I have been experimenting with Redis and MongoDB lately and it would seem that there are often cases where you would store an array of id's in either MongoDB or Redis. I'll stick with Redis for this question since I am asking about the MySQL IN operator.
I was wondering how performant it is to list a large number (300-3000) of id's inside the IN operator, which would look something like this:
SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)
Imagine something as simple as a products and categories table which you might normally JOIN together to get the products from a certain category. In the example above you can see that under a given category in Redis ( category:4:product_ids ) I return all the product ids from the category with id 4, and place them in the above SELECT query inside the IN operator.
How performant is this?
Is this an "it depends" situation? Or is there a concrete "this is (un)acceptable" or "fast" or "slow" or should I add a LIMIT 25, or doesn't that help?
SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)
LIMIT 25
Or should I trim the array of product id's returned by Redis to limit it to 25 and only add 25 id's to the query rather than 3000 and LIMIT-ing it to 25 from inside the query?
SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 25)
Any suggestions/feedback is much appreciated!
Generally speaking, if the IN list gets too large (for some ill-defined value of 'too large' that is usually in the region of 100 or smaller), it becomes more efficient to use a join, creating a temporary table if need so be to hold the numbers.
If the numbers are a dense set (no gaps - which the sample data suggests), then you can do even better with WHERE id BETWEEN 300 AND 3000.
However, presumably there are gaps in the set, at which point it may be better to go with the list of valid values after all (unless the gaps are relatively few in number, in which case you could use:
WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836
Or whatever the gaps are.
这篇关于MySQL"IN"运算符的性能(大?)值的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
