tutorial on improving storage efficiency of InnoDB table blob columns in Mysql _mysql

First, introduce a few key points about the InnoDB engine storage format:
1, InnoDB can choose to use shared table space or independent table space, the proposed use of independent table space, easy to manage, maintain. Enable the innodb_file_per_table option, after 5.5 can be dynamically modified online, and execute ALTER TABLE XX ENGINE = InnoDB the existing table into a stand-alone table space, earlier than 5.5 version, after modifying this option, need to restart to take effect;
2, InnoDB's data page default 16kb,5.6 version, the new option Innodb_page_size can be modified, in 5.6 before the version, can only modify the source code recompile, but not recommended to modify this configuration, unless you are very clear about its advantages and disadvantages;
3, the InnoDB data page in the new write, will reserve 1/16 of space, reserved space can be used for subsequent new record writing, reduce the frequent increase in the cost of data page;
4, each data page, you need to store at least 2 rows of records. So theoretically the maximum length of the row is 8KB, but in fact it should be smaller, because there are some INNODB internal data structures to store;
5, limited by InnoDB storage mode, if the data is sequentially written, the most ideal case, the data page fill rate is 15/16, but generally do not guarantee full sequential write, therefore, the data page filling rate is generally 1/2 to 15/16. Therefore, each InnoDB table is best to have a self added column as the primary key, so that the new record write as much as possible in order;
6, when the data page filling rate of less than 1/2, InnoDB will shrink, free free space;
7, MySQL 5.6 version of the INNODB engine currently supports the compact, redundant, DYNAMIC, compressed four formats, the default is the compact format, compressed with little and not recommended (see next), If you need to use the compression characteristics, you can directly consider the TOKUDB engine;
8, the compact line format compared to redundant, probably can save 20% of the storage space, compressed compared to the compact may save 50% of the storage space, but will lead to TPS down 90%. Therefore, it is strongly not recommended to use compressed line format;
9, when the row format is dynamic or compressed, long columns such as TEXT/BLOB (long column, also may be other longer columns, not necessarily only text/blob type, see specific circumstances) will be completely stored in a separate data page, The clustered index page uses only 20-byte pointers to point to the new page, which is called Off-page, which resembles Oracle-like row migrations, is more wasteful of disk space, and has poor I/O performance. Therefore, it is strongly not recommended to use BLOB, TEXT, over 255-length varchar column types;
10, when the InnoDB file format (Innodb_file_format) is set to Antelope, and the line format is compact or redundant, BLOB, The text or long varchar column will only store its first 768 bytes in the aggregate cable page (the maximum of 768 bytes is easy to create a prefix index/prefix index), and the rest of the content is stored in the extra page, even if it is just one more byte. Therefore, the shorter the length of all columns, the better;
11, the off-page stored in the Blob, text or long varchar column of the page is exclusive, can not be shared. Therefore, it is strongly not recommended to use multiple long columns in one table.

In summary, if you really need to store blobs, TEXT, and long varchar columns in the InnoDB table in the actual business, there are several suggestions:
1, as far as possible, all data serialization, compression, stored in the same column, to avoid multiple off-page;
2, the actual maximum storage length of less than 255 of the column, converted to varchar or char type (if it is variable length data, if it is fixed-length data, use char type);
3, if you cannot consolidate all the columns to a column, you can return to the second, according to the maximum length of each column to split into a number of child tables, as far as possible is the total row length of each child table is less than 8KB, reduce the frequency of occurrence of off-page;
4, the above recommendation is in the data page for the default 16KB premise, if modified to 8KB or other size, please use the above theory to test, to find the most appropriate value;
5, character column length is less than 255, whether using char or varchar to store, or the varchar column length defined as 255, will not cause the actual table space to increase;
6, generally in the game field will be used more than a BLOB column type, the game industry peers can be concerned about.

The following is the test verification process, patient students can slowly look:

