备案控制台
探索云世界
开发者社区 数据库 文章 正文

【MySQL】varbinary 真的比varchar 更合适？

2017-03-11 2547
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： 一 前言     在讨论数据表字段设计的时候，有同学提出使用vabinary 代替 varchar ,部分开发不明所以,其实我也是。两者之间具体有什么区别？使用vabinary 代替 varchar 对业务有何优势？本文尝试从性能,数据大小,查询,创建索引等对比功能等方面进行研究，有不妥或者不到位之处还请各位读者朋友提示。
一 前言
    在讨论数据表字段设计的时候，有同学提出使用vabinary 代替 varchar ,部分开发不明所以,其实我也是。两者之间具体有什么区别？使用vabinary 代替 varchar 对业务有何优势？本文尝试从性能,数据大小,查询,创建索引等对比功能等方面进行研究，有不妥或者不到位之处还请各位读者朋友提示。
二 对比测试
2.1 测试环境
 数据库版本 Percona Server 5.6.24-72.2-log
  1. create table vbinary
  2. (
  3.   id int primary key auto_increment ,
  4.   val varbinary(776) not null default ''
  5. ) engine=innodb default charset=utf8mb4;

  7. create table vachar
  8. (
  9.   id int primary key auto_increment ,
  10.   val varchar(12) not null default ''
  11. ) engine=innodb default charset=utf8mb4;
  12. insert into vbinary(val) values('abaa'),('aabb'),('bcdd'),('ccdd');
  13. insert into vachar(val) values('abaa'),('aabb'),('bcdd'),('ccdd')
2.2 定义
   varchar(N)   字符串类型，用于存储变长字符串，使用表默认或者指定的校验集合，其中N代表存储字符的个数，详细信息请移步 《浅谈varchar(N)》.
   varbinary(N)二进制字符串类型，以 二进制字节串存储字符,无字符集校验区别，均以二进制实际数值作比较。
