前两天同事提了一个问题，MySQL 5.7中给某张表字段增加一个单键值索引，提示了如下错误，

小白学习MySQL - 变通创建索引的案例一则_mysql

一些背景信息，

1. 表设置的字符集，utf8mb4。

2. 表的存储引擎，MyISAM。

3. 表的数据量，1个亿。

4. 执行的SQL，select c1, c2 from test where c1 = :1，c1数据类型是varchar(255)，区分度较高，需要创建一个单键值索引。

我们知道，MySQL和Oracle在索引上最大的一个区别，就是索引存在长度的限制。如果是超长键值，可以支持创建前缀的索引，顾名思义，取这个字段的前多少个字符/字节作为索引的键值。

P.S. 《小白学习MySQL - 索引键长度限制的问题》

之所以可以定义一个字段前缀作为键值，存储效率是考虑的一个因素，如果列名的前10个字符通常都是不同的，检索这10个字符创建的索引应该会比检索整个列作为索引的效率更高，使用列前缀作为索引会让索引树更小，不仅节省空间，还可能提高插入操作的速度。

对于非二进制的字符串类型(CHAR、VARCHAR、TEXT)，前缀会按照字符个数计算，对二进制的字符串类型(BINARY、VARBINARY、BLOB)，前缀会按照字节个数计算，因此，当对非二进制的字符串列明确前缀长度的时候，需要考虑多字节字符集的因素。

MySQL官方手册索引的章节提到了，前缀索引长度限制是和引擎相关的，如果用的是InnoDB，前缀上限是767字节，当启用innodb_large_prefix时，上限可以达到3072字节。如果用的是MyISAM，前缀上限是1000字节，这正是上述创建索引错误提示的内容，

小白学习MySQL - 变通创建索引的案例一则_字符串_02

https://dev.mysql.com/doc/refman/5.7/en/create-index.html

utf8mb4是MySQL 5.5.3之后增加的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode，utf8mb4是utf8的超集。之所以需要utf8mb4，是因为之前的utf8编码最大字符长度为3字节，如果遇到4字节的宽字符就会插入异常了。三个字节的UTF-8最大能编码的Unicode字符是 0xffff，也就是Unicode中的基本多文种平面(BMP)。因此，任何不在基本多文本平面的Unicode字符，都无法使用MySQL的utf8字符集存储，这就包括Emoji表情(Emoji是一种特殊的Unicode 编码，常见于手机上)，和很多不常用的汉字，以及任何新增的Unicode字符等(这些都是utf8的缺点)。

InnoDB，如果需要建索引，就不能超过767bytes，utf8编码，255*3=765 bytes，是能建索引情况下的最大值，utf8mb4编码，默认字符长度则应该是767除以4向下取整，就是191。如果设置了innodb_large_prefix，最大长度是3072字节，utf8编码，1024*3=3072 bytes，utf8mb4编码，768*4=3072。

MyISAM，如果需要建索引，就不能超过1000bytes，utf8编码，333*3=999 bytes，是能建索引情况下的最大值，utf8mb4编码，默认字符长度则应该是1000除以4，就是250。

我们可以测下MyISAM，utf8mb4编码表test1，250长度的字段c1，251长度的字段c2，

CREATE TABLE test1 (
  c1 varchar(250),
  c2 varchar(251)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

c1能创建索引，c2创建出错，utf8mb4编码，varchar最大限制250个字符，

/* 可执行 */
create index idx_test1_01 on test1(c1);
/*SQL 错误 [1071] [42000]: Specified key was too long; max key length is 1000 bytes*/
create index idx_test1_02 on test1(c2);

utf8编码表test2，333长度的字段c1，334长度的字段c2，

CREATE TABLE test2 (
  c1 varchar(333),
  c2 varchar(334)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

c1能创建索引，c2创建出错，utf8编码，varchar最大限制333个字符，

/* 可执行 */
create index idx_test2_01 on test2(c1);
/*SQL 错误 [1071] [42000]: Specified key was too long; max key length is 1000 bytes*/
create index idx_test2_02 on test2(c2);

其他场景的测试相近，不再展示。

因此，针对这个问题，可以为c1字段创建一个前缀索引，

create index idx_test_01 on test(c1(250(含)以下的值))

但是1亿数据量，创建非常慢，

小白学习MySQL - 变通创建索引的案例一则_mysql_03

通过沟通，了解到这个需求是检索今年的数据，表中存在时间戳的字段，且今年的数据，是1000多万，如果能改应用SQL，或者只是通过手工执行SQL的前提下，可以有几种解决方案，

1. 如果从原表检索，可以创建一个c1和时间戳字段的复合索引，利用索引扫描，定位所需数据。

2. 如果不需要从原表检索，可以使用时间戳作为条件，通过CTAS，创建一张今年数据的表，通过c1单键值索引，即可定位数据。