MySQL中采用类型varchar(20)和varchar(255)对性能上的影响

本文详细解析了MySQL中不同类型的字符串字段(varchar与char)在建立索引时的存储方式及内存占用情况,对比了不同长度的varchar字段在创建索引时的区别,并探讨了这些区别如何影响查询性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1.MySQL建立索引时如果没有限制索引的大小,索引长度会默认采用的该字段的长度,也就是说varchar(20)和varchar(255)对应的索引长度分别为20*3(utf-8)(+2+1),255*3(utf-8)(+2+1),其中"+2"用来存储长度信息,“+1”用来标记是否为空,加载索引信息时用varchar(255)类型会占用更多的内存; (备注:当字段定义为非空的时候,是否为空的标记将不占用字节)例如,测试sql(InnoDB引擎)如下:


varchr(10)变长字段且允许NULL:10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) 

varchr(10)变长字段且不允许NULL:10*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段) 

char(10)固定字段且允许NULL:10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL) char(10)固定

字段且允许NULL:10*(Character Set:utf8=3,gbk=2,latin1=1)根据这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段都被查询用到。

2.varchar(20)与varchar(255)都是保持可变的字符串,当使用ROW_FORMAT=FIXED创建MyISAM表时,会为每行使用固定的长度空间,这样设置不同的varchar长度值时,存储相同数据所占用的空间是不一样。

通常情况下使用varchar(20)和varchar(255)保持'hello'占用的空间都是一样的,但使用长度较短的列却有巨大的优势。较大的列使用更多的内存,因为MySQL通常会分配固定大小的内存块来保存值,这对排序或使用基于内存的临时表尤其不好。同样的事情也会发生在使用文件排序或者基于磁盘的临时表的时候。