# # Test Case: Long-column storage efficiency in INNODB # test Scenario Description: # Store 64KB of data in InnoDB table, compare various storage modes # Write 5000 rows per table, observe final tablespace file size comparison # #表0: All data is stored in a BLOB column creat E TABLE ' t_longcol_0 ' (' id ' int ' unsigned NOT null auto_increment, ' longcol ' blob not null COMMENT ' store all data in

A BLOB column ', PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8 row_format=compact;
#相应的数据写入存储过程: Mysp_longcol_0_ins () CREATE PROCEDURE ' Mysp_longcol_0_ins ' (in CNT int) begin SET @i = 1;
While @i < CNT did insert into T_LONGCOL_0 (longcol) Select repeat (' a ', 65535);
Set @i = @i + 1;
End while;

End #表1: Store 64KB bytes on average in 9 columns CREATE TABLE ' t_longcol_1 ' (' id ' int "unsigned NOT null auto_increment, ' longcol1 ' blob not NULL
COMMENT ' Store all data in 9 blob columns ', ' longcol2 ' blob not null, ' longcol3 ' blob not null, ' LONGCOL4 ' blob not NULL, ' LONGCOL5 ' blob not null, ' longcol6 ' blob isn't null, ' LONGCOL7 ' blob not null, ' Longcol8 ' blob not null, ' LONGCOL9 ' blob NO

T NULL, PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8; #相应的数据写入存储过程:Mysp_longcol_1_ins () CREATE PROCEDURE ' Mysp_longcol_1_ins ' (in CNT int) begin SET @i = 1; While @i < CNT did insert into T_longcol_1 (Longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8, LONGCOL9) Select repeat (' a ', 7500), repeat (' a ', 7500), repeat (' a ', 7500), repeat (' a ', 7500), repeat (' a ', 7500) and repeat (' a ')
, 7500), repeat (' a ', 7500), repeat (' a ', 7500), repeat (' a ', 5535);
Set @i = @i + 1;
End while;

End #表2: Storage of 64KB data in multiple BLOB columns CREATE TABLE ' t_longcol_2 ' (' id ' int ' unsigned not NULL auto_increment, ' longcol1 ' blob not N ull COMMENT ' Store bytes data ', ' longcol2 ' blob not null COMMENT ' store-bytes data ', ' longcol3 ' blob not null COMM ENT ' Store bytes data ', ' LONGCOL4 ' blob not null COMMENT ' store-bytes data ', ' LONGCOL5 ' blob not null COMMENT ' sto Re bytes Data ', ' longcol6 ' blob not null COMMENT ' store 255 bytes data ', ' LONGCOL7 ' blob not null COMMENT ' store 368 b Ytes data ', ' Longcol8 ' blob not NULL COMMENT ' store 496 bytes data ', ' longcol9 ' blob not NULL COMMENT ' Store bytes data ', ' LONGCOL10 ' blob not null COMMENT ' store 640 bytes data ', ' longcol11 ' blob not null COM ment ' store 768 bytes data ', ' longcol12 ' blob not null COMMENT ' store 912 bytes data ', ' longcol13 ' blob not null COMMENT ' Store 1024 bytes Data ', ' longcol14 ' blob not null COMMENT ' store 2048 bytes data ', ' longcol15 ' blob not null COMMENT ' Stor E 3082 bytes Data ', ' longcol16 ' blob not null COMMENT ' store 4096 bytes data ', ' longcol17 ' blob not null COMMENT ' store 81  Bytes data ', ' longcol18 ' blob not null COMMENT ' store 16284 bytes data ', ' longcol19 ' blob not null COMMENT ' store 20380 Bytes data ', ' longcol20 ' blob not NULL COMMENT ' store 5977 bytes data ', PRIMARY KEY (' id ') Engine=innodb DEFAULT charse

