MySQL 多查询或使用 IN(value1,value2,value3) [英] MySQL Multiple Queries or use the IN(value1,value2,value3)

问题描述

简单介绍:

我有一张表 t1( id(mediumint), string(varchar(45) ) 都属于主键.

I have one table t1( id(mediumint), string(varchar(45) ) both belong to primary key.

我需要在这个表中找到 5 个字符串，比方说.而这张表就像 5M + 行.

I need to find in this table 5 strings, let's say. And this table is like 5M + rows.

什么会更快?

1. 一个使用 IN() 的查询:

SELECT id, string FROM t1 WHERE string IN (value1,value2,...,value5)

或

五个查询，每个值一个:

Five queries, one for each value:

SELECT id, string FROM t1 WHERE string = value1
SELECT id, string FROM t1 WHERE string = value2
'
'
SELECT id, string FROM t1 WHERE string = value5

应用服务器和数据库服务器将在同一个网络上(100Mbit 或 1Gbit，还不确定)，而不是在同一台机器上.

The application server and database server will be on the same network (100Mbit or 1Gbit, not sure yet), not on the same machine.

推荐答案

大多数时候，使用单个查询可能比使用多个查询更快，因为它避免了解析、发送查询和接收多个查询的开销单独的响应，它允许更好的优化.如有疑问，请提供个人资料.

Most of the time, using a single query is likely to be faster than using multiple queries, since it avoids the overhead of parsing, sending the queries and receiving multiple separate responses, and it allows for better optimization. When in doubt, profile.

还要记住查询的长度.大多数数据库服务器对查询字符串的长度有限制.因此，如果您有很长的值列表，您应该分批发送它们.

Also keep in mind the length of the query. Most database servers have a limit of how long the query string can be. So, if you have long lists of values, you should send them in batches.

这篇关于MySQL 多查询或使用 IN(value1,value2,value3)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！