玉面飞龙的BLOG

Mysql的Innodb存储类型的表，结构非常类似Oracle的Index Orginazed Table，都是基于主键的cluster table.

Oracle中，当一行太长的时候，可以根据PCTTHRESHOLD和INCLUDING等将一些列存储在另外的overflow segment里面。通常将不常用的列或者太长的字符串类型的列存储在overflow中.

当Innodb类型的表列太长的时候，Mysql会如何处理哪？

Innodb没有 overflow segment的概念。从Innodb的限制可以看到。

#A table cannot contain more than 1000 columns.

#The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.

#The maximum row length, except for VARCHAR, BLOB and TEXT columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including also BLOB and TEXT columns, must be less than 4GB. InnoDB stores the first 768 bytes of a VARCHAR, BLOB, or TEXT column in the row, and the rest into separate pages.

#Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARCHAR columns with a combined size larger than 65535:

默认情况下，innodb page size是16K。则除了VARCHAR, BLOB 和TEXT类型的字段，字段长度不能超过8000个字节左右。当 VARCHAR, BLOB 和TEXT类型的字段超出长度的时候，则将存储在单独的段里面。这非常类似Oracle的Lob Segment。

同理，在mysql Innodb中，尽量不要使用太长的varchar类型字段，或者不要频繁的去查询这些字段。否则SQL会有额外的IO。

当创建Innodb表,列定义超过限制的时候，会提示”ERROR 1005 (HY000)”错误。

mysql> CREATE TABLE large_table (a VARCHAR(8000), b VARCHAR(10000),
-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
-> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE large_table (a VARCHAR(8000), b VARCHAR(10000),
-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
-> f VARCHAR(10000) ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into large_table values( RPAD(’hi’,8000,’?') , RPAD(’hi’,9000,’?'),
-> RPAD(’hi’,9000,’?'), RPAD(’hi’,9000,’?'), RPAD(’hi’,9000,’?'),
-> RPAD(’hi’,9000,’?') );
Query OK, 1 row affected (0.01 sec)

如上varchar类型的列值,将存储在单独的”varchar segment”中。

mysql> CREATE TABLE long_column_table (id int primary key,a CHAR(250), b CHAR(250),
c CHAR(250), d CHAR(250), e CHAR(250),
f CHAR(250), g CHAR(250), h CHAR(250),
f1 CHAR(250), d1 CHAR(250), e1 CHAR(250),
f2 CHAR(250), d2 CHAR(250), e2 CHAR(250),
f3 CHAR(250), d3 CHAR(250), e3 CHAR(250),
f4 CHAR(250), d4 CHAR(250), e4 CHAR(250),
f5 CHAR(250), d5 CHAR(250), e5 CHAR(250),
f6 CHAR(250), d6 CHAR(250), e6 CHAR(250),
f7 CHAR(250), d7 CHAR(250), e7 CHAR(250),
f11 CHAR(250), d11 CHAR(250), e11 CHAR(250),
f22 CHAR(250), d22 CHAR(250), e22 CHAR(250),
f33 CHAR(250), d33 CHAR(250), e33 CHAR(250),
f44 CHAR(250), d44 CHAR(250), e44 CHAR(250),
f55 CHAR(250), d55 CHAR(250), e55 CHAR(250),
f66 CHAR(250), d66 CHAR(250), e66 CHAR(250),
f77 CHAR(250), d77 CHAR(250), e77 CHAR(250),
f111 CHAR(250), d111 CHAR(250), e111 CHAR(250),
f222 CHAR(250), d222 CHAR(250), e222 CHAR(250),
f333 CHAR(250), d333 CHAR(250), e333 CHAR(250),
f444 CHAR(250), d444 CHAR(250), e444 CHAR(250),
f555 CHAR(250), d555 CHAR(250), e555 CHAR(250),
f666 CHAR(250), d666 CHAR(250), e666 CHAR(250),
f777 CHAR(250), d777 CHAR(250), e777 CHAR(250),
f1111 CHAR(250), d1111 CHAR(250), e1111 CHAR(250),
f2222 CHAR(250), d2222 CHAR(250), e2222 CHAR(250),
f3333 CHAR(250), d3333 CHAR(250), e3333 CHAR(250),
f4444 CHAR(250), d4444 CHAR(250), e4444 CHAR(250),
f5555 CHAR(250), d5555 CHAR(250), e5555 CHAR(250),
f6666 CHAR(250), d6666 CHAR(250), e6666 CHAR(250),
f7777 CHAR(250), d7777 CHAR(250), e7777 CHAR(250)
) ENGINE=InnoDB;

ERROR 1005 (HY000): Can’t create table ‘./test/long_column_table.frm’ (errno: 139)

列定义超过8000个字节，提示错误。错误信息仍然有待改进。

mysql> CREATE TABLE long_column_table (id int primary key,a CHAR(250), b CHAR(250),
-> c CHAR(250), d CHAR(250), e CHAR(250),
-> f CHAR(250), g CHAR(250), h CHAR(250),
-> f1 CHAR(250), d1 CHAR(250), e1 CHAR(250),
-> f2 CHAR(250), d2 CHAR(250), e2 CHAR(250),
-> f3 CHAR(250), d3 CHAR(250), e3 CHAR(250),
-> f4 CHAR(250), d4 CHAR(250), e4 CHAR(250),
-> f5 CHAR(250), d5 CHAR(250), e5 CHAR(250),
-> f6 CHAR(250), d6 CHAR(250), e6 CHAR(250),
-> f7 CHAR(250), d7 CHAR(250), e7 CHAR(250)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)