如何优化Mysql中text和blob类

什么是行溢出数据?

InnoDB会将一些大对象数据存放在数据页之外的BLOB页中,然后在查询时根据指针去对应的BLOB页中查询。要不要将数据放在BLOB页中,取决于当前页中是否可以存放下至少两行数据,对于默认是16KB大小的页,这个阈值长度是,大于该值的会存放在BLOB页中。BLOB不只存放text和blob类型,varchar类型的数据也有可能被存放在BLOB页中,而blob类型和text类型的数据也有可能不被存放在BLOB页中。对于Compact和Redundant行存储格式存放的数据,采用的是部分行溢出存储,前字节还是会存放在当前数据页中的。对于Compressed和Dynamic行存储格式存放的数据,采用的完全行溢出存储,只用20个字节存放指针,其余所有数据都放在行溢出数据中。

为什么要尽量少使用text和blob类型?

首先对于text和blob类型,在遇到使用临时表的情况时,无法使用内存临时表,只能在磁盘上创建临时表。对于行溢出数据,InnoDB一次只会为一个列分配一页的空间,但是当该列超过32个页后会一次性分配64个页面,存储空间有一定的浪费。行溢出数据禁用了自适应哈希索引,如果作为where条件时必须完整的比较整个列。对于text和blob字段进行排序时,只能使用部分前缀进行排序,默认是字节,可以通过max_sort_length进行设置。数据量太大,会导致InnoDB每个数据页中存放的行数减少,从而影响对页面的缓存。如果存放在行溢出数据中,每次会根据指针去对应的溢出页进行查询,增加页面访问次数,而且每次查询都是随机IO,text字段越多查询次数越多。

如何优化查询?

如果有许多大字段,可以考虑合并这些字段到一个字段,存储一个大的kb比存储20个10kb更高效,检查随机页面访问次数。查询时尽量避免对大字段查询,尤其是获取列表时,杜绝使用select*查询。可以考虑将大字段专门放在另外一张表中,只有在需要时再关联查询,增加InnoDB的当前表缓存命中率。

如果只需要获取大字段的部分数据,可以使用SUBSTRING()函数,这样可以避免使用磁盘临时表。如果必须使用到磁盘临时表,可以考虑将磁盘临时表指向在基于内存的文件系统中,可以通过修改tmpdir参数实现。必要时可以考虑对大字段进行压缩后再存储到表中。尽量不要使用大字段作为where中的查询条件。




转载请注明:http://www.aierlanlan.com/rzfs/2259.html