宜宾网站建设公司

MySQL中使用varbinary比varchar更合适吗

本篇内容主要讲解“MySQL中使用varbinary比varchar更合适吗”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL中使用varbinary比varchar更合适吗”吧!

平定网站建设公司创新互联公司,平定网站设计制作，有大型网站制作公司丰富经验。已为平定1000+提供企业网站建设服务。企业网站搭建\成都外贸网站建设公司要多少钱，请找那个售后服务好的平定做网站的公司定做！

一 前言
    在讨论数据表字段设计的时候，有同学提出使用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带来的性能可以忽略不计.

到此，相信大家对“MySQL中使用varbinary比varchar更合适吗”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！


分享题目：MySQL中使用varbinary比varchar更合适吗
本文URL：http://hbruida.cn/article/gggsic.html

其他资讯