你好，我是李輝。
這是專(zhuān)欄的第一篇文章，在專(zhuān)欄的前幾個(gè)小節(jié)，我會(huì)先和你先聊一聊 MySQL 的數(shù)據(jù)類(lèi)型。了解 MySQL 的數(shù)據(jù)類(lèi)型是開(kāi)發(fā)人員在使用 MySQL 數(shù)據(jù)庫(kù)的時(shí)候，必備的基礎(chǔ)技能之一。也正因?yàn)榇耍@部分知識(shí)也是面試官面試的時(shí)候?qū)覍姨峒暗母哳l問(wèn)題，所以盡量不要在這個(gè)地方栽跟頭。這一小節(jié)我們就先來(lái)說(shuō)說(shuō)在數(shù)據(jù)建模設(shè)計(jì)時(shí)使用非常頻繁的字符串類(lèi)型 – VARCHAR。
為了在閱讀時(shí)不引起歧義，本專(zhuān)欄中我們先做一個(gè)簡(jiǎn)單的約定：
- MySQL 數(shù)據(jù)庫(kù)版本 5.7
- 使用 InnoDB 存儲(chǔ)引擎
- 默認(rèn)隔離級(jí)別是 Repeatable Read
- 默認(rèn)使用 UTF8 編碼
本專(zhuān)欄中的所有文章都使用上述約定，有不同的地方我們?cè)賳为?dú)做說(shuō)明。
1. VARCHAR (50) 中的 50 到底是能存 50 個(gè)字還是 50 個(gè)字節(jié)？
我們先做個(gè)簡(jiǎn)單的實(shí)驗(yàn)：
mysql> create database imooc_mysql_interview;
Query OK, 1 row affected (0.00 sec)
mysql> use imooc_mysql_interview
Database changed
mysql> create table varchar_test(col_1 varchar(8));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into varchar_test values('ABCDEFGH'),('數(shù)一數(shù)是不是八個(gè)');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from varchar_test;
+--------------------------+
| col_1 |
+--------------------------+
| ABCDEFGH |
| 數(shù)一數(shù)是不是八個(gè) |
+--------------------------+
2 rows in set (0.00 sec)
從上述實(shí)驗(yàn)的結(jié)果可知，顯然是能存 8 個(gè)字符而不是 8 個(gè)字節(jié)，也就是說(shuō) VARCHAR 的括號(hào)中的數(shù)字代表的是字符。如果存的是字節(jié)，由于中文、英文和 emoji 在 utf-8 中的字節(jié)數(shù)都不一樣，勢(shì)必會(huì)給編程造成一定的困擾。
1.1 CHAR (50) 和 VARCHAR (50) 有什么區(qū)別？
剛工作不久的同學(xué)可能會(huì)有這個(gè)疑問(wèn)，為什么大家都喜歡用 VARCHAR，CHAR 卻很少見(jiàn)，存的長(zhǎng)度不是都一樣嗎？
首先要說(shuō)明的一點(diǎn)，CHAR 和 VARCHAR 在存儲(chǔ)方式上存在著差異：CHAR 是定長(zhǎng)字符，MySQL 數(shù)據(jù)庫(kù)會(huì)根據(jù)建表時(shí)定義的長(zhǎng)度給它分配相應(yīng)的存儲(chǔ)空間。而 VARCHAR 是可變長(zhǎng)度字符的數(shù)據(jù)類(lèi)型，在存儲(chǔ)時(shí)只使用必要的空間。
舉個(gè)例子，假如一張表上有兩列，分別是 CHAR (20) 和 VARCHAR (20)，我們插入一個(gè)字符串 “abcd”，在數(shù)據(jù)庫(kù)中存儲(chǔ)時(shí)，CHAR 會(huì)使用全部的 20 個(gè)字符的長(zhǎng)度，不足的部分用空格填充，而 VARCHAR 僅僅就只使用 4 個(gè)字符的長(zhǎng)度。
其次，由于 CHAR 數(shù)據(jù)類(lèi)型的這個(gè)特性，在將數(shù)據(jù)寫(xiě)入表中時(shí)，如果字符串尾部存在空格，會(huì)被自動(dòng)刪除，而 VARCHAR 數(shù)據(jù)類(lèi)型會(huì)保留這個(gè)空格。在一些特殊場(chǎng)景中要注意這個(gè)問(wèn)題。所以推薦你使用 CHAR 數(shù)據(jù)類(lèi)型存儲(chǔ)一些固定長(zhǎng)度的字符串，比如身份證號(hào)、手機(jī)號(hào)、性別等。
最后，CHAR 和 VARCHAR 的存儲(chǔ)長(zhǎng)度不同。CHAR 數(shù)據(jù)類(lèi)型可定義的最大長(zhǎng)度是 255 個(gè)字符，而 VARCHAR 根據(jù)所使用的字符集不同，最大可以使用 65535 個(gè)字節(jié)。注意我剛說(shuō)的 VARCHAR 的最大長(zhǎng)度不是字符數(shù)而是字節(jié)數(shù)，那么新的問(wèn)題來(lái)了，我們接著往下看。
2. VARCHAR 能使用的最大長(zhǎng)度是多少？
由于 VARCHAR 能存儲(chǔ)的最大長(zhǎng)度會(huì)因?yàn)槟阍诒矶x中使用的字符集不同而發(fā)生變化，下面我們就以業(yè)內(nèi)使用較多的 UTF8 這個(gè)字符集作為前提條件來(lái)做個(gè)分析。
我們?cè)倏匆粋€(gè)例子：
mysql> create table varchar_test2(col_1 varchar(65535))charset=utf8 engine=innodb;
ERROR 1074 (42000): Column length too big for column 'col_1' (max = 21845); use BLOB or TEXT instead
mysql> create table varchar_test2(col_1 varchar(21845))charset=utf8 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table varchar_test2(col_1 varchar(21844))charset=utf8 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
由于 UTF8 字符集中一個(gè)漢字占用 3 個(gè)字節(jié)，因此我們能創(chuàng)建的最大長(zhǎng)度理論上應(yīng)該是 21845（65535/3=21845）。但是為什么 varchar (21845) 仍然報(bào)錯(cuò)，而使用 varchar (21844) 卻創(chuàng)建成功？
這是因?yàn)橛|及了 MySQL 數(shù)據(jù)庫(kù)定義的 VARCHAR 的最大行長(zhǎng)度限制。
雖然 MySQL 官方定義了最大行長(zhǎng)度是 65535 個(gè)字節(jié)，但是因?yàn)檫€有別的開(kāi)銷(xiāo)，我們能使用的最大行長(zhǎng)度只有 65532。
剛剛的實(shí)驗(yàn)中，我們把 VARCHAR 的字段長(zhǎng)度改成 21844 后騰出來(lái) 3 個(gè)字節(jié) (65535-21844*3=3)，因此可以創(chuàng)建成功。
因此在使用了 UTF-8 的字符集時(shí)，VARCHAR 的最大長(zhǎng)度為 21844。
另外提醒你注意一下，做表設(shè)計(jì)時(shí)不要肆意的放飛自我，在單表上設(shè)計(jì)出一堆較大的 VARCHAR 字段，在失去了擴(kuò)展性時(shí)以后可能會(huì)哭。因?yàn)?MySQL 的最大行長(zhǎng)度限制不只是 1 個(gè) VARCHAR 列，而是所有列的長(zhǎng)度總和。
我們?cè)僮鰝€(gè)實(shí)驗(yàn)觀察一下：
mysql> create table varchar_test3(id int auto_increment, col_2 varchar(21844), primary key(id))charset=utf8 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table varchar_test3(id int auto_increment, col_2 varchar(21843), primary key(id))charset=utf8 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table varchar_test3(id int auto_increment, col_2 varchar(21842), primary key(id))charset=utf8 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create table varchar_test4(id int auto_increment, col_2 varchar(21842), col_3 smallint, primary key(id))charset=utf8 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
你可以自行計(jì)算一下，在上面的這個(gè)實(shí)驗(yàn)中，所有列的字節(jié)數(shù)加起來(lái)也是不能超過(guò) 65532 的，超出時(shí)會(huì)報(bào)錯(cuò)。
你可能還有一個(gè)疑惑，在 UTF8 中英文字符不是只占用 1 個(gè)字節(jié)嗎，那 varchar (21844) 使用了 65532 個(gè)字節(jié)，如果我只存英文字符的話是不是就能存 65532 個(gè)？
然而并非如此，VARCHAR (M) 中的 M 仍然表示的是 M 個(gè)字符，而不是 M 個(gè)字節(jié)。
只不過(guò)它在存儲(chǔ)的時(shí)候仍然是按實(shí)際字節(jié)數(shù)來(lái)存的。所以在 UTF8 的字符編碼下，我們能使用的最大長(zhǎng)度就只有 21844 個(gè) VARCHAR 字符。
如果要存儲(chǔ)更多的字符該怎么辦呢？使用 TEXT、BLOB 這樣的大對(duì)象列類(lèi)型。因?yàn)檫@些大對(duì)象可以把數(shù)據(jù)存放到溢出頁(yè)面上，也就是 DBA 們常說(shuō)的行溢出。
3. VARCHAR 數(shù)據(jù)類(lèi)型優(yōu)化
下面我們?cè)倭囊涣?VARCHAR 的性能優(yōu)化相關(guān)的一些事情。
3.1 只分配所需要用的 VARCHAR 空間
雖然使用 VARCHAR (50) 和 VARCHAR (1000) 存儲(chǔ)‘a(chǎn)bcd’的存儲(chǔ)空間開(kāi)銷(xiāo)是一樣的，但是當(dāng)你在讀取數(shù)據(jù)時(shí)，把這些數(shù)據(jù)讀取到內(nèi)存的過(guò)程中，MySQL 數(shù)據(jù)庫(kù)需要分配相應(yīng)大小的內(nèi)存空間來(lái)存放數(shù)據(jù)。
所以更大的 VARCHAR 列在讀取時(shí)要使用更大的內(nèi)存空間，即使它實(shí)際上只存儲(chǔ)了一丁點(diǎn)數(shù)據(jù)。
并且在操作這個(gè)表的過(guò)程中，如果遇到一些聚合（GROUP BY）或排序（ORDER BY）的操作，需要調(diào)用內(nèi)存臨時(shí)表或磁盤(pán)臨時(shí)表時(shí)，性能會(huì)更加糟糕。
因此，在保留一定冗余的前提下，只給 VARCHAR 分配恰到好處的空間使用。
3.2 VARCHAR 的字段過(guò)長(zhǎng)也會(huì)導(dǎo)致行溢出
剛剛你不是說(shuō)了 TEXT 和 BLOB 會(huì)溢出嗎，VARCHAR 也會(huì)溢出？
是的。你在給 MySQL 的數(shù)據(jù)表加索引時(shí)，可能遇到過(guò)要在大的 VARCHAR 字段上創(chuàng)建索引卻發(fā)現(xiàn)只能創(chuàng)建前綴索引的問(wèn)題。那這個(gè)其實(shí)是和行溢出有關(guān)。
mysql> create index idx_col_2 on varchar_test4(col_2);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create index idx_col_2 on varchar_test4(col_2(3072));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create index idx_col_2 on varchar_test4(col_2(1024));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index idx_col_2 on varchar_test4;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index idx_col_2 on varchar_test4(col_2(1025));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
上面實(shí)驗(yàn)可以看到，最大的索引長(zhǎng)度不能超過(guò) 3072 字節(jié)（在 UTF-8 的字符集中對(duì)應(yīng)的是 1024 個(gè)字符）。
由于行溢出是另外一個(gè)話題，我們今天就不過(guò)多贅述，我們只說(shuō)說(shuō)大字段和行溢出造成的性能問(wèn)題。
- 大字段會(huì)占用較大的內(nèi)存，使得 MySQL 內(nèi)存利用率較差
- 行溢出的數(shù)據(jù)在讀取時(shí)需要多一個(gè) IO，造成 IO 效率下降
- 行溢出會(huì)使用 uncompress BLOB page，造成 InnoDB 的表空間越來(lái)越大
- InnoDB 中的大字段在做更新和刪除操作時(shí)，只能進(jìn)行悲觀操作，這會(huì)造成并發(fā)性能下降。
另外，因?yàn)?InnoDB 的數(shù)據(jù)頁(yè)默認(rèn)是 16K，每個(gè)頁(yè)中至少存放 2 行數(shù)據(jù)，因此建議 VARCHAR 字段的總長(zhǎng)度不要超過(guò) 8K。
4. 小結(jié)
今天我給你介紹了 MySQL 的 VARCHAR 數(shù)據(jù)類(lèi)型，希望你對(duì) VARCHAR 數(shù)據(jù)類(lèi)型能有更多的了解。
由于篇幅的限制，諸如行溢出這些知識(shí)點(diǎn)就不再做深入的探討。
不過(guò)對(duì)于開(kāi)發(fā)人員來(lái)說(shuō)，了解到這里也就基本足夠了。如果你對(duì)沒(méi)有展開(kāi)的那些知識(shí)點(diǎn)感興趣，可以自行搜索研究一番。
最后留一個(gè)問(wèn)題吧，使用 UTF8 字符集時(shí)我們最大可以存儲(chǔ) 21844 個(gè)字符，那么如果是 UTF8MB4 呢？
感謝你的閱讀，歡迎你給我留言，也歡迎你分享給更多的朋友一起閱讀。