【MySQL经典案例分析】关于数据行溢出由浅至深的探讨

一、从常见的报错说起

        故事的开头我们先来看一个常见的sql报错信息:

        相信对于这类报错大家一定遇到过很多次了,特别对于OMG这种已内容生产为主要工作核心的BG,在内容线的存储中,数据大一定是个绕不开的话题。这里的数据“大”,远不止存储空间占用多,其中也包括了单个(表)字段存储多、大,数据留存时间长,数据冗余多,冷热数据不明显导致的体量大,访问峰值随着热点变化明显,逻辑处理复杂导致数据存储压力放大等等。回到这个报错的问题上来,我们先来看一下这个表的结构:

看到这里,我相信大家会有不同的处理方式了,这里就不对各种处理方式的优劣做比较了,仅仅叙述使用频率较高的两种处理方式。

  • 根据报错的指引,把两个大的varchar(22288)改成text、blob
  • 根据业务特点,缩小varchar的存储长度,或者按照规则拆分成多个小的vachar和char

       这两种的处理方式也各有优缺点,把字段改成text或者blob,不仅增大了数据存储的容量,对这个字段的索引页只能采用前缀或者全文索引了,如果业务侧存储的是json格式的数据,5.7支持json数据类型是个不错的选择,可以针对单个子类进行查询和输出。同样如果缩小和拆分的话就比较依赖业务的场景和逻辑需求了,业务使用的逻辑上需要修改,工程量也需要评估。

二、深入探索

       接着我们再来深入分析下关于限制大小“65535”的一些容易混淆的概念。

1、“65535”不是单个varchar(N)中N的最大限制,而是整个表非大字段类型的字段的bytes总合。

---------------------------------------------------------------------------------------------

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

---------------------------------------------------------------------------------------------

2、不同的字符集对字段可存储的max会有影响,例如,UTF8字符需要3个字节存储,对于VARCHAR(255)CHARACTER SET UTF8列,会占用255×3 =765的字节。故该表不能包含超过65,535/765=85这样的列。GBK是双字节的以此类推。

3、可变长度列在评估字段大小时还要考虑存储列实际长度的字节数。例如,VARCHAR(255)CHARACTER SET UTF8列需要额外的两个字节来存储值长度信息,所以该列需要多达767个字节存储,其实最大可以存储65533字节,剩余两个字节存储长度信息。

4、BLOB、TEXT、JSON列不同于varchar、char等字段,列长度信息独立于行长存储,可以达到65535字节真实存储

5、定义NULL列会降低允许的最大列数。

  • InnoDB表,NULL和NOT NULL列存储大小是一样
  • MyISAM表,NULL列需要额外的空间记录其值是否为NULL。每个NULL需要一个额外的位(四舍五入到最接近的字节)。最大行长度计算如下:

         row length = 1  + (sum of column lengths)   + (number of NULL columns + delete_flag + 7)/8 + (number of variable-length columns)

  •         静态表,delete_flag = 1,静态表通过在该行记录一个位来标识该行是否已被删除。
  •         动态表,delete_flag = 0,该标记存储在动态行首,动态表具体可以根据

6、对于InnoDB表,NULL和NOT NULL列存储大小是一样

7、InnoDB允许单表最多1000个列

8、varchar主键只支持不超过767个字节或者768/2=384个双字节 或者767/3=255个三字节的字段 而GBK是双字节的,UTF8是三字节的

9、不用的引擎对索引的限制有区别

  • innodb每个列的长度不能大于767 bytes;所有组成索引列的长度和不能大于3072 bytes
  • myisam 每个列的长度不能大于1000 bytes,所有组成索引列的长度和不能大于1000 bytes

三、真正的故障

      下面来说下今天遇到的业务故障,线上业出现了大量的如下报错,导致程序无法写入数据:

