從一個慢查詢到MySQL字符集編碼

2020 年 4 月 13 日
筆記

從一個慢查詢到MySQL字符集編碼

從一個慢查詢到MySQL字符集編碼

1. 問題起源

最近在完成一個線上日誌修復工作的過程中遇到了一個意想不到的慢查詢。當時使用的SQL以及表結構其實都很簡單，而且在關鍵的欄位上也有索引，但是MySQL的執行計劃就是跑出來了Range checked for each record (index map: 0x1)。如下為問題中的表結構定義和執行計劃（刪減了其他欄位，留下了關鍵的部分）:

Create Table: CREATE TABLE `Order1` (
  `orderid` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `productid` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  KEY `productid` (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

Create Table: CREATE TABLE `Product` (
  `productid` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  KEY (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> explain select * from  Order1 left join Product using (productid ) ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | Order1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                           |
|  1 | SIMPLE      | Product | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 |   100.00 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

正常情況下我們希望MySQL使用到表中定義的索引productid，並且執行計劃應該是如下的情形：

mysql> explain select * from  Order1 left join Product using (productid ) ;
+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | Order1  | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                 |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | Product | NULL       | eq_ref | PRIMARY       | PRIMARY | 257     | Order1.productid |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

但是最後MySQL的執行過程就是沒有使用索引，甚至於強制索引force index 也沒有用：

mysql> explain select * from  Order1 left  join Product force index (productid) on Order1.productid = Product.productid ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | Order1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                           |
|  1 | SIMPLE      | Product | NULL       | ALL  | productid     | NULL | NULL    | NULL |    1 |   100.00 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

當時有些懵逼沒太明白是因為什麼。後面經過一頓GOOGLE之後，明白了其實是字符集排序規則導致的。觀察可以發現兩張表的字符集排序規則其實是不一樣的。Product默認字符集是latin1 ，在MySQL中默認字符集的排序規則是latin1_swedish_ci，而Order1表中的productid 的排序規則是latin1_bin。因此對於MySQL來說沒法使用索引進行join，只能使用Range checked for each record的方式來完成查詢。由此可見在SQL執行的過程中，字符集和字符集排序規則對於執行計劃也是相當重要的。而在MySQL資料庫的字符集設置相當靈活和複雜，因此經常容易導致各種問題（例如索引失效，亂碼，字符集轉換損失性能等），因此本文專門整理和介紹下MySQL字符集相關的內容，為DBA同學和開發同學在實際工作中提供一些參考資訊。

2. MySQL字符集和字符集排序規則

2.1 字符集相關概念

在談起資料庫的字符集之前，首先需要了解下字元，字符集和字元編碼的概念。相信很多人在一開始的時候也對這些概念比較混亂。（以下的概念說明摘自維基百科和百度百科）

字元

在電腦和電信領域中，字元（Character）是一個資訊單位。對使用字母系統或音節文字等自然語言，它大約對應為一個音位、類音位的單位或符號。簡單來講就是一個漢字、假名、韓文字……，或是一個英文、其他西方語言的字母[1]。

字符集

字符集（Character set）是多個字元的集合，字符集種類較多，每個字符集包含的字元個數不同，常見字符集名稱：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等[2]。同時字符集還為每個集合中的字元規定了一套編碼規則，將每個字元與一個編碼做映射。

字元編碼

字元編碼（英語：Character encoding）也稱字集碼，是把字符集中的字元編碼為指定集合中某一對象（例如：比特模式、自然數序列、8位組或者電脈衝），以便文本在電腦中存儲和通過通訊網路的傳遞[3]。

由上面的概念可以見得，字符集就是一組字元的抽象集合，也可以稱為字符集合，例如所有的漢字可以算成一個字符集，所有的英文字母也可以算成一個字符集。字符集只是邏輯上的概念，如何將字符集映射到電腦世界中具體的表現呢？就是靠字元編碼。在電腦中資訊是以一個個0和1表示的，因此字符集最後也是需要在電腦世界中表現成二進位的形式存儲。字符集編碼就給出了從邏輯上的字符集到二進位編碼的映射。例如我們經常在工作中聽到unicode 和utf-8，unicode就是一個字符集，而utf-8是unicode 字符集在電腦中具體的實現方式。換句話說，字符集規定了有多少的字元，每個字元的編碼是多少(例如ASCII碼錶中，01000011這個二進位對應的十進位是67，代表的是C)，而字符集編碼將字符集中規定的編碼轉換成最終的二進位格式(比如ASCII碼錶中C的編碼是67，可以使用單位元組0x43表示這種編碼方案表示，也可以使用多個位元組，例如0x0043來表示)。

ps:關於字符集相關的更多介紹可以參見如下的參考文獻：

[1].字符集與排序規則概念

[2].刨根究底字元編碼之一——關鍵術語解釋(上)

[3].字元編碼-教程(1)-概述與基本知識

[4].程式設計師必備：徹底弄懂常見的7種中文字元編碼

2.2 MySQL中的字符集和字符集排序規則

2.2.1 字符集和字符集排序基本概念

上一節簡單介紹了字符集相關的概念，本節開始講介紹MySQL的字符集以及字符集排序規則相關內容。一般而言MySQL中的字符集和前文介紹的字符集沒有任何的區別。包括了工作中常用的字符集例如Latin1、GBK、GB2312、BIG5、UTF8、UTF8MB4、UTF16、UTF32等等。通過命令SHOW CHARACTER SET，可以看到MySQL支援的所有字符集：

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

輸出的第一列是字符集名稱，第二列是字符集的描述，第三列是字符集默認的排序規則，第四列表示一個字符集的一個字元最大的位元組數。這在里著重介紹下字符集的排序規則。首先在MySQL的官網文檔中，排序規則(collations，下文都稱之為collation)的定義是：

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let’s make the distinction clear with an example of an imaginary character set.

翻譯過來就是collation就是一套規則，用來比較字符集中的字元。那麼怎麼理解這個比較字符集中的字元呢？文檔給出了一個例子，例如有一個簡單的字符集包括了如下的幾個字元：a b A B。為了能夠比較每個字元，我們給每個字元都附上一個編碼，比如a=0，b=1，A=2，B=3。那麼在排序比如order by 或者比較兩個字元是否相等的時候，自然可以使用這個編碼來進行，例如order by 的時候就應該是 a<b<A<B ，而比較大小的時候是a<A 。這個規則就算做是一個collation，也是最簡單的規則。但是如果今天不需要大小寫敏感，那麼a就是等於A ，所以這個規則就需要做下改變，例如a 和 A 的編碼都變成0 ，然後order by的時候a和A不分先後，這樣子的規則可以稱之為大小不敏感。再比如世界上有些語言例如德語，會有口音敏感或者不敏感之分(MySQL文檔上給出的原文是accent-sensitive ，我是直譯過來的，如果有更好的翻譯請大家告訴我)，那麼會可能存在Ö=OE這樣子的情況，因此會有collation是口音敏感或者不敏感[5]。

Suppose that we have an alphabet with four letters: A, B, a, b. We give each letter a number: A = 0, B = 1, a = 2, b = 3. The letter A is a symbol, the number 0 is the encoding for A, and the combination of all four letters and their encodings is a character set.

Suppose that we want to compare two string values, A and B. The simplest way to do this is to look at the encodings: 0 for A and 1 for B. Because 0 is less than 1, we say A is less than B. What we’ve just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): 「compare the encodings.」 We call this simplest of all possible collations a binary collation.

But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters a and b as equivalent to A and B; (2) then compare the encodings. We call this a case-insensitive collation. It is a little more complex than a binary collation.

In real life, most character sets have many characters: not just A and B but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules, not just for whether to distinguish lettercase, but also for whether to distinguish accents (an 「accent」 is a mark attached to a character as in German Ö), and for multiple-character mappings (such as the rule that Ö = OE in one of the two German collations).

這麼看來collation其實就是定義了字符集中的字元需要怎麼被比較以及如何排序的問題。那麼在MySQL中有多少中collation呢?通過 SHOW COLLATION 命令可以看到(全部內容就不貼出來了，請大家自行執行命令)：

+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
... ...
| geostd8_general_ci       | geostd8  |  92 | Yes     | Yes      |       1 |
| geostd8_bin              | geostd8  |  93 |         | Yes      |       1 |
| cp932_japanese_ci        | cp932    |  95 | Yes     | Yes      |       1 |
| cp932_bin                | cp932    |  96 |         | Yes      |       1 |
| eucjpms_japanese_ci      | eucjpms  |  97 | Yes     | Yes      |       1 |
| eucjpms_bin              | eucjpms  |  98 |         | Yes      |       1 |
| gb18030_chinese_ci       | gb18030  | 248 | Yes     | Yes      |       2 |
| gb18030_bin              | gb18030  | 249 |         | Yes      |       1 |
| gb18030_unicode_520_ci   | gb18030  | 250 |         | Yes      |       8 |
+--------------------------+----------+-----+---------+----------+---------+
222 rows in set (0.00 sec)

可以看到這些collation都有一種統一的格式：字符集+語言名稱+後綴。前面兩個部分好理解，來看下最後一個部分所代表的含義：

後綴	含義
_ai	Accent-insensitive 口音不敏感
_as	Accent-sensitive 口音敏感
_ci	Case-insensitive 大小寫不敏感
_cs	Case-sensitive 大小寫敏感
_bin	Binary 二進位排序

在這裡MySQL給出了一些解釋：對於是ci後綴的collation(大小寫不敏感)也意味著Accent-insensitive(口音不敏感)。同理對於是cs後綴的collation(大小寫敏感)也意味著Accent-sensitive(口音敏感)[6]。

For nonbinary collation names that do not specify accent sensitivity, it is determined by case sensitivity. If a collation name does not contain _ai or _as, _ci in the name implies _ai and _cs in the name implies _as. For example, latin1_general_ci is explicitly case-insensitive and implicitly accent-insensitive, and latin1_general_cs is explicitly case-sensitive and implicitly accent-sensitive.

這裡舉一個實際的例子說明下這個collation的cs和ci後綴的作用(一般在工作中經常遇到的是這兩個collation)。假設當前有這麼一張表：

mysql> show create table test_collation \G
*************************** 1. row ***************************
       Table: test_collation
Create Table: CREATE TABLE `test_collation` (
  `c` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
  `c1` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

其中的數據是：

mysql> select * from test_collation ;
+------+------+
| c    | c1   |
+------+------+
| a    | A    |
| b    | B    |
| B    | b    |
+------+------+
3 rows in set (0.00 sec)

那麼我們分別執行如下的幾個SQL：

1. select * from test_collation where c = 'b';
2. select * from test_collation where c1 = 'b';
3. select c from test_collation group by c;
4. select c1 from test_collation group by c1;

分別得到如下的幾個結果：

1. select * from test_collation where c = 'b';
+------+------+
| c    | c1   |
+------+------+
| b    | B    |
+------+------+
1 row in set (0.00 sec)

2. select * from test_collation where c1 = 'b';
+------+------+
| c    | c1   |
+------+------+
| b    | B    |
| B    | b    |
+------+------+
2 rows in set (0.00 sec)

3. select c from test_collation group by c;
+------+
| c    |
+------+
| a    |
| B    |
| b    |
+------+
3 rows in set (0.00 sec)

4. select c1 from test_collation group by c1;
+------+
| c1   |
+------+
| A    |
| B    |
+------+
2 rows in set (0.00 sec)

c 是大小寫敏感的列，cs 是大小寫不敏感的列。通過實驗結果可以可以得到如下幾個結論：

大小寫敏感的情況下，b和B(大小和小寫是不等的)。
大小寫不敏感的話，查詢b會得到B和b的結果(相信很多DBA在工作中也遇到個這個問題，明明應該一條記錄，怎麼就查出了兩條)。
對於group by，cs會區分b和B，ci則會把兩個字元當做一個，這點從上面1-2可以推測出。

ps 關於collation的問題，還可以參考這篇文章:

[1].How to choose your MySQL encoding and collation。

2.2.2 collation bin和Binary strings的不同

在前文的介紹中，我們忽略了一種類型的collation: _bin。這種排序規則是按照字元串的二進位值進行排序和比較[7]。

For the binary collation of the binary character set, comparisons are based on numeric byte values. For the _bin collation of a nonbinary character set, comparisons are based on numeric character code values, which differ from byte values for multibyte characters. For information about the differences between the binary collation of the binary character set and the _bin collations of nonbinary character

我們可能會想起在MySQL中有另外的一種數據類型叫做Binary strings (as stored using the BINARY, VARBINARY, and BLOB data types)，那麼這個Binary strings 和 collation 中的_bin 又有什麼區別呢？

從用於排序比較的數據單元(The Unit for Comparison and Sorting)

Binary strings是一連串位元組序，比較和排序是基於位元組值的。而collation 中的_bin對應的字元串一般而言是多位元組的(每個字元由N個位元組組成，因此是多個位元組)，排序和比較的規則是基於每個字元的位元組值。

字符集的轉換(Character Set Conversion)

對於具有_bin 後綴的collation的字符集，可以在多種情況下自動轉換成其他的字符集。對於Binary strings而言，只能 copied byte-wise按照位元組的值一個個複製。

大小寫轉換(Lettercase Conversion)

對於具有_bin 後綴的collation的字符集可以進行字元的大小寫轉換，而Binary strings會直接忽略這個事情，除非先把它轉成某一個字符集。例如：

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT LOWER('aA'), UPPER('zZ');
+-------------+-------------+
| LOWER('aA') | UPPER('zZ') |
+-------------+-------------+
| aa          | ZZ          |
+-------------+-------------+
mysql> SET NAMES binary;
mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING utf8mb4));
+-------------+------------------------------------+
| LOWER('aA') | LOWER(CONVERT('aA' USING utf8mb4)) |
+-------------+------------------------------------+
| aA          | aa                                 |
+-------------+------------------------------------+

對於字元串最後的空格的處理(Trailing Space Handling in Comparisons)

對於具有_bin 後綴的collation的字符集，字元串最後的一個空格，在比較字元串是否相等的時候不會被計入：

mysql> SET NAMES utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
mysql>  SELECT 'a a ' = 'a a';
+----------------+
| 'a a ' = 'a a' |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

而對於Binary strings空格會被計入：

mysql> SET NAMES binary;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

而在插入數據的時候，_bin 後綴的collation的字符集會忽略空格，但是Binary strings不會：

mysql> CREATE TABLE t1 (
         a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
         b BINARY(10)
       );
mysql> INSERT INTO t1 VALUES ('x','x');
mysql> INSERT INTO t1 VALUES ('x ','x ');
mysql> SELECT a, b, HEX(a), HEX(b) FROM t1;
+------+------------+--------+----------------------+
| a    | b          | HEX(a) | HEX(b)               |
+------+------------+--------+----------------------+
| x    | x          | 78     | 78000000000000000000 |
| x    | x          | 78     | 78200000000000000000 |
+------+------------+--------+----------------------+

2.3 字符集配置以及轉換規則

前文我們介紹了字符集與字符集排序規則，本節開始介紹下MySQL中字符集配置相關的內容。在MySQL中字符集設置相當靈活和複雜，使用命令可以看到相關的幾個配置如下：

+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| character_set_client     | utf8                                     |
| character_set_connection | utf8                                     |
| character_set_database   | latin1                                   |
| character_set_filesystem | binary                                   |
| character_set_results    | utf8                                     |
| character_set_server     | latin1                                   |
| character_set_system     | utf8                                     |
| character_sets_dir       | /home/mysql/mysql-5.7.18/share/charsets/ |
+--------------------------+------------------------------------------+

接下來介紹下每個配置：

character_set_client : 客戶端發送SQL語句給MySQL所使用的編碼。這個值隨每個客戶端設置的不同而變化。

The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server. (Many clients support a --default-character-set option to enable this character set to be specified explicitly. See also Section 10.4, 「Connection Character Sets and Collations」.) The global value of the variable is used to set the session value in cases when the client-requested value is unknown or not available, or the server is configured to ignore client requests:

The client requests a character set not known to the server. For example, a Japanese-enabled client requests sjis when connecting to a server not configured with sjis support.

The client is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.

mysqld was started with the --skip-character-set-client-handshake option, which causes it to ignore client character set configuration. This reproduces MySQL 4.0 behavior and is useful should you wish to upgrade the server without upgrading all the clients.

Some character sets cannot be used as the client character set. Attempting to use them as the character_set_client value produces an error. See Impermissible Client Character Sets.

character_set_connection : MySQL連接字符集。其實一開始接觸到這個配置的時候，我也是一臉懵逼為什麼要有一個這樣子的配置。客戶端已經有字符集配置了，為什麼還要在進入內部字符集之前設置一個這樣子的字符集進行轉換。後面我查了下發現不單單是我，中國外很多人都有這個疑問，比如這個貼子下[What is the purpose of character_set_connection?] 就對這個問題進行了討論。後續我也仔細閱讀了MySQL的問題，根據我自己的理解之所以設置這個參數，應該是針對於這種情況的：

假設MySQL當前沒有character_set_connection這個參數，SQL語句在server端通過character_set_client參數解碼之後變進入內部字符集進行比較，那麼例如這樣子的SQL中的字元串(“ABC”)的編碼也會是character_set_client的編碼值：select * from order where orderid =”ABC”。那麼如果開發者想讓”ABC”擁有其他編碼怎麼辦，MySQL提供了一個叫做Character Set Introducers的[8]方法，可以這麼指定 select * from order where orderid = _utf8 “ABC”。後面發現每個語句都寫一個Character Set Introducers來指定編碼太累了，於是提供了character_set_connection參數，對於沒有Character Set Introducers的字元串，都編碼成character_set_connection所指定的編碼。

The character set used for literals specified without a character set introducer and for number-to-string conversion. For information about introducers, see Section 10.3.8, 「Character Set Introducers」.

ps 關於character_set_connection參數的其他討論，可以參見如下的幾篇文章：

[1].mysql中character_set_connection的作用

[2].理解MySQL字符集

[3].What is the purpose of character_set_connection?

[4].徹底解決MySQL中的亂碼問題

character_set_database : 這個好理解，就是資料庫的字符集。

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

character_set_filesystem : 文件系統字符集。該變數用於解釋引用文件名的字元串文字，例如在LOAD DATA INFILE和SELECT … INTO OUTFILE語句和LOAD_FILE（）函數中。

The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to 'utf8mb4'.

character_set_results : SQL語句執行的結果集字符集。當查詢執行完畢之後，返回給客戶端的結果使用這個字符集編碼。

The character set used for returning query results to the client. This includes result data such as column values, result metadata such as column names, and error messages.

character_set_server : 整個伺服器基本的字符集配置。如果創建資料庫的時候不指定字符集就使用這個配置。

The servers default character set. See Section 10.15, 「Character Set Configuration」. If you set this variable, you should also set collation_server to specify the collation for the character set.

character_set_system : 系統元數據字符集，系統元數據(表名、欄位名等)存儲時使用的編碼字符集，該欄位和具體存儲的數據無關。總是固定不變的UTF8字符集。

The character set used by the server for storing identifiers. The value is always utf8.

前面介紹了很多的關於字符集的配置，這些字符集配置之間也有一定的轉換關係[9]:

對於Client發起的SQL，會使用 character_set_client進行編碼。當SQL到了MySQL Server的時候，會使用character_set_client進行解碼。之後如果character_set_client的編碼與character_set_connection編碼不一致的時候，會將請求的數據轉換成character_set_connection的編碼。在進行內部操作前會判斷內部編碼是否與character_set_connection一致，如果不一致則將character_set_connection的編碼進行轉換：

使用每個數據欄位的CHARACTER SET設定值；
若上述值不存在，則使用對應數據表的DEFAULT CHARACTER SET設定值(MySQL擴展，非SQL標準)；
若上述值不存在，則使用對應資料庫的DEFAULT CHARACTER SET設定值；
若上述值不存在，則使用character_set_server設定值。

3. MySQL字符集的一些問題

3.1 字符集配置對於Innodb引擎存儲數據所帶來的一點影響

MySQL對於一條記錄的長度是有限制而且這個限制分為兩層，在server層一條記錄最大不能超過65535個位元組；在Innodb引擎層因為記錄都必須按照B樹的格式組織，因此如果在一個page是16KB的配置下，最大的記錄長度不能大於16KB/2=8K(如果一個page裡面就只有一條記錄，那麼B樹就退化成鏈表也就失去了B樹的意義)。此外還要扣除page中的一些元數據的長度，最後實際一個記錄的最大長度會小於8K(實際應該是8126 B)。如果一個記錄的行大於8K怎麼辦，比如有一個欄位用於存儲文章的內容定義為varchar(3000)，然後是utf8mb4字符集(utf8mb4 是4個位元組的，然後3000個字元最大就是12000個位元組大約12K)。對於這種大欄位Innodb會將其存儲在溢出頁(page overflow)中。不同的行格式對於溢出頁的實現還有一定的區別：

compact 格式: 如果欄位長度小於768 Bytes則不會發生page overflow。而如果超過了768 Bytes，那麼前768 Bytes 依然在數據頁中，剩餘的部分放在溢出頁(off-page)中。同時當前頁中增加一個 20 個位元組的指針（即 SPACEID + PAGEID + OFFSET）和本地長度資訊（2 個位元組），共計 768 + 20 + 2 = 790 個位元組存儲在當前記錄。
compressed或dynamic格式: 這種格式下資料庫會儘可能的存放數據在數據頁中，只有當一個頁沒法存放2條數據的時候，會將最長的一列的所有數據放入溢出頁，同時在原來的記錄上保留20個位元組的指針，而對text的數據類型會存放前40byte 在數據頁中。

根據上面的分析可以看出，字符集的設置對於表能夠定義的列和記錄的最大長度都有影響。例如文獻[12]給出的一個例子:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
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

這裡定義了的66000個字元並且是latin1的字符集，因此超過了65535個位元組的限制。如果把這個表的欄位減少一個就可以創建成功:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),        f VARCHAR(10000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.00 sec)

那麼換一個字符集比如utf8，那麼創建就會失敗：

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),        f VARCHAR(10000)) ENGINE=InnoDB CHARACTER SET utf8;
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>

