CODEWITHSUNDEEP
mysqlblobstorage
grit639
grit639

Reputation: 346

how blobs are stored in mysql

I have read that some storage engine of mysql may store blobs "externally".

My question is:

  1. What is the benefit of storing blobs in external space and not in-line?
  2. How are the externally stored blobs tied to the original record?

Upvotes: 4

Views: 1312

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562881

Blobs are "external" in the sense that they may not be stored on same page with the rest of the columns of a given row. They are still stored in the same tablespace file with the row they are associated with.

The advantage is that blobs can be larger than a single page. InnoDB pages are of fixed size, by default they are 16KiB. Blobs may be up to 64KiB, and Mediumblob and Longblob may be up to 16MiB and 4GiB, respectively. That's obviously going to take quite a few pages.

How they are tied to the row varies by row format. In all cases, if a given Blob happens to fit in the page, it is stored in the page. That is, even though Blob allows up to 64KiB, you might typically store shorter content.

If the Blob is too long to be stored in the page, there are two ways this is handled:

  • In row format of redundant or compact, the first 768 bytes is stored with the row, and then there's a pointer to the page where the remainder of the Blob begins to be stored.

  • In row format of dynamic or compressed, the Blob is replaced in the row page with a 20-byte pointer to the page where the entire Blob begins.

External pages for the Blob are allocated one by one up to 32 pages (half of one megabyte). If the Blob is larger than that, further pages are allocated 64 at a time (1MiB).

Text/Mediumtext/Longtext and Varchar/Varbinary are also handled similarly. They can be larger than a single page, so they may need to be stored on multiple pages.

Upvotes: 5

Related Questions