Mysql的row_format

问题描述：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'variabaes like "%per_table%"'

 

解决方案：

前段时间在项目压测的时候出现了mysql的大字段问题，导致数据层无法存储。

 

【问题描述】

最早mysql端报这个错

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

 

今天线上版本的错误：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

 

【导致问题的原因】

总结了下原因是因为mysql-innodb是按照page存储数据的，每个page max size是16k，然后每个page两行数据，所以每行最大8k数据。如果你的字段是blob之类的话，会存储在page之外的溢出区里。

但是innodb默认的approach（羚羊）存储格式会把每个blob字段的前864个字节存储在page里，所以你的blob超过一定数量的话，单行大小就会超过8k，所以就报错了

【解决思路】

解决方式是使用innodb的Barracuda（梭鱼） 存储格式

这种格式对blob字段的处理方式是在page里头只存储一个20byte大小的指针，其它全存在溢出区，所以你轻易超不了8k

 

【详细步骤】

1. 打开mysql的配置my.ini。在innodb配置出添加：innodb_file_per_table=1

大概的意思就是打开mysql每张表都是独立存储空间的开关。

 

2. 然后命令检查下上述开关是否打开。

show variables like '%per_table%';

 

如上图开关就打开了。

 

3. 设置mysql全局变量：innodb_file_format = Barracuda（梭鱼）

命令：set GLOBAL innodb_file_format = 'Barracuda';

然后检查下是否设置好了：

命令：show GLOBAL VARIABLES LIKE '%file_format%';

 

如上就是好了

 

4. 设置对应表的属性：ROW_FORMAT=COMPRESSED

然后检查下标的属性是否是你设置的：COMPRESSED

 

5. 如上步骤以后，就OK了

 

今天又让tx折腾一天，MD。长记性，再忙也要记录，要给团队分享，希望我们团队，我的兄弟们，我们公司积累越来越多的财富。

【补充】

1. 最后一定要检查下相关表的属性，一定要是COMPRESSED,如下图

 

这点一定要注意，这是导致线上事故的一个重要原因

 

 

 

mysql的format:

在mysql中， 若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的row_format是fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。

  www.2cto.com  

若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，即该表的row_format是dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。

所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。

 

row_format还有其他一些值：

DEFAULT

FIXED

DYNAMIC

COMPRESSED

REDUNDANT

COMPACT

  www.2cto.com  

修改行格式

ALTER TABLE table_name ROW_FORMAT = DEFAULT

 

修改过程导致：

fixed--->dynamic: 这会导致CHAR变成VARCHAR

dynamic--->fixed: 这会导致VARCHAR变成CHAR