T=utf8;
#相应的数据写入存储过程: Mysp_longcol_1_ins () CREATE PROCEDURE ' Mysp_longcol_1_ins ' (in CNT int) begin SET @i = 1; While @i < CNT did insert into t_longcol_2 (Longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8, LONGCOL9,LONGCOL10, Longcol11,longcol12,LONGCOL13,LONGCOL14,LONGCOL15,LONGCOL16,LONGCOL17,LONGCOL18,LONGCOL19,LONGCOL20) Select repeat (' a ', MB), repeat
(' A ', M), repeat (' a ', M), repeat (' a ', M), repeat (' a ', M), repeat (' a ', 256), repeat (' a ', 368), repeat (' a ', 496), Repeat (' a ', repeat), (' A ', 640), repeat (' a ', 768), repeat (' a ', 912), repeat (' a ', 1024), repeat (' a ', 2048), and repeat (' a ';
3082), repeat (' a ', 4096), repeat (' a ', 8192), repeat (' a ', 16284), repeat (' a ', 20380), repeat (' a ', 5977);
Set @i = @i + 1;
End while;

End #表3:64KB data is stored in multiple char, VARCHAR, blob columns, CREATE TABLE ' t_longcol_3 ' (' id ' int () unsigned not NULL auto_increment, ' Longco L1 ' char not null default ' COMMENT ' Store bytes data ', ' longcol2 ' char (MB) NOT null default ' COMMENT ' Store Bytes data ', ' longcol3 ' char (MB) NOT null DEFAULT ' COMMENT ' Store bytes data ', ' Longcol4 ' char (m) NOT null DE FAULT ' COMMENT ' Store bytes data ', ' longcol5 ' char (+) not NULL DEFAULT ' COMMENT ' Store bytes data ', ' Longcol 6 ' varchar (256) Not NULL DEFAULT ' COMment ' store 255 bytes data ', ' longcol7 ' varchar (368) Not NULL DEFAULT ' COMMENT ' store 368 bytes data ', ' Longcol8 ' varcha R (496) NOT null default ' COMMENT ' store 496 bytes data ', ' longcol9 ' varchar () NOT null default ' COMMENT ' store 512 Bytes data ', ' longcol10 ' varchar (640) not NULL DEFAULT ' COMMENT ' store 640 bytes data ', ' longcol11 ' varchar (768) Not NUL
L default ' COMMENT ' store 768 bytes data ', ' longcol12 ' varchar (912) not NULL DEFAULT ' COMMENT ' store 912 bytes data ', ' LONGCOL13 ' varchar (1024) NOT null default ' COMMENT ' store 1024 bytes data ', ' longcol14 ' varchar (2048) NOT NULL default ' COMMENT ' store 2048 bytes data ', ' longcol15 ' varchar (3082) not NULL DEFAULT ' COMMENT ' store 3082 bytes data ', ' Longco L16 ' varchar (4096) NOT null DEFAULT ' COMMENT ' store 4096 bytes data ', ' longcol17 ' blob not null COMMENT ' store 8192 byte s data ', ' longcol18 ' blob not null COMMENT ' store 16284 bytes data ', ' longcol19 ' blob not null COMMENT ' store 20380 bytes Data ', ' LONGCOL20 ' VarchAR (5977) not NULL default ' COMMENT ' store 5977 bytes data ', PRIMARY KEY (' id ') engine=innodb DEFAULT Charset=utf8;
#相应的数据写入存储过程: Mysp_longcol_3_ins () CREATE PROCEDURE ' Mysp_longcol_1_ins ' (in CNT int) begin SET @i = 1; While @i < CNT did insert into T_longcol_3 (Longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8, Longcol9,longcol10, Longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19, LONGCOL20) Select repeat (' a ', MB), repeat (' a ', M), repeat (' a ', M), repeat (' a ', M), repeat (' a ', M), repeat (' a ', 256 ), repeat (' a ', 368), repeat (' a ', 496), repeat (' a ', ","), repeat (' a ', 640), repeat (' a ', 768), repeat (' a ', "912"), repeat (' a '),
1024), repeat (' a ', 2048), repeat (' a ', 3082), repeat (' a ', 4096), repeat (' a ', 8192), repeat (' a ', 16284), and repeat (' a ', A/(' a '), 20380),
Repeat (' a ', 5977);
Set @i = @i + 1;
End while;