<think>我们讨论MySQL中的CHARVARCHAR类型。区别:1.存储方式:-CHAR:固定长度。当定义一个CHAR(10)的字段时,无论实际存储的字符串长度是多少,它都会占用10个字符的存储空间(如果字符集是单字节的,则是10个字节;如果是多字节字符集,则可能占用更多字节)。如果存储的字符串长度小于定义的长度,会在右边填充空格以达到指定长度。当检索CHAR值时,尾部的空格会被移除(除非设置了PAD_CHAR_TO_FULL_LENGTH模式)。-VARCHAR:可变长度。VARCHAR(10)的字段,如果存储的字符串长度为5,则只占用5个字符的空间(另外需要1或2个字节来存储长度信息,因为VARCHAR类型在数据前有一个或两个字节的前缀表示长度,当长度<=255时用1个字节,否则用2个字节)。存储时不会填充空格,检索时也不会移除尾部空格(但要注意,在插入时如果字符串尾部有空格,是会保留的,除非超过列定义的长度会被截断)。2.存储空间:-CHAR类型会占用定义长度的空间,即使实际内容较少。-VARCHAR类型只占用实际存储数据的长度加上长度前缀的空间。3.性能:-由于CHAR是固定长度,所以对于经常变更的数据,CHAR类型不容易产生碎片。对于长度变化不大的数据(如MD5值),使用CHAR性能可能更好,因为存储检索更快。-VARCHAR类型由于长度可变,在更新时如果长度变化,可能导致额外的操作(如页分裂),所以更新频繁且长度变化大的字段使用VARCHAR可能影响性能。4.最大长度:-CHAR最大长度为255个字符(与字符集有关,如果是utf8mb4,每个字符最多4字节,那么一个CHAR(255)列最多需要255*4=1020字节,但实际存储时,MySQL对每个行有最大限制,通常是65535字节,所以还要考虑整行的总大小)。-VARCHAR的最大长度也是受行大小限制,但VARCHAR可以定义更长(例如65535字节,但实际可用长度会减去长度前缀其他列所占空间,并且也受字符集影响)。在MySQL5.0.3之前,VARCHAR最大长度为255字节,之后版本最大长度为65535字节(注意是字节,而不是字符,所以对于多字节字符集,实际字符数会减少)。5.空格处理:-在存储时,CHAR类型会在值不够长度时填充空格,检索时默认会去掉尾部空格(除非设置SQL模式为PAD_CHAR_TO_FULL_LENGTH)。-VARCHAR类型在存储时不会填充空格,检索时也不会去掉尾部空格。例子:假设有一个表:CREATETABLEtest(aCHAR(10),bVARCHAR(10));插入数据:INSERTINTOtest(a,b)VALUES('x','x');在CHAR列a中,存储的实际是'x'(后面9个空格),但在查询时,SELECTa,LENGTH(a)FROMtest;返回的a是'x'(去掉尾部空格),LENGTH(a)返回1(去掉空格后的长度)。而b列返回的则是'x',LENGTH(b)也是1。但是,如果使用SELECTa,HEX(a)FROMtest;可以看到CHAR列存储的实际数据(在非严格模式下,如果使用PAD_CHAR_TO_FULL_LENGTH,则返回时会保留空格)。另外,如果插入的字符串尾部有空格,在CHAR列中会被去掉(因为存储时会填充到固定长度,然后检索时又去掉填充的空格,所以相当于原始字符串尾部的空格也会被去掉)。而在VARCHAR列中,尾部空格会被保留。例如:插入('x','x'),然后查询:-CHAR列a会返回'x'(去掉尾部空格)-VARCHAR列b会返回'x'(保留空格)注意:在比较时,MySQL会忽略尾部的空格(对于CHARVARCHAR都是如此,但TEXT类型不会)。例如:SELECT*FROMtestWHEREa='x';--会匹配到'a'列值为'x'的行SELECT*FROMtestWHEREb='x';--不会匹配到'b'列值为'x'的行,因为b列保留了空格,而等值比较时,如果比较的字符串没有空格,那么只有没有空格的值才会匹配。但是,如果使用LIKE或者BINARY比较,则不会忽略尾部空格。总结:-当存储的数据长度相对固定(例如总是10个字符)时,使用CHAR效率更高。-当存储的数据长度变化较大时,使用VARCHAR可以节省存储空间。代码示例(创建表):```sql--创建一个表,包含CHARVARCHAR类型CREATETABLEchar_vs_varchar(idINTAUTO_INCREMENTPRIMARYKEY,fixed_lengthCHAR(10),variable_lengthVARCHAR(10));--插入数据INSERTINTOchar_vs_varchar(fixed_length,variable_length)VALUES('abc','abc'),--插入短字符串('abc','abc'),--插入带尾部空格的字符串('abcdefghij','abcdefghij');--插入正好10个字符的字符串--查询数据--使用LENGTH函数(返回字节数,对于'abc',在utf8mb4中,每个英文字符1字节,所以是3)--使用CHAR_LENGTH函数(返回字符数)SELECTfixed_length,LENGTH(fixed_length)ASfixed_length_bytes,CHAR_LENGTH(fixed_length)ASfixed_length_chars,variable_length,LENGTH(variable_length)ASvar_length_bytes,CHAR_LENGTH(variable_length)ASvar_length_charsFROMchar_vs_varchar;--观察尾部空格的影响SELECTCONCAT('"',fixed_length,'"')ASfixed_with_quotes,CONCAT('"',variable_length,'"')ASvar_with_quotesFROMchar_vs_varchar;--比较操作(注意:在比较时,CHARVARCHAR都会忽略尾部空格,除非使用BINARY或LIKE)SELECT*FROMchar_vs_varcharWHEREfixed_length='abc';--会匹配所有fixed_length以'abc'开头(后面有空格)的行,因为比较时忽略尾部空格SELECT*FROMchar_vs_varcharWHEREvariable_length='abc';--只会匹配variable_length为'abc'(没有空格)的行,因为variable_length保存了空格,而比较时忽略尾部空格,所以'abc''abc'在比较时都被视为'abc',因此也会匹配?但实际测试中,对于VARCHAR,比较时也会忽略尾部空格,所以也会匹配?--实际上,根据MySQL文档,在比较CHAR、VARCHARTEXT时,会忽略尾部空格。因此,以下比较是相等的:--'abc'='abc'->true--如果想精确匹配(包括尾部空格),可以使用BINARY运算符:SELECT*FROMchar_vs_varcharWHEREBINARYfixed_length='abc';--不会匹配,因为fixed_length存储的是'abc'(7个空格)或者插入时带空格,但检索时去掉了空格?实际上,插入'abc',在fixed_length中存储时会被填充为10个字符(后面填充空格),然后检索时去掉尾部空格,所以实际值是'abc'(没有空格),因此等于'abc'。--但是,如果我们插入时是'abc'(3个空格),那么在fixed_length中存储的是'abc'(然后填充7个空格?)然后检索时去掉尾部空格,所以得到'abc'(没有空格)。所以,BINARYfixed_length='abc'会匹配,因为比较的是'abc'(去掉尾部空格后的值)'abc'(没有空格)的二进制比较?这有点复杂。--为了验证,我们插入一条记录:INSERTINTOchar_vs_varchar(fixed_length,variable_length)VALUES('abc','abc');--注意:插入fixed_length时,字符串'abc'(5个字符,其中后面3个空格)会被填充为10个字符(后面再补5个空格?),然后检索时去掉所有尾部空格,所以得到'abc'(只有前面3个字符)。--因此,对于fixed_length列,无论插入的字符串尾部有多少空格(除非超过10个字符,会截断),最终检索到的都是去掉尾部空格后的字符串(即原始字符串去掉尾部空格,然后填充到10个字符,再检索时去掉填充的空格,所以最终是原始字符串去掉尾部空格后的结果?)。实际上,在插入CHAR列时,如果字符串尾部有空格,会被当作普通字符,然后在存储时填充空格到定义长度,检索时去掉填充的空格,但原始字符串中尾部的空格不会被去掉?不对,根据MySQL文档,当存储CHAR值时,会去掉尾部的空格(然后填充到指定长度)。所以,插入'abc',存储时会先去掉尾部空格变成'abc',然后填充到10个字符(即'abc'),检索时去掉尾部空格得到'abc'。--所以,在插入CHAR列时,字符串尾部的空格会被去掉,然后再填充。因此,插入'abc'插入'abc'的结果是一样的。--对于VARCHAR列,插入时不会去掉尾部空格(除非超过定义长度,会截断并产生警告,如果是严格模式,则报错)。--因此,在比较时,CHAR列的值已经去掉了尾部空格,而VARCHAR列则保留了尾部空格(但比较时,MySQL也会忽略VARCHAR列的尾部空格)。所以,在非二进制比较中,'abc''abc'在CHARVARCHAR列中都会被视作相等。--如果想严格比较(包括空格),可以使用BINARY:SELECT*FROMchar_vs_varcharWHEREBINARYvariable_length='abc';--这样不会匹配'abc',因为二进制比较会考虑空格。--或者使用LIKE:SELECT*FROMchar_vs_varcharWHEREvariable_lengthLIKE'abc';--不会匹配'abc',因为LIKE模式匹配不会忽略尾部空格(除非模式中有通配符)。注意:'abc'模式不会匹配'abc',因为模式'abc'要求后面没有空格。--但注意:LIKE'abc'只匹配正好是'abc'的字符串,而不会匹配后面有空格的字符串。如果要匹配后面有任意空格的,可以用'abc%'。--所以,总结空格处理:--CHAR列:在存储时会去掉插入字符串的尾部空格(然后填充到固定长度),检索时再去掉填充的空格,因此最终结果中不包含尾部空格。--VARCHAR列:存储时保留尾部空格(不填充),检索时也保留,但在比较操作(非二进制)中,会忽略尾部空格。因此,选择CHAR还是VARCHAR需要根据实际应用场景来决定。相关问题:§§相关问题§§:1.在MySQL中,CHARVARCHAR的最大长度限制分别是多少?2.当使用多字节字符集(如utf8mb4)时,CHAR(10)VARCHAR(10)分别能存储多少个中文字符?3.在什么情况下使用CHAR类型VARCHAR类型性能更好?4.如何设置SQL模式以保留CHAR类型在检索时的尾部空格?</think>### MySQL 中 CHAR VARCHAR 类型有什么区别? #### 核心区别总结 1. **存储方式** - `CHAR`:固定长度,存储时**填充空格**至定义长度 - `VARCHAR`:可变长度,仅存储实际数据 + **长度前缀**(1-2字节) 2. **空间效率** - `CHAR`:浪费空间(短数据时) - `VARCHAR`:节省空间(仅用所需空间) 3. **性能特点** - `CHAR`:读取更快(固定长度,无需计算偏移) - `VARCHAR`:更新可能更慢(长度变化时需重新分配空间) 4. **尾部空格处理** - `CHAR`:存储检索时**自动移除**尾部空格 - `VARCHAR`:**保留**尾部空格(比较时可能被忽略) --- #### 代码示例与解释 ```sql -- 创建测试表 CREATE TABLE string_demo ( char_col CHAR(10), varchar_col VARCHAR(10) ); -- 插入测试数据 INSERT INTO string_demo VALUES ('abc', 'abc'), -- 短字符串 ('abc ', 'abc '), -- 带尾部空格 ('abcdefghij', 'abcdefghij'); -- 满长度字符串 -- 查询数据(显示实际存储效果) SELECT CONCAT('[', char_col, ']') AS char_output, CONCAT('[', varchar_col, ']') AS varchar_output, LENGTH(char_col) AS char_bytes, LENGTH(varchar_col) AS varchar_bytes FROM string_demo; ``` **执行结果:** | char_output | varchar_output | char_bytes | varchar_bytes | |-------------|----------------|------------|---------------| | `[abc]` | `[abc]` | 3 | 3 | | `[abc]` | `[abc ]` | 3 | 5 | <!-- 尾部空格被保留 --> | `[abcdefghij]` | `[abcdefghij]` | 10 | 10 | **关键解释:** 1. **空间占用** - `CHAR(10)` 始终占用 10 字节(单字节字符集) - `VARCHAR(10)` 占用实际长度 + 长度前缀(如 'abc' 占 3+1=4 字节) 2. **空格处理** - `CHAR` 插入 `'abc '` → 存储为 `'abc'`(空格被移除) - `VARCHAR` 插入 `'abc '` → 存储为 `'abc '`(空格保留) 3. **比较行为** ```sql -- CHAR 比较忽略尾部空格 SELECT * FROM string_demo WHERE char_col = 'abc'; -- 匹配所有行 -- VARCHAR 比较也忽略尾部空格(SQL标准) SELECT * FROM string_demo WHERE varchar_col = 'abc'; -- 匹配前两行 ``` --- #### 使用场景建议 - **用 `CHAR` 当**: - 数据长度绝对固定(如 MD5 哈希值、国家代码) - 需要极致读取性能(如高频查询的标识字段) - **用 `VARCHAR` 当**: - 数据长度变化大(如用户名、地址) - 存储空间敏感(大表或文本数据) - 需保留尾部空格(如密码字段) > **临界点提示**:当数据长度变化小于 10% 时,`CHAR` 可能更高效(避免碎片化)。 ---
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值