MySQL优化之大字段longtext、text引发的生产问题

在对接多个外部接口的场景中，为了统一保存请求参数及返回参数，方便消息补偿，采用大字段(longtext)进行存储。然而，即使数据量仅为40万条，查询速度依然异常缓慢，长达40秒左右。为解决此问题，我们尝试了为关键字段"risk_buss_no"添加索引，将速度提升至几百毫秒。

然而，40万数据的查询时间依然不合理，促使我们对字段进行了调整，将`request_msg`和`response_msg`字段的长度调整为64位，明显提高了查询效率。这一问题的根本原因在于理解InnoDB存储引擎的底层处理方式。

InnoDB在5.1版本中的默认行格式为compact，对于如blob、text、varchar(8099)等大字段，InnoDB仅在数据页中存储前768字节，剩余数据则会存储在溢出段中。在块大小默认为16kb的条件下，每个页至少应存储两行记录，这限制了单行数据的最大长度为8k（8098字节）。

在使用blob数据类型时，数据并非一定存放在溢出段中。关键点在于，数据页是否能存放两行数据，blob数据可以完全存放在数据页中（单行长度未超过8098字节），而varchar类型的单行数据也可能存放在溢出页中（单行长度超过8098字节，前768字节存放在数据页中）。

InnoDB引入了新文件格式：barracuda，包括compressed和dynamic两种行格式。对于blob字段，这两种格式采取完全溢出方式，数据页仅存放20字节，剩余数据存放在溢出段中。

MySQL在操作数据时，以page为单位，涉及更新、插入、删除一行数据时，需要读取那行数据所在的page到内存中，这导致了命中率问题。如果一个page能够存储更多行，那么命中率会相对较高，性能也会提升。

通过查询字段长度和MySQL的row_format，我们了解到每个page的最大长度约为2.5kb。结合InnoDB的特性，每个page至少存储两行，理论最大长度约为8k。考虑到InnoDB内部数据结构和性能优化，实际最大长度可能略小于8k。因此，大字段数据主要存放在数据页中，而非溢出到page外，导致大量随机读取。

主要的解决策略包括拆表、使用索引。拆表将大字段移至单独的表，索引则通过访问密度较大的索引页替代数据页，实现随机读取向顺序读取的转换，大幅提高内存命中率。

总结而言，优化大字段存储的关键在于提升内存命中率，确保单个page能够存放足够多的行。通过合理设计表结构和使用索引，可以显著提升查询性能。此外，理解InnoDB的行存储和page管理机制是优化大字段存储效率的基础。

您可能感兴趣问答

Collapsible

热门标签

热点问答