第02期：MySQL 数据类型的艺术 – 大对象字段

于由杨涛涛发布

我以前分享过一篇《MySQL 大对象一例》，今天就来详细说下大对象的优缺点以及使用场景。

我们把 MySQL 的大对象类型分 TEXT / BLOB 和 JSON 两部分来说明。

一、TEXT / BLOB 类型

TEXT 和 BLOB 的区别非常简单。TEXT 存储以明文存储，有对应的字符集和校验规则；BLOB 则以二进制存储，没有字符集和排序规则，所有的对比都是以二进制来进行。

示例 1

创建一张表 c1 字段 f1,f2 分别为 tinytext 和 tinyblob。

  1. mysql> create table c1 (f1 tinytext, f2 tinyblob);
  2. Query OK, 0 rows affected (0.03 sec)

插入示例数据。

  1. insert into c1 values ('a','a'),('b','b'),('B','B'),('d','d'),('F','F'),('你','你'),('我','我'),('是吧','是吧');

根据字段 f1 排序。

  1. mysql> select * from c1 order by f1;
  2. +--------+--------+
  3. | f1     | f2     |
  4. +--------+--------+
  5. | a      | a      |
  6. | b      | b      |
  7. | B      | B      |
  8. | d      | d      |
  9. | F      | F      |
  10. | 你     | 你     |
  11. | 我     | 我     |
  12. | 是吧   | 是吧   |
  13. +--------+--------+
  14. 8 rows in set (0.01 sec)

根据字段 f2 排序。

  1. mysql> select * from c1 order by f2;
  2. +--------+--------+
  3. | f1     | f2     |
  4. +--------+--------+
  5. | B      | B      |
  6. | F      | F      |
  7. | a      | a      |
  8. | b      | b      |
  9. | d      | d      |
  10. | 你     | 你     |
  11. | 我     | 我     |
  12. | 是吧   | 是吧   |
  13. +--------+--------+
  14. 8 rows in set (0.00 sec)

f1,f2 字段各自排序的结果都不一致。f1 是按照不区分大小写的校验规则，f2 直接二进制检验。

MySQL 的大对象类型，我从以下几类详细说明：

1.1 磁盘空间占用

1.2 表的存储格式

  • 1.2.1 redundant/compact

    对 redundant 格式来说，保存大对象的前 768 字节在 InnoDB 数据页，多出来的放在溢出页。如果有多个 TEXT/BLOB 字段，那数据页将会变得臃肿不堪，性能影响很大。数据页里几乎全是无用的数据，导致额外的资源消耗。同时如果是主从架构，也会把数据全部同步到从机，对网络也是额外的消耗。所以这种场景下，一般都只是保存大对象的路径到数据库，真实的数据则放在磁盘上。

  • 1.2.2 dynamic/compressed

    对 dynamic 格式来说，如果大对象字段存储数据大小小于 40 字节，那全部放在数据页，剩余的场景，数据页只保留一个 20 字节的指针指向溢出页。 这种场景下，如果每个大对象字段保存的数据小于 40 个字节，也就和 varchar(40)，效果一样。所以用不用大对象不能一概而论。

1.3 表引擎相关

NDB 引擎不推荐使用大对象。涉及的细节比较多，比如含有大对象的关联字段不支持引擎关联数据下推；含有大对象的表需要额外的加锁等等。
1.4 索引相关
在大对象字段上建立索引必须是前缀，比如字段 f1 为 text，给前 10 个字符建立索引 idx_f1(f1(10))。
  1. mysql> alter table t2 add key idx_f100(f100);
  2. ERROR 1170 (42000): BLOB/TEXT column 'f100' used in key specification without a key length
1.5 分区表相关
分区表分区字段不支持大对象。
  1. mysql> create table p1(f1 text) partition by list columns(f1) (partition p0 values in ('1','2'));
  2. ERROR 1502 (HY000): A BLOB field is not allowed in partition function
1.6 参数相关
mysql_allowed_packet，这个参数代表 MySQL 服务端和客户端传输的单次数据包上限，如果有 text/blob 字段，此参数设置为最大值 1GB。当然了，必须同时设置客户端和服务端。

1.7 如何插入/获取

示例 2

