如何为不同的单列子句索引表

Question

我有下表：

CREATE TABLE Test (
device varchar(12),
pin varchar(4),
authToken varchar(32),
Primary Key (device)
);

在应用程序的不同点，我需要通过不同的单列子句查询此表。意思是我有以下疑问：

SELECT * FROM Test WHERE device = ?;
SELECT * FROM Test WHERE authToken = ?;
SELECT * FROM Test WHERE pin = ?;

据我了解，在这种情况下，(device, authToken, pin)的组合索引毫无意义，因为这只会加快第一个查询的速度，而不会加快第二个或第三个查询的速度。

读取速度比为这个表编写更重要，所以简单地单独索引每一列是这里的最佳解决方案吗？

Answer 1

简单的答案是为每个查询创建单独的单列索引：

create index ix1 (device); -- no need to create it since it's the PK.
create index ix2 (pin);
create index ix3 (authToken);

第一个索引(来自 PK(使用主索引。第二个和第三个可能会更慢，因为它们遭受"二级索引"缓慢的影响：它们总是需要先访问二级索引，然后再访问一级索引;如果选择大量行，这可能会变慢。

现在，如果你想以修改(INSERT、UPDATE和DELETE(的速度为代价，在速度SELECT方面过火，你可以使用为每个查询量身定制的"覆盖索引"。这些应如下所示：

create index ix4 (device, pin, authToken); -- [non needed] optimal for WHERE device = ?
create index ix5 (authToken, device, pin); -- optimal for WHERE authToken = ?
create index ix6 (pin, device, authToken); -- optimal for WHERE pin = ?

注意：如Rick James所示ix4与InnoDB表的主键索引是多余的。无需创建它。此处列出它只是为了完整起见。

这些"覆盖索引"仅使用二级索引，无需访问主索引即可解析查询。对于检索的大量行，它们要快得多。

Answer 2

不需要索引引脚列，因为它已经编制索引。对于其他 2 列(即设备和 authToken(，是的，根据您的共享查询，最好将它们单独索引。

请注意，当您有大量此类查询命中服务器时，您将看到巨大的性能改进，而您在此表上也有巨大的数据集。

Answer 3

回答：

"如何为不同的单列子句索引表？">

CREATE INDEX Test_device_index ON Test(device);
CREATE INDEX Test_authToken_index ON Test(authToken DESC);
CREATE INDEX Test_pin_index ON Test(pin);

Answer 4

这是我建议的架构：

CREATE TABLE Test (
id SERIAL PRIMARY KEY,
device VARCHAR(255),
pin VARCHAR(255),
authToken VARCHAR(255),
UNIQUE KEY index_authToken (authToken),
UNIQUE KEY index_device (device),
KEY index_pin (pin)
);

您有一个不与任何特定数据关联的id类型列，并且您对authToken和device有UNIQUE约束。

请记住将WHERE中使用的任何列编入索引，并使用以下内容测试您的覆盖率：

EXPLAIN SELECT ... FROM Test WHERE pin=?

如果您在计划中看到"表扫描"，那就是缺少索引的问题。

使用VARCHAR(255)作为默认值也是一个好主意，除非您有非常令人信服的理由来限制它。在应用程序层中强制实施长度限制，以便以后可以轻松放宽长度限制。例如，更改为 6 位 PIN 与 4 位 PIN 是一个简单的代码更改，甚至可以增量推出，这不是架构更改。