varchar和text说不清的那些事

标签: MySQL基础知识 text VARCHAR | 发表时间:2014-12-09 07:45 | 作者:OurMySQL
出处:http://ourmysql.com

最近有几个同学问我varchar和text有啥别吗,这个问题,以前说真的也没太多的整理,以前遇到text在设计中就是尽可能的拆到另一个表中,保持主表尽量的瘦小,可以让innodb bp缓存更多的数据。

今天借次机会系统整理一下,主要从存储上,最大值,默认值几个方面进行比较。

BTW: 从ISO SQL:2003上讲VARCHAR是一个标准型,但TEXT不是(包括tinytext).varchar在MySQL 5.0.3之前只支持0-255byte, 在5.0.3之后才支持到0-65535byte.

从存储上讲:

- text 是要要进overflow存储。 也是对于text字段,不会和行数据存在一起。但原则上不会全部overflow ,
会有768字节和原始的行存储在一块,多于768的行会存在和行相同的Page或是其它Page上。
  
- varchar 在MySQL内部属于从blob发展出来的一个结构,在早期版本中innobase中,也是768字节以后进行overfolw存储。
  
- 对于Innodb-plugin后: 对于变长字段处理都是20Byte后进行overflow存储
(在新的row_format下:dynimic compress)

说完存储后,说一下使用这些大的变长字段的缺点:


- 在Innobase中,变长字段,是尽可能的存储到一个Page里,这样,如果使用到这些大的变长字段,会造成一个Page里能容纳的行
数很少,在查询时,虽然没查询这些大的字段,但也会加载到innodb buffer pool中,等于浪费的内存。
(buffer pool 的缓存是按page为单位)(不在一个page了会增加随机的IO)
 
- 在innodb-plugin中为了减少这种大的变长字段对内存的浪费,引入了大于20个字节的,都进行overflow存储,
而且希望不要存到相同的page中,为了增加一个page里能存储更多的行,提高buffer pool的利用率。 这也要求我们,
如果不是特别需要就不要读取那些变长的字段。


那问题来了? 为什么varchar(255+)存储上和text很相似了,但为什么还要有varchar, mediumtext, text这些类型?
(从存储上来讲大于255的varchar可以说是转换成了text.这也是为什么varchar大于65535了会转成mediumtext)

我理解:这块是一方面的兼容,另一方面在非空的默认值上varchar和text有区别。从整体上看功能上还是差别的。

这里还涉及到字段额外开销的:

- varchar 小于255byte  1byte overhead
- varchar 大于255byte  2byte overhead
 
- tinytext 0-255 1 byte overhead
- text 0-65535 byte 2 byte overhead
- mediumtext 0-16M  3 byte overhead
 
- longtext 0-4Gb 4byte overhead

备注 overhead是指需要几个字节用于记录该字段的实际长度。

从处理形态上来讲varchar 大于768字节后,实质上存储和text差别不是太大了。 基本认为是一样的。
另外从8000byte这个点说明一下: 对于varcahr, text如果行不超过8000byte(大约的数,innodb data page的一半) ,overflow不会存到别的page中。基于上面的特性可以总结为text只是一个MySQL扩展出来的特殊语法有兼容的感觉。

默认值问题:

- 对于text字段,MySQL不允许有默认值。
- varchar允许有默认值

总结:

根据存储的实现: 可以考虑用varchar替代tinytext
如果需要非空的默认值,就必须使用varchar
如果存储的数据大于64K,就必须使用到mediumtext , longtext
varchar(255+)和text在存储机制是一样的
 
需要特别注意varchar(255)不只是255byte ,实质上有可能占用的更多。
 
特别注意,varchar大字段一样的会降低性能,所以在设计中还是一个原则大字段要拆出去,主表还是要尽量的瘦小

源码中类型:

+--Field_str (abstract)
 |  +--Field_longstr
 |  |  +--Field_string
 |  |  +--Field_varstring
 |  |  +--Field_blob
 |  |     +--Field_geom
 |  |
 |  +--Field_null
 |  +--Field_enum
 |     +--Field_set

(末完待续,也希望大家一块讨论一下)

参考:

http://yoshinorimatsunobu.blogspot.com/2010/11/handling-long-textsblobs-in-innodb-1-to.html

http://nicj.net/mysql-text-vs-varchar-performance/

http://www.pythian.com/blog/text-vs-varchar/

测试SQL及方法

create table tb_01(
c1 varchar(255),
c2 varchar(255),
c3 varchar(255),
c4 varchar(255),
c5 varchar(255),
c6 varchar(255),
c7 varchar(255),
c8 varchar(255),
c9 varchar(255),
c10 varchar(255),
c11 varchar(255)
)engine=Innodb;
 
insert into tb_01(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11) values(repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255));
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
 
(testing)[email protected] [wubx]> set global innodb_file_format=BARRACUDA;
Query OK, 0 rows affected (0.00 sec)
 
(testing)[email protected] [wubx]> alter table tb_01 row_format=dynamic;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
(testing)[email protected] [wubx]> insert into tb_01(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11) values(repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255));
Query OK, 1 row affected (0.00 sec)
 
 
set global innodb_file_format=Antelope;
create table tb_02(
c1 varchar(2000),
c2 varchar(2000),
c3 varchar(2000),
c4 varchar(2000),
c5 varchar(2000),
c6 varchar(2000),
c7 varchar(2000),
c8 varchar(2000)
)engine=Innodb;
 
