MySql 全文索引 导致查询效率问题

场景描述

业务系统因需要提升查询效率，考虑使用全文索引进行查询，故建立全文索引
全文索引建立后，使用索引查询效率明显提升，但是过了几天后，查询变得非常缓慢，查询一次数据需要10秒以上
使用 alter table xxx engine=innodb 重建表之后，查询效率又恢复到刚建立索引时的速度
通过 profile 发现，耗时主要消耗在 FULLTEXT initialization 这个步骤

问题分析

通过排查业务系统功能得知，使用全文索引的表每天更新量巨大，通过查看系统文件可以得知在重建表之前，FTS_XXXX_DELETED.ibd的容量为268M，这个文件对应INNODB_FT_DELETED表

• 想在数据库中查看该表，需要开启innodb_ft_aux_table参数

• 重建表后

• 查阅官方文档可以了解这些文件&表的作用
  FULLTEXT Index
  INFORMATION_SCHEMA INNODB_FT_DELETED

至此大致原因归纳为

1. 全文索引所在列，每天更新量非常大，而全文索引中的update，实际是 insert + delete 操作，delete的数据会存放在INNODB_FT_DELETED表中，而全文索引的索引数据不会改变
2. MySql这样设定的原因是为了避免数据删除后维护索引的巨大代价，每当进行查询的时候，会优先过滤INNODB_FT_DELETED中的数据，再进行查询
3. 当进行了 OPTIMIZE TABLE 操作后，才能remove这部分数据

踩坑总结

全文索引并不适合大数据量更新或删除的场景，可能导致查询效率更慢，且处理时需要DBA手动重建表，生产环境风险大