如何应对MySQL中因大字段TEXT建立索引导致的性能急剧下降？

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 不支持 ORDER BY relevance 以外的排序，也难与其它条件高效组合（如 WHERE status = 1 AND MATCH(...) AGAINST(...) 可能退化为全表扫描）
• 全文索引更新延迟明显，INSERT/UPDATE 后不能立刻搜到，MVCC 下还可能引发不一致

真正见效快的替代方案：摘要索引 + 垂直拆分

不依赖 MySQL 原生索引机制，绕过 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 的，只有不查该字段，或确保它压根不在当前查询路径里（比如拆表后只查主表）。