MySQL什么时候重建FTS索引？
如果您想使用 MySQL 全文搜索 功能您可以在 VARCHAR 类型的字段上定义全文索引。当行被 INSERTed 和 UPDATEd 时，mysql 必须保持索引是最新的。我的问题是：MySQL 何时重建 FTS 索引？
- A）在影响索引的 INSERT 或 UPDATE 发生后立即重建。
- B) 当运行第一个 SELECT 时，需要最近受 UPDATE 或 INSERT 影响的索引。
- C）其他的东西。
不必要的背景信息：我的经验似乎认为选项B。这是正确的吗？我问这个问题是因为我在进行全文搜索时遇到了突然的随机缓慢查询，我不知道为什么有些查询很慢，而另一些则不然。我的预感是，如果查询正在等待 mysql 重建 FTS 索引，查询可能会很慢，但我不知道这是否是 mysql 的工作方式。随机慢查询的示例（通常相同的查询在一秒内运行），慢日志中没有慢 UPDATE 或 INSERT：
# Query_time: 61.775612 Lock_time: 10.110924 Rows_sent: 20 Rows_examined: 222498
SELECT SQL_CALC_FOUND_ROWS id
FROM members
WHERE
AND MATCH (bio,profile_text,name) AGAINST ('building surveyor')
AND MATCH (town, postcode, country) AGAINST ('united kingdom, liverpool');
注意 Lock_time。我在慢日志中看不到任何其他 INSERT 或 UPDATE，所以我不确定它在等待什么。这就是为什么我猜测它可能正在等待 FTS 索引重建？
If you want to use the MySQL Full Text Search functionality you define a Full Text Index on field(s) with type VARCHAR. As rows are INSERTed and UPDATEd, mysql has to keep the indexes up-to-date. My question is: When does MySQL rebuild a FTS index?
- A) Immediately after a INSERT or UPDATE occurs that affects the index.
- B) When the first SELECT is run that needs the index which has recently been affected by a UPDATE or INSERT.
- C) Something else.
Unecessary background info: My experience seems to think option B. Is this right? I ask because I have been experiencing sudden random slow queries which do a Full Text Search and I don't know why some are slow and not the others. My hunch is that the queries can be slow if they are waiting for mysql to rebuild the FTS index, but I don't know if this is how mysql works. Example of a random slow query (usually the same query runs under a second), there is no slow UPDATE or INSERT in the slow log:
# Query_time: 61.775612 Lock_time: 10.110924 Rows_sent: 20 Rows_examined: 222498
SELECT SQL_CALC_FOUND_ROWS id
FROM members
WHERE
AND MATCH (bio,profile_text,name) AGAINST ('building surveyor')
AND MATCH (town, postcode, country) AGAINST ('united kingdom, liverpool');
Notice the Lock_time. I can't see any other INSERT or UPDATE in the slow log, so I'm not sure what it is waiting for. That is why I guessed it could be waiting for a FTS index rebuild?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
考虑使用
... AGAINST('"building Surveyor"')来获取该短语，而不是分散在任何地方的这两个单词。您使用的是 MyISAM 还是 InnoDB？他们的工作方式不同。
我认为，MyISAM 在插入/更新行时更新 FT 索引。
我认为，InnoDB 将更改排队，并最终将它们写入磁盘。这通常比 MyISAM 的写入速度更快。
除非明确要求，否则引擎都不会重建索引。
Consider using
... AGAINST('"building surveyor"')to get that phrase instead of those two words scattered anywhere.Are you using MyISAM or InnoDB? They work differently.
MyISAM, I think, updates the FT indexes when a row is inserted/updated.
InnoDB, I think, queues up changes, and eventually writes them to disk. This generally makes it faster for writes than MyISAM.
Neither Engine rebuilds the index except when explicitly asked to.