MySQL压缩的使用场景和解决方案
技术点来源于每周技术分享会,对于MySQL压缩的技术方案进行了分享,针对本次分享思考是否使用技术方案改造,并代入demo。
压缩相关的场景
- 客户端与服务端传输的数据量太大需要压缩,用于节省带宽。
- 当某个表或某列数据量大时,对某列进行压缩(官方指出可进行对表和列的压缩)。
- 当用于日志记录,序列化或json化大量item数据时对此列进行压缩。
- 数据压缩以提高CPU利用率为代价,实现了更小的数据库大小、减少I/O和提高吞吐量
翻阅大量资料才找到官网出处,附链接,我在《高性能MySQL》第三版中也并未找到此章节。
https://docs.gitcode.net/mysql/guide/the-innodb-storage-engine/innodb-compression-usage.html
对于表压缩甚至官方的文档也侧重于解释带来的好处,并没有压缩列常用,合理看下压缩表的限制就知道为什么了。
压缩表的限制
- 压缩表不能存储在InnoDB系统表空间(日常我们优先选择InnoDB存储引擎的)。
- 通用表空间可以包含多个表,但压缩表和未压缩表不能在同一个通用表空间中共存(对于数据库也太不友好,不好管理了)。
- 压缩适用于整个表及其所有关联索引,而不适用于单个行,尽管有子句名称ROW_FORMAT.
- InnoDB不支持压缩临时表。什么时候innodb_strict_mode已启用(默认),创建临时表如果返回错误ROW_FORMAT=压缩要么KEY_BLOCK_SIZE被指定。如果innodb_strict_mode被禁用,发出警告并使用非压缩行格式创建临时表。同样的限制适用于更改表对临时表的操作。
那么我们直接进入压缩列的部分。
MySQL的列压缩
MySQL 针对列的压缩目前直接的方案并不支持,但是在业务层面使用 MySQL 提供的压缩和解压函数来针对列进行压缩和解压操作。也就是要对某一列做压缩,就需要在写入的时候调用 COMPRESS 函数对那个列的内容进行压缩,然后存放到对应的列。读取的时候,使用 UNCOMPRESSED 函数对压缩的内容进行解压缩
适用场景:针对MySQL中某个列或者某几个列数据量特别大,一般都是varchar、text、char等数据类型(如果内容包含emoj表情则注意需要使用编码类型为utf8mb4)。
压缩算法
一些操作系统在文件系统级别实现压缩。文件通常被分成固定大小的块,这些块被压缩成可变大小的块,这很容易导致碎片。每次修改块内的某些内容时,都会在将整个块写入磁盘之前对其进行重新压缩。这些特性使得这种压缩技术不适合在更新密集型数据库系统中使用。
innodb 压缩借助的是著名的 zlib 库,采用 L777 压缩算法,这种算法在减少数据大小和 CPU 利用方面很成熟高效。同时这种算法是无损的,因此原生的未压缩的数据总是能够从压缩文件中重构,LZ777 实现原理是查找重复数据的序列号然后进行压缩,所以数据模式决定了压缩效率,一般而言,用户的数据能够被压缩 50%以上。
- Java的工具包(java.util.zip)下提供了Inflater和Deflater工具类来实现压缩和解压缩处理
支持的字段类
压缩 BLOB、VARCHAR 和 TEXT 列
在 InnoDB 表中,BLOB、VARCHAR 和 TEXT不属于主键的列可以存储在单独分配的溢出页面.我们将这些列称为页外栏.它们的值存储在溢出页面的单链表中,压缩后的数据不能直接在SQL中查询显示,所以建议使用在记录列上。
mybaits-plus 处理方式类型处理器,用于 JavaType 与 JdbcType 之间的转换,用于 PreparedStatement 设置参数值和从 ResultSet或CallableStatement中取出一个值,mybaits-plus内置常用类型处理器通过TableField注解快速注入到 mybatis 容器中。
@Data
@Accessors(chain = true)
@TableName(autoResultMap = true)
public class User {
private Long id;
...
/**
* 注意!!必须开启映射注解
*
* @TableName(autoResultMap = true)
*
* 以下两种类型处理器,二选一 也可以同时存在
*
* 注意!!选择对应的 JSON 处理器也必须存在对应 JSON 解析依赖包
*/
@TableField(typeHandler = JacksonTypeHandler.class)
// @TableField(typeHandler = FastjsonTypeHandler.class)
private OtherInfo otherInfo;
}
那么针对本次的压缩类型来说则指定为TypeHandler来实现BaseTypeHandler(org.apache.ibatis.type.BaseTypeHandler)
实例SQL
select length(compress('你好?')); -- 返回压缩后的字节数
select convert(uncompress(compress('hello ?')) using 'utf8mb4')
insert into table xxx (对要压缩的字段使用compress())
对比压缩前后数据
SELECT UNCOMPRESSED_LENGTH(content) AS length, LENGTH(content) AS compress_length, UNCOMPRESS(content), content FROM `test_compress`
注意:当字段类型为longtext时不适用,表结构虽然为utf8mb4但仍旧报错
对于日常请求入参出参的记录表来说,如果长时间不使用该字段做任何业务逻辑,短暂问题也会通过ELK来查询问题,建议对参数字段进行压缩,能够大大减少内存使用,并通过调节设置参数减少使用CPU资源。
--- 我站在十字路口,如同站在时间长河的上游,转念回首,它仿佛踏着神辉在游走。
相关文章
- 学生数据库管理系统
- SpringDataJpa 用MySQL语句怎么分页,spring全家桶SpringDataJpa 用MySQL语句怎么分页
- Docker创建MySQL容器模板命令
- Elasticsearch对应MySQL的对应关系
- 使用SpringDataJpa保存(save)报错误:SQL Error: 1062, SQLState: 23000 控制台会报:Duplicate entry ‘数‘ for key ‘PRIMA
- Navicat Premium 连接sqlserver数据库时提示安装Client失败,解决方案
- Mysql查询当前用户所有数据库语句(SHOW DATABASES)
- MySQL语句-查看当前数据库有哪些表(SHOW TABLES)
- MySQL5.0版本以上新增的 information_schema 数据库是什么?
- MariaDB数据库备份之逻辑备份
- MariaDB数据库创建用户
- MariaDB数据库给用户授权
- MariaDB数据库刷新权限表命令
- MariaDB数据库删除用户命令
- PhpStudy 2016搭建-sqli-libs靶场
- MySQL手动注入步骤
- Pikachu靶场-SQL注入-数字型注入(post)过关步骤
- Pikachu靶场-SQL注入-字符型注入(get)过关步骤
- 利用SQL注入漏洞实现MySQL数据库读写文件
- Kali-工具-sqlmap常见用法