代码之家 › 专栏 › 技术社区 › Sasha Chedygov

mysql/postgresql中in关键字的速度

list postgresql performance mysql

Sasha Chedygov · 技术社区 · 16 年前

我听过很多人说 IN 大多数关系数据库中的关键字速度很慢。这是怎么回事?一个示例查询是这样的,在我的头上:

SELECT * FROM someTable WHERE someColumn IN (value1, value2, value3)

我听说这样做要慢得多:

SELECT * FROM someTable WHERE
  someColumn = value1 OR
  someColumn = value2 OR
  someColumn = value3

这是真的吗?或者速度差可以忽略不计?如果重要的话,我使用的是PostgreSQL,但是我也想知道MySQL的价格(如果有什么不同的话)。事先谢谢。

7 回复 | 直到 16 年前

Greg Smith 16 年前

在PostgreSQL中,您将在这里得到的具体内容取决于基础表,因此您应该针对有用的数据子集,对一些示例查询使用explain analyze,以准确地了解优化器将要做什么(确保对您正在运行的表也进行了分析)。在中可以通过两种不同的方式进行处理,这就是为什么您需要查看一些示例来确定数据使用的是哪种替代方法。你的问题没有简单的一般答案。

对于您在修订版中添加的特定问题,与不涉及索引的普通数据集相比,下面是两个查询计划的示例:

postgres=# explain analyze select * from x where s in ('123','456');
 Seq Scan on x  (cost=0.00..84994.69 rows=263271 width=181) (actual time=0.015..1819.702 rows=247823 loops=1)
   Filter: (s = ANY ('{123,456}'::bpchar[]))
 Total runtime: 1931.370 ms

postgres=# explain analyze select * from x where s='123' or s='456';
 Seq Scan on x  (cost=0.00..90163.62 rows=263271 width=181) (actual time=0.014..1835.944 rows=247823 loops=1)
   Filter: ((s = '123'::bpchar) OR (s = '456'::bpchar))
 Total runtime: 1949.478 ms

这两个运行时本质上是相同的,因为实际处理时间主要由表中的顺序扫描控制;多次运行表明这两个运行时的差异低于运行时的误差范围。如您所见,PostgreSQL将In-Case转换为使用它的any过滤器,它的执行速度应该总是比一系列ORS快。同样,这种小的情况不一定代表您将在涉及索引等的严重查询中看到的情况。无论如何,用一系列OR语句手动替换INS永远不会更快,因为如果优化器有好的数据可以使用,那么它知道在这里要做的最好的事情。

一般来说,PostgreSQL比MySQL优化器知道更多关于如何优化复杂查询的技巧,但它也很大程度上依赖于您给优化器足够的数据来处理。PostgreSQL wiki“性能优化”部分的第一个链接涵盖了从优化器获得良好结果所需的最重要的内容。

Quassnoi 16 年前

在 MySQL ,这些是优化器的完整同义词:

SELECT  *
FROM    someTable
WHERE   someColumn IN (value1, value2, value3)

和

SELECT  *
FROM    someTable
WHERE   someColumn = value1 OR
        someColumn = value2 OR
        someColumn = value3

,前提是 value 的是文本内容或预设变量。

根据 documentation :

单个零件索引的范围条件定义如下:

对于两者 BTREE 和 HASH 索引,当使用 = , <=> , IN() , IS NULL 或 IS NOT NULL 运算符。

艾斯

对于所有类型的索引,多个范围条件与 OR 或 AND 形成一个范围条件。

_常量值_在前面的描述中是指以下其中一种:

查询字符串中的常量

来自同一联接的常量或系统表的列

不相关子查询的结果

完全由上述类型的子表达式组成的任何表达式

但是,此查询:

SELECT  *
FROM    table
WHERE   id = 1
        OR id = (SELECT id FROM other_table WHERE unique_condition)

将使用上的索引 id ,而这个:

SELECT  *
FROM    table
WHERE   id IN (1, (SELECT id FROM other_table WHERE unique_condition))

将使用完全扫描。

也就是说,当 价值 's是单行子查询。

我最近把它作为 bug 45145 在里面 MySQL (结果是 5.2 特定,不存在于 5.1 并在 6.0 )

Brent Baisley 16 年前

在中使用并不一定很慢,而是您如何构建将大大降低速度的内置参数。人们经常使用select…其中x in(选择…,这可能是非常糟糕的优化(即根本不优化)。在“相关子查询”上搜索,看看它会有多糟。

通常,您根本不必使用,而是可以使用联接,并利用派生表。

SELECT * FROM table1 WHERE x IN (SELECT y FROM table2 WHERE z=3)

可以这样重新表述

SELECT * FROM table1 JOIN (SELECT y FROM table2 WHERE z=3) AS table2 ON table1.x=table2.y

如果in语法很慢,那么join语法通常会快很多倍。您可以使用Explain查看每个查询将如何进行不同的优化。这是一个简单的示例,您的数据库可能显示相同的查询路径,但更复杂的查询通常显示不同的内容。

derobert 16 年前

在用嵌套select语句时通常比较慢。在WITH中,值列表的速度不应低于someColumn=value1或someColumn=value2或someColumn=value3等。只要值的数量正常,速度就足够快。

在WITHA子查询中,当优化器无法找到执行查询的好方法时速度很慢,并且必须使用明显的方法来构建子查询的完整结果。例如:

SELECT username
  FROM users
  WHERE userid IN (
    SELECT userid FROM users WHERE user_first_name = 'Bob'
  )

会比

SELECT username FROM users WHERE user_first_name = 'Bob'

除非优化器能理解您的意思。

joedevon 16 年前

我想你得到了你上面想要的答案。只想增加一件事。

您需要优化并以正确的方式使用它。在开发过程中,每当有查询时,我总是在页面的底部设置一个调试部分,它会自动在每次选择时运行explain extended,然后显示警告,以查看MySQL的查询优化器在内部重写查询的方式(可能)。从中可以学到很多关于如何确保你的工作。

Matthew Vines 16 年前

in关键字的速度实际上取决于子查询的复杂性。在您提供的示例中,您只想查看somecolumns值是否在一组值列表中,以及一个相当短的值列表中。所以我可以想象,在这种情况下,性能成本会非常低。

Greg 16 年前

医生说 IN 在MySQL中速度很快,但目前找不到源代码。