前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL的实战系列:大字段如何优化

MySQL的实战系列:大字段如何优化

作者头像
慕白
发布2018-09-21 11:48:33
5.2K0
发布2018-09-21 11:48:33
举报
文章被收录于专栏:极客慕白的成长之路

MySQL的实战系列:大字段如何优化

# 背景

线上发现一张表,1亿的数据量,物理大小尽然惊人的大,1.2T 最终发现,原来有很多字段,10个VARCHAR,1个文本 这么大的表,会给运维带来很大的痛苦:DDL咋办恢复咋办备份咋办???

基本知识:InnoDB磁盘格式的InnoDB存储架构

蓝图:数据库 - >表空间 - >页面 - >行 - >列

InnoDB物理结构存储结构

InnoDB逻辑存储结构

InnoDB页面存储结构

页类型

数据页(B-tree Node) 撤消页面(撤消日志页面) 系统页面(系统页面) 事务数据页(事务系统页面) 插入缓冲位图页(插入缓冲页面) 未压缩的二进制大对象页面(未压缩的BLOB页面) ) 压缩的二进制大对象页(压缩BLOB页面)

页大小

默认16K(若果没有特殊情况,下面介绍的都是默认16K大小为准) 一个页内必须存储2行记录,否则就不是B +树,而是链表了

结构图

InnoDB row存储结构

行文件格式总体规划图
行fomat为紧凑型的结构图
行fomat为冗余的结构图

不常用

compress&dynamic与Compact的区别之处

字段之字符串类型

char(N)vs varchar(N)

不管是char,还是varchar,在compact row-format格式下,NULL都不占用任何存储空间 在多字节字符集的情况下,CHAR vs VARCHAR的实际行存储基本没区别 CHAR不管是否是多字符集,对未能占满长度的字符还是会填充为0x20 规范中:对字符和VARCHAR可以不做要求

varchar(N):255 vs 256

当实际长度大于255的时候,变长字段长度列表需要用两个字节存储,也就意味着每一行数据都会增加1字节个 实测下来存储空间增长并不算大,且性能影响也不大,所以,尽量在256之内吧

varchar(N)&char(N)的最大限制

char的最大限制是:N <= 255 varchar的最大限制是:N <= 65535,注意官方文档说的是N是字节,并且说的是一行的所有字段的总和小于65535,而varchar(N)中的ñ表示的是字符。 测试后发现,65535并不是最大限制,最大的限制是65532

代码语言:javascript
复制
[MySQL 5.6.27]

* char的最大限制是: N<=255
root:test> create table test( a char(65535))charset=latin1 engine=innodb;
ERROR 1074 (42000): Column length too big for column 'a' (max = 255); use BLOB or TEXT instead

* 测试后发现,65535并不是最大限制,最大的限制是65532
root:test> create table test( a varchar(65535))charset=latin1 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

root:test> create table test( a varchar(65532))charset=latin1 engine=innodb;
Query OK, 0 rows affected (0.00 sec)

* varchar 的最大限制是: N<=65535 , 注意官方文档说的是N是字节,并且说的是一行的所有字段的总和小于65535,而varchar(N)中的N表示的是字符

root:test> create table test_1( a varchar(30000),b varchar(30000),c varchar(5535))charset=latin1 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

* varchar(N)中的N表示的是字符

root:test> create table test_1( a varchar(50000))charset=utf8 engine=innodb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root:test> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1246 | Converting column 'a' from VARCHAR to TEXT |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

