AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2016-08-19 7513
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介：

背景

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

基本知识：InnoDB Storage Architecture for InnoDB On Disk Format

蓝图： database --> tablespaces --> pages --> rows --> columns

InnoDB 物理结构存储结构

database_file

InnoDB 逻辑存储结构

tablespace

InnoDB page 存储结构

页类型

数据页（B-tree Node）
undo页（undo Log Page）
系统页（System Page）
事务数据页（Transaction system Page）
插入缓冲位图页（Insert Buffer Page）
未压缩的二进制大对象页（Uncompressd BLOB Page）
压缩的二进制大对象页（compressd BLOB Page）

页大小

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

结构图

innodb_page

InnoDB row 存储结构

rows 文件格式总体规划图

row_file_format

row-fomat为Compact的结构图

compact

row-fomat为Redundant的结构图

不常用

compress & dynamic 与 Compact 的区别之处

dynamic

字段之字符串类型

char(N) vs varchar(N)

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

varchar(N) : 255 vs 256

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

varchar（N） & char（N） 的最大限制

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

[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： 行溢出

  • 为什么会有行溢出off-page这个概念呢

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

  • 如何看出行溢出了？

可以通过姜承尧写的工具查看
其中溢出的页有 Uncompressed BLOB Page: 243453

[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中
溢出的数据使用的是uncompress BLOB page，并且存储独享,这就是存储越来越大的真正原因
通过下面的测试，你会发现，t_long 插入的数据仅仅比 t_short 多了几个字节，但是最终的存储却是2~3倍的差距

* 表结构

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，text，一行数据如果实际长度大于8k会溢出，如果实际长度小于8k则不会溢出，并非所有的blob，text都会溢出

  • 多列总和大字段 vs 一列大字段

多个大字段会导致多次off-page

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
将text等大字段从主表中拆分出来，a）存储到key-value中 b）存储在单独的一张子表中，并且压缩
必须保证一行记录小于8k

参考

http://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html
INNOBASE 官方文档
MySQL技术内幕 InnoDB存储引擎 --姜承尧
http://hedengcheng.com/ --何登成
http://imysql.cn/ --叶金荣

文章标签：
云数据库 RDS MySQL 版
关系型数据库
存储
MySQL
Python
C++
关键词：
云数据库 RDS MySQL 版优化
云数据库 RDS MySQL 版字段
云数据库 RDS MySQL 版实战
云数据库 RDS MySQL 版实战字段
云数据库 RDS MySQL 版字段优化
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
兰春
目录
相关文章
mikechen的互联网架构
|
15天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案，包括索引优化、SQL慢查询优化和数据库表优化，帮助提升数据库性能。关注【mikechen的互联网架构】，10年+BAT架构经验倾囊相授。
mikechen的互联网架构
92 9
MySQL慢查询优化、索引优化、以及表等优化详解
刘大猫.
|
17天前
|
分布式计算 关系型数据库 MySQL
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型 图像处理 光通信 分布式计算 算法语言 信息技术 计算机应用
刘大猫.
36 8
vohelon
|
20天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度？
如何优化MySQL查询速度？【10月更文挑战第31天】
vohelon
46 3
mao29955
|
22天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能？
【10月更文挑战第28天】
mao29955
46 1
土木林森
|
24天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
土木林森
122 1
潇洒洒
|
30天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战：优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时，传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率，我们可以结合使用MySQL和Redis，利用两者的优势来优化数据查询。本文将分享一次实战经验，探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
潇洒洒
60 5
土木林森
|
25天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件，其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引（如B-Tree、复合索引）和优化查询语句（如使用EXPLAIN、优化分页查询），可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志，持续优化性能。
土木林森
54 0
刘大猫.
|
26天前
|
关系型数据库 MySQL Java
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
刘大猫.
26 0
路卿老师
|
15天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法，包括安装并连接MySQL服务器、选择数据库、执行SQL语句（如插入、更新、删除和查询），以及将结果集返回到数组。通过具体示例代码，详细介绍了每一步的操作流程，帮助读者快速入门PHP与MySQL的交互。
路卿老师
29 1
听风de歌
|
17天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
听风de歌
30 4

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    自动执行主从mysql同步的脚本（能执行，但有缺陷）
  • 4
    MySQL的innodb_flush_log_at_trx_commit配置值的设定
  • 5
    FAQ系列 | 启用SELinux后，PHP连接MySQL异常
  • 6
    mysql 数据类型
  • 7
    云原生数据仓库产品使用合集之ADB MySQL湖仓版和 StarRocks 的使用场景区别，或者 ADB 对比 StarRocks 的优劣势
  • 8
    mysql合并查询(多张表) union 和 union all
  • 9
    Mysql数据库主从不同步解决案例
  • 10
    MySQL各种日期类型与整型（转）
  • 1
    MySQL 参数innodb_read_io_threads
    611
  • 2
    实时计算 Flink版产品使用合集之从MySQL到Flink 1.16.2 Flink-SQL的数据同步工作出现了一个异常如何解决
    129
  • 3
    实时计算 Flink版产品使用合集之如果在 MySQL 表中为某个字段设置了默认值，并且在插入数据时指定了该字段为 NULL，那么 MySQL 是否会使用默认值来填充这个字段
    57
  • 4
    实时计算 Flink版产品使用合集之mysql通过flink cdc同步数据，有没有办法所有表共用一个dump线程
    46
  • 5
    实时计算 Flink版产品使用合集之如果MySQL的binlog保存时间只有三天，那么三天之前的数据是不是会通过Doris的外表手动插入
    56
  • 6
    《MySQL 入门教程》第 05 篇 账户和权限，Java高并发编程详解深入理解pdf
    54
  • 7
    实时计算 Flink版产品使用合集之支持 MySQL 的并行复制吗
    73
  • 8
    实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
    374
  • 9
    Mysql优化提高笔记整理，来自于一位鹅厂大佬的笔记，阿里P7亲自教你
    61
  • 10
    Echarts+vue+java+mysql实现数据可视化
    185

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    无影云桌面