从一个慢查询到MySQL字符集编码

11. 问题起源

最近在完成一个线上日志修复工作的过程中遇到了一个意想不到的慢查询。当时使用的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同学和开发同学在实际工作中提供一些参考信息。

22. MySQL字符集和字符集排序规则

2.1 字符集相关概念

在谈起数据库的字符集之前，首先需要了解下字符，字符集和字符编码的概念。相信很多人在一开始的时候也对这些概念比较混乱。（以下的概念说明摘自维基百科和百度百科）

1. 字符

在电脑和电信领域中，字符（Character）是一个信息单位。对使用字母系统或音节文字等自然语言，它大约对应为一个音位、类音位的单位或符号。简单来讲就是一个汉字、假名、韩文字……，或是一个英文、其他西方语言的字母[1]。

1. 字符集

字符集（Character set）是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同，常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等[2]。同时字符集还为每个集合中的字符规定了一套编码规则，将每个字符与一个编码做映射。

1. 字符编码

字符编码（英语：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