MySQL Varchar前缀索引的一个细节

在线上环境中，对一个varchar类型的字段做索引的时候，如果这个字段的长度太长，我们经常会用到前缀索引。

使用前缀索引有2个好处：

1、前缀索引的存在，使得整个索引的数据量变小；
2、在insert数据的时候，维护索引的速度变快，insert操作的效率也会提升。

例如，下面是一个前缀索引的例子，我们对name列的前10个字段做了个索引。

注意：以下测试版本都是MySQL8.0.12

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `val` int(11) DEFAULT NULL,
  `name` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE test ADD INDEX idx_name (name ( 10 ));

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `val` int(11) DEFAULT NULL,
  `name` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  KEY `idx_name` (`name`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

在MySQL中，前缀索引的长度是有一定限制的。我们将name字段上的索引修改成name(1000)，看看结果：

mysql> ALTER TABLE test DROP INDEX idx_name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test ADD INDEX idx_name (name ( 1000 ));
1071 - Specified key was too long; max key length is 3072 bytes
mysql> show warnings;
+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+
1 row in set (0.04 sec)

可以看到，出现1个warning，信息是这个前缀索引太长了。最长长度是3072个字节。

mysql> ALTER TABLE test ADD INDEX idx_name (name ( 769 ));
1071 - Specified key was too long; max key length is 3072 bytes
mysql> ALTER TABLE test ADD INDEX idx_name (name ( 768 ));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `val` int(11) DEFAULT NULL,
  `name` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  KEY `idx_name` (`name`(768))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

可以看到name列前缀索引长度修改为769失败了，而修改为768成功了，本质原因其实是3072个字节，采用utf8mb4编码，对应的字符数量就是3072/4，也就是768

若编码是utf8就除3

mysql> ALTER TABLE test ADD INDEX idx_name (name ( 1025 ));
1071 - Specified key was too long; max key length is 3072 bytes
mysql> ALTER TABLE test ADD INDEX idx_name (name ( 1024 ));
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE `test`  (
  `id` int(11) NULL DEFAULT NULL,
  `val` int(11) NULL DEFAULT NULL,
  `name` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  INDEX `idx_name`(`name`(1024)) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

经过查询官方文档，得到正确答案：

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables that use the REDUNDANT or COMPACT row format. The prefix length limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. For MyISAM tables, the prefix length limit is 1000 bytes.

也就是说，对于Innodb来说，redundant或者compact类型的行格式，默认最大前缀索引长度是767；dynamic或者compressed行格式，默认的最大前缀索引长度是3072；
对于MyISAM来讲，最大前缀索引长度是1000。

我们看下test表的行格式：

mysql> SELECT * FROM information_schema.TABLES WHERE	table_name = 'test';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION    | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
| def           | laravel      | test       | BASE TABLE | InnoDB |      10 | Dynamic    |         11 |           1489 |       16384 |               0 |            0 |         0 | NULL           | 2022-07-21 16:10:05 | NULL        | NULL       | utf8mb4_general_ci | NULL     |                |               |
| def           | test         | test       | BASE TABLE | InnoDB |      10 | Dynamic    |      15756 |            100 |     1589248 |               0 |      1589248 |         0 | NULL           | 2021-12-28 11:34:43 | NULL        | NULL       | utf8_general_ci    | NULL     |                | 测试表        |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
2 rows in set (0.06 sec)

可以看到，这个行格式是Dynamic，当然，最大索引长度就是3072了。
MySQL默认的行格式就是Dynamic，其他格式的，这里不再测试。

原文地址