在数据库业务场景中，经常需要存储长文本类数据，比如文章内容、用户备注、操作日志等，这类数据通常会使用TEXT类型字段存储。当表中包含TEXT大字段时，查询操作很容易出现性能瓶颈，因为TEXT字段的数据通常存储在额外的存储空间中，查询时需要额外的IO开销，尤其是全表扫描或者对该字段做条件查询时，性能下降会非常明显。针对这类问题，常见的优化方向主要有剥离表结构和使用前缀索引两种，不同的方案适用不同的业务场景，需要结合实际需求做取舍。
TEXT大字段查询的性能痛点
TEXT类型字段的存储机制和普通字段不同，在InnoDB存储引擎中，当字段长度超过一定阈值时，数据会存储在溢出页中，行内只保留一个20字节的指针指向溢出页。这意味着查询TEXT字段时，需要额外读取溢出页的数据，增加IO次数。如果频繁对TEXT字段做查询、排序或者分组操作，会大幅提升查询的响应时间，甚至影响整个数据库实例的稳定性。
方案一：剥离表结构优化
剥离表结构的核心思路是将TEXT大字段从主表中拆分出来，放到单独的附属表中，主表只保留业务常用的核心字段，附属表通过外键和主表关联，只在需要查询TEXT字段内容时才关联附属表查询。
适用场景
- TEXT字段的查询频率远低于主表其他字段的查询频率
- TEXT字段的数据不需要频繁更新
- 主表需要频繁做列表查询、分页查询等操作，不需要每次都返回TEXT字段内容
具体操作示例
假设原来的用户表结构如下，包含TEXT类型的用户简介字段：
-- 原用户表结构 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL COMMENT '用户名', `age` int(11) DEFAULT NULL COMMENT '年龄', `intro` text COMMENT '用户简介', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
剥离后的表结构分为主表和附属表：
-- 主表，保留核心字段 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL COMMENT '用户名', `age` int(11) DEFAULT NULL COMMENT '年龄', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 附属表，存储TEXT大字段 CREATE TABLE `user_intro` ( `user_id` int(11) NOT NULL COMMENT '用户ID', `intro` text COMMENT '用户简介', PRIMARY KEY (`user_id`), CONSTRAINT `fk_user_intro_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
日常查询用户列表时，只需要查询主表即可，不需要读取TEXT字段：
-- 查询用户列表，不需要intro字段 SELECT id, username, age FROM user WHERE age > 18 LIMIT 10;
只有需要查看用户简介时，才关联附属表查询：
-- 查询用户详情，需要intro字段 SELECT u.id, u.username, u.age, ui.intro FROM user u LEFT JOIN user_intro ui ON u.id = ui.user_id WHERE u.id = 1;
方案优缺点
优点：主表的查询性能会大幅提升，因为行大小变小，每页可以存储更多的行数据，减少IO次数；TEXT字段的存储和查询不会影响主表的常规操作。
缺点：如果业务需要频繁同时查询主表字段和TEXT字段，会增加关联查询的开销；需要维护两张表的数据一致性，插入、更新、删除操作都需要操作两张表。
方案二：前缀索引优化
前缀索引是指只对TEXT字段的前N个字符创建索引，而不是对整个字段内容创建索引，这样索引的大小会大幅减小，同时可以提升对TEXT字段的条件查询性能。
适用场景
- 对TEXT字段的查询主要是前缀匹配查询，比如查询简介中包含某个开头内容的用户
- 不需要对TEXT字段做精确的全字段匹配查询
- 业务需要频繁对TEXT字段做条件过滤，不适合拆分表结构
具体操作示例
首先需要确定合适的前缀长度，保证前缀的选择性足够高，也就是不同前缀的数量和总记录数的比值尽量接近1。可以通过以下SQL计算不同前缀长度的选择性：
-- 计算user表intro字段不同前缀长度的选择性 SELECT COUNT(DISTINCT LEFT(intro, 10)) / COUNT(*) as sel_10, COUNT(DISTINCT LEFT(intro, 20)) / COUNT(*) as sel_20, COUNT(DISTINCT LEFT(intro, 50)) / COUNT(*) as sel_50, COUNT(DISTINCT LEFT(intro, 100)) / COUNT(*) as sel_100 FROM user;
假设计算后发现前缀长度为50时选择性已经足够高，就可以创建前缀索引：
-- 对intro字段创建长度为50的前缀索引 ALTER TABLE user ADD INDEX idx_intro_prefix (intro(50));
查询时如果是前缀匹配，就可以使用到前缀索引：
-- 查询简介以某个内容开头的用户，会使用前缀索引 SELECT id, username, intro FROM user WHERE intro LIKE '热爱编程%';
方案优缺点
优点：不需要拆分表结构，不会增加关联查询的开销；索引大小远小于全字段索引，创建和维护的成本更低；可以提升TEXT字段前缀查询的性能。
缺点：无法用于全字段精确匹配查询，比如WHERE intro = '完整简介内容'无法使用前缀索引；如果查询的条件不是前缀匹配，比如LIKE '%编程%'，也无法使用前缀索引；前缀长度选择不合理的话，可能会导致索引选择性低，查询性能提升不明显。
两种方案的取舍建议
在实际业务中，可以根据以下维度做选择：
| 对比维度 | 剥离表结构 | 前缀索引 |
|---|---|---|
| 查询频率 | TEXT字段查询频率低时优先选择 | TEXT字段查询频率高时优先选择 |
| 查询类型 | 适合需要返回完整TEXT字段内容的场景 | 适合前缀匹配查询的场景 |
| 维护成本 | 需要维护两张表，成本较高 | 只需要维护一个索引，成本较低 |
| 性能影响范围 | 提升主表所有查询的性能 | 只提升TEXT字段前缀查询的性能 |
如果业务既需要频繁查询TEXT字段的完整内容，又需要提升查询性能，也可以结合两种方案，将TEXT字段拆分到附属表的同时，对附属表的TEXT字段创建合适的前缀索引，兼顾不同查询场景的需求。
注意事项
- 剥离表结构时，外键约束可以根据业务需求选择是否添加，如果不需要强一致性，也可以不用外键，通过业务逻辑保证数据一致性，减少数据库约束的开销。
- 创建前缀索引时，前缀长度不是越长越好，需要在选择性和索引大小之间做平衡，避免索引过大反而影响性能。
- 如果TEXT字段需要频繁更新，剥离表结构的方案会更合适，因为更新大字段时，拆分后不会影响主表的其他操作。
优化TEXT大字段的查询没有通用的完美方案，需要结合业务的实际查询场景、数据特征、性能要求综合判断，选择最适合当前业务的优化方式。