MySQL“in 子句”内的项目数
我有三个表来定义用户：
USER: user_id (int), username (varchar)
USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar)
USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar)
我想创建一个中间层用户，该用户对应用程序中的其他用户具有一定的访问权限。为了确定登录用户可以访问哪些用户，我使用如下子查询：
SELECT user_id FROM user WHERE user_id
IN (SELECT user_id
FROM user_metadata
WHERE user_metadata_field_id = 1 AND field_value = 'foo')
目前，我将子查询字符串存储在变量中，然后每次需要拉取用户列表时将其动态插入到外部查询中。完成此操作后，我想，“最好只存储实际的 user_id 字符串”。
因此，不是将其存储在变量中...
$subSql = "SELECT user_id FROM user_metadata WHERE user_metadata_field_id = 1 AND field_value = 'foo'";
...我实际上执行查询并像这样存储结果...
$subSql = "12, 56, 89, 100, 1234, 890";
然后，当我需要提取登录用户有权访问的一些用户时，我可以这样做所以：
$sql = "SELECT user_id FROM user WHERE user_id IN ($subSql)";
最后是问题：
在 MySQL IN CLAUSE 中可以使用多少个项目？每次执行外部查询时，存储实际的 id 而不是子 sql 语句必须更快，对吧？
I have three tables to define users:
USER: user_id (int), username (varchar)
USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar)
USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar)
I'd like to create a middle tier user that has certain access to other users within the application. To determine which users the logged in use can access, I am using a subquery like the following:
SELECT user_id FROM user WHERE user_id
IN (SELECT user_id
FROM user_metadata
WHERE user_metadata_field_id = 1 AND field_value = 'foo')
Currently I am storing the subquery string in a variable and then dynamically inserting it into the outer query each time I need to pull a list of users. After doing this I thought, "it has got to be better to just store a string of the actual user_ids".
So instead of storing this in a variable...
$subSql = "SELECT user_id FROM user_metadata WHERE user_metadata_field_id = 1 AND field_value = 'foo'";
... I actually perform the query and store the result like this...
$subSql = "12, 56, 89, 100, 1234, 890";
Then when I need to pull a lit of users that the logged in user has access to, I can do so with:
$sql = "SELECT user_id FROM user WHERE user_id IN ($subSql)";
And finally the questions:
How many items can you use in a MySQL IN CLAUSE? Storing the actual ids instead of the sub-sql statement has got to be faster for performing that outer query each time, right?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
从 手册：
IN 中的值的数量列表仅受max_allowed_packet值。From the manual:
The number of values in the
INlist is only limited by themax_allowed_packetvalue.从某个数字开始，
IN表速度更快。MySQL 的代码中有一些东西使得构建大量常量值的范围比在嵌套循环中执行相同操作要慢。
有关性能详细信息，请参阅我的博客中的这篇文章：
Starting from a certain number, the
INtables are faster.MySQLhas something inside its code that makes building a range over a large number of constant values slower than doing the same in a nested loop.See this article in my blog for performance details:
正如 Quassnoi 的回应中所暗示的，在达到给定 MySql 版本的实现所强加的任何可能的限制之前，会偶然发现其他实际考虑因素 (*)。因此，随着管理用户数量（或可能需要 IN 构造的其他标准）的增长，人们应该寻求使用文字“IN”的替代方案，例如使用临时（甚至永久）表。
由于您正在考虑对“管理员用户”标准进行特殊处理，出于性能目的，我想提供一条评论和建议。
评论：这可能是过早优化的情况吗？
我不知道这个数据库的具体情况、它的体积、复杂性等。而且，是的，我知道 EAV（实体属性值）格式的一些性能贡献，但我认为即使对于成功的企业来说，帐户数据库的用户数量也很少超过 10,000 个。因此，即使每个用户有很多属性，我们仍然会考虑相对较小的 EAV 表，这可能不需要这种类型的优化。 （另一方面，其他一些优化技巧在其他领域可能会受到欢迎）。
此外，相对于其他查询，典型的用例涉及对帐户数据库的相对较少的查询，因此这是推迟的另一个原因应用程序的帐户相关功能的任何重要性能考虑。
建议：也许使用“重新规范化属性”
对于单值属性，特别是如果它们很短，则可以在实体表（本例中为“USER”表）中移动（或复制）它们。这在插入或更新项目时引入了一些逻辑，但这与许多联接（或子查询）相同，并且还提供了考虑多字段索引以支持最常见用例的机会。
(*) 有限制吗？
我还没有读过任何这样的限制；我知道 Oracle 有时有 1,000 个限制，而 MSSQL 没有；当然，所有服务器都有基于 SQL 语句总长度的限制，但这是一个非常大的数字！如果有人偶然发现这个问题，他/她还有其他问题......;-)
As hinted in Quassnoi's response, one stumbles upon other practical considerations, before hitting any possible limit imposed by a given MySql version's implementation (*). Therefore, as the number of admin users (or other criteria which may require an IN construct) grows, one should seek to use alternatives to a literal "IN", such as the use of temporary (or even permanent) tables.
Since you are considering special handling of the "admin user" criteria, for performance purposes, I'd like to offer an comment and a suggestion.
Comment: Could this be a case of premature optimization?
I'm unaware of the specifics of this database, its volume, complexity etc. And, yes, I am aware of some the performance tribute to be paid to the EAV (Entity-Attribute-Value) format, but I'm thinking that even for successful businesses, the accounts database rarely counts in excess of 10,000 users. So even with very many attributes per user we're still looking at a relatively small EAV table, which may not require this type of optimization. (On the other hand a few other optimization tricks may be welcome in other areas).
Furthermore, typical use cases, involve a relative few inquiries into the account database, relative to other queries, and this is therefore another reason to deffer any non trivial performance consideration for the accounts-related features of the application.
Suggestion: Maybe use "re-normalized attributes"
For attributes that are singled-valued, and in particular if they are short, they can be moved (or duplicated) in the Entity table ('USER' table in this case). This introduces a bit of logic at the time items are inserted or updated, but this sames many joins (or subqueries) and also provides opportunities to consider multi-field indexes to support the most common use cases.
(*) Is there a limt?
I haven't read about any such a limit; I know Oracle has (had) a 1,000 limit at some time, MSSQL doesn't; of course all servers do have a limit based on the overall length of the SQL statement, but this is a really big number! if one ever stumble upon that one, he/she has other problems... ;-)
MySQL的IN子句本身没有这样的限制。我尝试了 8000 个元素，它对我来说效果很好。堆栈溢出错误可能是声明的变量引起的，
MySQL's IN Clause itself doesn't have such limit. I tried with 8000 elements its work fine for me. Stack overflow error could be of variable declared,
如果
IN()子句中的值超过 1000 个，MariaDB 似乎会自动创建临时表以提高性能。您可以使用EXPLAIN查看这一点。If you have more than 1000 values within the
IN()clause MariaDB seems to automatically create temporary tables for performance improvement. You can see this usingEXPLAIN.