MySQL数据类型之TEXT与BLOB

一般在保存少量字符串的时候，我们会选择CHAR或者VARCHAR,而在保存较大文本时，通常会选择使用TEXT或者BLOB。二者之间的主要差别是BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符数据，比如一遍文章或日记。TEXT和BLOB中又分别包括TEXT,MEDIUMTEXT,LONGTEXT和BLOB,MEDIUMBLOB,LONGBLOB三种不同的类型，他们之间的主要区别是存储文本长度不用和存储字节不用，用户应该根据实际情况选择能够满足需求的最小存储类型。

BLOB和TEXT值会引起一些性能问题，特别是执行了大量的删除操作时。

删除操作会在数据库表中留下很大的“空洞”，以后要填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMEIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。
创建测试表t,字段id和context的类型分别为varchar(100)和text：
然后往t中插入大量记录，这里使用repeat函数插入大量字符串
repeat()函数解释（返回字符串str重复count次后的字符串。如果count小于1，则返回一个空字符串。如果str或count为NULL，返回NULL。）

查看表t的物理文件大小

从表t中删除id为“1”的数据，这些数据占总数据量的1/3；


发现表t的数据文件仍然为155MB,并没有因为数据删除而减少，接下来对表进行OPTIMIZE（优化）操作：

查看表t的物理文件的大小

可以发现，表的数据文件大大缩小，“空洞”空间已经被回收。

可以使用合成的（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。

简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似“<”或“>=”等范围搜索操作符是没有用处的）。可以使用MD5（）函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。数值型散列值可以很高效率地存储。如果散列算法生成的字符串带有尾部空格，就不要把他们存储在CHAR或VARCHAR列中，他们会收到尾部去除的影响。合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列的本身速度快很多。
创建一张表，来介绍合成索引的使用方法。

插入数据

如果要查询context值为“beijingbeijing”的记录，则可以通过相应的散列值来查询

这种做法只能用于精确匹配，在一定程序上减少了I/O，从而提高了查询效率。

在不必要的时候避免检索大型的BLOB或TEXT值。

把BLOB或TEXT列分离到单独的表中。

转载于:https://www.cnblogs.com/aotemanzhifu/p/9192389.html