mysql对text字段update致磁盘增长

mysql

描述

公司有一张表占用的磁盘空间很大，占用磁盘的主要是该表的一个text类型字段。于是我们打算通过洗数据的方式，先将该表的text值更新成一个长度很短的值，预期更新完后该表的磁盘占用不会变，但是表的数据页会有大量的碎片空间，然后再通过例如命令OPTIMIZE TABLE来回收释放未使用的磁盘空间，最终实现减小该表的磁盘空间占用。

但实际操作时，当我们将该表的text值的字段批量更新成更短的值时，通过监控发现该表的磁盘空间迅速增长，与我们预期不符。此时才意识到遇到了mysql的行溢出的场景。

复现

创建两个带text类型字段的表，来复现行溢出场景，如下语句，并默认使用Dynamic作为表的格式：

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `long_text` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `short_text` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1
2
3
4
5
6
7
mysql> show table status like "%text";
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| long_text  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2020-06-01 20:09:50 | NULL        | NULL       | utf8mb4_general_ci | NULL     |                |         |
| short_text | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2020-06-01 20:09:50 | NULL        | NULL       | utf8mb4_general_ci | NULL     |                |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

分别向两个表各新增10000条记录，其中short_text表中每个text字段的值长设置成8100个字节，而long_text表中每个text字段的值长设置成8102个字节。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
delimiter  //
CREATE PROCEDURE init_text() 
BEGIN 
	DECLARE content_id INT;
	SET content_id = 0;
	START TRANSACTION; 
	WHILE content_id < 10000 DO
		insert into short_text(content) values (SPACE(8100));
		insert into long_text(content) values (SPACE(8102));
		SET content_id = content_id + 1;
	END WHILE;
	COMMIT; 
END //
delimiter ;

call init_text();

数据新增完毕后，查看两个表的磁盘占用大小。如下图，两个表都是新增10000条记录，每条记录的长度仅相差2个字节，但实际上两个表的磁盘占用却相差了将近一倍！

image

然后复现我们洗数据时的场景，将两个表的所有记录均更新成一个1000字节长度的值，再看两个表的磁盘占用大小，如下图，long_text表的磁盘空间增加了40MB，而short_text表的磁盘空间与预期相同磁盘空间大小没有变化。

1
2
update short_text set content = SPACE(1000);
update long_text set content = SPACE(1000);

image

原理

《高性能MySQL》

对于很长的变长列（例如，BLOB、TEXT，以及长字符列），InnoDB存储一个768字节的前缀在行内。如果列的值比前缀长，InnoDB会在行外分配扩展存储空间来存剩下的部分。它会分配一个完整的16KB的页，像其他所有的InnoDB页面一样，每个列都有自己的页面（不同的列不会共享扩展存储空间）。InnoDB一次只为一个列分配一个页的扩展存储空间，直到使用了超过32个页以后，就会一次性分配64个页面。

注意，我们说过InnoDB可能会分配扩展存储空间。如果总的行长（包括大字段的完整长度）比InnoDB的最大行长限制要短（比8KB小一些），InnoDB将不会分配扩展存储空间，即使大字段的长度超过了前缀长度。

最后，当InnoDB更新存储在扩展存储空间中的大字段时，将不会在原来的位置更新，而是会在扩展存储空间中写一个新值到一个新的位置，并且不会删除旧的值。

《MySQL技术内幕:InnoDB存储引擎(第2版)》

Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在Off Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。

image

image

mysql的所有记录存放在数据页上，默认每一个页大小为16KB，对于mysql而言，每个页至少需要存放2条记录，否则mysql的B+Tree数据结构就会退化成了链表。因此当一条行记录的占用空间超过页的一半也就是8KB时，会将例如TEXT、BLOB类型的字段存放到额外的数据页上，如上图所示，这就是mysql的行溢出。

行溢出占用的溢出页非常浪费磁盘空间，每个溢出页只被一条记录的一个字段占用，且同一个行记录的不同字段和不同行记录之间的字段均不共享溢出页。例如两条记录的TEXT字段值各长15KB，那么两条记录的TEXT值各独占一个溢出页即16KB x 2 = 32KB空间；而当TEXT值各长17KB时，则两条记录的TEXT值各独占2个溢出页即16KB x 2 x 2 = 64KB的空间，未使用的30KB空间会完全被浪费。

所以上述复现操作中，short_text表和long_text表都是新增10000条记录，两表的记录长度仅相差2个字节，而实际上两个表的磁盘占用却相差了76MB。这是因为long_text表发生了行溢出，8102字节长度的值独占了16KB的溢出页，每个溢出页浪费了约8KB的磁盘空间，即8KB * 10000 = 78MB，与实际差额的76MB接近。

另外，当用户更新溢出页的值时，mysql不会去修改原本溢出页的值，而是直接新建溢出页存放新值，旧的溢出页也不会回收，从而造成更大的磁盘空间浪费。因此对long_text表做更新操作时，原先的溢出页均未更新，而是在新的数据页上存放更新后的新数据，导致long_text表的磁盘空间占用增长。

测试

1
2
3
4
5
CREATE TABLE `long_text` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1
insert into long_text(id,content) values (1,repeat('a',8102));

直接查看mysql的表数据二进制文件long_text.ibd，如下图，新增的ID为1的行记录发生了行溢出，20字节的溢出页指针指向了序号为0x00000004的数据页，而该数据页中的确存放了长度8102的值。

image

image

对溢出行进行更新操作，再查看二进制文件，发现ID为1的行记录的溢出页指针发生了变化，溢出页由0x00000004变成了0x00000005。查看原先的0x00000004溢出页，发现该页数据未更新，空间未被回收，而新增的0x00000005溢出页则存放了更新后的新值。

1
update long_text set content = repeat('b',8102);

image

image

image

再对溢出行进行更新操作，这次将值更新为一个不会发生行溢出的短值，见如下二进制文件，原先的20字节溢出页指针被更新成了该字段的新值，而序号为0x00000004和0x00000005的溢出页均未发生变化，即该两个溢出页的空间被完全浪费。

1
update long_text set content = repeat('b',10);

image

建议优化

为了避免大字段的行溢出导致磁盘空间的浪费，可以通过如下方式进行大字段的优化：

  1. 如果一条行记录有多个大字段，尽量序列化后合并成一个大字段，避免同时使用多个大字段；
  2. 压缩长字段值，保证一条行记录小于8KB；
>