为什么在对索引字段进行选择时使用IN(...)，会杀死SELECT查询的性能? [英] Why using IN(...) when selecting on indexed fields, will kill the performance of SELECT query?

问题描述

在索引字段中进行选择时避免使用IN(...)，这会破坏SELECT查询的性能.

Avoid using IN(...) when selecting on indexed fields, It will kill the performance of SELECT query.

我在这里找到了它: https://wikis.oracle.com/pages /viewpage.action?pageId=27263381

您能解释一下吗?为什么那样会降低性能?我应该用什么代替IN.也许是"OR"语句?

Can you explain it? Why that will kill performance? And what should I use instead of IN. "OR" statement maybe?

推荐答案

说实话，该陈述与我在MySQL的书籍和文章中阅读的许多提示相矛盾.

To tell the truth, that statement contradicts to many hints that I have read in books and articles on MySQL.

这里是一个示例:此外， expr IN(value，... )本身具有处理大型值列表的其他增强功能，因为它可以用作某些range查询的有用替代方法:

Moreover, expr IN(value, ...) itself has additional enhancements for dealing with large value lists, since it is supposed to be used as a useful alternative to certain range queries:

如果所有值都是常量，则根据expr的类型对其求值并进行排序.然后使用二进制搜索完成对项目的搜索.这意味着，如果IN值列表完全由常量组成，则IN很快.

If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants.

仍然过度使用IN可能会导致查询速度变慢. 本文中指出了某些情况.

Still overusing INs may result in slow queries. Some cases are noted in the article.

这篇关于为什么在对索引字段进行选择时使用IN(...)，会杀死SELECT查询的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！