I’m running in this misconception second time in a week or so, so it is time to blog about it.
How blobs are stored in Innodb ? This depends on 3 factors. Blob size; Full row size and Innodb row format. >>mysql innodb存储引擎下如何存储BLOB,TEXT以及long VARCHAR这类的大对象？ 它会根据如下三个因素决定如何存储这些大对象，1.大对象的size；2.整个行的size；innodb 的 row format。
But before we look into how BLOBs are really stored lets see what misconception is about. A lot of people seems to think for standard (“Antelope”) format first 768 bytes are stored in the row itself while rest is stored in external pages, which would make such blobs really bad. I even seen a solution to store several smaller blobs or varchar fields which are when concatenated to get the real data. This is not exactly what happens >>在我们具体的说明mysql innodb存储引擎如何存储大对象之前，我们先来看一下大家对于该知识点可能存在的误解。很多人可能在innodb_file_format=Antelope时，mysql会把大对象的前768 bytes数据保存在行的本地页中(B-tree Node)，剩下的部分放在external pages(溢出页，也就是Uncompressed BLOB Page)。
With COMPACT and REDUNDANT row formats (used in before Innodb plugin and named “Antelope” in Innodb Plugin and XtraDB) Innodb would try to fit the whole row onto Innodb page. At least 2 rows have to fit to each page plus some page data, which makes the limit about 8000 bytes. If row fits completely Innodb will store it on the page and not use external blob storage pages. For example 7KB blob can be stored on the page. However if row does not fit on the page, for example containing two 7KB blobs Innodb will have to pick some of them and store them in external blob pages. It however will keep at least 768 bytes from each of the BLOBs on the row page itself. With two of 7KB blobs we will have one blob stored on the page completely while another will have 768 bytes stored on the row page and the remainder at external page. >>innodb_file_format=Antelope时，建表时支持COMPACT 和 REDUNDANT两种 row format。innodb 每个页至少保存两个以上row，并且加上一些页信息，就是说每行必须小于 8000 bytes(innodb存储引擎表结构是索引组织表，这样每个页中至少应该有两条记录，否则就失去了B+Tree的意义，变成一个链表了)。如果行的size小于这个限制，那么就可以保存在innodb索引页中(不论是否使用了BLOB等类型)，不用使用external blob storage page。例如你的行中包含一个7KB的blob字段，那么该行可以保存在innodb 索引页中。然而如果你行的size 超过限制，那么innodb必须选择该行中的一些blob字段，放到external blob pages上(其实是在本地索引页放blob 的前768bytes 剩下的放在external blob pages)。例如一行中有两个7KB的blob字段，那么innodb会把其中一个blob字段保存在索引页中，另一个blob字段的前768 bytes保存在索引页中，剩下部分保存在 external blob page上。
Such decision to store first 768 bytes of the BLOB may look strange, especially as MySQL internally has no optimizations to read portions of the blob – it is either read completely or not at all, so the 768 bytes on the row page is a little use – if BLOB is accessed external page will always have to be read. This decision seems to be rooted in desire to keep code simple while implementing initial BLOB support for Innodb – BLOB can have prefix index and it was easier to implement index BLOBs if their prefix is always stored on the row page. >>BLOG字段会在索引页保存 768 bytes数据(COMPACT或者REDUNDANT row format时)，这个很奇怪。因为对于BLOB这类大对象，mysql读取时无法只读取其部分数据，要么不读，要么就读取全部数据。mysql之所以这样设计可能是想让BLOB支持前缀索引的代码实现起来更简单。
This decision also causes strange data storage “bugs” – you can store 200K BLOB easily, however you can’t store 20 of 10K blobs. Why ? Because each of them will try to store 768 bytes on the row page itself and it will not fit. >>这样的设计也导致了下面的数据存储问题，你可以保存一个包含200K的BLOB行，但是你无法保存包含20个10K BLOB字段的行。为什么呢？因为如为每个BLOB字段在本地保留768 bytes 就超过了 8000 bytes的限制。
Another thing to beware with Innodb BLOB storage is the fact external blob pages are not shared among the blobs. Each blob, even if it has 1 byte which does not fit on the page will have its own 16K allocated. This can be pretty inefficient so I’d recommend avoiding multiple large blobs per row when possible. Much better decision in many cases could be combine data in the single large Blob (and potentially compress it) >>另一个关于值得关注的BLOB等大对象存储的问题是，external blob pages是不能在blobs之间共享的。每个blob即使它只需要1 byte的external blob page空间也会独自占有一个page(mysql 默认情况下一个页16K)。这样对空间的利用是非常低效的，所以我建议避免在一行中出现多个large blob字段。我们应该尽可能把大对象组合起来放在一个BLOB中，并对它进行压缩(如果可能的话)
If all columns do not fit to the page completely Innodb will automatically chose some of them to be on the page and some stored externally. This is not clearly documented neither can be hinted or seen. Furthermore depending on column sizes it may vary for different rows. I wish Innodb would have some way to tune it allowing me to force actively read columns for inline store while push some others to external storage. May be one day we’ll come to implementing this in XtraDB >>如果一行数据大于8000 bytes，innodb会自动选择它们中的一些字段放在external blob pages，至于根据什么规则选择哪些行放在external blob pages这个没有文档给出明确的说明。此外同一个表中根据字段size的不同，不同的行可能选择的结果可能不同。我希望 innodb能够提供一些方法让我可以强制让某些(这些字典会经常被使用到)列放在索引页，把另一些不常使用的列放在external blob pages。也许有一天我们会在XtraDB存储引擎中中实现这个(这边文章的作者 Peter Zaitsev 在2006之前管理着mysql High Performance团队，2006年他创立了Percona公司)
So BLOB storage was not very efficient in REDUNDANT (MySQL 4.1 and below) and COMPACT (MySQL 5.0 and above) format and the fix comes with Innodb Plugin in “Barracuda” format andROW_FORMAT=DYNAMIC.In this format Innodb stores either whole blob on the row page or only 20 bytes BLOB pointer giving preference to smaller columns to be stored on the page, which is reasonable as you can store more of them. BLOBs can have prefix index but this no more requires column prefix to be stored on the page – you can build prefix indexes on blobs which are often stored outside the page. >>在REDUNDANT (MySQL 4.1 and below)和COMPACT (MySQL 5.0 and above) 两种row_format格式下BLOB存储的效率并不高。从innodb 1.0.6开始引入了新的文件格式 Barracuda(之前用的是Antelope)，innodb_file_format=Barracuda在支持之前的REDUNDANT和COMPACT row_format基础上新增了Dynamic和Compressed两种 row_format，新的row_format能够高效的保存BLOB等大对象。Dynamic格式下和之前保存BLOB等大对象的不同之处是，如果需要把大对象保存到external blob pages，那么只会在索引页上保存一个 20 bytes的指针(而之前是保存大对象的前768 bytes)，而实际的数据都放在external blob pages中。
COMPRESSED row format is similar to DYNAMIC when it comes to handling blobs and will use the same strategy storing BLOBs completely off page. It however will always compress blobs which do not fit to the row page, even if KEY_BLOCK_SIZE is not specified and compression for normal data and index pages is not enabled. >>COMPRESSED row_format在存储BLOB等大对象时策略与 DYNAMIC类似。另外，Compressed行格式会把存储在其他页面中的数据压缩处理。
If you’re interested to learn more about Innodb row format check out this page in Innodb docs:
It is worth to note I use BLOB here in a very general term. From storage prospective BLOB, TEXT as well as long VARCHAR are handled same way by Innodb. This is why Innodb manual calls it “long columns” rather than BLOBs. >>注意在上面的描述中BLOB只是一个统称，表示mysql中支持的所有大对象比如BLOB,TEXT,LONG VARCHAR(应该还包括LONG VARBINARY)，innodb 对mysql所有的大对象处理都是一样的。所以innodb 帮助中说 "long columns" 而不是 BLOBs的原因。
407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
