通过具有良好性能的唯一列选择多行

I want to select about 20 to 100 rows from a table using four columns which make up an unique index.

我想从一个表中选择大约20到100行,使用四列构成一个唯一索引。

The first approach I came up with was using OR:

我想出的第一种方法是使用OR:

SELECT ...
WHERE (w_id = ? AND type_id = ? AND object_id = ? AND part_name = ?)
   OR (w_id = ? AND type_id = ? AND object_id = ? AND part_name = ?)
   OR [...]

I've also seen a solution using row constructors:

我也看到了使用行构造函数的解决方案:

SELECT ...
WHERE (w_id, type_id, object_id, part_name) IN ((1,2,3,''),(1,2,4,''), [...])

However, it was said that this has a bad performance when selecting many rows.

但是,据说这在选择多行时表现不佳。

Which solution has the better performance or should I use an other solution (such as splitting the queries)?

哪种解决方案具有更好的性能,还是应该使用其他解决方案(例如拆分查询)?

Thanks in advance!

提前致谢!

Table structure

CREATE TABLE page(
    page_id PRIMARY KEY AUTO_INCREMENT,
    w_id INTEGER NOT NULL,
    ns_id INTEGER NOT NULL,
    type_id INTEGER NOT NULL,
    object_id INTEGER NOT NULL,
    part_name VARCHAR(20) NOT NULL,
);

Both w_id and ns_id are foreign keys.

w_id和ns_id都是外键。

There is only one index, which is the unique index containing the columns w_id, type_id, object_id and part_name.

只有一个索引,它是包含列w_id,type_id,object_id和part_name的唯一索引。

Measurement

I filled the table with about 700k rows and ran three querys (also one with using UNION). I always queried for the same rows. These are the results:

我用大约700k行填充了表格并运行了三个查询(也使用了UNION)。我一直在查询相同的行。结果如下:

Solution  Time [s]  EXPLAIN
with OR   0.0003    ref
with IN   0.4546    ALL
UNION     0.0004    const

Raymond's presumption that using an IN could cause a full table scan was proved. However, the results show that OR and UNION show quite the same time.

Raymond推测使用IN可能会导致全表扫描。但是,结果表明OR和UNION表现出相同的时间。

What also makes me worry is the fact that these results only were achieved after a defragmentation. Before the defragmentation even the solution with OR took about 0.3 s.

让我担心的是,这些结果仅在碎片整理后实现。在碎片整理之前,甚至OR的解决方案也需要大约0.3秒。

1 个解决方案

#1

Selecting using =, AND and OR/UNION ALL works faster than using IN in combination with row constructor. IN forces a full table scan which makes it a few times slower than the other solution.

选择使用=,AND和OR / UNION ALL比使用IN与行构造函数组合更快。 IN强制进行全表扫描,这使得它比其他解决方案慢几倍。

However, on the recommendation of wildplasser:

但是,根据wildplasser的建议:

"the need for UNION and/or AND/OR clauses are often the result of a sub-optimal data model"

“对UNION和/或AND / OR子句的需求通常是次优数据模型的结果”

I will change my data base model so there is no need for selecting the rows in this way but rather I'll use a join.

我将更改我的数据库模型,因此不需要以这种方式选择行,而是我将使用连接。

#1