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

楊奇龍發表於2017-03-11
MySql
一 前言
    在討論資料表欄位設計的時候，有同學提出使用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。
推薦文章《官方文件》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2135146/，如需轉載，請註明出處，否則將追究法律責任。

相關文章