解释告诉我们查询很糟糕（它不使用单个键），但我正在使用 LIMIT 1。这是一个问题吗？
带查询的解释命令：
explain SELECT * FROM leituras
WHERE categorias_id=75 AND
textos_id=190304 AND
cookie='3f203349ce5ad3c67770ebc882927646' AND
endereco_ip='127.0.0.1'
LIMIT 1
结果：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE leituras ALL (null) (null) (null) (null) 1022597 Using where
在表上添加一些键会有什么不同吗？即使查询始终只返回一行。
The explain command with the query:
explain SELECT * FROM leituras
WHERE categorias_id=75 AND
textos_id=190304 AND
cookie='3f203349ce5ad3c67770ebc882927646' AND
endereco_ip='127.0.0.1'
LIMIT 1
The result:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE leituras ALL (null) (null) (null) (null) 1022597 Using where
Will it make any difference adding some keys on the table? Even that the query will always return only one row.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
回答你的问题，是的。您应该在必要时在
WHERE子句中出现的列上添加索引（感谢 @Col.Shrapnel） - 在本例中为categorias_id，textos_id、cookie和endereco_ip。如果您始终在
WHERE子句中使用相同的 3 列执行查询，则一次性添加包含 3 列的索引可能比添加单独的索引更有利。In answer to your question, yes. You should add indexes where necessary (thanks @Col.Shrapnel) on the columns that appear in your
WHEREclause - in this case,categorias_id,textos_id,cookie, andendereco_ip.If you always perform a query using the same 3 columns in the
WHEREclause, it may be beneficial to add an index which comprises the 3 columns in one go, rather than adding individual indexes.它仍然需要对表进行线性搜索，直到找到这一行。因此添加索引可以显着提高性能。
It still has to do a linear search over the table until it finds that one row. So adding indexes could noticeably improve performance.
是的，当您只想返回一行时，索引甚至更加重要。
如果您返回一半的行并且数据库系统必须扫描整个表，那么您的效率仍然是 50%。
但是，如果您只想返回一行，并且数据库系统必须扫描 1022597 行才能找到您的行，那么您的效率微乎其微。
LIMIT 1 确实提供了一些效率，因为它在找到第一个匹配行后立即停止，但显然它必须扫描大量记录才能找到第一行。
为
WHERE子句中的每一列添加索引可以让数据库系统避免扫描与您的条件不匹配的行。有了足够的索引，您将看到解释中的行列将更接近返回的实际行数。使用覆盖
WHERE子句中所有四列的复合索引可以实现更好的性能并减少扫描，因为索引将提供完全覆盖。复合索引确实会使用大量内存并对插入性能产生负面影响，因此，如果大部分查询重复查找同一列，或者很少插入记录，或者很少插入记录，您可能只想添加复合索引。对于您来说，特定查询的速度非常重要。提高性能的另一种方法是仅返回所需的列，而不是使用
SELECT *。如果您在这四列上有一个复合索引，并且只返回这四列，那么您的数据库系统根本不需要访问您的记录。数据库系统可以从索引中获取所需的一切。Yes, indexes are even more important when you want to return only one row.
If you are returning half of the rows and your database system has to scan the entire table, you're still at 50% efficiency.
However, if you want to return just one row, and your database system has to scan 1022597 rows to find your row, your efficiency is minuscule.
LIMIT 1does offer some efficiency in that it stops as soon as it finds the first matching row, but it obviously has to scan an enormous number of records to find that first row.Adding an index for each of the columns in your
WHEREclause allows your database system to avoid scanning rows that don't match your criteria. With adequate indexes, you'll see that the rows column in the explain will get closer to the actual number of returned rows.Using a compound index that covers all four of the columns in your
WHEREclause allows even better performance and less scanning, as the index will provide full coverage. Compound indexes do use a lot of memory and negatively affect insert performance, so you might only want to add a compound index if a large percentage of your queries repeatedly do a look up on the same columns, or if you rarely insert records, or it's just that important to you for that particular query to be fast.Another way to improve performance is to return only the columns that you need rather than using
SELECT *. If you had a compound index on those four columns, and you returned only those four columns, your database system wouldn't need to hit your records at all. The database system could get everything it needed right from the indexes.