mysql的text/blob类型性能问题分析

text/blob类型介绍

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
实际上，它们分别属于两组不同的数据类型家族：
TEXT：字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT；
BLOB ：对应的二进制类型是TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。

BLOB是SMALLBOLB的同义词，TEXT是SMALLTEXT的同义词。
与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1 ~ 4个字节存储一个指针，然后在外部存储区域存储实际的值。
BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用 ORDER BY SUBSTRING(column,length)。
MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

text/blob容易引起性能问题的原因

当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1 ~ 4个字节存储一个指针，然后在外部存储区域存储实际的值。
在存储时使用了“外部”存储区，会使用磁盘临时表存储。磁盘临时表会导致严重的性能开销。

解决方式

1. 避免使用BLOB和TEXT类型；
2. 所有用到Blob类型的地方使用SUBSTRING(column,length)将列值转换为字符串，使用内存临时表；
   有时候出现mysql出现慢查询但是数据量又不是特别大，并且使用了BLOB和TEXT类型类型的时候查看了执行计划也没有什么头绪的时候可以从这方面考虑一下。dolphinshcduler2.0以前使用大json存储工作流内容的时候明显有这个问题，2.0以后进行了sql拆分，应该是解决了。

参考文档

1. 《高性能mysql》