BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
实际上,它们分别属于两组不同的数据类型家族:
字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT;对应的二进制类型是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列全部长度的字符串进行索引,也不能使用这些索引消除排序。

磁盘临时表和文件排序
因为memory引擎不支持BLOB和TEXT类型,所以,如果查询使用了BLOB或TEXT列并且需要使用隐式临时表,将不得不使用MyISAM磁盘临时表,即使只有几行数据也是如此。
这会导致严重的性能开销。即使配置MySQL将临时表存储在内存块设备上(RAM Disk),依然需要许多昂贵的系统调用。
最好的解决方案是尽量避免使用BLOB和TEXT类型。如果实在无法避免,有一个技巧是在所有用到BLOB字段的地方都使用SUBSTRING(column,length)将列值转换为字符串(在ORDER BY子句中也适用),这样就可以使用内存临时表了。但是要确保截取的子字符串足够短,不会使临时表的大小超过max_heap_table_size或tmp_table_size,超过以后MySQL会将内存临时表转换为MyISAM磁盘临时表。
最坏的情况下的长度分配对于排序的时候也是一样的,所以这一招对于内存中创建大临时表和文件排序,以及在磁盘上创建大临时表和文件排序这两种情况都很有帮助。
例如,假设有一个1000万行的表,占用几个GB的磁盘空间。其中有一个utf8字符集的VARCHAR(1000)列。每个字符最多使用3个字节,最坏情况下需要3000字节的空间。如果在ORDER BY中用到这个列,并且查询扫描整个表,为了排序就需要超过30GB(1000万行 * 3000字节)的临时表。 如果EXPLAIN执行计划的Extra列包含“Using temporary”,则说明这个查询使用了隐式临时表。


发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

PowerDesigner16.5 使用遇到的问题知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。