最近在数据库优化的时候,看到一些表在设计上使用了 text 或者 blob 的字段,单表的存储空间已经达到了近 100G,这种情况再去改变和优化就非常难了
为了清楚大字段对性能的影响,我们必须要知道 innodb 存储引擎的处理方式:
1.1 在 InnoDB 1.0.x 版本之前,InnoDB 存储引擎提供了
和
- Compact
两种格式来存放行记录数据,compact 和 redundant 合称为
- Redundant(Redundant 格式是为兼容之前版本而保留的)
- Antelope (羚羊)
对于 blob,text,varchar(5120) 这样的大字段,innodb 只会存放前 768 字节在数据页中,而剩余的数据则会存储在溢出段中(发生溢出情况的时候适用),最大 768 字节的作用是便于创建前缀索引 / prefix index,其余更多的内容存储在额外的 page 里,哪怕只是多了一个字节。因此,所有列长度越短越好
1.2 MySQL 5.1 中的 innodb_plugin 引入了新的文件格式:
,该文件格式拥有新的两种行格式:
- Barracuda (梭子鱼)
和
- compressed
- dynamic,两种格式对blob字段采用完全溢出的方式,数据页中只存放20字节,其余的都存放在溢出段中,因此,强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型;
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。用户可以通过命令
来查看当前表使用的行格式,其中 row_format 列表示当前所使用的行记录结构类型
- SHOW TABLE STATUS LIKE'table_name'
- 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
,也可以在 create table 的时候指定
- DYNAMIC
。
- ROW_FORMAT=DYNAMIC
注意,如果要修改现有表的行模式为
或
- compressed
,必须先将文件格式设置成 Barracuda:
- dynamic
,再用
- set global innodb_file_format=Barracuda;
去修改才能生效,否则修改无效却无提示
- ALTER TABLE tablename ROW_FORMAT=COMPRESSED;
变长大字段类型包括 blob,text,varchar,其中 varchar 列值长度大于某数 N 时也会存溢出页,在 latin1 字符集下 N 值可以这样计算:innodb 的块大小默认为 16kb,由于 innodb 存储引擎表为索引组织表,树底层的叶子节点为一双向链表,因此每个页中至少应该有两行记录,这就决定了 innodb 在存储一行数据的时候不能够超过 8k,减去其它列值所占字节数,约等于 N。对于 InnoDB,内存是极为珍贵的,如果把 768 字节长度的 blob 都放在数据页,虽然可以节省部分 IO,但是能缓存行数就变少,也就是能缓存的索引值变少了,降低了索引效率
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
类型存储的方式和 COMPACT 一样。
- CHAR
mysql 的 io 以 page 为单位,因此不必要的数据(大字段)也会随着需要操作的数据一同被读取到内存中来,这样带来的问题由于大字段会占用较大的内存(相比其他小字段),使得内存利用率较差,造成更多的随机读取。从上面的分析来看,我们已经看到性能的瓶颈在于由于大字段存放在数据页中,造成了内存利用较差,带来过多的随机读,那怎么来优化掉这个大字段的影响
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
将主表拆分为一对一的两个关联表,将大字段单独放到另外一张表后,单行长度变的非常的小,page 的行密度相比原来的表大很多,这样就能够缓存足够多的行,buffer pool 的命中率就会提高,应用程序需要额外维护的是一张大字段的子表,还可以通过覆盖索引来优化,将索引和原表结构分开,从访问密度较小的数据页改为访问密度很大的索引页,随机 io 转换为顺序 io
总结:还是让单个 page 能够存放足够多的行,不断的提示内存的命中率,从数据库底层存储的原理出发,能够更深刻的优化数据库
综上,如果在实际业务中,确实需要在 InnoDB 表中存储 BLOB、TEXT、长 VARCHAR 列时,有下面几点建议:
http://www.hudong.com/wiki/《MySQL 技术内幕:InnoDB 存储引擎》
http://www.mysqlperformanceblog.com/2008/01/11/mysql-blob-compression-performance-benefits/
http://www.mysqlperformanceblog.com/2012/05/30/data-compression-in-innodb-for-text-and-blob-fields/
http://yoshinorimatsunobu.blogspot.com/2010/11/handling-long-textsblobs-in-innodb-1-to.html
http://blog.opskumu.com/mysql-blob.html
http://hidba.org/?p=551
http://blog.chinaunix.net/uid-24485075-id-3523032.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html
来源: http://www.cnblogs.com/chenpingzhao/p/6719258.html