root:test> show create table test_1;
+--------+-------------------------------------------------------------------------------+
| Table  | Create Table                                                                  |
+--------+-------------------------------------------------------------------------------+
| test_1 | CREATE TABLE `test_1` (
  `a` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
off-page:行溢出
  • 为什么会有行溢出关闭页的这个概念呢

假设创建了一张表,里面有一个字段是一个varchar(30000),innoDB的页才16384个字节,如何存储的下呢?所以行溢出就来了嘛

  • 如何看出行溢出了?

可以通过姜承尧写的工具查看 其中溢出的页有未压缩的BLOB页面:243453

代码语言:javascript
复制
[root()@xx script]# python py_innodb_page_info.py t.ibd

Total number of page: 537344:
Insert Buffer Bitmap: 33
Freshly Allocated Page: 74040
File Segment inode: 1
B-tree Node: 219784
File Space Header: 1
扩展描述页: 32
Uncompressed BLOB Page: 243453
  • 溢出有什么危害

溢出的数据不再存储在B + tree中 溢出的数据使用的是解压缩BLOB页面,并且存储独享,这就是存储越来越大的真正原因 通过下面的测试,你会发现,t_long插入的数据仅仅比t_short多了几个字节,但是最终的存储却是2~3倍的差距

代码语言:javascript
复制
* 表结构

root:test> show create table t_long;
+--------+---------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                            |
+--------+---------------------------------------------------------------------------------------------------------+
| t_long | CREATE TABLE `t_long` (
  `id` int(11) DEFAULT NULL,
  `col1` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root:test> show create table t_short;
+---------+----------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                             |
+---------+----------------------------------------------------------------------------------------------------------+
| t_short | CREATE TABLE `t_short` (
  `id` int(11) DEFAULT NULL,
  `col1` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


* 测试案例

foreach $num (1 .. 48849){

        $sql_1 = "insert into $table_short select $num,repeat('a',8090)";
        $sql_2 = "insert into $table_long select $num,repeat('a',8098)";
        `$cmd -e " $sql_1 "`;
        `$cmd -e " $sql_2 "`;
}


* 最终的记录数

root:test> select count(*) from t_short;
+----------+
| count(*) |
+----------+
|    48849 |
+----------+
1 row in set (0.03 sec)

root:test> select count(*) from t_long;
+----------+
| count(*) |
+----------+
|    48849 |
+----------+
1 row in set (0.02 sec)


* 页类型的比较

[root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_short.ibd
Total number of page: 25344:
Insert Buffer Bitmap: 2
Freshly Allocated Page: 887
File Segment inode: 1
B-tree Node: 24452
File Space Header: 1
扩展描述页: 1



[root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_long.ibd
Total number of page: 60160:
Insert Buffer Bitmap: 4
Freshly Allocated Page: 8582
File Segment inode: 1
B-tree Node: 2720
File Space Header: 1
扩展描述页: 3
Uncompressed BLOB Page: 48849


* 最终大小的对比

[root()@xx test]# du -sh * | grep 'long\|short' | grep ibd
941M    t_long.ibd
397M    t_short.ibd

* 结论

t_short 的表,在400M左右可以理解,因为 8k * 48849 = 400M

t_long 的表,由于独享48849个Uncompressed BLOB Page,严重浪费空间
  • 什么情况下会溢出

原则:只要一行记录的总和超过8k,就会溢出。 所以:varchar(9000)或者varchar(3000)+ varchar(3000)+ varchar(3000),当实际长度大于8k的时候,就会溢出 所以:Blob ,文字,一行数据如果实际长度大于8K会溢出,如果实际长度小于8K则不会溢出,并非所有的斑点,文本都会溢出

多个大字段会导致多次关闭页

代码语言:javascript
复制
root:test> show create table t_3_col;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------+
| Table   | Create Table
                                       |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------+
| t_3_col | CREATE TABLE `t_3_col` (
  `id` int(11) DEFAULT NULL,
  `col1` varchar(7000) DEFAULT NULL,
  `col2` varchar(7000) DEFAULT NULL,
  `col3` varchar(7000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------+
1 row in set (0.00 sec)

root:test> show create table t_1_col;
+---------+---------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                    |
+---------+---------------------------------------------------------------------------------------------------------------------------------+
| t_1_col | CREATE TABLE `t_1_col` (
  `id` int(11) DEFAULT NULL,
  `col1` varchar(21000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root:test>
root:test>
root:test> insert into t_1_col(col1) select repeat('a',21000);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

root:test>
root:test>
root:test> insert into t_3_col(col1,col2,col3) select repeat('a',7000),repeat('a',7000),repeat('a',7000);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0


[root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_1_col.ibd
Total number of page: 6:
Insert Buffer Bitmap: 1
Uncompressed BLOB Page: 2
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

[root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_3_col.ibd
Total number of page: 7:
Insert Buffer Bitmap: 1
Uncompressed BLOB Page: 3
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

如何对大字段进行优化

如果有多个大字段,尽量序列化后,存储在同一列中,避免多次off-page 将文等大字段从主表中拆分出来,a)存储到键值中b)存储在单独的一张子表中,压缩并且 必须保证一行记录小于8K

除特别注明外,本站所有文章均为慕白博客原创,转载请注明出处来自https://cloud.tencent.com/developer/article/1345584

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

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

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • # 背景
  • 基本知识:InnoDB磁盘格式的InnoDB存储架构
    • InnoDB物理结构存储结构
      • InnoDB逻辑存储结构
        • InnoDB页面存储结构
          • 页类型
          • 页大小
          • 结构图
        • InnoDB row存储结构
          • 行文件格式总体规划图
          • 行fomat为紧凑型的结构图
          • 行fomat为冗余的结构图
          • compress&dynamic与Compact的区别之处
        • 字段之字符串类型
          • char(N)vs varchar(N)
          • varchar(N):255 vs 256
          • varchar(N)&char(N)的最大限制
          • off-page:行溢出
        • 如何对大字段进行优化
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档