上面演示的例子都是server層面的限制，接下來看看引擎層面的限制:

mysql> create table t (a varchar(1000),a1 varchar(1000),a2 varchar(1000),a3 varchar(1000),a4 varchar(1000),a5 varchar(1000),a6 varchar(1000),a7 varchar(1000),a8 varchar(1000),a9 varchar(1000),a10 varchar(1000),a11 varchar(1000),a12 varchar(1000)) ENGINE=InnoDB   ROW_FORMAT=COMPACT CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t select repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000);
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

在上面的例子中，建表的語句中構建了12個1000個字元的列，因為12*1000<65533所以在server層檢查通過。但是因為定義的行格式是COMPACT，當列的數據超過768位元組的時候會將前768位元組放在數據頁中，剩餘的數據才放入溢出頁中，所以整個插入的數據行是大於8126K的，在引擎層就被拒絕了。

ps 關於MySQL記錄長度限制的討論可以參見如下的幾篇文字:

[1].【MySQL經典案例分析】關於數據行溢出由淺至深的探討

[2]. 技術分析 | MySQL TEXT 欄位的限制

[3]. 技術分享 | MySQL 欄位長度限制的計算方法

3.2 MySQL中的UTF8和Latin1

為了能夠支援中文以及一些其他的非英文文字，開發者經常使用utf8字符集。但是在MySQL中的utf8字符集存在一個問題，它最大的長度是三位元組最大能編碼的 Unicode 字元是 0xffff，僅僅只能支援Unicode 中的基本多文種平面（BMP）[11]。如果字元不在BMP裡面，則會被截斷並且造成亂碼。例如當前有下面這樣一張表：

