共计 7473 个字符,预计需要花费 19 分钟才能阅读完成。
一 前言
在讨论数据表字段设计的时候,有同学提出使用 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 带来的性能可以忽略不计.
三 总结
本文从存储长度单位,索引,查询条件校验,性能方面做了测试, 其优点是 无需考虑字符集,比较的时候安装字节比较理论上比字符要快 (测试结果的确会快 4% 左右,但不明显), 考虑实际应用的时候 varbinary 存储单位的改变给开发带来更多的迷惑性,尤其是使用 varbinary 存储汉字时, 开发需要更多的考虑具体设计多长才能满足业务需求,存在被截断的风险。从结果上来看并没有特别好的理由让我们选择 varbinary。
推荐文章《官方文档》
本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-05/143384.htm