MySQL库表设计为什么不建议使用TEXT类型

Shawfix
5,096 阅读3分钟

MySQL库表设计为什么不建议使用TEXT类型

背景

在需求中有一段不确定长度的序列化数据，后端同学的库表设计中将这个字段设计为VARCHAR(2000)类型。

因此我产生了一个疑问：既然不确定长度，为什么不用TEXT类型呢🤔️？咨询了后端同学，得到的回复是：TEXT会影响查询性能。

image.png

于是，我便又在网上冲浪起来🏄～

什么是TEXT

TEXT在MySQL中是一个可变长度的数据类型，包括TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。它们通常用于存储大量的文本数据，其允许存储的长度如下：

类型长度
TINYTEXT0 - 255字节
TEXT0 - 65,535字节
MEDIUMTEXT0 - 16,777,215字节
LONGTEXT0 - 4,294,967,295字节

TEXT如何存储

每个BLOB或TEXT数据在内部由单独分配的对象表示，而其他类型在打开表时为每个列分配一次存储空间。

对于字符串类型数据的存储，InnoDB会将大于或等于768字节的固定长度字段编码为可变长度字段，存储在“溢出页”中；小于768字节的数据直接存储在数据行中。 所以在使用其他字符串类型时应避免存储大于或等于768字节的数据。

TEXT的使用限制

  1. TEXT无法设置默认值。image.png
  2. TEXT被索引时必须指定索引前缀长度。image.png
  3. 索引条目比较时，会在末尾填充空格。如果索引需要唯一值，可能会导致重复键错误。image.png
  4. TEXT字段可能会特别长，排序时只有前max_sort_length字节（默认值是1024）会被使用，可以通过设置这个变量来调整。
-- 查看max_sort_length
SELECT @@max_sort_length;

-- 设置max_sort_length
SET max_sort_length = 2048;

  1. 使用临时表处理时，服务器会使用磁盘上的表而不是内存中的表，因为MEMORY存储引擎不支持TEXT类型。

  2. TEXT对象的大小由其类型决定，但实际能传输的最大值由可用内容和通信缓冲区大小决定。可以通过调整max_allowed_packet变量改变消息缓冲区大小。

-- 查看max_allowed_packet
SELECT @@max_allowed_packet;

-- 设置max_allowed_packet
SET max_allowed_packet = 67108864;

结论

TEXT可以用于存储大量的文本数据。但因几点原因，不建议使用TEXT：

  1. 性能问题

    • TEXT在内部由单独分配的对象表示，存储/检索时会有额外的操作和资源消耗
    • 若TEXT字段特别大，读取时会导致内存压力增大，影响系统整体性能
    • MEMORY存储引擎不支持TEXT类型，使用临时表处理时会从磁盘中读取TEXT字段的数据，而不会直接从内存中读取

  2. 索引限制

    索引能够提高查询性能，但对TEXT字段进行索引存在一些限制和复杂性

    • 作为唯一索引时，可能会导致重复键错误
    • 创建全文索引需要额外的计算和空间来维护，TEXT字段数据过大时可能会对性能产生影响

因此，在库表设计时应尽量避免使用TEXT类型。如要使用，可以参考以下几种方式：

  1. 尽可能把TEXT字段拆到独立的表中，用PK与主表关联
  2. 非必要不读取TEXT字段，比如：禁止使用“SELECT *”
  3. 大字段建议放到oss

参考链接：

  1. BLOB和TEXT类型
  2. 索引前缀
  3. String类型存储要求
  4. 内部临时表
avatar
文章
阅读
粉丝