MySQL Longtext字段优化记录

工作中遇到一个查询很慢的情况，环境如下：

开发语言：JAVA

数据库：MySQL

数据量：1600~1800

问题：查询200条时就很慢，是慢在IO上

原因：数据表中有个longtext字段：来存储新闻内容，有图片，图片是转换后的base64；

优化思路：数据库中不存longtext字段，新增blob字段或者longblob字段，将文本在后端压缩为bytep[]存到blob二进制字段中，查询时返回。理由：zip是现在成熟的压缩算法，基于LZ77算法和哈夫曼编码，可以把文本(String)较大程度地压缩为byte[]。注：不建议再把压缩后的byte[] BASE64为String，因为BASE64是一种编码方式。

备注：BLOB 和 LongBLOB 是 MySQL 中的两种二进制类型，它们存储的内容可以是任意的二进制数据，如图片、音频、视频等。其中 BLOB 可以存储最大长度为 65,535 字节的数据，而 LongBLOB 则可以存储最大长度为 4GB 的数据。

后端使用Zip压缩算法，使用java.util.zip包下的DeflaterOutputStream和InflaterOutputStream，压缩文本再保存。压缩使用DeflaterOutputStream：

/**
	 * 压缩方法
	 * @param text
	 * @return
	 */
	public static byte[] zipBase64(String text) {
		try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
			try (DeflaterOutputStream deflaterOutputStream = new DeflaterOutputStream(out)) {
				deflaterOutputStream.write(text.getBytes(Charset.defaultCharset()));
			}
			return out.toByteArray();
		} catch (IOException e) {
			log.error("压缩文本失败:{}",text,e);
		}
		return new byte[]{};
	}


-- service 层
public void save(String content){
   // content是前端通过base64编码后的数据
   News news = new News();
   byte[] bytes = StringUtils.zipBase64(content);
   news.setContentBlob(bytes);
}


-- News

    private byte[] contentBlob;

查询时用InflaterOutputStream将byte[]解压缩还原为文本，new String()：

/**
	 * 解压方法
	 * @param zipedStr
	 * @return
	 */
	public static byte[] unzipBase64(String zipedStr) {
		try (ByteArrayOutputStream os = new ByteArrayOutputStream()) {
			try (InflaterOutputStream outputStream = new InflaterOutputStream(os)) {
				outputStream.write(new BASE64Decoder().decodeBufferToByteBuffer(zipedStr).array());
			}
			return os.toByteArray();
		} catch (IOException e) {
			log.error("解压文本失败:{}",zipedStr,e);
		}
		return new byte[]{};
	}


-- service层 import sun.misc.BASE64Encoder;
   --news是查出来的实体
 byte[] contentBlob = news.getContentBlob();//压缩后的byte[]
 BASE64Encoder base64Encoder = new BASE64Encoder();
 String encodedContent =base64Encoder.encode(contentBlob);
 byte[] unZipedBytes = StringUtils.unzipBase64(encodedContent);
 news.setContent(new String(unZipedBytes, Charset.defaultCharset()));

插入和查询测试通过，再将原表的longtext全都更新到blob字段中。

说明：blob字段IO速度比long text字段IO速度快很多。

BLOB 与 LongBLOB 的性能比较

存储空间

由于 BLOB 和 LongBLOB 存储的数据类型是二进制数据，因此在存储空间方面，它们占用的存储空间一般比字符型字段要大得多。同时， LongBLOB 比 BLOB 占用的存储空间更大。

读取速度

在读取数据方面， BLOB 和 LongBLOB 的读取速度是非常快的，因为这些数据类型存储的是二进制数据，可以直接从二进制流中读取。但是，需要注意的是，如果从这些数据类型中读取的数据非常大，可能会占用大量的内存，对系统性能产生压力。

写入速度

在写入数据方面， BLOB 和 LongBLOB 的写入速度通常要慢于其他数据类型。这是因为：BLOB 和 LongBLOB 存储的数据类型是二进制数据，当插入数据时，需要将这些二进制数据转换成“十六进制”等文本类型，然后再插入到数据库中。这种转换需要消耗较多的 CPU 时间，因此插入数据的速度会变慢。

索引

在 MySQL 中， BLOB 和 LongBLOB 数据类型不能被用于索引操作。这是因为这些数据类型存储的内容不是文本类型，无法进行比较运算。因此，在进行索引操作时，应该考虑使用其他数据类型。

备份和恢复

在进行备份和恢复操作时， BLOB 和 LongBLOB 数据类型需要特别注意。这是因为这些数据类型所存储的数据比较大，因此备份和恢复需要更多的时间和空间。同时，如果备份和恢复过程中出现错误，可能会丢失大量的二进制数据。