mysq优化之（char和varchar）

最新推荐文章于 2022-10-13 15:05:00 发布

Lei_Da_Gou

最新推荐文章于 2022-10-13 15:05:00 发布

阅读量771

点赞数 1

分类专栏： mysql优化

mysql优化专栏收录该内容

19 篇文章 0 订阅

订阅专栏

总结：char的设计提高查询速度，varchar的设计节省空间。(在表设计合理的情况下)

适合存储很短的字符串、且对于经常变更的数据，char比varchar好（因为varchar会涉及碎片问题，变更的长度大于指定的长度才会出现。）

字符较固定的字段使用char，字符大于60建议使用varchar，因为大于这个长度的字符可能时间的节省大多使建立在空间的基础上的。网上经常看到char(100)和varchar(100)的查询速度的测试，测试出来的结果使varchar更快，我觉得测试方式都有问题，创建的使char(100)和varchar(100)，但是里面的测试字符串却只有6、7个，这样测试的结果varchar比char更快有什么好惊讶的？可以直接的说表设计都错了。如果字符串只有6、7个请用char(10)和varchar(10)进行测试。

需要注意的是,varchar(10)和varchar(255)的区别还是很大的，因为varchar会自动扩充，varchar节省的是磁盘空间，而不是内存的空间，varchar(255)会导致每次内存使用255来保存这个字符串，因此建表的时候不要再使用默认的255了，数据量大了，排序、分组等使用临时表操作的时候会占用大量mysql内存，影响mysql性能。当然最好的是不使用临时表。

在mySQL 中char 和 varchar 都是存储字符串的，区别在于char有固定的长度，而varchar属于可变长的字符类型。

char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）。

char 固定长度，所以在处理速度上要比varchar快速很多，但是对费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型。

定长的char的优势：
一，存储很短的信息，比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占1个byte用于存储信息长度，本来打算节约存储的现在得不偿失。

二，固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占1个长度信息。

三，十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

总的说来：char定长，存储效率不如varchar，对于短数据的查询优于varchar
---------------------
作者：米斯特昂
来源：CSDN
原文：https://blog.csdn.net/CSDN_GOON/article/details/50057669
版权声明：本文为博主原创文章，转载请附上博文链接！

一般初学会认为，二者占用的空间是一样的。比如说我存储5个char，二者都是实际占用了5个char了【勘误：varchar在实际存储的时候会多一个byte用来存放长度】。
但是深入一下，设计数据库的时候，二者一样吗？
答案是否定的【至少varchar类型需要在数据之前利用一个或者两个字节来存储数据的长度】【二者在内存中的操作方式也是不同的，下面的例子中有体现】。看下面的例子。
如现在用户需要存储一个地址信息。根据评估，只要使用100个字符就可以了。但是有些数据库管理员会认为，反正Varchar数据类型是根据实际的需要来分配长度的。还不如给其大一点的呢。为此他们可能会为这个字段一次性分配200个字符的存储空间。这VARCHAR(100)与VARCHAR(200)真的相同吗?结果是否定的。虽然他们用来存储90个字符的数据，其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，但是对于内存来说，则不是。其时使用固定大小的内存块来保存值。简单的说，就是使用字符类型中定义的长度，即200个字符空间。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响。解释可以参见这里。所以如果某些字段会涉及到文件排序或者基于磁盘的临时表时，分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度，然后选择一个最长的字段来设置字符长度。如果为了考虑冗余，可以留10%左右的字符长度。千万不能认为其为根据实际长度来分配存储空间，而随意的分配长度，或者说干脆使用最大的字符长度。
----------------------------------char------------------------------------------
1、从碎片角度进行考虑，使用CHAR字符型时，由于存储空间都是一次性分配的。为此某个字段的内容，其都是存储在一起的。单从这个角度来讲，其不存在碎片的困扰。而可变长度的字符数据类型，其存储的长度是可变的。当其更改前后数据长度不一致时，就不可避免的会出现碎片的问题。故使用可变长度的字符型数据时，数据库管理员要时不时的对碎片进行整理。如执行数据库导出导入作业，来消除碎片。
2、考虑其长度的是否相近，如果某个字段其长度虽然比较长，但是其长度总是近似的，如一般在90个到100个字符之间，甚至是相同的长度。此时比较适合采用CHAR字符类型。比较典型的应用就是MD5哈希值。当利用MD5哈希值来存储用户密码时，就非常使用采用CHAR字符类型。因为其长度是相同的。另外，像用来存储用户的身份证号码等等，一般也建议使用CHAR类型的数据。
另外请大家考虑一个问题，CHAR(1)与VARCHAR(1)两这个定义，会有什么区别呢?虽然这两个都只能够用来保存单个的字符，但是VARCHAR要比CHAR多占用一个存储位置。这主要是因为使用VARCHAR数据类型时，会多用1个字节用来存储长度信息。这个管理上的开销char字符类型是没有的。

---------------------------------总结---------------------------------------------

二者在磁盘上存储占的空间是一样的。区别有二。第一、一个变长一个固定长度。第二、在内存中的操作方式，varchar也是按照最长的方式在内存中进行操作的。比如说要进行排序的时候，varcahr(100)是按照100这个长度来进行的。

-----------------------------------------------------------------------------------

varchar的最大长度是多少呢？

参见这里

mysql的vachar字段的类型虽然最大长度是65535，但是并不是能存这么多数据，最大可以到65533（不允许非空字段的时候），当允许非空字段的时候只能到65532【在允许空的时候，varchar(65532) will be 2 bytes (length) + up to 65532 chars (latin1) + 1 null byte】

VARCHAR
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。他比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。
VARCHAR需要使用1或者2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。
VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。
下面这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大得多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
在5.0或者更高版本，MySQL在存储和检索时会保留末尾空格。但在4.1或更老的版本，MySQL会剔除末尾空格。
InnoDB则更灵活，它可以把过长的VARCHAR存储为BLOB。

CHAR
CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格（在MySQL4.1和更老版本中VARCHAR也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区别只是在存储格式上）。CHAR值会根据需要采用空格进行填充以方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

CHAR类型的这些行为可能有一点难以理解，下面通过一个具体的例子来说明。首先，我们创建一张只有一个CHAR(10)字段的表并且往里面插入一些值：

当检索这些值的时候，会发现string3末尾空格被截断了。

如果用VARCHAR(10)字段存储相同的值，可以得到如下结果：

数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和变长的字符串。Memory引擎只支持定长的行，即使有变长字段也会根据最大长度分配最大空间。不过，填充和截取空格的行为在不同存储引擎都是一样的，因为这是在MySQL服务器层进行处理的。
与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,他们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。填充也不一样：MySQL填充BINARY采用的是\0（零字节）而不是空格，在检索时也不会去掉填充值。
当需要存储二进制数据，并且希望MySQL使用字节码而不是字符串进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。

使用VARCHAR(5)和VARCHAR(200)存储’hello’的空间开销是一样的。那么使用更短的列有什么优势吗？
事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排列或者操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。
所以最好的策略是只分配真正需要的空间。

笔者注：当存储CHAR值时，MySQL会删除所有的末尾空格？
1、CHAR是定长字符串，若CHAR(10)存储”hello”，MySQL服务器层会在末尾用5个空格补全。
2、若CHAR(10)存储”hello “，MySQL服务器层会在末尾用4个空格补全。
3、MySQL无法判断字符串末尾的空格来源，所以删除末尾空格。
---------------------
作者：一任天然
来源：CSDN
原文：https://blog.csdn.net/yirentianran/article/details/79318103
版权声明：本文为博主原创文章，转载请附上博文链接！