insert into tb_02(c1, c2, c3,c4,c5,c6,c7,c8) values(repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000) );
 
 
create table tb_03(
c1 text,
c2 text,
c3 text,
c4 text,
c5 text,
c6 text,
c7 text,
c8 text,
c9 text,
c10 text,
c11 text
)engine=Innodb;
insert into tb_03(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11) values(repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255));
 
(testing)[email protected] [wubx]> insert into tb_03(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11) values(repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255));
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
 
set global innodb_file_format=BARRACUDA;
alter table tb_03 row_format=dynamic;

猜您喜欢

相关 [varchar text] 推荐:

varchar和text说不清的那些事

- - OurMySQL
最近有几个同学问我varchar和text有啥别吗,这个问题,以前说真的也没太多的整理,以前遇到text在设计中就是尽可能的拆到另一个表中,保持主表尽量的瘦小,可以让innodb bp缓存更多的数据. 今天借次机会系统整理一下,主要从存储上,最大值,默认值几个方面进行比较. BTW: 从ISO SQL:2003上讲VARCHAR是一个标准型,但TEXT不是(包括tinytext).varchar在MySQL 5.0.3之前只支持0-255byte, 在5.0.3之后才支持到0-65535byte..

Sublime Text 2 简介

- Gnauk - 走走停停看看
最近试用了一款新的编辑器 Sublime Text 2,跨平台,据说他是仿TextMate的,没用过TextMate,不知道后者有多厉害. 然而 Sublime Text 2 我一用就爱上他了. 一开始是由于他的迷你地图模式而吸引我的注意力的,这个迷你地图可以概览整个文件. 这个是个亮点,在其他编辑器中都没有见过此类功能.

前端神器 Sublime Text 2

- - 博客园_首页
  博主之前一直用notepdd++写前端代码,用得也挺顺手了,早就听说sublime的大名,一直也懒得去试试看,认为都是工具用着顺手就好. 这几天突然心血来潮,下了个试了下,结果. 结果博主毫无节操的抛弃了notepad++. 下面根据博主这几天的使用心得,来介绍下这款前端神器,介于使用时间很短,有些说的不妥的地方还望各位看官海涵.

CSS3.0之text-shadow属性

- - 收集分享互联网资源!
CSS3.0的text shadwo属性已经出现一段时间了,下面就分享一下收集的关于text-shadow效果的使用案例. 如果你仔细研究了这些,你必将对text-shadow有更加深入的了解. text-shadow其语法包括投影X轴偏移、Y轴偏移、模糊参数以及投影颜色,看下图. 一、vintage 文字效果.

Sublime Text 2 使用心得

- - CSDN博客Web前端推荐文章
作为一个前端,有一款好的开发利器是必不可少的,editplus、notepad++都是不错的工具,体积轻巧,启动迅速(dw太浮肿了). 最近,又有一款新的编辑器诞生,席卷前端界,惹得无数喜爱,不少前端er纷纷抛弃用了数年的“伙伴”,投入了她的怀抱——Sublime Text2. Sublime Text2是一款跨平台的编辑器,再也不用为换平台而找不到合适的、熟悉的编辑器担忧了.

MySQL数据库中char与varchar性能分析

- - 数据库 - ITeye博客
在数据库中,字符型的数据是最多的,可以占到整个数据库的80%以上. 为此正确处理字符型的数据,对于提高数据库的性能有很大的作用. 在字符型数据中,用的最多的就是Char与Varchar两种类型. 前面的是固定长度,而后面的是可变长度. 现在我们需要考虑的是,在什么情况下使用Char字符型数据,什么情况下采用Varchar字符型数据.

mysql text 字段过多解决方法

- - 非技术 - ITeye博客
表类型: innodb, row_format=compact (这是默认的行格式). 插入超过10个blob, blob的数据量很小(<768字节), 插入成功. 插入超过10个blob, blob的数据量很大(>768字节), 插入失败:报 Got error 139 from storage engine.

一些必不可少的Sublime Text 2插件

- - 前端观察
中文原文: 一些必不可少的sublime text 2插件. 整理自: Essential Sublime Text 2 Plugins and Extensions. 请尊重版权,转载请注明来源,多谢. Sublime Text 2是一个轻量、简洁、高效、跨平台的编辑器,方便的配色以及兼容vim快捷键等各种优点博得了很多前端开发人员的喜爱,当然也包括我,在看到 小飞的介绍后,我就一直在用了.

Sublime Text 2 – 非常强大的跨平台编辑器

- - 小众软件 - Appinn
@ sfufoet 我一直用 EmEditor 6 这个老古董编辑器,就因为两点:支持宏;输入文字之后全部删除,关闭标签不会提示保存,因此 EmEditor 6 是个非常适合替代记事本的编辑器,但是它不适合写代码. 目前 EmEditor 6 的英文版是免费的. Sublime Text 2 之后,发现它非常强大好用,更新也非常快.

无限存储之胖文本数据库TTD(Thick Text Database)

- - ITeye博客
无限存储之胖文本数据库TTD(Thick Text Database). 所谓的“胖”就是多、大、丰富的意思,像我们平时看到的胖客户端、胖操作系统、胖文本编辑器等各种应用中的“胖”,就是指丰富多样的功能;这里的胖文本数据库的“胖”是指无限多、无限大的意思,结合数据库一起来说就是可以存储无限多数据的数据库.