MySQL中利用varbinary比varchar更合适
|
本篇内容主要讲解“MySQL中使用varbinary比varchar更合适吗”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“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带来的性能可以忽略不计. （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
