Mysql数据压缩(表或列压缩)

问题:当用户量上升到一定数据时,mysql存在性能问题,如何减少表数据存储空间进行下面优化
MySQL自带压缩和解压缩函数

1 表列数据压缩

测试数据270w条存表(room_record1)中存储空间9G

CREATE TABLE `room_record1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `room` int(11) NOT NULL,
  `uuid` varchar(100) NOT NULL,
  `info` text NOT NULL,
  `timestamp` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  PRIMARY KEY (`id`,`timestamp`),
  KEY `room` (`room`),
  KEY `uuid` (`uuid`),
  KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

在这里插入图片描述
优化字段:压缩info字段
占用空间:
在这里插入图片描述

优化表结构:

CREATE TABLE `room_record2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `room` int(11) NOT NULL,
  `uuid` varchar(100) NOT NULL,
  `info` blob NOT NULL,
  `timestamp` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  PRIMARY KEY (`id`,`timestamp`),
  KEY `room` (`room`),
  KEY `uuid` (`uuid`),
  KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

INSERT INTO  `room_record2` (`id`, `room`, `uuid`, `info`, `timestamp`, `type`) VALUES ('202737598', '434436', 'f2ba4943-6779-494d-a103-0b86873462a3',compress( '[[0,2,\"4s\",[1,431642,-500,\"1s,1b,2s,2s,2b,2b,3b,4b,5s,8b,9s,9s,9b,Tb\",-2,-2,0,-1,\"194.5\"],[2,634577,295,\"2s,2s,2b,2b,3b,4b,4b,5b,6s,6s,7s,7b,8s,9b\",-2,-2,0,-1,\"829\"],[0,2,2,1,0,1,0,0,1,0,0,0,1,0,1,0,0,0,0,0,0,10,1,0,2,5,{\"min_gold_ka\":3,\"less_score_free\":5,\"limit_enter_battle_score\":50,\"limit_battle_score\":20,\"score_limit_open\":1,\"score_max_limit\":11,\"min_limit_gold_ka\":100,\"score_limit_open1\":1,\"score_max_limit1\":150,\"min_limit_gold_ka1\":300,\"less_score_reback_header\":-1,\"choupai_num\":0,\"tuoguan\":120,\"noreback\":0,\"beilv\":50,\"fanbei\":2,\"less_score\":5,\"fanbei_open\":1,\"add_score_open\":0,\"less_score_add_level\":1,\"add_score\":1,\"hutoall\":0,\"zhangshu\":3,\"changshuang\":1,\"tuoguan_mode\":1}]],[1,2,12,\"4s\",0,0,[[1,0,0],[2,0,0]]],[1,2,11,\"9b\",0,0,[[1,0,0],[2,0,0]]],[2,1,\"2,\"],[1,1,1,\"9b,9s,9s,9b,8b,Tb\",0,0,[[1,0,0],[2,0,0]]],[1,1,11,\"4b\",0,0,[[1,0,0],[2,0,0]]],[2,2,\"1,2,\"],[1,2,2,\"4b,4b,4b\",3,0,[[1,0,0],[2,0,0]]],[1,2,11,\"4s\",3,0,[[1,0,0],[2,0,0]]],[1,2,7,\"4s\",3,0,[[1,0,0],[2,0,0]]],[1,1,12,\"1s\",0,0,[[1,0,0],[2,0,0]]],[2,1,\"2,\"],[1,1,10,\"1s\",0,0,[[1,0,0],[2,0,0]]],[1,1,7,\"1s\",0,0,[[1,0,0],[2,0,0]]],[1,2,12,\"Ts\",3,0,[[1,0,0],[2,0,0]]],[1,1,9,\"\",0,0,[[1,0,0],[2,0,0]]],[1,1,12,\"Ts\",9,0,[[1,0,4],[2,0,-4]]],[3,1,[1,4,4,-500],[2,-4,-4,295],[1,\"1s,1b,2s,2s,2b,2b,3b,5s\"],[2,\"2s,2s,2b,2b,3b,5b,6s,6s,7s,7b,8s\"],[1,2,1,4,-1,0,2,-4,-1,0,3,1,\"Ts\",0,0,29,\"5s\",\"7b\",\"Tb\",\"4b\",\"5b\",\"6s\",\"9b\",\"3s\",\"3s\",\"9s\",\"6b\",\"Tb\",\"1s\",\"4s\",\"8s\",\"Ts\",\"7b\",\"6b\",\"3s\",\"Tb\",\"5b\",\"1b\",\"8b\",\"Ts\",\"7s\",\"7b\",\"5s\",\"5s\",\"7s\",2,1,8,\"1s\",\"1b\",\"2s\",\"2s\",\"2b\",\"2b\",\"3b\",\"5s\",2,11,\"2s\",\"2s\",\"2b\",\"2b\",\"3b\",\"5b\",\"6s\",\"6s\",\"7s\",\"7b\",\"8s\",5,5,0,3,\"9b\",\"9s\",\"9s\",3,0,3,\"9b\",\"8b\",\"Tb\",2,6,3,\"1b\",\"2b\",\"3b\",2,3,3,\"1s\",\"5s\",\"Ts\",5,0,3,\"2s\",\"2s\",\"2b\",9,4,\"Ts\",2,1,4,2,-4,2,434436,1,0,1582128000,4,10,0]]]'), '1582128000', '18');

先说明一下Blob的类型
1、MySQL有四种BLOB类型:
  ·tinyblob:仅255个字符
  ·blob:最大限制到65K字节
  ·mediumblob:限制到16M字节
  ·longblob:可达4GB
2、除了类型对后面存取文件大小有限制,还要修改mysql的配置文件。
  Windows、linux基本一样通过修改文件my.ini或my.cnf文件,在文件中增加 max_allowed_packet=10M(就是最大10M,mysql默认似乎1MB,增加前先查找一下确保没有设置过)
3、做了以上设置后,如果上传较大一点文件时或者某些文件时还是出错,如报一些乱码,估计就是下面的问题了。
数据库或表的字符集问题,如hibernate连接使用utf-8,表是gbk等,一般只要设置hibernate中数据连接部分就行,如
jdbc:mysql://192.168.0.4:3306/test?useUnicode=true&characterEncoding=UTF-8

  • 压缩
    使用函数COMPRESS :COMPRESS(COL)即可,如下图,是一个测试压缩的一列。压缩后列格式为blob格式BLOB字段用于存储二进制数据,是一个可以存储大量数据的容器),压缩率能达到80~90%
  • 解压
    使用函数UNCOMPRESS :UNCOMPRESS(col)即可,由于被压缩后代额格式是blob,那么解压后的格式也为blob,还得转换一层才能查看的到,如下所示:
select id,info '存储的',UNCOMPRESS(info)'解压的' ,cast(UNCOMPRESS(info) as char) '解压并转换' from room_record2

表(room_record2)数据:
在这里插入图片描述
优化压缩后数据存储空间:
在这里插入图片描述
总结:通过表列压缩可以节省表存储空间的60%效果,结果还是比较客观
弊端:在查询数据是需要通过函数解压数据,会产生查询时间消耗,通过时间换取空间达到存储性能问题

2 表数据压缩

可以参考
表压缩:https://www.cnblogs.com/mysql-dba/p/5125220.html

  • 2
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

菜鸟-要努力

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值