MySQL Innodb数据库性能实践――VARCHAR vs CHAR

Home

Database

Mysql Tutorial

MySQL Innodb数据库性能实践――VARCHAR vs CHAR_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:44 PM

database

bitsCN.com

学过数据库理论的读者，都应该还记得关于CHAR和VARCHAR的性能对比：CHAR比VARCHAR更快，因为CHAR是固定长度的，而VARCHAR需要增加一个长度标识，处理时需要多一次运算。

针对这种情况，我做了一下基准测试，基准测试环境如下：
【硬件配置】

硬件

配置

CPU

Intel(R) Xeon(R) CPU E5620 主频2.40GHz，物理CPU 2个，逻辑CPU 16个

内存

24G（6块 * 4G DDR3 1333 REG）

硬盘

300G * 3个，SAS硬盘 15000转，无RAID，有RAID卡，且开了回写功能

RHEL5

MySQL

5.1.49/5.1.54

【MySQL配置】

配置项

配置

innodb_buffer_pool_size

18G

innodb_log_file_size

200M

innodb_log_files_in_group

sync_binlog

100

innodb_flush_log_at_trx_commit

【表配置】
VARCHAR平均长度200，CHAR长度250，其它配置如下：

配置项

配置

记录数

1000万，2000万，5000万，1亿

存储引擎

Innodb

行格式

compact

性能测试结果如下：
【查询】

【插入】

【更新】
更新时VARCHAR也是随机长度

【删除】

测试结果展现了一个与理论不太相符的现象：当表大小小于Innodb buffer pool时，CHAR和VARCHAR没有差别，而在表大小大于Innodb buffer pool时，VARCHAR性能反而更高！这是为什么呢？

首先，性能是综合硬件、配置、表记录数、业务模型等多种因素综合后的结果，单一因素的差异，对整体来说可能几乎没有影响；
例如，执行一个操作需要100ms，而CHAR 比 VARCHAR性能上只快了1微秒，那么最终的性能就不会有影响。
这就是当Innodb buffer pool足够大时，CHAR 和VARCHAR没有差别的原因。

再次，理论上CHAR比VARCHAR快的根本原因是站在CPU的角度来说的，但性能是综合各种因素后的最终结果，当Innodb buffer pool小于表大小时，"磁盘读写"成为了性能的关键因素，而VARCHAR更短，因此性能反而比CHAR高。

最后，有朋友可能会认为，VARCHAR更新时如果新的数据比旧的数据要长，可能需要移动数据，导致性能更低；从实测结果来看，这种操作对最终的性能也是没有明显影响的。可能是因为Innodb采用页管理数据，数据移动是先在内存里完成，再写到磁盘，因此数据即使移动也很快。

【应用技巧】
基于以上测试结果和分析，我个人认为一般情况下优先使用VARCHAR，特别是字符串的平均长度比最大长度要小很多的情况；
当然，如果你的字符串本来就很短，例如只有10个字符，那么就优先选CHAR了。

附：
1）有兴趣的朋友可以推断一下：为什么测试结果中10KW的表性能，VARCHAR比CHAR快大约20%？
2）测试数据只为对比用，不代表一般情况下MySQL的性能就这么高，因为为了能够对比，测试时做了很多准备工作，测试操作也是比较特殊的

摘自 yah99_wolf的专栏

bitsCN.com

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

MySQL: How to Add a User RemotelyMay 12, 2025 am 12:10 AM

ToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori

The Ultimate Guide to MySQL String Data Types: Efficient Data StorageMay 12, 2025 am 12:05 AM

TostorestringsefficientlyinMySQL,choosetherightdatatypebasedonyourneeds:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseTEXTforlong-formtextcontent.4)UseBLOBforbinarydatalikeimages.Considerstorageov

MySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMay 11, 2025 am 12:13 AM

When selecting MySQL's BLOB and TEXT data types, BLOB is suitable for storing binary data, and TEXT is suitable for storing text data. 1) BLOB is suitable for binary data such as pictures and audio, 2) TEXT is suitable for text data such as articles and comments. When choosing, data properties and performance optimization must be considered.

MySQL: Should I use root user for my product?May 11, 2025 am 12:11 AM

No,youshouldnotusetherootuserinMySQLforyourproduct.Instead,createspecificuserswithlimitedprivilegestoenhancesecurityandperformance:1)Createanewuserwithastrongpassword,2)Grantonlynecessarypermissionstothisuser,3)Regularlyreviewandupdateuserpermissions

MySQL String Data Types Explained: Choosing the Right Type for Your DataMay 11, 2025 am 12:10 AM

MySQLstringdatatypesshouldbechosenbasedondatacharacteristicsandusecases:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseBINARYorVARBINARYforbinarydatalikecryptographickeys.4)UseBLOBorTEXTforlargeuns

See all articles