按照提示和正常的思路,我们先第一反应认为业务存在如下的问题:

  • 设置的表结构中字段超过了限制
  • 某个字段插入的数据长度超过了改字段设置的max值

        接着查看了业务的库表结构,如下:

       很快排除了第一个原因,因为首先业务的报错不是在建立表的时候出现的,如果是表中非大字段之和65535,在建表的时候就会出错,而业务是在写入的时候才报错的,而且通过库表结构也能发现大量的都是mediumblob类型字段,非大字段加起来远小于65535。

       接着根据业务提供的具体SQL,appversion、datadata、elt_stamp、id这几个非大字段,也并没有超过限制,mediumblob类型字段最大可存储16M,业务的数据远远没有达到这个量级。按照报错的提示把 appversion、datadata、elt_stamp、id这几个非大字段均改成blob类型,还是无法解决。(根据之前的分析,必然不是问题的根源)。

       冷静下来后,发现其实还有个细节被忽略掉了,业务的失败率不是100%,说明还是有成功的请求,通过对比成功和失败的sql,发现果然数据量差异的还是mediumblob类型字段。那么现在第一个想到的就是,max_allowed_packet这个参数,是不是调小了,是的单个请求超过大小被拒绝了,查了下配置的值(如下图),配置的大小1G,sql的数据长度远没有这么大,这个原因也排除了。

         查到这里基本上排除了常见几个问题,接着再看一下另一个参数的限制:innodb_page_size,这个的默认值是16K,每个page两行数据,所以每行最大8k数据。

查看了下数据表Row_format是Compact,那么我们可以推断问题的原因应该就是innodb默认的approach存储格式会把每个blob字段的前864个字节存储在page里,所以blob超过一定数量的话,单行大小就会超过8k,所以就报错了。通过对比业务写成功和失败的SQL也应征了这个推论,那么现在要怎么解决这个问题?

  • 业务拆分表,大字段进行分表存储
  • 通过解决Row_format的存储方式解决问题

由于业务单表的存储条数并不大,而且业务逻辑不适合拆分,所以我们要在Row_format上来解决这个问题。

       Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic两种,新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式,在数据页中只存放20个字节的指针,实际的数据都存放在BLOB Page中。Compressed行记录格式的另一个功能就是存储在其中的数据会以zlib的算法进行压缩。

相关的变更操作就相对简单了:

     1、 修改MySQL全局变量:

           SET GLOBAL innodb_file_format='Barracuda';

    2、平滑变更原表的属性:

           ROW_FORMAT=COMPRESSED

四、继续学习

       通过这个案例我们可以从中提炼出两个值得深入研究一下的点:

 1、关于innodb_page_size

      从MySQL5.6开始,innodb_page_size可以设置Innodb数据页为8K,4K,默认为16K。这个参数在一开始初始化时就要加入my.cnf里,如果已经创建了表,再修改,启动MySQL会报错。

那么在5.6的版本之前要修改这个值,怎么办?那只能是在源码上做点文章了,然后重新rebuild一下MySQL。

         UNIV_PAGE_SIZE是数据页大小,默认的是16K,该值是可以设置必须为2的次方。对于该值可以设置成4k、8k、16k、32K、64K。同时更改了UNIV_PAGE_SIZE后需要更改UNIV_PAGE_SIZE_SHIFT 该值是2的多少次方为UNIV_PAGE_SIZE,所以设置数据页分别情况如下:

          接着再来说一下innodb_page_size设置成不同值的对于mysql性能上的影响,测试的表含有1亿条记录,文件大小30G。

            ①读写场景(50%读50%写)

               16K,对CPU压力较小,平均在20%

                8K,CPU压力为30%~40%,但select吞吐量要高于16K

            ②读场景(100%读)

               16K和8K差别不明显

InnoDB Buffer Pool管理页面本身也有代价,Page数越多,那么相同大小下,管理链表就越长。因此当我们的数据行本身就比较长(大块插入),更大的页面更有利于提升速度,因为一个页面可以放入更多的行,每个IO写的大小更大,可以更少的IOPS写更多的数据。 当行长超过8K的时候,如果是16K的页面,就会强制转换一些字符串类型为TEXT,把字符串主体转移到扩展页中,会导致读取列需要多一个IO,更大的页面也就支持了更大的行长,64K页面可以支持近似32K的行长而不用使用扩展页。 但是如果是短小行长的随机读取和写入,则不适合使用这么大的页面,这会导致IO效率下降,大IO只能读取到小部分。

