MySQL中使用varbinary比varchar更合適嗎

148次閱讀

共計 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 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！

正文完