End #表4: Store 64KB data in multiple varchar, blob columns, and compare several columns in t_longcol_3 with char CREATE TABLE ' t_longcol_4 ' (' id ' int () unsigned not NULL Auto_increment, ' Longcol1 ' VArchar (MB) NOT null default ' COMMENT ' Store bytes data ', ' longcol2 ' varchar (MB) NOT null default ' COMMENT ' Store Bytes data ', ' longcol3 ' varchar (MB) not NULL DEFAULT ' COMMENT ' Store bytes data ', ' longcol4 ' varchar (MB) not Null default ' COMMENT ' Store bytes data ', ' longcol5 ' varchar (MB) Not NULL default ' COMMENT ' Store bytes data ' , ' longcol6 ' varchar (256) NOT NULL default ' COMMENT ' store 255 bytes data ', ' longcol7 ' varchar (368) NOT NULL default ' COMMENT ' store 368 bytes data ', ' longcol8 ' varchar (496) not NULL DEFAULT ' COMMENT ' store 496 bytes data ', ' Longcol9 ' var Char NOT NULL default ' COMMENT ' Store bytes data ', ' longcol10 ' varchar (640) NOT null default ' COMMENT ' Store  640 bytes Data ', ' longcol11 ' varchar (768) not NULL DEFAULT ' COMMENT ' store 768 bytes data ', ' longcol12 ' varchar (912) not Null default ' COMMENT ' store 912 bytes data ', ' longcol13 ' varchar (1024) NOT null default ' COMMENT ' store 1024 bytes D Ata ', ' longcol14 ' varchaR (2048) NOT null default ' COMMENT ' store 2048 bytes data ', ' longcol15 ' varchar (3082) NOT null default ' COMMENT ' Store  3082 bytes Data ', ' longcol16 ' varchar (4096) NOT null DEFAULT ' COMMENT ' store 4096 bytes data ', ' longcol17 ' blob not NULL COMMENT ' store 8192 bytes data ', ' longcol18 ' blob not null COMMENT ' store 16284 bytes data ', ' longcol19 ' blob not null CO  Mment ' store 20380 bytes data ', ' longcol20 ' varchar (5977) not NULL DEFAULT ' COMMENT ' store 5977 bytes data ', PRIMARY KEY (' ID '))

Engine=innodb DEFAULT Charset=utf8;
#相应的数据写入存储过程: Mysp_longcol_4_ins () CREATE PROCEDURE ' Mysp_longcol_1_ins ' (in CNT int) begin SET @i = 1; While @i < CNT did insert into T_longcol_4 (Longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8, Longcol9,longcol10, Longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19, LONGCOL20) Select repeat (' a ', MB), repeat (' a ', M), repeat (' a ', M), repeat (' a ', M), repeat (' a ', M), repeat (' a ', 256 ), repeat (' a ', 368), repeat (' a ', 496), repeat (' a ', repeat), repeat (' a ', 768), repeat (' a ', 912), repeat (' a ',, 1024), repeat
2048), repeat (' a ', 3082), repeat (' a ', 4096), repeat (' a ', 8192), repeat (' a ', 16284), repeat (' a ', 20380), "Repeat" (' a ', A/(' a '), 5977);
Set @i = @i + 1;
End while;

