MySQL：如何在boolean / tinyint列上创建部分索引？

问题描述

CREATE TABLE participations (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    participant_id int(10) unsigned NOT NULL,
    prize_id int(10) unsigned NOT NULL,
    win tinyint(1) NOT NULL DEFAULT '0',
    -- ...
    PRIMARY KEY (id),
    INDEX participant_id (participant_id),
    INDEX prize_id (prize_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

我有这些疑问：

SELECT prize_id, participant_id FROM participations WHERE win=1;
SELECT participant_id FROM participations WHERE prize_id=1 AND win=1;

可以创建一个部分索引，如（）？

Can one create a partial index like (PostgreSQL-style) ?

CREATE UNIQUE INDEX prize_win ON participations (prize_id) WHERE win=1;

__

B计划：拥有表获胜者（prize_id，participant_id）。

Plan B: Having a table "winners" (prize_id, participant_id).

推荐答案

不，MySQL不支持这种性质的部分索引。

No, MySQL does not support partial indexes of this nature.

如果人们在MySQL中引用部分索引，则它们指的是文本前缀索引，其中只有一定数量的前导字符被索引而不是整个值。例如，索引可以仅通过索引前5个字符来生成。

If people refer to "partial indexes" in MySQL, they're referring to text "prefix indexes" where only a certain number of leading characters are indexed rather than the whole value. For example, an index could be made by only indexing the first 5 characters.

如果你正在寻找性能提升，因为它没有同时使用 win 和 prize_id 列（虽然我看到你目前没有 win 已编入索引），您可以创建一个超过（win，prize_id）的复合索引（按此顺序），以便按 win 首先，然后查看剩余的 prize_id 值。

If you're looking for a performance boost because it's not using both win and prize_id columns (though I see you don't currently have win indexed), you could create a composite index over (win, prize_id) (in that order) so that it would filter values by win first, then look at the remaining prize_id value.

如果您正在寻找偏索引只是为了减少该索引的大小/内存使用量，不幸的是，这会产生相反的效果。

If you're instead looking for a partial index simply for the purposes of decreasing size/memory usage of that index, this would unfortunately have the opposite effect.

这篇关于MySQL：如何在boolean / tinyint列上创建部分索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！