您的位置:新葡亰496net > 网络数据库 > 新葡亰496net至于InnoDB存款和储蓄引擎text和blob类型

新葡亰496net至于InnoDB存款和储蓄引擎text和blob类型

发布时间:2019-12-10 14:08编辑:网络数据库浏览(121)

     

    我们在数据库优化的时候,看到一些表在设计上使用了text或者blob的字段,如果单表的存储空间达到了近上百G或者大几十G,这种情况再去改变和优化就非常难了

    本文出处:新葡亰496net至于InnoDB存款和储蓄引擎text和blob类型的优化,数据库设计。 

    一、简介

    为了清楚大字段对性能的影响,我们有必要知道innodb存储引擎的处理方式:

     

    1、一些知识点 

    1.1 在InnoDB 1.0.x版本之前,InnoDB 存储引擎提供了 Compact 和 Redundant(Redundant 格式是为兼容之前版本而保留的) 两种格式来存放行记录数据,compact 和 redundant 合称为Antelope (羚羊)

    对于blob,text,varchar(5120)这样的大字段,innodb只会存放前768字节在数据页中,而剩余的数据则会存储在溢出段中(发生溢出情况的时候适用),最大768字节的作用是便于创建前缀索引/prefix index,其余更多的内容存储在额外的page里,哪怕只是多了一个字节。因此,所有列长度越短越好

    • 大字段在InnoDB里可能浪费大量空间。例如,若存储字段值只是比行的要求多了一个字节,也会使用整个页面来存储剩下的字节,浪费了页面的大部分空间。如果有一个值只是稍微超过了32个页的大小,实际上就需要使用96个页面
    • 扩展存储禁用了自适应哈希,因为需要完整的比较列的整个长度,才能发现是不是正确的数据(哈希帮助InnoDB非常快速的找到“猜测的位置”,但是必须检查“猜测的位置”是不是正确)。因为自适应哈希是完全的内存结构,并且直接指向Buffer Pool中访问“最”频繁的页面,但对于扩展存储空间却无法使用Adaptive Hash

    新葡亰496net 1

     

    1.2 MySQL 5.1 中的 innodb_plugin 引入了新的文件格式Barracuda (梭子鱼),该文件格式拥有新的两种行格式:compresseddynamic,两种格式对blob字段采用完全溢出的方式,数据页中只存放20字节,其余的都存放在溢出段中,因此,强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型;

    新葡亰496net 2

    1.3 innodb的page大小默认为16kb,innodb存储引擎表为索引组织表,树底层的叶子节点为一双向链表,因此每个页中至少应该有两行记录,这就决定了innodb在存储一行数据的时候不能够超过8k,但事实上应该更小,因为还有一些InnoDB内部数据结构要存储,5.6版本以后,新增选项 innodb_page_size 可以修改,在5.6以前的版本,只能修改源码重新编译,但并不推荐修改这个配置

    1.4 InnoDB的data page在有新数据写入时,会预留1/16的空间,预留出来的空间可用于后续的新纪录写入,减少频繁的新增data page的开销,受限于InnoDB存储方式,数据如果是顺序写入的话,最理想的情况下,data page的填充率是15/16,但一般没办法保证完全的顺序写入,因此data page的填充率一般是1/2到15/16。因此每个InnoDB表都最好要有一个自增列作为主键,使得新纪录写入尽可能是顺序的;当data page填充率不足1/2时,InnoDB会进行收缩,释放空闲空间

    1.5 COMPACT行格式相比REDUNDANT,大概能节省20%的存储空间,COMPRESSED相比COMPACT大概能节省50%的存储空间,但会导致TPS下降了90%。因此强烈不推荐使用COMPRESSED行格式

    1.6 使用了blob数据类型,是不是一定就会存放在溢出段中?通常我们认为blob这类的大对象的存储会把数据存放在数据页之外,其实不然,关键点还是要看一个page中到底能否存放两行数据,blob可以完全存放在数据页中(单行长度没有超过8096字节),而varchar类型的也有可能存放在溢出页中(单行长度超过8096字节,前768字节存放在数据页中)

    1.7 mysql在操作数据的时候,以page为单位,不管是更新,插入,删除一行数据,都需要将那行数据所在的page读到内存中,然后在进行操作,这样就存在一个命中率的问题,如果一个page中能够相对的存放足够多的行,那么命中率就会相对高一些,性能就会有提升

    1.8 在off-page中存储的BLOB、TEXT或者长VARCHAR列的page是独享的,不能共享。因此强烈不建议在一个表中使用多个长列

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

    mysql>desc db_page;
     ----------------- ---------------- ---------------- --------------- ------------------- ----------------- 
    | Field           | Type           | Null           | Key           | Default           | Extra           |
     ----------------- ---------------- ---------------- --------------- ------------------- ----------------- 
    | id              | int(11)        | NO             | PRI           |                   | auto_increment  |
    | title           | varchar(100)   | NO             |               |                   |                 |
    | name            | varchar(100)   | YES            |               |                   |                 |
    | content         | text           | YES            |               |                   |                 |
     ----------------- ---------------- ---------------- --------------- ------------------- ----------------- 
    mysql>show variables like "innodb_file_format";
     ------------------------- ----------------- 
    | Variable_name           | Value           |
     ------------------------- ----------------- 
    | innodb_file_format      | Barracuda       |
     ------------------------- ----------------- 
    mysql>show table status like "db_page" G
    *************************** 1. row ***************************
               Name: db_page
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 2
     Avg_row_length: 8192
        Data_length: 16384
    Max_data_length: 0
       Index_length: 0
          Data_free: 0
     Auto_increment: 3
        Create_time: 2017-03-07 13:30:19
        Update_time: 
         Check_time: 
          Collation: utf8_general_ci
           Checksum: 
     Create_options: 
            Comment: 
       Block_format: Original
    

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

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

    在做数据统计类数据库设计的时候,在考虑数据存储的时候,经常会遇到逻辑上同一个BusinessID对应多个数据点的情况,
    比如工资表中的员工ID以及各项工资信息,财务表中的各个报表Id和多个数据点之间的信息
    面对这种情况,如何来设计表结构,是横表,还是竖表,各有那些优缺点,本文将做一个粗浅的分析。

    二、对TEXT/BLOB这类大字段类型的影响

     

    2.1 compact

    变长大字段类型包括blob,text,varchar,其中varchar列值长度大于某数N时也会存溢出页,在latin1字符集下N值可以这样计算:innodb的块大小默认为16kb,由于innodb存储引擎表为索引组织表,树底层的叶子节点为一双向链表,因此每个页中至少应该有两行记录,这就决定了innodb在存储一行数据的时候不能够超过8k,减去其它列值所占字节数,约等于N。对于InnoDB,内存是极为珍贵的,如果把768字节长度的blob都放在数据页,虽然可以节省部分IO,但是能缓存行数就变少,也就是能缓存的索引值变少了,降低了索引效率

    横标和竖表的表现形式

    2.2 dynamic

    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 一样。

    日常生活中也有很多类似的例子,先用一个Excel画一个例子,比如工资表
    这么做就是“横表”,特点是,一个ID对应所有的值信息,以行Key-Value1-Value2-Value3的方式存储

    三. 对TEXT/BLOB型字段存取优化

    mysql的 io 以page为单位,因此不必要的数据(大字段)也会随着需要操作的数据一同被读取到内存中来,这样带来的问题由于大字段会占用较大的内存(相比其他小字段),使得内存利用率较差,造成更多的随机读取。从上面的分析来看,我们已经看到性能的瓶颈在于由于大字段存放在数据页中,造成了内存利用较差,带来过多的随机读,那怎么来优化掉这个大字段的影响

    新葡亰496net 3

    3.1 压缩&合并

    a、innodb提供了barracuda文件格式,将大字段完全存放在溢出段中,数据段中只存放20个字节,这样就大大的减小了数据页的空间占用,使得一个数据页能够存放更多的数据行,也就提高了内存的命中率(对于本实例,大多数行的长度并没有超过8k,所以优化的幅度有限);如果对溢出段的数据进行压缩,那么在空间使用上也会大大的降低,具体的的压缩比率可以设置key_blok_size来实现。

    b、可以把大字段用COMPRESS()压缩后再存为BLOB,或者在发送到MySQL前在应用程序中进行压缩

    c、一张表有多个类blob字段,把它们组合起来如<TEXT><f_big_col1>long..</f_big_col1> <f_content>long..</f_content></TEXT>,再压缩存储

    d、如果预期长度范围varchar就满足,就避免使用TEXT

    如下是竖表(纵表),特点是每行仅存储该ID的某一个类别字段的值,以行的方式存储Key-Value的方式存储

    3.2 拆分

    将主表拆分为一对一的两个关联表,将大字段单独放到另外一张表后,单行长度变的非常的小,page的行密度相比原来的表大很多,这样就能够缓存足够多的行,buffer pool的命中率就会提高,应用程序需要额外维护的是一张大字段的子表,还可以通过覆盖索引来优化,将索引和原表结构分开,从访问密度较小的数据页改为访问密度很大的索引页,随机io转换为顺序io

     

    总结:还是让单个page能够存放足够多的行,不断的提示内存的命中率,从数据库底层存储的原理出发,能够更深刻的优化数据库

    综上,如果在实际业务中,确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时,有下面几点建议:

    • 尽可能将所有数据序列化、压缩之后,存储在同一个列里,避免发生多次off-page

    • 如果预期长度范围varchar就满足,就避免使用TEXT

    • 如果无法将所有列整合到一个列,可以退而求其次,根据每个列最大长度进行排列组合后拆分成多个子表,尽量是的每个子表的总行长度小于8KB,减少发生off-page的频率

    新葡亰496net 4

     

    横标和竖表的设计示例

      下面通过一个具体的例子来说明横标和竖表的一些特点

    --横标
    CREATE TABLE HorizontalTable
    (
        Id                int identity(1,1),
        BusinessId        varchar(50)         ,
        CategoryVal1    varchar(20)         ,
        CategoryVal2    decimal(20,5)     ,
        CategoryVal3    datetime         ,
        CategoryVal4    varchar(20)         ,
        CategoryVal5    varchar(20)         ,
        CategoryVal6    varchar(20)
    )
    insert into HorizontalTable  values ('BH000001','value1',89.12,'20170406','abc4','abc5','abc6')
    insert into HorizontalTable  values ('BH000002','value2',99.11,'20170407','abc4','abc5','abc6')
    
    --竖表
    CREATE TABLE VerticalTable
    (
        Id                int identity(1,1),
        BusinessId        varchar(50),
        CategoryKey        varchar(20),
        Val                varchar(20)
    )
    insert into VerticalTable values ('BH000001','CategoryKey1','values1')
    insert into VerticalTable values ('BH000001','CategoryKey2',89.12)
    insert into VerticalTable values ('BH000001','CategoryKey3','20170406')
    insert into VerticalTable values ('BH000001','CategoryKey4','abc4')
    insert into VerticalTable values ('BH000001','CategoryKey5','ab5')
    insert into VerticalTable values ('BH000001','CategoryKey6','ab6')
    insert into VerticalTable values ('BH000002','CategoryKey1','values2')
    insert into VerticalTable values ('BH000002','CategoryKey2',99.12)
    insert into VerticalTable values ('BH000002','CategoryKey3','20170407')
    insert into VerticalTable values ('BH000002','CategoryKey4','abc4')
    insert into VerticalTable values ('BH000002','CategoryKey5','abc5')
    insert into VerticalTable values ('BH000002','CategoryKey6','abc6')
    

    横表中的数据:

    新葡亰496net 5

    竖表中的数据

      新葡亰496net 6

    可能实际应用中,要比这个示例中的情况更加复杂,那么在设计表结构的时候,如何选择横标或者竖表?
    首先来看横标的特点

    对于横表
      1,同一个Key值对应的列是固定的,比如,比如HorizontalTable中有6个字段
      2,各个字段的值是自由的,比如HorizontalTable中的CategoryVal1是varchar类型的,CategoryVal2是decimal的
      3,表中并不存储描述性字段本身(相比纵表)
      4,相比竖表,存储同样多的数据,行数要少
    对于竖表
      1,同一个Key值对应的列是动态的,因为是按照行存储的,可以存储成Key1—Value1,Key1—Value2,Key1—Value3的方式存储
      2,字段的类型是固定的,但是类似是要兼容的,不能有个性化的字段,比如VerticalTable中的CategoryKey Val,因为固定了这么一个字段
      3,表中需要存储描述字段本身(相比横标),要根据BusinessKey值的不同,重复存储CategoryKey
      4,相比横表,存储同样多的数据,行数要多

    综上可以看出,
      横标的优点:横标的有点事显示的较为清晰直观,同时在字段的选择上更为科学合理,具体的字段可以根据具体情况划分字段类型,
      横标的缺点:不方便扩展和公用,也就是说设计了一张横标,只能在固定的某一种特定的相对不变的场景下使用,
            比如加字段,或者类似的业务想公用一张横表,都有局限

      竖表的优点:最大的特点是可以灵活扩展存储的内容,同时具有一定的公用性
            因为竖表的存储结构不受字段个数的限制,可以存储具有一定共性的业务数据。
      竖表的缺点:竖表的字段类型要兼容,比如横标可以根据具体的值设计成varchar,decimal,datetime等,
            横标为了兼容以上字段类型,只能设计成varchar的,可能会浪费一定的空间

     

      横标和竖表主要考虑的是扩展性和共同性,对于显示方式问题,个人认为倒是问题不大,无非是行转列和列转行的问题
      如下是一个将上述设计的横表转竖表和竖表转横标的示例,也不复杂,因此说,显示的问题不是大问题

    select * from HorizontalTable
    --列转行
    ;WITH HorizontalCET
    AS
    (
        SELECT Id,BusinessId,CategoryVal1,
                cast(CategoryVal2 as varchar(20)) as CategoryVal2,
                cast(CategoryVal3 as varchar(20)) as CategoryVal3,
                CategoryVal4,
                CategoryVal5
        FROM HorizontalTable
    )
    SELECT Id,BusinessId,ColumnName,ColumnVal
    FROM HorizontalCET
    UNPIVOT (ColumnVal FOR ColumnName IN 
                (CategoryVal1,
                CategoryVal2,
                CategoryVal3,
                CategoryVal4,
                CategoryVal5)
            ) tmp
    
    --列转行
    select * from VerticalTable
    SELECT * FROM 
    (
        select BusinessId ,
               CategoryKey,
               Val 
        from VerticalTable
    )t
     PIVOT( MIN(Val) FOR CategoryKey IN (CategoryKey1,
                                        CategoryKey2,
                                        CategoryKey3,
                                        CategoryKey4,
                                        CategoryKey5,
                                        CategoryKey6)
    )a
    

    新葡亰496net 7

      新葡亰496net 8

     

    关于横表和竖表的性能问题

      关于性能问题,很难一概而论,还要结合具体的情况作分析,比如查询方式,查询数据了,索引结构等等都有一定的关系。
      表面上看,竖表存储了大量冗余的数据,浪费了一定量的磁盘空间是事实,但是极端情况下横表也有可能造成极大的空间浪费
      了解SQL Server的同学肯定知道,
      SQL Server中正常来来说是行存储,一行数据不能跨页存储(当然forwarded存储方式的数据除外,有机会说这个),
      SQL Server的最小存储单位是页(Page),一个页的大小是8kb,除去page信息固定占用的空间之外是8060个字节,
      每一行固定的一行数据除了数据自身占用的空间外,至少(不是一定,表结构越复杂占用的额外空间越大)还要占用1 1 2 2 1=7个字节

    新葡亰496net 9

      对于宽表,一旦字段长度达到一定的程度,
      比如每行长度为800个字节,理论上将,在一个page上,存储9行记录之后,还剩余800字节的空间(具体剩余多少跟表结构有关,这里只是举例说明),
      对不起,第十行数据来了已经存不进去了,只能新开页面分配存储空间,这样,当前这个页面就浪费了800字节的存储空间
      反观竖表,因为存储的数据行都非常短,即便发生上述情况,也只会浪费很少的一点数据空间(小于一行数据的空间)
      极端情况下会更加有意思,参考这个

       新葡亰496net 10

      从读取的另外角度来看,大多数情况下,建表的方式都是行存储,意味着每一行的数据是存储在一起的(字段大的时候当然可以跨页面),单个页面存储的数据行数就变得较少
      sqlserver读取数据的时候是按照行来读取的,不管你查询几个字段,最终都是要将整个行的数据读取出来,
      而存储的最小单元是页,也就是page,
      如果一个表的字段非常多,那么一次查询,即便是需要这些字段中的一部分,也要将所有的字段读取出来,这意味着,你读取的行数多的话,读取出来的不需要的字段也将变得更多
      比如一个表中设计了30个字段,正常情况下,一个查询只需要读取6个字段,即便是这样,sqlserver在执行查询的时候依然读取的是30个字段出来,
      这样的的话,读取同样多的数据,就可能需要读取更多的页才能完成这个查询
      如果是设计成竖表,根据具体的Id来,如果有合理的索引,使用索引就可以完成查询,而不需要再去读表的page,这样就可以避免横表读取时候的这种情况。

      有上述可见,对于横表和竖表,不管是设计上还是存储上,优点和缺点都是看站在哪个角度来看的,
      从一个角度来看是有点,从另外一个角度看就可能会变成缺点,只有舍弃一部分,根据实际情况权衡之后做出取舍。
      凡事无绝对,适合即可。

     

    总结:

      本文从适应场景、存储、性能等方面粗浅第分析了表设计时候横标和竖表的特点和优缺点,
      具体设计的时候可综合考虑,做出合理的选择。
      另外,本文肯定还有没有预计或者说想到的情况以及评估方向,也希望有想法的同学补充,谢谢。

     

        

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net至于InnoDB存款和储蓄引擎text和blob类型

    关键词: