共计 7273 个字符，预计需要花费 19 分钟才能阅读完成。
本篇内容主要讲解“MySQL 中使用 varbinary 比 varchar 更合适吗”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“MySQL 中使用 varbinary 比 varchar 更合适吗”吧!
一 前言
在讨论数据表字段设计的时候，有同学提出使用 vabinary 代替 varchar , 部分开发不明所以, 其实我也是。两者之间具体有什么区别？使用 vabinary 代替 varchar 对业务有何优势？
二 对比测试
2.1 测试环境
数据库版本 Percona Server 5.6.24-72.2-log
create table vbinary
(
id int primary key auto_increment ,
val varbinary(776) not null default
) engine=innodb default charset=utf8mb4;
create table vachar
(
id int primary key auto_increment ,
val varchar(12) not null default
) engine=innodb default charset=utf8mb4;
insert into vbinary(val) values(abaa),(aabb),(bcdd),(ccdd
insert into vachar(val) values(abaa),(aabb),(bcdd),(ccdd)
2.2 定义
varchar(N) 字符串类型，用于存储变长字符串，使用表默认或者指定的校验集合，其中 N 代表存储字符的个数，详细信息请移步《浅谈 varchar(N)》.
varbinary(N) 二进制字符串类型，以二进制字节串存储字符, 无字符集校验区别，均以二进制实际数值作比较。
2.3 长度定义
varchar 存储的是字符个数，varbinary 存储的是字节个数。
test [RW] 10:57:50 insert into vbinary (val,name) value(2msdmlsdyo2enwlenw , disodmalsdsi
Query OK, 1 row affected, 1 warning (0.00 sec)
test [RW] 10:57:55 show warnings;
+———+——+——————————————+
| Level | Code | Message |
+———+——+——————————————+
| Warning | 1265 | Data truncated for column val at row 1 |
+———+——+——————————————+
1 row in set (0.00 sec)
test [RW] 10:58:11 insert into vbinary (val,name) value(有赞是一家移动零售服务提供商 , disodmalsdsi
Query OK, 1 row affected, 1 warning (0.01 sec)
test [RW] 10:59:00 show warnings;
+———+——+——————————————+
| Level | Code | Message |
+———+——+——————————————+
| Warning | 1265 | Data truncated for column val at row 1 |
+———+——+——————————————+
1 row in set (0.00 sec)
test [RW] 10:59:08 select * from vbinary;
+—-+————–+————–+
| id | val | name |
+—-+————–+————–+
| 6 | 2msdmlsdyo2e | disodmalsdsi |
| 7 | 有赞是一 | disodmalsdsi | #
+—-+————–+————–+
7 rows in set (0.00 sec)
test [RW] 10:59:12 insert into vachar(val,name) value(有赞是一家移动零售服务提供商 , disodmalsdsi
Query OK, 1 row affected, 1 warning (0.00 sec)
test [RW] 11:00:02 show warnings;
+———+——+——————————————+
| Level | Code | Message |
+———+——+——————————————+
| Warning | 1265 | Data truncated for column val at row 1 |
+———+——+——————————————+
1 row in set (0.00 sec)
test [RW] 11:00:06 select * from vachar;
+—-+————————————–+————–+
| id | val | name |
+—-+————————————–+————–+
| 4 | ccdd | yz |
| 5 | 有赞是一家移动零售服务提 | disodmalsdsi |
+—-+————————————–+————–+
5 rows in set (0.00 sec)
分析:
varbinary(N)中长度 N 指的是字节串的长度, 一个数字 / 英文字母占用 1 个字节，一个汉字占用 3 个字节 (默认 utf8、utf8mb4 字符集), 指定 N 则可以存储 N 个数字或者字母,N/ 3 个汉字。
varchar(N) 中长度 N 指的是字符串的长度, 一个数字 / 英文字母 / 汉字占用一个字符，指定 N 可以存储 N 个字符。
注意两种存储类型支持的字段长度计算方式的不同，会给开发带来一定的困扰，使用 varbinary 的开发需要深入了解该字段的存储单位，设计字段的时候还要根据业务逻辑计算好 N 的值是多少。否则可能会被截断，在 sql_mode 为严格模式时则会报错。
2.4 索引功能
分别对 name 字段创建索引
test [RW] 10:47:01 alter table vbinary add name varbinary(255) not null default yz
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
test [RW] 10:47:24 alter table vbinary add key idx_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
test [RW] 10:48:25 rename table vchar to vachar;
Query OK, 0 rows affected (0.01 sec)
test [RW] 10:49:00 alter table vachar add name varchar(255) not null default yz
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
test [RW] 10:49:31 alter table vachar add key idx_name(name);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
test [RW] 10:49:53 show Warnings;
+———+——+———————————————————+
| Level | Code | Message |
+———+——+———————————————————+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+———+——+———————————————————+
1 row in set (0.00 sec)
test [RW] 10:50:06 show create table vachar \G
*************************** 1. row ***************************
Table: vachar
Create Table: CREATE TABLE `vachar` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(12) NOT NULL DEFAULT ,
`name` varchar(255) NOT NULL DEFAULT yz ,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
test [RW] 10:50:19 show create table vbinary \G
*************************** 1. row ***************************
Table: vbinary
Create Table: CREATE TABLE `vbinary` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varbinary(12) NOT NULL DEFAULT ,
`name` varbinary(255) NOT NULL DEFAULT yz ,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
test [RW] 11:53:08 create table vbinary1
– (
– id int primary key auto_increment ,
– val varbinary(776) not null default
– ) engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
test [RW] 11:53:09 alter table vbinary1 add key idx_val(val);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
test [RW] 11:53:37 show Warnings;
+———+——+———————————————————+
| Level | Code | Message |
+———+——+———————————————————+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+———+——+———————————————————+
1 row in set (0.00 sec)
test [RW] 11:53:44 show create table vbinary1 \G
*************************** 1. row ***************************
Table: vbinary1
Create Table: CREATE TABLE `vbinary1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varbinary(776) NOT NULL DEFAULT ,
PRIMARY KEY (`id`),
KEY `idx_val` (`val`(767)) ## 被修改为 767，索引支持的最大字节数。
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
分析:
基于 varbinary 和 varchar 存储字符的长度定义不同，varchar 可以存储字符串前 191 个字符的索引，varbinary 字段的索引则最多可以存储 767 字节。如果是英文字母则可以存储更长的字符串。
2.5 校验方面
test [RW] 12:15:06 select * from vachar where val= ABAA
+—-+——+——+
| id | val | name |
+—-+——+——+
| 1 | abaa | yz |
+—-+——+——+
1 row in set (0.00 sec)
test [RW] 12:14:31 select * from vbinary where val= ABAA
Empty set (0.00 sec)
test [RW] 12:15:11 select * from vbinary where val= abaa
+—-+——+——+
| id | val | name |
+—-+——+——+
| 1 | abaa | yz |
+—-+——+——+
1 row in set (0.00 sec)
分析:
varbinary 存储的是二进制字节串而不是字符串，这意味着它没有字符集校验的概念，排序和比较都是基于字节中的实际数值大小进行的。varchar 类型存储的列在比较的时候是通过字符集的方式进行的,varchar 中 ABAA 和 abaa 是一致的.
2.6 性能测试
使用 mysqlslap 进行 10 个并发 100w 次查询做对比
[root@rac4 00:31:35 ~]
# time mysqlslap –no-defaults -uroot –create-schema=test -S /srv/my3306/run/mysql.sock –number-of-queries=1000000 –concurrency=10 –query= select * from vbinary where val= abaa
Benchmark
Average number of seconds to run all queries: 30.569 seconds
Minimum number of seconds to run all queries: 30.569 seconds
Maximum number of seconds to run all queries: 30.569 seconds
Number of clients running queries: 10
Average number of queries per client: 100000
real 0m30.574s
user 0m8.124s
sys 0m6.286s
[root@rac4 00:32:18 ~]
# time mysqlslap –no-defaults -uroot –create-schema=test -S /srv/my3306/run/mysql.sock –number-of-queries=1000000 –concurrency=10 –query= select * from vachar where val= abaa
Benchmark
Average number of seconds to run all queries: 31.986 seconds
Minimum number of seconds to run all queries: 31.986 seconds
Maximum number of seconds to run all queries: 31.986 seconds
Number of clients running queries: 10
Average number of queries per client: 100000
real 0m31.991s
user 0m8.351s
sys 0m6.407s
分析
简单的 select 查询对比来看
varbinary 30.569s
varchar 31.986s
varbinary 相对性能有 1.4s 约 4% 的性能提升，在压测环境下每秒几乎 3wqps，如果是普通的业务场景 1000-2000 左右的 qps，varbinary 带来的性能可以忽略不计.
到此，相信大家对“MySQL 中使用 varbinary 比 varchar 更合适吗”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
