mysql创建索引异常
背景
mysql创建表的时候失败：
Specified key was too long;max key length is 767 bytes
原因
索引长度限制
From the manual at http://dev.mysql.com/doc/refman/5.6/en/create-table.html >>从5.6的官方文档中我们能找到如下双引号中解释
"For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length.
...
Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters ...">>>对于myisam和innodb存储引擎，prefixes的长度限制分别为1000 bytes和767 bytes。注意prefix的单位是bytes，但是建表时我们指定的长度单位是字符。A utf8 character can use up to 3 bytes. Hence you cannot index columns or prefixes of columns longer than 333 (MyISAM) or 255 (InnoDB) utf8 characters. >>一个utf8字符占3个bytes（767/3=255.6666666666667）。因此在utf8字符集下，创建索引的单列长度不能超过333个字符(myisam存储引擎)和255个字符(innodb存储引擎)
每个列的长度不能大于767 bytes；所有组成索引列的长度和不能大于3072 bytes
innodb存储引擎:
- smallint 占2个bytes
- timestamp占4个bytes
- utf8字符集下，一个character占3个bytes( utf8: 767/3=255.6666666666667 )
- utf8mb4字符集下，一个character占4个bytes(utf8mb4: 767/4=191.75 )
解决
- 对列的前面部分创建索引（通过减小索引长度，这样能够减小索引文件的大小，能够加快数据的insert）
语法
mysql CREATE INDEX index_name ON table_name (column_name(length), clolumn_name(length)…)；如何确认当前字段设置一个合适的长度呢？
mysql select count(distinct left(password, 5))/count(*) from user
- 启用innodb_large_prefix参数 （ 启用innodb_large_prefix参数能够取消对于索引中每列长度的限制(但是无法取消对于索引总长度的限制3072bytes) ）
启用innodb_large_prefix必须同时指定innodb_file_format=barracuda，innodb_file_per_table=true，并且建表的时候指定表的row_format为dynamic或者compressed(mysql 5.6中row_format默认值为compact)
语法
mysql show variables like 'innodb_large_prefix'; show variables like 'innodb_file_format'; set global innodb_large_prefix=1; set global innodb_file_format=BARRACUDA;