MySQL 前缀索引究竟怎么个事

MySQL 前缀索引摘要

索引长度的限制

MySQL中，索引长度受限于表的存储引擎和MySQL版本。MyISAM存储引擎的单列索引最大长度为1000 bytes，组合索引总长度也不得超过1000 bytes。InnoDB存储引擎允许单列索引最大767 bytes，组合索引各列不超过767 bytes，总和不得超过3072 bytes。不同版本的MySQL对索引长度限制不同，MySQL 5.5引入了innodb_large_prefix选项，允许单索引长度达3072 bytes，而在MySQL 8.0版本中，索引长度限制取决于表字段的格式。

utf8 与 utf8mb4 的区别

MySQL最早设计时将UTF-8阉割为3个字节，后来发现无法存储4字节的宽字符，如某些汉字和emoji表情，因此在5.7.3版本引入utf8mb4字符集，支持最多4个字节。

什么是前缀索引

前缀索引是指对文本字段的前几个字符建立索引。这种索引较小，查询效率较高，无需使用函数即可完成匹配。

为什么要用前缀索引

使用前缀索引可以减小索引文件的大小，提高索引查询速度。适用于字段数据量大且查询频繁的情况。然而，前缀索引不适用于ORDER BY和GROUP BY，且当字段前几个字符相同时索引效果不明显。

创建前缀索引

可以通过CREATE INDEX、ALTER TABLE或CREATE TABLE语句在定义索引时指定前缀长度。

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

不是所有字段都适合使用前缀索引。对于BLOB和TEXT列，或非常长的VARCHAR列，必须使用前缀索引。前缀索引减小了空间占用，但可能增加记录扫描次数。