MySQL Innodb数据库性能实践――VARCHAR vs CHAR_MySQL
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卡，且开了回写功能
OS
RHEL5
MySQL
5.1.49/5.1.54
【MySQL配置】
配置项
配置
innodb_buffer_pool_size
18G
innodb_log_file_size
200M
innodb_log_files_in_group
3
sync_binlog
100
innodb_flush_log_at_trx_commit
2
【表配置】
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
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1423
52
1318
25
1269
29
1248
24
iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos
Jul 18, 2024 am 05:48 AM
Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps
How does Hibernate implement polymorphic mapping?
Apr 17, 2024 pm 12:09 PM
Hibernate polymorphic mapping can map inherited classes to the database and provides the following mapping types: joined-subclass: Create a separate table for the subclass, including all columns of the parent class. table-per-class: Create a separate table for subclasses, containing only subclass-specific columns. union-subclass: similar to joined-subclass, but the parent class table unions all subclass columns.
How to handle database connection errors in PHP
Jun 05, 2024 pm 02:16 PM
To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.
Detailed tutorial on establishing a database connection using MySQLi in PHP
Jun 04, 2024 pm 01:42 PM
How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())
How to use database callback functions in Golang?
Jun 03, 2024 pm 02:20 PM
Using the database callback function in Golang can achieve: executing custom code after the specified database operation is completed. Add custom behavior through separate functions without writing additional code. Callback functions are available for insert, update, delete, and query operations. You must use the sql.Exec, sql.QueryRow, or sql.Query function to use the callback function.
How to save JSON data to database in Golang?
Jun 06, 2024 am 11:24 AM
JSON data can be saved into a MySQL database by using the gjson library or the json.Unmarshal function. The gjson library provides convenience methods to parse JSON fields, and the json.Unmarshal function requires a target type pointer to unmarshal JSON data. Both methods require preparing SQL statements and performing insert operations to persist the data into the database.
How to connect to remote database using Golang?
Jun 01, 2024 pm 08:31 PM
Through the Go standard library database/sql package, you can connect to remote databases such as MySQL, PostgreSQL or SQLite: create a connection string containing database connection information. Use the sql.Open() function to open a database connection. Perform database operations such as SQL queries and insert operations. Use defer to close the database connection to release resources.
PHP connections to different databases: MySQL, PostgreSQL, Oracle and more
Jun 01, 2024 pm 03:02 PM
PHP database connection guide: MySQL: Install the MySQLi extension and create a connection (servername, username, password, dbname). PostgreSQL: Install the PgSQL extension and create a connection (host, dbname, user, password). Oracle: Install the OracleOCI8 extension and create a connection (servername, username, password). Practical case: Obtain MySQL data, PostgreSQL query, OracleOCI8 update record.
