请选择 进入手机版 | 继续访问电脑版
  • 关注官方微信 微信公众号 添加方式:
    1:搜索微信号(gogolinux
    2:扫描左侧二维码
  • 登录 注册
  • 一起学LINUX - GOGOLINUX

    查看: 625|回复: 0

    MySQL数据类型的艺术中大对象字段-爱可生

    [复制链接]

    7

    主题

    7

    帖子

    8

    积分

    新手上路

    Rank: 1

    积分
    8
    发表于 2020-4-27 17:55:25 | 显示全部楼层 |阅读模式


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

    我们把 MySQL 的大对象类型分 TEXT/BLOB 和 JSON 两部分来说明。
    一、TEXT/BLOB 类型
    TEXT 和 BLOB 的区别非常简单。TEXT 存储以明文存储,有对应的字符集和校验规则;BLOB 则以二进制存储,没有字符集和排序规则,所有的对比都是以二进制来进行。
    示例 1
    创建一张表 c1 字段 f1,f2 分别为 tinytext 和 tinyblob。
    mysql> create table c1 (f1 tinytext, f2 tinyblob);Query OK, 0 rows affected (0.03 sec)
    插入示例数据。
    insert into c1 values ('a','a'),('b','b'),('B','B'),('d','d'),('F','F'),('你','你'),('我','我'),('是吧','是吧');
    根据字段 f1 排序。
    mysql> select * from c1 order by f1 f1 | f2 a | a || b | b || B | B || d | d || F | F || 你 | 你 || 我 | 我 || 是吧 | 是吧 8 rows in set (0.01 sec)
    根据字段 f2 排序。
    mysql> select * from c1 order by f2 f1 | f2 B | B || F | F || a | a || b | b || d | d || 你 | 你 || 我 | 我 || 是吧 | 是吧 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))。
    mysql> alter table t2 add key idx_f100(f100);ERROR 1170 (42000): BLOB/TEXT column 'f100' used in key specification without a key length
    1.5 分区表相关
    分区表分区字段不支持大对象。
    mysql> create table p1(f1 text) partition by list columns(f1) (partition p0 values in ('1','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 目录下的所有文件内容。
    存放文件内容的表结构。
    mysql-(ytt/3305)->create table file_list_upload_dir(file_name varchar(100) not null primary key,content longtext, updated datetime);Query OK, 0 rows affected (0.03 sec)
    插入文件内容。
    root@ytt-pc:/var/lib/mysql-files# for i in $(ls); \do mysql -uytt -pytt -P3305 -h127.0.0.1 \-e "use ytt;insert into file_list_upload_dir \values ('$i',load_file('$(pwd)/$i'),now)";done;
    二、JSON 类型
    JSON 类型相比大家都非常熟悉了,轻量级的文本交互格式,不依赖于任何语句。
    JSON 类型在 MySQL 内部以特殊的二进制方式存放,类似于 PostgreSQL 的 JSONB 类型。最大占用空间和 longtext 或者 longblob 一样。text 其实也能存储 JSON 对象,但是没有 JSON 类型的格式校验以及内部提供的众多函数。比如以下例子:
    示例 3
    变量 @a 和 @b 分别为标准 JSON 格式和非 JSON 格式。创建 json1 表。
    mysql-(ytt/3305)->set @a='{"a":1,"b":2,"c":3,"d":4}';Query OK, 0 rows affected (0.00 sec)mysql-(ytt/3305)->set @b="{'a':1,'b':2,'c':3,'d':4}";Query OK, 0 rows affected (0.00 sec)mysql-(ytt/3305)->create table json1 (str1 json,str2 longtext);Query OK, 0 rows affected (0.02 sec)
    给 str1 插入 @a 成功,@b 失败;str2 任何字符都能插入。
    mysql-(ytt/3305)->insert into json1 values (@a,@a);Query OK, 1 row affected (0.00 sec)mysql-(ytt/3305)->insert into json1 values (@b,@b);ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'json1.str1'.mysql-(ytt/3305)->insert into json1 values (@a,@b);Query OK, 1 row affected (0.01 sec)
    又比如说检索部分。对 JSON 的检索比对 text 的检索容易太多。还是拿刚才的例子,需要拿出 a 对应的值 1,对 @b 来说,可能比较麻烦,但是换成 @a 就容易多了。json_extract 函数即可。
    mysql-(ytt/3305)->select json_extract(@a,'$.a') as 'a';+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)
    但是有一点比较幸运的,针对 @b 这种格式,可以把单个字段拆分为多个,这样就可以利用 SQL 语句简单的检索出结果来了。关于拆或者不拆的优劣暂时不在本篇讨论范围内,将会在表设计理念篇来详细介绍。
    好了,本篇关于 MySQL 的大对象类型就介绍就到此为止,希望对大家有所帮助。
    关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!
    关于爱可生
    爱可生成立于2003年,依托于融合、开放、创新的数据处理技术和服务能力,为大型行业用户的特定场景提供深度挖掘数据价值的解决方案。
    公司持续积累的核心关键技术,覆盖到分布式数据库集群、云数据平台、数据库大体量运管平台、海量数据集成于存储、清洗与治理、人工智能分析挖掘、可视化展现、安全与隐私保护等多个领域。
    公司已与多个行业内的专业公司建立了长期伙伴关系,不断促进新技术与行业知识相结合,为用户寻求新的数据驱动的价值增长点。公司已在金融、能源电力、广电、政府等行业取得了众多大型用户典型成功案例,获得了市场的认可和业务的持续增长。

    本帖子中包含更多资源

    您需要 登录 才可以下载或查看,没有帐号?立即注册

    x
    分享到:
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    官方微博:

    官方头条号:

    官方微信

    手机访问:

    官方微信

    QQArchiver 手机版 小黑屋 一起学LINUX - GOGOLINUX 闽ICP备18025837号-1 Discuz! X3.4 Powered by © 2001-2013 Comsenz Inc. 

    本站资源均来自互联网或会员发布,如果侵犯了您的权益请与我们联系,我们将在24小时内删除!谢谢!

    快速回复 快速发帖 返回顶部 返回列表