在删除查询中使用大表的 IN 子句中的子查询性能 [英] Performance of sub-query in IN clause with large tables in delete query

问题描述

我想知道，为什么使用 IN-Operator 的查询比简单的 SELECT 慢得多.

让我用一个例子来证明我的观察:

Query1:SELECT VIDEO_ID FROM 视频(大约 8000 行，1 列)查询 2:从 video_snapshot 中删除 video_snapshot.VIDEO_ID IN(查询 1)

video_snapshot 是一个非常大的表，有超过 7.000.000 行，但 VIDEO_ID 已编入索引，因此在 WHERE 子句中使用 VIDEO_ID 进行查询已经足够快了.

IN-Operator 如何工作?我猜这只是几个 WHERE 子句的简短形式.

我在 XAMPP 上使用 MariaDB 10.1.16

解决方案

对于大数据集表IN子句性能很慢这种情况下可以使用INNER JOIN 带删除查询

从 video_snapshot 中删除 video_snapshot内连接视频 on video_snapshot.VIDEO_ID=videos.VIDEO_ID;

<块引用>

根据我的经验 IN 是一个非常慢的操作符，因为 SQL 通常将其计算为由OR"分隔的一系列 WHERE 子句(WHERE x=YOR x=Z OR...).. 但是当需要将字段与其他表字段进行比较时，主要是显式连接更快.

I am wondering, why queries with IN-Operator are much slower than a simple SELECT.

Let me demonstrate my oberservations on an example:

Query1: SELECT VIDEO_ID FROM videos (about 8000 rows with 1 column)

Query2: DELETE FROM video_snapshot WHERE video_snapshot.VIDEO_ID IN (Query1)

video_snapshot is a very big table with over 7.000.000 rows but VIDEO_ID is indexed, so querys with VIDEO_ID in a WHERE-clause are fast enough.

How does the IN-Operator work? I guessed that this is just a short form for serveral WHERE clauses.

I am using MariaDB 10.1.16 on XAMPP

解决方案

For large data-set table IN clause performance is very slow in this case you can use INNER JOIN with delete query

DELETE video_snapshot FROM video_snapshot
INNER JOIN videos ON video_snapshot.VIDEO_ID=videos.VIDEO_ID;

In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by "OR" (WHERE x=Y OR x=Z OR...).. BUT mainly explicit join is faster when need to compare field with other table field.

这篇关于在删除查询中使用大表的 IN 子句中的子查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！