2.3 长度定义
varchar存储的是字符个数，varbinary存储的是字节个数。
  1. test [RW] 10:57:50 >insert into vbinary (val,name) value('2msdmlsdyo2enwlenw','disodmalsdsi');
  2. Query OK, 1 row affected, 1 warning (0.00 sec)
  3. test [RW] 10:57:55 >show warnings;
  4. +---------+------+------------------------------------------+
  5. | Level | Code | Message |
  6. +---------+------+------------------------------------------+
  7. | Warning | 1265 | Data truncated for column 'val' at row 1 |
  8. +---------+------+------------------------------------------+
  9. 1 row in set (0.00 sec)
  10. test [RW] 10:58:11 >insert into vbinary (val,name) value('有赞是一家移动零售服务提供商','disodmalsdsi');
  11. Query OK, 1 row affected, 1 warning (0.01 sec)
  12. test [RW] 10:59:00 >show warnings;
  13. +---------+------+------------------------------------------+
  14. | Level | Code | Message |
  15. +---------+------+------------------------------------------+
  16. | Warning | 1265 | Data truncated for column 'val' at row 1 |
  17. +---------+------+------------------------------------------+
  18. 1 row in set (0.00 sec)
  19. test [RW] 10:59:08 >select * from vbinary;
  20. +----+--------------+--------------+
  21. | id | val | name |
  22. +----+--------------+--------------+
  23. | 6 | 2msdmlsdyo2e | disodmalsdsi |
  24. | 7 | 有赞是一 | disodmalsdsi | #
  25. +----+--------------+--------------+
  26. 7 rows in set (0.00 sec)
  27. test [RW] 10:59:12 >insert into vachar(val,name) value('有赞是一家移动零售服务提供商','disodmalsdsi');
  28. Query OK, 1 row affected, 1 warning (0.00 sec)
  29. test [RW] 11:00:02 >show warnings;
  30. +---------+------+------------------------------------------+
  31. | Level | Code | Message |
  32. +---------+------+------------------------------------------+
  33. | Warning | 1265 | Data truncated for column 'val' at row 1 |
  34. +---------+------+------------------------------------------+
  35. 1 row in set (0.00 sec)
  36. test [RW] 11:00:06 >select * from vachar;
  37. +----+--------------------------------------+--------------+
  38. | id | val | name |
  39. +----+--------------------------------------+--------------+
  40. | 4 | ccdd | yz |
  41. | 5 | 有赞是一家移动零售服务提 | disodmalsdsi |
  42. +----+--------------------------------------+--------------+
  43. 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字段创建索引
  1. test [RW] 10:47:01 >alter table vbinary add name varbinary(255) not null default 'yz';
  2. Query OK, 0 rows affected (0.02 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0
  4. test [RW] 10:47:24 >alter table vbinary add key idx_name(name);
  5. Query OK, 0 rows affected (0.01 sec)
  6. Records: 0 Duplicates: 0 Warnings: 0
  7. test [RW] 10:48:25 >rename table vchar to vachar;
  8. Query OK, 0 rows affected (0.01 sec)
  9. test [RW] 10:49:00 >alter table vachar add name varchar(255) not null default 'yz';
  10. Query OK, 0 rows affected (0.02 sec)
  11. Records: 0 Duplicates: 0 Warnings: 0
  12. test [RW] 10:49:31 >alter table vachar add key idx_name(name);
  13. Query OK, 0 rows affected, 1 warning (0.02 sec)
  14. Records: 0 Duplicates: 0 Warnings: 1
  15. test [RW] 10:49:53 >show Warnings;
  16. +---------+------+---------------------------------------------------------+
  17. | Level | Code | Message |
  18. +---------+------+---------------------------------------------------------+
  19. | Warning | 1071 | Specified key was too long; max key length is 767 bytes |
  20. +---------+------+---------------------------------------------------------+
  21. 1 row in set (0.00 sec)
  22. test [RW] 10:50:06 >show create table vachar \G
  23. *************************** 1. row ***************************
  24.        Table: vachar
  25. Create Table: CREATE TABLE `vachar` (
  26.   `id` int(11) NOT NULL AUTO_INCREMENT,
  27.   `val` varchar(12) NOT NULL DEFAULT '',
  28.   `name` varchar(255) NOT NULL DEFAULT 'yz',
  29.   PRIMARY KEY (`id`),
  30.   KEY `idx_name` (`name`(191))
  31. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
  32. 1 row in set (0.00 sec)
  33. test [RW] 10:50:19 >show create table vbinary \G
  34. *************************** 1. row ***************************
  35.        Table: vbinary
  36. Create Table: CREATE TABLE `vbinary` (
  37.   `id` int(11) NOT NULL AUTO_INCREMENT,
  38.   `val` varbinary(12) NOT NULL DEFAULT '',
  39.   `name` varbinary(255) NOT NULL DEFAULT 'yz',
  40.   PRIMARY KEY (`id`),
  41.   KEY `idx_name` (`name`)
  42. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
  43. 1 row in set (0.00 sec)
  44. test [RW] 11:53:08 >create table vbinary1
  45.     -> (
  46.     -> id int primary key auto_increment ,
  47.     -> val varbinary(776) not null default ''
  48.     -> ) engine=innodb default charset=utf8mb4;
  49. Query OK, 0 rows affected (0.01 sec)
  50. test [RW] 11:53:09 >alter table vbinary1 add key idx_val(val);
  51. Query OK, 0 rows affected, 1 warning (0.02 sec)
  52. Records: 0 Duplicates: 0 Warnings: 1
  53. test [RW] 11:53:37 >show Warnings;
  54. +---------+------+---------------------------------------------------------+
  55. | Level | Code | Message |
  56. +---------+------+---------------------------------------------------------+
  57. | Warning | 1071 | Specified key was too long; max key length is 767 bytes |
  58. +---------+------+---------------------------------------------------------+
  59. 1 row in set (0.00 sec)
  60. test [RW] 11:53:44 >show create table vbinary1 \G
  61. *************************** 1. row ***************************
  62.        Table: vbinary1
  63. Create Table: CREATE TABLE `vbinary1` (
  64.   `id` int(11) NOT NULL AUTO_INCREMENT,
  65.   `val` varbinary(776) NOT NULL DEFAULT '',
  66.   PRIMARY KEY (`id`),
  67.   KEY `idx_val` (`val`(767)) ## 被修改为 767 ，索引支持的最大字节数。
  68. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  69. 1 row in set (0.00 sec)
分析:
  基于 varbinary和 varchar 存储字符的长度定义不同，varchar 可以存储字符串前191个字符的索引，varbinary 字段的索引则最多可以存储767字节。如果是英文字母则可以存储更长的字符串。
2.5 校验方面
  1. test [RW] 12:15:06 >select * from vachar where val='ABAA';
  2. +----+------+------+
  3. | id | val | name |
  4. +----+------+------+
  5. | 1 | abaa | yz |
  6. +----+------+------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 12:14:31 >select * from vbinary where val='ABAA';
  9. Empty set (0.00 sec)
  10. test [RW] 12:15:11 >select * from vbinary where val='abaa';
  11. +----+------+------+
  12. | id | val | name |
  13. +----+------+------+
  14. | 1 | abaa | yz |
  15. +----+------+------+
  16. 1 row in set (0.00 sec)
分析:
varbinary存储的是二进制字节串而不是字符串，这意味着它没有字符集校验的概念，排序和比较都是基于字节中的实际数值大小进行的。varchar类型存储的列在比较的时候是通过字符集的方式进行的,varchar 中'ABAA'和'abaa'是一致的.
2.6 性能测试
使用mysqlslap 进行10个并发100w次查询做对比
  1. [root@rac4 00:31:35 ~]
  2. # 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';"
  3. Benchmark
  4.   Average number of seconds to run all queries: 30.569 seconds
  5.   Minimum number of seconds to run all queries: 30.569 seconds
  6.   Maximum number of seconds to run all queries: 30.569 seconds
  7.   Number of clients running queries: 10
  8.   Average number of queries per client: 100000
  9. real 0m30.574s
  10. user 0m8.124s
  11. sys 0m6.286s
  12. [root@rac4 00:32:18 ~]
  13. # 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';"
  14. Benchmark
  15.   Average number of seconds to run all queries: 31.986 seconds
  16.   Minimum number of seconds to run all queries: 31.986 seconds
  17.   Maximum number of seconds to run all queries: 31.986 seconds
  18.   Number of clients running queries: 10
  19.   Average number of queries per client: 100000
  20. real 0m31.991s
  21. user 0m8.351s
  22. sys 0m6.407s
分析
简单的select查询对比来看
varbinary  30.569s
varchar    31.986s
varbinary 相对性能有 1.4s 约4%的性能提升，在压测环境下每秒几乎3wqps，如果是普通的业务场景1000-2000左右的qps，varbinary带来的性能可以忽略不计.
三 总结
    本文从存储长度单位，索引，查询条件校验，性能方面做了测试， 其优点是 无需考虑字符集，比较的时候安装字节比较理论上比字符要快(测试结果的确会快4% 左右，但不明显)， 考虑实际应用的时候 varbinary 存储单位的改变给开发带来更多的迷惑性，尤其是使用 varbinary 存储汉字时,开发需要更多的考虑具体设计多长才能满足业务需求，存在被截断的风险。 从结果上来看并没有特别好的理由让我们选择varbinary 。
推荐文章《 官方文档》

文章标签：
云数据库 RDS MySQL 版
存储
关系型数据库
索引
MySQL
测试技术
关键词：
云数据库 RDS MySQL 版varchar
云数据库 RDS MySQL 版varbinary
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
yangyi_dba
目录
相关文章
砖业洋__
|
1月前
|
存储 缓存 关系型数据库
MySQL的varchar水真的太深了——InnoDB记录存储结构
varchar(M) 能存多少个字符，为什么提示最大16383？innodb怎么知道varchar真正有多长？记录为NULL，innodb如何处理？某个列数据占用的字节数非常多怎么办？影响每行实际可用空间的因素有哪些？本篇围绕innodb默认行格式dynamic来说说原理。
砖业洋__
828 6
MySQL的varchar水真的太深了——InnoDB记录存储结构
Meteor.792
|
9月前
|
存储 关系型数据库 MySQL
MySQL中varchar的最大长度是多少
MySQL中varchar的最大长度是多少
Meteor.792
170 0
念广隶
|
10天前
|
存储 关系型数据库 MySQL
深入理解MySQL中varchar和text的区别
在MySQL中，varchar和text都是用于存储文本数据的数据类型。varchar是可变长度字符串，存储时按实际长度分配空间，适合存储较短的、长度可变的字符串，如用户名。text类型用于存储大量文本，始终占用足够空间，适合文章内容。varchar在存储和查询时可能更快，可被索引，而text需特殊搜索技术。在数据库设计时，应根据存储需求和性能平衡选择。
念广隶
40 0
蒾酒
|
1月前
|
存储 机器学习/深度学习 关系型数据库
mysql中char和varchar的区别
mysql中char和varchar的区别
蒾酒
28 1
飞翔的佩奇
|
3月前
|
关系型数据库 MySQL 数据库
mysql数据库 字段类型 varchar 值为数字，取最大值时出现的问题
mysql数据库 字段类型 varchar 值为数字，取最大值时出现的问题
飞翔的佩奇
45 0
墨三十一
|
4月前
|
存储 关系型数据库 MySQL
MySQL之varchar和char的区别
MySQL之varchar和char的区别
墨三十一
17 0
瑞崽崽崽
|
4月前
|
存储 关系型数据库 MySQL
知识笔记（五十四）———mysql比较varchar值大小_Mysql varchar大小长度问题
知识笔记（五十四）———mysql比较varchar值大小_Mysql varchar大小长度问题
瑞崽崽崽
39 0
Meteor.792
|
9月前
|
关系型数据库 MySQL
MySQL中varchar能存多少汉字、数字
MySQL中varchar能存多少汉字、数字
Meteor.792
183 0
终有救赎
|
4月前
|
存储 关系型数据库 MySQL
MySQL 总结char与varchar的区别
MySQL 总结char与varchar的区别
终有救赎
28 0
阿甘兄
|
5月前
|
JSON 关系型数据库 MySQL
MySQL如何确定VARCHAR大小
MySQL如何确定VARCHAR大小
阿甘兄
38 0

热门文章

最新文章

  • 1
    超1/3中国500强企业都在用的「汇联易」，为什么选用阿里云RDS？
  • 2
    详解MySQL字符集和Collation
  • 3
    RDS MySQL 数据库运维简述
  • 4
    阿里云mysql数据库价格购买和使用教程
  • 5
    阿里云MySQL云数据库优惠价格、购买和使用教程分享！
  • 6
    阿里云MySQL数据库价格、购买、创建账号密码和连接数据库教程
  • 7
    使用阿里云的数据传输服务DTS（Data Transmission Service）进行MySQL 5.6到MySQL 8.0的迁移
  • 8
    Docker安装详细步骤及相关环境安装配置（mysql、jdk、redis、自己的私有仓库Gitlab 、C和C++环境以及Nginx服务代理）
  • 9
    购买阿里云RDS实例
  • 10
    MySQL学习（三）
  • 1
    [MySQL]事务原理之redo log,undo log
    41
  • 2
    [MySQL]SQL优化之索引的使用规则
    35
  • 3
    [MySQL] SQL优化之性能分析
    28
  • 4
    【mysql】—— 用户管理
    31
  • 5
    【mysql】—— 视图
    26
  • 6
    【mysql】—— 函数的基本介绍
    25
  • 7
    【mysql】—— 表的增删改查
    40
  • 8
    【mysql】—— 表的约束
    29
  • 9
    【MYSQL】—— MySQL 在 Centos 7环境安装
    43
  • 10
    MySQL模糊查询二三事
    24

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云oss简介和使用流程
    为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
    产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
    解决方案技术解决方案
    文档与社区文档开发者社区天池大赛培训与认证
    权益中心免费试用高校计划企业扶持计划推荐返现计划
    支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
    关注阿里云
    关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
    阿里云APP阿里云微信
    联系我们：4008013260
    法律声明Cookies政策廉正举报安全举报联系我们加入我们
    阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
    © 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
    浙公网安备 33010602009975号浙B2-20080101-4