MySQL 前缀索引究竟怎么个事

索引长度的限制

MySQL 的每个单表中所创建的索引长度是有限制的,且对不同存储引擎下的表有不同的限制。

myisam 表，单列索引，最大长度不能超过 1000 bytes，否则会报警，但是创建成功，最终创建的是前缀索引（取前333个字符）。

myisam 表，组合索引，索引长度和不能超过 1000 bytes，否则会报错，创建失败；

innodb 表，单列索引，超过 767 bytes的，给出warning，最终索引创建成功，取前缀索引（取前 255 字符）。

innodb表，组合索引，各列长度不超过 767 bytes ，如果有超过 767 bytes 的，则给出报警，索引最后创建成功，

但是对于超过 767 字节的列取前缀索引，与索引列顺序无关，总和不得超过 3072 ，否则失败，无法创建。

MySQL 版本不同而导致的索引长度限制不同。在 MySQL5.5 版本，引入了 innodb_large_prefix，用来禁用大型前缀索引，以便与不支持大索引键前缀的早期版本的 InnoDB 兼容 开启 innodb_large_prefix 可以使单索引的长度限制达到 3072 字节（但是联合索引总长度限制还是 3072 字节），禁用时单索引的长度限制为 767 字节 在 MySQL5.5版本与MySQL5.6 版本，innodb_large_prefix 是默认关闭的，在 MySQL5.7 及以上版本则默认开启 在 MySQL8.0 版本中，innodb_large_prefix 已被移除，从版本 8.0 开始，索引长度限制由表字段(row format)决定， 若为 DYNAMIC 或 COMPRESSED 时，限制值为 3072；为 REDUNDANT 或 COMPACT 时，限制值为 767。

那么为什么是 3072 呢？

InnoDB 一个 page 的默认大小是 16 k。由于是 Btree 组织，要求叶子节点上一个 page 至少包含两条记录（否则就退化链表了）。 所以一个记录最多不能超过 8 k。又由于 InnoDB 的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过 4 k（极端情况，pk 和某个二级索引都达到这个限制）。 由于需要预留和辅助空间，扣掉后不能超过 3500，取个“整数”就是(1024 * 3 = 3072)。

utf8 与 utf8mb4 的区别

由于历史原因，MySQL 刚开始设计的时候，"天真的"认为使用 3 个字节就足够存储字符串了，因此将 UTF-8 进行阉割； 然而遇到复杂的汉字或者 emoji 表情等 4 字节的宽字符的时候，存储就会出现异常， 因此在版本 5.7.3 开始引入 utf8mb4，其表示为 most bytes 4，即最多占用 4 个字节。

所以当创建索引时报错很有可能是因为字符集的原因。

[42000][1071] Specified key was too long; max key length is 767 bytes

TextField 是不支持建立索引的;

什么是前缀索引

所谓前缀索引，说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时去指定），比如以产品名称的前 10 位来建索引，这样建立起来的索引更小，查询效率更快！

有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数。

为什么要用前缀索引

一般来说，当某个字段的数据量太大，而且查询又非常的频繁时，使用前缀索引能有效的减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。

比如，客户店铺名称，有的名称很长，有的很短，如果完全按照全覆盖来建索引，索引的存储空间可能会非常的大，有的表如果索引创建的很多，甚至会出现索引存储的空间都比数据表的存储空间大很多，因此对于这种文本很长的字段，我们可以截取前几个字符来建索引，在一定程度上，既能满足数据的查询效率要求，又能节省索引存储空间。

但是另一方面，前缀索引也有它的缺点，MySQL 中无法使用前缀索引进行 ORDER BY 和 GROUP BY，也无法用来进行覆盖扫描，当字符串本身可能比较长，而且前几个字符完全相同，这个时候前缀索引的优势已经不明显了，就没有创建前缀索引的必要了。

因此这又回到一个概念，那就是关于索引的选择性！当某个字段内容的前几位区分度很高的时候，这个时候采用前缀索引，可以在查询性能和空间存储方面达到一个很高的性价比。

计算选择性 计算整列的选择性: select count(distinct city)/count() from cityTable 计算前缀列的选择性: select count(distinct left(city,3))/count() from cityTable

创建前缀索引

1. 使用CREATE INDEX语句，在定义索引时指定前缀长度，例如：

CREATE INDEX index_name ON table_name (column_name(length));

2. 使用ALTER TABLE语句，在添加索引时指定前缀长度，例如：

ALTER TABLE table_name ADD INDEX (column_name(length));

3. 在CREATE TABLE语句中直接定义索引时指定前缀长度，例如：

CREATE TABLE table_name (
  column_name varchar(255) NOT NULL,
  PRIMARY KEY (column_name(10))
);

使用前缀索引需要注意的事项

是不是所有的字段，都适合用前缀索引呢？

答案显然不是，在上文我们也说到了，当某个索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引，或者非常长的 VARCHAR 列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。

由于前缀索引只索引了字符串的前面一部分，所以占用的空间会更小，这就是使用前缀索引的优势。但，这同时带来的损失是，可能会增加额外的记录扫描次数。

欢迎加入 DevXTalk 的程序员、架构师、IT从业者交流群,在这里你可以和大佬们一起交流分享技术,行业经验,职位内推,摸鱼聊天。总之期待你的加入... 添加我的wx,备注：“进群”拉你进群(群二维码有效时间太短维持不了几天)

有招聘需求的也可以加我微信把招聘岗位发给我，我会贴在文章末尾，毕竟我的读者个个都是人才。

看完本文有收获？转发给更多的朋友吧

DevXTalk 不止于技术