CREATE TABLE `test_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

字符集設定的是utf8，然後插入字元”𡋾”(編碼是F0A18BBE)，則最後會發現warning 以及數據亂碼:

set names utf8;
mysql> INSERT INTO test_user(name) VALUES("𡋾");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select *  from test_user ;
+----+------+
| id | name |
+----+------+
|  1 | �  |
+----+------+
1 row in set (0.00 sec)

因此如果使用UTF8字符集存一些比較特殊的字元就會出現亂碼。至於MySQL為啥會有這個bug，可以參見如下這兩篇文章:

[1].MySQL utf8之坑

[2].記住：永遠不要在MySQL中使用UTF-8

那麼要存一些特殊的字元並且不能出現亂碼的問題怎麼辦？MySQL後面推出了utf8mb4字符集解決這個問題。因此如果需要支援全部的Unicode編碼，建議使用utf8mb4字符集。關於utf8mb4的介紹可以參見如下的兩篇文字：

[1].全面了解mysql中utf8和utf8mb4的區別

[2].10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

說完UTF8字符集，再來談談Latin1字符集的問題。以前經常遇到這樣一個問題: Latin1字符集是否支援中文? 為了說明這個問題，本文來做如下的測試:

設置終端字符集為utf8。
mysql的所有字符集配置都改成latin1。

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| character_set_client     | latin1                                   |
| character_set_connection | latin1                                   |
| character_set_database   | latin1                                   |
| character_set_filesystem | binary                                   |
| character_set_results    | latin1                                   |
| character_set_server     | latin1                                   |
| character_set_system     | utf8                                     |
| character_sets_dir       | /home/mysql/mysql-5.7.20/share/charsets/ |
+--------------------------+------------------------------------------+
8 rows in set (0.01 sec)

然後創建一個latin字符集的表，並插入數據:

mysql> show create table test_user \G
*************************** 1. row ***************************
       Table: test_user
Create Table: CREATE TABLE `test_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into test_user select "1","一";
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_user;
+----+------+
| id | name |
+----+------+
|  1 | 一  |
+----+------+
1 row in set (0.00 sec)

