从ID列表中选择数千行的最有效方法 [英] Most efficient way of selecting thousands of rows from a list of ids

问题描述

是否有使用mysql IN或类似方法选择数千行的可伸缩方法?

Are there any scalable ways of selecting thousands of rows using mysql IN or something similar?

例如

SELECT * FROM awesomeTable WHERE id IN (1,2,3,4......100000)

这可能还是我只是在做梦?模式是InnoDB，如果另一个可以提供更具扩展性的解决方案，则可以更改.

Is this possible or am I just dreaming? Schema is InnoDB, can be changed if another would provide a more scalable solution.

作为参考，我从Solr返回的一组ID中获取搜索结果.我想使用mysql进行最终检索，因为这将使对这些结果的排序和最终过滤更加容易(我不会详细说明为什么).

For reference I am getting search results from a set of ids returned from Solr. I'd like to use mysql for the final retrieval as it would make sorting and final filtering of these results much easier (I won't get into the details of why).

该查询可以使用LIMIT子句，只要IN仍包含所有100000个ID

The query could be use a LIMIT clause as long as the IN still contained all 100000 of the ids

例如

SELECT * FROM awesomeTable WHERE id IN (1,2,3,4......100000) LIMIT 10;

推荐答案

我就是我，并不真正了解您的框架，您应该尝试将这些ID插入临时表，并通过简单的内部联接使用该表.事实证明可以和这个IN一样快.

I that were me, and not really knowing your framework, you should try inserting those ID into a temporary table and using this table with a simple inner join. That could prove to be as fast as this IN.

这篇关于从ID列表中选择数千行的最有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！