mysql 大字段 性能 mysql 大字段存储

前言

       最近在做一个分布式任务调度系统，支持万级的JOB调度，支持任务编排，涉及到公司的核心业务。在做系统时出现MySQL存储某个字段很大的问题，超过text的长度，导致查询更新性能低下。

1. demo

模拟MySQL数据库，可以看到有个text字段，然而在开发时以前存储了json字符串，大小居然超过2M，必须使用mediumtext才能存储，而且经常更新json的部分字段，MySQL本身是关系型数据库，应该做成关联表存储，但是已经上线的系统必须及时优化，先满足现有的性能要求。

如上图所示，模拟数据库表，本身id字段是建有索引的，索引B+树查找，效率极高。性能瓶颈分析可以看出来源于MySQL读写。单个字段长度超过MySQL的page页。

模拟一条数据，数据是我百度的模板作文

看一下长度,LENGTH(str) 函数查看字符串的字节长度

2.6KB左右，这个是笔者模拟的。实际很恐怖，笔者的数据库存过3-4M一个字段，由于是以前的设计，短时间内无法优化数据库设计。只能在字段上下功夫

2. MySQL compress,uncompress

笔者想到了MySQL可以压缩字符串，自带函数compress,uncompress，让我们来试试

使用上面的数据库demo

SELECT COMPRESS(b.text) from business b WHERE b.businessId=1;

可以看出MySQL有3个与压缩相关的函数，试一下

可以看出只有1.54KB，类型变成了BLOB类型，二进制了。但毫无疑问压缩了1KB多一点。

缺点：对象需要代码在转换成字符串。

为了更直观，模拟一个2M的数据

压缩一下

不到14K，足足压缩了148.88倍，说明字段越大，压缩率越高，实际上笔者测试读写效率明显提高。牺牲了部分CPU的运算能力，但对笔者而言是能接受的。性能明显提升。

2.1 注意blob类型

根据官方链接，可以直接转换成string类型：官方链接

笔者测试

any string，OK，但是Navicat不行，变成blob类型了，估计Navicat做了处理，其他工具不知道是否正常。

笔者自己转换一下正常了

SELECT CONVERT (UNCOMPRESS(COMPRESS(b.text)) USING utf8) textStr from business b WHERE b.businessId=1;

查看结果，OK了

3. 代码压缩字符串

使用Gzip算法或者其他算法，比如7z算法等压缩字符串，压缩率越高，压缩与解压时间越长，这需要在这之间寻找平衡点。综合可以选择GZIP。

package com.feng.gzip;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;

public class GZipTest {
    public static void main(String[] args) {
        String str = "西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀西都可以低估，但唯独不能低估青春的能量；青年时期的积累与沉淀";

        String compressStr = compress(str);
        System.out.println(str.length());
        System.out.println("压缩后\t" + compressStr.length());

        String uncompressStr = uncompress(compressStr);
        System.out.println(uncompressStr);

        System.out.println("解压后\t" + uncompressStr.length());
    }

    public static String compress(String str) {
        if (str == null || str.trim().length() == 0) {
            return null;
        }

        try (ByteArrayOutputStream out = new ByteArrayOutputStream();
             GZIPOutputStream gzip = new GZIPOutputStream(out)) {
            gzip.write(str.getBytes("utf-8"));
            gzip.close();

            return new String(out.toByteArray(), "iso-8859-1");
        } catch (Exception e) {
            e.printStackTrace();
            return str;
        }

    }

    public static String uncompress(String str) {
        if (str == null || str.trim().length() == 0) {
            return null;
        }

        try (ByteArrayOutputStream out = new ByteArrayOutputStream();
             ByteArrayInputStream in = new ByteArrayInputStream(str.getBytes("iso-8859-1"))){
            GZIPInputStream ungzip = new GZIPInputStream(in);
            byte[] buffer = new byte[1024];
            int n;
            while ((n = ungzip.read(buffer)) >= 0) {
                out.write(buffer, 0, n);
            }

            return new String(out.toByteArray(), "utf-8");
        } catch (Exception e) {
            e.printStackTrace();
            return str;
        }

    }
}

run

可以看出压缩后空间明显变小，而且可以直接转为字符串，不用存储blob类型。

当然还有zip压缩，很有名气，压缩比率不高，但速度快

 

总结

      笔者主要通过GZIP压缩来降低text文本的存储空间，来提升速度的，只是对部分字段GZIP压缩不明显就使用数据库压缩方式。这需要根据实际数据来测试。也许可以试试两种方式同时使用，但这样复杂度很高，先GZIP压缩，然后数据库压缩，blob存储，这种方式理论可行，只是代码需要处理对称，压缩与解压要一一对应；另外还要计算得失，避免CPU引起的瓶颈。