MySQL 全文搜索 innoDB 表的解决方法

Question

我正在设计一个使用 MySQL 作为后端数据库的内部 Web 应用程序。数据的完整性至关重要，因此我使用 innoDB 引擎来实现其外键约束功能。

我想对一种类型的记录进行全文搜索，而 innoDB 表本身不支持这种搜索。我不愿意转向 MyISAM 表，因为它们缺乏外键支持，而且它们的锁定是针对每个表而不是针对每行。

使用 MyISAM 引擎创建我需要搜索的记录的镜像表并将其用于全文搜索是否是不好的做法？这样，我只是搜索数据的副本，如果该数据发生任何事情，也没什么大不了的，因为它总是可以重新创建。

或者这是一种应该避免的尴尬方式？

谢谢。

Answer 1

您也许可以使用触发器进行某种数据同步（如果您的 mysql 版本支持它们）。它们允许您在某些点运行 SQL 的小片段，例如在将数据插入表或从表中删除数据之后。

例如……

create trigger TRIGGER_NAME after insert on INNODB_TABLE
insert into MYISAM_TABLE select * from INNODB_TABLE
where id = last_insert_id();

每当向INNODB表插入数据时，相同的数据会自动插入到MYISAM表中。

Answer 2

我认为这确实很尴尬。也就是说，我的“快速原型可能会意外地成为生产代码”这样做的方法是这样的：

CREATE TEMPORARY TABLE search_mirror (FULLTEXT INDEX (col1, col2, ...)) Engine=MyISAM SELECT * FROM original_innodb_table;

SELECT * FROM search_mirror WHERE MATCH(col1, col2, ...) AGAINST ('foo');

DROP TEMPORARY TABLE search_mirror;

对于奖励积分，如果适合您的喜好，您可以在交易中完成所有这些操作（如果您使用的是双倍奖励）非持久连接，每个连接仅搜索一次，因为您可以消除 drop 语句）。

是的，我意识到这不是真正的镜像/复制。是的，我意识到复制表格可能会很昂贵（这里的数据集相对较小）。就像我说的，快速而肮脏的原型。青年MMV

Answer 3

您可以创建镜像表。这可能不太理想，因为 MyISAM 表不会尊重您的事务（如果 InnoDB 上的事务失败，您在该事务中对该 MyISAM 所做的更改仍然会出现）。

您可以使用专用的全文搜索系统，例如 Sphinx，这就是我用于全文搜索的系统搜索（因为我的数据库是InnoDB）。

Answer 4

好消息！在 MySQL 5.6 及更高版本中，全文索引可以与 InnoDB 表一起使用。如果尚未更新，您应该考虑将 MySQL 更新到 5.6 或更高版本。

对于我的应用程序来说，全文搜索非常重要，因此我只使用了 MyISAM。现在，我已将 MySQL 更新到 5.6，将数据库转换为 InnoDB 并添加了正确的约束。最好的麻烦世界。

MySQL 5.6手册-全文搜索功能

Answer 5

我认为解决这个问题的最简单的解决方案是创建一个用于搜索的索引表，并带有一个指向包含真实数据的表的指针。我面临着完全相同的问题，并且我不想在我的系统中使用 MyISAM 表，因为 InnoDB 表可以让我安心。

因此，我计划解决我的问题是使用 MyISAM 创建一个索引表，这样我就只能在其上建立索引的信息。同步将使用触发器来完成，这是最简单的方法。我不想复制整个表，因为这会花费大量空间。然而，仅复制所需的字段会消耗空间，从而牺牲搜索引擎的功能。

这个索引表可以理解为搜索设施的索引。与任何索引一样，它都会消耗空间。作为一种优化，该索引表上插入的数据可以只是术语，但是这样需要额外的处理来清理无用的单词以进行搜索。