InnoDB行格式对text/blob大变长字段的影响

admin Mysql 暂无评论 1,189 浏览

最近在排查现网Text与Blob类型，发现有不少，在《高性能MySQL(第3版)》看到对这两种变长数据类型的处理会涉及到在磁盘上创建临时表，性能开销比较大。于是把影响blob型数据存储方式了解了一下：row_format。

1. InnoDB的Antelop与Barracuda文件格式

Innodb存储引擎保存记录，是以行的形式存放的（与之对应的是像Google BigTable这种列数据库）。在InnoDB 1.0.x版本之前，InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据，这也是目前使用最多的一种格式。Redundant 格式是为兼容之前版本而保留的。

MySQL 5.1 中的 innodb_plugin 引入了新的文件格式：Barracuda（将以前的行格式 compact 和 redundant 合称为Antelope），该文件格式拥有新的两种行格式：compressed和dynamic。

在 MySQL 5.6 版本中，默认还是 Compact 行格式，也是目前使用最多的一种 ROW FORMAT。用户可以通过命令SHOW TABLE STATUS LIKE'table_name' 来查看当前表使用的行格式，其中 row_format 列表示当前所使用的行记录结构类型。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
mysql> show variables like "innodb_file_format";
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set

mysql> show table status like "tablename%"\G
*************************** 1. row ***************************
           Name: t_rf_compact
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
 Avg_row_length: 36864
    Data_length: 147456
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7
    Create_time: 2016-05-14 20:52:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

在 msyql 5.7.9 及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC，也可以在 create table 的时候指定ROW_FORMAT=DYNAMIC。

注意，如果要修改现有表的行模式为compressed或dynamic，必须先将文件格式设置成Barracuda：set global innodb_file_format=Barracuda;，再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效，否则修改无效却无提示：

1
2
3
4
5
6
7
8
9
10
11
12
13
 
mysql> ALTER TABLE tablename ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 2

修改失败
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                                  |
+---------+------+-----------------------------------------------------------------------+
2 rows in set

2. 对TEXT/BLOB这类大字段类型的影响

2.1 compact

在 Antelope 两种行格式下，如果blob列值长度 <= 768 bytes，就不会发生行溢出(page overflow)，内容都在数据页(B-tree Node)；如果列值长度 > 768字节，那么前768字节依然在数据页，而剩余的则放在溢出页(off-page)，如下图：

上面所讲的讲的blob或变长大字段类型包括blob,text,varchar，其中varchar列值长度大于某数N时也会存溢出页，在latin1字符集下N值可以这样计算：innodb的块大小默认为16kb，由于innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，减去其它列值所占字节数，约等于N。

我们知道对于InnoDB来说，内存是极为珍贵的，如果把768字节长度的blob都放在数据页，虽然可以节省部分IO，但相对来说能缓存行数就变少，也就是能缓存的索引值变少了，降低了索引效率。

2.2 dynamic

Barracuda 的两种行格式对blob采用完全行溢出，即聚集索引记录（数据页）只保留20字节的指针，指向真实存放它的溢出段地址：

dynamic行格式，列存储是否放到off-page页，主要取决于行大小，它会把行中最长的那一列放到off-page，直到数据页能存放下两行。TEXT/BLOB列 <=40 bytes 时总是存放于数据页。这种方式可以避免compact那样把太多的大列值放到 B-tree Node，因为dynamic格式认为，只要大列值有部分数据放在off-page，那把整个值放入都放入off-page更有效。

compressed 物理结构上与dynamic类似，但是对表的数据行使用zlib算法进行了压缩存储。在long blob列类型比较多的情况下用，可以降低off-page的使用，减少存储空间（一般40%左右），但要求更高的CPU，buffer pool里面可能会同时存储数据的压缩版和非压缩版，所以也多占用部分内存。这里 MySQL 5.6 Manual innodb-compression-internals 讲的十分清楚。

另外，由于ROW_FORMAT=DYNAMIC 和 ROW_FORMAT=COMPRESSED 是从 ROW_FORMAT=COMPACT 变化来的，所以他们处理 CHAR类型存储的方式和 COMPACT 一样。

3. 对blob型字段存取优化

如果一个查询涉及BLOB值，又需要使用临时表——不管它多小——它都会立即在磁盘上创建临时表。这样效率很低，尤其是对小而快的查询，临时表可能是查询中最大的开销。