可以見得，在MySQL表和欄位裡面的字符集是Latin1，通過終端插入中文正常插入並且正常顯示了沒有亂碼。那麼什麼情況下會有亂碼出現？比如將name這個列改成2個字元長度:

mysql> show create table test_user \G
*************************** 1. row ***************************
       Table: test_user
Create Table: CREATE TABLE `test_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into test_user select "1","一";
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_user;
+----+------+
| id | name |
+----+------+
|  1 | �   |
+----+------+
1 row in set (0.00 sec)

到了這裡我們應該可以得出結論了。其實對於Latin1字符集而言它存的數據是單個位元組，對於UTF8這種多個位元組組成的字符集，Latin1編碼的列會將每個位元組都存入。因此在插入數據和讀取數據的時候，其實都是把Latin1編碼中的每個位元組讀出來，之後在終端會被轉換成UTF8編碼顯示。所以顯示的數據也是正確的沒有亂碼。那麼什麼情況下有亂碼，答案就是當列的長度不夠包含多個位元組的時候。比如本文前面的例子，name列的長度變成了2個字元(此時編碼是latin1 ，那麼2個字元就是2個位元組的長度)，而終端編碼是UTF8，插入的數據自然是3個位元組的，因此到了資料庫層面就會被截斷，因此也產生了亂碼。所以要說 Latin1支援中文這個說法，其實也對也不對，要根據當前系統的配置才能最後確認Latin1編碼下是否支援中文。

ps 關於這個問題的更多討論可以參見如下的文章:

[1]. mysql中文亂碼的一點理解

此外在此文形成的過程中，還發現了對於字符集利用進行SQL注入的一些討論，本文沒有去做測試，僅僅將查閱到的資料放在這裡感興趣的讀者可以自行查閱:

[1].Mysql字元編碼利用技巧

[2]./Team:紅日安全團隊團隊成員：CPRTitle：寬位元組注入/

3.3 collation和字符集對於執行計劃的影響

最後回到本文開頭的時候提到的慢查詢問題。在這個例子中explain 輸出了提示資訊為Range checked for each record (index map: 0x1)。並且通過show warnings命令會提示如下的資訊:

| Warning | 1739 | Cannot use ref access on index ‘productid’ due to type or collation conversion on field ‘productid’ |
| Note | 1003 | /* select#1 */ select djx.Order1.productid AS productid,djx.Order1.orderid AS orderid,djx.Product.name AS name from djx.Order1 left join djx.Product on((djx.Order1.productid = djx.Product.productid)) where 1 |

可以見得執行優化器認為Order1表的索引productid 和 Product表的索引productid因為類型或者collation不一致，所以無法通過索引進行join。此外通過MySQL開發團隊的部落格了解到在Range checked for each record的提示資訊下，SQL的執行有如下的2種辦法[12]：

Read all rows in the table through a table scan : 對於驅動表的每一行記錄進行全表掃描
read all rows which were sent using the dynamic range access method on index 『0x2』.:對於驅動表的每一行記錄，通過可能的索引在另外的一個表把所有數據掃出來

因此根據show warnings命令提示的資訊和文獻[12]的資訊，可以推斷本文開頭的那個慢查詢SQL執行的過程是對於驅動表的每一行記錄都去被驅動表裡面全表掃描匹配一次，所以整個查詢變得非常的慢。(ps : 不過這裡我一直有一個疑問，兩個表的productid的索引的字符集其實是一樣的，不同的只是排序規則和比較的規則(是否大小寫敏感)不一樣。個人認為其實還是可以通過被驅動表的索引進行join的。至於為什麼SQL優化器最後沒有選擇這麼做，如果大家有答案希望告訴我下。)

通過本文開頭的問題可以看出collation 對於執行計劃是有影響的，同樣字符集也會對整個SQL的執行計劃有影響。因為這個問題還挺常見的，在這裡就不繼續做相關的討論和敘述，感興趣的讀者可以閱讀如下的幾篇文章：

[1].MySQL表欄位字符集不同導致的索引失效問題

[2].記一次資料庫更改字符集踩的坑