英文:
Mysql - end to end compression
问题
我正在使用Java的MySQL（标准jdbc连接）。
我知道我可以启用连接压缩（以便在数据从Java生成后传递到MySQL服务器之前进行压缩）。还有一个选项可以启用InnoDB行压缩，以便到达MySQL的数据以压缩状态保存。
问题是 - 由于从Java客户端接收到的压缩数据需要在MySQL中解压缩，然后再次压缩，是否有可能减轻这种情况？我只想将其保存在压缩状态并节省CPU周期。
英文:
I'm using MySQL with Java (standard jdbc connection).
I know that I can enable connection compression (so that the data generated in Java is compressed before getting to MySQL server). There is also an option to enable InnoDB Row compression so that the data that reaches MySQL is then saved in a compressed state.
Question is - since when compressed data is received by MySQL from java client it needs to be decompressed and then again compressed in this case - is it possible to mitigate this? I just wanted to have it saved in compressed state and save CPU cycles.
答案1
得分: 0
You could compress the strings yourself using Java's Deflater class. Then store the bytes resulting from the compression in a binary column (e.g. VARBINARY, BLOB, LONGBLOB).
There's no need to use MySQL's compressed protocol or compressed columns if you store such pre-compressed data. It won't be compressed any further, it will just be wasted CPU cycles.
The disadvantage of storing strings you compress in the client is that you can't use SQL expressions to manipulate the original string. You can only store and fetch the compressed bytes verbatim.
Re your comment:
The closest thing is the JDBC URL property useCompression, but as you know this only applies to the networking protocol, not to storage.
A few things prevent the end-to-end compression that you want:
-
MySQL's storage engine architecture. The network protocol is independent of storage engine. The compression algorithm used by a given storage engine might not be the same compression algorithm used in the protocol.
-
SQL query result sets may be combined from joining multiple tables. Joined tables may use compression or not, and may even use different storage engines.
-
Compression has always been more or less experimental in MySQL, and there isn't enough demand to implement all the code required to handle the combinations implied by the above cases. As the saying goes, pull requests welcome.
英文:
You could compress the strings yourself using Java's Deflater class. Then store the bytes resulting from the compression in a binary column (e.g. VARBINARY, BLOB, LONGBLOB).
There's no need to use MySQL's compressed protocol or compressed columns if you store such pre-compressed data. It won't be compressed any further, it will just be wasted CPU cycles.
The disadvantage of storing strings you compress in the client is that you can't use SQL expressions to manipulate the original string. You can only store and fetch the compressed bytes verbatim.
Re your comment:
The closest thing is the JDBC URL property useCompression, but as you know this only applies to the networking protocol, not to storage.
A few things prevent the end-to-end compression that you want:
-
MySQL's storage engine architecture. The network protocol is independent of storage engine. The compression algorithm used by a given storage engine might not be the same compression algorithm used in the protocol.
-
SQL query result sets may be combined from joining multiple tables. Joined tables may use compression or not, and may even use different storage engines.
-
Compression has always been more or less experimental in MySQL, and there isn't enough demand to implement all the code required to handle the combinations implied by the above cases. As the saying goes, pull requests welcome.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论