Will limiting a query to one result record, improve performance in a large(ish) MySQL table if the table only has one matching result?
如果一个表只有一个匹配的结果，那么将一个查询限制在一个结果记录中，会提高一个大型(ish) MySQL表的性能吗?
for example
例如
select * from people where name = "Re0sless" limit 1
if there is only one record with that name? and what about if name was the primary key/ set to unique? and is it worth updating the query or will the gain be minimal?
如果只有一条记录有这个名字?如果名称是主键/设置为unique呢?更新查询是否值得，还是收益最小?
6 个解决方案
#1
36
If the column has
如果列
a unique index: no, it's no faster
唯一的索引:不，不会更快
a non-unique index: maybe, because it will prevent sending any additional rows beyond the first matched, if any exist
非唯一索引:可能，因为它将防止发送超出第一个匹配行之外的任何其他行(如果存在的话)
no index: sometimes
没有索引:有时
- if 1 or more rows match the query, yes, because the full table scan will be halted after the first row is matched.
- 如果1行或更多行与查询匹配，则为yes，因为在第一行匹配后将停止完整的表扫描。
- if no rows match the query, no, because it will need to complete a full table scan
- 如果没有行匹配查询，则不需要，因为它需要完成一个完整的表扫描。
#2
3
If you have a slightly more complicated query, with one or more joins, the LIMIT clause gives the optimizer extra information. If it expects to match two tables and return all rows, a hash join is typically optimal. A hash join is a type of join optimized for large amounts of matching.
如果您有一个稍微复杂一点的查询，包含一个或多个连接，那么LIMIT子句将为优化器提供额外的信息。如果它期望匹配两个表并返回所有行，那么散列连接通常是最优的。散列连接是为大量匹配而优化的连接类型。
Now if the optimizer knows you've passed LIMIT 1, it knows that it won't be processing large amounts of data. It can revert to a loop join.
现在，如果优化器知道您已经通过了LIMIT 1，它就知道它不会处理大量的数据。它可以恢复到循环连接。
Based on the database (and even database version) this can have a huge impact on performance.
基于数据库(甚至数据库版本)，这会对性能产生巨大的影响。
#3
2
To answer your questions in order: 1) yes, if there is no index on name. The query will end as soon as it finds the first record. take off the limit and it has to do a full table scan every time. 2) no. primary/unique keys are guaranteed to be unique. The query should stop running as soon as it finds the row.
按顺序回答问题:1)是的，如果没有名字索引的话。一旦找到第一个记录，查询就会结束。去掉限制，每次都要进行全表扫描。2)没有。主键/唯一键保证是唯一的。查询一旦找到行，就应该停止运行。
#4
0
I believe the LIMIT is something done after the data set is found and the result set is being built up so I wouldn't expect it to make any difference at all. Making name the primary key will have a significant positive effect though as it will result in an index being made for the column.
我相信这个极限是在找到数据集之后完成的，结果集正在构建，所以我不会期望它会产生任何影响。将name作为主键将产生显著的积极影响，因为它将导致为列创建索引。
#5
0
If "name" is unique in the table, then there may still be a (very very minimal) gain in performance by putting the limit constraint on your query. If name is the primary key, there will likely be none.
如果“name”在表中是惟一的，那么通过对查询设置限制，仍然可以获得(非常非常小的)性能收益。如果名称是主键，则很可能没有。
#6
0
Yes, you will notice a performance difference when dealing with the data. One record takes up less space than multiple records. Unless you are dealing with many rows, this would not be much of a difference, but once you run the query, the data has to be displayed back to you, which is costly, or dealt with programmatically. Either way, one record is easier than multiple.
是的，在处理数据时，您将注意到性能差异。一条记录占用的空间比多条记录要少。除非您正在处理许多行，否则这不会有太大的差别，但是一旦您运行查询，数据就必须显示给您，这是昂贵的，或者以编程方式处理。无论哪种方式，一条记录都比多条记录简单。
#1
36
If the column has
如果列
a unique index: no, it's no faster
唯一的索引:不，不会更快
a non-unique index: maybe, because it will prevent sending any additional rows beyond the first matched, if any exist
非唯一索引:可能，因为它将防止发送超出第一个匹配行之外的任何其他行(如果存在的话)
no index: sometimes
没有索引:有时
- if 1 or more rows match the query, yes, because the full table scan will be halted after the first row is matched.
- 如果1行或更多行与查询匹配，则为yes，因为在第一行匹配后将停止完整的表扫描。
- if no rows match the query, no, because it will need to complete a full table scan
- 如果没有行匹配查询，则不需要，因为它需要完成一个完整的表扫描。
#2
3
If you have a slightly more complicated query, with one or more joins, the LIMIT clause gives the optimizer extra information. If it expects to match two tables and return all rows, a hash join is typically optimal. A hash join is a type of join optimized for large amounts of matching.
如果您有一个稍微复杂一点的查询，包含一个或多个连接，那么LIMIT子句将为优化器提供额外的信息。如果它期望匹配两个表并返回所有行，那么散列连接通常是最优的。散列连接是为大量匹配而优化的连接类型。
Now if the optimizer knows you've passed LIMIT 1, it knows that it won't be processing large amounts of data. It can revert to a loop join.
现在，如果优化器知道您已经通过了LIMIT 1，它就知道它不会处理大量的数据。它可以恢复到循环连接。
Based on the database (and even database version) this can have a huge impact on performance.
基于数据库(甚至数据库版本)，这会对性能产生巨大的影响。
#3
2
To answer your questions in order: 1) yes, if there is no index on name. The query will end as soon as it finds the first record. take off the limit and it has to do a full table scan every time. 2) no. primary/unique keys are guaranteed to be unique. The query should stop running as soon as it finds the row.
按顺序回答问题:1)是的，如果没有名字索引的话。一旦找到第一个记录，查询就会结束。去掉限制，每次都要进行全表扫描。2)没有。主键/唯一键保证是唯一的。查询一旦找到行，就应该停止运行。
#4
0
I believe the LIMIT is something done after the data set is found and the result set is being built up so I wouldn't expect it to make any difference at all. Making name the primary key will have a significant positive effect though as it will result in an index being made for the column.
我相信这个极限是在找到数据集之后完成的，结果集正在构建，所以我不会期望它会产生任何影响。将name作为主键将产生显著的积极影响，因为它将导致为列创建索引。
#5
0
If "name" is unique in the table, then there may still be a (very very minimal) gain in performance by putting the limit constraint on your query. If name is the primary key, there will likely be none.
如果“name”在表中是惟一的，那么通过对查询设置限制，仍然可以获得(非常非常小的)性能收益。如果名称是主键，则很可能没有。
#6
0
Yes, you will notice a performance difference when dealing with the data. One record takes up less space than multiple records. Unless you are dealing with many rows, this would not be much of a difference, but once you run the query, the data has to be displayed back to you, which is costly, or dealt with programmatically. Either way, one record is easier than multiple.
是的，在处理数据时，您将注意到性能差异。一条记录占用的空间比多条记录要少。除非您正在处理许多行，否则这不会有太大的差别，但是一旦您运行查询，数据就必须显示给您，这是昂贵的，或者以编程方式处理。无论哪种方式，一条记录都比多条记录简单。