BLOB Sütun Tipleri

Giriş

4 tane BLOB tip var. Bunlar şöyle

BLOB         A small BLOB

TINYBLOB         A very small BLOB (binary large object)

MEDIUMBLOB A medium-sized BLOB

LONGBLOB         A large BLOB

Depolama

Açıklaması şöyle. 4 çeşit depolama yöntemi var

DYNAMIC , REDUNDANT , COMPACT , COMPRESSED 

Another aspect is the MySQL’s storage row format. Depending on row format, MySQL stores data differently.

DYNAMIC row format for BLOB stores 20-byte pointer to where it’s stored. As opposed to REDUNDANT and COMPACT row formats, which store first 768 bytes in a table and the rest in overflow pages. In this benchmark data sizes start from 1028 bytes, so this kind of optimization is irrelevant.

COMPRESSED row format enables compression on database level, so 20-byte pointer will point to another storage which will be compressed using deflate with 4-byte length prefix. For the benchmark sake we will use the same algorithm on application level to see the overhead of MySQL over compression. You may find useful another blog post with overview of MySQL compression.

Örnek

Şöyle yaparız

CREATE TABLE `uncompressed_blobs` (
  `id` INT NOT NULL PRIMARY KEY,
  `data` MEDIUMBLOB NOT NULL
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Açıklaması şöyle

In this table we will store either uncompressed data or data compressed on an application level without using MySQL compression (obviously, we want to avoid double compression as it ineffective).

Örnek

Şöyle yaparız

CREATE TABLE `compressed_blobs` (
  `id` INT NOT NULL PRIMARY KEY,
  `data` MEDIUMBLOB NOT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

Sütun Tipleri - JSON

Örnek

Şöyle yaparız

CREATE TABLE `kv` (
  `key` varchar(32) NOT NULL PRIMARY KEY,
  `val` JSON,
);

INSERT INTO kv
  SET `key` = 'test', val = '{"name": "val"}'
  ON DUPLICATE KEY UPDATE val = '{"name": "val"}';

SELECT val FROM kv WHERE `key` = 'test' LIMIT 1;