End #表5:64KB data is stored in multiple varchar, blob columns, and t_longcol_4 compared to the number of previous column lengths to 255, but the actual storage length is 100 bytes CREATE TABLE ' t_longcol_5 ' (' ID ' int (a) unsigned NOT null auto_increment, ' longcol1 ' varchar (255) NOT null DEFAULT ' COMMENT ' Store bytes data ', ' lon Gcol2 ' varchar (255) NOT null default ' COMMENT ' store-bytes data ', ' longcol3 ' varchar (255) NOT null default ' Commen T ' Store bytes data ', ' longcol4 ' varchar (255) Not NULL DEFAULT ' COMMENT ' Store bytes data ', ' longcol5 ' varchar (2 Not null default ' COMMENT ' store-bytes data ', ' longcol6 ' varchar (256) NOT NULL default ' COMMENT ' Store 255 byt Es data ', ' longcol7 ' varchar (368) NOT null DEFAULT ' COMMENT ' store 368 bytes data ', ' longcol8 ' varchar (496) NOT NULL DEF Ault '' COMMENT ' store 496 bytes data ', ' longcol9 ' varchar () not NULL DEFAULT ' COMMENT ' Store bytes data ', ' LONGCOL10 ' VARCHAR (640) NOT null default ' COMMENT ' store 640 bytes data ', ' longcol11 ' varchar (768) NOT null default ' COMMENT ' sto  Re 768 bytes data ', ' longcol12 ' varchar (912) not NULL DEFAULT ' COMMENT ' store 912 bytes data ', ' longcol13 ' varchar (1024) Not null default ' COMMENT ' store 1024 bytes data ', ' longcol14 ' varchar (2048) NOT null default ' COMMENT ' store 2048 by TES data ', ' longcol15 ' varchar (3082) not NULL DEFAULT ' COMMENT ' store 3082 bytes data ', ' longcol16 ' varchar (4096) not NU  LL DEFAULT ' COMMENT ' store 4096 bytes data ', ' longcol17 ' blob not NULL COMMENT ' store 8192 bytes data ', ' longcol18 ' blob Not null COMMENT ' store 16284 bytes data ', ' longcol19 ' blob not null COMMENT ' store 20380 bytes data ', ' LONGCOL20 ' varcha

R (5977) not NULL default ' COMMENT ' store 5977 bytes data ', PRIMARY KEY (' id ') engine=innodb DEFAULT Charset=utf8; #相应的数据写入存储过程: Mysp_longcol_5_ins () CREATE PROCEDURE ' Mysp_longcol_1_ins ' (in CNT int) begin SET @i = 1; While @i < CNT did insert into t_longcol_5 (Longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8, Longcol9,longcol10, Longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19, LONGCOL20) Select repeat (' a ', MB), repeat (' a ', M), repeat (' a ', M), repeat (' a ', M), repeat (' a ', M), repeat (' a ', 256 ), repeat (' a ', 368), repeat (' a ', 496), repeat (' a ', ","), repeat (' a ', 640), repeat (' a ', 768), repeat (' a ', "912"), repeat (' a '),
1024), repeat (' a ', 2048), repeat (' a ', 3082), repeat (' a ', 4096), repeat (' a ', 8192), repeat (' a ', 16284), and repeat (' a ', A/(' a '), 20380),
Repeat (' a ', 5977);
Set @i = @i + 1;
End while;

End #从下面开始, refer to the 3rd recommendation of the table, each table all column length sum #分表1, row maximum length 100 + 100 + 100 + 100 + 100 + 255 + 368 + 496 + 512 + 640 + 768 + 912 + 3082 = 75 33 bytes CREATE TABLE ' t_longcol_51 ' (' id ' int () unsigned not null auto_increment, ' longcol1 ' varchar (255) NOT NULL Defaul T ' COMMENT ' Store bytes data ', ' longcol2 ' VARchar (255) NOT null default ' COMMENT ' store-bytes data ', ' longcol3 ' varchar (255) NOT null default ' COMMENT ' Store Bytes data ', ' longcol4 ' varchar (255) Not NULL DEFAULT ' COMMENT ' store-bytes data ', ' longcol5 ' varchar (255) Not N 
ull default ' COMMENT ' Store bytes data ', ' longcol6 ' varchar (256) Not NULL DEFAULT ' COMMENT ' store 255 bytes data ', ' LONGCOL7 ' varchar (368) NOT null default ' COMMENT ' store 368 bytes data ', ' longcol8 ' varchar (496) NOT null default ' C Omment ' Store 496 bytes data ', ' longcol9 ' varchar () not NULL DEFAULT ' COMMENT ' store, bytes data ', ' Longcol10 ' var CHAR (640) NOT null default ' COMMENT ' store 640 bytes data ', ' longcol11 ' varchar (768) NOT null default ' COMMENT ' Store 768 bytes Data ', ' longcol12 ' varchar (912) not NULL DEFAULT ' COMMENT ' store 912 bytes data ', ' longcol15 ' varchar (3082) NO

