如何以有效的方式摆脱带有列表的mysql表中的冗余

Question

前段时间我做了一个快速的MVP，现在已经成为一个更现实的项目。现在我正在重构和改进它。我有一张这样的桌子

CREATE TABLE `records` (
`id` int(11) NOT NULL,
`type` int(11) NOT NULL,
.....
`ref` int(11) DEFAULT NULL,
`enabled` tinyint(1) NOT NULL DEFAULT '1',
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `mrecord`
ADD PRIMARY KEY (`id`),
ADD KEY `type` (`type`);
ADD KEY `ref` (`ref`);

ref是对以前id的引用，如果没有，则为 null，enabled让我知道这个项目是否是该类型的最新版本。关键是，当项目类型X被替换为新项目类型时，旧项目将被禁用(enabled= 0(，而新项目将ref设置为旧项目类型id。

例如，我们有这个包含 3 种类型的项目的表格：

--------------------------------------------
| ID | type | other columns | ref  | enabled |
|--------------------------------------------|
| 1  |   1  |               | null |    1    |
| 2  |   2  |               | null |    1    |
| 3  |   3  |               | null |    1    |
--------------------------------------------

现在我们添加一个替换项目类型 2 的新项目版本：

--------------------------------------------
| ID | type | other columns | ref  | enabled |
|--------------------------------------------|
| 1  |   1  |               | null |    1    |
| 2  |   2  |               | null |    0    |
| 3  |   3  |               | null |    1    |
| 4  |   2  |               |  2   |    1    |
--------------------------------------------

如果我们更新全新的项目，我们将：

--------------------------------------------
| ID | type | other columns | ref  | enabled |
|--------------------------------------------|
| 1  |   1  |               | null |    1    |
| 2  |   2  |               | null |    0    |
| 3  |   3  |               | null |    1    |
| 4  |   2  |               |  2   |    0    |
| 5  |   2  |               |  4   |    1    |
--------------------------------------------

我们在这里提供的是仅启用最新版本的项目类型列表。

但这里的enabled列是多余的，因为启用的项目只是一个没有新版本的项目。

所以我的问题是如何执行相当于以下内容的SQL查询：

SELECT * FROM `records` WHERE type='2' AND enabled='1'

不使用enabled，以有效的方式(此查询为 <1 毫秒(。

Answer 1

您可以使用not exists：

select  r.*
from records r
where not exists (select 1
from records r2
where r2.ref = r.id
) and
r.type = 2;

但是，在我看来，使用enabled使代码更清晰。 性能需要records(ref)索引。

如果假设 id 始终递增，则还可以使用最大id。

Answer 2

对于此查询，您需要在 where 子句中显示的两列上都有一个索引：

create index myidx on records(type, enabled);

使用索引，数据库应该能够有效地执行查询。您可能还想尝试反转列顺序，看看它是否会提高性能。