MySQL大字段TEXT建立索引导致的性能下降解决方案

MySQL中的TEXT字段无法直接创建常规B+树索引，只能使用前缀索引（例如content(100)）或全文索引。然而，前缀索引仅支持左匹配，对于LIKE '%xxx%'这类模糊查询完全无效；全文索引则受限于词长、中文分词、更新延迟等问题。真正高效的解决方案是采用摘要索引（如MD5哈希）配合垂直拆分策略。

TEXT字段根本无法直接建立常规B+树索引——如果你认为建成功了，那很可能只是前缀索引，而且实际上并未生效。

为什么TEXT字段添加索引后，查询速度仍然缓慢如同卡顿？

MySQL会直接抛出错误ERROR 1170 (42000): BLOB/TEXT column 'content' used in key specification without a key length，明确提示无法直接索引TEXT字段。你所认为的“已建立索引”，很可能只是使用了前缀语法，例如ADD INDEX idx_content (content(100))。然而，问题随之而来——

• 如果关键词出现在第150个字符之后，该索引将完全失效。通过EXPLAIN查看，key_len显示为100，但type却标注为ALL，意味着全表扫描。
• LIKE '%xxx%'这类左模糊查询，无论前缀长度设置多少，B-tree索引都无法发挥作用。
• InnoDB对前缀索引的统计信息本身就不准确，导致优化器容易选错执行计划，这是常见问题。
• 前缀长度越长，索引体积越大，写入放大效应越严重，进而影响主键插入性能。

全文索引（FULLTEXT）能否真正解决性能问题？

可以，但不要期望它万能。InnoDB的FULLTEXT存在以下硬性限制：

• 字段最小词长默认为4，搜索“AI”“Go”等短词时无法命中。
• 中文需要额外安装ngram或mechanical分词插件，线上部署和升级成本较高。
• MATCH ... AGAINST仅支持按相关性排序，其他排序方式基本不可用。若与其他条件组合，例如WHERE status = 1 AND MATCH(...) AGAINST(...)，很可能退化为全表扫描。
• 全文索引更新存在延迟，INSERT/UPDATE后无法立即搜索到，在MVCC环境下还可能引发数据不一致问题。

真正高效的替代方案：摘要索引与垂直拆分

既然原生索引机制存在局限，不妨绕过TEXT字段的限制。实现思路如下：

• 在应用层对content字段计算固定长度摘要，例如MD5(content)或SHA2(content, 256)，存储为content_hash CHAR(64)，然后创建普通B+树索引，用于等值判断、去重和幂等控制，性能极佳。
• 若需关键词检索，可使用SUBSTRING(content, 1, 2000)提取首屏内容，在应用层生成关键词向量，交由外部搜索引擎（如Elasticsearch或Meilisearch）处理，数据库中仅存储es_doc_id。
• 垂直拆分时，新表article_content的主键应为article_id（非自增），与主表严格一对一。避免使用JOIN，改为应用层两次查询，以减少锁竞争和溢出页加载。
• 拆分后，将主表的ROW_FORMAT改为DYNAMIC，并确认innodb_file_per_table = ON，以防止旧溢出页残留干扰Buffer Pool。

最易被忽视的细节：SUBSTRING并未节省IO

有人编写SELECT id, title, SUBSTRING(content, 1, 300) FROM article，自以为聪明——网络传输量确实减少了，但InnoDB仍需先定位并读取整个溢出页才能截取。磁盘随机I/O并未减少，Buffer Pool压力依然存在。真正节省IO的唯一方法，就是不查询该字段，或者确保它不在当前查询路径中（例如拆表后只查询主表）。