T NULL Default ' COMMENT ' store 3082 bytes data ', PRIMARY KEY (' id ') engine=innodb DEFAULT Charset=utf8; #分表2, row Maximum length 1024 + 2048 + 4096 = 7168byte CREATE TABLE ' t_longcol_52 ' (' id ' int () unsigned not null auto_increment, ' longcol13 ' varchar (1024) NOT NULL DEFAULT ' COMMENT ' store 1024 bytes data ', ' longcol14 ' varchar (2048) not NULL DEFAULT ' COMMENT ' store 2048 bytes data ', ' LONGC Ol16 ' varchar (4096) not NULL default ' COMMENT ' store 4096 bytes data ', PRIMARY KEY (' id ') Engine=innodb DEFAULT charse

T=utf8; #分表3, row Maximum length 8192 bytes CREATE TABLE ' t_longcol_53 ' (' id ' int () unsigned not null auto_increment, ' LONGCOL17 ' blob not NULL

COMMENT ' store 8192 bytes data ', PRIMARY KEY (' id ') engine=innodb DEFAULT Charset=utf8; #分表4, row maximum length 16284 + 20380 = 36664 bytes CREATE TABLE ' t_longcol_54 ' (' id ' int (a) unsigned not NULL auto_increment, ' longcol1  8 ' blob not null COMMENT ' store 16284 bytes data ', ' longcol19 ' blob not null COMMENT ' store 20380 bytes data ', PRIMARY KEY (' ID '))

Engine=innodb DEFAULT Charset=utf8; #分表5, row Maximum length 5977 + 4 = 5981 bytes CREATE TABLE ' t_longcol_55 ' (' id ' int (a) unsigned not NULL auto_increment, ' LONGCOL20 ' VARchar (5977) not NULL default ' COMMENT ' store 5977 bytes data ', PRIMARY KEY (' id ') engine=innodb DEFAULT Charset=utf8;
#相应的数据写入存储过程: Mysp_longcol_51_ins () CREATE PROCEDURE ' Mysp_longcol_51_ins ' (in CNT int) begin SET @i = 1; While @i < CNT did insert into t_longcol_51 (Longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8 , LONGCOL9,LONGCOL10, longcol11,longcol12,longcol15) Select repeat (' a ', MB), repeat (' a ', M), repeat (' a ', m), repeat
(' A ', M), repeat (' a ', M), repeat (' a ', 256), repeat (' a ', 368), repeat (' a ', 496), repeat (' a ', ","), repeat (' a ', "a ',", ","),

Repeat (' a ', 768), repeat (' a ', 912), repeat (' a ', 3082);

Insert into t_longcol_52 (LONGCOL13,LONGCOL14,LONGCOL16) Select repeat (' a ', 1024), repeat (' a ', 2048), repeat (' a ', 4096);

Insert into t_longcol_53 (LONGCOL17) Select repeat (' a ', 8192);

Insert into t_longcol_54 (longcol18,longcol19) Select repeat (' a ', 16284), repeat (' a ', 20380);

Insert into t_longcol_55 (LONGCOL20) Select repeat (' a ', 5977);
Set @i = @i + 1;
End while;

 End

Each of these test tables is written to 5000 rows, then compare the size of the table space file, and the size of the rebuild table space, to observe the fragment rate. Detailed comparison See below:

In the last form of the table, the total table space file size for the 5 child tables is 40960 + 40960 + 98304 + 286720 + 40960 = 507904 bytes.
As you can see, the total size of this approach is the smallest difference from the original table size, and several other storage methods are larger than this.