比如：创建一个带Text字段的compact表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
mysql> CREATE TABLE `t_rf_compact` (
  `f_id` int(11) NOT NULL AUTO_INCREMENT,
  `f_char` char(30) DEFAULT NULL,
  `f_varchar` varchar(30) NOT NULL DEFAULT '',
  `f_text` text NOT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

mysql> insert into t_rf_compact(f_char,f_varchar,f_text) values('aa','中中',repeat('b',700));
mysql> insert into t_rf_compact(f_char,f_varchar,f_text) values('aa','文',repeat('c',60000));
第二条数据会行溢出，前768字节放在Clustered Index数据页，剩余的放扩展存储空间

mysql> explain select t1.f_id from t_rf_compact t1,t_rf_compact t2 where t1.f_id=t2.f_id order by t1.f_id limit 1; 
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | t1    | index  | PRIMARY       | PRIMARY | 4       | NULL              |    6 | Using index |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | d_ec_crm2.t1.f_id |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
2 rows in set (0.00 sec)


mysql> show status like "%tmp%tables";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 7     |
| Created_tmp_tables      | 36    |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> select t1.f_id from t_rf_compact t1,t_rf_compact t2 where t1.f_id=t2.f_id order by t1.f_id limit 1;
+------+
| f_id |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show status like "%tmp%tables";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 7     |
| Created_tmp_tables      | 36    |
+-------------------------+-------+
2 rows in set (0.00 sec)

没有临时表产生，所以磁盘临时表无变化。让它产生临时表：（但不涉及text列）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
mysql> explain select t1.f_id from t_rf_compact t1,t_rf_compact t2 where t1.f_id=t2.f_id order by t2.f_id;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | t1    | index  | PRIMARY       | PRIMARY | 4       | NULL              |    6 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | d_ec_crm2.t1.f_id |    1 | Using index                                  |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> select t1.f_id from t_rf_compact t1,t_rf_compact t2 where t1.f_id=t2.f_id order by t2.f_id;

mysql> show status like "%tmp%tables";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 7     |
| Created_tmp_tables      | 37    |
+-------------------------+-------+
2 rows in set (0.00 sec)

虽然有Using temporary，但内存临时表还是够用，磁盘临时表还是无变化。返回TEXT列（也会使用临时表排序）：

1
2
3
4
5
6
7
8
9
 
mysql> select t1.f_text from t_rf_compact t1,t_rf_compact t2 where t1.f_id=t2.f_id order by t2.f_id;
mysql> show status like "%tmp%tables";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 8     |
| Created_tmp_tables      | 38    |
+-------------------------+-------+
2 rows in set (0.00 sec)

Created_tmp_disk_tables磁盘临时表有增加，与上面结论相符：只有有TEXT/BLOB列参与，如果用到临时表，不管它多小，都会创建在磁盘上，从而带来性能消耗。

注：磁盘临时表存储引擎一定是 MyISAM，与select @@default_tmp_storage_engine;（5.6.3开始）看到的InnoDB无关，它是控制CREATE TEMPORARY TABLE时的默认引擎。在 5.7.5 开始internal_tmp_disk_storage_engine选项可以定义磁盘临时表的引擎类型。关于临时表与内存表可以参考 [MySQL FAQ]系列 — 什么情况下会用到临时表 -老叶 。

有两种办法来减轻这种不利的情况：通过 SUBSTRING() 函数把值转换为 VARCHAR，或者让磁盘临时表更快一些。

让磁盘临时表运行更快的方式是，把它们放在基于内存的文件系统tmpfs，tmpfs文件系统为了降低开销不会刷新内存数据到磁盘，读写速度也很快，而临时表也不需要持久存放。mysql的 tmpdir 参数控制临时文件存放位置，建议如果使用的话要监控空间使用率。另外如果BLOB列非常大或多，可以考虑调大InnoDB日志缓存大小innodb_log_buffer_size。

如果使用BLOB这类变长大字段类型，需要以下后果考虑：

  • 大字段在InnoDB里可能浪费大量空间。例如，若存储字段值只是比行的要求多了一个字节，也会使用整个页面来存储剩下的字节，浪费了页面的大部分空间。同样的，如果有一个值只是稍微超过了32个页的大小，实际上就需要使用96个页面。
  • 扩展存储禁用了自适应哈希，因为需要完整的比较列的整个长度，才能发现是不是正确的数据（哈希帮助InnoDB非常快速的找到“猜测的位置”，但是必须检查“猜测的位置”是不是正确）。因为自适应哈希是完全的内存结构，并且直接指向Buffer Pool中访问“最”频繁的页面，但对于扩展存储空间却无法使用Adaptive Hash。
  • 太长的值可能使得在查询中作为WHERE条件不能使用索引，因而执行很慢。在应用WHERE条件之前，MySQL需要把所有的列读出来，所以可能导致MySQL要求InnoDB读取很多扩展存储，然后检查WHERE条件，丢弃所有不需要的数据。查询不需要的列绝对不是好主意，在这种特殊的场景下尤其需要避免这样做。如果发现查询正遇到这个限制带来的问题，可以尝试通过覆盖索引来解决部分问题。
  • 如果一张表里有很多大字段，最好是把它们组合起来单独存到一个列里面，比如说用XML文档格式存储。这让所有的大字段共享一个扩展存储空间，这比每个字段用自己的页要好。
  • 有时候可以把大字段用COMPRESS()压缩后再存为BLOB，或者在发送到MySQL前在应用程序中进行压缩，这可以获得显著的空间优势和性能收益。
    —— 《高性能MySQL(第3版)》 P368

对上面的解读就是：

  • 如果预期长度范围varchar就满足，就避免使用TEXT
  • 对于字段非常大的列可以在应用程序里压缩后再存到mysql，如果列值很长请考虑用单独的表存放
  • 一张表有多个类blob字段，把它们组合起来如<TEXT><f_big_col1>long..</f_big_col1> <f_content>long..</f_content></TEXT>，再压缩存储。但要考虑是否使用全文索引，是否需要前缀索引。

参考

相关博文

InnoDB行格式对text/blob大变长字段的影响
赏
标签: