MySQL的实战系列：大字段如何优化 # 背景 线上发现一张表，1亿的数据量，物理大小尽然惊人的大，1.2T 最终发现，原来有很多字段，10个VARCHAR，1个文本 这么大的表，会给运维带来很大的痛苦...0x20 规范中：对字符和VARCHAR可以不做要求 varchar（N）：255 vs 256 当实际长度大于255的时候，变长字段长度列表需要用两个字节存储，也就意味着每一行数据都会增加...所以：varchar（9000）或者varchar（3000）+ varchar（3000）+ varchar（3000），当实际长度大于8k的时候，就会溢出 所以：Blob ，文字，一行数据如果实际长度大于...8K会溢出，如果实际长度小于8K则不会溢出，并非所有的斑点，文本都会溢出 多列总和大字段vs一列大字段 多个大字段会导致多次关闭页 root:test> show create table t...Header: 1 B-tree Node: 1 File Segment inode: 1 如何对大字段进行优化 如果有多个大字段，尽量序列化后，存储在同一列中，避免多次off-page
BLOB类型的字段用于存储二进制数据 MySQL中，BLOB是个类型系列，包括：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储文件的最大大小上不同。...MySQL的四种BLOB类型 类型 大小(单位：字节) TinyBlob 最大 255 Blob 最大 65K MediumBlob 最大 16M LongBlob 最大 4G
有一个longtext字段，用于存储长文本，仅万条数据，InnoDB存储文件就达G级，由于是一个小项目，受限于服务器与运维人员水平，不适合使用hdfs，MongoDB等拓展技术栈来解决这种问题，因此直接对mysql...涉及mysql基础知识 一、innodb存储引擎的处理方式 1.mysql在操作数据的时候，以page为单位 不管是更新，插入，删除一行数据，都需要将那行数据所在的page读到内存中，然后在进行操作，这样就存在一个命中率的问题...，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，但事实上应该更小，有一些InnoDB内部数据结构要存储以及预留操作空间， 3.blob...，text大字段 innodb只会存放前768字节在数据页中，而剩余的数据则会存储在溢出段中(发生溢出情况的时候适用)，最大768字节的作用是便于创建前缀索引/prefix index，其余更多的内容存储在额外的...因此，所有列长度越短越好 4.扩展存储禁用了自适应哈希 因为需要完整的比较列的整个长度，才能发现是不是正确的数据(哈希帮助InnoDB非常快速的找到“猜测的位置”，但是必须检查“ 发布者：全栈程序员栈长
java.sql.ResultSet; import java.sql.SQLException; /** * CREATE: CREATE TABLE IDCard ( id char(18),pic BLOB...from fhadmin.cn */ public class LOBTest { protected static final String DEFAULT_URL = "jdbc:mysql...useUnicode=true&characterEncoding=utf8"; protected static final String DRIVER_NAME = "com.mysql.jdbc.Driver...的四种BLOB类型 类型 大小(单位：字节) TinyBlob 最大 255B Blob 最大 65K MediumBlob 最大 16M... LongBlob 最大 4G 插入图像的时候，注意下图像大小，图像超过该类型所能容纳的最大字节的时候，会报错
①compact 如果blob列值长度 768字节，那么前768字节依然在数据页...上面讲的blob或变长大字段类型包括blob、text、varchar，其中varchar列值长度大于某数N时也会存在溢出页，在latin1字符集下N值可以这样计算：innodb的块大小默认为16kb，...②compressed或dynamic 对blob采用完全行溢出，即聚集索引记录（数据页）只保留20字节的指针，指向真实存放它的溢出段地址： ?...五、DBbrain如何轻松处理 数据库智能管家DBbrain对于用户在数据库中使用BLOB这类变长大字段类型也会根据具体场景给出如下优先建议，帮助用户更好的规避一些由于变长大字段带来的业务问题。...把大字段用COMPRESS()压缩后再存为BLOB，或者在发送到MySQL前在应用程序中进行压缩，可以获得显著的空间优势和性能收益。 5.
①compact 如果blob列值长度 768字节，那么前768...字节依然在数据页，而剩余的则放在溢出页(off-page)，如下图： 8.jpg 上面讲的blob或变长大字段类型包括blob、text、varchar，其中varchar列值长度大于某数...②compressed或dynamic 对blob采用完全行溢出，即聚集索引记录（数据页）只保留20字节的指针，指向真实存放它的溢出段地址： 9.jpg dynamic行格式，列存储是否放到...最后参考了《高性能MySQL》，给出一些使用BLOB这类变长大字段类型的建议： ①大字段在InnoDB里可能浪费大量空间。...④把大字段用COMPRESS()压缩后再存为BLOB，或者在发送到MySQL前在应用程序中进行压缩，可以获得显著的空间优势和性能收益。
BLOB与TEXT是为了存储极大的字符串而设计的数据类型，采用二进制与字符串方式存储。...mysql对待这两个类型可谓煞费苦心，mysql会把这两种类型的值当做一个独立的对象处理，存储引擎在存储时通常会做特殊处理，当BLOB与TEXT的值太大时，InnoDB会使用专门的“外部”存储区域来进行存储...Mysql对BLOB与TEXT类型进行排序的处理上与其他类型不同，只针对最前面的max_sort_length自己进行排序，如果只需要对前面的更少的字节进行排序，那么可以通过设置max_sort_length
utf8 结论： 1、int:固定占用4个字节 2、不同字符集占用字节数不同： 3、latin1:2个字节 4、utf8:3个字节 5、是否为空占用1个字节，not null不占用字节 6、变长字段需要记录长度占用...2个字节 7、索引长度计算公式：varchar(20）*3+1+2=63
import java.io.ByteArrayInputStream; import java.io.UnsupportedEncodingException; import java.sql.Blob...blob = rs.getBlob(columnName); byte[] returnValue = null; if (null !...= blob) { returnValue = blob.getBytes(1, (int) blob.length()); } try...blob = cs.getBlob(columnIndex); byte[] returnValue = null; if (null !...= blob) { returnValue = blob.getBytes(1, (int) blob.length()); } try
我们在操作数据存入blob数据的类型,常用来存储头像图片等流数据,blob类型如果想要存储比较大的流文件的数据,建议选用longBlob的数据类型,Demo中的数据就简单的示范了一下,sql文件如下...的格式到数据库 // storePicBlog(); //从数据库读取blob的格式的图片数据 getPicBlog(); } public...storePicBlog() throws FileNotFoundException, SQLException, IOException { String m_dbDriver ="com.mysql.jdbc.Driver..."; String m_dbUrl ="jdbc:mysql://localhost:3306/test?..."; String m_dbUrl ="jdbc:mysql://localhost:3306/test?
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。...这些对应4种BLOB类型，有相同的最大长度和存储需求。 BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。...· 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。 · BLOB和TEXT列不能有 默认值。...MySQL连接程序/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。...例如，可以使用 mysql和mysqldump来更改客户端的max_allowed_packet值。 每个BLOB或TEXT值分别由内部分配的对象表示。
思路： 1：由于日志查询问题，没看到日志之前怀疑：MYSQL数据库字段长度是否真的足够，排查问题未果。...at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3681) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java...:2512) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL...(ConnectionImpl.java:2486) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java...:1858) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal
二者之间的主要差别是BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符数据，比如一遍文章或日记。...TEXT和BLOB中又分别包括TEXT,MEDIUMTEXT,LONGTEXT和BLOB,MEDIUMBLOB,LONGBLOB三种不同的类型，他们之间的主要区别是存储文本长度不用和存储字节不用，用户应该根据实际情况选择能够满足需求的最小存储类型...可以使用合成的（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。...合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列的本身速度快很多。 创建一张表，来介绍合成索引的使用方法。 ?...在不必要的时候避免检索大型的BLOB或TEXT值。 把BLOB或TEXT列分离到单独的表中。
只扫描索引而无需回表的优点： 1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。...，但不是整个查询涉及的字段，mysql5.5和之前的版本也会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。...2.mysql不能在索引中执行LIke操作。mysql能在索引中做最左前缀匹配的like比较，但是如果是通配符开头的like查询，存储引擎就无法做比较匹配。...这种情况下mysql只能提取数据行的值而不是索引值来做比较 优化后SQL：添加索引（artist,title,prod_id），使用了延迟关联(延迟了对列的访问) 说明：在查询的第一阶段可以使用覆盖索引...记录自己对mysql的一些总结 发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/179723.html原文链接：https://javaforall.cn
InnoDB行格式 innodb_file_format 对TEXT/BLOB的影响： 摘录自： http://seanlook.com/2016/05/18/mysql-blob-row_format...http://blog.opskumu.com/mysql-blob.html # MySQL大字段溢出导致数据回写失败 2.1 compact 在 Antelope 两种行格式下，如果blob...(off-page) 上面所讲的讲的blob或变长大字段类型包括blob,text,varchar，其中varchar列值长度大于某数N时也会存溢出页，在latin1字符集下N值可以这样计算：innodb...我们知道对于InnoDB来说，内存是极为珍贵的，如果把768字节长度的blob都放在数据页，虽然可以节省部分IO，但相对来说能缓存行数就变少，也就是能缓存的索引值变少了，降低了索引效率。...2.2 dynamic Barracuda 的两种行格式对blob采用完全行溢出，即聚集索引记录(数据页)里面只保留20字节的指针，指向真实存放它的溢出段地址。
char 是固定长度，其单位也是字符，比如 char(10)，就表示不管你给的什么值，都会被 mysql 固定保存成 10 个字符。 如果给的字符长度小于 10，那么在尾部就会自动用空格补齐。...varchar 最大长度限制 = （行最大字节数（65535） - null 标识字节数 - 长度前缀字节数（1或2）） / 字符集单字符占用最多字节数 看到这里，不知道你有没有一个疑问，为什么长度前缀...1 或 2 个字节就够用了呢，因为 2 个字节的话，2^16 = 65536，这已经超过 mysql 行最大字节数 65535 的限制了，所以 1 到 2 个字节就够用了。...那么 varchar 类型字段的最大字节数应该就是, 65535 (行最大字节数) - 255*3 (一个字符最多占 3 个字节) = 64770， 然后再减去 2 个长度前缀字节， 64770 -...2 = 64768， 最后再算出 varchar 最大字符数为 64768 / 3 = 21589.33， ##字符数21590超过最大字符数会报错 mysql> create table test4
前言 这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记，本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。...varcahr最大长度 一般为65535字节。 若表为一个字段，且为utf8编码，其最大能定义的长度为65535/3 TEXT 四种不同大小，支持的更多样化。...TEXT或BLOB区别 blob的主要优势是可以存储二进制数据，所以可以把图片等存成blob，而text就只能存文本，这个是他们的主要区别，另外text类型可以有字符集和排序规则，这个在blob里是没有的...数据类型-命名规范 所有表名，字段名全部使用小写字母 不同业务，表名使用不同前缀区分，比如：kaola_good,music_song、news_comment 生产环境表名字段名要有实际意义 单个子段尽量使用字段全名...整型在字段长度、索引大小等方面开销更小效率更高，入邮编字段、手机号字段等 注释，每个字段必须以comment语句给出字段的作用 经常访问的大字段（如blob/text）等需要单独放到一张表中，避免降低sal
1. blob 类型 blob(binary large object) 是一个可以存储二进制文件的容器，主要用于存储二进制大对象，例如可以存储图片，音视频等文件。...对比 varchar ，text 类型有以下特点： text 类型无须指定长度。 若数据库未启用严格的 sqlmode ，当插入的值超过 text 列的最大长度时，则该值会被截断插入并生成警告。...255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 # 创建索引测试 发现text类型必须指定前缀长度...mysql> alter table tb_text add index idx_a (a); ERROR 1170 (42000): BLOB/TEXT column 'a' used in key...): BLOB/TEXT column 'b' used in key specification without a key length mysql> alter table tb_text add
uniq_code` (`nick_name`,`account`,`city`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’Test’; 复制代码 原因 在MySQL5.6...在MySQL5.7里默认 innodb_large_prefix=1 解除了767bytes长度限制，但是单列索引长度最大还是不能超过3072bytes。...https://dev.mysql.com/doc/refman/8.0/en/create-index.html varchar(n)占用几个字节跟字符集有关系： 字符类型若为gbk，每个字符占用2个字节...所以我们经常会见到把字段设置成varchar(255)长度的，在utf8字符集下这个是最大不超过767bytes的长度了，但是并不是一定要设置成varchar(255)，还是要根据业务设置每个字段的长度...，这个就是我们说的前缀索引 修改单个索引的最大长度 修改索引限制长度需要在my.ini配置文件中添加以下内容，并重启： #修改单列索引字节长度为767的限制，单列索引的长度变为3072 innodb_large_prefix
个字节，除了我们需要存储的真是数据外，还有额外数据：变长字段长度列表，null值列表(若有not null)则可以省略。...因为我们没有设置not null，所以变长字段长度可能占两个字节，null占一个字节，所以65532个字节。...ERROR 1074 (42000): Column length too big for column 'c' (max = 32767); use BLOB or TEXT instead mysql...or TEXT instead 根据sql提示可以看到，创建gbk字符集的表，则最大可以用32767个字节(65532/2)，因为gbk一个字符占用的最大字节是2，而utf8一个字符占用的最大字节是3...一个表中所有列(不包括隐藏列和记录头信息)，占用的最大字节长度为65535个字节。
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
ICP备案
对象存储
即时通信 IM
实时音视频
