具有唯一索引的 MySQL 零星 MATCH AGAINST 行为

Question

将唯一键添加到多表全文 bool 搜索时，结果循环通过 3 个任意状态中的 1 个，只有 1 个是正确的。

在检查下面的 sqlfiddle 时请记住这一点，因为查询最初可能会正常工作 - 在这种情况下，将空格添加到左侧面板然后重建并重新运行 - 然后它应该被破坏(但它很容易失败).

http://sqlfiddle.com/#!9/8d95ba/18

这是有问题的查询:

SELECT `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name`
  FROM `item` `i`
  JOIN `group_alias` `g_a` USING (group_id)
    WHERE
      MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE)
    OR
      MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);

很简单。但是添加了以下唯一索引:

ALTER TABLE `item_with_unique` ADD UNIQUE INDEX `unique_item_group` (`group_id`, `name`)

结果在这三种状态之间任意循环:

1. 返回所有行，就好像没有 WHERE 子句一样
2. 返回别名匹配，就像 WHERE 子句中没有 OR 部分一样
3. 返回正确的结果(根据我的经验，这是最罕见的)

行为似乎与它所处的这 3 种状态中的任何一种一致，直到以某种次要方式更改查询(例如添加括号)或重建架构 - 此时它可能会更改。

这些是我在描述此行为的 MySQL 文档中遗漏的某种限制吗？这是一个错误吗？还是我只是做了一些明显错误的事情？

Mysql 版本 5.6.35(撰写本文时为 sqlfiddle)。

万一链接失效，后代的 Sqlfiddle:

CREATE TABLE `group` (
  `group_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(256),
  FULLTEXT INDEX `search` (`name`)
) ENGINE = InnoDB;

CREATE TABLE `group_alias` (
  `group_id` INT UNSIGNED NOT NULL,
  `alias` VARCHAR(256),
  CONSTRAINT `alias_group_id`
    FOREIGN KEY (`group_id`)
    REFERENCES `group` (`group_id`),
  FULLTEXT INDEX `search` (`alias`)
) ENGINE = InnoDB;

CREATE TABLE `item` (
  `item_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `group_id` INT UNSIGNED,
  `name` VARCHAR(255) NOT NULL,
  CONSTRAINT `item_group_id`
    FOREIGN KEY (`group_id`)
    REFERENCES `group` (`group_id`),
  FULLTEXT INDEX `search` (`name`)
) ENGINE = InnoDB;

CREATE TABLE `item_with_unique` LIKE `item`;
ALTER TABLE `item_with_unique` ADD UNIQUE INDEX `unique_item_group` (`group_id`, `name`);

INSERT INTO `group` (`group_id`, `name`) VALUES (1, 'Thompson');
INSERT INTO `group` (`group_id`, `name`) VALUES (2, 'MacDonald');
INSERT INTO `group` (`group_id`, `name`) VALUES (3, 'Stewart');

INSERT INTO `group_alias` (`group_id`, `alias`) VALUES (1, 'Tomson');
INSERT INTO `group_alias` (`group_id`, `alias`) VALUES (2, 'Something');
INSERT INTO `group_alias` (`group_id`, `alias`) VALUES (3, 'MacStewart');

INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (1, 1, 'MacTavish');
INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (2, 1, 'MacTavish; Red');
INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (3, 2, 'MacAgnew');
INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (4, 3, 'Spider');
INSERT INTO `item` (`item_id`, `group_id`, `name`) VALUES (5, 2, 'blahblah');

INSERT INTO `item_with_unique` SELECT * FROM `item`;


SELECT `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name`,
IF(MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE), 1, 0) AS `group_match`,
IF(MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE), 1, 0) AS `item_match`
  FROM `item` `i`
  JOIN `group_alias` `g_a` USING (group_id)
    WHERE
      MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE)
    OR
      MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);

SELECT "Same query, using table with unique index (NOTE: sporadically this is actually correct, in such case, skip to bottom notes)";
SELECT `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name`,
IF(MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE), 1, 0) AS `group_match`,
IF(MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE), 1, 0) AS `item_match`
  FROM `item_with_unique` `i`
  JOIN `group_alias` `g_a` USING (group_id)
    WHERE
      MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE)
    OR
      MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);

SELECT "Union of the two OR match conditions seperately (expected result from second query)";
SELECT `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name`,
IF(MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE), 1, 0) AS `group_match`,
IF(MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE), 1, 0) AS `item_match`
  FROM `item_with_unique` `i`
  JOIN `group_alias` `g_a` USING (group_id)
    WHERE
      MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE)
UNION
SELECT `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name`,
IF(MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE), 1, 0) AS `group_match`,
IF(MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE), 1, 0) AS `item_match`
  FROM `item_with_unique` `i`
  JOIN `group_alias` `g_a` USING (group_id)
    WHERE
      MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);

SELECT "Now rebuild the schema (add a newline somewhere so sqlfiddle thinks it has changed) and observe that the results of the second query.  It may take multiple attempts but it usually cycles between 3 states:";
SELECT "1: Returns ALL results as if there were no conditions (5 rows)";
SELECT "2: Returns results as if there were no second part to the OR condition (1 row)";
SELECT "3: Returns the correct results (rarely)";

Answer 1

尝试对您的语句使用IGNORE INDEX:

SELECT `i`.`item_id`, `g_a`.`alias` AS `group`, `i`.`name` AS `name`
  FROM `item` `i`
  IGNORE INDEX (unique_item_group)
  JOIN `group_alias` `g_a` USING (group_id)
    WHERE
      MATCH (`g_a`.`alias`) AGAINST ('Mac*' IN BOOLEAN MODE)
    OR
      MATCH (`i`.`name`) AGAINST ('Mac*' IN BOOLEAN MODE);

MySQL 随机使用 unique_item_group 进行全文搜索是非常愚蠢的。