为什么TEXT或BLOB字段加了索引查询依然缓慢
首先需要明确一个核心原理：TEXT或BLOB字段查询性能低下，其根本原因在于MySQL对这类大文本数据类型的特殊处理机制——默认仅支持创建前缀索引。问题的症结正在于此：如果未能科学设定“前缀长度”，那么建立的索引几乎等同于无效。
举例说明，假设您为content字段创建了索引INDEX(content(10))，但您实际要搜索的关键词位于第20个字符之后。此时，数据库引擎会完全忽略这个索引，因为它仅“读取”并存储了前10个字节的内容。最终结果是，即便数据表仅有数千行记录，使用EXPLAIN命令分析时，仍会显示type: ALL（全表扫描），key_len值极小，查询耗时稳定在数秒甚至更久。
以下是几个常见的误区与关键点：
- 前缀长度需科学计算，而非随意设定：设定的长度必须足以覆盖您绝大多数查询中关键词的起始位置。
- 字符集对长度计算有重大影响：在
utf8mb4字符集下，一个中文字符占用4个字节。您以为content(50)能索引50个字？实际上可能仅覆盖了12个汉字。 - 模糊查询是另一大挑战：如果查询模式是
LIKE '%关键词%'（即关键词前后均带有通配符），那么前缀索引将完全失效，必须考虑其他优化方案。
如何科学确定前缀长度：基于数据计算，杜绝猜测
那么，如何准确地确定合适的前缀长度？依赖经验猜测并不可靠，必须依据数据进行分析。核心方法是：统计真实业务数据中，需要多长的前缀才能将大部分记录有效区分开来。这对于WHERE content LIKE 'xxx%'这类“前缀匹配”查询尤为重要。
具体操作可分为两个步骤：
- 第一步，抽样分析数据分布：首先观察数据的前缀构成，识别高频前缀。
SELECT LEFT(content, 100) AS prefix, COUNT(*) FROM articles GROUP BY prefix ORDER BY COUNT(*) DESC LIMIT 5;
- 第二步，聚合统计寻找最佳平衡点：计算内容长度的分布情况，找到那个能有效区分绝大多数记录的“甜蜜点”长度。
SELECT len, COUNT(*) cnt FROM (SELECT LEAST(LENGTH(content), 200) AS len FROM articles) t GROUP BY len ORDER BY cnt DESC;
最终创建索引时，建议为计算出的长度预留一定余量。例如，统计发现85%的内容在前120个字节就已足够区分，那么创建content(150)的索引会更加稳妥。这才是基于数据驱动的数据库优化方法。
LIKE '%xxx'与LIKE '%xxx%'查询无法利用前缀索引
此处必须明确一个技术边界：只要LIKE查询模式是以通配符%开头，无论是LIKE '%xxx'（后缀匹配）还是LIKE '%xxx%'（包含匹配），MySQL的任何B-tree索引（包括前缀索引）都会失效，数据库将被迫执行全表扫描。在这种情况下，您添加的索引不仅无法提升查询速度，反而会成为写入操作的负担并占用额外的存储空间。
在实际业务场景中，此类查询常见于后台管理系统搜索或日志分析。但我们需要反思：用户是否真的需要“包含任意位置”的搜索结果？很多时候，业务需求可以被重新梳理和拆解。例如，用户可能只是想查找“标题包含某关键词”或“内容以某段文本开头”的记录。
面对此类需求，有以下几种更专业的优化思路：
- 抽取关键元数据：将标题、摘要等核心信息单独存储在一个
VARCHAR字段中（例如title_snippet），然后为该字段建立完整的普通索引。 - 启用全文索引功能：使用MySQL内置的
FULLTEXT索引来应对全文搜索需求。但需注意，它仅支持MyISAM和InnoDB（5.6及以上版本）存储引擎，并且对停用词列表、最小词长等参数配置较为敏感。 - 引入专业搜索引擎：如果确实需要在文本中间进行复杂、高效的检索，更合理的架构是将
BLOB/TEXT内容同步到Elasticsearch、Sphinx或MeiliSearch这类专用搜索引擎中。不应将MySQL视为解决所有文本搜索问题的万能工具。
大字段本身即是性能瓶颈，仅靠索引治标不治本
即便索引成功生效并定位到了目标行，另一个更为隐蔽的性能杀手便会显现：大字段数据本身。每次执行SELECT *或SELECT content时，数据库都需要将数MB甚至更大的BLOB/TEXT数据从磁盘读取到内存，再通过网络传输至客户端。这个过程的I/O开销与网络带宽消耗，往往远超索引查找本身的开销。
以下是几个常被忽略但至关重要的优化细节：
- 按需查询，避免全量读取：如果仅需检查字段是否存在或获取其长度，应使用
SELECT LENGTH(content)，而非将整个内容SELECT出来。 - 实施垂直分表：考虑将大字段迁移至一张独立的附属表中（例如
articles_content），主表仅通过ID进行关联。这能显著减轻主查询的I/O压力，并降低行级锁竞争。 - 优化数据库服务器配置：
innodb_log_file_size（日志文件大小）和innodb_buffer_pool_size（缓冲池大小）等参数若设置过小，大字段的频繁更新会加剧脏页刷写频率，从而拖慢整个数据库的响应速度。
因此，问题的本质常常并非“无法利用索引定位数据”，而是“即便通过索引找到了数据，也需要搬运一整块‘巨石’才能获取结果”。真正的性能优化，必须从数据存储结构与访问模式两个维度同时入手，单纯依靠ADD INDEX这一条命令是无法解决根本性问题的。
