Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "is it more appropriate to use varbinary than varchar in MySQL?" interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "is it more appropriate to use varbinary than varchar in MySQL?"

A preface

When discussing the design of datasheet fields, some students proposed to use vabinary instead of varchar, but some of the development is unknown, so, in fact, so am I. What is the specific difference between the two? What are the business advantages of using vabinary instead of varchar?

Two contrast test

2.1 Test environment

Database version Percona Server 5.6.24-72.2-log

Create table vbinary

(

Id int primary key auto_increment

Val varbinary 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 definition

Varchar (N) string type, used to store variable-length strings, using table defaults or specified check sets, where N represents the number of stored characters, please go to "talk about varchar (N)" for more information.

The varbinary (N) binary string type, which stores characters in binary byte strings, has no character set check distinction, and is compared with binary actual values.

2.3 length definition

Varchar stores the number of characters and varbinary stores the number of bytes.

Test [RW] 10:57:50 > insert into vbinary (val,name) value ('2msdmlsdyo2enwlenwilled Magna 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 ('Youzai is a mobile retail service provider', '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 | one with a like | disodmalsdsi | #

+-- +

7 rows in set (0.00 sec)

Test [RW] 10:59:12 > insert into vachar (val,name) value ('Youzai is a mobile retail service provider', '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 | Youzun is a mobile retail service | disodmalsdsi |

+-+-- +-+

5 rows in set (0.00 sec)

Analysis:

The length N in varbinary (N) refers to the length of the byte string, a number / English letter occupies 1 byte, and a Chinese character occupies 3 bytes (default utf8, utf8mb4 character set). If you specify N, you can store N numbers or letters, Nmax 3 Chinese characters.

The length N in varchar (N) refers to the length of a string, a number / alphabet / Chinese character occupies one character, and N is specified to store N characters.

Note that the different calculation methods of field length supported by the two storage types will bring some trouble to the development. The development using varbinary requires an in-depth understanding of the storage unit of the field, and the value of N should be calculated according to the business logic when designing the field. Otherwise, it may be truncated and an error will be reported when the sql_mode is in strict mode.

2.4 indexing function

Create indexes on the name field separately

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 767bytes |

+-- +

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 767bytes |

+-- +

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 `val` (`val` (767)) # # is modified to 767. the maximum number of bytes supported by the index.

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

Analysis:

Based on the different definitions of varbinary and varchar storage character length, varchar can store the index of the first 191 characters of a string, and the index of the varbinary field can store up to 767 bytes. If it is an English alphabet, you can store a longer string.

2.5 Calibration

Test [RW] 12:15:06 > select * from vachar whereval='ABAA'

+-- +

| | id | val | name | |

+-- +

| | 1 | abaa | yz |

+-- +

1 row in set (0.00 sec)

Test [RW] 12:14:31 > select * from vbinary whereval='ABAA'

Empty set (0.00 sec)

Test [RW] 12:15:11 > select * from vbinary whereval='abaa'

+-- +

| | id | val | name | |

+-- +

| | 1 | abaa | yz |

+-- +

1 row in set (0.00 sec)

Analysis:

Varbinary stores binary byte strings instead of strings, which means that it has no concept of character set checking, and sorting and comparisons are based on the actual numerical size in the bytes. The columns stored in the varchar type are compared through the character set, and the 'ABAA'' and 'abaa'' in varchar are consistent.

2.6 performance testing

Use mysqlslap to do 10 concurrent 100w queries for comparison

[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 whereval='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 whereval='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

Analysis.

A simple select query by comparison

Varbinary 30.569s

Varchar 31.986s

The relativity of varbinary can achieve a performance improvement of 1.4 s by about 4%. In a pressure test environment, the performance of almost 3wqps per second is negligible if it is an ordinary business scenario of about 1000-2000 qps,varbinary.

At this point, I believe you have a deeper understanding of "is it more appropriate to use varbinary than varchar in MySQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.