插入一个 /var/lib/mysql-files 目录下的所有文件内容。

  1. root@ytt-pc:/var/lib/mysql-files# ll
  2. 总用量 32M
  3. 1324681  14M -rw-r--r-- 1 root  root   14M 12月 30 17:23 test3
  4. 1311598 6.6M -rw-r--r-- 1 root  root  6.6M 12月 30 17:23 test
  5. 1316840 6.6M -rw-r--r-- 1 root  root  6.6M 12月 30 17:23 test2
  6. 1316693 5.5M -rw-r--r-- 1 root  root  5.4M 12月 30 17:26 random_test
  7. 1315066 4.0K -rw-r--r-- 1 root  root  1.9K 12月 30 14:56 sample_y1.txt.nl
  8. 1311256 4.0K -rw-r----- 1 mysql mysql 1.7K 12月 30 16:23 y1.txt
  9. 1316129 4.0K -rw-r--r-- 1 root  root  1.5K 12月 30 15:28 sample_y1.txt.nl2
  10. 1312042 4.0K -rw-r--r-- 1 root  root  1.5K 12月 30 15:07 sample_y1.txt.awk
  11. 1316691 4.0K -rw-rw-rw- 1 mysql mysql 1.4K 12月 30 16:25 y1_older.txt
  12. 1316168 4.0K -rw-r----- 1 mysql mysql  874 12月 30 14:41 sample_y1.txt
  13. 1315893 4.0K -rw-r----- 1 root  root   874 12月 30 14:45 sample_y1.txt.bak
  14. 1311076 4.0K -rw-r----- 1 mysql mysql  662 12月 30 13:56 ascii.txt
  15. 1312152 4.0K -rw-r--r-- 1 root  root   648 12月 30 14:37 sample.txt
  16. 1325161 4.0K -rw-r--r-- 1 root  root   403 12月 31 10:59 y1111.txt.10
  17. 1312256 4.0K -rw-r----- 1 mysql mysql  164 12月 31 09:49 y111.txt
  18. 1311248 4.0K -rw-r----- 1 mysql mysql  159 12月 31 09:42 y11.txt
  19. 1327711 4.0K -rw-r--r-- 1 root  root   126 12月  5 09:56 performance_schema_enable.sql
  20. 1326639 4.0K -rw-r--r-- 1 root  root    22 1月  10 11:32 系统名称
存放文件内容的表结构。
  1. mysql-(ytt/3305)->create table file_list_upload_dir(file_name varchar(100) not null primary key,content longtext, updated datetime);
  2. Query OK, 0 rows affected (0.03 sec)

插入文件内容。

  1. root@ytt-pc:/var/lib/mysql-files# for i in $(ls); \
  2. do mysql -uytt -pytt -P3305 -h127.0.0.1  \
  3. -e "use ytt;insert into file_list_upload_dir  \
  4. values ('$i',load_file('$(pwd)/$i'),now())";done;

二、JSON 类型

JSON 类型相比大家都非常熟悉了，轻量级的文本交互格式，不依赖于任何语句。

制定信息参考：

https://tools.ietf.org/html/rfc7159

https://tools.ietf.org/html/rfc7396

JSON 类型在 MySQL 内部以特殊的二进制方式存放，类似于 PostgreSQL 的 JSONB 类型。最大占用空间和 longtext 或者 longblob 一样。text 其实也能存储 JSON 对象，但是没有 JSON 类型的格式校验以及内部提供的众多函数。比如以下例子：

示例 3

变量 @a 和 @b 分别为标准 JSON 格式和非 JSON 格式。创建 json1 表。

  1. mysql-(ytt/3305)->set @a='{"a":1,"b":2,"c":3,"d":4}';
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql-(ytt/3305)->set @b="{'a':1,'b':2,'c':3,'d':4}";
  5. Query OK, 0 rows affected (0.00 sec)

  7. mysql-(ytt/3305)->create table json1 (str1 json,str2 longtext);
  8. Query OK, 0 rows affected (0.02 sec)

给 str1 插入 @a 成功，@b 失败；str2 任何字符都能插入。

  1. mysql-(ytt/3305)->insert into json1 values (@a,@a);
  2. Query OK, 1 row affected (0.00 sec)

  4. mysql-(ytt/3305)->insert into json1 values (@b,@b);
  5. ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'json1.str1'.

  7. mysql-(ytt/3305)->insert into json1 values (@a,@b);
  8. Query OK, 1 row affected (0.01 sec)

又比如说检索部分。对 JSON 的检索比对 text 的检索容易太多。还是拿刚才的例子，需要拿出 a 对应的值 1，对 @b 来说，可能比较麻烦，但是换成 @a 就容易多了。json_extract 函数即可。

  1. mysql-(ytt/3305)->select json_extract(@a,'$.a') as 'a';
  2. +------+
  3. | a    |
  4. +------+
  5. | 1    |
  6. +------+
  7. 1 row in set (0.00 sec)

但是有一点比较幸运的时，针对 @b 这种格式，可以把单个字段拆分为多个，这样就可以利用 SQL 语句简单的检索出结果来了。关于拆或者不拆的优劣暂时不在本篇讨论范围内，将会在表设计理念篇来详细介绍。

好了，本篇关于 MySQL 的大对象类型就介绍就到此为止，希望对大家有所帮助。

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

分类： MySQL 调优

相关文章

MySQL 调优

第45期：一条 SQL 语句优化的基本思路

SQL 语句优化是一个既熟悉又陌生的话题。面对千奇百怪的 SQL 语句 阅读更多…

MySQL 调优

第44期：无主键分区表该不该使用

本来想着分区表在上一篇后就不续写了，最近又有同学咨询我分区表的新问题： 阅读更多…

MySQL 调优

第43期：多表关联场景下如何用好分区表

如何在多表关联场景下合理利用分区表来提升查询性能？基于前几篇关于分区表 阅读更多…