2、关于Row_format

        Innodb存储引擎保存记录,是以行的形式存放的。在InnoDB 1.0.x版本之前,InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据。MySQL 5.1 中的innodb_plugin 引入了新的文件格式:Barracuda,该文件格式拥有新的两种行格式:compressed和dynamic。并且把 compact 和 redundant 合称为Antelope。可以通过命令SHOW TABLE STATUS LIKE 'table_name';来查看当前表使用的行格式,其中 row_format 列表示当前所使用的行记录结构类型。

       MySQL 5.6 版本中,默认 Compact ,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;去修改才能生效,否则修改无效却无提示。

①compact

如果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。

②compressed或dynamic

对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的使用,减少存储空间(50%左右,可参见之前“【数据库评测报告】第三期:innodb、tokudb压缩性能”报告中的测试结果),但要求更高的CPU,buffer pool里面可能会同时存储数据的压缩版和非压缩版,所以也多占用部分内存。

         最后参考了《高性能MySQL》,给出一些使用BLOB这类变长大字段类型的建议:

          ①大字段在InnoDB里可能浪费大量空间。例如,若存储字段值只是比行的要求多了一个字节,也会使用整个页面来存储剩下的字节,浪费了页面的大部分空间。同样的,如果有一个值只是稍微超过了32个页的大小,实际上就需要使用96个页面。

          ②太长的值可能使得在查询中作为WHERE条件不能使用索引,因而执行很慢。在应用WHERE条件之前,MySQL需要把所有的列读出来,所以可能导致MySQL要求InnoDB读取很多扩展存储,然后检查WHERE条件,丢弃所有不需要的数据。

          ③一张表里有很多大字段,最好组合起来单独存到一个列里面。让所有的大字段共享一个扩展存储空间,比每个字段用自己的页要好。

          ④把大字段用COMPRESS()压缩后再存为BLOB,或者在发送到MySQL前在应用程序中进行压缩,可以获得显著的空间优势和性能收益。

         ⑤扩展存储禁用了自适应哈希,因为需要完整的比较列的整个长度,才能发现是不是正确的数据。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

使用字面量或者绑定变量在HANA Studio里执行SQL语句

在SAP note 2000002 – FAQ: SAP HANA SQL Optimization里提到了SQL语句的两种执行方式,具体差异体现在where语...

2149
来自专栏信安之路

webgoat-Injection

注入攻击是WEB安全领域中一种最为常见的攻击方式。在“跨站脚本攻击”一章中曾经提到过,XSS本质上也是一种针对HTML的注入攻击。而在“我的安全世界观”一章中,...

860
来自专栏数据和云

Delphi 程序错误写法造成Oracle数据库负载异常

作者介绍 ? 张洪涛 富士康 DBA 注意:本文涉及的问题在9i的环境中测试的,经验证,同样适用于11g. 在用Toad的SGA Trace工具监控我们的Or...

3346
来自专栏黑泽君的专栏

day28_Struts2综合案例

a、拷贝必要的jar包(图中黄色框框) 和 与数据库操作有关的jar包与配置文件(图中绿色框框)

961
来自专栏文渊之博

优化SqlServer--数据压缩

 数据压缩是对存储和性能优势的加强。减少数据库占用的磁盘空间量将减少整体数据文件存储空间,在一下几个方面增加吞吐量:      1.更好的I/O利用率,每个页面...

2057
来自专栏MongoDB中文社区

论MongoDB索引选择的重要性

线上某业务,频繁出现IOPS 使用率100%的(每秒4000IOPS)现象,每次持续接近1个小时,从慢请求的日志发现是一个 getMore 请求耗时1个小时,导...

1082
来自专栏后台架构

Sphinx源码学习笔记(一):索引创建

  因为项目开发需要在游戏内部实现玩家名称的模糊查找功能,本身直接使用Sphinx配置mysql可以直接搭建一套模糊匹配的即可支持功能的实现。

4997
来自专栏Java进阶架构师

「mysql优化专题」90%程序员没听过的存储过程和存储函数教学(7)

储存过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时...

933
来自专栏逸鹏说道

SQL Server 存储过程的几种常见写法分析

最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫, 不知道各种写法孰优孰劣,该选用那种写法,以及各种写法优缺点,本文以一...

4288
来自专栏ChaMd5安全团队

老司机带你过常规WAF

0x00 前言 最近看了不少关于WAF绕过的文章,想把理论深化到实践当中去,于是就有了您正在看的这篇文章,这篇文章分为两大部分,分别写的是SQL注入相关的WAF...

56011

扫码关注云+社区

领取腾讯云代金券