我今天偶然发现了这个，非常震惊。在搜索谷歌时，我通常会看到这个问题被尊敬 - 使用limit导致它返回得更慢。
我有一个MySQL表，里面有几百万行。 PK是id的，因此它是一个唯一的索引。
当我执行表单查询时select a, b, c, ... from table where id in (1, 2, 3, ..., 5000)获取所有结果大约需要 15-20 分钟。 但是，当我只是在末尾添加limit 1000000时(我故意使用了比需要的更大的数字)，它会在几秒钟内返回。
我知道使用比返回的帮助更少的limit，因为它会在"配额"填满后立即返回，但在这里我找不到如此显着改进的原因.
任何人都可以解释一下吗？
我应该为每个查询添加一个limit来提高其性能吗？
为什么MySQL搜索没有相同的内容？
更新
根据要求对每个解释：
有限制(需要几秒钟)
{
"id" : 1,
"select_type" : "SIMPLE",
"table" : "table",
"partitions" : null,
"type" : "range",
"possible_keys" : "PRIMARY",
"key" : "PRIMARY",
"key_len" : "4",
"ref" : null,
"rows" : 4485,
"filtered" : 100.0,
"Extra" : "Using where"
}
无限制(需要15-20分钟)
{
"id" : 1,
"select_type" : "SIMPLE",
"table" : "table",
"partitions" : null,
"type" : "ALL",
"possible_keys" : "PRIMARY",
"key" : null,
"key_len" : null,
"ref" : null,
"rows" : 69950423,
"filtered" : 50.0,
"Extra" : "Using where"
}
我对此并不流利，但看起来它在我使用limit时使用了密钥，但在没有它的情况下运行时却没有.
可能是filtered和type字段中的其他差异，我不知道它们是什么意思.
怎么来了？
更新 2
问了很多问题，所以我将尝试为所有人提供详细信息。
MySQL版本是8.0.28，表引擎是InnoDB.
我已经一个接一个地运行了几次测试，而不仅仅是一次。
在IN子句中使用较少 (10) 个值运行相同的EXPLAIN，对于有limit和没有它，都返回相同的结果！
{
"id" : 1,
"select_type" : "SIMPLE",
"table" : "table",
"partitions" : null,
"type" : "range",
"possible_keys" : "PRIMARY",
"key" : "PRIMARY",
"key_len" : "4",
"ref" : null,
"rows" : 10,
"filtered" : 100.0,
"Extra" : "Using where"
}
现在是FORMAT=JSON(带有编辑的部分)：
无限制
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "8369910.88"
},
"table": {
"table_name": "table",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 70138598,
"rows_produced_per_join": 35069299,
"filtered": "50.00",
"cost_info": {
"read_cost": "4862980.98",
"eval_cost": "3506929.90",
"prefix_cost": "8369910.88",
"data_read_per_join": "558G"
},
"used_columns": [...],
"attached_condition": "(`db`.`table`.`id` in (...))"
}
}
}
有限制
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "8371410.92"
},
"table": {
"table_name": "table",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 4485,
"rows_produced_per_join": 35069255,
"filtered": "100.00",
"cost_info": {
"read_cost": "4864485.17",
"eval_cost": "3506925.54",
"prefix_cost": "8371410.92",
"data_read_per_join": "558G"
},
"used_columns": [...],
"attached_condition": "(`db`.`table`.`id` in (...))"
}
}
}
由于评论中的帖子下有一个很长的线程，我将在这里添加既是我的又是@Bill的答案，看起来问题在声明IN()部分是一个很长的参数列表。
罪魁祸首是更改range_optimizer_max_mem_size参数编号以容纳IN中的更多输入，因为超过该参数将导致全表扫描。
范围优化是为范围扫描保留内存，因此没有足够的内存集 - 将导致全表扫描
现在为什么 LIMIT 子句会让它发生 - 这部分我猜：
- LIMIT 迫使 MySQL 使用不同的范围扫描类型
- LIMIT 实际上是在限制将返回的资源数量，因此 MySQL 会知道它不会返回超过 X，而没有限制，它会假设它可以返回69950423这将超过您设置的其他一些内存限制，值得尝试限制等于表中的行数